-- ================================================
-- Migration: Absent (Pre-Final) — session communication templates seed
--
-- Seeds the PRE_FINAL absentee EMAIL + WhatsApp (WATI) templates into
-- communication_templates_master and clones them to every PT_TAT program, mirroring
-- 28_session-communication-templates-seed.sql (the ABSENT REGULAR/FINAL rows).
--
-- Enum values (access keys + template keys) live in 37_absent-pre-final-enums.sql,
-- which must run before this file.
--
--   access key                              template_key                        purpose
--   --------------------------------------  ----------------------------------  ------------------
--   ABSENT_PRE_FINAL_SESSION_EMAIL_SEEKER   TAT_ABSENT_PRE_FINAL_EMAIL_SEEKER   Absent (pre-final)
--   ABSENT_PRE_FINAL_SESSION_WATI_SEEKER    TAT_ABSENT_PRE_FINAL_WATI_SEEKER    Absent (pre-final)
--
-- The PRE_FINAL variant is the absentee comm for the "last but one" (penultimate) session of
-- a multi-session TAT program (e.g. the 11th of 12). It drops final_but_one_session_date from
-- the REGULAR merge set (that date IS this session), keeping final_session_date so the seeker
-- is reminded of the one remaining session.
--
-- Both channels are seeded: EMAIL (ZeptoMail template_id + sandbox) and WhatsApp
-- (WATI template_type='whatsapp', template_id = WATI template name, no sandbox).
--
-- ------------------------------------------------------------------------------------------
-- APPLICATION WIRING REQUIRED (not part of this seed): for this occurrence to be resolved and
-- sent, the app layer must also be extended —
--   • SessionOccurrence: add PRE_FINAL (session-communication.constants.ts).
--   • resolveOccurrence: return PRE_FINAL for the penultimate TAT session, ahead of FINAL
--     (session-communication.service.ts) — today the penultimate session resolves to REGULAR.
--   • SESSION_COMMUNICATION_TEMPLATES: add the two descriptors mapping
--     ABSENT + PRE_FINAL → the access keys above (session-communication.constants.ts).
--   • CommunicationTemplateAccessKeyEnum: add the two access-key values
--     (src/common/enum/communication-template-access-key.enum.ts).
-- ------------------------------------------------------------------------------------------
--
-- Idempotent — safe to re-run.
-- ================================================

-- ------------------------------------------------
-- PART 1: Insert master template rows (program_type_id = PT_TAT)
-- ------------------------------------------------

-- 1) Absent — pre-final session — Email
INSERT INTO communication_templates_master (
    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, is_active
) VALUES (
    'TAT_ABSENT_PRE_FINAL_EMAIL_SEEKER',
    'Online Session — Absent (Pre-Final) — Email to Seeker',
    'email',
    '2d6f.2a60732b5ed84938.k1.e2b79f21-85dc-11f1-ad6a-fae9afc80e45.19f8a4fda11',
    '2d6f.2a60732b5ed84938.k1.b2be2b30-85dd-11f1-ad6a-fae9afc80e45.19f8a552d63',
    NULL, 'general', 'ABSENT_PRE_FINAL_SESSION_EMAIL_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_TAT' AND deleted_at IS NULL),
    '[
        {"keyName": "reg_name",           "sourceTable": "hdb_program_registration", "sourceColumn": "full_name",       "dataType": "string", "isCommon": false, "isNullable": false},
        {"keyName": "program_name",       "sourceTable": "computed", "sourceColumn": "getProgramField:name",       "dataType": "string", "isCommon": false, "isNullable": false},
        {"keyName": "final_session_date", "sourceTable": "computed", "sourceColumn": "getProgramFinalSessionDate", "dataType": "string", "isCommon": false, "isNullable": true},
        {"keyName": "absentDataTabel",    "sourceTable": "computed", "sourceColumn": "getAbsentSessionsTable",     "dataType": "string", "isCommon": false, "isNullable": true, "defaultValue": ""}
    ]'::jsonb,
    36, true
) ON CONFLICT (template_key, COALESCE(program_type_id, 0), template_type) DO UPDATE SET
    template_id = EXCLUDED.template_id, sandbox_template_id = EXCLUDED.sandbox_template_id,
    template_access_key = EXCLUDED.template_access_key, merge_field_map = EXCLUDED.merge_field_map,
    updated_at = CURRENT_TIMESTAMP;

