-- ================================================
-- PT_ENTRAINMENT — Master communications FULL REFRESH (Email + WhatsApp/WATI)
-- 2026-08-10
-- DRAFT ONLY — not executed against any database, not yet reviewed/approved.
--
-- RUN ORDER: run 05aentrainment-communications-enums.sql FIRST and let it commit (it adds enum
-- values this file's inserts depend on — Postgres rejects using a new enum value in the same
-- transaction that added it; confirmed for real by running an earlier, combined version of this
-- file, which failed on 'TRAVEL_PLAN_CHANGE_EMAIL_SEEKER' — see that file's own header). Then
-- run this file.
-- ================================================
--
-- WHY: none of the files in this entrainment-migrations-v2(2026-08-10) folder have been run
-- yet, so — consistent with the same soft-delete-then-recreate-from-scratch pattern already
-- used for workflow_configuration (02workflow-refresh.sql) and program_template
-- (03template-refresh.sql) — this does the same for communication templates instead of just
-- additively inserting the 5 gap-fill rows on top of the untouched original 18. It supersedes
-- database/program-configuration/17_master_seed_data_entrainment.sql's 18 rows (7 email + 11
-- WhatsApp/WATI) AND adds the 5 templates identified as missing relative to PAP/TAT parity, for
-- a full, fresh set of 23 live PT_ENTRAINMENT communication templates:
--
--   Reproduced from file 17 (18, one of them re-keyed): registration completed (WATI+WATI-RM;
--   its EMAIL row is re-keyed to the online-payment-confirmed trigger — see row 35), offline
--   payment acknowledgement (email+WATI), blessed/payment-link (email+WATI), travel-plan-change
--   (email), full waitlist family — waitlisted/offline-waitlisted/online-paid-waitlisted (WATI),
--   release/deny/paid-deny (email+WATI), offline-payment-done/invoice (WATI).
--
--   NEW (4, gap vs PAP/TAT — see prior research in this session):
--     (Online payment confirmed — Email is NOT new: it is file 17's registration-completed
--      email re-keyed onto ONLINE_PAYMENT_ACKNOWLEDGEMENT_EMAIL_SEEKER at row 35, since that
--      email always described a completed-and-paid registration.)
--     2. Online payment confirmed — WhatsApp     (PAP has this; ENT's only "paid" WATI row,
--        ONLINE_PAYMENT_DONE_BUT_WAITING_LIST_WATI_SEEKER, is waitlist-specific)
--     3. Invoice — Email                         (PAP has this; ENT's only invoice-tagged row is
--        WATI-only and scoped to the offline-payment path, not a general invoice trigger)
--     4. RM "registration completed WITH payment" — WhatsApp (TAT has this, file 20)
--     5. E-invoice (IRP) generation failure — Email          (TAT has this, file 23)
--
-- Session-lifecycle communications (welcome, invite, absent, program-completion — TAT files
-- 27-52) are deliberately NOT included: those exist because TAT is a multi-session *online*
-- program type; ENT (like PAP) is a fixed-dates residential program.
--
-- SOFT-DELETE MECHANICS: communication_templates_master has NO deleted_at column (confirmed —
-- src/common/entities/communication-templates-master.entity.ts) — its off-switch is the
-- `is_active` boolean. hdb_communication_templates (the per-program clones) DOES have deleted_at
-- (src/common/entities/communication-templates.entity.ts:133-134). So "soft-delete" below means
-- is_active = false on the master rows, deleted_at = NOW() on their clones — matching each
-- table's own mechanism, not a uniform deleted_at sweep.
--
-- PART 2's dedup check is changed from PAP's own file 56/58 version (which has no deleted_at
-- filter on its EXISTS check, so it treats an old soft-deleted clone as "already present" and
-- would skip creating the fresh one) to explicitly require `deleted_at IS NULL` — otherwise, right
-- after this file's own STEP 1 soft-deletes the old clones, PART 2 would see them as existing and
-- silently skip re-creating them, leaving PT_ENTRAINMENT programs with zero live clones.
--
-- Naming/merge-field convention: ENT's own (template_key suffixed by channel only, never
-- "_SEEKER"/"_RM"; merge fields like reg_fullname/course_name/getProgramField:name/
-- getRmContactUserName) — not PAP's or TAT's differing conventions. See prior research for the
-- exact provenance of every one of the 18 reproduced rows.
--
-- OPEN FLAGS — please confirm/fill in before running:
--   1. New WATI template names (2 rows) and new Zepto email template ids (2 rows) are literal
--      placeholders (TO_BE_UPDATED_...) — those templates don't exist in WATI/Zepto yet.
--   2. display_order for the 5 new rows continues at 57-61, after file 17's highest ENT value
--      (56) — no uniqueness constraint on this column, so it's a readability choice only.
--   3. This soft-deletes ALL 18 of file 17's rows, including its waitlist family — confirm
--      PT_ENTRAINMENT still wants waitlisting (program_type_v1.waitlist_applicable = true per
--      01program-type-refresh.sql, so yes, but flagging since this migration is the one place
--      that would need to change if that's ever revisited).
--   4. Idempotent — every INSERT uses the same `ON CONFLICT (template_key,
--      COALESCE(program_type_id, 0), template_type) DO UPDATE` pattern as file 17/PAP's files, so
--      re-running this file is safe.
-- ================================================

-- =====================================================
-- STEP 1: Soft-delete every currently-active PT_ENTRAINMENT communication template — master rows
-- (is_active = false) and their per-program clones (deleted_at = NOW()).
-- Each UPDATE is preceded by the equivalent SELECT (same WHERE clause) so the affected rows are
-- visible before they're mutated — review this output before trusting the UPDATE beneath it.
-- =====================================================

-- Preview: master rows about to be soft-deactivated
SELECT id, template_key, template_type, template_access_key, category, display_order, is_active
FROM communication_templates_master
WHERE program_type_id = (SELECT id FROM program_type_v1 WHERE key = 'PT_ENTRAINMENT')
  AND is_active = true
ORDER BY display_order;

UPDATE communication_templates_master
SET is_active = false, updated_at = CURRENT_TIMESTAMP
WHERE program_type_id = (SELECT id FROM program_type_v1 WHERE key = 'PT_ENTRAINMENT')
  AND is_active = true;

-- Preview: per-program clones about to be soft-deleted
SELECT h.id, h.program_id, h.template_key, h.template_type, h.template_access_key, h.is_enabled
FROM hdb_communication_templates h
WHERE h.deleted_at IS NULL
  AND h.program_id IN (
      SELECT p.id FROM program_v1 p
      JOIN program_type_v1 pt ON pt.id = p.program_type_id
      WHERE pt.key = 'PT_ENTRAINMENT' AND pt.deleted_at IS NULL AND p.deleted_at IS NULL
  )
ORDER BY h.program_id, h.template_key;

UPDATE hdb_communication_templates
SET deleted_at = CURRENT_TIMESTAMP, updated_at = CURRENT_TIMESTAMP
WHERE deleted_at IS NULL
  AND program_id IN (
      SELECT p.id FROM program_v1 p
      JOIN program_type_v1 pt ON pt.id = p.program_type_id
      WHERE pt.key = 'PT_ENTRAINMENT' AND pt.deleted_at IS NULL AND p.deleted_at IS NULL
  );

-- =====================================================
-- PREREQUISITE: run 05aentrainment-communications-enums.sql FIRST and let it commit.
-- It used to be inlined here as "PART 0", but Postgres rejects using a newly-ADDed enum value in
-- the same transaction that added it (error 55P04) — confirmed by actually running the combined
-- version of this file, which failed on 'TRAVEL_PLAN_CHANGE_EMAIL_SEEKER' for exactly that
-- reason. Split out into its own file, same as PAP's 55/57 vs 56/58 split.
-- =====================================================

-- =====================================================
-- PART 1: Master templates — 18 reproduced verbatim from file 17, then 5 new (57-61)
-- =====================================================

-- ---------- EMAIL (Zepto) ----------

-- 35. ENT Online Payment Confirmed Email
-- This is the "registration completed WITH payment" email — it must go out after payment, not at
-- registration submit. File 17 had it on REGISTRATION_COMPLETED_EMAIL_SEEKER, whose only
-- payment-time send site (PaymentService.sendRegistrationConfirmationAtPayment) is commented out,
-- while its registration-time site is gated off for ENT precisely because ENT is meant to send at
-- payment (registration.service.ts:1340, `!(requiresPayment && !requiresApproval)`). Re-keyed here
-- onto ONLINE_PAYMENT_ACKNOWLEDGEMENT_EMAIL_SEEKER, which PaymentService.sendOnlinePaymentAckEmail
-- fires on online capture and which is live today. The offline half of the pair is row 36
-- (PAYMENT_ACKNOWLEDGEMENT_OFFLINE_EMAIL_SEEKER), same content, already correct.
--
-- This row absorbs what was drafted separately as row 57 — that row claimed this same access key
-- but carried TO_BE_UPDATED_... placeholders, whereas this one has the real Zepto ids. Two master
-- rows sharing one access key for one program type would make findByProgramAndAccessKey (a
-- findOne) pick between them arbitrarily, so they are merged rather than both kept.
INSERT INTO communication_templates_master (
    template_key, template_name, template_type, template_id, sandbox_template_id,
    category, template_usage_type, template_access_key, program_type_id, merge_field_map, display_order, is_active
) VALUES (
    'ENT_ONLINE_PAYMENT_ACKNOWLEDGEMENT_EMAIL',
    'ENT Online Payment Confirmed - Email',
    'email',
    '2d6f.2a60732b5ed84938.k1.601e64d0-5cc6-11f0-b019-5254001dc20d.197ef51039d',
    '2d6f.2a60732b5ed84938.k1.78b75710-58cc-11f1-91bc-765e7256bde4.19e62facf01',
    'online_payment_comfirmed',
    'general',
    'ONLINE_PAYMENT_ACKNOWLEDGEMENT_EMAIL_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_ENTRAINMENT'),
    '[
        {"keyName": "reg_fullname", "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "dataType": "string", "isNullable": false},
        {"keyName": "reg_email", "sourceTable": "hdb_program_registration", "sourceColumn": "email_address", "dataType": "string", "isNullable": false},
        {"keyName": "reg_mobile", "sourceTable": "hdb_program_registration", "sourceColumn": "mobile_number", "dataType": "string", "isNullable": false},
        {"keyName": "reg_number", "sourceTable": "hdb_program_registration", "sourceColumn": "registration_seq_number", "dataType": "string", "isNullable": false},
        {"keyName": "course_name", "sourceTable": "computed", "sourceColumn": "getProgramField:name", "dataType": "string", "isNullable": false},
        {"keyName": "prg_venue", "sourceTable": "computed", "sourceColumn": "getAllocatedProgramField:venue_name_in_emails", "dataType": "string", "isNullable": true},
        {"keyName": "Email_Data_Program_Date", "sourceTable": "computed", "sourceColumn": "formatEntrainmentProgramDateRange", "dataType": "string", "formatType": "custom:full-date", "isNullable": false},
        {"keyName": "Email_Data_Checkin_and_Checkout", "sourceTable": "computed", "sourceColumn": "getCheckInOut", "dataType": "string", "isNullable": true},
        {"keyName": "pay_method", "sourceTable": "hdb_registration_payment_detail", "sourceColumn": "payment_mode", "dataType": "string", "formatType": "conditional", "isNullable": true}
    ]'::jsonb,
    35,
    true
) ON CONFLICT (template_key, COALESCE(program_type_id, 0), template_type) DO UPDATE SET
    template_id = EXCLUDED.template_id,
    sandbox_template_id = EXCLUDED.sandbox_template_id,
    template_access_key = EXCLUDED.template_access_key,
    merge_field_map = EXCLUDED.merge_field_map,
    is_active = true,
    updated_at = CURRENT_TIMESTAMP;

