-- Discovery Strategy Extended Schema
-- 011_discovery_strategy.sql

CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Discovery Strategies (extended)
ALTER TABLE IF EXISTS discovery_strategies ADD COLUMN IF NOT EXISTS objectives TEXT;
ALTER TABLE IF EXISTS discovery_strategies ADD COLUMN IF NOT EXISTS business_problem TEXT;
ALTER TABLE IF EXISTS discovery_strategies ADD COLUMN IF NOT EXISTS desired_insights TEXT;
ALTER TABLE IF EXISTS discovery_strategies ADD COLUMN IF NOT EXISTS data_storage_scope UUID[] DEFAULT '{}';
ALTER TABLE IF EXISTS discovery_strategies ADD COLUMN IF NOT EXISTS discovery_approach VARCHAR(50);
ALTER TABLE IF EXISTS discovery_strategies ADD COLUMN IF NOT EXISTS priority VARCHAR(20);
ALTER TABLE IF EXISTS discovery_strategies ADD COLUMN IF NOT EXISTS status VARCHAR(50) DEFAULT 'draft';
ALTER TABLE IF EXISTS discovery_strategies ADD COLUMN IF NOT EXISTS created_by UUID REFERENCES users(id) ON DELETE SET NULL;
ALTER TABLE IF EXISTS discovery_strategies ADD COLUMN IF NOT EXISTS created_at TIMESTAMPTZ DEFAULT NOW();
ALTER TABLE IF EXISTS discovery_strategies ADD COLUMN IF NOT EXISTS updated_at TIMESTAMPTZ DEFAULT NOW();

-- Data Discovery Process Tracking
CREATE TABLE IF NOT EXISTS data_discovery_processes (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  strategy_id UUID REFERENCES discovery_strategies(id) ON DELETE SET NULL,
  process_type VARCHAR(50) NOT NULL, -- 'discovery', 'classification', 'governance', 'repeat'
  status VARCHAR(50) DEFAULT 'pending', -- 'pending', 'running', 'completed', 'failed'
  started_at TIMESTAMPTZ,
  completed_at TIMESTAMPTZ,
  objectives_met BOOLEAN DEFAULT FALSE,
  data_points_discovered INT DEFAULT 0,
  data_points_classified INT DEFAULT 0,
  governance_policies_applied INT DEFAULT 0,
  created_by UUID REFERENCES users(id) ON DELETE SET NULL,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- On-Device Classification
CREATE TABLE IF NOT EXISTS on_device_classifications (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  device_id VARCHAR(255) NOT NULL,
  device_name VARCHAR(255),
  classification_type VARCHAR(50) NOT NULL, -- 'structured', 'unstructured', 'hybrid'
  data_source_type VARCHAR(50), -- 'local_file', 'database', 'api'
  data_source_path TEXT,
  classification_method VARCHAR(50), -- 'rule', 'ml', 'hybrid'
  model_version VARCHAR(50),
  results JSONB DEFAULT '{}',
  status VARCHAR(50) DEFAULT 'pending',
  started_at TIMESTAMPTZ,
  completed_at TIMESTAMPTZ,
  created_by UUID REFERENCES users(id) ON DELETE SET NULL,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Marketplace Connectors
CREATE TABLE IF NOT EXISTS marketplace_connectors (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  marketplace_type VARCHAR(50) NOT NULL, -- 'alibaba', 'sahab', 'hausabah'
  name VARCHAR(255) NOT NULL,
  config JSONB NOT NULL,
  status VARCHAR(50) DEFAULT 'disconnected', -- 'connected', 'disconnected', 'error'
  last_sync_at TIMESTAMPTZ,
  sync_schedule VARCHAR(100),
  created_by UUID REFERENCES users(id) ON DELETE SET NULL,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Admin Units (for scoping policies)
CREATE TABLE IF NOT EXISTS admin_units (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(255) NOT NULL,
  description TEXT,
  parent_unit_id UUID REFERENCES admin_units(id) ON DELETE SET NULL,
  created_by UUID REFERENCES users(id) ON DELETE SET NULL,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- User Groups (for scoping policies)
CREATE TABLE IF NOT EXISTS user_groups (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(255) NOT NULL,
  description TEXT,
  members UUID[] DEFAULT '{}', -- array of user_ids
  created_by UUID REFERENCES users(id) ON DELETE SET NULL,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Indexes
CREATE INDEX IF NOT EXISTS idx_discovery_strategies_status ON discovery_strategies(status);
CREATE INDEX IF NOT EXISTS idx_discovery_strategies_created_by ON discovery_strategies(created_by);
CREATE INDEX IF NOT EXISTS idx_data_discovery_processes_strategy ON data_discovery_processes(strategy_id);
CREATE INDEX IF NOT EXISTS idx_data_discovery_processes_status ON data_discovery_processes(status);
CREATE INDEX IF NOT EXISTS idx_on_device_classifications_device ON on_device_classifications(device_id);
CREATE INDEX IF NOT EXISTS idx_on_device_classifications_status ON on_device_classifications(status);
CREATE INDEX IF NOT EXISTS idx_marketplace_connectors_type ON marketplace_connectors(marketplace_type);
CREATE INDEX IF NOT EXISTS idx_marketplace_connectors_status ON marketplace_connectors(status);
CREATE INDEX IF NOT EXISTS idx_admin_units_parent ON admin_units(parent_unit_id);
CREATE INDEX IF NOT EXISTS idx_user_groups_members ON user_groups USING GIN(members);

