-- Migration: Communication Template Configuration — Back-fill + Seed
-- Date: 2026-05-27
-- Description: Back-fills step on master + clones, back-fills master_template_id
--              on clones, populates attached_steps with the native step, and
--              seeds merge_field_catalog from master.merge_field_map JSONB.
-- Spec: docs/technical-specs/COMMUNICATION_TEMPLATE_CONFIG_SPEC.md §3.1, §3.3, §6
-- Idempotent — safe to re-run. Run AFTER 2026-05-27-communication-template-config-schema.sql.

-- =====================================================================
-- Step 1: Back-fill step on communication_templates_master by parsing
--         template_access_key. Legacy keys stay NULL.
-- =====================================================================
UPDATE communication_templates_master SET
  step = CASE
    WHEN template_access_key::text LIKE 'REGISTRATION_COMPLETED%'       THEN 'REGISTRATION_COMPLETED'
    WHEN template_access_key::text LIKE 'REGISTRATION_CANCELLED%'       THEN 'REGISTRATION_CANCELLED'
    WHEN template_access_key::text LIKE 'REGISTRATION_REFUND%'          THEN 'REGISTRATION_REFUND'
    WHEN template_access_key::text LIKE 'BLESSED_NO_PAYMENT%'           THEN 'BLESSED_NO_PAYMENT'
    WHEN template_access_key::text LIKE 'BLESSED_%'                     THEN 'BLESSED'
    WHEN template_access_key::text LIKE 'RM_HOLD%'                      THEN 'RM_HOLD'
    WHEN template_access_key::text LIKE 'HOLD_%'                        THEN 'HOLD'
    WHEN template_access_key::text LIKE 'INVOICE_%'                     THEN 'INVOICE'
    WHEN template_access_key::text LIKE 'PAYMENT_ACKNOWLEDGEMENT_OFFLINE%' THEN 'PAYMENT_ACKNOWLEDGEMENT_OFFLINE'
    WHEN template_access_key::text LIKE 'PAYMENT_PENDING_REMINDER%'     THEN 'PAYMENT_PENDING_REMINDER'
    WHEN template_access_key::text LIKE 'BILLING_DETAILS_EDIT_REMOVED%' THEN 'BILLING_DETAILS_EDIT_REMOVED'
    WHEN template_access_key::text LIKE 'BILLING_DETAILS_EDIT%'         THEN 'BILLING_DETAILS_EDIT'
    WHEN template_access_key::text LIKE 'EINVOICE_ERROR%'              THEN 'EINVOICE_ERROR'
    WHEN template_access_key::text LIKE 'SWAP_ACCEPT_NO_PAYMENT%'       THEN 'SWAP_ACCEPT_NO_PAYMENT'
    WHEN template_access_key::text LIKE 'SWAP_ACCEPT%'                  THEN 'SWAP_ACCEPT'
    WHEN template_access_key::text LIKE 'SWAP_DEMAND%'                  THEN 'SWAP_DEMAND'
    WHEN template_access_key::text LIKE 'CO_ORDINATOR_NEW_TRAVEL_PLAN_MADE%'      THEN 'CO_ORDINATOR_NEW_TRAVEL_PLAN_MADE'
    WHEN template_access_key::text LIKE 'CO_ORDINATOR_ONWARD_TRAVEL_PLAN_CHANGE%' THEN 'CO_ORDINATOR_ONWARD_TRAVEL_PLAN_CHANGE'
    WHEN template_access_key::text LIKE 'CO_ORDINATOR_RETURN_TRAVEL_PLAN_CHANGE%' THEN 'CO_ORDINATOR_RETURN_TRAVEL_PLAN_CHANGE'
    WHEN template_access_key::text LIKE 'CO_ORDINATOR_TRAVEL_PLAN_CHANGE%'        THEN 'CO_ORDINATOR_TRAVEL_PLAN_CHANGE'
    WHEN template_access_key::text LIKE 'TRAVEL_PLAN_NEW%'             THEN 'TRAVEL_PLAN_NEW'
    WHEN template_access_key::text LIKE 'TRAVEL_PLAN_ONWARD_CHANGE%'   THEN 'TRAVEL_PLAN_ONWARD_CHANGE'
    WHEN template_access_key::text LIKE 'TRAVEL_PLAN_RETURN_CHANGE%'   THEN 'TRAVEL_PLAN_RETURN_CHANGE'
    WHEN template_access_key::text LIKE 'TRAVEL_PLAN_CHANGE%'          THEN 'TRAVEL_PLAN_CHANGE'
    WHEN template_access_key::text LIKE 'PREFERENCE_EDITED%'          THEN 'PREFERENCE_EDITED'
    WHEN template_access_key::text LIKE 'ADMIN_MESSAGE_NOTIFICATION%' THEN 'ADMIN_MESSAGE_NOTIFICATION'
    WHEN template_access_key::text LIKE 'GENERIC%'                    THEN 'GENERIC'
    WHEN template_access_key::text LIKE 'OTP%'                THEN 'OTP'
    WHEN template_access_key::text LIKE 'FIRST_TIMER%'        THEN 'FIRST_TIMER'
    WHEN template_access_key::text LIKE 'CHECKLIST%'          THEN 'CHECKLIST'
    WHEN template_access_key::text LIKE 'PREPARATORY%'        THEN 'PREPARATORY'
    ELSE step
  END