-- 36. ENT Offline Payment Acknowledgement Email
INSERT INTO communication_templates_master (
    template_key, template_name, template_type, template_id, sandbox_template_id,
    category, template_usage_type, template_access_key, program_type_id, merge_field_map, display_order, is_active
) VALUES (
    'ENT_OFFLINE_PAYMENT_ACKNOWLEDGEMENT_EMAIL',
    'ENT Offline Payment Acknowledgement - Email',
    'email',
    '2d6f.2a60732b5ed84938.k1.63287b10-5cc7-11f0-b019-5254001dc20d.197ef57a541',
    '2d6f.2a60732b5ed84938.k1.b3dc06b0-58cc-11f1-91bc-765e7256bde4.19e62fc529b',
    'payment_offline',
    'general',
    'PAYMENT_ACKNOWLEDGEMENT_OFFLINE_EMAIL_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_ENTRAINMENT'),
    '[
        {"keyName": "reg_fullname", "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "dataType": "string", "isNullable": false},
        {"keyName": "reg_email", "sourceTable": "hdb_program_registration", "sourceColumn": "email_address", "dataType": "string", "isNullable": false},
        {"keyName": "reg_mobile", "sourceTable": "hdb_program_registration", "sourceColumn": "mobile_number", "dataType": "string", "isNullable": false},
        {"keyName": "reg_number", "sourceTable": "hdb_program_registration", "sourceColumn": "registration_seq_number", "dataType": "string", "isNullable": false},
        {"keyName": "course_name", "sourceTable": "computed", "sourceColumn": "getProgramField:name", "dataType": "string", "isNullable": false},
        {"keyName": "prg_venue", "sourceTable": "computed", "sourceColumn": "getAllocatedProgramField:venue_name_in_emails", "dataType": "string", "isNullable": true},
        {"keyName": "Email_Data_Program_Date", "sourceTable": "computed", "sourceColumn": "formatEntrainmentProgramDateRange", "dataType": "string", "formatType": "custom:full-date", "isNullable": false},
        {"keyName": "Email_Data_Checkin_and_Checkout", "sourceTable": "computed", "sourceColumn": "getCheckInOut", "dataType": "string", "isNullable": true},
        {"keyName": "pay_method", "sourceTable": "hdb_registration_payment_detail", "sourceColumn": "payment_mode", "dataType": "string", "formatType": "conditional", "isNullable": true}
    ]'::jsonb,
    36,
    true
) ON CONFLICT (template_key, COALESCE(program_type_id, 0), template_type) DO UPDATE SET
    template_id = EXCLUDED.template_id,
    sandbox_template_id = EXCLUDED.sandbox_template_id,
    template_access_key = EXCLUDED.template_access_key,
    merge_field_map = EXCLUDED.merge_field_map,
    is_active = true,
    updated_at = CURRENT_TIMESTAMP;

