-- ===========================================================================
-- Migration: Hatchery
-- Date: 2026-07-29
--
-- Incubation batches ("sets") from egg-set to hatch, with candling events and a
-- hatchability result. Fully integrated:
--   * an "own" set draws its eggs from egg stock (an egg_usage row, reason
--     'hatching'), stored on egg_usage_id so cancelling the set restores them;
--   * a "bought" set records a supplier + per-egg cost, no stock draw;
--   * hatching can place the chicks as a new flock (flock_id links back).
-- Idempotent.
-- ===========================================================================

-- eggs leaving stock to go into the incubator are a new kind of egg usage
ALTER TABLE egg_usage
  MODIFY COLUMN reason ENUM('sold','donated','consumed','missing','hatching','other') NOT NULL;

CREATE TABLE IF NOT EXISTS hatch_sets (
  id                INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id           INT UNSIGNED  NOT NULL,
  ref_no            VARCHAR(40)   NOT NULL,          -- HS-0001, per farm
  source            ENUM('own','bought') NOT NULL DEFAULT 'own',
  breed             VARCHAR(64)   NOT NULL,
  store             VARCHAR(64)   NULL,              -- own: egg store drawn from
  machine           VARCHAR(80)   NULL,              -- incubator/setter
  eggs_set          INT UNSIGNED  NOT NULL DEFAULT 0,
  set_on            DATE          NOT NULL,
  incubation_days   INT UNSIGNED  NOT NULL DEFAULT 21,
  expected_hatch_on DATE          NULL,
  supplier          VARCHAR(120)  NULL,              -- bought: where the eggs came from
  egg_unit_cost     DECIMAL(10,2) NULL,              -- bought: cost per egg
  eggs_removed      INT UNSIGNED  NOT NULL DEFAULT 0, -- pulled at candling (infertile/dead)
  status            ENUM('incubating','hatched','cancelled') NOT NULL DEFAULT 'incubating',
  hatched_on        DATE          NULL,
  chicks_hatched    INT UNSIGNED  NULL,
  chicks_culled     INT UNSIGNED  NOT NULL DEFAULT 0, -- weak/deformed culled at hatch
  dead_in_shell     INT UNSIGNED  NULL,               -- fully-formed but didn't hatch
  flock_id          INT UNSIGNED  NULL,               -- flock created from the hatch
  egg_usage_id      INT UNSIGNED  NULL,               -- the stock draw (own source)
  notes             VARCHAR(500)  NULL,
  created_at        TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at        TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_hs_farm (farm_id),
  KEY idx_hs_status (farm_id, status),
  CONSTRAINT fk_hs_farm  FOREIGN KEY (farm_id)      REFERENCES farms(id)     ON DELETE CASCADE,
  CONSTRAINT fk_hs_flock FOREIGN KEY (flock_id)     REFERENCES flocks(id)    ON DELETE SET NULL,
  CONSTRAINT fk_hs_usage FOREIGN KEY (egg_usage_id) REFERENCES egg_usage(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS hatch_candlings (
  id           INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id      INT UNSIGNED NOT NULL,
  hatch_set_id INT UNSIGNED NOT NULL,
  candled_on   DATE         NOT NULL,
  stage        VARCHAR(40)  NULL,              -- e.g. "Day 7", "Day 14"
  eggs_checked INT UNSIGNED NULL,
  removed      INT UNSIGNED NOT NULL DEFAULT 0, -- infertile + dead pulled this candling
  note         VARCHAR(255) NULL,
  created_at   TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_hc_set (hatch_set_id),
  CONSTRAINT fk_hc_farm FOREIGN KEY (farm_id)      REFERENCES farms(id)      ON DELETE CASCADE,
  CONSTRAINT fk_hc_set  FOREIGN KEY (hatch_set_id) REFERENCES hatch_sets(id) ON DELETE CASCADE
) ENGINE=InnoDB;