-- Migration: Add Protection Fields to Documents Table
-- 034_documents_protection_fields.sql
-- Adds protection-related columns to documents table to support protection actions

-- Add protection status column
ALTER TABLE documents ADD COLUMN IF NOT EXISTS protection_status VARCHAR(50) DEFAULT 'none';
-- Values: 'none', 'masked', 'encrypted', 'quarantined', 'watermarked', 'multiple'

-- Add encryption fields
ALTER TABLE documents ADD COLUMN IF NOT EXISTS encryption_key_id VARCHAR(255);
ALTER TABLE documents ADD COLUMN IF NOT EXISTS encryption_algorithm VARCHAR(100);
-- References encryption_keys.key_id

-- Add quarantine fields
ALTER TABLE documents ADD COLUMN IF NOT EXISTS is_quarantined BOOLEAN DEFAULT false;
ALTER TABLE documents ADD COLUMN IF NOT EXISTS quarantine_reason TEXT;
ALTER TABLE documents ADD COLUMN IF NOT EXISTS quarantine_date TIMESTAMPTZ;

-- Add watermark fields
ALTER TABLE documents ADD COLUMN IF NOT EXISTS watermark_applied BOOLEAN DEFAULT false;
ALTER TABLE documents ADD COLUMN IF NOT EXISTS watermark_config_json JSONB DEFAULT '{}'::jsonb;

-- Add masking fields
ALTER TABLE documents ADD COLUMN IF NOT EXISTS masked_data_json JSONB DEFAULT '{}'::jsonb;
-- Stores masked field details: { field_name: masked_value, rule_id: uuid, ... }

-- Create indexes for performance
CREATE INDEX IF NOT EXISTS idx_documents_protection_status ON documents(protection_status);
CREATE INDEX IF NOT EXISTS idx_documents_is_quarantined ON documents(is_quarantined);
CREATE INDEX IF NOT EXISTS idx_documents_encryption_key_id ON documents(encryption_key_id) WHERE encryption_key_id IS NOT NULL;

-- Add comments for documentation
COMMENT ON COLUMN documents.protection_status IS 'Current protection status: none, masked, encrypted, quarantined, watermarked, or multiple';
COMMENT ON COLUMN documents.is_quarantined IS 'Whether the file is currently quarantined';
COMMENT ON COLUMN documents.masked_data_json IS 'JSON object storing masked field information for logging';