-- 37. ENT Payment Link Email
-- The admin-triggered nudge for seekers who submitted their basic details and never came back to
-- pay (POST -> RegistrationService.sendPaymentLinkCommunicationBulk). Paired with WhatsApp row #50.
--
-- Was keyed BLESSED_EMAIL_SEEKER — the approval / seat-offered trigger fired from
-- RegistrationApprovalService. PT_ENTRAINMENT is requires_approval = false, so that send site
-- never runs for ENT and this email had no live trigger at all.
--
-- Now on PAYMENT_LINK_EMAIL_SEEKER, the email counterpart of row #50's PAYMENT_LINK_WATI_SEEKER
-- — following the {PURPOSE}_{CHANNEL}_{RECIPIENT} convention every other channel pair in the
-- enum uses. This key is NEW: added to src/common/enum/communication-template-access-key.enum.ts
-- and to 05a's ALTER TYPE list alongside this migration.
--
-- This was not a data-only change: sendPaymentLinkCommunication was WhatsApp-only despite its
-- docstring claiming "email + WhatsApp". An email send was added alongside this migration —
-- seeker only, coordinators keep the WhatsApp mirror. The send site is shared, so any program
-- type that seeds an email row on this key now gets the email too.
--
-- Merge fields unchanged — reg_fullname / course_name / link (generatePaymentLink) were already
-- correct for this message.
INSERT INTO communication_templates_master (
    template_key, template_name, template_type, template_id, sandbox_template_id,
    category, template_usage_type, template_access_key, program_type_id, merge_field_map, display_order, is_active
) VALUES (
    'ENT_PAYMENT_LINK_EMAIL',
    'ENT Payment Link - Email',
    'email',
    '2d6f.2a60732b5ed84938.k1.c20f5ce0-5c93-11f0-b019-5254001dc20d.197ee0549ae',
    '2d6f.2a60732b5ed84938.k1.d90eb930-58e2-11f1-91bc-765e7256bde4.19e638d7443',
    'payment_link',
    'general',
    'PAYMENT_LINK_EMAIL_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_ENTRAINMENT'),
    '[
        {"keyName": "reg_fullname", "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "dataType": "string", "isNullable": false},
        {"keyName": "course_name", "sourceTable": "computed", "sourceColumn": "getProgramField:name", "dataType": "string", "isNullable": false},
        {"keyName": "link", "sourceTable": "computed", "sourceColumn": "generatePaymentLink", "dataType": "string", "formatType": "url", "isNullable": false}
    ]'::jsonb,
    37,
    true
) ON CONFLICT (template_key, COALESCE(program_type_id, 0), template_type) DO UPDATE SET
    template_id = EXCLUDED.template_id,
    sandbox_template_id = EXCLUDED.sandbox_template_id,
    template_access_key = EXCLUDED.template_access_key,
    merge_field_map = EXCLUDED.merge_field_map,
    is_active = true,
    updated_at = CURRENT_TIMESTAMP;

-- 38. ENT Travel Plan Email
INSERT INTO communication_templates_master (
    template_key, template_name, template_type, template_id, sandbox_template_id,
    category, template_usage_type, template_access_key, program_type_id, merge_field_map, display_order, is_active
) VALUES (
    'ENT_TRAVEL_PLAN_EMAIL',
    'ENT Travel Plan - Email',
    'email',
    '2d6f.2a60732b5ed84938.k1.db7b9ef0-5cc5-11f0-b019-5254001dc20d.197ef4d9e5f',
    '2d6f.2a60732b5ed84938.k1.3e53b610-58e3-11f1-91bc-765e7256bde4.19e63900bf1',
    'travel_plan_change',
    'general',
    'TRAVEL_PLAN_CHANGE_EMAIL_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_ENTRAINMENT'),
    '[
        {"keyName": "reg_fullname", "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "dataType": "string", "isNullable": false},
        {"keyName": "course_name", "sourceTable": "computed", "sourceColumn": "getProgramField:name", "dataType": "string", "isNullable": false},
        {"keyName": "link", "sourceTable": "computed", "sourceColumn": "generateTravelPlanLink", "dataType": "string", "formatType": "url", "isNullable": false}
    ]'::jsonb,
    38,
    true
) ON CONFLICT (template_key, COALESCE(program_type_id, 0), template_type) DO UPDATE SET
    template_id = EXCLUDED.template_id,
    sandbox_template_id = EXCLUDED.sandbox_template_id,
    template_access_key = EXCLUDED.template_access_key,
    merge_field_map = EXCLUDED.merge_field_map,
    is_active = true,
    updated_at = CURRENT_TIMESTAMP;

-- 39. ENT Waitlist Release Payment Email
INSERT INTO communication_templates_master (
    template_key, template_name, template_type, template_id, sandbox_template_id,
    category, template_usage_type, template_access_key, program_type_id, merge_field_map, display_order, is_active
) VALUES (
    'ENT_WAITLIST_RELEASE_PAYMENT_EMAIL',
    'ENT Waitlist Release (Payment) - Email',
    'email',
    '2d6f.2a60732b5ed84938.k1.4ae8c790-5c94-11f0-b019-5254001dc20d.197ee08ca89',
    '2d6f.2a60732b5ed84938.k1.94ee6d31-58e3-11f1-91bc-765e7256bde4.19e63924383',
    'waitlist_release',
    'general',
    'WAITLIST_RELEASE_PAYMENT_EMAIL_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_ENTRAINMENT'),
    '[
        {"keyName": "reg_fullname", "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "dataType": "string", "isNullable": false},
        {"keyName": "course_name", "sourceTable": "computed", "sourceColumn": "getProgramField:name", "dataType": "string", "isNullable": false},
        {"keyName": "link", "sourceTable": "computed", "sourceColumn": "generatePaymentLink", "dataType": "string", "formatType": "url", "isNullable": false}
    ]'::jsonb,
    39,
    true
) ON CONFLICT (template_key, COALESCE(program_type_id, 0), template_type) DO UPDATE SET
    template_id = EXCLUDED.template_id,
    sandbox_template_id = EXCLUDED.sandbox_template_id,
    template_access_key = EXCLUDED.template_access_key,
    merge_field_map = EXCLUDED.merge_field_map,
    is_active = true,
    updated_at = CURRENT_TIMESTAMP;

