-- ============================================================
-- PART 2: Update communication_templates_master
-- ============================================================
-- Update generic template_access_key values to specific recipient variants

-- 1. BLESSED_NO_PAYMENT: Update generic to SEEKER-specific
UPDATE communication_templates_master
SET template_access_key = 'BLESSED_NO_PAYMENT_EMAIL_SEEKER'::communication_template_access_key_enum,
    updated_at = NOW()
WHERE template_access_key = 'BLESSED_NO_PAYMENT_EMAIL'::communication_template_access_key_enum
  AND template_type = 'email';

UPDATE communication_templates_master
SET template_access_key = 'BLESSED_NO_PAYMENT_WATI_SEEKER'::communication_template_access_key_enum,
    updated_at = NOW()
WHERE template_access_key = 'BLESSED_NO_PAYMENT_WATI'::communication_template_access_key_enum
  AND template_type = 'whatsapp';

-- 2. INVOICE: Update generic to SEEKER-specific
UPDATE communication_templates_master
SET template_access_key = 'INVOICE_EMAIL_SEEKER'::communication_template_access_key_enum,
    updated_at = NOW()
WHERE template_access_key = 'INVOICE_EMAIL'::communication_template_access_key_enum
  AND template_type = 'email';

UPDATE communication_templates_master
SET template_access_key = 'INVOICE_WATI_SEEKER'::communication_template_access_key_enum,
    updated_at = NOW()
WHERE template_access_key = 'INVOICE_WATI'::communication_template_access_key_enum
  AND template_type = 'whatsapp';

-- 3. PAYMENT_ACKNOWLEDGEMENT_OFFLINE: Update generic to SEEKER-specific
UPDATE communication_templates_master
SET template_access_key = 'PAYMENT_ACKNOWLEDGEMENT_OFFLINE_EMAIL_SEEKER'::communication_template_access_key_enum,
    updated_at = NOW()
WHERE template_access_key = 'PAYMENT_ACKNOWLEDGEMENT_OFFLINE_EMAIL'::communication_template_access_key_enum
  AND template_type = 'email';

UPDATE communication_templates_master
SET template_access_key = 'PAYMENT_ACKNOWLEDGEMENT_OFFLINE_WATI_SEEKER'::communication_template_access_key_enum,
    updated_at = NOW()
WHERE template_access_key = 'PAYMENT_ACKNOWLEDGEMENT_OFFLINE_WATI'::communication_template_access_key_enum
  AND template_type = 'whatsapp';

-- 4. REGISTRATION_REFUND: Update generic to FINANCE-specific
UPDATE communication_templates_master
SET template_access_key = 'REGISTRATION_REFUND_EMAIL_FINANCE'::communication_template_access_key_enum,
    updated_at = NOW()
WHERE template_access_key = 'REGISTRATION_REFUND'::communication_template_access_key_enum
  AND template_type = 'email';

-- 5. REGISTRATION_CANCELLED: Update generic to SEEKER-specific
UPDATE communication_templates_master
SET template_access_key = 'REGISTRATION_CANCELLED_EMAIL_SEEKER'::communication_template_access_key_enum,
    updated_at = NOW()
WHERE template_access_key = 'REGISTRATION_CANCELLED'::communication_template_access_key_enum
  AND template_type = 'email';

-- 6. BILLING_DETAILS_EDIT: Update generic to SEEKER-specific
UPDATE communication_templates_master
SET template_access_key = 'BILLING_DETAILS_EDIT_EMAIL_SEEKER'::communication_template_access_key_enum,
    updated_at = NOW()
WHERE template_access_key = 'BILLING_DETAILS_EDIT'::communication_template_access_key_enum
  AND template_type = 'email';

-- 7. BILLING_DETAILS_REMOVED: Update generic to SEEKER-specific
UPDATE communication_templates_master
SET template_access_key = 'BILLING_DETAILS_EDIT_REMOVED_EMAIL_SEEKER'::communication_template_access_key_enum,
    updated_at = NOW()
