-- ============================================================
-- Sync HDB Communication Templates with Master Templates
-- ============================================================
-- Date: 2026-04-08
-- Description: Updates hdb_communication_templates and merge_info_answer_location_map 
--              to match the master template configuration from communication_templates_master
-- 
-- This script:
-- 1. Updates master template merge field mappings with correct formatType
-- 2. Updates template_access_key values in hdb_communication_templates
-- 3. Syncs merge field mappings from master to program-specific templates
-- 4. Updates merge_info_answer_location_map entries
-- ============================================================

-- ============================================================
-- PART 1: Update Master Templates with Correct Format Types
-- ============================================================

-- 1A. Update master template: Payment Acknowledgement Email
UPDATE communication_templates_master 
SET template_access_key = 'PAYMENT_ACKNOWLEDGEMENT_OFFLINE_EMAIL_SEEKER',
    merge_field_map = '[
        {"keyName": "pay_date", "sourceTable": "computed", "sourceColumn": "currentDate", "dataType": "date", "formatType": "custom:DD-MM-YYYY", "isNullable": false},
        {"keyName": "pay_amount", "sourceTable": "computed", "sourceColumn": "calculateTotalPaymentAmount", "dataType": "number", "formatType": "currency", "isNullable": false},
        {"keyName": "reg_name", "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "dataType": "string", "isNullable": false},
        {"keyName": "pay_method", "sourceTable": "hdb_registration_payment_detail", "sourceColumn": "payment_mode", "dataType": "string", "formatType": "conditional", "isNullable": false},
        {"keyName": "hdb_msd", "sourceTable": "computed", "sourceColumn": "getAllocatedProgramField:name", "dataType": "string", "isNullable": false}
    ]'::jsonb,
    updated_at = NOW()
WHERE template_key = 'HDB_PAYMENT_ACKNOWLEDGEMENT_OFFLINE_EMAIL'
  AND template_type = 'email';

-- 1B. Update master template: Payment Acknowledgement WhatsApp  
UPDATE communication_templates_master 
SET template_access_key = 'PAYMENT_ACKNOWLEDGEMENT_OFFLINE_WATI_SEEKER',
    merge_field_map = '[
        {"keyName": "reg_name", "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "dataType": "string", "isNullable": false},
        {"keyName": "pay_date", "sourceTable": "computed", "sourceColumn": "currentDate", "dataType": "date", "formatType": "custom:DD-MM-YYYY", "isNullable": false},
        {"keyName": "pay_amount", "sourceTable": "computed", "sourceColumn": "calculateTotalPaymentAmount", "dataType": "number", "formatType": "currency", "isNullable": false},
        {"keyName": "hdb_msd", "sourceTable": "computed", "sourceColumn": "getAllocatedProgramField:name", "dataType": "string", "isNullable": false}
    ]'::jsonb,
    updated_at = NOW()
WHERE template_key = 'HDB_PAYMENT_ACKNOWLEDGEMENT_OFFLINE_WHATSAPP'
  AND template_type = 'whatsapp';

