
-- This function clones all master templates for a given program_type_id to specific PRIMARY programs
-- It also expands the merge_field_map JSON into merge_info_answer_location_map table

-- ============================================================
-- PART 0: Ensure all template keys exist in template_key_enum
-- Each statement must auto-commit before the clone DO block runs.
-- DO NOT wrap these in a DO $$ block — new enum values must be
-- committed before they can be used in the same session.
-- ============================================================
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'GENERIC_EMAIL';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'OTP_EMAIL';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'OTP_SMS';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'HDB_BLESSED_EMAIL';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'HDB_BLESSED_WHATSAPP';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'HDB_BLESSED_RM_WHATSAPP';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'HDB_BLESSED_NO_PAYMENT_EMAIL';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'HDB_BLESSED_NO_PAYMENT_WHATSAPP';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'HDB_REGISTRATION_COMPLETED_EMAIL';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'HDB_SEEKER_REGISTRATION_COMPLETED_WHATSAPP';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'HDB_RM_REGISTRATION_COMPLETED_WHATSAPP';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'HDB_RM_REGISTRATION_NOTIFY_SMS';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'HDB_CANCEL_EMAIL';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'HDB_REGISTRATION_REFUND_EMAIL';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'HDB_HOLD_EMAIL';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'HDB_RM_HOLD_EMAIL';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'HDB_INVOICE_EMAIL';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'HDB_INVOICE_WHATSAPP';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'HDB_PAYMENT_ACKNOWLEDGEMENT_OFFLINE_EMAIL';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'HDB_PAYMENT_ACKNOWLEDGEMENT_OFFLINE_WHATSAPP';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'HDB_RM_PAYMENT_PENDING_REMINDER_WHATSAPP';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'HDB_BILLING_DETAILS_EDIT_EMAIL';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'HDB_BILLING_DETAILS_EDIT_REMOVED_EMAIL';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'HDB_EINVOICE_ERROR_EMAIL';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'HDB_COORDINATOR_TRAVEL_PLAN_CHANGE_WHATSAPP';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'HDB_COORDINATOR_NEW_TRAVEL_PLAN_MADE_WHATSAPP';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'HDB_COORDINATOR_RETURN_TRAVEL_PLAN_CHANGE_WHATSAPP';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'HDB_COORDINATOR_ONWARD_TRAVEL_PLAN_CHANGE_WHATSAPP';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'HDB_SWAP_DEMAND_EMAIL';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'HDB_SWAP_DEMAND_WHATSAPP';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'HDB_RM_PREFERENCE_EDITED_WHATSAPP';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'HDB_ADMIN_MESSAGE_NOTIFICATION_WHATSAPP';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'HDB_RM_BLESS_NOTIFY_SMS';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'BLESSED_EMAIL_SEEKER';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'BLESSED_WATI_SEEKER';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'BLESSED_WATI_RM';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'BLESSED_SMS_RM';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'BLESSED_NO_PAYMENT_EMAIL_SEEKER';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'BLESSED_NO_PAYMENT_WATI_SEEKER';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'REGISTRATION_COMPLETED_EMAIL_SEEKER';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'REGISTRATION_COMPLETED_WATI_SEEKER';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'REGISTRATION_COMPLETED_WATI_RM';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'REGISTRATION_COMPLETED_SMS_RM';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'REGISTRATION_CANCELLED_EMAIL_SEEKER';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'REGISTRATION_REFUND_EMAIL_FINANCE';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'HOLD_EMAIL_SEEKER';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'RM_HOLD_EMAIL_RM';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'INVOICE_EMAIL_SEEKER';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'INVOICE_WATI_SEEKER';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'PAYMENT_ACKNOWLEDGEMENT_OFFLINE_EMAIL_SEEKER';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'PAYMENT_ACKNOWLEDGEMENT_OFFLINE_WATI_SEEKER';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'PAYMENT_PENDING_REMINDER_WATI_RM';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'BILLING_DETAILS_EDIT_EMAIL_SEEKER';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'BILLING_DETAILS_EDIT_REMOVED_EMAIL_SEEKER';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'EINVOICE_ERROR_EMAIL_SEEKER';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'CO_ORDINATOR_TRAVEL_PLAN_CHANGE_WATI';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'CO_ORDINATOR_NEW_TRAVEL_PLAN_MADE_WATI';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'CO_ORDINATOR_RETURN_TRAVEL_PLAN_CHANGE_WATI';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'CO_ORDINATOR_ONWARD_TRAVEL_PLAN_CHANGE_WATI';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'SWAP_DEMAND_EMAIL_SEEKER';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'SWAP_DEMAND_WATI_SEEKER';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'PREFERENCE_EDITED_WATI_RM';
ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'ADMIN_MESSAGE_NOTIFICATION_WATI';

