-- Protection Tables Migration
-- 033_protection_tables.sql
-- Tables for Masking Rules, Encryption Keys, Watermark Logs, and other protection features

CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- ============================================================================
-- 1. MASKING RULES
-- ============================================================================

CREATE TABLE IF NOT EXISTS masking_rules (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    rule_name VARCHAR(255) NOT NULL,
    data_type VARCHAR(100) NOT NULL, -- 'credit_card', 'ssn', 'email', 'phone', 'name', 'iban', 'custom'
    masking_pattern VARCHAR(255) NOT NULL, -- e.g., '#### **** **** ####', '••••• ####'
    description TEXT,
    examples_json JSONB DEFAULT '{}'::jsonb, -- before/after examples
    is_default BOOLEAN DEFAULT false,
    is_active BOOLEAN DEFAULT true,
    created_by INTEGER REFERENCES users(id) ON DELETE SET NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_masking_rules_data_type ON masking_rules(data_type);
CREATE INDEX IF NOT EXISTS idx_masking_rules_is_active ON masking_rules(is_active);
CREATE INDEX IF NOT EXISTS idx_masking_rules_is_default ON masking_rules(is_default);

-- Insert default masking rules
INSERT INTO masking_rules (rule_name, data_type, masking_pattern, description, examples_json, is_default, is_active) VALUES
('Credit Card - Show Last 4', 'credit_card', '#### **** **** ####', 'Show first 4 and last 4 digits of credit card', '{"before": "4090 5532 1123 9911", "after": "4090 **** **** 9911"}'::jsonb, true, true),
('SSN - Show Last 4', 'ssn', '•••-••-####', 'Show last 4 digits of SSN', '{"before": "123-45-6789", "after": "•••-••-6789"}'::jsonb, true, true),
('Email - Mask Username', 'email', '#@domain.com', 'Mask username part of email', '{"before": "user@example.com", "after": "u***@example.com"}'::jsonb, true, true),
('Phone - Show Last 4', 'phone', '+# ••• ••• ####', 'Show last 4 digits of phone number', '{"before": "+1 555 123 4567", "after": "+1 ••• ••• 4567"}'::jsonb, true, true),
('Name - Mask Last Name', 'name', 'First L', 'Mask last name, show first name and last initial', '{"before": "John Doe", "after": "John D***"}'::jsonb, true, true),
('IBAN - Show First 4 and Last 4', 'iban', '#### **** **** **** **** ####', 'Show first 4 and last 4 characters of IBAN', '{"before": "GB82WEST12345698765432", "after": "GB82 **** **** **** **** 5432"}'::jsonb, true, true)
ON CONFLICT DO NOTHING;

-- ============================================================================
-- 2. ENCRYPTION KEYS
-- ============================================================================

CREATE TABLE IF NOT EXISTS encryption_keys (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    key_id VARCHAR(255) UNIQUE NOT NULL, -- reference to KMS or local key ID
    key_type VARCHAR(50) NOT NULL, -- 'system', 'user', 'file_specific'
    algorithm VARCHAR(100) NOT NULL, -- 'AES-256-GCM', 'AES-256-CBC', 'ChaCha20-Poly1305'
    key_metadata_json JSONB DEFAULT '{}'::jsonb, -- key rotation info, version, etc.
    created_by INTEGER REFERENCES users(id) ON DELETE SET NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    expires_at TIMESTAMPTZ,
    is_active BOOLEAN DEFAULT true,
    rotation_count INTEGER DEFAULT 0,
    last_rotated TIMESTAMPTZ
);

CREATE INDEX IF NOT EXISTS idx_encryption_keys_key_id ON encryption_keys(key_id);
CREATE INDEX IF NOT EXISTS idx_encryption_keys_is_active ON encryption_keys(is_active);
CREATE INDEX IF NOT EXISTS idx_encryption_keys_key_type ON encryption_keys(key_type);

-- ============================================================================
-- 3. FILE ENCRYPTION
-- ============================================================================

CREATE TABLE IF NOT EXISTS file_encryption (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    file_id INTEGER REFERENCES scan_files(id) ON DELETE CASCADE,
    encryption_key_id VARCHAR(255) REFERENCES encryption_keys(key_id) ON DELETE SET NULL,
    algorithm VARCHAR(100) NOT NULL,
    iv VARCHAR(255), -- Initialization Vector (hex)
    auth_tag VARCHAR(255), -- Authentication Tag for GCM
    encrypted_at TIMESTAMPTZ DEFAULT NOW(),
    encrypted_by INTEGER REFERENCES users(id) ON DELETE SET NULL,
    decryption_attempts INTEGER DEFAULT 0,
    last_decryption_attempt TIMESTAMPTZ,
    last_decrypted_by INTEGER REFERENCES users(id) ON DELETE SET NULL,
    is_currently_encrypted BOOLEAN DEFAULT true
);

CREATE INDEX IF NOT EXISTS idx_file_encryption_file_id ON file_encryption(file_id);
CREATE INDEX IF NOT EXISTS idx_file_encryption_key_id ON file_encryption(encryption_key_id);
CREATE INDEX IF NOT EXISTS idx_file_encryption_is_encrypted ON file_encryption(is_currently_encrypted);

-- ============================================================================
-- 4. WATERMARK LOGS (Enhanced)
-- ============================================================================

CREATE TABLE IF NOT EXISTS watermark_logs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    file_id INTEGER REFERENCES scan_files(id) ON DELETE CASCADE,
    watermark_token UUID DEFAULT gen_random_uuid() UNIQUE, -- unique identifier for tracking leaks
    applied_to_user_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
    applied_to_user_email VARCHAR(255),
    watermark_content TEXT NOT NULL, -- actual watermark text applied
    watermark_config_json JSONB DEFAULT '{}'::jsonb, -- position, opacity, color, etc.
    applied_at TIMESTAMPTZ DEFAULT NOW(),
    access_context_json JSONB DEFAULT '{}'::jsonb, -- ip, device, session info
    file_version_hash VARCHAR(64), -- hash of watermarked version
    investigation_status VARCHAR(50) DEFAULT 'none' -- 'none', 'suspected_leak', 'confirmed_leak', 'cleared'
);

