-- Data Classification & Protection System Migration
-- 027_data_classification_protection.sql
-- Comprehensive system similar to Microsoft Purview Information Protection

CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- ============================================================================
-- 1. CLASSIFICATION LABELS (4-Tier System)
-- ============================================================================

-- Enhanced Labels table with 4-tier classification support
CREATE TABLE IF NOT EXISTS classification_labels (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(255) NOT NULL UNIQUE,
  display_name VARCHAR(255) NOT NULL,
  level INT NOT NULL CHECK (level BETWEEN 1 AND 4), -- 1=Top Secret, 2=Secret, 3=Restricted, 4=Public
  color VARCHAR(7) NOT NULL, -- hex color (e.g., #D13438 for Top Secret red)
  icon VARCHAR(50), -- icon identifier (e.g., 'shield-lock', 'lock', 'eye', 'globe')
  description TEXT,
  parent_label_id UUID REFERENCES classification_labels(id) ON DELETE SET NULL,
  is_active BOOLEAN DEFAULT true,
  -- Scope & Application settings
  apply_to JSONB DEFAULT '[]'::jsonb, -- ['Files & Documents', 'Emails', 'SharePoint Sites', etc.]
  auto_apply_rules JSONB DEFAULT '{}'::jsonb, -- {sensitive_data_types: [], file_location: '', keywords: ''}
  departments_access JSONB DEFAULT '{"all": true, "specific": []}'::jsonb, -- {all: true/false, specific: ['HR', 'Finance']}
  visual_marking JSONB DEFAULT '{}'::jsonb, -- {header_text: '', footer_text: ''}
  created_date TIMESTAMP DEFAULT NOW(),
  modified_date TIMESTAMP DEFAULT NOW(),
  created_by UUID REFERENCES users(id) ON DELETE SET NULL
);

-- Label hierarchy support (for modern scheme with groups and sublabels)
CREATE TABLE IF NOT EXISTS label_groups (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(255) NOT NULL UNIQUE,
  display_name VARCHAR(255) NOT NULL,
  description TEXT,
  is_active BOOLEAN DEFAULT true,
  created_date TIMESTAMP DEFAULT NOW(),
  modified_date TIMESTAMP DEFAULT NOW()
);

-- Link labels to groups (modern scheme support)
CREATE TABLE IF NOT EXISTS label_group_members (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  group_id UUID NOT NULL REFERENCES label_groups(id) ON DELETE CASCADE,
  label_id UUID NOT NULL REFERENCES classification_labels(id) ON DELETE CASCADE,
  sort_order INT DEFAULT 0,
  UNIQUE(group_id, label_id)
);

-- ============================================================================
-- 2. SENSITIVE DATA DETECTION
-- ============================================================================

-- Enhanced Sensitive Data Types (extends existing sensitive_info_types)
CREATE TABLE IF NOT EXISTS sensitive_data_patterns (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(255) NOT NULL UNIQUE,
  display_name VARCHAR(255) NOT NULL,
  pattern_regex TEXT,
  description TEXT,
  severity_level VARCHAR(20) NOT NULL DEFAULT 'medium', -- 'low', 'medium', 'high'
  confidence_threshold DECIMAL(3,2) DEFAULT 0.75,
  category VARCHAR(50), -- 'pii', 'pci', 'financial', 'health', 'custom'
  is_active BOOLEAN DEFAULT true,
  created_date TIMESTAMP DEFAULT NOW(),
  updated_date TIMESTAMP DEFAULT NOW()
);

-- Classified Files tracking
CREATE TABLE IF NOT EXISTS classified_files (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  file_path TEXT NOT NULL,
  file_name VARCHAR(255),
  file_size BIGINT,
  file_type VARCHAR(50),
  classification_label_id UUID REFERENCES classification_labels(id) ON DELETE SET NULL,
  detected_patterns JSONB DEFAULT '[]'::jsonb, -- Array of detected sensitive data types
  confidence_score DECIMAL(3,2),
  classified_by_user_id UUID REFERENCES users(id) ON DELETE SET NULL,
  classification_date TIMESTAMP DEFAULT NOW(),
  last_modified_date TIMESTAMP,
  host_name VARCHAR(255),
  ip_address VARCHAR(45),
  location_type VARCHAR(50), -- 'SharePoint', 'OneDrive', 'Local', 'Email', 'Database'
  file_hash VARCHAR(64), -- SHA-256 hash for deduplication
  metadata JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

-- ============================================================================
-- 3. PROTECTION POLICIES
-- ============================================================================

-- Protection Policies (comprehensive policy management)
CREATE TABLE IF NOT EXISTS protection_policies (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  policy_name VARCHAR(255) NOT NULL,
  description TEXT,
  label_id UUID REFERENCES classification_labels(id) ON DELETE CASCADE,
  policy_type VARCHAR(50) NOT NULL, -- 'domain', 'event', 'print', 'visual_marking', 'encryption', 'access_control'
  conditions_json JSONB DEFAULT '{}'::jsonb, -- IF conditions (label, action, user, location, etc.)
  actions_json JSONB DEFAULT '{}'::jsonb, -- THEN actions (mask, watermark, quarantine, block, delete, encrypt, notify)
  priority INT DEFAULT 0, -- Higher priority = evaluated first
  is_enabled BOOLEAN DEFAULT true,
  created_by UUID REFERENCES users(id) ON DELETE SET NULL,
  created_date TIMESTAMP DEFAULT NOW(),
  updated_date TIMESTAMP DEFAULT NOW()
);

-- Policy Actions (detailed action configuration)
CREATE TABLE IF NOT EXISTS policy_actions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  policy_id UUID NOT NULL REFERENCES protection_policies(id) ON DELETE CASCADE,
  action_type VARCHAR(50) NOT NULL, -- 'mask', 'watermark', 'quarantine', 'block', 'delete', 'encrypt', 'notify'
  action_config_json JSONB DEFAULT '{}'::jsonb, -- Specific config for each action type
  apply_to_file_types TEXT[] DEFAULT '{}', -- e.g., ['pdf', 'docx', 'xlsx']
  apply_to_locations TEXT[] DEFAULT '{}', -- e.g., ['SharePoint', 'OneDrive', 'Local']
  created_date TIMESTAMP DEFAULT NOW()
);

-- ============================================================================
-- 4. CLASSIFICATION EVENTS MONITORING
-- ============================================================================

-- Classification Events (real-time event tracking)
CREATE TABLE IF NOT EXISTS classification_events (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  file_id UUID REFERENCES classified_files(id) ON DELETE SET NULL,
  file_path TEXT,
  file_name VARCHAR(255),
  event_type VARCHAR(50) NOT NULL, -- 'created', 'modified', 'accessed', 'shared', 'downloaded', 'deleted'
  user_id UUID REFERENCES users(id) ON DELETE SET NULL,
  user_email VARCHAR(255),
  ip_address VARCHAR(45),
  host_name VARCHAR(255),
  old_classification_id UUID REFERENCES classification_labels(id) ON DELETE SET NULL,
  new_classification_id UUID REFERENCES classification_labels(id) ON DELETE SET NULL,
  event_date TIMESTAMP DEFAULT NOW(),
  is_alert BOOLEAN DEFAULT false,
  alert_reason TEXT,
  status VARCHAR(20) DEFAULT 'pending', -- 'pending', 'cleared', 'investigating', 'resolved'
  metadata JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMP DEFAULT NOW()
);

-- ============================================================================
-- 5. DOWNGRADE LABEL REQUESTS
-- ============================================================================

-- Downgrade Requests (approval workflow)
CREATE TABLE IF NOT EXISTS downgrade_requests (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  request_id UUID UNIQUE DEFAULT gen_random_uuid(), -- Public UUID for tracking
  file_id UUID REFERENCES classified_files(id) ON DELETE SET NULL,
  file_location TEXT,
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  current_label_id UUID NOT NULL REFERENCES classification_labels(id) ON DELETE RESTRICT,
  requested_label_id UUID NOT NULL REFERENCES classification_labels(id) ON DELETE RESTRICT,
  justification TEXT NOT NULL,
  request_date TIMESTAMP DEFAULT NOW(),
  status VARCHAR(20) DEFAULT 'pending', -- 'pending', 'approved', 'rejected', 'auto_rejected'
  reviewed_by_id UUID REFERENCES users(id) ON DELETE SET NULL,
  review_date TIMESTAMP,
  reviewer_comment TEXT,
  auto_rejected_reason TEXT,
  metadata JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

-- ============================================================================
-- 6. SETTINGS & CONFIGURATION
-- ============================================================================

-- Departments (for access management)
CREATE TABLE IF NOT EXISTS departments (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(255) NOT NULL,
  parent_dept_id UUID REFERENCES departments(id) ON DELETE SET NULL,
  label_rules_json JSONB DEFAULT '{}'::jsonb, -- Department-specific classification rules
  created_date TIMESTAMP DEFAULT NOW(),
  updated_date TIMESTAMP DEFAULT NOW()
);

-- Link users to departments (if not already in users table)
ALTER TABLE users ADD COLUMN IF NOT EXISTS department_id UUID REFERENCES departments(id) ON DELETE SET NULL;

-- System Settings (enhanced settings management)
CREATE TABLE IF NOT EXISTS system_settings (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  category VARCHAR(50) NOT NULL, -- 'access_management', 'notifications', 'integration', 'language'
  key VARCHAR(100) NOT NULL,
  value JSONB NOT NULL,
  description TEXT,
  updated_by UUID REFERENCES users(id) ON DELETE SET NULL,
  updated_date TIMESTAMP DEFAULT NOW(),
  UNIQUE(category, key)
);

-- Notification Configuration
CREATE TABLE IF NOT EXISTS notification_config (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  notification_type VARCHAR(50) NOT NULL, -- 'sensitive_data_detected', 'policy_violated', 'downgrade_requested', 'unusual_activity'
  recipients JSONB DEFAULT '[]'::jsonb, -- Array of user emails or roles
  email_template TEXT,
  frequency VARCHAR(20) DEFAULT 'realtime', -- 'realtime', 'daily_digest', 'weekly_summary'
  channels TEXT[] DEFAULT '{"email"}'::text[], -- 'email', 'slack', 'teams'
  is_enabled BOOLEAN DEFAULT true,
  created_date TIMESTAMP DEFAULT NOW(),
  updated_date TIMESTAMP DEFAULT NOW()
);

-- Integration Settings
CREATE TABLE IF NOT EXISTS integration_settings (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  integration_type VARCHAR(50) NOT NULL, -- 'microsoft365', 'google_workspace', 'slack', 'siem'
  config_json JSONB NOT NULL, -- API keys, endpoints, etc.
  is_enabled BOOLEAN DEFAULT true,
  created_date TIMESTAMP DEFAULT NOW(),
  updated_date TIMESTAMP DEFAULT NOW(),
  UNIQUE(integration_type)
);

-- ============================================================================
-- 7. AUDIT LOG (Enhanced)
-- ============================================================================

-- Audit Log for classification system
CREATE TABLE IF NOT EXISTS classification_audit_log (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES users(id) ON DELETE SET NULL,
  action VARCHAR(100) NOT NULL, -- 'label_created', 'label_applied', 'policy_created', 'downgrade_approved', etc.
  entity_type VARCHAR(50), -- 'label', 'policy', 'file', 'downgrade_request'
  entity_id UUID,
  changes JSONB DEFAULT '{}'::jsonb, -- Before/after values
  ip_address VARCHAR(45),
  host_name VARCHAR(255),
  timestamp TIMESTAMP DEFAULT NOW()
);

-- ============================================================================
-- INDEXES FOR PERFORMANCE
-- ============================================================================

-- Classification Labels
CREATE INDEX IF NOT EXISTS idx_classification_labels_level ON classification_labels(level);
CREATE INDEX IF NOT EXISTS idx_classification_labels_parent ON classification_labels(parent_label_id);
CREATE INDEX IF NOT EXISTS idx_classification_labels_active ON classification_labels(is_active);

-- Classified Files
CREATE INDEX IF NOT EXISTS idx_classified_files_label ON classified_files(classification_label_id);
CREATE INDEX IF NOT EXISTS idx_classified_files_path ON classified_files(file_path);
CREATE INDEX IF NOT EXISTS idx_classified_files_hash ON classified_files(file_hash);
CREATE INDEX IF NOT EXISTS idx_classified_files_location ON classified_files(location_type);
CREATE INDEX IF NOT EXISTS idx_classified_files_classification_date ON classified_files(classification_date);

-- Protection Policies
CREATE INDEX IF NOT EXISTS idx_protection_policies_label ON protection_policies(label_id);
CREATE INDEX IF NOT EXISTS idx_protection_policies_type ON protection_policies(policy_type);
CREATE INDEX IF NOT EXISTS idx_protection_policies_enabled ON protection_policies(is_enabled);
CREATE INDEX IF NOT EXISTS idx_protection_policies_priority ON protection_policies(priority DESC);

-- Classification Events
CREATE INDEX IF NOT EXISTS idx_classification_events_file ON classification_events(file_id);
CREATE INDEX IF NOT EXISTS idx_classification_events_user ON classification_events(user_id);
CREATE INDEX IF NOT EXISTS idx_classification_events_date ON classification_events(event_date DESC);
CREATE INDEX IF NOT EXISTS idx_classification_events_type ON classification_events(event_type);
CREATE INDEX IF NOT EXISTS idx_classification_events_alert ON classification_events(is_alert) WHERE is_alert = true;
CREATE INDEX IF NOT EXISTS idx_classification_events_status ON classification_events(status);

-- Downgrade Requests
CREATE INDEX IF NOT EXISTS idx_downgrade_requests_user ON downgrade_requests(user_id);
CREATE INDEX IF NOT EXISTS idx_downgrade_requests_status ON downgrade_requests(status);
CREATE INDEX IF NOT EXISTS idx_downgrade_requests_request_date ON downgrade_requests(request_date DESC);
CREATE INDEX IF NOT EXISTS idx_downgrade_requests_current_label ON downgrade_requests(current_label_id);
CREATE INDEX IF NOT EXISTS idx_downgrade_requests_requested_label ON downgrade_requests(requested_label_id);

-- System Settings
CREATE INDEX IF NOT EXISTS idx_system_settings_category ON system_settings(category);
CREATE INDEX IF NOT EXISTS idx_classification_audit_log_user ON classification_audit_log(user_id);
CREATE INDEX IF NOT EXISTS idx_classification_audit_log_timestamp ON classification_audit_log(timestamp DESC);

-- ============================================================================
-- SEED DATA: Default 4-Tier Classification Labels
-- ============================================================================

-- Insert default 4-tier labels if they don't exist
-- Note: Level 1 = Top Secret (Highest), Level 4 = Public (Lowest) - as per requirements
INSERT INTO classification_labels (name, display_name, level, color, icon, description)
VALUES
  ('top_secret', 'Top Secret', 1, '#D13438', 'shield-lock', 'Highest sensitivity - Restricted to authorized personnel only'),
  ('secret', 'Secret', 2, '#FF8C00', 'lock', 'Highly sensitive - Internal use with authorization'),
  ('restricted', 'Restricted', 3, '#FFB900', 'eye', 'Sensitive - Internal use within organization'),
  ('public', 'Public', 4, '#107C10', 'globe', 'Low sensitivity - Can be shared publicly')
ON CONFLICT (name) DO UPDATE SET level = EXCLUDED.level, color = EXCLUDED.color;

-- Insert default sensitive data patterns if they don't exist
INSERT INTO sensitive_data_patterns (name, display_name, pattern_regex, description, severity_level, category)
VALUES
  ('credit_card', 'Credit Card Number', '\\b\\d{4}[\\s-]?\\d{4}[\\s-]?\\d{4}[\\s-]?\\d{4}\\b', 'Credit card numbers (16 digits)', 'high', 'pci'),
  ('ssn', 'Social Security Number', '\\b\\d{3}-\\d{2}-\\d{4}\\b', 'US Social Security Numbers', 'high', 'pii'),
  ('email', 'Email Address', '\\b[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Z|a-z]{2,}\\b', 'Email addresses', 'medium', 'pii'),
  ('phone', 'Phone Number', '\\b\\d{3}[-.]?\\d{3}[-.]?\\d{4}\\b', 'Phone numbers', 'medium', 'pii')
ON CONFLICT (name) DO NOTHING;

COMMENT ON TABLE classification_labels IS '4-tier classification system: Top Secret (1), Secret (2), Restricted (3), Public (4)';
COMMENT ON TABLE protection_policies IS 'Protection policies for enforcing data protection rules';
COMMENT ON TABLE classification_events IS 'Real-time tracking of classification-related events';
COMMENT ON TABLE downgrade_requests IS 'Approval workflow for label downgrade requests';

