-- Backfill opening_closing_map: add a row for every opening-form submission that doesn't have one.
-- Run after allow_null_closing_sub_id_in_opening_closing_map.sql.
-- Opening forms are submissions where form_details.form_type_id = 1.
INSERT IGNORE INTO opening_closing_map (audit_id, opening_sub_id, closing_sub_id)
SELECT s.audit_id, s.submission_id, NULL
FROM submissions s
INNER JOIN form_details f ON s.form_id = f.form_id
WHERE f.form_type_id = 1
  AND NOT EXISTS (
    SELECT 1 FROM opening_closing_map ocm
    WHERE ocm.audit_id = s.audit_id AND ocm.opening_sub_id = s.submission_id
  );