WHERE template_access_key = 'BILLING_DETAILS_REMOVED'::communication_template_access_key_enum
  AND template_type = 'email';

-- 8. SWAP_DEMAND: Update generic to SEEKER-specific
UPDATE communication_templates_master
SET template_access_key = 'SWAP_DEMAND_EMAIL_SEEKER'::communication_template_access_key_enum,
    updated_at = NOW()
WHERE template_access_key = 'SWAP_DEMAND_EMAIL'::communication_template_access_key_enum
  AND template_type = 'email';

UPDATE communication_templates_master
SET template_access_key = 'SWAP_DEMAND_WATI_SEEKER'::communication_template_access_key_enum,
    updated_at = NOW()
WHERE template_access_key = 'SWAP_DEMAND_WATI'::communication_template_access_key_enum
  AND template_type = 'whatsapp';

-- ============================================================
-- PART 3: Fix Merge Field Mappings for program_v1 Fields
-- ============================================================
-- All fields sourced from program_v1 need to use computed function because:
-- 1. program_v1 does not have a registration_id column
-- 2. We need to join from registration -> program to get program fields
-- The getProgramField computed function handles this join automatically

UPDATE merge_info_answer_location_map
SET 
  source_table = 'computed',
  source_column = CONCAT('getProgramField:', source_column),
  updated_at = NOW()
WHERE 
  source_table = 'program_v1'
  AND is_active = true
  AND source_column NOT LIKE 'getProgramField:%';

-- ============================================================
-- PART 4: Fix rm_name Merge Field Mapping
-- ============================================================
-- Fix rm_name to use computed function instead of users table
-- This resolves: "column users.registration_id does not exist"

UPDATE merge_info_answer_location_map
SET 
  source_table = 'computed',
  source_column = 'getRmContactUserName',
  updated_at = NOW()
WHERE 
  key_name = 'rm_name'
  AND source_table = 'users'
  AND source_column = 'full_name';

-- ============================================================
-- PART 5: Verification Queries
-- ============================================================

-- Verify master templates with updated access keys
SELECT 
    id,
    template_key,
    template_name,
    template_type,
    template_access_key,
    category,
    program_type_id,
    updated_at
FROM communication_templates_master
WHERE template_access_key IN (
    'BLESSED_NO_PAYMENT_EMAIL_SEEKER',
    'BLESSED_NO_PAYMENT_WATI_SEEKER',
    'INVOICE_EMAIL_SEEKER',
    'INVOICE_WATI_SEEKER',
    'PAYMENT_ACKNOWLEDGEMENT_OFFLINE_EMAIL_SEEKER',
    'PAYMENT_ACKNOWLEDGEMENT_OFFLINE_WATI_SEEKER',
    'REGISTRATION_REFUND_EMAIL_FINANCE',
    'REGISTRATION_CANCELLED_EMAIL_SEEKER',
    'BILLING_DETAILS_EDIT_EMAIL_SEEKER',
    'BILLING_DETAILS_EDIT_REMOVED_EMAIL_SEEKER',
    'SWAP_DEMAND_EMAIL_SEEKER',
    'SWAP_DEMAND_WATI_SEEKER'
)
ORDER BY template_type, template_access_key;

-- Verify program_v1 merge field mappings
SELECT 
  id, 
  template_id, 
  key_name, 
  source_table, 
  source_column,
  is_active,
  updated_at
FROM merge_info_answer_location_map
WHERE source_table = 'computed' 
  AND source_column LIKE 'getProgramField:%'
  AND is_active = true
ORDER BY template_id, key_name;

-- Verify rm_name merge field mapping
SELECT 
  id, 
  template_id, 
  key_name, 
  source_table, 
  source_column,
  is_active,
  updated_at
FROM merge_info_answer_location_map
WHERE key_name = 'rm_name'
  AND is_active = true
ORDER BY template_id;

-- Count summary
SELECT 
  COUNT(*) as total_program_fields_converted
