-- Add missing audit header DATE and row-3 WHO when section uses repeated WHO:/WHEN: labels.
-- Idempotent for section 11 (AUDIT) — adjust @audit_section_id if your section_id differs.

SET @text_type_id := (
  SELECT question_type_id FROM ptw_question_type
  WHERE LOWER(question_type) LIKE '%text%' OR question_type_id = 1
  ORDER BY question_type_id LIMIT 1
);

SET @audit_section_id := (
  SELECT section_id FROM ptw_section_details
  WHERE LOWER(title) LIKE '%audit%'
  ORDER BY section_id DESC LIMIT 1
);

INSERT INTO ptw_questions (question, question_type_id, allows_media)
SELECT 'DATE', @text_type_id, 0
WHERE @audit_section_id IS NOT NULL
  AND @text_type_id IS NOT NULL
  AND NOT EXISTS (
    SELECT 1 FROM ptw_section_question_map m
    JOIN ptw_questions q ON q.question_id = m.question_id
    WHERE m.section_id = @audit_section_id AND LOWER(TRIM(q.question)) IN ('date', 'audit date')
  );

INSERT INTO ptw_questions (question, question_type_id, allows_media)
SELECT 'WHO :', @text_type_id, 0
WHERE @audit_section_id IS NOT NULL
  AND @text_type_id IS NOT NULL
  AND (
    SELECT COUNT(*)
    FROM ptw_section_question_map m
    JOIN ptw_questions q ON q.question_id = m.question_id
    WHERE m.section_id = @audit_section_id
      AND LOWER(REPLACE(REPLACE(TRIM(q.question), '—', '-'), '–', '-')) LIKE 'who%'
  ) < 4;

INSERT INTO ptw_section_question_map (section_id, question_id)
SELECT @audit_section_id, q.question_id
FROM ptw_questions q
WHERE @audit_section_id IS NOT NULL
  AND LOWER(TRIM(q.question)) IN ('date', 'audit date')
  AND NOT EXISTS (
    SELECT 1 FROM ptw_section_question_map m
    WHERE m.section_id = @audit_section_id AND m.question_id = q.question_id
  );

INSERT INTO ptw_section_question_map (section_id, question_id)
SELECT @audit_section_id, q.question_id
FROM ptw_questions q
WHERE @audit_section_id IS NOT NULL
  AND LOWER(TRIM(q.question)) LIKE 'who%'
  AND NOT EXISTS (
    SELECT 1 FROM ptw_section_question_map m
    WHERE m.section_id = @audit_section_id AND m.question_id = q.question_id
  )
ORDER BY q.question_id;

SELECT
  @audit_section_id AS audit_section_id,
  (
    SELECT COUNT(*)
    FROM ptw_section_question_map m
    JOIN ptw_questions q ON q.question_id = m.question_id
    WHERE m.section_id = @audit_section_id
  ) AS mapped_question_count;