-- 1C. Update master template: HDB Blessed Email - Add day fields with weekname formatType
UPDATE communication_templates_master 
SET merge_field_map = '[
        {"keyName": "venue_name", "sourceTable": "computed", "sourceColumn": "getAllocatedProgramField:venue_name_in_emails", "dataType": "string", "isNullable": true},
        {"keyName": "s_day", "sourceTable": "computed", "sourceColumn": "getAllocatedProgramField:starts_at", "dataType": "date", "formatType": "weekname", "isNullable": false},
        {"keyName": "s_date", "sourceTable": "computed", "sourceColumn": "getAllocatedProgramField:starts_at", "dataType": "date", "formatType": "custom:DD-MM-YYYY", "isNullable": false},
        {"keyName": "e_day", "sourceTable": "computed", "sourceColumn": "getAllocatedProgramField:ends_at", "dataType": "date", "formatType": "weekname", "isNullable": false},
        {"keyName": "e_date", "sourceTable": "computed", "sourceColumn": "getAllocatedProgramField:ends_at", "dataType": "date", "formatType": "custom:DD-MM-YYYY", "isNullable": false},
        {"keyName": "hdb_price", "sourceTable": "computed", "sourceColumn": "getAllocatedProgramField:base_price", "dataType": "number", "formatType": "currency", "isNullable": true},
        {"keyName": "payment_last_date", "sourceTable": "computed", "sourceColumn": "getAllocatedProgramField:starts_at", "dataType": "date", "formatType": "custom:DD-MM-YYYY", "isNullable": false},
        {"keyName": "cash_details", "sourceTable": "computed", "sourceColumn": "cashDetails", "dataType": "string", "formatType": "conditional", "isNullable": true},
        {"keyName": "reg_name", "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "dataType": "string", "isNullable": false},
        {"keyName": "hdb_or_msd", "sourceTable": "computed", "sourceColumn": "getAllocatedProgramField:name", "dataType": "string", "isNullable": false},
        {"keyName": "hdb_dates", "sourceTable": "computed", "sourceColumn": "formatProgramDateRange", "dataType": "string", "formatType": "custom:DD-MM-YYYY", "isNullable": false},
        {"keyName": "hdb_no", "sourceTable": "computed", "sourceColumn": "getAllocatedProgramField:name", "dataType": "string", "isNullable": false},
        {"keyName": "payment_online_link", "sourceTable": "computed", "sourceColumn": "generatePaymentLink", "dataType": "string", "formatType": "url", "isNullable": false},
        {"keyName": "payment_back_transfer_link", "sourceTable": "computed", "sourceColumn": "generatePaymentLink", "dataType": "string", "formatType": "url", "isNullable": false},
        {"keyName": "payment_cheque_link", "sourceTable": "computed", "sourceColumn": "generatePaymentLink", "dataType": "string", "formatType": "url", "isNullable": false}
    ]'::jsonb,
    updated_at = NOW()
WHERE template_key = 'HDB_BLESSED_EMAIL'
  AND template_type = 'email';

-- 1D. Update master template: HDB Blessed WhatsApp - Add day/date fields with weekname formatType
UPDATE communication_templates_master 
SET merge_field_map = '[
        {"keyName": "reg_name", "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "dataType": "string", "isNullable": false},
        {"keyName": "hdb_msd", "sourceTable": "computed", "sourceColumn": "getAllocatedProgramField:name", "dataType": "string", "isNullable": false},
        {"keyName": "s_day", "sourceTable": "computed", "sourceColumn": "getAllocatedProgramField:starts_at", "dataType": "date", "formatType": "weekname", "isNullable": false},
        {"keyName": "s_date", "sourceTable": "computed", "sourceColumn": "getAllocatedProgramField:starts_at", "dataType": "date", "formatType": "custom:DD-MM-YYYY", "isNullable": false},
        {"keyName": "e_day", "sourceTable": "computed", "sourceColumn": "getAllocatedProgramField:ends_at", "dataType": "date", "formatType": "weekname", "isNullable": false},
        {"keyName": "e_date", "sourceTable": "computed", "sourceColumn": "getAllocatedProgramField:ends_at", "dataType": "date", "formatType": "custom:DD-MM-YYYY", "isNullable": false},
        {"keyName": "payment_link", "sourceTable": "computed", "sourceColumn": "generatePaymentLink", "dataType": "string", "formatType": "url", "isNullable": false}
    ]'::jsonb,
    updated_at = NOW()
WHERE template_key = 'HDB_BLESSED_WHATSAPP'
  AND template_type = 'whatsapp';


