-- Fix audit_logs: allow multiple log entries per audit+user (e.g. multiple OBSERVATION_ADDED)
-- Error: Duplicate entry '51-16' for key 'audit_logs.PRIMARY'
-- Cause: PRIMARY KEY (audit_id, user_id) allows only one row per audit+user.
-- Fix: Use id as primary key so each observation gets its own log entry.
--
-- Run this migration if you get the duplicate key error on form submission.

-- Case A: Table has composite PK (audit_id, user_id) and NO id column
-- Step 1: Drop composite primary key
ALTER TABLE audit_logs DROP PRIMARY KEY;

-- Step 2: Add id as new primary key
ALTER TABLE audit_logs ADD COLUMN id INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

-- If you get "Duplicate column name 'id'" on Step 2, your table already has id.
-- In that case, run only:
--   ALTER TABLE audit_logs DROP PRIMARY KEY;
--   ALTER TABLE audit_logs MODIFY id INT NOT NULL AUTO_INCREMENT PRIMARY KEY;