FROM merge_info_answer_location_map
WHERE source_table = 'computed' 
  AND source_column LIKE 'getProgramField:%';

---- ============================================================
-- PART X: Update Coordinator Travel Plan WhatsApp Templates in Master Table
-- ============================================================

UPDATE communication_templates_master
SET template_access_key = 'CO_ORDINATOR_TRAVEL_PLAN_CHANGE_WATI'
WHERE template_type = 'whatsapp'
  AND template_key = 'HDB_COORDINATOR_TRAVEL_PLAN_CHANGE_WHATSAPP';

UPDATE communication_templates_master
SET template_access_key = 'CO_ORDINATOR_NEW_TRAVEL_PLAN_MADE_WATI'
WHERE template_type = 'whatsapp'
  AND template_key = 'HDB_COORDINATOR_NEW_TRAVEL_PLAN_MADE_WHATSAPP';

UPDATE communication_templates_master
SET template_access_key = 'CO_ORDINATOR_RETURN_TRAVEL_PLAN_CHANGE_WATI'
WHERE template_type = 'whatsapp'
  AND template_key = 'HDB_COORDINATOR_RETURN_TRAVEL_PLAN_CHANGE_WHATSAPP';

UPDATE communication_templates_master
SET template_access_key = 'CO_ORDINATOR_ONWARD_TRAVEL_PLAN_CHANGE_WATI'
WHERE template_type = 'whatsapp'
  AND template_key = 'HDB_COORDINATOR_ONWARD_TRAVEL_PLAN_CHANGE_WHATSAPP';

  -- Fix ALL template_access_key values to match the TypeScript enum expectations
-- Update BOTH master templates and program-specific templates

-- ============================================================
-- PART 1: Update communication_templates_master (program_type_id based)
-- ============================================================

-- 1. BLESSED_NO_PAYMENT: Update generic to SEEKER-specific
UPDATE communication_templates_master
SET template_access_key = 'BLESSED_NO_PAYMENT_EMAIL_SEEKER'
WHERE template_access_key = 'BLESSED_NO_PAYMENT_EMAIL'
  AND template_type = 'email';

UPDATE communication_templates_master
SET template_access_key = 'BLESSED_NO_PAYMENT_WATI_SEEKER'
WHERE template_access_key = 'BLESSED_NO_PAYMENT_WATI'
  AND template_type = 'whatsapp';

-- 2. INVOICE: Update generic to SEEKER-specific
UPDATE communication_templates_master
SET template_access_key = 'INVOICE_EMAIL_SEEKER'
WHERE template_access_key = 'INVOICE_EMAIL'
  AND template_type = 'email';

UPDATE communication_templates_master
SET template_access_key = 'INVOICE_WATI_SEEKER'
WHERE template_access_key = 'INVOICE_WATI'
  AND template_type = 'whatsapp';

-- 3. PAYMENT_ACKNOWLEDGEMENT_OFFLINE: Update generic to SEEKER-specific
UPDATE communication_templates_master
SET template_access_key = 'PAYMENT_ACKNOWLEDGEMENT_OFFLINE_EMAIL_SEEKER'
WHERE template_access_key = 'PAYMENT_ACKNOWLEDGEMENT_OFFLINE_EMAIL'
  AND template_type = 'email';

UPDATE communication_templates_master
SET template_access_key = 'PAYMENT_ACKNOWLEDGEMENT_OFFLINE_WATI_SEEKER'
WHERE template_access_key = 'PAYMENT_ACKNOWLEDGEMENT_OFFLINE_WATI'
  AND template_type = 'whatsapp';

-- 4. REGISTRATION_REFUND: Update generic to FINANCE-specific
UPDATE communication_templates_master
SET template_access_key = 'REGISTRATION_REFUND_EMAIL_FINANCE'
WHERE template_access_key = 'REGISTRATION_REFUND'
  AND template_type = 'email';