-- 40. ENT Waitlist Denied Email
INSERT INTO communication_templates_master (
    template_key, template_name, template_type, template_id, sandbox_template_id,
    category, template_usage_type, template_access_key, program_type_id, merge_field_map, display_order, is_active
) VALUES (
    'ENT_WAITLIST_DENIED_EMAIL',
    'ENT Waitlist Denied - Email',
    'email',
    '2d6f.2a60732b5ed84938.k1.cb1dfa20-5c94-11f0-b019-5254001dc20d.197ee0c12c2',
    '2d6f.2a60732b5ed84938.k1.b70f9bf0-58e3-11f1-91bc-765e7256bde4.19e6393232f',
    'waitlist_denied',
    'general',
    'WAITLIST_DENIED_EMAIL_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_ENTRAINMENT'),
    '[
        {"keyName": "reg_name", "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "dataType": "string", "isNullable": false},
        {"keyName": "program_name", "sourceTable": "computed", "sourceColumn": "getProgramField:name", "dataType": "string", "isNullable": false}
    ]'::jsonb,
    42,
    true
) ON CONFLICT (template_key, COALESCE(program_type_id, 0), template_type) DO UPDATE SET
    template_id = EXCLUDED.template_id,
    sandbox_template_id = EXCLUDED.sandbox_template_id,
    template_access_key = EXCLUDED.template_access_key,
    merge_field_map = EXCLUDED.merge_field_map,
    is_active = true,
    updated_at = CURRENT_TIMESTAMP;

-- 43. ENT Waitlist Denied (Paid) Email
INSERT INTO communication_templates_master (
    template_key, template_name, template_type, template_id, sandbox_template_id,
    category, template_usage_type, template_access_key, program_type_id, merge_field_map, display_order, is_active
) VALUES (
    'ENT_WAITLIST_DENIED_PAID_EMAIL',
    'ENT Waitlist Denied (Paid) - Email',
    'email',
    '2d6f.2a60732b5ed84938.k1.29ff3901-5c95-11f0-b019-5254001dc20d.197ee0e808a',
    '2d6f.2a60732b5ed84938.k1.dc7ded20-58d3-11f1-91bc-765e7256bde4.19e632b3cf2',
    'waitlist_denied',
    'general',
    'WAITLIST_DENIED_PAID_EMAIL_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_ENTRAINMENT'),
    '[
        {"keyName": "reg_name", "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "dataType": "string", "isNullable": false},
        {"keyName": "program_name", "sourceTable": "computed", "sourceColumn": "getProgramField:name", "dataType": "string", "isNullable": false}
    ]'::jsonb,
    43,
    true
) ON CONFLICT (template_key, COALESCE(program_type_id, 0), template_type) DO UPDATE SET
    template_id = EXCLUDED.template_id,
    sandbox_template_id = EXCLUDED.sandbox_template_id,
    template_access_key = EXCLUDED.template_access_key,
    merge_field_map = EXCLUDED.merge_field_map,
    is_active = true,
    updated_at = CURRENT_TIMESTAMP;

-- ---------- WHATSAPP (WATI) ----------

-- 44. ENT Registration Completed WhatsApp (Seeker)
-- WhatsApp counterpart to row 35's re-keyed email, so it shares that row's category
-- ('online_payment_comfirmed') rather than 'online_payment_completed' — the latter was never
-- added to communication_category_enum by 05a or any prior migration and fails with 22P02.
INSERT INTO communication_templates_master (
    template_key, template_name, template_type, template_id, sandbox_template_id,
    category, template_usage_type, template_access_key, program_type_id, merge_field_map, display_order, is_active
) VALUES (
    'ENT_ONLINE_PAYMENT_COMPLETED_WHATSAPP',
    'ENT Online Payment Completed - WhatsApp (Seeker)',
    'whatsapp',
    'erc_online_reg_confirmation_v1',
    '',
    'online_payment_comfirmed',
    'general',
    'ONLINE_PAYMENT_COMPLETED_WATI_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_ENTRAINMENT'),
    '[
        {"keyName": "reg_fullname", "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "dataType": "string", "isNullable": false},
        {"keyName": "course_name", "sourceTable": "computed", "sourceColumn": "getProgramField:name", "dataType": "string", "isNullable": false},
        {"keyName": "reg_number", "sourceTable": "hdb_program_registration", "sourceColumn": "registration_seq_number", "dataType": "string", "isNullable": false},
        {"keyName": "reg_email", "sourceTable": "hdb_program_registration", "sourceColumn": "email_address", "dataType": "string", "isNullable": false}
    ]'::jsonb,
    44,
    true
) ON CONFLICT (template_key, COALESCE(program_type_id, 0), template_type) DO UPDATE SET
    template_id = EXCLUDED.template_id,
    sandbox_template_id = EXCLUDED.sandbox_template_id,
    template_access_key = EXCLUDED.template_access_key,
    merge_field_map = EXCLUDED.merge_field_map,
    is_active = true,
    updated_at = CURRENT_TIMESTAMP;

-- 45. ENT Offline Registration Completed WhatsApp
INSERT INTO communication_templates_master (
    template_key, template_name, template_type, template_id, sandbox_template_id,
    category, template_usage_type, template_access_key, program_type_id, merge_field_map, display_order, is_active
) VALUES (
    'ENT_OFFLINE_REGISTRATION_COMPLETED_WHATSAPP',
    'ENT Offline Registration Completed - WhatsApp',
    'whatsapp',
    'erc_offline_provisional_v1',
    '',
    'payment_offline',
    'general',
    'PAYMENT_ACKNOWLEDGEMENT_OFFLINE_WATI_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_ENTRAINMENT'),
    '[
        {"keyName": "reg_fullname", "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "dataType": "string", "isNullable": false},
        {"keyName": "course_name", "sourceTable": "computed", "sourceColumn": "getProgramField:name", "dataType": "string", "isNullable": false},
        {"keyName": "reg_number", "sourceTable": "hdb_program_registration", "sourceColumn": "registration_seq_number", "dataType": "string", "isNullable": false},
        {"keyName": "reg_email", "sourceTable": "hdb_program_registration", "sourceColumn": "email_address", "dataType": "string", "isNullable": false}
    ]'::jsonb,
    45,
    true
) ON CONFLICT (template_key, COALESCE(program_type_id, 0), template_type) DO UPDATE SET
    template_id = EXCLUDED.template_id,
    sandbox_template_id = EXCLUDED.sandbox_template_id,
    template_access_key = EXCLUDED.template_access_key,
    merge_field_map = EXCLUDED.merge_field_map,
    is_active = true,
    updated_at = CURRENT_TIMESTAMP;

