-- Audit Trail for Sources and Discovery Jobs
-- 021_audit_trail.sql

CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Audit Trail Table (add columns if table exists with different schema)
DO $$
BEGIN
  -- Add entity_type if it doesn't exist (map to resource_type)
  IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'audit_logs' AND column_name = 'entity_type') THEN
    ALTER TABLE audit_logs ADD COLUMN entity_type VARCHAR(50);
    -- Migrate existing data: map resource_type to entity_type
    UPDATE audit_logs SET entity_type = resource_type WHERE resource_type IS NOT NULL;
  END IF;
  
  -- Add entity_id if it doesn't exist (map to resource_id)
  IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'audit_logs' AND column_name = 'entity_id') THEN
    ALTER TABLE audit_logs ADD COLUMN entity_id UUID;
    -- Migrate existing data: map resource_id to entity_id
    UPDATE audit_logs SET entity_id = resource_id WHERE resource_id IS NOT NULL;
  END IF;
  
  -- Add user_name if it doesn't exist
  IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'audit_logs' AND column_name = 'user_name') THEN
    ALTER TABLE audit_logs ADD COLUMN user_name VARCHAR(255);
  END IF;
  
  -- Add changes JSONB if it doesn't exist
  IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'audit_logs' AND column_name = 'changes') THEN
    ALTER TABLE audit_logs ADD COLUMN changes JSONB;
  END IF;
  
  -- Add metadata JSONB if it doesn't exist (map from details)
  IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'audit_logs' AND column_name = 'metadata') THEN
    ALTER TABLE audit_logs ADD COLUMN metadata JSONB;
    -- Migrate existing data: map details to metadata
    UPDATE audit_logs SET metadata = details WHERE details IS NOT NULL;
  END IF;
  
  -- Ensure created_at exists (map from timestamp)
  IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'audit_logs' AND column_name = 'created_at') THEN
    ALTER TABLE audit_logs ADD COLUMN created_at TIMESTAMPTZ DEFAULT NOW();
    -- Migrate existing data: map timestamp to created_at
    UPDATE audit_logs SET created_at = timestamp WHERE timestamp IS NOT NULL;
  END IF;
END $$;

