-- Protection Services Migration
-- 028_protection_services.sql
-- Tables for Masking, Watermarking, Quarantine, and Decision Engine

CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- ============================================================================
-- 1. QUARANTINE RECORDS
-- ============================================================================

CREATE TABLE IF NOT EXISTS quarantine_records (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  file_id UUID NOT NULL,
  original_path TEXT NOT NULL,
  quarantine_path TEXT NOT NULL,
  restored_path TEXT,
  reason_type VARCHAR(50) NOT NULL, -- 'high_risk', 'sensitive_data', 'policy_violation', 'malware_flag', 'manual'
  reason_description TEXT NOT NULL,
  detected_type VARCHAR(50), -- PII, PCI, PHI, etc.
  confidence DECIMAL(3,2),
  policy_id UUID REFERENCES protection_policies(id) ON DELETE SET NULL,
  classified_by UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  classified_at TIMESTAMPTZ DEFAULT NOW(),
  status VARCHAR(20) DEFAULT 'quarantined', -- 'quarantined', 'reviewed', 'approved', 'rejected', 'deleted', 'restored'
  reviewed_by UUID REFERENCES users(id) ON DELETE SET NULL,
  reviewed_at TIMESTAMPTZ,
  reviewer_comment TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_quarantine_file_id ON quarantine_records(file_id);
CREATE INDEX IF NOT EXISTS idx_quarantine_status ON quarantine_records(status);
CREATE INDEX IF NOT EXISTS idx_quarantine_classified_by ON quarantine_records(classified_by);

-- ============================================================================
-- 2. WATERMARK TOKENS (Audit Trail)
-- ============================================================================

CREATE TABLE IF NOT EXISTS watermark_tokens (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  file_id UUID,
  file_path TEXT,
  token TEXT NOT NULL UNIQUE,
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  user_email VARCHAR(255),
  session_id VARCHAR(255),
  device_id VARCHAR(255),
  ip_address VARCHAR(45),
  action VARCHAR(50) NOT NULL, -- 'view', 'download', 'print', 'share'
  watermark_text TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_watermark_token ON watermark_tokens(token);
CREATE INDEX IF NOT EXISTS idx_watermark_file_id ON watermark_tokens(file_id);
CREATE INDEX IF NOT EXISTS idx_watermark_user_id ON watermark_tokens(user_id);
CREATE INDEX IF NOT EXISTS idx_watermark_created_at ON watermark_tokens(created_at);

-- ============================================================================
-- 3. MASKING LOGS (Audit Trail)
-- ============================================================================

CREATE TABLE IF NOT EXISTS masking_logs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  file_id UUID,
  field_path TEXT, -- e.g., "email", "credit_card", "table.column"
  detected_type VARCHAR(50) NOT NULL,
  original_value_hash TEXT, -- SHA-256 hash (not the actual value)
  masked_value TEXT, -- The masked version (safe to store)
  rule_applied VARCHAR(50),
  user_id UUID REFERENCES users(id) ON DELETE SET NULL,
  accessed_at TIMESTAMPTZ DEFAULT NOW(),
  context JSONB DEFAULT '{}'::jsonb
);

CREATE INDEX IF NOT EXISTS idx_masking_file_id ON masking_logs(file_id);
CREATE INDEX IF NOT EXISTS idx_masking_user_id ON masking_logs(user_id);
CREATE INDEX IF NOT EXISTS idx_masking_detected_type ON masking_logs(detected_type);

-- ============================================================================
-- 4. DECISION ENGINE LOGS
-- ============================================================================

CREATE TABLE IF NOT EXISTS decision_engine_logs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  context_id UUID, -- Unique identifier for this decision context
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  file_id UUID,
  action VARCHAR(50) NOT NULL, -- 'open', 'download', 'upload', 'share', 'edit', 'send_email', 'print'
  decision VARCHAR(50) NOT NULL, -- 'allow', 'deny', 'mask', 'watermark', 'encrypt', 'quarantine', 'block'
  rationale TEXT,
  applied_actions TEXT[] DEFAULT '{}',
  policy_refs TEXT[] DEFAULT '{}',
  sensitivity_level VARCHAR(20),
  classification_label VARCHAR(255),
  detected_types TEXT[] DEFAULT '{}',
  is_external_recipient BOOLEAN DEFAULT false,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_decision_user_id ON decision_engine_logs(user_id);
CREATE INDEX IF NOT EXISTS idx_decision_file_id ON decision_engine_logs(file_id);
CREATE INDEX IF NOT EXISTS idx_decision_action ON decision_engine_logs(action);
CREATE INDEX IF NOT EXISTS idx_decision_created_at ON decision_engine_logs(created_at);

-- ============================================================================
-- 5. PROTECTION POLICY ACTIONS (Enhanced)
-- ============================================================================

-- Add columns to protection_policies if they don't exist
ALTER TABLE protection_policies ADD COLUMN IF NOT EXISTS mask_config JSONB DEFAULT '{}'::jsonb;
ALTER TABLE protection_policies ADD COLUMN IF NOT EXISTS watermark_config JSONB DEFAULT '{}'::jsonb;
ALTER TABLE protection_policies ADD COLUMN IF NOT EXISTS encryption_config JSONB DEFAULT '{}'::jsonb;
ALTER TABLE protection_policies ADD COLUMN IF NOT EXISTS quarantine_config JSONB DEFAULT '{}'::jsonb;

-- ============================================================================
-- 6. USER PERMISSIONS (Enhanced for Protection)
-- ============================================================================

-- Create user_permissions table if it doesn't exist
CREATE TABLE IF NOT EXISTS user_permissions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  permission VARCHAR(100) NOT NULL, -- 'view_full_sensitive_data', 'bypass_masking', 'view_quarantine', etc.
  granted_by UUID REFERENCES users(id) ON DELETE SET NULL,
  granted_at TIMESTAMPTZ DEFAULT NOW(),
  expires_at TIMESTAMPTZ,
  is_active BOOLEAN DEFAULT true,
  UNIQUE(user_id, permission)
);

CREATE INDEX IF NOT EXISTS idx_user_permissions_user_id ON user_permissions(user_id);
CREATE INDEX IF NOT EXISTS idx_user_permissions_permission ON user_permissions(permission);

-- ============================================================================
-- 7. COMMENTS
-- ============================================================================

COMMENT ON TABLE quarantine_records IS 'Records of files moved to quarantine/safe folder';
COMMENT ON TABLE watermark_tokens IS 'Audit trail for watermark applications';
COMMENT ON TABLE masking_logs IS 'Audit trail for data masking operations';
COMMENT ON TABLE decision_engine_logs IS 'Audit trail for decision engine evaluations';
COMMENT ON TABLE user_permissions IS 'User-specific permissions for protection features';