-- 46. ENT RM Registration Completed WhatsApp
INSERT INTO communication_templates_master (
    template_key, template_name, template_type, template_id, sandbox_template_id,
    category, template_usage_type, template_access_key, program_type_id, merge_field_map, display_order, is_active
) VALUES (
    'ENT_RM_REGISTRATION_COMPLETED_WHATSAPP',
    'ENT RM Registration Completed - WhatsApp',
    'whatsapp',
    'erc_online_reg_rm_update',
    '',
    'registration_completed',
    'general',
    'REGISTRATION_COMPLETED_WATI_RM',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_ENTRAINMENT'),
    '[
        {"keyName": "participant_name", "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "dataType": "string", "isNullable": false},
        {"keyName": "course_name", "sourceTable": "computed", "sourceColumn": "getProgramField:name", "dataType": "string", "isNullable": false},
        {"keyName": "rm_name", "sourceTable": "computed", "sourceColumn": "getRmContactUserName", "dataType": "string", "isNullable": false},
        {"keyName": "participant_email", "sourceTable": "hdb_program_registration", "sourceColumn": "email_address", "dataType": "string", "isNullable": false},
        {"keyName": "participant_mobile", "sourceTable": "hdb_program_registration", "sourceColumn": "mobile_number", "dataType": "string", "isNullable": false},
        {"keyName": "payment_mode", "sourceTable": "hdb_registration_payment_detail", "sourceColumn": "payment_mode", "dataType": "string", "isNullable": true},
        {"keyName": "order_id", "sourceTable": "hdb_registration_payment_detail", "sourceColumn": "order_id", "dataType": "string", "isNullable": true}
    ]'::jsonb,
    46,
    true
) ON CONFLICT (template_key, COALESCE(program_type_id, 0), template_type) DO UPDATE SET
    template_id = EXCLUDED.template_id,
    sandbox_template_id = EXCLUDED.sandbox_template_id,
    template_access_key = EXCLUDED.template_access_key,
    merge_field_map = EXCLUDED.merge_field_map,
    is_active = true,
    updated_at = CURRENT_TIMESTAMP;

-- 47. ENT Waitlist Registration WhatsApp
INSERT INTO communication_templates_master (
    template_key, template_name, template_type, template_id, sandbox_template_id,
    category, template_usage_type, template_access_key, program_type_id, merge_field_map, display_order, is_active
) VALUES (
    'ENT_WAITLIST_REGISTRATION_WHATSAPP',
    'ENT Waitlist Registration - WhatsApp',
    'whatsapp',
    'erc_reg_waitlist_trigger_v3',
    '',
    'waitlisted',
    'general',
    'REGISTRATION_WAITINGLIST_WATI_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_ENTRAINMENT'),
    '[
        {"keyName": "reg_name", "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "dataType": "string", "isNullable": false},
        {"keyName": "prg_batch", "sourceTable": "computed", "sourceColumn": "getProgramField:name", "dataType": "string", "isNullable": false}
    ]'::jsonb,
    47,
    true
) ON CONFLICT (template_key, COALESCE(program_type_id, 0), template_type) DO UPDATE SET
    template_id = EXCLUDED.template_id,
    sandbox_template_id = EXCLUDED.sandbox_template_id,
    template_access_key = EXCLUDED.template_access_key,
    merge_field_map = EXCLUDED.merge_field_map,
    is_active = true,
    updated_at = CURRENT_TIMESTAMP;

-- 48. ENT Offline Registration Waitinglist WhatsApp
INSERT INTO communication_templates_master (
    template_key, template_name, template_type, template_id, sandbox_template_id,
    category, template_usage_type, template_access_key, program_type_id, merge_field_map, display_order, is_active
) VALUES (
    'ENT_OFFLINE_REGISTRATION_WAITINGLIST_WHATSAPP',
    'ENT Offline Registration Waitinglist - WhatsApp',
    'whatsapp',
    'erc_waiting_list_after_offline_pament_v3',
    '',
    'waitlisted',
    'general',
    'OFFLINE_REGISTRATION_WAITINGLIST_WATI_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_ENTRAINMENT'),
    '[
        {"keyName": "reg_name", "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "dataType": "string", "isNullable": false},
        {"keyName": "prg_batch", "sourceTable": "computed", "sourceColumn": "getProgramField:name", "dataType": "string", "isNullable": false},
        {"keyName": "reg_orderid", "sourceTable": "hdb_registration_payment_detail", "sourceColumn": "order_id", "dataType": "string", "isNullable": true}
    ]'::jsonb,
    48,
    true
) ON CONFLICT (template_key, COALESCE(program_type_id, 0), template_type) DO UPDATE SET
    template_id = EXCLUDED.template_id,
    sandbox_template_id = EXCLUDED.sandbox_template_id,
    template_access_key = EXCLUDED.template_access_key,
    merge_field_map = EXCLUDED.merge_field_map,
    is_active = true,
    updated_at = CURRENT_TIMESTAMP;

-- 49. ENT Online Payment Waitlisted WhatsApp
INSERT INTO communication_templates_master (
    template_key, template_name, template_type, template_id, sandbox_template_id,
    category, template_usage_type, template_access_key, program_type_id, merge_field_map, display_order, is_active
) VALUES (
    'ENT_ONLINE_PAYMENT_WAITLISTED_WHATSAPP',
    'ENT Online Payment Waitlisted - WhatsApp',
    'whatsapp',
    'erc_online_paid_but_waitlist_v1',
    '',
    'waitlisted',
    'general',
    'ONLINE_PAYMENT_DONE_BUT_WAITING_LIST_WATI_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_ENTRAINMENT'),
    '[
        {"keyName": "reg_name", "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "dataType": "string", "isNullable": false},
        {"keyName": "course_name", "sourceTable": "computed", "sourceColumn": "getProgramField:name", "dataType": "string", "isNullable": false},
        {"keyName": "reg_orderid", "sourceTable": "hdb_registration_payment_detail", "sourceColumn": "order_id", "dataType": "string", "isNullable": true}
    ]'::jsonb,
    49,
    true
) ON CONFLICT (template_key, COALESCE(program_type_id, 0), template_type) DO UPDATE SET
    template_id = EXCLUDED.template_id,
    sandbox_template_id = EXCLUDED.sandbox_template_id,
    template_access_key = EXCLUDED.template_access_key,
    merge_field_map = EXCLUDED.merge_field_map,
    is_active = true,
    updated_at = CURRENT_TIMESTAMP;

