-- Fix opening_closing_map to allow multiple rows per audit (one per opening observation).
-- Current issue: table has audit_id as sole PRIMARY KEY, so only one row per audit is allowed.
-- Solution: Use composite PRIMARY KEY (audit_id, opening_sub_id) and allow closing_sub_id NULL.
--
-- Run this against your database (e.g. sat). If using a different schema, replace opening_closing_map
-- with your_schema.opening_closing_map or run in the correct database context.

-- Step 1: Drop existing primary key
ALTER TABLE opening_closing_map DROP PRIMARY KEY;

-- Step 2: Allow closing_sub_id to be NULL (for opening-only rows at audit creation)
ALTER TABLE opening_closing_map MODIFY COLUMN closing_sub_id INT NULL;

-- Step 3: Add composite primary key so one row per (audit_id, opening_sub_id)
ALTER TABLE opening_closing_map ADD PRIMARY KEY (audit_id, opening_sub_id);
