-- =====================================================
-- Update FS_BASICDETAILS field ORDER for TAT (PT_TAT) only
--   1) template_question.display_order   (TAT templates)
--   2) hdb_program_question.display_order (TAT program questions)
-- Order only. Idempotent.
-- =====================================================
-- New basic-details sequence (changed fields in bold):
--   mobileNumber -> email -> dob -> rmContact (Contact person at infinitheism)
-- i.e. Email address now appears immediately after Mobile number,
-- before Date of birth and Contact person at infinitheism.
--
-- Full FS_BASICDETAILS display_order applied:
--   registrationForWhom=0, name=1, gender=2, countryName=3, city=4,
--   otherCityName=5, mobileNumber=6, email=7, dob=8, rmContact=9,
--   otherInfinitheismContact=10, profileUrl=11
-- (terms is appended after these and is left untouched.)
-- =====================================================

-- 1) TAT templates
UPDATE template_question tq
SET display_order = desired.ord,
    updated_at    = NOW(),
    updated_by    = -2
FROM (VALUES
    ('registrationForWhom',      0),
    ('name',                     1),
    ('gender',                   2),
    ('countryName',              3),
    ('city',                     4),
    ('otherCityName',            5),
    ('mobileNumber',             6),
    ('email',                    7),
    ('dob',                      8),
    ('rmContact',                9),
    ('otherInfinitheismContact', 10),
    ('profileUrl',               11)
) AS desired(bk, ord)
WHERE tq.binding_key = desired.bk
  AND tq.deleted_at IS NULL
  AND tq.template_form_section_id IN (
        SELECT tfs.id
        FROM template_form_section tfs
        JOIN program_template ptpl ON ptpl.id = tfs.program_template_id
        JOIN program_type_v1   pt  ON pt.id   = ptpl.program_type_id
        WHERE pt.key = 'PT_TAT' AND pt.deleted_at IS NULL
          AND ptpl.deleted_at IS NULL AND tfs.deleted_at IS NULL
          AND tfs.section_key = 'FS_BASICDETAILS'
  );

-- 2) TAT program questions
UPDATE hdb_program_question hpq
SET display_order = desired.ord,
    updated_at    = NOW(),
    updated_by    = -2
FROM (VALUES
    ('registrationForWhom',      0),
    ('name',                     1),
    ('gender',                   2),
    ('countryName',              3),
    ('city',                     4),
    ('otherCityName',            5),
    ('mobileNumber',             6),
    ('email',                    7),
    ('dob',                      8),
    ('rmContact',                9),
    ('otherInfinitheismContact', 10),
    ('profileUrl',               11)
) AS desired(bk, ord)
JOIN hdb_question     hq  ON hq.binding_key = desired.bk AND hq.deleted_at IS NULL
JOIN hdb_form_section hfs ON hfs.id = hq.form_section_id
                         AND hfs.key = 'FS_BASICDETAILS'
                         AND hfs.deleted_at IS NULL
WHERE hpq.question_id = hq.id
  AND hpq.deleted_at IS NULL
  AND hpq.program_id IN (
        SELECT p.id
        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
  );
