-- =============================================================================
-- Update the WATI template id and merge fields for the ENT Waitlist Release
-- (Payment) WhatsApp template.
--
-- - template_id: erc_entrainment_waitinglist_release_prod_v5
--            -> erc_entrainment_waitinglist_release_v1
-- - merge fields: renames the "link" key to "payment_link" (WATI template
--   placeholder rename) but keeps the same reg_name / course_name mappings.
--
-- Each template lives in two places — communication_templates_master (the
-- PT_ENTRAINMENT master row) and hdb_communication_templates (per-program
-- cloned rows, whose merge fields are expanded into
-- merge_info_answer_location_map). Both are updated here; cloned merge rows
-- are rebuilt from the master map since a key name changed.
-- Pattern mirrors database/program-configuration/48_reset_tat_invite_wati_templates_and_merge.sql.
--
-- There is no program-level/sub-level column on either table — drill-down is
-- template_key + template_type + program_type_id (communication_templates_master's
-- actual unique index), and program_id via program_v1/program_type_v1 for the clones.
--
-- Idempotent — safe to re-run (unconditional set + rebuild from master).
-- Run PER ENVIRONMENT (dev / staging / prod). Never run against prod from a local box.
-- Review the verification output before COMMIT.
-- =============================================================================

BEGIN;

-- 1. Master row
UPDATE communication_templates_master
SET
    template_id = 'erc_entrainment_waitinglist_release_v1',
    merge_field_map = '[
        {"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": "payment_link", "sourceTable": "computed", "sourceColumn": "generatePaymentLink", "dataType": "string", "formatType": "url", "isNullable": false}
    ]'::jsonb,
    updated_at = CURRENT_TIMESTAMP
WHERE template_key = 'ENT_WAITLIST_RELEASE_PAYMENT_WHATSAPP'
  AND template_type = 'whatsapp'
  AND program_type_id = (SELECT id FROM program_type_v1 WHERE key = 'PT_ENTRAINMENT');

-- 2. Per-program cloned rows: sync template_id from the master row and rebuild
--    merge_info_answer_location_map from the (now updated) master merge map.
DO $$
DECLARE
    master_id     TEXT;
    master_map    JSONB;
    hct_rec       RECORD;
    merge_field   JSONB;
    updated_count INT := 0;
BEGIN
    SELECT template_id, merge_field_map
    INTO master_id, master_map
    FROM communication_templates_master
    WHERE template_key = 'ENT_WAITLIST_RELEASE_PAYMENT_WHATSAPP'
      AND template_type = 'whatsapp'
      AND program_type_id = (SELECT id FROM program_type_v1 WHERE key = 'PT_ENTRAINMENT')
    LIMIT 1;

    IF master_map IS NULL THEN
        RAISE EXCEPTION 'Master template ENT_WAITLIST_RELEASE_PAYMENT_WHATSAPP not found for PT_ENTRAINMENT';
    END IF;

    FOR hct_rec IN
        SELECT hct.id
        FROM hdb_communication_templates hct
        JOIN program_v1 p       ON p.id  = hct.program_id
        JOIN program_type_v1 pt ON pt.id = p.program_type_id
        WHERE pt.key = 'PT_ENTRAINMENT'
          AND hct.template_key::text = 'ENT_WAITLIST_RELEASE_PAYMENT_WHATSAPP'
          AND hct.template_type = 'whatsapp'
          AND hct.deleted_at IS NULL
    LOOP
        UPDATE hdb_communication_templates
        SET template_id = master_id, updated_at = CURRENT_TIMESTAMP
        WHERE id = hct_rec.id;

        DELETE FROM merge_info_answer_location_map WHERE template_id = hct_rec.id;

        FOR merge_field IN SELECT * FROM jsonb_array_elements(master_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',
                hct_rec.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;

        updated_count := updated_count + 1;
    END LOOP;

    RAISE NOTICE '======================================';
    RAISE NOTICE 'ENT waitlist release (payment) WATI template — cloned rows reset: %', updated_count;
    RAISE NOTICE '======================================';
END $$;

-- ------------------------------------------------
-- Verification
-- ------------------------------------------------
SELECT template_key, template_id, is_active
FROM communication_templates_master
WHERE template_key = 'ENT_WAITLIST_RELEASE_PAYMENT_WHATSAPP'
  AND template_type = 'whatsapp';

SELECT hct.template_key::text AS template_key,
       hct.program_id,
       hct.template_id,
       COUNT(mf.id)                                       AS merge_fields,
       STRING_AGG(mf.key_name, ', ' ORDER BY mf.key_name) AS merge_keys
FROM hdb_communication_templates hct
LEFT JOIN merge_info_answer_location_map mf ON mf.template_id = hct.id
WHERE hct.template_key::text = 'ENT_WAITLIST_RELEASE_PAYMENT_WHATSAPP'
  AND hct.template_type = 'whatsapp'
  AND hct.deleted_at IS NULL
GROUP BY hct.template_key::text, hct.program_id, hct.template_id
ORDER BY hct.program_id;

COMMIT;