-- Create indexes for performance (if they don't exist)
CREATE INDEX IF NOT EXISTS idx_audit_logs_entity ON audit_logs(entity_type, entity_id) WHERE entity_type IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_audit_logs_user ON audit_logs(user_id) WHERE user_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_audit_logs_created_at ON audit_logs(created_at DESC) WHERE created_at IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_audit_logs_action ON audit_logs(action) WHERE action IS NOT NULL;

-- Add file type breakdown to documents metadata
ALTER TABLE documents ADD COLUMN IF NOT EXISTS file_category VARCHAR(50); -- 'document', 'spreadsheet', 'presentation', 'email', 'database', 'image', 'archive', 'other'
ALTER TABLE documents ADD COLUMN IF NOT EXISTS mime_type VARCHAR(100);
ALTER TABLE documents ADD COLUMN IF NOT EXISTS page_count INT;
ALTER TABLE documents ADD COLUMN IF NOT EXISTS word_count INT;
ALTER TABLE documents ADD COLUMN IF NOT EXISTS language VARCHAR(10);

-- Add database-specific fields for database sources
ALTER TABLE sources ADD COLUMN IF NOT EXISTS database_config JSONB; -- {host, port, database, tables: [], schemas: []}

-- Add scan configuration details to discovery_jobs
ALTER TABLE discovery_jobs ADD COLUMN IF NOT EXISTS scan_config JSONB; -- {tables: [], columns: [], filters: {}, percentage: null}
ALTER TABLE discovery_jobs ADD COLUMN IF NOT EXISTS file_type_filters TEXT[]; -- ['pdf', 'docx', 'xlsx', etc.]
ALTER TABLE discovery_jobs ADD COLUMN IF NOT EXISTS file_size_limit BIGINT; -- Max file size in bytes
ALTER TABLE discovery_jobs ADD COLUMN IF NOT EXISTS path_filters TEXT[]; -- Path patterns to include/exclude

-- Create view for source statistics
CREATE OR REPLACE VIEW source_statistics AS
SELECT 
  s.id,
  s.name,
  s.provider_type,
  s.status,
  COUNT(DISTINCT d.id) as total_documents,
  COUNT(DISTINCT CASE WHEN d.file_category = 'document' THEN d.id END) as document_count,
  COUNT(DISTINCT CASE WHEN d.file_category = 'spreadsheet' THEN d.id END) as spreadsheet_count,
  COUNT(DISTINCT CASE WHEN d.file_category = 'presentation' THEN d.id END) as presentation_count,
  COUNT(DISTINCT CASE WHEN d.file_category = 'email' THEN d.id END) as email_count,
  COUNT(DISTINCT CASE WHEN d.file_category = 'database' THEN d.id END) as database_count,
  COUNT(DISTINCT CASE WHEN d.file_category = 'image' THEN d.id END) as image_count,
  COUNT(DISTINCT CASE WHEN d.file_category = 'archive' THEN d.id END) as archive_count,
  SUM(d.file_size) as total_size_bytes,
  COUNT(DISTINCT dj.id) as total_scans,
  COUNT(DISTINCT CASE WHEN dj.status = 'completed' THEN dj.id END) as completed_scans,
  COUNT(DISTINCT CASE WHEN dj.status = 'running' THEN dj.id END) as running_scans,
  MAX(dj.completed_at) as last_scan_at,
  MAX(a.created_at) as last_activity_at
FROM sources s
LEFT JOIN documents d ON d.source_id = s.id
LEFT JOIN discovery_jobs dj ON dj.source_id = s.id
LEFT JOIN audit_logs a ON a.entity_type = 'source' AND a.entity_id = s.id
GROUP BY s.id, s.name, s.provider_type, s.status;

-- Create view for job statistics
CREATE OR REPLACE VIEW job_statistics AS
SELECT 
  dj.id,
  dj.source_id,
  dj.scan_type,
  dj.status,
  dj.total_files,
  dj.processed_files,
  dj.sensitive_detected,
  dj.started_at,
  dj.completed_at,
  dj.created_by,
  COUNT(DISTINCT d.id) as documents_found,
  COUNT(DISTINCT CASE WHEN d.file_category = 'document' THEN d.id END) as document_count,
  COUNT(DISTINCT CASE WHEN d.file_category = 'spreadsheet' THEN d.id END) as spreadsheet_count,
  COUNT(DISTINCT CASE WHEN d.file_category = 'email' THEN d.id END) as email_count,
  COUNT(DISTINCT CASE WHEN d.file_category = 'database' THEN d.id END) as database_count,
  SUM(d.file_size) as total_size_bytes,
  COUNT(DISTINCT dd.id) as total_detections,
  COUNT(DISTINCT CASE WHEN dd.severity = 'high' THEN dd.id END) as high_severity_detections,
  COUNT(DISTINCT CASE WHEN dd.severity = 'medium' THEN dd.id END) as medium_severity_detections,
  COUNT(DISTINCT CASE WHEN dd.severity = 'low' THEN dd.id END) as low_severity_detections
FROM discovery_jobs dj
LEFT JOIN discovery_detections dd ON dd.job_id = dj.id
LEFT JOIN documents d ON d.id = dd.document_id
GROUP BY dj.id, dj.source_id, dj.scan_type, dj.status, dj.total_files, dj.processed_files, 
         dj.sensitive_detected, dj.started_at, dj.completed_at, dj.created_by;

-- Function to automatically log audit events
CREATE OR REPLACE FUNCTION log_audit_event(
  p_entity_type VARCHAR(50),
  p_entity_id UUID,
  p_action VARCHAR(50),
  p_user_id UUID DEFAULT NULL,
  p_user_email VARCHAR(255) DEFAULT NULL,
  p_user_name VARCHAR(255) DEFAULT NULL,
  p_changes JSONB DEFAULT NULL,
  p_metadata JSONB DEFAULT NULL
) RETURNS UUID AS $$
DECLARE
  v_audit_id UUID;
BEGIN
  INSERT INTO audit_logs (
    entity_type, entity_id, action, user_id, user_email, user_name, changes, metadata
  ) VALUES (
    p_entity_type, p_entity_id, p_action, p_user_id, p_user_email, p_user_name, p_changes, p_metadata
  ) RETURNING id INTO v_audit_id;
  RETURN v_audit_id;
END;
$$ LANGUAGE plpgsql;

