-- Enhanced Discovery Sessions for 5-Step Discovery Process
-- 019_discovery_sessions.sql

CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Discovery Sessions Table
CREATE TABLE IF NOT EXISTS discovery_sessions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  
  -- Step 1: Objectives
  purpose VARCHAR(50), -- 'compliance_audit', 'litigation_preparation', 'data_inventory', 'security_assessment', 'data_cleanup', 'custom'
  business_problem TEXT,
  desired_insights TEXT[], -- ['pii_location', 'sensitive_data_distribution', 'compliance_gaps', etc.]
  compliance_requirements TEXT[], -- ['gdpr', 'ccpa', 'hipaa', 'pci_dss', 'sox', 'custom']
  timeline_start DATE,
  timeline_end DATE,
  stakeholders UUID[], -- Array of user IDs
  
  -- Step 2: Scope
  included_sources UUID[], -- Array of source IDs
  scope_refinement JSONB, -- {locations: [], data_types: [], time_period: {}}
  exclusions JSONB, -- {file_extensions: [], paths: [], size_limit_mb: null}
  estimated_files INT,
  estimated_size_gb NUMERIC(10, 2),
  estimated_scan_time VARCHAR(50),
  coverage_percentage NUMERIC(5, 2),
  
  -- Step 3: Approach
  approach VARCHAR(50), -- 'manual', 'automated', 'hybrid'
  approach_config JSONB, -- {classification_engine, models, confidence_threshold, ocr_enabled, etc.}
  estimated_accuracy NUMERIC(5, 2),
  estimated_false_positives NUMERIC(5, 2),
  
  -- Step 4: Preparation
  preparation_config JSONB, -- {deduplication, normalization, metadata_extraction, pre_filtering}
  quality_check_results JSONB, -- {ocr_accuracy, classification_confidence}
  ready_to_run BOOLEAN DEFAULT FALSE,
  
  -- Step 5: Execution
  job_id UUID REFERENCES discovery_jobs(id) ON DELETE SET NULL,
  execution_status VARCHAR(50) DEFAULT 'pending', -- 'pending', 'running', 'paused', 'completed', 'failed', 'cancelled'
  execution_started_at TIMESTAMPTZ,
  execution_completed_at TIMESTAMPTZ,
  execution_results JSONB, -- {total_files, total_size_gb, sensitive_files, classification_breakdown, top_sources}
  
  -- Session Metadata
  current_step INT DEFAULT 1, -- 1-5
  status VARCHAR(50) DEFAULT 'in_progress', -- 'in_progress', 'completed', 'abandoned'
  recommended_approach VARCHAR(50),
  estimated_duration VARCHAR(50),
  created_by UUID REFERENCES users(id) ON DELETE SET NULL,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Discovery Session Progress Tracking
CREATE TABLE IF NOT EXISTS discovery_session_progress (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  session_id UUID REFERENCES discovery_sessions(id) ON DELETE CASCADE,
  job_id UUID REFERENCES discovery_jobs(id) ON DELETE SET NULL,
  
  -- Phase Tracking
  current_phase VARCHAR(50), -- 'source_connection', 'file_enumeration', 'ocr_processing', 'classification', 'results_aggregation'
  phase_progress NUMERIC(5, 2) DEFAULT 0, -- 0-100
  
  -- Overall Progress
  files_processed INT DEFAULT 0,
  total_files INT DEFAULT 0,
  data_scanned_gb NUMERIC(10, 2) DEFAULT 0,
  total_data_gb NUMERIC(10, 2) DEFAULT 0,
  sensitive_detected INT DEFAULT 0,
  sensitive_breakdown JSONB, -- {high: 0, medium: 0, low: 0}
  
  -- Performance Metrics
  current_speed_files_per_min INT DEFAULT 0,
  eta_seconds INT,
  
  -- Worker Status
  worker_status JSONB, -- {ingest_workers: {active: 0, total: 0}, ocr_workers: {}, classifier_workers: {}}
  
  -- Issues
  issues JSONB[], -- Array of {type, severity, message, timestamp}
  
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Indexes
CREATE INDEX IF NOT EXISTS idx_discovery_sessions_created_by ON discovery_sessions(created_by);
CREATE INDEX IF NOT EXISTS idx_discovery_sessions_status ON discovery_sessions(status, current_step);
CREATE INDEX IF NOT EXISTS idx_discovery_sessions_job_id ON discovery_sessions(job_id);
CREATE INDEX IF NOT EXISTS idx_discovery_session_progress_session ON discovery_session_progress(session_id);
CREATE INDEX IF NOT EXISTS idx_discovery_session_progress_job ON discovery_session_progress(job_id);

-- Triggers
CREATE OR REPLACE FUNCTION update_discovery_sessions_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_discovery_sessions_updated_at
  BEFORE UPDATE ON discovery_sessions
  FOR EACH ROW
  EXECUTE FUNCTION update_discovery_sessions_updated_at();

CREATE TRIGGER trigger_discovery_session_progress_updated_at
  BEFORE UPDATE ON discovery_session_progress
  FOR EACH ROW
  EXECUTE FUNCTION update_discovery_sessions_updated_at();

