-- Enforce uniqueness of serial_hex (ideation I-107): no two certs
-- issued by orca may share the same serial. Implemented as a UNIQUE
-- INDEX so existing 0004_certs.sql need not be re-run on deployed
-- databases. v0.7 P01 (REQ-053 companion).
--
-- P1-001 fix (final review): before creating the UNIQUE index, dedup
-- any existing rows that share a serial_hex. Keep the newest row
-- (MAX(created_at)) per serial_hex and delete older duplicates. This
-- makes the migration backward-compatible with v0.6 deployments that
-- may have accumulated duplicate serials before the constraint existed.
DELETE FROM certs WHERE id NOT IN (
  SELECT id FROM (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY serial_hex ORDER BY created_at DESC) AS rn
    FROM certs
  ) WHERE rn = 1
);
CREATE UNIQUE INDEX IF NOT EXISTS idx_certs_serial_unique ON certs(serial_hex);