-- ================================================
-- PT_ENTRAINMENT — Communication enum prerequisites
-- 2026-08-10
-- DRAFT ONLY — not executed against any database, not yet reviewed/approved.
-- ================================================
--
-- WHY: run this file FIRST, on its own, and let it commit before running
-- 05master-communications-full-refresh.sql. Postgres does not allow a newly ADDed enum value to
-- be used in the same transaction that added it (error 55P04, "unsafe use of new value ... enum
-- values must be committed before they can be used") — confirmed by actually running the
-- combined version of this file, which failed on 'TRAVEL_PLAN_CHANGE_EMAIL_SEEKER' precisely
-- because both the ALTER TYPE and the INSERT that used it were in the same file/transaction.
-- PAP's own migrations avoid this the same way — enum values in their own file (e.g.
-- 55_pap-email-enums.sql), committed, before the seed file that uses them
-- (56_pap-email-template-seed.sql) — this file follows that same split, just applied
-- retroactively after finding the bug the hard way.
--
-- Reproduces file 17's own waitlist-family enum additions (idempotent — safe even if file 17
-- already ran on this database) plus template_key_enum values for the 5 new gap-fill rows.
-- Access-key/category values needed by the 5 new rows
-- (ONLINE_PAYMENT_ACKNOWLEDGEMENT_EMAIL_SEEKER, ONLINE_PAYMENT_COMPLETED_WATI_SEEKER,
-- INVOICE_EMAIL_SEEKER, EINVOICE_ERROR_EMAIL_SEEKER, REGISTRATION_COMPLETED_WITH_PAYMENT_WATI_RM,
-- 'online_payment_comfirmed', 'invoice', 'registration_completed_with_payment') are assumed to
-- already exist from PAP's/TAT's/HDB's own seeding — if this database has never had any of
-- those migrations applied, this file's ADD VALUE IF NOT EXISTS calls won't cover them; check
-- against pg_enum first in that case.
--
-- CORRECTION (found by actually running this against the target DB): template_key_enum on this
-- database had NONE of file 17's original 18 ENT_* labels — proving file 17 itself was never
-- applied here (only communication_templates_master.template_key is varchar; per error 22P02,
-- "invalid input value for enum template_key_enum", hdb_communication_templates.template_key is
-- STILL the enum type on this DB, and PART 2's ::template_key_enum cast failed on the very first
-- key). Original assumption ("PT_ENTRAINMENT already has 18 live rows, only the 5 new ones need
-- enum values") was wrong for this environment. All 23 labels are now added below, not just 5.
--
-- Idempotent — safe to re-run (ADD VALUE IF NOT EXISTS is a no-op once the value exists).
-- ================================================

ALTER TYPE communication_category_enum ADD VALUE IF NOT EXISTS 'waitlisted';
ALTER TYPE communication_category_enum ADD VALUE IF NOT EXISTS 'waitlist_denied';
ALTER TYPE communication_category_enum ADD VALUE IF NOT EXISTS 'waitlist_release';
ALTER TYPE communication_category_enum ADD VALUE IF NOT EXISTS 'payment_link';

-- PAYMENT_LINK_WATI_SEEKER: row #50 (ENT_PAYMENT_LINK_WHATSAPP) was fixed to use this instead of
-- BLESSED_WATI_SEEKER (see that row's comment in 05master-communications-full-refresh.sql) —
-- matches TAT's own dedicated payment-link key (18_master_seed_data_tat.sql:192-195). Already a
-- real enum member in src/common/enum/communication-template-access-key.enum.ts, so this is
-- almost certainly a no-op — added defensively given 05a already had to be corrected once for
-- wrongly assuming enum values existed on this database.
ALTER TYPE communication_template_access_key_enum ADD VALUE IF NOT EXISTS 'PAYMENT_LINK_WATI_SEEKER';

-- PAYMENT_LINK_EMAIL_SEEKER: email counterpart of the key above, for row #37
-- (ENT_PAYMENT_LINK_EMAIL), which was previously mis-keyed to BLESSED_EMAIL_SEEKER — the
-- approval/seat-offered trigger, which never fires for ENT (requires_approval = false).
-- Unlike PAYMENT_LINK_WATI_SEEKER this one is genuinely NEW: it did not exist in
-- src/common/enum/communication-template-access-key.enum.ts and has been added there alongside
-- this migration, together with the email send it needs — sendPaymentLinkCommunication was
-- WhatsApp-only despite its docstring claiming "email + WhatsApp".
ALTER TYPE communication_template_access_key_enum ADD VALUE IF NOT EXISTS 'PAYMENT_LINK_EMAIL_SEEKER';

ALTER TYPE communication_template_access_key_enum ADD VALUE IF NOT EXISTS 'TRAVEL_PLAN_CHANGE_EMAIL_SEEKER';
ALTER TYPE communication_template_access_key_enum ADD VALUE IF NOT EXISTS 'REGISTRATION_WAITINGLIST_WATI_SEEKER';
ALTER TYPE communication_template_access_key_enum ADD VALUE IF NOT EXISTS 'OFFLINE_REGISTRATION_WAITINGLIST_WATI_SEEKER';
ALTER TYPE communication_template_access_key_enum ADD VALUE IF NOT EXISTS 'ONLINE_PAYMENT_DONE_BUT_WAITING_LIST_WATI_SEEKER';
ALTER TYPE communication_template_access_key_enum ADD VALUE IF NOT EXISTS 'WAITLIST_DENY_WATI_SEEKER';
ALTER TYPE communication_template_access_key_enum ADD VALUE IF NOT EXISTS 'WAITLIST_PAID_DENY_WATI_SEEKER';
ALTER TYPE communication_template_access_key_enum ADD VALUE IF NOT EXISTS 'WAITLIST_DENIED_EMAIL_SEEKER';
ALTER TYPE communication_template_access_key_enum ADD VALUE IF NOT EXISTS 'WAITLIST_DENIED_PAID_EMAIL_SEEKER';
ALTER TYPE communication_template_access_key_enum ADD VALUE IF NOT EXISTS 'WAITLIST_RELEASE_PAYMENT_WATI_SEEKER';
ALTER TYPE communication_template_access_key_enum ADD VALUE IF NOT EXISTS 'WAITLIST_RELEASE_PAYMENT_EMAIL_SEEKER';

-- Original 18 (from file 17) — email
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'ENT_REGISTRATION_COMPLETED_EMAIL';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'ENT_OFFLINE_PAYMENT_ACKNOWLEDGEMENT_EMAIL';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'ENT_PAYMENT_LINK_EMAIL';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'ENT_TRAVEL_PLAN_EMAIL';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'ENT_WAITLIST_RELEASE_PAYMENT_EMAIL';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'ENT_WAITLIST_DENIED_EMAIL';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'ENT_WAITLIST_DENIED_PAID_EMAIL';

-- Original 18 (from file 17) — whatsapp
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'ENT_REGISTRATION_COMPLETED_WHATSAPP';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'ENT_OFFLINE_REGISTRATION_COMPLETED_WHATSAPP';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'ENT_RM_REGISTRATION_COMPLETED_WHATSAPP';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'ENT_WAITLIST_REGISTRATION_WHATSAPP';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'ENT_OFFLINE_REGISTRATION_WAITINGLIST_WHATSAPP';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'ENT_ONLINE_PAYMENT_WAITLISTED_WHATSAPP';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'ENT_PAYMENT_LINK_WHATSAPP';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'ENT_OFFLINE_PAYMENT_DONE_WHATSAPP';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'ENT_WAITLIST_RELEASE_PAYMENT_WHATSAPP';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'ENT_WAITLIST_DENY_WHATSAPP';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'ENT_WAITLIST_PAID_DENY_WHATSAPP';

-- New (5) — gap-fill vs PAP/TAT
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'ENT_ONLINE_PAYMENT_ACKNOWLEDGEMENT_EMAIL';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'ENT_ONLINE_PAYMENT_COMPLETED_WHATSAPP';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'ENT_INVOICE_EMAIL';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'ENT_EINVOICE_ERROR_EMAIL';

-- =====================================================
-- VERIFICATION — confirm every value above now exists before running 05
-- =====================================================
SELECT t.typname, e.enumlabel
FROM pg_enum e
JOIN pg_type t ON t.oid = e.enumtypid
WHERE (t.typname = 'communication_category_enum' AND e.enumlabel IN ('waitlisted', 'waitlist_denied', 'waitlist_release', 'payment_link'))
   OR (t.typname = 'communication_template_access_key_enum' AND e.enumlabel IN (
        'TRAVEL_PLAN_CHANGE_EMAIL_SEEKER', 'REGISTRATION_WAITINGLIST_WATI_SEEKER',
        'OFFLINE_REGISTRATION_WAITINGLIST_WATI_SEEKER', 'ONLINE_PAYMENT_DONE_BUT_WAITING_LIST_WATI_SEEKER',
        'WAITLIST_DENY_WATI_SEEKER', 'WAITLIST_PAID_DENY_WATI_SEEKER', 'WAITLIST_DENIED_EMAIL_SEEKER',
        'WAITLIST_DENIED_PAID_EMAIL_SEEKER', 'WAITLIST_RELEASE_PAYMENT_WATI_SEEKER', 'WAITLIST_RELEASE_PAYMENT_EMAIL_SEEKER',
        'PAYMENT_LINK_WATI_SEEKER', 'PAYMENT_LINK_EMAIL_SEEKER'
   ))
   OR (t.typname = 'template_key_enum' AND e.enumlabel LIKE 'ENT\_%')
ORDER BY t.typname, e.enumlabel;
