-- Migration: Add Protection Logs Tables
-- 036_protection_logs.sql
-- Creates tables for logging all protection actions

-- Field Masking Log (if not exists)
CREATE TABLE IF NOT EXISTS field_masking_log (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    document_id UUID REFERENCES documents(id) ON DELETE CASCADE,
    file_id INTEGER, -- Legacy support for scan_files
    masked_at TIMESTAMPTZ DEFAULT NOW(),
    accessed_by UUID REFERENCES users(id) ON DELETE SET NULL,
    access_granted BOOLEAN DEFAULT false,
    masked_fields_count INTEGER DEFAULT 0,
    detection_types TEXT[],
    masking_rules_applied JSONB,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Create indexes
CREATE INDEX IF NOT EXISTS idx_field_masking_log_document_id ON field_masking_log(document_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);

-- Protection Action Log (comprehensive logging)
CREATE TABLE IF NOT EXISTS protection_action_log (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    document_id UUID REFERENCES documents(id) ON DELETE CASCADE,
    file_id INTEGER, -- Legacy support
    user_id UUID REFERENCES users(id) ON DELETE SET NULL,
    action_type VARCHAR(50) NOT NULL, -- 'open', 'download', 'upload', 'share', 'send_email', 'edit'
    protection_applied JSONB DEFAULT '{}'::jsonb, -- { masked: true, watermarked: true, encrypted: false }
    decision_result JSONB, -- Decision engine result
    policy_refs TEXT[],
    blocked BOOLEAN DEFAULT false,
    block_reason TEXT,
    recipient_email TEXT, -- For share/email actions
    is_external_recipient BOOLEAN DEFAULT false,
    ip_address VARCHAR(45),
    user_agent TEXT,
    session_id VARCHAR(255),
    device_id VARCHAR(255),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Create indexes
CREATE INDEX IF NOT EXISTS idx_protection_action_log_document_id ON protection_action_log(document_id);
CREATE INDEX IF NOT EXISTS idx_protection_action_log_user_id ON protection_action_log(user_id);
CREATE INDEX IF NOT EXISTS idx_protection_action_log_action_type ON protection_action_log(action_type);
CREATE INDEX IF NOT EXISTS idx_protection_action_log_created_at ON protection_action_log(created_at);
CREATE INDEX IF NOT EXISTS idx_protection_action_log_blocked ON protection_action_log(blocked);

-- Add comments
COMMENT ON TABLE field_masking_log IS 'Logs all masking operations applied to sensitive data';
COMMENT ON TABLE protection_action_log IS 'Comprehensive log of all protection actions (masking, watermark, encryption, block, etc.)';