-- 1E. Update master template: HDB Invoice Email - Add formatType for fields
UPDATE communication_templates_master
SET template_access_key = 'INVOICE_EMAIL_SEEKER',
    merge_field_map = '[
        {"keyName": "hdb_msd_no", "sourceTable": "computed", "sourceColumn": "getAllocatedProgramField:name", "dataType": "string", "isNullable": false},
        {"keyName": "hdb_msd_date", "sourceTable": "computed", "sourceColumn": "formatProgramDateRange", "dataType": "string", "formatType": "custom:DD-MM-YYYY", "isNullable": false},
        {"keyName": "hdb_msd_amount", "sourceTable": "computed", "sourceColumn": "calculateTotalPaymentAmount", "dataType": "number", "formatType": "currency", "isNullable": false},
        {"keyName": "reg_name", "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "dataType": "string", "isNullable": false},
        {"keyName": "hdb_msd", "sourceTable": "computed", "sourceColumn": "getAllocatedProgramField:name", "dataType": "string", "isNullable": false},
        {"keyName": "last_date", "sourceTable": "computed", "sourceColumn": "getAllocatedProgramStartsMinus10Days", "dataType": "date", "formatType": "custom:DD-MM-YYYY", "isNullable": false}
    ]'::jsonb,
    updated_at = NOW()
WHERE template_key = 'HDB_INVOICE_EMAIL'
  AND template_type = 'email';

-- 1F. Update master template: HDB Invoice WhatsApp - Add formatType for fields
UPDATE communication_templates_master
SET merge_field_map = '[
        {"keyName": "reg_name", "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "dataType": "string", "isNullable": false},
        {"keyName": "hbd_msd_variable", "sourceTable": "computed", "sourceColumn": "getAllocatedProgramField:name", "dataType": "string", "isNullable": false},
        {"keyName": "s_date", "sourceTable": "computed", "sourceColumn": "getAllocatedProgramField:starts_at", "dataType": "date", "formatType": "custom:DD-MM-YYYY", "isNullable": false},
        {"keyName": "e_date", "sourceTable": "computed", "sourceColumn": "getAllocatedProgramField:ends_at", "dataType": "date", "formatType": "custom:DD-MM-YYYY", "isNullable": false}
    ]'::jsonb,
    updated_at = NOW()
WHERE template_key = 'HDB_INVOICE_WHATSAPP'
  AND template_type = 'whatsapp';

-- ============================================================
-- PART 2: Sync Master Templates to Program-Level Templates
-- ============================================================

DO $$
DECLARE
    master_rec RECORD;
    program_template_id BIGINT;
    merge_field JSONB;
    sync_count INT := 0;
    merge_field_count INT := 0;