-- 50. ENT Payment Link WhatsApp
-- FIXED (was BLESSED_WATI_SEEKER / 'hdb_blessed' in file 17 — a copy-paste mismatch from HDB's
-- approval vocabulary). PT_ENTRAINMENT.requires_approval = false, same as PT_TAT, and confirmed
-- by reading the actual send-sites: BLESSED_* only fires from manual admin approval/resend-
-- invoice/swap-accept actions (registration-approval.service.ts, program-registration.service.ts)
-- that an ENT registration's normal "seat confirmed -> payment pending" journey never passes
-- through — it is NOT evidence of a real approval-gated flow for ENT. TAT hit the exact same
-- need and added a dedicated PAYMENT_LINK_WATI_SEEKER access key + payment_link category
-- (database/program-configuration/18_master_seed_data_tat.sql:192-195) specifically for this,
-- wired to the real send-site: RegistrationService.sendPaymentLinkCommunication /
-- sendPaymentLinkCommunicationBulk (registration.service.ts:13825,13959), exposed via the admin
-- bulk action POST /registration/send-payment-links — which looks up templates by
-- PAYMENT_LINK_WATI_SEEKER, not BLESSED_WATI_SEEKER, so ENT's row was actually unreachable by
-- that action before this fix. Switched to match TAT exactly.
INSERT INTO communication_templates_master (
    template_key, template_name, template_type, template_id, sandbox_template_id,
    category, template_usage_type, template_access_key, program_type_id, merge_field_map, display_order, is_active
) VALUES (
    'ENT_PAYMENT_LINK_WHATSAPP',
    'ENT Payment Link - WhatsApp',
    'whatsapp',
    'erc_entrainment_payment_link_v3',
    '',
    'payment_link',
    'general',
    'PAYMENT_LINK_WATI_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_ENTRAINMENT'),
    '[
        {"keyName": "reg_name", "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "dataType": "string", "isNullable": false},
        {"keyName": "course_name", "sourceTable": "computed", "sourceColumn": "getProgramField:name", "dataType": "string", "isNullable": false},
        {"keyName": "link", "sourceTable": "computed", "sourceColumn": "generatePaymentLink", "dataType": "string", "formatType": "url", "isNullable": false},
        {"keyName": "reg_rm", "sourceTable": "computed", "sourceColumn": "getRmContactUserName", "dataType": "string", "isNullable": true},
        {"keyName": "reg_mobile", "sourceTable": "hdb_program_registration", "sourceColumn": "mobile_number", "dataType": "string", "isNullable": false},
        {"keyName": "reg_email", "sourceTable": "hdb_program_registration", "sourceColumn": "email_address", "dataType": "string", "isNullable": false}
    ]'::jsonb,
    50,
    true
) ON CONFLICT (template_key, COALESCE(program_type_id, 0), template_type) DO UPDATE SET
    template_id = EXCLUDED.template_id,
    sandbox_template_id = EXCLUDED.sandbox_template_id,
    template_access_key = EXCLUDED.template_access_key,
    merge_field_map = EXCLUDED.merge_field_map,
    is_active = true,
    updated_at = CURRENT_TIMESTAMP;

-- 51. ENT Offline Payment Done WhatsApp (Invoice) ---- need to check
INSERT INTO communication_templates_master (
    template_key, template_name, template_type, template_id, sandbox_template_id,
    category, template_usage_type, template_access_key, program_type_id, merge_field_map, display_order, is_active
) VALUES (
    'ENT_OFFLINE_PAYMENT_DONE_WHATSAPP',
    'ENT Offline Payment Done - WhatsApp',
    'whatsapp',
    'erc_offline_registration_complete',
    '',
    'invoice',
    'general',
    'INVOICE_WATI_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_ENTRAINMENT'),
    '[
        {"keyName": "reg_fullname", "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "dataType": "string", "isNullable": false},
        {"keyName": "course_name", "sourceTable": "computed", "sourceColumn": "getProgramField:name", "dataType": "string", "isNullable": false},
        {"keyName": "reg_number", "sourceTable": "hdb_program_registration", "sourceColumn": "registration_seq_number", "dataType": "string", "isNullable": false},
        {"keyName": "reg_email", "sourceTable": "hdb_program_registration", "sourceColumn": "email_address", "dataType": "string", "isNullable": false}
    ]'::jsonb,
    51,
    true
) ON CONFLICT (template_key, COALESCE(program_type_id, 0), template_type) DO UPDATE SET
    template_id = EXCLUDED.template_id,
    sandbox_template_id = EXCLUDED.sandbox_template_id,
    template_access_key = EXCLUDED.template_access_key,
    merge_field_map = EXCLUDED.merge_field_map,
    is_active = true,
    updated_at = CURRENT_TIMESTAMP;

-- 52. ENT Waitlist Release Payment WhatsApp
INSERT INTO communication_templates_master (
    template_key, template_name, template_type, template_id, sandbox_template_id,
    category, template_usage_type, template_access_key, program_type_id, merge_field_map, display_order, is_active
) VALUES (
    'ENT_WAITLIST_RELEASE_PAYMENT_WHATSAPP',
    'ENT Waitlist Release (Payment) - WhatsApp',
    'whatsapp',
    'erc_entrainment_waitinglist_release_prod_v5',
    '',
    'waitlist_release',
    'general',
    'WAITLIST_RELEASE_PAYMENT_WATI_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_ENTRAINMENT'),
    '[
        {"keyName": "reg_name", "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "dataType": "string", "isNullable": false},
        {"keyName": "course_name", "sourceTable": "computed", "sourceColumn": "getProgramField:name", "dataType": "string", "isNullable": false},
        {"keyName": "link", "sourceTable": "computed", "sourceColumn": "generatePaymentLink", "dataType": "string", "formatType": "url", "isNullable": false}
    ]'::jsonb,
    52,
    true
) ON CONFLICT (template_key, COALESCE(program_type_id, 0), template_type) DO UPDATE SET
    template_id = EXCLUDED.template_id,
    sandbox_template_id = EXCLUDED.sandbox_template_id,
    template_access_key = EXCLUDED.template_access_key,
    merge_field_map = EXCLUDED.merge_field_map,
    is_active = true,
    updated_at = CURRENT_TIMESTAMP;