-- 5. REGISTRATION_CANCELLED: Update generic to SEEKER-specific
UPDATE communication_templates_master
SET template_access_key = 'REGISTRATION_CANCELLED_EMAIL_SEEKER'
WHERE template_access_key = 'REGISTRATION_CANCELLED'
  AND template_type = 'email';

-- 6. BILLING_DETAILS_EDIT: Update generic to SEEKER-specific
UPDATE communication_templates_master
SET template_access_key = 'BILLING_DETAILS_EDIT_EMAIL_SEEKER'
WHERE template_access_key = 'BILLING_DETAILS_EDIT'
  AND template_type = 'email';

-- 7. BILLING_DETAILS_REMOVED: Update generic to SEEKER-specific
UPDATE communication_templates_master
SET template_access_key = 'BILLING_DETAILS_EDIT_REMOVED_EMAIL_SEEKER'
WHERE template_access_key = 'BILLING_DETAILS_REMOVED'
  AND template_type = 'email';

-- 8. SWAP_DEMAND: Update generic to SEEKER-specific
UPDATE communication_templates_master
SET template_access_key = 'SWAP_DEMAND_EMAIL_SEEKER'
WHERE template_access_key = 'SWAP_DEMAND_EMAIL'
  AND template_type = 'email';

UPDATE communication_templates_master
SET template_access_key = 'SWAP_DEMAND_WATI_SEEKER'
WHERE template_access_key = 'SWAP_DEMAND_WATI'
  AND template_type = 'whatsapp';

-- ============================================================
-- PART 2: Update hdb_communication_templates (program_id based)
-- ============================================================

-- 1. BLESSED_NO_PAYMENT: Update generic to SEEKER-specific
UPDATE hdb_communication_templates
SET template_access_key = 'BLESSED_NO_PAYMENT_EMAIL_SEEKER'
WHERE template_access_key = 'BLESSED_NO_PAYMENT_EMAIL'
  AND template_type = 'email';

UPDATE hdb_communication_templates
SET template_access_key = 'BLESSED_NO_PAYMENT_WATI_SEEKER'
WHERE template_access_key = 'BLESSED_NO_PAYMENT_WATI'
  AND template_type = 'whatsapp';

-- 2. INVOICE: Update generic to SEEKER-specific
UPDATE hdb_communication_templates
SET template_access_key = 'INVOICE_EMAIL_SEEKER'
WHERE template_access_key = 'INVOICE_EMAIL'
  AND template_type = 'email';

UPDATE hdb_communication_templates
SET template_access_key = 'INVOICE_WATI_SEEKER'
WHERE template_access_key = 'INVOICE_WATI'
  AND template_type = 'whatsapp';

-- 3. PAYMENT_ACKNOWLEDGEMENT_OFFLINE: Update generic to SEEKER-specific
UPDATE hdb_communication_templates
SET template_access_key = 'PAYMENT_ACKNOWLEDGEMENT_OFFLINE_EMAIL_SEEKER'
WHERE template_access_key = 'PAYMENT_ACKNOWLEDGEMENT_OFFLINE_EMAIL'
  AND template_type = 'email';

UPDATE hdb_communication_templates
SET template_access_key = 'PAYMENT_ACKNOWLEDGEMENT_OFFLINE_WATI_SEEKER'
WHERE template_access_key = 'PAYMENT_ACKNOWLEDGEMENT_OFFLINE_WATI'
  AND template_type = 'whatsapp';

-- 4. REGISTRATION_REFUND: Update generic to FINANCE-specific
UPDATE hdb_communication_templates
SET template_access_key = 'REGISTRATION_REFUND_EMAIL_FINANCE'
WHERE template_access_key = 'REGISTRATION_REFUND'
  AND template_type = 'email';

-- 5. REGISTRATION_CANCELLED: Update generic to SEEKER-specific
UPDATE hdb_communication_templates
SET template_access_key = 'REGISTRATION_CANCELLED_EMAIL_SEEKER'
WHERE template_access_key = 'REGISTRATION_CANCELLED'
  AND template_type = 'email';

