-- Migration: Durable per-registrant provisioning status on the online-session extension
-- Date: 2026-07-04
-- Description:
--   Makes a failed (registration, online session) pair a first-class, durable row
--   on hdb_program_registration_online_session instead of living only in
--   background_jobs.metadata.failures[]. A row now carries its provisioning
--   outcome and, when it failed, the reason/message and attempt bookkeeping.
--
--     status           -- 'registered' (default/legacy) | 'failed'
--     failure_reason   -- error code of the last failed attempt (null when registered)
--     failure_message  -- human-readable message of the last failed attempt
--     attempt_count    -- number of provisioning attempts recorded for this pair
--     last_attempt_at  -- when the last attempt (success or failure) was recorded
--
--   The existing partial unique index
--   uq_prog_reg_online_session_reg_session (registration_id, online_session_id)
--   WHERE deleted_at IS NULL already guarantees a single active row per pair, so a
--   failed attempt and a later success share ONE row (the success flips it back to
--   'registered'). A failed row holds no external_registrant_id/join_url.
--
-- Additive and idempotent. Existing rows are all successes and read as 'registered'.

ALTER TABLE hdb_program_registration_online_session
    ADD COLUMN IF NOT EXISTS status VARCHAR(30) NOT NULL DEFAULT 'registered';

COMMENT ON COLUMN hdb_program_registration_online_session.status IS
    'Provisioning outcome for this registrant on this online session: registered (provisioned, holds a join link) or failed (last attempt failed, durable failure record). Only registered rows count as generated / block re-registration.';

ALTER TABLE hdb_program_registration_online_session
    ADD COLUMN IF NOT EXISTS failure_reason VARCHAR(100) NULL;

COMMENT ON COLUMN hdb_program_registration_online_session.failure_reason IS
    'Error code of the last failed provisioning attempt for this pair; null when status is registered.';

ALTER TABLE hdb_program_registration_online_session
    ADD COLUMN IF NOT EXISTS failure_message TEXT NULL;

COMMENT ON COLUMN hdb_program_registration_online_session.failure_message IS
    'Human-readable message of the last failed provisioning attempt; null when status is registered.';

ALTER TABLE hdb_program_registration_online_session
    ADD COLUMN IF NOT EXISTS attempt_count INT NOT NULL DEFAULT 0;

COMMENT ON COLUMN hdb_program_registration_online_session.attempt_count IS
    'Number of provisioning attempts recorded for this pair (bumped on each failure and on the attempt that succeeds).';

ALTER TABLE hdb_program_registration_online_session
    ADD COLUMN IF NOT EXISTS last_attempt_at TIMESTAMPTZ NULL;

COMMENT ON COLUMN hdb_program_registration_online_session.last_attempt_at IS
    'Timestamp of the last recorded provisioning attempt (success or failure) for this pair.';

-- Fast "outstanding failures for a session" scans (consumed in a follow-up pass).
CREATE INDEX IF NOT EXISTS idx_prog_reg_online_session_failed
    ON hdb_program_registration_online_session (online_session_id)
    WHERE status = 'failed' AND deleted_at IS NULL;
