-- =====================================================================
-- Scope the online_session unique indexes to live (non-soft-deleted) rows.
--
-- hdb_online_session is now soft-deleted (deleted_at) on remove() rather than
-- hard-deleted. Without this, a soft-deleted row would keep occupying its
-- uniqueness slot and re-provisioning the same program/session would fail with a
-- unique violation. Adding `deleted_at IS NULL` frees the slot on soft delete
-- while still guaranteeing one LIVE template per program and one LIVE
-- online-session per program_session.
-- =====================================================================

-- Template uniqueness: at most one live template row per program.
DROP INDEX IF EXISTS uq_online_session_program_id_template;
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 AND deleted_at IS NULL;

-- Per-session uniqueness: at most one live online-session row per program_session.
DROP INDEX IF EXISTS uq_online_session_program_session_id;
CREATE UNIQUE INDEX IF NOT EXISTS uq_online_session_program_session_id
  ON hdb_online_session (program_session_id)
  WHERE program_session_id IS NOT NULL AND deleted_at IS NULL;
