-- Migration: Per-session join URLs for a program registration
-- Description: A program runs multiple online sessions (each a row in
--              hdb_online_session). The same registrant can be pushed to more
--              than one of them (e.g. the bulk job targets a session that need
--              not be the registration's own program_session), so a registration
--              needs ONE per-registrant record (join url, external registrant id,
--              panelist flag) PER online session — not a single 1:1 row.
--
--              This drops the registration_id 1:1 unique and re-keys the
--              extension on (registration_id, online_session_id). Legacy rows
--              created before this migration keep online_session_id NULL.
-- Idempotent — safe to re-run.

-- 1. Link each extension row to the online session its join URL was issued for.
ALTER TABLE hdb_program_registration_online_session
  ADD COLUMN IF NOT EXISTS online_session_id BIGINT NULL
    REFERENCES hdb_online_session (id);

COMMENT ON COLUMN hdb_program_registration_online_session.online_session_id IS
  'Online session (hdb_online_session) this per-registrant join URL was issued against. NULL for rows created before per-session links.';

-- 2. Drop the old "one extension per registration" unique constraint.
ALTER TABLE hdb_program_registration_online_session
  DROP CONSTRAINT IF EXISTS hdb_program_registration_online_session_registration_id_key;

-- 3. New key: one active (non-deleted) extension per (registration, online session).
--    Partial on deleted_at so a soft-deleted unregister can be re-registered.
CREATE UNIQUE INDEX IF NOT EXISTS uq_prog_reg_online_session_reg_session
  ON hdb_program_registration_online_session (registration_id, online_session_id)
  WHERE deleted_at IS NULL;

-- 4. Index the new FK for lookups/joins.
CREATE INDEX IF NOT EXISTS idx_prog_reg_online_session_online_session_id
  ON hdb_program_registration_online_session (online_session_id);
