-- Classification Tables Migration
-- 004_classification.sql

CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Classification Rules (regex, keyword, pattern-based detection)
CREATE TABLE IF NOT EXISTS classification_rules (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(255) NOT NULL,
  type VARCHAR(50) NOT NULL, -- 'regex', 'keyword', 'pattern'
  pattern TEXT, -- regex pattern or keyword list (JSON array)
  detection_type VARCHAR(50) NOT NULL, -- 'pii.email', 'pci.pan', etc.
  sensitivity VARCHAR(20) NOT NULL DEFAULT 'high',
  confidence FLOAT DEFAULT 1.0,
  priority INT DEFAULT 0, -- higher = run first
  active BOOLEAN DEFAULT TRUE,
  created_by UUID REFERENCES users(id) ON DELETE SET NULL,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Structured Fields (database columns with detected PII/PCI/Health data)
CREATE TABLE IF NOT EXISTS structured_fields (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  source_id UUID REFERENCES sources(id) ON DELETE CASCADE,
  database_name VARCHAR(255),
  schema_name VARCHAR(255),
  table_name VARCHAR(255) NOT NULL,
  column_name VARCHAR(255) NOT NULL,
  data_type VARCHAR(50),
  detected_type VARCHAR(50), -- 'pii.email', 'pci.pan', 'health.mrn', etc.
  sensitivity VARCHAR(20) DEFAULT 'public', -- 'public', 'low', 'medium', 'high'
  confidence FLOAT DEFAULT 0.0,
  sample_values_hash TEXT[], -- array of SHA256 hashes (not actual values)
  labels TEXT[] DEFAULT '{}',
  classification_method VARCHAR(50), -- 'rule', 'ml', 'hybrid'
  rule_id UUID REFERENCES classification_rules(id) ON DELETE SET NULL,
  model_version VARCHAR(50),
  classified_at TIMESTAMPTZ DEFAULT NOW(),
  reviewed_by UUID REFERENCES users(id) ON DELETE SET NULL,
  reviewed_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- ML Models (ONNX models for NER, text classification, etc.)
CREATE TABLE IF NOT EXISTS ml_models (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  task VARCHAR(50) NOT NULL, -- 'ner', 'text_classification', 'sentence_similarity'
  name VARCHAR(100) NOT NULL, -- 'arabic-bert', 'xlm-roberta-large', 'deberta-v3'
  version VARCHAR(50),
  path_reference VARCHAR(500), -- /models/arabic-bert-v1.onnx
  active BOOLEAN DEFAULT TRUE,
  params JSONB DEFAULT '{}', -- {max_length: 512, batch_size: 32, threshold: 0.75}
  performance_metrics JSONB DEFAULT '{}', -- {f1: 0.92, precision: 0.94, recall: 0.90}
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Labels (hierarchical labels for classification)
CREATE TABLE IF NOT EXISTS labels (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(255) UNIQUE NOT NULL,
  color VARCHAR(7), -- hex color
  description TEXT,
  parent_label_id UUID REFERENCES labels(id) ON DELETE SET NULL,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Label Policies (access restrictions, protection levels, retention)
CREATE TABLE IF NOT EXISTS label_policies (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  label_id UUID REFERENCES labels(id) ON DELETE CASCADE,
  access_restriction VARCHAR(50) DEFAULT 'none', -- 'none', 'need_to_know', 'approval_required'
  protection_level VARCHAR(50) DEFAULT 'standard', -- 'standard', 'watermark', 'no_export'
  sharing_allowed BOOLEAN DEFAULT TRUE,
  external_sharing_allowed BOOLEAN DEFAULT FALSE,
  retention_days INT,
  auto_enforce BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Indexes for performance
CREATE INDEX IF NOT EXISTS idx_structured_fields_source ON structured_fields(source_id);
CREATE INDEX IF NOT EXISTS idx_structured_fields_detected_type ON structured_fields(detected_type);
CREATE INDEX IF NOT EXISTS idx_structured_fields_sensitivity ON structured_fields(sensitivity);
CREATE INDEX IF NOT EXISTS idx_structured_fields_classified_at ON structured_fields(classified_at);
CREATE INDEX IF NOT EXISTS idx_classification_rules_active ON classification_rules(active);
CREATE INDEX IF NOT EXISTS idx_classification_rules_priority ON classification_rules(priority DESC);
CREATE INDEX IF NOT EXISTS idx_ml_models_task ON ml_models(task);
CREATE INDEX IF NOT EXISTS idx_ml_models_active ON ml_models(active);
CREATE INDEX IF NOT EXISTS idx_labels_name ON labels(name);
CREATE INDEX IF NOT EXISTS idx_label_policies_label ON label_policies(label_id);

-- Add classification fields to documents table (if not exists)
DO $$ 
BEGIN
  IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'documents' AND column_name = 'sensitivity') THEN
    ALTER TABLE documents ADD COLUMN sensitivity VARCHAR(20) DEFAULT 'public';
  END IF;
  IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'documents' AND column_name = 'labels') THEN
    ALTER TABLE documents ADD COLUMN labels TEXT[] DEFAULT '{}';
  END IF;
  IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'documents' AND column_name = 'classified_at') THEN
    ALTER TABLE documents ADD COLUMN classified_at TIMESTAMPTZ;
  END IF;
  IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'documents' AND column_name = 'reviewed_by') THEN
    ALTER TABLE documents ADD COLUMN reviewed_by UUID REFERENCES users(id) ON DELETE SET NULL;
  END IF;
  IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'documents' AND column_name = 'reviewed_at') THEN
    ALTER TABLE documents ADD COLUMN reviewed_at TIMESTAMPTZ;
  END IF;
END $$;

-- Add indexes for documents classification fields
CREATE INDEX IF NOT EXISTS idx_documents_sensitivity ON documents(sensitivity);
CREATE INDEX IF NOT EXISTS idx_documents_labels ON documents USING GIN(labels);
CREATE INDEX IF NOT EXISTS idx_documents_classified_at ON documents(classified_at);

