breakpilot-compliance/ai-compliance-sdk/migrations/030_iace_mitigation_unique.sql

-- Migration 030: De-duplicate iace_mitigations + add UNIQUE(hazard_id, name)
-- ==========================================================================
-- The mitigation init-handler used to be non-idempotent: a second click on
-- "Neu initialisieren in Grenzen" inserted every engine-suggested mitigation
-- a second time. The Bremsscheibe benchmark accumulated 5 such duplicate
-- (hazard_id, name) pairs (HMI-Usability, Eindeutiges-Feedback,
-- Betriebsarten-Anzeige, Sicher begrenzter Bewegungsbereich, …).
--
-- Frontend mitigates the symptom with a per-hazard dedupe in groupByTitle,
-- but the DB still carries the redundant rows: confusing for SQL audits,
-- and the cleanup work was deferred only because the expert had not yet
-- decided which copy to keep.
--
-- This migration:
--   1. Picks the winning row per (hazard_id, name) using a stable rank:
--        is_relevant   DESC   (expert decision survives)
--        status        DESC   (verified > implemented > planned)
--        created_at    DESC   (newest wins when nothing else differs)
--      and deletes the rest.
--   2. Adds a UNIQUE constraint so future engine init runs cannot
--      re-create the duplicates.
-- ==========================================================================

WITH ranked AS (
    SELECT id,
           ROW_NUMBER() OVER (
               PARTITION BY hazard_id, name
               ORDER BY is_relevant DESC,
                        CASE status
                            WHEN 'verified'    THEN 2
                            WHEN 'implemented' THEN 1
                            ELSE 0
                        END DESC,
                        created_at DESC
           ) AS rn
    FROM iace_mitigations
)
DELETE FROM iace_mitigations
 WHERE id IN (SELECT id FROM ranked WHERE rn > 1);

ALTER TABLE iace_mitigations
    DROP CONSTRAINT IF EXISTS iace_mitigations_hazard_name_uniq;

ALTER TABLE iace_mitigations
    ADD CONSTRAINT iace_mitigations_hazard_name_uniq
        UNIQUE (hazard_id, name);