-- =====================================================================
-- Update: TAT Payment Link (WhatsApp) template access key & category
-- ---------------------------------------------------------------------
-- The TAT_PAYMENT_LINK_WHATSAPP template (template_id: otat_payment_link_msg_v3)
-- was seeded/repurposed with a registration-completed access key, which is
-- semantically wrong for a payment-link message. This re-points it to the
-- dedicated PAYMENT_LINK_WATI_SEEKER access key (basic registration done,
-- payment pending) so it resolves correctly for the payment-link API flow.
--
-- Idempotent: safe to run multiple times.
-- NOTE: run with per-statement autocommit (default psql -f). The new enum
-- value is committed by the ALTER below before the UPDATE references it.
-- =====================================================================

-- Register the dedicated payment-link access key & category (no-op if they already exist):
ALTER TYPE communication_template_access_key_enum ADD VALUE IF NOT EXISTS 'PAYMENT_LINK_WATI_SEEKER';
ALTER TYPE communication_category_enum ADD VALUE IF NOT EXISTS 'payment_link';

-- Before:
SELECT template_key, category, template_access_key, template_id
FROM public.communication_templates_master
WHERE template_key = 'TAT_PAYMENT_LINK_WHATSAPP'
  AND program_type_id IN (SELECT id FROM program_type_v1 WHERE key = 'PT_TAT');

UPDATE public.communication_templates_master
SET
    category = 'payment_link',
    template_access_key = 'PAYMENT_LINK_WATI_SEEKER',
    is_active = true,
    updated_at = CURRENT_TIMESTAMP
WHERE template_key = 'TAT_PAYMENT_LINK_WHATSAPP'
  AND program_type_id IN (SELECT id FROM program_type_v1 WHERE key = 'PT_TAT');

-- After (verify):
SELECT template_key, category, template_access_key, template_id
FROM public.communication_templates_master
WHERE template_key = 'TAT_PAYMENT_LINK_WHATSAPP'
  AND program_type_id IN (SELECT id FROM program_type_v1 WHERE key = 'PT_TAT');

-- =====================================================================
-- PART 2: Clone the PAYMENT_LINK_WATI_SEEKER (WhatsApp) template to a
--         specific program and expand its merge fields.
-- ---------------------------------------------------------------------
-- Target program: 1393
-- Template metadata (template_id / name / sandbox id / display_order) is
-- read from the master row so it stays the single source of truth.
-- is_enabled = true  -> the payment-link API can resolve & fire this clone.
--
-- Idempotent: the hdb_communication_templates insert is guarded by NOT
-- EXISTS, and the merge-field insert only fires for the row(s) actually
-- created (empty CTE => no merge rows inserted on re-run).
-- =====================================================================

WITH ins_template AS (
    INSERT INTO hdb_communication_templates (
        template_id,
        template_name,
        template_key,
        sandbox_template_id,
        template_type,
        category,
        template_usage_type,
        template_access_key,
        program_id,
        is_enabled,
        display_order,
        created_at,
        updated_at
    )
    SELECT
        m.template_id,
        m.template_name,
        'TAT_PAYMENT_LINK_WHATSAPP',
        m.sandbox_template_id,
        'whatsapp'::communication_type_enum,
        'payment_link'::communication_category_enum,
        'general'::template_usage_type_enum,
        'PAYMENT_LINK_WATI_SEEKER'::communication_template_access_key_enum,
        1393,
        true,
        m.display_order,
        CURRENT_TIMESTAMP,
        CURRENT_TIMESTAMP
    FROM communication_templates_master m
    WHERE m.template_key   = 'TAT_PAYMENT_LINK_WHATSAPP'
      AND m.template_type  = 'whatsapp'
      AND NOT EXISTS (
          SELECT 1
          FROM hdb_communication_templates h
          WHERE h.program_id    = 1393
            AND h.template_key   = 'TAT_PAYMENT_LINK_WHATSAPP'
            AND h.template_type  = 'whatsapp'
            AND h.deleted_at IS NULL
      )
    RETURNING id
)
INSERT INTO merge_info_answer_location_map (
    key_name,
    template_id,
    is_common,
    source_table,
    source_column,
    data_type,
    format_type,
    is_nullable,
    is_active,
    created_at,
    updated_at
)
SELECT
    mf.key_name,
    ins_template.id,
    false,
    mf.source_table,
    mf.source_column,
    mf.data_type,
    mf.format_type,
    mf.is_nullable,
    true,
    CURRENT_TIMESTAMP,
    CURRENT_TIMESTAMP
FROM ins_template
CROSS JOIN (VALUES
    ('reg_name',   'hdb_program_registration', 'full_name',            'string', NULL::varchar, false),
    ('batch_name', 'computed',                 'getProgramField:name', 'string', NULL,          false),
    ('link',       'computed',                 'generatePaymentLink',  'string', 'url',         false),
    ('reg_mobile', 'hdb_program_registration', 'mobile_number',        'string', NULL,          false),
    ('reg_email',  'hdb_program_registration', 'email_address',        'string', NULL,          false),
    ('reg_rm',     'computed',                 'getRmContactUserName', 'string', NULL,          true)
) AS mf(key_name, source_table, source_column, data_type, format_type, is_nullable);

-- Verify the program-level clone + its merge fields:
SELECT h.id, h.program_id, h.template_key, h.template_access_key, h.category, h.is_enabled,
       COUNT(mf.id) AS merge_field_count
FROM hdb_communication_templates h
LEFT JOIN merge_info_answer_location_map mf ON mf.template_id = h.id
WHERE h.program_id = 1393
  AND h.template_key = 'TAT_PAYMENT_LINK_WHATSAPP'
  AND h.template_type = 'whatsapp'
GROUP BY h.id, h.program_id, h.template_key, h.template_access_key, h.category, h.is_enabled;
