-- ============================================================
-- Clone PT_TAT E-INVOICE ERROR EMAIL to existing TAT programs
-- ============================================================
-- The e-invoice failure email (template_key TAT_EINVOICE_ERROR_EMAIL,
-- access key EINVOICE_ERROR_EMAIL_SEEKER) was added to the PT_TAT master
-- (see 23_master_seed_data_tat_einvoice_error.sql) AFTER existing TAT
-- programs had already been cloned. This backfills the per-program table
-- hdb_communication_templates (+ its merge_info_answer_location_map rows)
-- for every active TAT program that does not already have it.
--
-- Mirrors the clone logic in 09_clone-templates-to-existing-programs.sql,
-- scoped to PT_TAT + this single template. Idempotent: programs that
-- already have the template are skipped.
--
-- PREREQUISITE: the template_key_enum value 'TAT_EINVOICE_ERROR_EMAIL' must already
-- be COMMITTED before running this file. It is added by 23_master_seed_data_tat_einvoice_error.sql.
-- (Postgres forbids adding a new enum value and using it in the same transaction —
--  error 55P04. Run file 23 first, in its own transaction, then run this file.)
-- ============================================================

DO $$
DECLARE
    master_record RECORD;
    prog_record   RECORD;
    new_template_id BIGINT;
    merge_field   JSONB;
    cloned_count  INT := 0;
    existing_count INT := 0;
BEGIN
    -- Single PT_TAT master template we are backfilling.
    SELECT
        ctm.template_id,
        ctm.template_name,
        ctm.template_key,
        ctm.template_type,
        ctm.sandbox_template_id,
        ctm.category,
        ctm.template_usage_type,
        ctm.template_access_key,
        ctm.merge_field_map,
        ctm.display_order
    INTO master_record
    FROM communication_templates_master ctm
    WHERE ctm.program_type_id = (SELECT id FROM program_type_v1 WHERE key = 'PT_TAT')
        AND ctm.template_key = 'TAT_EINVOICE_ERROR_EMAIL'
        AND ctm.template_type = 'email'
        AND ctm.is_active = true;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'PT_TAT master template TAT_EINVOICE_ERROR_EMAIL not found — run 23_master_seed_data_tat_einvoice_error.sql first';
    END IF;

    -- All active PRIMARY TAT programs.
    FOR prog_record IN
        SELECT p.id AS program_id, p.name AS program_name
        FROM program_v1 p
        INNER JOIN program_type_v1 pt ON p.program_type_id = pt.id
        WHERE pt.key = 'PT_TAT'
            AND p.deleted_at IS NULL
            AND (p.is_primary_program = true OR p.primary_program_id IS NULL)
        ORDER BY p.id
    LOOP
        -- Skip if this program already has the template.
        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
            existing_count := existing_count + 1;
            RAISE NOTICE '  ✓ Already present for program % (%), skipping', prog_record.program_id, prog_record.program_name;
            CONTINUE;
        END IF;

        -- Clone the program-specific template row.
        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;

        cloned_count := cloned_count + 1;
        RAISE NOTICE '  ✓ Cloned to program % (%), new template id %', prog_record.program_id, prog_record.program_name, new_template_id;

        -- Expand merge fields for the new program template.
        IF master_record.merge_field_map IS NOT NULL
            AND jsonb_array_length(master_record.merge_field_map) > 0 THEN
            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;
        END IF;
    END LOOP;

    RAISE NOTICE '======================================';
    RAISE NOTICE 'TAT E-Invoice Error template backfill complete: % cloned, % already present', cloned_count, existing_count;
END $$;

-- ============================================================
-- VERIFICATION QUERY
-- ============================================================
-- SELECT p.id AS program_id, p.name,
--        (hct.id IS NOT NULL) AS has_einvoice_error_template
-- FROM program_v1 p
-- INNER JOIN program_type_v1 pt ON p.program_type_id = pt.id
-- LEFT JOIN hdb_communication_templates hct
--        ON hct.program_id = p.id
--       AND hct.template_key::text = 'TAT_EINVOICE_ERROR_EMAIL'
--       AND hct.template_type = 'email'
-- WHERE pt.key = 'PT_TAT'
--   AND p.deleted_at IS NULL
--   AND (p.is_primary_program = true OR p.primary_program_id IS NULL)
-- ORDER BY p.id;
