-- ================================================
-- Migration: Move seekers of a program into user_participation_summary
-- Date: 2026-06-23
-- For a given base program (program_v1.id), backfills
-- user_participation_summary (one row per user + program + sub-program + session)
-- and resyncs its identity sequence.
--
-- EXCLUDED registrations (not moved):
--   * registration_status in (draft, save_as_draft, rejected, cancelled)
--   * not-allocated rows           (allocated_program_id IS NULL)
--   * rows without a user_id, or soft-deleted rows
--
-- Field mapping mirrors UserRegistrationMapService.ensureSummary():
--   program_id        = registration base program (program_v1)
--   sub_program_id    = allocated_program_id
--   session_id        = allocated_session_id, else program_session_id
--   sub_program_type  = allocated program's, else base program's
--   program_starts_at = session.starts_at, else base.starts_at, else base.registration_starts_at
--   program_ends_at   = session.ends_at,   else base.ends_at,   else base.registration_ends_at
--
-- Idempotent (NOT EXISTS guards) — safe to re-run.
--
-- >>> SET THE TARGET BASE PROGRAM ID BEFORE RUNNING <<<
--   The program id is hardcoded as a literal (currently 1082) in the
--   three places marked "TARGET PROGRAM ID" below. Change all three
--   (Part 1 INSERT + both verification queries) to your program id.
--   No psql -v / \set variable is used, so this runs identically in
--   psql, DBeaver, pgAdmin, TablePlus, etc.
-- ================================================

-- ------------------------------------------------
-- PART 1: Backfill user_participation_summary
-- ------------------------------------------------
INSERT INTO user_participation_summary (
    user_id, program_id, program_name,
    sub_program_id, sub_program_name,
    session_id, session_name,
    sub_program_type,
    program_starts_at, program_ends_at,
    created_by, updated_by,
    origin_type, created_at, updated_at
)
SELECT
    reg.user_id,
    base.id,
    base.name,
    reg.allocated_program_id,
    sub.name,
    COALESCE(reg.allocated_session_id, reg.program_session_id),
    sess.name,
    COALESCE(sub.sub_program_type, base.sub_program_type)::text,
    COALESCE(sess.starts_at, base.starts_at, base.registration_starts_at),
    COALESCE(sess.ends_at,   base.ends_at,   base.registration_ends_at),
    NULL, NULL,
    'migrated'::origin_type_enum,
    CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
FROM hdb_program_registration reg
JOIN      program_v1      base ON base.id = reg.program_id
LEFT JOIN program_v1      sub  ON sub.id  = reg.allocated_program_id
LEFT JOIN program_session sess ON sess.id = COALESCE(reg.allocated_session_id, reg.program_session_id)
WHERE reg.program_id           = :program_id
  AND reg.deleted_at           IS NULL
  AND reg.user_id              IS NOT NULL
  AND reg.allocated_program_id IS NOT NULL
  AND reg.registration_status  NOT IN ('draft', 'save_as_draft', 'rejected', 'cancelled')
  AND NOT EXISTS (
        SELECT 1
        FROM user_participation_summary ups
        WHERE ups.user_id    = reg.user_id
          AND ups.program_id = base.id
          AND COALESCE(ups.sub_program_id, 0) = COALESCE(reg.allocated_program_id, 0)
          AND COALESCE(ups.session_id, 0)     = COALESCE(reg.allocated_session_id, reg.program_session_id, 0)
          AND ups.deleted_at IS NULL
  );

-- ------------------------------------------------
-- PART 2: Resync identity sequence (set seq)
-- Robust for both serial and IDENTITY columns.
-- ------------------------------------------------
SELECT setval(
    pg_get_serial_sequence('user_participation_summary', 'id'),
    (SELECT GREATEST(COALESCE(MAX(id), 1), 1) FROM user_participation_summary)
);

-- ------------------------------------------------
-- Verification
-- ------------------------------------------------

-- 1. Summary rows now present for this program
SELECT COUNT(*) AS summary_rows
FROM user_participation_summary
WHERE program_id = :program_id
  AND deleted_at IS NULL;

-- 2. Eligible registrations that did NOT get a summary (should be 0)
SELECT COUNT(*) AS eligible_without_summary
FROM hdb_program_registration reg
JOIN program_v1 base ON base.id = reg.program_id
WHERE reg.program_id           = :program_id
  AND reg.deleted_at           IS NULL
  AND reg.user_id              IS NOT NULL
  AND reg.allocated_program_id IS NOT NULL
  AND reg.registration_status  NOT IN ('draft', 'save_as_draft', 'rejected', 'cancelled')
  AND NOT EXISTS (
        SELECT 1
        FROM user_participation_summary ups
        WHERE ups.user_id    = reg.user_id
          AND ups.program_id = base.id
          AND COALESCE(ups.sub_program_id, 0) = COALESCE(reg.allocated_program_id, 0)
          AND COALESCE(ups.session_id, 0)     = COALESCE(reg.allocated_session_id, reg.program_session_id, 0)
          AND ups.deleted_at IS NULL
  );
