-- Migration: email_access_audit
-- Phase 1.1 - Audit trail for tokenized link access attempts and key operations.

CREATE SEQUENCE IF NOT EXISTS email_access_audit_seq START WITH 1 INCREMENT BY 1;

CREATE TABLE IF NOT EXISTS email_access_audit (
    id BIGINT PRIMARY KEY DEFAULT nextval('email_access_audit_seq'),
    token_jti VARCHAR(64),
    action VARCHAR(32) NOT NULL,
    result VARCHAR(16) NOT NULL,
    actor_user_id BIGINT,
    actor_email VARCHAR(255),
    ip_address VARCHAR(64),
    user_agent VARCHAR(512),
    correlation_id VARCHAR(64),
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_email_access_audit_jti ON email_access_audit(token_jti);
CREATE INDEX IF NOT EXISTS idx_email_access_audit_created_at ON email_access_audit(created_at);