-- ================================================================================================
-- Migration: Backfill PENDING approval rows for TAT registrations missing one
-- Date: 2026-06-26
-- ------------------------------------------------------------------------------------------------
-- Context:
--   handleWaitlistRegistration historically did NOT create a hdb_registration_approval row
--   (unlike the pending/direct registration flows). The code now creates a PENDING row at
--   registration level, but pre-existing TAT registrations placed on the waitlist are missing it.
--   Without the row, release / payment-completion / approve-reject all fail (REGISTRATION_APPROVAL_NOTFOUND).
--
-- What this does:
--   Inserts one PENDING approval row for every non-deleted TAT registration that does NOT already
--   have a non-deleted approval row. The NOT EXISTS guard makes it idempotent (safe to re-run) and
--   automatically excludes direct/pending registrations that already have their row.
--
-- Set value: approval_status = 'pending' (mirrors the code change in handleWaitlistRegistration).
-- ================================================================================================

BEGIN;

-- Preview the rows that WILL be inserted (run/inspect before COMMIT if desired).
-- SELECT reg.id AS registration_id, reg.registration_status, prog.code AS program_code
-- FROM hdb_program_registration reg
-- JOIN program_v1 prog            ON prog.id = reg.program_id        AND prog.deleted_at IS NULL
-- JOIN program_type_v1 ptype      ON ptype.id = prog.program_type_id AND ptype.deleted_at IS NULL
-- WHERE reg.deleted_at IS NULL
--   AND ptype.key = 'PT_TAT'
--   -- AND reg.registration_status = 'waitlisted'   -- uncomment to restrict to waitlisted only
--   AND NOT EXISTS (
--     SELECT 1 FROM hdb_registration_approval appr
--     WHERE appr.registration_id = reg.id AND appr.deleted_at IS NULL
--   )
-- ORDER BY reg.id;

INSERT INTO hdb_registration_approval (
    registration_id,
    approval_status,
    auto_approved,
    created_by,
    updated_by,
    created_at,
    updated_at
)
SELECT
    reg.id,
    'pending',
    FALSE,
    reg.created_by,
    reg.created_by,
    CURRENT_TIMESTAMP,
    CURRENT_TIMESTAMP
FROM hdb_program_registration reg
JOIN program_v1 prog       ON prog.id = reg.program_id        AND prog.deleted_at IS NULL
JOIN program_type_v1 ptype ON ptype.id = prog.program_type_id AND ptype.deleted_at IS NULL
WHERE reg.deleted_at IS NULL
  AND ptype.key = 'PT_TAT'
  -- AND reg.registration_status = 'waitlisted'   -- uncomment to restrict to waitlisted only
  AND NOT EXISTS (
    SELECT 1
    FROM hdb_registration_approval appr
    WHERE appr.registration_id = reg.id
      AND appr.deleted_at IS NULL
  );

-- Verify: every non-deleted TAT registration should now have exactly one non-deleted approval row.
-- SELECT COUNT(*) AS tat_regs_without_approval
-- FROM hdb_program_registration reg
-- JOIN program_v1 prog       ON prog.id = reg.program_id        AND prog.deleted_at IS NULL
-- JOIN program_type_v1 ptype ON ptype.id = prog.program_type_id AND ptype.deleted_at IS NULL
-- WHERE reg.deleted_at IS NULL
--   AND ptype.key = 'PT_TAT'
--   AND NOT EXISTS (
--     SELECT 1 FROM hdb_registration_approval appr
--     WHERE appr.registration_id = reg.id AND appr.deleted_at IS NULL
--   );  -- expect 0

COMMIT;