CREATE INDEX IF NOT EXISTS idx_watermark_logs_file_id ON watermark_logs(file_id);
CREATE INDEX IF NOT EXISTS idx_watermark_logs_watermark_token ON watermark_logs(watermark_token);
CREATE INDEX IF NOT EXISTS idx_watermark_logs_applied_to_user_id ON watermark_logs(applied_to_user_id);
CREATE INDEX IF NOT EXISTS idx_watermark_logs_applied_at ON watermark_logs(applied_at);
CREATE INDEX IF NOT EXISTS idx_watermark_logs_investigation_status ON watermark_logs(investigation_status);

-- ============================================================================
-- 5. FIELD MASKING LOG
-- ============================================================================

CREATE TABLE IF NOT EXISTS field_masking_log (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    file_id INTEGER REFERENCES scan_files(id) ON DELETE CASCADE,
    field_name VARCHAR(255),
    original_value_hash VARCHAR(64), -- hash only, never store original
    masked_value TEXT,
    masking_rule_id UUID REFERENCES masking_rules(id) ON DELETE SET NULL,
    masked_at TIMESTAMPTZ DEFAULT NOW(),
    accessed_by INTEGER REFERENCES users(id) ON DELETE SET NULL,
    access_granted BOOLEAN DEFAULT false -- whether user was allowed to see original
);

CREATE INDEX IF NOT EXISTS idx_field_masking_log_file_id ON field_masking_log(file_id);
CREATE INDEX IF NOT EXISTS idx_field_masking_log_accessed_by ON field_masking_log(accessed_by);
CREATE INDEX IF NOT EXISTS idx_field_masking_log_masked_at ON field_masking_log(masked_at);

-- ============================================================================
-- 6. PROTECTION ACTIONS LOG
-- ============================================================================

