-- Chain of Custody Schema
-- 012_chain_of_custody.sql

CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Chain of Custody Events
CREATE TABLE IF NOT EXISTS chain_of_custody (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    document_id UUID REFERENCES documents(id) ON DELETE CASCADE,
    event_type VARCHAR(50) NOT NULL, -- 'collected', 'reviewed', 'exported', 'accessed', 'modified', 'tagged', 'labeled', 'hold_applied', 'hold_released'
    actor_id UUID REFERENCES users(id) ON DELETE SET NULL,
    timestamp TIMESTAMPTZ DEFAULT NOW() NOT NULL,
    ip_address INET,
    user_agent TEXT,
    action_details JSONB DEFAULT '{}', -- {reason, case_id, review_set_id, export_id, hold_id, etc.}
    signature VARCHAR(500), -- HMAC-SHA256(secret, document_id + event_type + timestamp + actor_id) for integrity
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Indexes for performance
CREATE INDEX IF NOT EXISTS idx_chain_of_custody_document ON chain_of_custody(document_id);
CREATE INDEX IF NOT EXISTS idx_chain_of_custody_timestamp ON chain_of_custody(timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_chain_of_custody_actor ON chain_of_custody(actor_id);
CREATE INDEX IF NOT EXISTS idx_chain_of_custody_event_type ON chain_of_custody(event_type);
CREATE INDEX IF NOT EXISTS idx_chain_of_custody_document_ts ON chain_of_custody(document_id, timestamp DESC);

-- Function to compute HMAC signature for chain of custody events
CREATE OR REPLACE FUNCTION compute_custody_signature(
    p_document_id UUID,
    p_event_type VARCHAR,
    p_timestamp TIMESTAMPTZ,
    p_actor_id UUID
) RETURNS TEXT AS $$
DECLARE
    secret_key TEXT := COALESCE(current_setting('app.custody_secret', true), 'default-secret-change-in-production');
    payload TEXT;
BEGIN
    payload := p_document_id::TEXT || '|' || p_event_type || '|' || p_timestamp::TEXT || '|' || COALESCE(p_actor_id::TEXT, '');
    RETURN encode(hmac(payload, secret_key, 'sha256'), 'hex');
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Trigger to auto-compute signature on insert
CREATE OR REPLACE FUNCTION auto_sign_custody_event() RETURNS TRIGGER AS $$
BEGIN
    IF NEW.signature IS NULL THEN
        NEW.signature := compute_custody_signature(NEW.document_id, NEW.event_type, NEW.timestamp, NEW.actor_id);
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_auto_sign_custody
    BEFORE INSERT ON chain_of_custody
    FOR EACH ROW
    EXECUTE FUNCTION auto_sign_custody_event();

-- View for chain of custody integrity verification
CREATE OR REPLACE VIEW v_chain_of_custody_integrity AS
SELECT
    id,
    document_id,
    event_type,
    actor_id,
    timestamp,
    signature,
    compute_custody_signature(document_id, event_type, timestamp, actor_id) as computed_signature,
    CASE
        WHEN signature = compute_custody_signature(document_id, event_type, timestamp, actor_id) THEN true
        ELSE false
    END as is_valid
FROM chain_of_custody;

COMMENT ON TABLE chain_of_custody IS 'Immutable audit trail of all document access and modification events for legal compliance';
COMMENT ON COLUMN chain_of_custody.signature IS 'HMAC-SHA256 signature for integrity verification';
COMMENT ON COLUMN chain_of_custody.action_details IS 'JSON object containing event-specific metadata (case_id, review_set_id, export_id, etc.)';

