-- Enhanced Discovery Scans System Migration
-- This migration adds support for:
-- 1. Scan schedules (date range)
-- 2. Scan status (open/closed)
-- 3. Exclusion patterns
-- 4. Enhanced scan configuration
-- 5. Scan logs

-- Add columns to discovery_jobs if they don't exist
DO $$ 
BEGIN
    -- Scan title/name
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'discovery_jobs' AND column_name = 'name') THEN
        ALTER TABLE discovery_jobs ADD COLUMN name VARCHAR(200);
    END IF;

    -- Scanner name (for agent-based scans)
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'discovery_jobs' AND column_name = 'scanner_name') THEN
        ALTER TABLE discovery_jobs ADD COLUMN scanner_name VARCHAR(200);
    END IF;

    -- Status: 'open' or 'closed' (in addition to queued, running, etc.)
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'discovery_jobs' AND column_name = 'scan_status') THEN
        ALTER TABLE discovery_jobs ADD COLUMN scan_status VARCHAR(20) DEFAULT 'open'; -- 'open', 'closed'
    END IF;

    -- End date for scheduled scans
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'discovery_jobs' AND column_name = 'end_time') THEN
        ALTER TABLE discovery_jobs ADD COLUMN end_time TIMESTAMPTZ;
    END IF;

    -- Enhanced config_json for scan configuration
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'discovery_jobs' AND column_name = 'config_json') THEN
        ALTER TABLE discovery_jobs ADD COLUMN config_json JSONB DEFAULT '{}';
    END IF;

    -- Exclusion patterns (paths/extensions to exclude)
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'discovery_jobs' AND column_name = 'exclusion_patterns') THEN
        ALTER TABLE discovery_jobs ADD COLUMN exclusion_patterns TEXT[] DEFAULT '{}';
    END IF;

    -- Data types to scan (PII, PCI, etc.)
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'discovery_jobs' AND column_name = 'data_types') THEN
        ALTER TABLE discovery_jobs ADD COLUMN data_types TEXT[] DEFAULT '{}';
    END IF;

    -- File types to scan
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'discovery_jobs' AND column_name = 'file_type_filters') THEN
        ALTER TABLE discovery_jobs ADD COLUMN file_type_filters TEXT[];
    END IF;

    -- Schedule information (for scheduled scans)
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'discovery_jobs' AND column_name = 'schedule_from') THEN
        ALTER TABLE discovery_jobs ADD COLUMN schedule_from TIMESTAMPTZ;
    END IF;

    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'discovery_jobs' AND column_name = 'schedule_to') THEN
        ALTER TABLE discovery_jobs ADD COLUMN schedule_to TIMESTAMPTZ;
    END IF;

    -- Time range for data changes (scan files changed between dates)
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'discovery_jobs' AND column_name = 'data_date_from') THEN
        ALTER TABLE discovery_jobs ADD COLUMN data_date_from TIMESTAMPTZ;
    END IF;

    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'discovery_jobs' AND column_name = 'data_date_to') THEN
        ALTER TABLE discovery_jobs ADD COLUMN data_date_to TIMESTAMPTZ;
    END IF;

    -- Errors count (already exists as errors_count in some versions)
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'discovery_jobs' AND column_name = 'errors_count') THEN
        IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'discovery_jobs' AND column_name = 'errors') THEN
            ALTER TABLE discovery_jobs RENAME COLUMN errors TO errors_count;
        ELSE
            ALTER TABLE discovery_jobs ADD COLUMN errors_count INT DEFAULT 0;
        END IF;
    END IF;

    -- Files found count (alias for total_files)
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'discovery_jobs' AND column_name = 'files_found') THEN
        ALTER TABLE discovery_jobs ADD COLUMN files_found INT DEFAULT 0;
    END IF;

    -- Update files_found to match total_files if not set
    UPDATE discovery_jobs SET files_found = total_files WHERE files_found = 0 AND total_files > 0;
END $$;

-- Create scan_schedules table for recurring scans
CREATE TABLE IF NOT EXISTS scan_schedules (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(200) NOT NULL,
    source_id UUID REFERENCES sources(id) ON DELETE CASCADE,
    scan_type VARCHAR(20) NOT NULL, -- 'full', 'quick', 'targeted', 'scheduled'
    frequency VARCHAR(20) NOT NULL, -- 'once', 'daily', 'weekly', 'monthly', 'custom'
    start_date TIMESTAMPTZ NOT NULL,
    end_date TIMESTAMPTZ, -- Optional end date for schedule
    time_range_from TIME, -- Execution time window start (e.g., '02:00:00')
    time_range_to TIME, -- Execution time window end (e.g., '06:00:00')
    last_run TIMESTAMPTZ,
    next_run TIMESTAMPTZ,
    status VARCHAR(20) DEFAULT 'active', -- 'active', 'paused', 'completed', 'expired'
    config_json JSONB DEFAULT '{}',
    data_types TEXT[] DEFAULT '{}',
    file_type_filters TEXT[],
    exclusion_patterns TEXT[] DEFAULT '{}',
    created_by UUID REFERENCES users(id),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Create scan_logs table for real-time scan logging
CREATE TABLE IF NOT EXISTS scan_logs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    scan_id UUID REFERENCES discovery_jobs(id) ON DELETE CASCADE,
    level VARCHAR(20) NOT NULL, -- 'info', 'warn', 'error', 'debug'
    message TEXT NOT NULL,
    context JSONB,
    timestamp TIMESTAMPTZ DEFAULT NOW()
);