BEGIN
    RAISE NOTICE '======================================';
    RAISE NOTICE 'Starting HDB Template Sync Process';
    RAISE NOTICE '======================================';
    
    -- Loop through all master templates for PT_HDBMSD program type
    FOR master_rec IN 
        SELECT 
            ctm.id AS master_id,
            ctm.template_key,
            ctm.template_name,
            ctm.template_type,
            ctm.template_access_key,
            ctm.merge_field_map,
            ctm.sandbox_template_id,
            ctm.display_order
        FROM communication_templates_master ctm
        WHERE ctm.program_type_id = (SELECT id FROM program_type_v1 WHERE key = 'PT_HDBMSD')
            AND ctm.is_active = true
        ORDER BY ctm.display_order
    LOOP
        RAISE NOTICE '--------------------------------------';
        RAISE NOTICE 'Processing Master Template: % (Key: %)', 
            master_rec.template_name, 
            master_rec.template_key;
        
        -- Update all matching program templates
        UPDATE hdb_communication_templates
        SET 
            template_access_key = master_rec.template_access_key,
            sandbox_template_id = master_rec.sandbox_template_id,
            display_order = master_rec.display_order,
            updated_at = CURRENT_TIMESTAMP
        WHERE template_key::text = master_rec.template_key
            AND template_type = master_rec.template_type
            AND program_id IS NOT NULL;

        IF FOUND THEN
            GET DIAGNOSTICS sync_count = ROW_COUNT;
            RAISE NOTICE '  ✓ Updated % program template(s) with access_key: %', 
                sync_count, 
                master_rec.template_access_key;
        END IF;
        
        -- Now update merge field mappings for each program template
        IF master_rec.merge_field_map IS NOT NULL 
            AND jsonb_array_length(master_rec.merge_field_map) > 0 THEN
            
            RAISE NOTICE '  → Syncing % merge fields...', 
                jsonb_array_length(master_rec.merge_field_map);
            
            -- Loop through all program templates with this template_key
            FOR program_template_id IN 
                SELECT id 
                FROM hdb_communication_templates 
                WHERE template_key::text = master_rec.template_key
                    AND template_type = master_rec.template_type
                    AND program_id IS NOT NULL
            LOOP
                -- First, deactivate existing merge fields for this template
                UPDATE merge_info_answer_location_map
                SET is_active = false,
                    updated_at = CURRENT_TIMESTAMP
                WHERE template_id = program_template_id;
                
                -- Insert or update merge fields from master
                merge_field_count := 0;
                FOR merge_field IN SELECT * FROM jsonb_array_elements(master_rec.merge_field_map)
                LOOP
                    -- Check if merge field already exists
                    IF EXISTS (
                        SELECT 1 
                        FROM merge_info_answer_location_map 
                        WHERE template_id = program_template_id 
                            AND key_name = merge_field->>'keyName'
                    ) THEN
                        -- Update existing
                        UPDATE merge_info_answer_location_map
                        SET 
                            is_common = COALESCE((merge_field->>'isCommon')::boolean, false),
                            source_table = merge_field->>'sourceTable',
                            source_column = merge_field->>'sourceColumn',
                            data_type = merge_field->>'dataType',
                            format_type = merge_field->>'formatType',
                            is_nullable = COALESCE((merge_field->>'isNullable')::boolean, true),
                            default_value = merge_field->>'defaultValue',
                            description = merge_field->>'description',
                            is_active = true,
                            updated_at = CURRENT_TIMESTAMP
                        WHERE template_id = program_template_id
                            AND key_name = merge_field->>'keyName';
                    ELSE
                        -- Insert new
                        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',
                            program_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 IF;
                    
                    merge_field_count := merge_field_count + 1;
                END LOOP;
                
                RAISE NOTICE '    → Synced % merge fields for template ID %', 
                    merge_field_count,
                    program_template_id;
            END LOOP;
        END IF;
        
    END LOOP;
    
    RAISE NOTICE '======================================';
    RAISE NOTICE 'HDB Template Sync Complete';
    RAISE NOTICE '======================================';
    
END $$;

-- ============================================================
-- PART 3: Update Generic Templates (Non-Program Specific)
-- ============================================================

-- Update OTP Email template
UPDATE hdb_communication_templates hct
SET 
    template_access_key = ctm.template_access_key,
    sandbox_template_id = ctm.sandbox_template_id,
    display_order = ctm.display_order,
    updated_at = CURRENT_TIMESTAMP
FROM communication_templates_master ctm
WHERE hct.template_key::text = ctm.template_key
    AND hct.template_type = ctm.template_type
    AND hct.template_key::text = 'OTP_EMAIL'
    AND ctm.program_type_id IS NULL;

-- Update Generic Email template
UPDATE hdb_communication_templates hct
SET 
    template_access_key = ctm.template_access_key,
    sandbox_template_id = ctm.sandbox_template_id,
    display_order = ctm.display_order,
    updated_at = CURRENT_TIMESTAMP
FROM communication_templates_master ctm
WHERE hct.template_key::text = ctm.template_key
    AND hct.template_type = ctm.template_type
    AND hct.template_key::text = 'GENERIC_EMAIL'
    AND ctm.program_type_id IS NULL;

-- ============================================================
-- PART 4: Apply Recipient-Specific Access Key Updates
-- ============================================================
-- Based on files 09 and 10, update to recipient-specific variants

-- BLESSED_NO_PAYMENT: Update 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';

