-- Audit form type: section + 11 questions matching the AUDIT table layout.
-- Run against the `hse` database after ptw.sql / base seeds.
--
-- Layout (see PtwAuditSectionTable.jsx):
--   WHO | DATE
--   3 × (ACTION TO BE IMPLEMENTED | WHO | WHEN)
--
-- Idempotent: skips if an Audit form type and audit section already exist.

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
);

INSERT INTO ptw_form_type (form_type_name)
SELECT 'Audit'
WHERE NOT EXISTS (
  SELECT 1 FROM ptw_form_type WHERE LOWER(form_type_name) = 'audit'
);

SET @audit_form_type_id := (
  SELECT form_type_id FROM ptw_form_type WHERE LOWER(form_type_name) = 'audit' LIMIT 1
);

INSERT INTO ptw_section_details (title, created_by)
SELECT 'AUDIT (At least one per shift)', 1
WHERE NOT EXISTS (
  SELECT 1 FROM ptw_section_details
  WHERE LOWER(title) LIKE '%audit%' AND LOWER(title) LIKE '%shift%'
);

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

-- Meta row
INSERT INTO ptw_questions (question, question_type_id, allows_media)
SELECT 'WHO', @text_type_id, 0
WHERE @audit_section_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(q.question) = 'who'
  );

INSERT INTO ptw_questions (question, question_type_id, allows_media)
SELECT 'DATE', @text_type_id, 0
WHERE @audit_section_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(q.question) = 'date'
  );

-- Action rows (3 × description / who / when)
INSERT INTO ptw_questions (question, question_type_id, allows_media)
SELECT label, @text_type_id, 0
FROM (
  SELECT 'Action 1 — to be implemented' AS label UNION ALL
  SELECT 'Action 1 — WHO' UNION ALL
  SELECT 'Action 1 — WHEN' UNION ALL
  SELECT 'Action 2 — to be implemented' UNION ALL
  SELECT 'Action 2 — WHO' UNION ALL
  SELECT 'Action 2 — WHEN' UNION ALL
  SELECT 'Action 3 — to be implemented' UNION ALL
  SELECT 'Action 3 — WHO' UNION ALL
  SELECT 'Action 3 — WHEN'
) AS rows
WHERE @audit_section_id IS NOT NULL
  AND @text_type_id IS NOT NULL
  AND NOT EXISTS (
    SELECT 1 FROM ptw_section_question_map WHERE section_id = @audit_section_id
  );

-- Map all audit questions to the section (ordered by 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(q.question) IN ('who', 'date')
    OR LOWER(q.question) LIKE 'action %'
  )
  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
  CASE LOWER(q.question)
    WHEN 'who' THEN 1
    WHEN 'date' THEN 2
    WHEN 'action 1 — to be implemented' THEN 3
    WHEN 'action 1 — who' THEN 4
    WHEN 'action 1 — when' THEN 5
    WHEN 'action 2 — to be implemented' THEN 6
    WHEN 'action 2 — who' THEN 7
    WHEN 'action 2 — when' THEN 8
    WHEN 'action 3 — to be implemented' THEN 9
    WHEN 'action 3 — who' THEN 10
    WHEN 'action 3 — when' THEN 11
    ELSE 99
  END,
  q.question_id;

-- Optional: sample Audit form linked to GENERAL job type (customize job_type_id as needed)
INSERT INTO ptw_form_details (title, description, form_type_id, job_type_id, created_by)
SELECT
  'Shift Audit Form',
  'At least one audit per shift',
  @audit_form_type_id,
  (SELECT job_type_id FROM ptw_job_type WHERE LOWER(job_type_name) = 'general' LIMIT 1),
  1
WHERE @audit_form_type_id IS NOT NULL
  AND NOT EXISTS (
    SELECT 1 FROM ptw_form_details
    WHERE form_type_id = @audit_form_type_id
      AND LOWER(title) LIKE '%audit%'
  );

SET @audit_form_id := (
  SELECT form_id FROM ptw_form_details
  WHERE form_type_id = @audit_form_type_id
  ORDER BY form_id DESC LIMIT 1
);

INSERT INTO ptw_form_section_map (form_id, section_id, section_in_form_order, skipable)
SELECT @audit_form_id, @audit_section_id, 1, 0
WHERE @audit_form_id IS NOT NULL
  AND @audit_section_id IS NOT NULL
  AND NOT EXISTS (
    SELECT 1 FROM ptw_form_section_map
    WHERE form_id = @audit_form_id AND section_id = @audit_section_id
  );

SELECT
  @audit_form_type_id AS audit_form_type_id,
  @audit_section_id AS audit_section_id,
  @audit_form_id AS audit_form_id;