WHERE is_active = true;

-- =====================================================================
-- Step 2: Back-fill the same step on hdb_communication_templates
-- =====================================================================
UPDATE hdb_communication_templates SET
  step = CASE
    WHEN template_access_key::text LIKE 'REGISTRATION_COMPLETED%'       THEN 'REGISTRATION_COMPLETED'
    WHEN template_access_key::text LIKE 'REGISTRATION_CANCELLED%'       THEN 'REGISTRATION_CANCELLED'
    WHEN template_access_key::text LIKE 'REGISTRATION_REFUND%'          THEN 'REGISTRATION_REFUND'
    WHEN template_access_key::text LIKE 'BLESSED_NO_PAYMENT%'           THEN 'BLESSED_NO_PAYMENT'
    WHEN template_access_key::text LIKE 'BLESSED_%'                     THEN 'BLESSED'
    WHEN template_access_key::text LIKE 'RM_HOLD%'                      THEN 'RM_HOLD'
    WHEN template_access_key::text LIKE 'HOLD_%'                        THEN 'HOLD'
    WHEN template_access_key::text LIKE 'INVOICE_%'                     THEN 'INVOICE'
    WHEN template_access_key::text LIKE 'PAYMENT_ACKNOWLEDGEMENT_OFFLINE%' THEN 'PAYMENT_ACKNOWLEDGEMENT_OFFLINE'
    WHEN template_access_key::text LIKE 'PAYMENT_PENDING_REMINDER%'     THEN 'PAYMENT_PENDING_REMINDER'
    WHEN template_access_key::text LIKE 'BILLING_DETAILS_EDIT_REMOVED%' THEN 'BILLING_DETAILS_EDIT_REMOVED'
    WHEN template_access_key::text LIKE 'BILLING_DETAILS_EDIT%'         THEN 'BILLING_DETAILS_EDIT'
    WHEN template_access_key::text LIKE 'EINVOICE_ERROR%'              THEN 'EINVOICE_ERROR'
    WHEN template_access_key::text LIKE 'SWAP_ACCEPT_NO_PAYMENT%'       THEN 'SWAP_ACCEPT_NO_PAYMENT'
    WHEN template_access_key::text LIKE 'SWAP_ACCEPT%'                  THEN 'SWAP_ACCEPT'
    WHEN template_access_key::text LIKE 'SWAP_DEMAND%'                  THEN 'SWAP_DEMAND'
    WHEN template_access_key::text LIKE 'CO_ORDINATOR_NEW_TRAVEL_PLAN_MADE%'      THEN 'CO_ORDINATOR_NEW_TRAVEL_PLAN_MADE'
    WHEN template_access_key::text LIKE 'CO_ORDINATOR_ONWARD_TRAVEL_PLAN_CHANGE%' THEN 'CO_ORDINATOR_ONWARD_TRAVEL_PLAN_CHANGE'
    WHEN template_access_key::text LIKE 'CO_ORDINATOR_RETURN_TRAVEL_PLAN_CHANGE%' THEN 'CO_ORDINATOR_RETURN_TRAVEL_PLAN_CHANGE'
    WHEN template_access_key::text LIKE 'CO_ORDINATOR_TRAVEL_PLAN_CHANGE%'        THEN 'CO_ORDINATOR_TRAVEL_PLAN_CHANGE'
    WHEN template_access_key::text LIKE 'TRAVEL_PLAN_NEW%'             THEN 'TRAVEL_PLAN_NEW'
    WHEN template_access_key::text LIKE 'TRAVEL_PLAN_ONWARD_CHANGE%'   THEN 'TRAVEL_PLAN_ONWARD_CHANGE'
    WHEN template_access_key::text LIKE 'TRAVEL_PLAN_RETURN_CHANGE%'   THEN 'TRAVEL_PLAN_RETURN_CHANGE'
    WHEN template_access_key::text LIKE 'TRAVEL_PLAN_CHANGE%'          THEN 'TRAVEL_PLAN_CHANGE'
    WHEN template_access_key::text LIKE 'PREFERENCE_EDITED%'          THEN 'PREFERENCE_EDITED'
    WHEN template_access_key::text LIKE 'ADMIN_MESSAGE_NOTIFICATION%' THEN 'ADMIN_MESSAGE_NOTIFICATION'
    WHEN template_access_key::text LIKE 'GENERIC%'                    THEN 'GENERIC'
    WHEN template_access_key::text LIKE 'OTP%'                THEN 'OTP'
    WHEN template_access_key::text LIKE 'FIRST_TIMER%'        THEN 'FIRST_TIMER'
    WHEN template_access_key::text LIKE 'CHECKLIST%'          THEN 'CHECKLIST'
    WHEN template_access_key::text LIKE 'PREPARATORY%'        THEN 'PREPARATORY'
    ELSE step
  END;

