-- Make flocks.code unique PER FARM instead of globally, so each farm (head or
-- branch) runs its own FL-#### sequence and two farms can both have "FL-2401".
-- Existing codes are globally unique today, so switching to (farm_id, code) is
-- strictly looser — no data can conflict, nothing to dedupe. Child records
-- (daily_entries, egg_collections, health_events, vaccinations) reference
-- flock_code only ever together with farm_id, so per-farm uniqueness is safe.
--
-- Idempotent: drops whatever uq_flock_code currently is, then adds the per-farm
-- form. The farm_id foreign key is backed by idx_flock_farm, so dropping the
-- old (code) index doesn't disturb it.

SET @has := (SELECT COUNT(*) FROM information_schema.statistics
             WHERE table_schema = DATABASE() AND table_name = 'flocks' AND index_name = 'uq_flock_code');
SET @sql := IF(@has > 0, 'ALTER TABLE flocks DROP INDEX uq_flock_code', 'SELECT ''no uq_flock_code'' AS note');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

ALTER TABLE flocks ADD UNIQUE KEY uq_flock_code (farm_id, code);