-- Fixes a "trigger won't stay off" bug on databases whose sms_triggers table was
-- restored WITHOUT its UNIQUE(farm_id, kind) key. Missing that key, saving a
-- trigger INSERTs a new row instead of updating the old one, leaving several
-- rows per (farm, kind); the app could then read a stale enabled=1 row and keep
-- sending an SMS the farm had switched off (e.g. the daily snapshot).
--
-- Safe and idempotent: collapses any duplicates to the most recent row, then
-- adds the unique key only if it isn't already present.

-- 1) Keep only the newest row per (farm_id, kind); drop the older duplicates.
DELETE t1 FROM sms_triggers t1
JOIN sms_triggers t2
  ON t1.farm_id = t2.farm_id AND t1.kind = t2.kind AND t1.id < t2.id;

-- 2) Add the unique key only when it's missing (so re-running this is harmless).
SET @has_key := (
  SELECT COUNT(*) FROM information_schema.statistics
  WHERE table_schema = DATABASE() AND table_name = 'sms_triggers'
    AND index_name = 'uq_sms_trigger_farm_kind'
);
SET @sql := IF(@has_key = 0,
  'ALTER TABLE sms_triggers ADD UNIQUE KEY uq_sms_trigger_farm_kind (farm_id, kind)',
  'SELECT ''unique key already present'' AS note');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;