-- =====================================================================
-- Step 3: Back-fill master_template_id on hdb_communication_templates
--         by joining clone -> program_v1 -> communication_templates_master
--         on (program_type_id, template_access_key, template_type) for any
--         active master. Unmatched rows stay NULL.
-- =====================================================================
UPDATE hdb_communication_templates h
SET master_template_id = m.id
FROM program_v1 p, communication_templates_master m
WHERE h.program_id = p.id
  AND m.program_type_id = p.program_type_id
  AND m.template_access_key = h.template_access_key
  AND m.template_type = h.template_type
  AND m.is_active = true
  AND h.master_template_id IS NULL;

-- =====================================================================
-- Step 3b: Back-fill attached_steps on existing clones.
--          Native attachment = the clone's own step. Cross-step attachment
--          is the admin's job later; the seed only attaches each clone to
--          its native step (so existing send behaviour is preserved).
-- =====================================================================
UPDATE hdb_communication_templates
SET attached_steps = ARRAY[step]::TEXT[]
WHERE step IS NOT NULL
  AND (attached_steps IS NULL OR cardinality(attached_steps) = 0);

-- =====================================================================
-- Step 4: Seed merge_field_catalog from communication_templates_master.merge_field_map.
--         The master JSONB is the authoritative source — merge_info_answer_location_map
--         is just the per-clone snapshot populated from it. Every distinct
--         (sourceTable, sourceColumn) pair declared by an active master becomes
--         one catalog entry. catalog_key = `<sourceTable>.<sourceColumn>` (unique
--         by definition). is_per_recipient is OR'd across all usages so any single
--         per-recipient declaration marks the catalog entry.
-- =====================================================================
INSERT INTO merge_field_catalog
  (catalog_key, display_name, source_table, source_column,
   data_type, format_type, default_value, description, is_per_recipient)
SELECT
  (field->>'sourceTable') || '.' || (field->>'sourceColumn')          AS catalog_key,
  field->>'sourceColumn'                                              AS display_name,
  field->>'sourceTable'                                               AS source_table,
  field->>'sourceColumn'                                              AS source_column,
  COALESCE(MIN(field->>'dataType'), 'string')                         AS data_type,
  MIN(field->>'formatType') FILTER (WHERE field->>'formatType' IS NOT NULL) AS format_type,
  MIN(field->>'defaultValue') FILTER (WHERE field->>'defaultValue' IS NOT NULL) AS default_value,
  MIN(field->>'description') FILTER (WHERE field->>'description' IS NOT NULL) AS description,
  BOOL_OR(COALESCE((field->>'isPerRecipient')::boolean, false))       AS is_per_recipient
FROM communication_templates_master m,
     LATERAL jsonb_array_elements(COALESCE(m.merge_field_map, '[]'::jsonb)) AS field
WHERE m.is_active = true
  AND field->>'sourceTable'  IS NOT NULL
  AND field->>'sourceColumn' IS NOT NULL
GROUP BY field->>'sourceTable', field->>'sourceColumn'
ON CONFLICT (catalog_key) DO NOTHING;

-- Catalog entries that are always per-recipient at send-time, regardless of how
-- any existing master happened to flag them. Keeps the ALL-scope fan-out correct
-- for RM/user contact fields.
UPDATE merge_field_catalog
SET is_per_recipient = true
WHERE source_table = 'computed'
  AND source_column IN (
    'getRmContactUserName',
    'getRmContactEmail',
    'getRmContactMobile',
    'getUserFormattedPhone'
  );