CREATE TABLE IF NOT EXISTS protection_actions_log (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    file_id INTEGER REFERENCES scan_files(id) ON DELETE CASCADE,
    action_type VARCHAR(100) NOT NULL, -- 'masked', 'encrypted', 'quarantined', 'watermarked', 'blocked'
    action_details_json JSONB DEFAULT '{}'::jsonb,
    policy_id UUID REFERENCES protection_policies(id) ON DELETE SET NULL,
    triggered_by VARCHAR(100), -- 'system', 'user', 'policy'
    triggered_by_user_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
    success BOOLEAN DEFAULT true,
    error_message TEXT,
    action_timestamp TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_protection_actions_log_file_id ON protection_actions_log(file_id);
CREATE INDEX IF NOT EXISTS idx_protection_actions_log_action_type ON protection_actions_log(action_type);
CREATE INDEX IF NOT EXISTS idx_protection_actions_log_timestamp ON protection_actions_log(action_timestamp);
CREATE INDEX IF NOT EXISTS idx_protection_actions_log_triggered_by ON protection_actions_log(triggered_by);

-- ============================================================================
-- 7. UPDATE EXISTING TABLES FOR PROTECTION STATUS
-- ============================================================================

-- Add protection-related columns to scan_files if they don't exist
ALTER TABLE scan_files ADD COLUMN IF NOT EXISTS protection_status VARCHAR(50) DEFAULT 'none';
ALTER TABLE scan_files ADD COLUMN IF NOT EXISTS masked_data_json JSONB DEFAULT '{}'::jsonb;
ALTER TABLE scan_files ADD COLUMN IF NOT EXISTS encryption_key_id VARCHAR(255);
ALTER TABLE scan_files ADD COLUMN IF NOT EXISTS encryption_algorithm VARCHAR(100);
ALTER TABLE scan_files ADD COLUMN IF NOT EXISTS is_quarantined BOOLEAN DEFAULT false;
ALTER TABLE scan_files ADD COLUMN IF NOT EXISTS quarantine_reason TEXT;
ALTER TABLE scan_files ADD COLUMN IF NOT EXISTS quarantine_date TIMESTAMPTZ;
ALTER TABLE scan_files ADD COLUMN IF NOT EXISTS watermark_applied BOOLEAN DEFAULT false;
ALTER TABLE scan_files ADD COLUMN IF NOT EXISTS watermark_config_json JSONB DEFAULT '{}'::jsonb;
ALTER TABLE scan_files ADD COLUMN IF NOT EXISTS masked_fields_count INTEGER DEFAULT 0;
ALTER TABLE scan_files ADD COLUMN IF NOT EXISTS encrypted_at TIMESTAMPTZ;
ALTER TABLE scan_files ADD COLUMN IF NOT EXISTS masked_at TIMESTAMPTZ;
ALTER TABLE scan_files ADD COLUMN IF NOT EXISTS watermark_applied_at TIMESTAMPTZ;

-- Add indexes for protection status
CREATE INDEX IF NOT EXISTS idx_scan_files_protection_status ON scan_files(protection_status);
CREATE INDEX IF NOT EXISTS idx_scan_files_is_quarantined ON scan_files(is_quarantined);
CREATE INDEX IF NOT EXISTS idx_scan_files_encryption_key_id ON scan_files(encryption_key_id);

-- ============================================================================
-- 8. COMMENTS
-- ============================================================================

COMMENT ON TABLE masking_rules IS 'Configuration rules for data masking patterns';
COMMENT ON TABLE encryption_keys IS 'Encryption keys managed by KMS/HSM';
COMMENT ON TABLE file_encryption IS 'File-level encryption metadata';
COMMENT ON TABLE watermark_logs IS 'Audit trail for watermark applications and leak tracking';
COMMENT ON TABLE field_masking_log IS 'Log of masked fields for audit trail';
COMMENT ON TABLE protection_actions_log IS 'Comprehensive log of all protection actions applied';