-- ============================================================
-- PART 0B: Fix sequences that may be out of sync with existing data
-- ============================================================
SELECT setval(
    pg_get_serial_sequence('merge_info_answer_location_map', 'id'),
    COALESCE((SELECT MAX(id) FROM merge_info_answer_location_map), 0) + 1,
    false
);

SELECT setval(
    pg_get_serial_sequence('hdb_communication_templates', 'id'),
    COALESCE((SELECT MAX(id) FROM hdb_communication_templates), 0) + 1,
    false
);

-- Drop the global unique constraint so the same key_name can exist across multiple templates
ALTER TABLE merge_info_answer_location_map
    DROP CONSTRAINT IF EXISTS merge_info_answer_location_map_key_name_key;

DO $$
DECLARE
    prog_record RECORD;
    master_record RECORD;
    new_template_id BIGINT;
    merge_field JSONB;
    cloned_count INT := 0;
    existing_count INT := 0;
BEGIN
    RAISE NOTICE '======================================';
    RAISE NOTICE 'Starting Communication Template Cloning Process (Primary Programs Only)';
    RAISE NOTICE '======================================';
    
    -- Loop through all active PRIMARY programs only
    FOR prog_record IN 
        SELECT 
            p.id AS program_id,
            p.name AS program_name,
            p.program_type_id AS program_type_id,
            pt.key AS program_type_key
        FROM program_v1 p
        INNER JOIN program_type_v1 pt ON p.program_type_id = pt.id
        WHERE p.deleted_at IS NULL
            AND (p.is_primary_program = true OR p.primary_program_id IS NULL)
        ORDER BY p.id
    LOOP
        RAISE NOTICE '--------------------------------------';
        RAISE NOTICE 'Processing Program ID: %, Name: %, Type: %', 
            prog_record.program_id, 
            prog_record.program_name, 
            prog_record.program_type_key;
        RAISE NOTICE '--------------------------------------';
        
        -- Loop through all master templates for this program type
        FOR master_record IN
            SELECT 
                id,
                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
            FROM communication_templates_master
            WHERE program_type_id = prog_record.program_type_id
                AND is_active = true
            ORDER BY display_order
        LOOP
            -- Check if template already exists for this program
            IF EXISTS (
                SELECT 1 
                FROM hdb_communication_templates 
                WHERE program_id = prog_record.program_id
                    AND template_key::text = master_record.template_key
                    AND template_type = master_record.template_type
            ) THEN
                RAISE NOTICE '  ✓ Template % (%) already exists for program %, skipping...', 
                    master_record.template_name, 
                    master_record.template_key,
                    prog_record.program_id;
                existing_count := existing_count + 1;
                CONTINUE;
            END IF;
            
            -- Insert program-specific template
            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,
                display_order,
                created_at,
                updated_at
            ) VALUES (
                master_record.template_id,
                master_record.template_name,
                master_record.template_key::template_key_enum,
                master_record.template_type,
                master_record.sandbox_template_id,
                master_record.category,
                master_record.template_usage_type,
                master_record.template_access_key,
                prog_record.program_id,
                master_record.display_order,
                CURRENT_TIMESTAMP,
                CURRENT_TIMESTAMP
            )
            RETURNING id INTO new_template_id;
            
            RAISE NOTICE '  ✓ Cloned template: % (%) to program ID: %, New Template ID: %', 
                master_record.template_name,
                master_record.template_key,
                prog_record.program_id,
                new_template_id;
            
            cloned_count := cloned_count + 1;

            -- Insert merge field mappings if they exist
            IF master_record.merge_field_map IS NOT NULL
                AND jsonb_array_length(master_record.merge_field_map) > 0 THEN

                RAISE NOTICE '    → Expanding % merge fields...',
                    jsonb_array_length(master_record.merge_field_map);

                FOR merge_field IN SELECT * FROM jsonb_array_elements(master_record.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;

                RAISE NOTICE '    → Merge fields created successfully';
            END IF;
            
        END LOOP;
        
    END LOOP;
    
    RAISE NOTICE '======================================';
    RAISE NOTICE 'Template Cloning Summary:';
    RAISE NOTICE '  - Templates Cloned: %', cloned_count;
    RAISE NOTICE '  - Templates Already Existed: %', existing_count;
    RAISE NOTICE '  - Total Processed: %', cloned_count + existing_count;
    RAISE NOTICE '======================================';
    
END $$;

-- ============================================================
-- PART 2: Verification Queries
-- ============================================================

-- Verify program templates were created for primary programs
SELECT 
    'Program Templates Created (Primary Programs)' AS status,
    COUNT(*) AS total_templates,
    COUNT(DISTINCT program_id) AS programs_with_templates,
    COUNT(DISTINCT template_key) AS unique_template_keys
FROM hdb_communication_templates
WHERE program_id IS NOT NULL;

-- Show templates by primary program only
SELECT 
    p.id AS program_id,
    p.name AS program_name,
    pt.key AS program_type,
    p.is_primary_program,
    COUNT(ct.id) AS template_count
FROM program_v1 p
LEFT JOIN program_type_v1 pt ON p.program_type_id = pt.id
LEFT JOIN hdb_communication_templates ct ON p.id = ct.program_id
WHERE p.deleted_at IS NULL
    AND (p.is_primary_program = true OR p.primary_program_id IS NULL)
GROUP BY p.id, p.name, pt.key, p.is_primary_program
ORDER BY template_count DESC, p.id;

-- Show templates with merge field mappings (primary programs only)
SELECT 
    ct.program_id,
    ct.template_key,
    ct.template_name,
    ct.template_type,
    COUNT(mf.id) AS merge_field_count
FROM hdb_communication_templates ct
INNER JOIN program_v1 p ON ct.program_id = p.id
LEFT JOIN merge_info_answer_location_map mf ON ct.id = mf.template_id
WHERE ct.program_id IS NOT NULL
    AND (p.is_primary_program = true OR p.primary_program_id IS NULL)
GROUP BY ct.program_id, ct.template_key, ct.template_name, ct.template_type
ORDER BY ct.program_id, ct.template_key;

-- Show merge field mappings by template (primary programs only)
SELECT 
    mf.template_id,
    ct.template_key,
    ct.template_name,
    ct.program_id,
    mf.key_name,
    mf.source_table,
    mf.source_column,
    mf.data_type,
    mf.is_active
FROM merge_info_answer_location_map mf
INNER JOIN hdb_communication_templates ct ON mf.template_id = ct.id
INNER JOIN program_v1 p ON ct.program_id = p.id
WHERE ct.program_id IS NOT NULL
    AND (p.is_primary_program = true OR p.primary_program_id IS NULL)
ORDER BY ct.program_id, ct.template_key, mf.key_name;


-- for HDBMSD program type, update email sender and bcc details for all existing and future templates to use
update program_type_v1
set email_sender_name = 'HDB Team',
    email_sender_address = 'hdb@infinitheism.com',
    email_bcc_address = 'hdb@infinitheism.com',
    email_bcc_name = 'HDB Team'
where key = 'PT_HDBMSD';

update program_v1
set email_sender_name = 'HDB Team',
    email_sender_address = 'hdb@infinitheism.com',
    email_bcc_address = 'hdb@infinitheism.com',
    email_bcc_name = 'HDB Team'
where program_type_id = (select id from program_type_v1 where key = 'PT_HDBMSD');


-- for entrainment 
update program_type_v1
set email_sender_name = 'Entrainment Team',
    email_sender_address = 'noreply@infinitheism.com',
    email_bcc_address = 'noreply@infinitheism.com',
    email_bcc_name = 'Entrainment Team'
where key = 'PT_ENTRAINMENT';

update program_v1
set email_sender_name = 'Entrainment Team',
    email_sender_address = 'noreply@infinitheism.com',
    email_bcc_address = 'noreply@infinitheism.com',
    email_bcc_name = 'Entrainment Team'
where program_type_id = (select id from program_type_v1 where key = 'PT_ENTRAINMENT');

update program_type_v1
set email_sender_name = 'TAT Team',
    email_sender_address = 'noreply@infinitheism.com',
    email_bcc_address = 'noreply@infinitheism.com',
    email_bcc_name = 'TAT Team'
where key = 'PT_TAT';

update program_v1
set email_sender_name = 'TAT Team',
    email_sender_address = 'noreply@infinitheism.com',
    email_bcc_address = 'noreply@infinitheism.com',
    email_bcc_name = 'TAT Team'
where program_type_id = (select id from program_type_v1 where key = 'PT_TAT');

-- for HDBMSD, update all existing templates to set is_active = true for merge field mappings and update sourceTable to 'computed' and sourceColumn to 'getProgramField:name' for the hdb_msd variable
