-- ================================================
-- Migration: PCF-ENT — Parental Consent Communication Template for PT_ENTRAINMENT
-- Date: 2026-08-13
-- DRAFT ONLY — not executed against any database, not yet reviewed/approved.
-- Mirrors database/migrations/2026-06-11-02-parental-consent-communication-template.sql,
-- adapted for PT_ENTRAINMENT. Adds a PARENTAL_CONSENT_EMAIL_SEEKER template to the master
-- table and clones it to all existing PT_ENTRAINMENT programs with is_enabled = false.
-- Safe to re-run (idempotent).
-- ================================================
--
-- REAL TEMPLATE IDs FOUND — sourced from the legacy infini-hdb-be repo (a separate, older
-- backend), src/helpers/communication-triggers.ts, which already sends a parental consent
-- email for Entrainment today via two DIFFERENT live Zepto templates, branched by payment mode:
--   Offline trigger (line ~135): 2d6f.2a60732b5ed84938.k1.53820260-5cc4-11f0-b019-5254001dc20d.197ef439586
--   Online  trigger (line ~1038): 2d6f.2a60732b5ed84938.k1.fcf24061-5cc6-11f0-b019-5254001dc20d.197ef550760
-- (Cross-checked: the sibling ENT_REGISTRATION_COMPLETED_EMAIL id used by the online trigger
-- in that same legacy file exactly matches the one already seeded in this repo's
-- database/program-configuration/17_master_seed_data_entrainment.sql:31 — confirms these are
-- real production Zepto template IDs, not placeholders.)
--
-- DECISION — per instruction, follow the exact same pattern as PT_TAT: ONE
--   PARENTAL_CONSENT_EMAIL_SEEKER template per program, regardless of payment mode (see
--   src/payment/payment.service.ts:sendParentalConsentEmail, which looks up a single
--   templateKey via getTemplateWithMergeInfo(programId, PARENTAL_CONSENT_EMAIL_SEEKER, ...) —
--   same call TAT's single row already relies on). PART 2 below seeds the ONLINE-path id as
--   that single master template_id. The OFFLINE-path id is kept above only as a reference note
--   (it is not inserted anywhere) — no second row, no access key, no branching logic, matching
--   TAT's single-template simplicity.
--   sandbox_template_id (2d6f...88e0b121-96e5-11f1...) provided directly — no longer a placeholder.
-- ================================================

-- ------------------------------------------------
-- PART 1: Extend enums
-- These must run outside a DO block — new enum values
-- must be committed before they can be used below.
-- (communication_template_access_key_enum.PARENTAL_CONSENT_EMAIL_SEEKER and
--  communication_category_enum.parental_consent already exist, added by
--  database/migrations/2026-06-11-02-parental-consent-communication-template.sql — only
--  the ENT-specific template_key value is new here.)
--
-- IMPORTANT: the explicit COMMIT below is required. Postgres forbids using a
-- just-added enum value inside the SAME transaction that added it ("unsafe use of
-- new value ... New enum values must be committed before they can be used") — PART 3's
-- DO block casts to template_key_enum, so ALTER TYPE must commit first. Most CLI tools
-- (psql -f, DBeaver/pgAdmin "execute script") run each top-level statement autocommitted
-- and this COMMIT is a no-op guard; but if your client wraps the whole file in one
-- transaction, run PART 1 (through this COMMIT) as a separate execution first, then run
-- the rest of the file.
-- ------------------------------------------------

ALTER TYPE template_key_enum ADD VALUE IF NOT EXISTS 'ENT_PARENTAL_CONSENT_EMAIL_SEEKER';
COMMIT;

-- ------------------------------------------------
-- PART 2: Insert master template row for PT_ENTRAINMENT
-- Template variables: course_name, full_course_name, reg_fullname
-- ------------------------------------------------

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 (
    'ENT_PARENTAL_CONSENT_EMAIL_SEEKER',
    'Entrainment Parental Consent Form — Email to Seeker',
    'email',
    '2d6f.2a60732b5ed84938.k1.fcf24061-5cc6-11f0-b019-5254001dc20d.197ef550760',  -- ONLINE-path id from legacy infini-hdb-be (see OPEN FLAG above re: offline id 53820260-5cc4-11f0 not yet wired in)
    '2d6f.2a60732b5ed84938.k1.88e0b121-96e5-11f1-b787-525400114fe6.19ff9f1cd2f',  -- sandbox counterpart of the online-path prod id above
    'parental_consent',
    'general',
    'PARENTAL_CONSENT_EMAIL_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_ENTRAINMENT' AND deleted_at IS NULL),
    '[
        {"keyName": "reg_fullname",     "sourceTable": "hdb_program_registration", "sourceColumn": "full_name",          "dataType": "string", "isNullable": false},
        {"keyName": "course_name",      "sourceTable": "computed",                 "sourceColumn": "getProgramField:name", "dataType": "string", "isNullable": false},
        {"keyName": "full_course_name", "sourceTable": "computed",                 "sourceColumn": "getProgramField:name", "dataType": "string", "isNullable": false}
    ]'::jsonb,
    99,
    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 3: Clone master template to PT_ENTRAINMENT programs and
-- expand merge fields into merge_info_answer_location_map.
-- Set p_program_id to a specific program ID to target one program,
-- or leave as NULL to clone to all existing PT_ENTRAINMENT programs.
-- is_enabled = false — admins enable per-program when they configure
-- parental consent for that programme.
-- ------------------------------------------------

DO $$
DECLARE
    p_program_id    BIGINT := NULL;  -- SET a specific program ID here, or leave NULL for all PT_ENTRAINMENT programs

    prog_rec        RECORD;
    master_rec      RECORD;
    merge_field     JSONB;
    new_template_id BIGINT;
    cloned_count    INT := 0;
    skipped_count   INT := 0;
BEGIN
    -- Fetch master template once
    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 = 'ENT_PARENTAL_CONSENT_EMAIL_SEEKER'
      AND is_active    = true
    LIMIT 1;

    IF master_rec IS NULL THEN
        RAISE EXCEPTION 'Master template ENT_PARENTAL_CONSENT_EMAIL_SEEKER not found — run PART 2 first';
    END IF;

    -- Loop over target PT_ENTRAINMENT 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_ENTRAINMENT'
          AND pt.deleted_at IS NULL
          AND p.deleted_at  IS NULL
          AND (p_program_id IS NULL OR p.id = p_program_id)
        ORDER BY p.id
    LOOP
        -- Skip if already cloned
        IF EXISTS (
            SELECT 1 FROM hdb_communication_templates h
            WHERE h.program_id              = prog_rec.program_id
              AND h.template_key::text      = 'ENT_PARENTAL_CONSENT_EMAIL_SEEKER'
              AND h.template_type           = master_rec.template_type
        ) THEN
            RAISE NOTICE '  ✓ Already exists for program % (%), skipping', prog_rec.program_id, prog_rec.program_name;
            skipped_count := skipped_count + 1;
            CONTINUE;
        END IF;

        -- Insert into hdb_communication_templates
        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;

        RAISE NOTICE '  ✓ Cloned to program % (%), new hdb template ID: %',
            prog_rec.program_id, prog_rec.program_name, new_template_id;

        -- Expand merge fields into merge_info_answer_location_map
        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;

            RAISE NOTICE '    → % merge fields inserted into merge_info_answer_location_map',
                jsonb_array_length(master_rec.merge_field_map);
        END IF;

        cloned_count := cloned_count + 1;
    END LOOP;

    RAISE NOTICE '======================================';
    RAISE NOTICE 'Done — cloned: %, skipped: %', cloned_count, skipped_count;
    RAISE NOTICE '======================================';
END $$;

-- ------------------------------------------------
-- Verification
-- ------------------------------------------------

-- Master template row
SELECT template_key, template_access_key, template_id, sandbox_template_id, is_active
FROM communication_templates_master
WHERE template_key = 'ENT_PARENTAL_CONSENT_EMAIL_SEEKER';

-- Cloned program-level rows
SELECT COUNT(*) AS cloned_program_count
FROM hdb_communication_templates
WHERE template_key::text = 'ENT_PARENTAL_CONSENT_EMAIL_SEEKER';

-- Merge fields expanded per program
SELECT hct.program_id, COUNT(mf.id) AS merge_field_count
FROM hdb_communication_templates hct
JOIN merge_info_answer_location_map mf ON mf.template_id = hct.id
WHERE hct.template_key::text = 'ENT_PARENTAL_CONSENT_EMAIL_SEEKER'
GROUP BY hct.program_id
ORDER BY hct.program_id;