-- 53. ENT Waitlist Deny WhatsApp
INSERT INTO communication_templates_master (
    template_key, template_name, template_type, template_id, sandbox_template_id,
    category, template_usage_type, template_access_key, program_type_id, merge_field_map, display_order, is_active
) VALUES (
    'ENT_WAITLIST_DENY_WHATSAPP',
    'ENT Waitlist Deny - WhatsApp',
    'whatsapp',
    'erc_entrainment_wailist_deny_v2_25_v1',
    '',
    'waitlist_denied',
    'general',
    'WAITLIST_DENY_WATI_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_ENTRAINMENT'),
    '[
        {"keyName": "reg_name", "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "dataType": "string", "isNullable": false},
        {"keyName": "program_name", "sourceTable": "computed", "sourceColumn": "getProgramField:name", "dataType": "string", "isNullable": false}
    ]'::jsonb,
    55,
    true
) ON CONFLICT (template_key, COALESCE(program_type_id, 0), template_type) DO UPDATE SET
    template_id = EXCLUDED.template_id,
    sandbox_template_id = EXCLUDED.sandbox_template_id,
    template_access_key = EXCLUDED.template_access_key,
    merge_field_map = EXCLUDED.merge_field_map,
    is_active = true,
    updated_at = CURRENT_TIMESTAMP;

-- 56. ENT Waitlist Paid Deny WhatsApp
INSERT INTO communication_templates_master (
    template_key, template_name, template_type, template_id, sandbox_template_id,
    category, template_usage_type, template_access_key, program_type_id, merge_field_map, display_order, is_active
) VALUES (
    'ENT_WAITLIST_PAID_DENY_WHATSAPP',
    'ENT Waitlist Paid Deny - WhatsApp',
    'whatsapp',
    'erc_entrainment_payment_wailist_deny_v2_25_v1',
    '',
    'waitlist_denied',
    'general',
    'WAITLIST_PAID_DENY_WATI_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_ENTRAINMENT'),
    '[
        {"keyName": "reg_name", "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "dataType": "string", "isNullable": false},
        {"keyName": "program_name", "sourceTable": "computed", "sourceColumn": "getProgramField:name", "dataType": "string", "isNullable": false}
    ]'::jsonb,
    56,
    true
) ON CONFLICT (template_key, COALESCE(program_type_id, 0), template_type) DO UPDATE SET
    template_id = EXCLUDED.template_id,
    sandbox_template_id = EXCLUDED.sandbox_template_id,
    template_access_key = EXCLUDED.template_access_key,
    merge_field_map = EXCLUDED.merge_field_map,
    is_active = true,
    updated_at = CURRENT_TIMESTAMP;

-- ---------- NEW (5) — gap-fill vs PAP/TAT ----------


-- 59. ENT Invoice - Email (NEW — distinct from #51's WATI-only, offline-payment-scoped row)
INSERT INTO communication_templates_master (
    template_key, template_name, template_type, template_id, sandbox_template_id,
    category, template_usage_type, template_access_key, program_type_id, merge_field_map, display_order, is_active
) VALUES (
    'ENT_INVOICE_EMAIL',
    'ENT Invoice - Email',
    'email',
    '2d6f.2a60732b5ed84938.k1.84a416a0-5ca9-11f0-b019-5254001dc20d.197ee93e50a',
    '2d6f.2a60732b5ed84938.k1.6cd350e0-960f-11f1-b787-525400114fe6.19ff4769cee',
    'invoice',
    'general',
    'INVOICE_EMAIL_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_ENTRAINMENT'),
    '[
        {"keyName": "prg_venue", "sourceTable": "computed", "sourceColumn": "getAllocatedProgramField:venue_name_in_emails", "dataType": "string", "isNullable": true},
        {"keyName": "firstPara", "sourceTable": "computed", "sourceColumn": "firstPara", "dataType": "string", "isNullable": true},
        {"keyName": "prg_coursename", "sourceTable": "computed", "sourceColumn": "getProgramField:name", "dataType": "string", "isNullable": false},
        {"keyName": "reg_fullname", "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "dataType": "string", "isNullable": false},
        {"keyName": "invoice_number", "sourceTable": "hdb_registration_invoice_detail", "sourceColumn": "invoice_sequence_number", "dataType": "string", "isNullable": false},
        {"keyName": "prg_programdates", "sourceTable": "computed", "sourceColumn": "formatEntrainmentProgramDateRange", "dataType": "string", "formatType": "custom:full-date", "isNullable": false},
        {"keyName": "prg_checkin_checkout", "sourceTable": "computed", "sourceColumn": "getCheckInOut", "dataType": "string", "isNullable": true}
    ]'::jsonb,
    59,
    true
) ON CONFLICT (template_key, COALESCE(program_type_id, 0), template_type) DO UPDATE SET
    template_id = EXCLUDED.template_id,
    sandbox_template_id = EXCLUDED.sandbox_template_id,
    template_access_key = EXCLUDED.template_access_key,
    merge_field_map = EXCLUDED.merge_field_map,
    is_active = true,
    updated_at = CURRENT_TIMESTAMP;


-- 61. ENT E-Invoice Error - Email (NEW — TAT-parity, mirrors file 23)
INSERT INTO communication_templates_master (
    template_key, template_name, template_type, template_id, sandbox_template_id,
    category, template_usage_type, template_access_key, program_type_id, merge_field_map, display_order, is_active
) VALUES (
    'ENT_EINVOICE_ERROR_EMAIL',
    'ENT E-Invoice Error - Email',
    'email',
    '2d6f.2a60732b5ed84938.k1.f0cbe980-960e-11f1-b787-525400114fe6.19ff4737018',
    '2d6f.2a60732b5ed84938.k1.fe658291-960e-11f1-b787-525400114fe6.19ff473c936',
    'invoice',
    'general',
    'EINVOICE_ERROR_EMAIL_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_ENTRAINMENT'),
    '[
        {"keyName": "Error_Code", "sourceTable": "hdb_registration_invoice_detail", "sourceColumn": "error_full_json->>''code''", "dataType": "string", "isNullable": true},
        {"keyName": "Error_Message", "sourceTable": "hdb_registration_invoice_detail", "sourceColumn": "error_message", "dataType": "string", "isNullable": true},
        {"keyName": "Invoice_Number", "sourceTable": "hdb_registration_invoice_detail", "sourceColumn": "invoice_sequence_number", "dataType": "string", "isNullable": true},
        {"keyName": "Invoice_Value", "sourceTable": "computed", "sourceColumn": "calculateTotalPaymentAmount", "dataType": "number", "isNullable": true},
        {"keyName": "Invoice_Date", "sourceTable": "hdb_registration_payment_detail", "sourceColumn": "payment_date", "dataType": "date", "formatType": "short", "isNullable": true},
        {"keyName": "Customer_GSTIN", "sourceTable": "hdb_registration_invoice_detail", "sourceColumn": "gst_number", "dataType": "string", "isNullable": true},
        {"keyName": "Seeker_Name", "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "dataType": "string", "isNullable": false}
    ]'::jsonb,
    61,
    true
) ON CONFLICT (template_key, COALESCE(program_type_id, 0), template_type) DO UPDATE SET
    template_id = EXCLUDED.template_id,
    sandbox_template_id = EXCLUDED.sandbox_template_id,
    template_access_key = EXCLUDED.template_access_key,
    merge_field_map = EXCLUDED.merge_field_map,
    is_active = true,
    updated_at = CURRENT_TIMESTAMP;

