-- Add the missing UNIQUE(farm_id, ref_no) keys to delivery_notes and hatch_sets.
-- Their reference numbers are meant to be unique per farm, but no constraint
-- enforced it, so the old count-based numbering could silently mint duplicate
-- DN-#### / HS-#### after a delete. The app now numbers from the highest
-- existing value with retry, and these keys make the guarantee real.
--
-- Safe and idempotent: any pre-existing duplicates are made unique by appending
-- their row id to the older copies (no document is deleted), then the key is
-- added only if it isn't already present.

-- ---- delivery_notes ---------------------------------------------------------
UPDATE delivery_notes d
JOIN delivery_notes keep
  ON keep.farm_id = d.farm_id AND keep.ref_no = d.ref_no AND keep.id < d.id
SET d.ref_no = CONCAT(d.ref_no, '-', d.id);

SET @has := (SELECT COUNT(*) FROM information_schema.statistics
             WHERE table_schema = DATABASE() AND table_name = 'delivery_notes'
               AND index_name = 'uq_dn_ref');
SET @sql := IF(@has = 0,
  'ALTER TABLE delivery_notes ADD UNIQUE KEY uq_dn_ref (farm_id, ref_no)',
  'SELECT ''uq_dn_ref already present'' AS note');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ---- hatch_sets -------------------------------------------------------------
UPDATE hatch_sets h
JOIN hatch_sets keep
  ON keep.farm_id = h.farm_id AND keep.ref_no = h.ref_no AND keep.id < h.id
SET h.ref_no = CONCAT(h.ref_no, '-', h.id);

SET @has2 := (SELECT COUNT(*) FROM information_schema.statistics
              WHERE table_schema = DATABASE() AND table_name = 'hatch_sets'
                AND index_name = 'uq_hs_ref');
SET @sql2 := IF(@has2 = 0,
  'ALTER TABLE hatch_sets ADD UNIQUE KEY uq_hs_ref (farm_id, ref_no)',
  'SELECT ''uq_hs_ref already present'' AS note');
PREPARE s2 FROM @sql2; EXECUTE s2; DEALLOCATE PREPARE s2;