-- Maps each opening observation (submission) to its corresponding closing observation per audit.
-- Ensures for every opening observation there is a closing observation.
CREATE TABLE IF NOT EXISTS opening_closing_map (
    audit_id INT NOT NULL,
    opening_sub_id INT NOT NULL,
    closing_sub_id INT NOT NULL,
    PRIMARY KEY (audit_id, opening_sub_id),
    UNIQUE KEY uk_closing (audit_id, closing_sub_id),
    FOREIGN KEY (audit_id) REFERENCES audits(audit_id) ON DELETE CASCADE,
    FOREIGN KEY (opening_sub_id) REFERENCES submissions(submission_id) ON DELETE CASCADE,
    FOREIGN KEY (closing_sub_id) REFERENCES submissions(submission_id) ON DELETE CASCADE
);
