-- ============================================================================
-- Managed lists (houses, breeds, egg stores, ...) become farm-scoped.
--
-- Until now `managed_lists` had no farm_id — every farm on the install shared
-- one global dropdown catalog. That was harmless while this install only
-- ever had one real farm, but it's a genuine cross-tenant bug the moment a
-- second farm exists: ListsController::rename()'s cascade updates real
-- records (flocks, daily_entries, egg_collections, employees, ...) by matching
-- the old value with NO farm_id filter, since the list itself carries none —
-- so Farm A renaming its own house "H-1" silently rewrites Farm B's "H-1"
-- records too, if the name happens to collide.
--
-- Fix: give every farm its own independent copy of whatever list values
-- already existed (so nothing disappears from anyone's dropdowns), then scope
-- everything — reads, writes, and the rename cascade — to the caller's own
-- farm_id from now on.
--
-- Every step below checks the table's current state first and skips itself
-- if that step is already done — safe to run start-to-finish no matter where
-- a previous, interrupted attempt left off (MySQL's DDL statements — ALTER
-- TABLE, CREATE/DROP TEMPORARY TABLE — each auto-commit individually and
-- can't be rolled back as a group, so a script that dies partway through
-- leaves real, permanent partial progress behind). The old (list_key, value)
-- unique key is dropped BEFORE the backfill, not after — the backfill
-- deliberately inserts the same (list_key, value) pair once per farm, which
-- the old key (with no farm_id in it) would otherwise reject as a duplicate
-- the moment there's more than one farm.
-- ============================================================================

-- 1) add farm_id, if it isn't already there
SET @has_col = (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'managed_lists' AND COLUMN_NAME = 'farm_id');
SET @ddl = IF(@has_col = 0,
  'ALTER TABLE managed_lists ADD COLUMN farm_id INT UNSIGNED NULL AFTER id',
  'DO 0');
PREPARE s FROM @ddl; EXECUTE s; DEALLOCATE PREPARE s;

-- 2) drop the old (list_key, value)-only unique key FIRST, if it's still that
--    shape — must happen before the backfill insert below
SET @old_key = (SELECT COUNT(*) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'managed_lists'
    AND INDEX_NAME = 'uq_list_value' AND SEQ_IN_INDEX = 1 AND COLUMN_NAME = 'list_key');
SET @ddl = IF(@old_key > 0, 'ALTER TABLE managed_lists DROP KEY uq_list_value', 'DO 0');
PREPARE s FROM @ddl; EXECUTE s; DEALLOCATE PREPARE s;

-- 3) drop the old (list_key)-only index too, same reasoning
SET @old_idx = (SELECT COUNT(*) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'managed_lists'
    AND INDEX_NAME = 'idx_list_key' AND SEQ_IN_INDEX = 1 AND COLUMN_NAME = 'list_key');
SET @ddl = IF(@old_idx > 0, 'ALTER TABLE managed_lists DROP KEY idx_list_key', 'DO 0');
PREPARE s FROM @ddl; EXECUTE s; DEALLOCATE PREPARE s;

-- 4) give every farm its own copy of whatever rows are still unassigned
--    (naturally a no-op once nothing has farm_id IS NULL anymore)
CREATE TEMPORARY TABLE IF NOT EXISTS tmp_shared_lists AS
  SELECT list_key, value, sort_order FROM managed_lists WHERE farm_id IS NULL;

INSERT INTO managed_lists (farm_id, list_key, value, sort_order)
SELECT f.id, t.list_key, t.value, t.sort_order
FROM farms f
JOIN tmp_shared_lists t;

DROP TEMPORARY TABLE IF EXISTS tmp_shared_lists;

DELETE FROM managed_lists WHERE farm_id IS NULL;

-- 5) make farm_id required, if it isn't already
SET @is_nullable = (SELECT IS_NULLABLE FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'managed_lists' AND COLUMN_NAME = 'farm_id');
SET @ddl = IF(@is_nullable = 'YES',
  'ALTER TABLE managed_lists MODIFY COLUMN farm_id INT UNSIGNED NOT NULL',
  'DO 0');
PREPARE s FROM @ddl; EXECUTE s; DEALLOCATE PREPARE s;

-- 6) add the new farm-scoped unique key, if not already present
SET @has_new_key = (SELECT COUNT(*) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'managed_lists' AND INDEX_NAME = 'uq_list_value');
SET @ddl = IF(@has_new_key = 0,
  'ALTER TABLE managed_lists ADD UNIQUE KEY uq_list_value (farm_id, list_key, value)',
  'DO 0');
PREPARE s FROM @ddl; EXECUTE s; DEALLOCATE PREPARE s;

-- 7) add the new farm-scoped index, if not already present
SET @has_new_idx = (SELECT COUNT(*) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'managed_lists' AND INDEX_NAME = 'idx_list_key');
SET @ddl = IF(@has_new_idx = 0,
  'ALTER TABLE managed_lists ADD KEY idx_list_key (farm_id, list_key)',
  'DO 0');
PREPARE s FROM @ddl; EXECUTE s; DEALLOCATE PREPARE s;

-- 8) add the foreign key, if not already present
SET @has_fk = (SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'managed_lists' AND CONSTRAINT_NAME = 'fk_managed_lists_farm');
SET @ddl = IF(@has_fk = 0,
  'ALTER TABLE managed_lists ADD CONSTRAINT fk_managed_lists_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE',
  'DO 0');
PREPARE s FROM @ddl; EXECUTE s; DEALLOCATE PREPARE s;