-- =====================================================
-- PT_PAP — 'roommate' field audit (READ-ONLY, run this first)
-- 2026-08-08

SELECT hraqa.question_id, COUNT(*) AS answer_count
FROM hdb_registration_question_answer hraqa
WHERE hraqa.question_id IN (
    SELECT hq.id
    FROM hdb_question hq
    JOIN hdb_program_question hpq ON hpq.question_id = hq.id
    JOIN program_v1      p  ON p.id  = hpq.program_id
    JOIN program_type_v1 pt ON pt.id = p.program_type_id
    WHERE pt.key = 'PT_PAP'
      AND hq.binding_key = 'roommate'
)
GROUP BY hraqa.question_id;

-- 1) Real submitted answers for PT_PAP's 'roommate' question(s)
DELETE FROM hdb_registration_question_answer hraqa
WHERE hraqa.question_id IN (
    SELECT hq.id
    FROM hdb_question hq
    JOIN hdb_program_question hpq ON hpq.question_id = hq.id
    JOIN program_v1      p  ON p.id  = hpq.program_id
    JOIN program_type_v1 pt ON pt.id = p.program_type_id
    WHERE pt.key = 'PT_PAP'
      AND hq.binding_key = 'roommate'
);

-- 2) Do any live PT_PAP programs have a 'roommate' hdb_question?
SELECT hq.id AS hdb_question_id, hq.label, hq.deleted_at, p.id AS program_id, p.name AS program_name
FROM hdb_question hq
JOIN hdb_program_question hpq ON hpq.question_id = hq.id
JOIN program_v1      p  ON p.id  = hpq.program_id
JOIN program_type_v1 pt ON pt.id = p.program_type_id
WHERE pt.key = 'PT_PAP'
  AND hq.binding_key = 'roommate';

-- 2) PT_PAP's own 'roommate' hdb_question row(s)
DELETE FROM hdb_question hq
WHERE hq.binding_key = 'roommate'
  AND hq.id IN (
        SELECT hpq.question_id
        FROM hdb_program_question hpq
        JOIN program_v1      p  ON p.id  = hpq.program_id
        JOIN program_type_v1 pt ON pt.id = p.program_type_id
        WHERE pt.key = 'PT_PAP'
  );

  -- 1) Does PT_PAP's own program_template have a 'roommate' template_question row?
--    (Expected: 0 rows, since 05_tat_template.sql excludes it.)
SELECT tq.id, tq.binding_key, tq.deleted_at, ptpl.id AS program_template_id
FROM template_question tq
JOIN template_form_section tfs ON tfs.id = tq.template_form_section_id
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_PAP'
  AND tq.binding_key = 'roommate';

-- 3) PT_PAP's cloned template's 'roommate' row (expected no-op, see header)
DELETE FROM template_question tq
WHERE tq.binding_key = 'roommate'
  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_PAP'
  );
