-- Migration: email_otp_challenge
-- Phase 1.1 - OTP step-up challenge state for sensitive form access.

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

CREATE TABLE IF NOT EXISTS email_otp_challenge (
    id BIGINT PRIMARY KEY DEFAULT nextval('email_otp_challenge_seq'),
    token_jti VARCHAR(64) NOT NULL,
    code_hash VARCHAR(128) NOT NULL,
    attempts INT NOT NULL DEFAULT 0,
    max_attempts INT NOT NULL DEFAULT 5,
    expires_at TIMESTAMP NOT NULL,
    verified_at TIMESTAMP,
    granted_until TIMESTAMP,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_email_otp_challenge_jti ON email_otp_challenge(token_jti);
CREATE INDEX IF NOT EXISTS idx_email_otp_challenge_expires_at ON email_otp_challenge(expires_at);