-- Migration: email_access_token
-- Phase 1.1 - Lifecycle records for tokenized download links.

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

CREATE TABLE IF NOT EXISTS email_access_token (
    id BIGINT PRIMARY KEY DEFAULT nextval('email_access_token_seq'),
    jti VARCHAR(64) NOT NULL UNIQUE,
    form_type VARCHAR(64) NOT NULL,
    form_id BIGINT NOT NULL,
    scope VARCHAR(32) NOT NULL,
    sensitivity VARCHAR(16) NOT NULL,
    recipient_domain VARCHAR(255),
    expires_at TIMESTAMP NOT NULL,
    max_downloads INT NOT NULL DEFAULT 1,
    download_count INT NOT NULL DEFAULT 0,
    revoked_at TIMESTAMP,
    created_by VARCHAR(128),
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_email_access_token_form ON email_access_token(form_type, form_id);
CREATE INDEX IF NOT EXISTS idx_email_access_token_expires_at ON email_access_token(expires_at);