-- 6. BILLING_DETAILS_EDIT: Update generic to SEEKER-specific
UPDATE hdb_communication_templates
SET template_access_key = 'BILLING_DETAILS_EDIT_EMAIL_SEEKER'
WHERE template_access_key = 'BILLING_DETAILS_EDIT'
  AND template_type = 'email';

-- 7. BILLING_DETAILS_REMOVED: Update generic to SEEKER-specific
UPDATE hdb_communication_templates
SET template_access_key = 'BILLING_DETAILS_EDIT_REMOVED_EMAIL_SEEKER'
WHERE template_access_key = 'BILLING_DETAILS_REMOVED'
  AND template_type = 'email';

-- 8. SWAP_DEMAND: Update generic to SEEKER-specific
UPDATE hdb_communication_templates
SET template_access_key = 'SWAP_DEMAND_EMAIL_SEEKER'
WHERE template_access_key = 'SWAP_DEMAND_EMAIL'
  AND template_type = 'email';

UPDATE hdb_communication_templates
SET template_access_key = 'SWAP_DEMAND_WATI_SEEKER'
WHERE template_access_key = 'SWAP_DEMAND_WATI'
  AND template_type = 'whatsapp';

-- ============================================================
-- Verify ALL changes
-- ============================================================

-- Verify master templates
SELECT 
    'MASTER' as table_name,
    id,
    template_key,
    template_name,
    template_type,
    template_access_key,
    category
FROM communication_templates_master
WHERE template_access_key IN (
    'BLESSED_NO_PAYMENT_EMAIL_SEEKER',
    'BLESSED_NO_PAYMENT_WATI_SEEKER',
    'INVOICE_EMAIL_SEEKER',
    'INVOICE_WATI_SEEKER',
    'PAYMENT_ACKNOWLEDGEMENT_OFFLINE_EMAIL_SEEKER',
    'PAYMENT_ACKNOWLEDGEMENT_OFFLINE_WATI_SEEKER',
    'REGISTRATION_REFUND_EMAIL_FINANCE',
    'REGISTRATION_CANCELLED_EMAIL_SEEKER',
    'BILLING_DETAILS_EDIT_EMAIL_SEEKER',
    'BILLING_DETAILS_EDIT_REMOVED_EMAIL_SEEKER',
    'SWAP_DEMAND_EMAIL_SEEKER',
    'SWAP_DEMAND_WATI_SEEKER'
)

UNION ALL

-- Verify program-specific templates
SELECT 
    'PROGRAM_SPECIFIC' as table_name,
    id,
    template_key,
    template_name,
    template_type,
    template_access_key,
    category
FROM hdb_communication_templates
WHERE template_access_key IN (
    'BLESSED_NO_PAYMENT_EMAIL_SEEKER',
    'BLESSED_NO_PAYMENT_WATI_SEEKER',
    'INVOICE_EMAIL_SEEKER',
    'INVOICE_WATI_SEEKER',
    'PAYMENT_ACKNOWLEDGEMENT_OFFLINE_EMAIL_SEEKER',
    'PAYMENT_ACKNOWLEDGEMENT_OFFLINE_WATI_SEEKER',
    'REGISTRATION_REFUND_EMAIL_FINANCE',
    'REGISTRATION_CANCELLED_EMAIL_SEEKER',
    'BILLING_DETAILS_EDIT_EMAIL_SEEKER',
    'BILLING_DETAILS_EDIT_REMOVED_EMAIL_SEEKER',
    'SWAP_DEMAND_EMAIL_SEEKER',
    'SWAP_DEMAND_WATI_SEEKER'
)
ORDER BY table_name, template_type, template_access_key;



-- update coordinator travel plan templates 

COMMENT ON COLUMN communication_templates_master.template_access_key IS 
'Common access key across program types for same purpose. Updated 2026-04-07 to use recipient-specific variants (e.g., INVOICE_EMAIL_SEEKER, INVOICE_WATI_SEEKER)';
