-- Fix sat_opening_closing_map to allow multiple rows per audit (one per opening observation).
-- Required when the table was created with PRIMARY KEY (audit_id) only.
-- Run against the hse database (unified EHS schema).

-- Step 1: Drop foreign keys that depend on the current primary key
ALTER TABLE sat_opening_closing_map DROP FOREIGN KEY sat_audits_sat_opening_closing_map;
ALTER TABLE sat_opening_closing_map DROP FOREIGN KEY sat_submissions_sat_opening_closing_map_1;
ALTER TABLE sat_opening_closing_map DROP FOREIGN KEY sat_submissions_sat_opening_closing_map_2;

-- Step 2: Drop existing primary key (audit_id only)
ALTER TABLE sat_opening_closing_map DROP PRIMARY KEY;

-- Step 3: Ensure opening_sub_id is NOT NULL for composite PK
ALTER TABLE sat_opening_closing_map MODIFY COLUMN opening_sub_id INT NOT NULL;
ALTER TABLE sat_opening_closing_map MODIFY COLUMN closing_sub_id INT NULL;

-- Step 4: Composite primary key — one row per (audit_id, opening_sub_id)
ALTER TABLE sat_opening_closing_map ADD PRIMARY KEY (audit_id, opening_sub_id);

-- Step 5: Restore foreign keys
ALTER TABLE sat_opening_closing_map
  ADD CONSTRAINT sat_audits_sat_opening_closing_map
    FOREIGN KEY (audit_id) REFERENCES sat_audits (audit_id);
ALTER TABLE sat_opening_closing_map
  ADD CONSTRAINT sat_submissions_sat_opening_closing_map_1
    FOREIGN KEY (opening_sub_id) REFERENCES sat_submissions (submission_id);
ALTER TABLE sat_opening_closing_map
  ADD CONSTRAINT sat_submissions_sat_opening_closing_map_2
    FOREIGN KEY (closing_sub_id) REFERENCES sat_submissions (submission_id);

-- Step 6: Backfill a map row for every opening-form submission missing one
INSERT INTO sat_opening_closing_map (audit_id, opening_sub_id, closing_sub_id)
SELECT s.audit_id, s.submission_id, NULL
FROM sat_submissions s
INNER JOIN sat_form_details f ON s.form_id = f.form_id
WHERE f.form_type_id = 1
  AND NOT EXISTS (
    SELECT 1 FROM sat_opening_closing_map ocm
    WHERE ocm.audit_id = s.audit_id AND ocm.opening_sub_id = s.submission_id
  );
