-- ============================================================================
-- Egg stores — named locations/containers the pooled egg stock is split across
-- (e.g. "Cold Room", "Shop Front"). Store names are a managed list ('eggStores',
-- same mechanism as houses/breeds/etc. — see ListsController::CASCADE), not a
-- new table, so renaming a store cascades everywhere automatically.
--
-- Current stock per store is never cached — computed on read the same way the
-- farm-wide total already is:
--   store qty = SUM(egg_collections.total WHERE store = X)
--             - SUM(egg_usage.qty        WHERE store = X)
--             + SUM(egg_store_transfers.qty WHERE to_store   = X)
--             - SUM(egg_store_transfers.qty WHERE from_store = X)
-- which always nets back to the existing farm-wide total, since every transfer
-- credits one store and debits another by the same amount.
--
-- `store` is nullable so this imports cleanly onto existing data with zero
-- downtime; the backfill below assigns every pre-existing row to a single
-- "Main Store" so nothing goes untracked once the feature ships, and every
-- new write going forward always gets a real store from the app.
-- ============================================================================

ALTER TABLE egg_collections
  ADD COLUMN store VARCHAR(64) NULL AFTER collected_on,
  ADD KEY idx_egg_farm_store (farm_id, store);

ALTER TABLE egg_usage
  ADD COLUMN store VARCHAR(64) NULL AFTER used_on,
  ADD KEY idx_eggusage_farm_store (farm_id, store);

CREATE TABLE IF NOT EXISTS egg_store_transfers (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id       INT UNSIGNED  NOT NULL,
  from_store    VARCHAR(64)   NOT NULL,
  to_store      VARCHAR(64)   NOT NULL,
  qty           INT UNSIGNED  NOT NULL,
  note          VARCHAR(255)  NULL,
  moved_on      DATE          NOT NULL,
  created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_esxfer_farm (farm_id, moved_on),
  CONSTRAINT fk_esxfer_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- backfill: give every farm a default store and attribute all of its existing
-- (store-less) history to it, so totals stay continuous the moment this ships
INSERT INTO managed_lists (list_key, value, sort_order)
SELECT 'eggStores', 'Main Store', 0
WHERE NOT EXISTS (SELECT 1 FROM managed_lists WHERE list_key = 'eggStores' AND value = 'Main Store');

UPDATE egg_collections SET store = 'Main Store' WHERE store IS NULL;
UPDATE egg_usage       SET store = 'Main Store' WHERE store IS NULL;