-- Migration: Live eligible-active counter on hdb_online_session
-- Date: 2026-07-13
-- Description:
--   Maintains, per online session, the current count of provisioned
--   (status = 'registered') registrants whose activation_status is 'active'.
--   Adjusted atomically by the activation-toggle flow rather than computed on
--   every read. Backfilled here from the existing extension rows so it starts
--   accurate for already-provisioned sessions.
--
-- Additive and idempotent — safe to re-run.

ALTER TABLE hdb_online_session
    ADD COLUMN IF NOT EXISTS eligible_active_count INT NOT NULL DEFAULT 0;

COMMENT ON COLUMN hdb_online_session.eligible_active_count IS
    'Live count of hdb_program_registration_online_session rows for this session with status = registered and activation_status = active. Maintained atomically by the activation-toggle flow.';

UPDATE hdb_online_session AS session
SET eligible_active_count = backfill.active_count
FROM (
    SELECT
        online_session_id,
        COUNT(*) AS active_count
    FROM hdb_program_registration_online_session
    WHERE status = 'registered'
      AND activation_status = 'active'
      AND deleted_at IS NULL
    GROUP BY online_session_id
) AS backfill
WHERE backfill.online_session_id = session.id;
