-- DEBT-014: Operation Audit Trail for Duplicate & Reconciliation Tracking

CREATE SCHEMA IF NOT EXISTS compliance;

CREATE TABLE IF NOT EXISTS compliance.operation_audit_trail (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    event_type VARCHAR(50) NOT NULL,
    correlation_id UUID NOT NULL,
    entity_type VARCHAR(50) NOT NULL,
    entity_id UUID NOT NULL,
    details JSONB,
    detected_at TIMESTAMP NOT NULL DEFAULT NOW(),
    resolved_by UUID,
    resolved_at TIMESTAMP,
    published_at TIMESTAMP NOT NULL DEFAULT NOW(),
    revision INT NOT NULL DEFAULT 1
);

CREATE INDEX idx_audit_trail_event_type ON compliance.operation_audit_trail(event_type, detected_at DESC);
CREATE INDEX idx_audit_trail_correlation ON compliance.operation_audit_trail(correlation_id);
CREATE INDEX idx_audit_trail_entity ON compliance.operation_audit_trail(entity_type, entity_id);