-- Create scan_results table for storing scan results metadata
CREATE TABLE IF NOT EXISTS scan_results (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    scan_id UUID REFERENCES discovery_jobs(id) ON DELETE CASCADE,
    document_id UUID REFERENCES documents(id) ON DELETE CASCADE,
    file_path TEXT,
    file_name VARCHAR(500),
    classification VARCHAR(50), -- 'Restricted', 'Confidential', etc.
    matches_count INT DEFAULT 0,
    verified BOOLEAN DEFAULT FALSE,
    remediation_status VARCHAR(50) DEFAULT 'Not Remediated', -- 'Not Remediated', 'Remediated', 'In Progress'
    remediation_message TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Create scan_reports table for generated reports
CREATE TABLE IF NOT EXISTS scan_reports (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    scan_id UUID REFERENCES discovery_jobs(id) ON DELETE CASCADE,
    report_name VARCHAR(200),
    report_type VARCHAR(50) DEFAULT 'standard', -- 'standard', 'custom', 'summary'
    summary TEXT, -- Dynamic summary text
    include_charts BOOLEAN DEFAULT TRUE,
    charts_config JSONB DEFAULT '[]', -- Array of chart configurations
    generated_by UUID REFERENCES users(id),
    generated_at TIMESTAMPTZ DEFAULT NOW(),
    file_path TEXT, -- Path to generated PDF/Excel file
    file_size BIGINT
);

-- Create indexes for performance
CREATE INDEX IF NOT EXISTS idx_discovery_jobs_scan_status ON discovery_jobs(scan_status);
CREATE INDEX IF NOT EXISTS idx_discovery_jobs_name ON discovery_jobs(name);
CREATE INDEX IF NOT EXISTS idx_discovery_jobs_scanner_name ON discovery_jobs(scanner_name);
CREATE INDEX IF NOT EXISTS idx_discovery_jobs_schedule_from ON discovery_jobs(schedule_from);
CREATE INDEX IF NOT EXISTS idx_discovery_jobs_schedule_to ON discovery_jobs(schedule_to);
CREATE INDEX IF NOT EXISTS idx_discovery_jobs_data_date_from ON discovery_jobs(data_date_from);
CREATE INDEX IF NOT EXISTS idx_discovery_jobs_data_date_to ON discovery_jobs(data_date_to);

CREATE INDEX IF NOT EXISTS idx_scan_schedules_source_id ON scan_schedules(source_id);
CREATE INDEX IF NOT EXISTS idx_scan_schedules_status ON scan_schedules(status);
CREATE INDEX IF NOT EXISTS idx_scan_schedules_next_run ON scan_schedules(next_run);

CREATE INDEX IF NOT EXISTS idx_scan_logs_scan_id ON scan_logs(scan_id);
CREATE INDEX IF NOT EXISTS idx_scan_logs_timestamp ON scan_logs(timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_scan_logs_level ON scan_logs(level);

CREATE INDEX IF NOT EXISTS idx_scan_results_scan_id ON scan_results(scan_id);
CREATE INDEX IF NOT EXISTS idx_scan_results_document_id ON scan_results(document_id);
CREATE INDEX IF NOT EXISTS idx_scan_results_classification ON scan_results(classification);
CREATE INDEX IF NOT EXISTS idx_scan_results_remediation_status ON scan_results(remediation_status);

CREATE INDEX IF NOT EXISTS idx_scan_reports_scan_id ON scan_reports(scan_id);
CREATE INDEX IF NOT EXISTS idx_scan_reports_generated_at ON scan_reports(generated_at DESC);

-- Add trigger for updated_at on scan_schedules
DROP TRIGGER IF EXISTS trg_scan_schedules_updated ON scan_schedules;
CREATE TRIGGER trg_scan_schedules_updated BEFORE UPDATE ON scan_schedules
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

-- Update existing discovery_jobs with default values
UPDATE discovery_jobs SET scan_status = 'open' WHERE scan_status IS NULL;
UPDATE discovery_jobs SET config_json = '{}' WHERE config_json IS NULL;
UPDATE discovery_jobs SET exclusion_patterns = '{}' WHERE exclusion_patterns IS NULL;
UPDATE discovery_jobs SET data_types = '{}' WHERE data_types IS NULL;

