-- ============================================================
-- Migration: feature_flag — per-program-TYPE scoping + 11 new report-download flags
--
-- feature_flag (database/base-script/portal-base-schema-script.sql,
-- re-declared/seeded in database/date-wise-scripts.sql ~line 8073) has been a
-- global singleton per feature_key: UNIQUE(feature_key), no program
-- association. This adds a nullable program_type_id so a flag row can
-- instead be scoped to one program TYPE (program_type_v1 — HDB/MSD/TAT/...,
-- see program_v1.program_type_id) rather than one individual program — the
-- same toggle applies to every program of that type at once, which is what
-- these report-download flags are actually meant to control. NULL
-- program_type_id keeps meaning "global" (the 3 existing keys —
-- sendPaymentRemindereToRM, showInfinipath, showInfiniminutePrayer — are
-- untouched by this migration and keep working exactly as before, since
-- their rows already have program_type_id NULL).
--
-- The old single-column UNIQUE(feature_key) can't coexist with per-type rows
-- (two different program types would collide on the same key), so it's
-- replaced with two partial unique indexes — same pattern already used by
-- zoom_analytics_attendee_summary for its own nullable registration_id (see
-- that entity's docblock): one enforces "at most one global row per key"
-- (program_type_id IS NULL), the other "at most one row per key per program
-- type" (program_type_id IS NOT NULL).
--
-- 11 new keys gate report-download features in the reports module (see
-- REPORT_PURPOSE_FEATURE_FLAGS in src/common/constants/constants.ts and
-- ReportsService.assertReportFeatureEnabled/getReportFeatureFlags): one flag
-- per report/purpose, 1:1 (enableZoomLinksReport, enableEligibleRegistrationsReport,
-- enableSingleSessionAttendanceReport, enableSessionDetailReport,
-- enableLateComerReport, enableSessionDropoffsReport, enableAttendeesLiveReport,
-- enableGeneralSessionReport, enableGeneralLateComerReport,
-- enableGeneralSessionDropoffsReport), PLUS enableSingleSessionReport — this one
-- is UNRELATED to the 4 individual single-session purpose flags above; it
-- exclusively gates OnlineSessionService.isSessionReportEnabled (the Sessions
-- list screen's own "download session report" affordance), nothing else.
-- Seeded here as global rows defaulting to FALSE (opt-in) — enable a program
-- type's reports by either flipping the global row to TRUE (turns it on for
-- every type) or inserting a type-scoped row (program_type_id = that type's
-- id) to override just for that type, same override precedence
-- FeatureFlagService.isFeatureEnabledForProgramType already implements
-- (type-scoped row wins over the global one; no row at all for a key means
-- disabled, so nothing needed for types that shouldn't have it).
-- ============================================================

ALTER TABLE feature_flag
  ADD COLUMN IF NOT EXISTS program_type_id INT NULL REFERENCES program_type_v1(id);

ALTER TABLE feature_flag
  DROP CONSTRAINT IF EXISTS feature_flag_feature_key_key;

CREATE UNIQUE INDEX IF NOT EXISTS uq_feature_flag_key_global
  ON feature_flag (feature_key)
  WHERE program_type_id IS NULL;

CREATE UNIQUE INDEX IF NOT EXISTS uq_feature_flag_key_program_type
  ON feature_flag (feature_key, program_type_id)
  WHERE program_type_id IS NOT NULL;