-- =====================================================
-- PART 2: Clone all 23 master rows to every existing PT_ENTRAINMENT program and expand each
-- merge_field_map into merge_info_answer_location_map. Dedup check requires deleted_at IS NULL
-- (fixed relative to PAP's own file 56/58 pattern — see header note) so this actually recreates
-- clones for the rows STEP 1 just soft-deleted, rather than skipping them as "already present".
-- =====================================================
DO $$
DECLARE
    ent_keys TEXT[] := ARRAY[
        'ENT_OFFLINE_PAYMENT_ACKNOWLEDGEMENT_EMAIL',
        'ENT_PAYMENT_LINK_EMAIL',
        'ENT_TRAVEL_PLAN_EMAIL',
        'ENT_WAITLIST_RELEASE_PAYMENT_EMAIL',
        'ENT_WAITLIST_DENIED_EMAIL',
        'ENT_WAITLIST_DENIED_PAID_EMAIL',
        'ENT_OFFLINE_REGISTRATION_COMPLETED_WHATSAPP',
        'ENT_RM_REGISTRATION_COMPLETED_WHATSAPP',
        'ENT_WAITLIST_REGISTRATION_WHATSAPP',
        'ENT_OFFLINE_REGISTRATION_WAITINGLIST_WHATSAPP',
        'ENT_ONLINE_PAYMENT_WAITLISTED_WHATSAPP',
        'ENT_PAYMENT_LINK_WHATSAPP',
        'ENT_OFFLINE_PAYMENT_DONE_WHATSAPP',
        'ENT_WAITLIST_RELEASE_PAYMENT_WHATSAPP',
        'ENT_WAITLIST_DENY_WHATSAPP',
        'ENT_WAITLIST_PAID_DENY_WHATSAPP',
        'ENT_ONLINE_PAYMENT_ACKNOWLEDGEMENT_EMAIL',
        'ENT_ONLINE_PAYMENT_COMPLETED_WHATSAPP',
        'ENT_INVOICE_EMAIL',
        'ENT_EINVOICE_ERROR_EMAIL'
    ];
    ent_key         TEXT;
    master_rec      RECORD;
    prog_rec        RECORD;
    merge_field     JSONB;
    new_template_id BIGINT;
    cloned_count    INT := 0;
    skipped_count   INT := 0;
BEGIN
    FOREACH ent_key IN ARRAY ent_keys LOOP
        SELECT id, template_id, template_name, template_key, template_type,
               sandbox_template_id, category, template_usage_type,
               template_access_key, merge_field_map, display_order
        INTO master_rec
        FROM communication_templates_master
        WHERE template_key = ent_key AND is_active = true
        LIMIT 1;

        IF master_rec IS NULL THEN
            RAISE EXCEPTION 'Master template % not found — run PART 1 first', ent_key;
        END IF;

        FOR prog_rec IN
            SELECT p.id AS program_id
            FROM program_v1 p
            JOIN program_type_v1 pt ON pt.id = p.program_type_id
            WHERE pt.key        = 'PT_ENTRAINMENT'
              AND pt.deleted_at IS NULL
              AND p.deleted_at  IS NULL
            ORDER BY p.id
        LOOP
            IF EXISTS (
                SELECT 1 FROM hdb_communication_templates h
                WHERE h.program_id         = prog_rec.program_id
                  AND h.template_key::text = ent_key
                  AND h.template_type      = master_rec.template_type
                  AND h.deleted_at        IS NULL
            ) THEN
                skipped_count := skipped_count + 1;
                CONTINUE;
            END IF;

            INSERT INTO hdb_communication_templates (
                template_id, template_name, template_key, template_type,
                sandbox_template_id, category, template_usage_type, template_access_key,
                program_id, is_enabled, display_order, created_at, updated_at
            ) VALUES (
                master_rec.template_id, master_rec.template_name,
                master_rec.template_key::template_key_enum, master_rec.template_type,
                master_rec.sandbox_template_id, master_rec.category, master_rec.template_usage_type,
                master_rec.template_access_key::communication_template_access_key_enum,
                prog_rec.program_id, true, master_rec.display_order,
                CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
            )
            RETURNING id INTO new_template_id;

            IF master_rec.merge_field_map IS NOT NULL
               AND jsonb_array_length(master_rec.merge_field_map) > 0 THEN
                FOR merge_field IN SELECT * FROM jsonb_array_elements(master_rec.merge_field_map)
                LOOP
                    INSERT INTO merge_info_answer_location_map (
                        key_name, template_id, is_common, source_table, source_column,
                        data_type, format_type, is_nullable, default_value, description,
                        is_active, created_at, updated_at
                    ) VALUES (
                        merge_field->>'keyName',
                        new_template_id,
                        COALESCE((merge_field->>'isCommon')::boolean,   false),
                        merge_field->>'sourceTable',
                        merge_field->>'sourceColumn',
                        merge_field->>'dataType',
                        merge_field->>'formatType',
                        COALESCE((merge_field->>'isNullable')::boolean, true),
                        merge_field->>'defaultValue',
                        merge_field->>'description',
                        true, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
                    );
                END LOOP;
            END IF;

            cloned_count := cloned_count + 1;
        END LOOP;
    END LOOP;

    RAISE NOTICE 'ENT communication templates (full refresh) — cloned: %, skipped (already present): %',
        cloned_count, skipped_count;
END $$;

-- =====================================================
-- VERIFICATION
-- =====================================================
-- SELECT template_key, template_access_key, category, template_id, sandbox_template_id,
--        jsonb_array_length(merge_field_map) AS merge_field_count, display_order, is_active
-- FROM communication_templates_master
-- WHERE program_type_id = (SELECT id FROM program_type_v1 WHERE key = 'PT_ENTRAINMENT')
-- ORDER BY display_order;

-- SELECT h.program_id, h.template_key, h.template_access_key, h.is_enabled,
--        COUNT(m.id) AS merge_field_count
-- FROM hdb_communication_templates h
-- LEFT JOIN merge_info_answer_location_map m ON m.template_id = h.id
-- WHERE h.deleted_at IS NULL
--   AND h.program_id IN (
--       SELECT p.id FROM program_v1 p
--       JOIN program_type_v1 pt ON pt.id = p.program_type_id
--       WHERE pt.key = 'PT_ENTRAINMENT'
--   )
-- GROUP BY h.program_id, h.template_key, h.template_access_key, h.is_enabled
-- ORDER BY h.program_id, h.template_key;

-- -- Confirm the old (pre-refresh) master rows are all soft-deactivated (should return 0 rows)
-- SELECT template_key FROM communication_templates_master
-- WHERE program_type_id = (SELECT id FROM program_type_v1 WHERE key = 'PT_ENTRAINMENT')
--   AND is_active = false
--   AND display_order > 61; -- none expected; sanity check only
