-- 2026-06-25-02-online-session-program-id-on-session-rows.sql
--
-- Purpose: allow `program_id` to be stored on session-level hdb_online_session
-- rows (a denormalized "which program does this session belong to") in addition
-- to program-template rows.
--
-- The original index uq_online_session_program_id was UNIQUE over every non-null
-- program_id, which restricted program_id to one row per program and so blocked
-- session rows from carrying it. Replace it with a TEMPLATE-ONLY partial unique
-- index (program_id unique only among template rows, i.e. program_session_id IS
-- NULL) so the "one template per program" guarantee is kept while session rows
-- may share their program's id. Add a plain lookup index on program_id.
--
-- Idempotent (IF EXISTS / IF NOT EXISTS) and transactional.

BEGIN;

-- 1. Drop the old table-wide unique partial index.
DROP INDEX IF EXISTS uq_online_session_program_id;

-- 2. Template-only uniqueness: at most one template row per program.
CREATE UNIQUE INDEX IF NOT EXISTS uq_online_session_program_id_template
  ON hdb_online_session (program_id)
  WHERE program_id IS NOT NULL AND program_session_id IS NULL;

-- 3. Plain lookup index for "all online sessions of a program".
CREATE INDEX IF NOT EXISTS idx_online_session_program_id
  ON hdb_online_session (program_id);

-- 4. Backfill program_id onto existing session rows from their program_session.
--    (Runs after the unique index is scoped to template rows, so session rows
--    sharing a program_id do not collide.)
UPDATE hdb_online_session os
SET program_id = ps.program_id
FROM program_session ps
WHERE os.program_session_id = ps.id
  AND os.program_id IS NULL
  AND ps.program_id IS NOT NULL;

COMMIT;
