-- Adds link_generation_at to program_session.
-- Holds the time at which bulk online-session registration links are generated
-- for the session. A one-time EventBridge schedule is registered against this
-- value when the session's online resource is provisioned. Nullable; null means
-- no scheduled link generation. Idempotent.

ALTER TABLE program_session
    ADD COLUMN IF NOT EXISTS link_generation_at timestamptz NULL;

COMMENT ON COLUMN program_session.link_generation_at IS
    'When bulk online-session registration links are generated for this session; drives the one-time link-generation schedule. Null = none.';

-- Thin lifecycle marker for the scheduled link generation. Per-registrant
-- results stay on the bulk job + extension rows; this only tracks the run state:
-- NOT_SCHEDULED | SCHEDULED | IN_PROGRESS | COMPLETED | FAILED.
ALTER TABLE program_session
    ADD COLUMN IF NOT EXISTS link_generation_status varchar(20) NOT NULL DEFAULT 'NOT_SCHEDULED';

COMMENT ON COLUMN program_session.link_generation_status IS
    'Lifecycle of the scheduled bulk link generation: NOT_SCHEDULED | SCHEDULED | IN_PROGRESS | COMPLETED | FAILED. Thin marker; counts derived from background_jobs + extension rows.';

-- Supports the two hot-path queries that drive link generation: the atomic
-- claim (by status) and the fallback sweep (status + due time, ordered by time).
-- Partial on deleted_at IS NULL so the index stays small and matches the query
-- guard. Idempotent.
CREATE INDEX IF NOT EXISTS idx_program_session_link_generation
    ON program_session (link_generation_status, link_generation_at)
    WHERE deleted_at IS NULL;
