-- =====================================================
-- PT_ENTRAINMENT — FS_BASICDETAILS: set DOB question age range to 3-100 years
-- 2026-08-11
-- DRAFT ONLY — not yet reviewed/approved.
-- No schema changes.
-- =====================================================
--update
-- Mirrors database/migrations/2026-08-07-02-pt-pap-program-template.sql's STEP 4c pattern
-- (config.pastValidation.minValue/maxValue on the 'dob' question) — PAP uses 18-100,
-- PT_ENTRAINMENT uses 3-100 per explicit instruction.
--
-- Two separate tables need this, covering two different points in time:
--   STEP 1: template_question — patches the live row already created by 03template-refresh.sql
--           (already executed). Only affects programs created in the FUTURE from this template.
--   STEP 2: hdb_question — patches the row actual LIVE PT_ENTRAINMENT registrations validate
--           answers against right now (src/common/services/registration-answer-validation.service.ts
--           reads Question.config, entity 'hdb_question', not template_question).
-- =====================================================

-- =====================================================
-- STEP 1: template_question (idempotent — plain jsonb_set on the current live template)
-- =====================================================

UPDATE template_question tq
SET config = jsonb_set(
                jsonb_set(
                    tq.config,
                    '{pastValidation,maxValue}',
                    '100'::jsonb
                ),
                '{pastValidation,minValue}',
                '3'::jsonb
             ),
    updated_at = NOW(),
    updated_by = -2
WHERE tq.deleted_at   IS NULL
  AND tq.binding_key   = 'dob'
  AND tq.template_form_section_id = (
        SELECT tfs.id
        FROM template_form_section tfs
        WHERE tfs.section_key      = 'FS_BASICDETAILS'
          AND tfs.deleted_at      IS NULL
          AND tfs.program_template_id = (
                SELECT pt.id
                FROM program_template pt
                INNER JOIN program_type_v1 ptv ON ptv.id = pt.program_type_id
                WHERE ptv.key        = 'PT_ENTRAINMENT'
                  AND ptv.deleted_at IS NULL
                  AND pt.deleted_at  IS NULL
                  AND pt.is_active   = TRUE
                ORDER BY pt.version DESC
                LIMIT 1
              )
      );

-- Verification (commented out — uncomment to check after running):
-- SELECT tq.id, tq.binding_key, tq.config
-- FROM template_question tq
-- INNER JOIN template_form_section tfs ON tfs.id = tq.template_form_section_id
-- WHERE tfs.section_key = 'FS_BASICDETAILS'
--   AND tq.binding_key  = 'dob'
--   AND tq.deleted_at  IS NULL;

-- =====================================================
-- STEP 2: hdb_question
--
-- ⚠️ hdb_question is a shared question bank: hdb_form_section.key is UNIQUE, and a single
-- hdb_question row can in principle be linked (via hdb_program_question) to programs of
-- OTHER program types too. Updating a row's config here would silently change validation for
-- every program that shares it — NOT just Entrainment. This UPDATE therefore only ever targets
-- hdb_question rows for binding_key='dob' that are EXCLUSIVELY linked to PT_ENTRAINMENT
-- programs (see the NOT EXISTS guard below). If the STEP 2a verification SELECT shows a 'dob'
-- row also linked to a non-Entrainment program, STOP — that row needs its own program-scoped
-- clone before this can safely apply; do not run STEP 2b against it as-is.
-- =====================================================

-- STEP 2a (run first, manually inspect): which hdb_question 'dob' rows does PT_ENTRAINMENT use,
-- and are any of them shared with other program types?
-- SELECT
--   hq.id                       AS hdb_question_id,
--   hq.binding_key,
--   hq.config,
--   ARRAY_AGG(DISTINCT pt.key)  AS program_types_referencing_this_question
-- FROM hdb_question hq
-- INNER JOIN hdb_program_question hpq ON hpq.question_id = hq.id AND hpq.deleted_at IS NULL
-- INNER JOIN program_v1 p             ON p.id = hpq.program_id
-- INNER JOIN program_type_v1 pt       ON pt.id = p.program_type_id
-- WHERE hq.binding_key = 'dob'
--   AND hq.deleted_at IS NULL
--   AND EXISTS (
--         SELECT 1 FROM hdb_program_question hpq2
--         INNER JOIN program_v1 p2 ON p2.id = hpq2.program_id
--         INNER JOIN program_type_v1 pt2 ON pt2.id = p2.program_type_id
--         WHERE hpq2.question_id = hq.id
--           AND hpq2.deleted_at IS NULL
--           AND pt2.key = 'PT_ENTRAINMENT'
--       )
-- GROUP BY hq.id, hq.binding_key, hq.config
-- ORDER BY hq.id;

-- STEP 2b: the actual update — only rows linked exclusively to PT_ENTRAINMENT programs.
UPDATE hdb_question hq
SET config = jsonb_set(
                jsonb_set(
                    COALESCE(hq.config, '{}'::jsonb),
                    '{pastValidation,maxValue}',
                    '100'::jsonb
                ),
                '{pastValidation,minValue}',
                '3'::jsonb
             ),
    updated_at = NOW(),
    updated_by = -2
WHERE hq.deleted_at   IS NULL
  AND hq.binding_key   = 'dob'
  AND EXISTS (
        SELECT 1
        FROM hdb_program_question hpq
        INNER JOIN program_v1 p       ON p.id = hpq.program_id
        INNER JOIN program_type_v1 pt ON pt.id = p.program_type_id
        WHERE hpq.question_id  = hq.id
          AND hpq.deleted_at  IS NULL
          AND pt.key           = 'PT_ENTRAINMENT'
      )
  AND NOT EXISTS (
        SELECT 1
        FROM hdb_program_question hpq
        INNER JOIN program_v1 p       ON p.id = hpq.program_id
        INNER JOIN program_type_v1 pt ON pt.id = p.program_type_id
        WHERE hpq.question_id  = hq.id
          AND hpq.deleted_at  IS NULL
          AND pt.key          != 'PT_ENTRAINMENT'
      );

-- Verification (commented out — uncomment to check after running):
-- SELECT id, binding_key, config
-- FROM hdb_question
-- WHERE binding_key = 'dob'
--   AND deleted_at  IS NULL;