-- INVOICE: Update 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';

-- PAYMENT_ACKNOWLEDGEMENT_OFFLINE: Update 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';

-- REGISTRATION_REFUND: Update 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';

-- REGISTRATION_CANCELLED: Update 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';

-- BILLING_DETAILS_EDIT: Update 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';

-- BILLING_DETAILS_REMOVED: Update 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';

-- SWAP_DEMAND: Update 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';

-- EINVOICE_ERROR: Update to SEEKER-specific 
UPDATE hdb_communication_templates
SET template_access_key = 'EINVOICE_ERROR_EMAIL_SEEKER'
WHERE template_access_key = 'EINVOICE_ERROR'
    AND template_type = 'email';

-- HOLD: Update to SEEKER-specific
UPDATE hdb_communication_templates
SET template_access_key = 'HOLD_EMAIL_SEEKER'
WHERE template_access_key = 'HOLD'
    AND template_type = 'email';

-- RM_HOLD: Update to RM-specific
UPDATE hdb_communication_templates
SET template_access_key = 'RM_HOLD_EMAIL_RM'
WHERE template_access_key = 'RM_HOLD_EMAIL_RM'
    AND template_type = 'email';

update hdb_communication_templates
set template_access_key = 'PREFERENCE_EDITED_WATI_RM'
where template_access_key = 'PREFERENCE_EDITED' and template_type = 'whatsapp';


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

-- Check template_access_key distribution
SELECT 
    template_access_key,
    template_type,
    COUNT(*) AS template_count,
    COUNT(DISTINCT program_id) AS program_count
FROM hdb_communication_templates
WHERE template_access_key IS NOT NULL
GROUP BY template_access_key, template_type
ORDER BY template_type, template_access_key;

-- Check templates with merge fields
SELECT 
    hct.template_key,
    hct.template_name,
    hct.template_access_key,
    hct.program_id,
    COUNT(mf.id) AS merge_field_count,
    COUNT(CASE WHEN mf.is_active THEN 1 END) AS active_merge_fields
FROM hdb_communication_templates hct
LEFT JOIN merge_info_answer_location_map mf ON hct.id = mf.template_id
WHERE hct.program_id IS NOT NULL
GROUP BY hct.id, hct.template_key, hct.template_name, hct.template_access_key, hct.program_id
ORDER BY hct.program_id, hct.template_key;

-- Check for templates without access keys
SELECT 
    id,
    template_key,
    template_name,
    template_type,
    program_id
FROM hdb_communication_templates
WHERE template_access_key IS NULL
ORDER BY program_id, template_key;

-- Check merge field sources
SELECT 
    mf.source_table,
    mf.source_column,
    COUNT(*) AS usage_count,
    COUNT(DISTINCT mf.template_id) AS template_count
FROM merge_info_answer_location_map mf
INNER JOIN hdb_communication_templates hct ON mf.template_id = hct.id
WHERE mf.is_active = true
    AND hct.program_id IS NOT NULL
GROUP BY mf.source_table, mf.source_column
ORDER BY usage_count DESC;

-- Summary by program
SELECT 
    p.id AS program_id,
    p.name AS program_name,
    COUNT(DISTINCT hct.id) AS template_count,
    COUNT(DISTINCT hct.template_access_key) AS unique_access_keys,
    COUNT(mf.id) AS total_merge_fields,
    COUNT(CASE WHEN mf.is_active THEN 1 END) AS active_merge_fields
FROM program_v1 p
LEFT JOIN hdb_communication_templates hct ON p.id = hct.program_id
LEFT JOIN merge_info_answer_location_map mf ON hct.id = mf.template_id
WHERE p.deleted_at IS NULL
    AND p.program_type_id = (SELECT id FROM program_type_v1 WHERE key = 'PT_HDBMSD')
GROUP BY p.id, p.name
ORDER BY p.id;

-- ============================================================
-- END OF MIGRATION
-- ============================================================