-- 2) Absent — pre-final session — WATI
INSERT INTO communication_templates_master (
    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, is_active
) VALUES (
    'TAT_ABSENT_PRE_FINAL_WATI_SEEKER',
    'Online Session — Absent (Pre-Final) — WhatsApp to Seeker',
    'whatsapp',
    'tat_session_absent_pre_final_v1',
    NULL,
    NULL, 'general', 'ABSENT_PRE_FINAL_SESSION_WATI_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_TAT' AND deleted_at IS NULL),
    '[
        {"keyName": "reg_name",           "sourceTable": "hdb_program_registration", "sourceColumn": "full_name",       "dataType": "string", "isCommon": false, "isNullable": false},
        {"keyName": "program_name",       "sourceTable": "computed", "sourceColumn": "getProgramField:name",       "dataType": "string", "isCommon": false, "isNullable": false},
        {"keyName": "final_session_date", "sourceTable": "computed", "sourceColumn": "getProgramFinalSessionDate", "dataType": "string", "isCommon": false, "isNullable": true},
        {"keyName": "absent_session",     "sourceTable": "computed", "sourceColumn": "getAbsentSessionsList",      "dataType": "string", "isCommon": false, "isNullable": true, "defaultValue": ""}
    ]'::jsonb,
    46, true
) ON CONFLICT (template_key, COALESCE(program_type_id, 0), template_type) DO UPDATE SET
    template_id = EXCLUDED.template_id, sandbox_template_id = EXCLUDED.sandbox_template_id,
    template_access_key = EXCLUDED.template_access_key, merge_field_map = EXCLUDED.merge_field_map,
    updated_at = CURRENT_TIMESTAMP;

-- ------------------------------------------------
-- PART 2: Clone each master row to every PT_TAT program, and expand its
-- merge_field_map into merge_info_answer_location_map.
-- is_enabled = false — admins enable per program when configuring session comms.
-- ------------------------------------------------
DO $$
DECLARE
    sc_keys TEXT[] := ARRAY[
        'TAT_ABSENT_PRE_FINAL_EMAIL_SEEKER',
        'TAT_ABSENT_PRE_FINAL_WATI_SEEKER'
    ];
    sc_key          TEXT;
    master_rec      RECORD;
    prog_rec        RECORD;
    merge_field     JSONB;
    new_template_id BIGINT;
    cloned_count    INT := 0;
    skipped_count   INT := 0;
BEGIN
    FOREACH sc_key IN ARRAY sc_keys LOOP
        SELECT id, template_id, template_name, template_key, template_type,
               sandbox_template_id, category, template_usage_type,
               template_access_key, merge_field_map, display_order
        INTO master_rec
        FROM communication_templates_master
        WHERE template_key = sc_key AND is_active = true
        LIMIT 1;

        IF master_rec IS NULL THEN
            RAISE EXCEPTION 'Master template % not found — run PART 1 first', sc_key;
        END IF;

        -- Target PT_TAT programs
        FOR prog_rec IN
            SELECT p.id AS program_id, p.name AS program_name
            FROM program_v1 p
            JOIN program_type_v1 pt ON pt.id = p.program_type_id
            WHERE pt.key        = 'PT_TAT'
              AND pt.deleted_at IS NULL
              AND p.deleted_at  IS NULL
            ORDER BY p.id
        LOOP
            IF EXISTS (
                SELECT 1 FROM hdb_communication_templates h
                WHERE h.program_id          = prog_rec.program_id
                  AND h.template_key::text  = sc_key
                  AND h.template_type       = master_rec.template_type
            ) THEN
                skipped_count := skipped_count + 1;
                CONTINUE;
            END IF;

            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, is_enabled, display_order, created_at, updated_at
            ) VALUES (
                master_rec.template_id, master_rec.template_name,
                master_rec.template_key::template_key_enum, master_rec.template_type,
                master_rec.sandbox_template_id, master_rec.category, master_rec.template_usage_type,
                master_rec.template_access_key::communication_template_access_key_enum,
                prog_rec.program_id, false, master_rec.display_order,
                CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
            )
            RETURNING id INTO new_template_id;

            IF master_rec.merge_field_map IS NOT NULL
               AND jsonb_array_length(master_rec.merge_field_map) > 0 THEN
                FOR merge_field IN SELECT * FROM jsonb_array_elements(master_rec.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;

            cloned_count := cloned_count + 1;
        END LOOP;
    END LOOP;

    RAISE NOTICE '======================================';
    RAISE NOTICE 'Absent (pre-final) templates — cloned: %, skipped (already present): %',
        cloned_count, skipped_count;
    RAISE NOTICE '======================================';
END $$;

-- ------------------------------------------------
-- Verification
-- ------------------------------------------------
SELECT template_key, template_access_key, template_id, sandbox_template_id, is_active
FROM communication_templates_master
WHERE template_access_key IN (
    'ABSENT_PRE_FINAL_SESSION_EMAIL_SEEKER', 'ABSENT_PRE_FINAL_SESSION_WATI_SEEKER'
)
ORDER BY template_key;

SELECT hct.template_key::text AS template_key,
       COUNT(DISTINCT hct.program_id) AS cloned_programs,
       COUNT(mf.id) AS merge_fields
FROM hdb_communication_templates hct
LEFT JOIN merge_info_answer_location_map mf ON mf.template_id = hct.id
WHERE hct.template_access_key IN (
    'ABSENT_PRE_FINAL_SESSION_EMAIL_SEEKER', 'ABSENT_PRE_FINAL_SESSION_WATI_SEEKER'
)
GROUP BY hct.template_key::text
ORDER BY hct.template_key::text;
