-- Migration: Waitlist Gaps (GAP-B, GAP-H)
-- Date: 2026-05-25

-- GAP-B: Add reserved_link flag to hdb_program_registration
-- Marks that admin has explicitly reserved a seat for a waitlisted registrant.
-- Payment is only allowed for WAITLISTED registrations when this flag is true.

ALTER TABLE hdb_program_registration
    ADD COLUMN IF NOT EXISTS reserved_link BOOLEAN NOT NULL DEFAULT FALSE;

-- reserved_status: tracks whether the seeker has acknowledged/accepted the released seat.
ALTER TABLE hdb_program_registration
    ADD COLUMN IF NOT EXISTS reserved_status BOOLEAN NOT NULL DEFAULT FALSE;

-- sub_status: rich state label for waitlist actions (e.g. WAITLIST_LINK_RELEASED, SEAT_RELEASED, DENIED).
ALTER TABLE hdb_program_registration
    ADD COLUMN IF NOT EXISTS sub_status VARCHAR(100) NULL;

-- released_count on program_v1: counter of how many waitlist seats have been released by admin.
ALTER TABLE program_v1
    ADD COLUMN IF NOT EXISTS released_count INT NOT NULL DEFAULT 0;

-- GAP-C: Add 'archived' to the registration_status enum
ALTER TYPE registration_status_enum ADD VALUE IF NOT EXISTS 'archived';

-- GAP-H: Unique constraint on (program_id, waiting_list_seq_number)
-- Prevents duplicate waitlist seq numbers under concurrent load.

DO $$ BEGIN
    ALTER TABLE hdb_program_registration
        ADD CONSTRAINT uq_program_waitlist_seq
        UNIQUE (program_id, waiting_list_seq_number);
EXCEPTION
    WHEN duplicate_table THEN NULL;
END $$;
