-- ---------------------------------------------------------------------------
-- Migration: Feed Mixing module
-- ---------------------------------------------------------------------------
-- schema.sql already carries these for FRESH installs. Run this ONCE against
-- an EXISTING database (paste into phpMyAdmin's SQL tab, or
-- `mysql -u <user> -p <db> < sql/migration-2026-07-feed-mixing.sql`) to add
-- the mixed-feed inventory + mixing-batch history tables, and to tag every
-- existing daily_entry_feed row as drawn from raw feed_stock (the only kind
-- that existed before this migration) so it stays distinguishable from mix
-- draws recorded going forward. No existing rows are touched beyond that tag.
-- ---------------------------------------------------------------------------

ALTER TABLE daily_entry_feed
  ADD COLUMN source ENUM('raw','mix') NOT NULL DEFAULT 'raw' AFTER feed_type;

CREATE TABLE IF NOT EXISTS feed_mixes (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id       INT UNSIGNED  NOT NULL,
  mix_name      VARCHAR(64)   NOT NULL,
  qty_kg        DECIMAL(12,2) NOT NULL DEFAULT 0,
  reorder_kg    DECIMAL(12,2) NOT NULL DEFAULT 0,
  unit_cost     DECIMAL(12,2) NOT NULL DEFAULT 0,
  created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_feedmix (farm_id, mix_name),
  KEY idx_feedmix_farm (farm_id),
  CONSTRAINT fk_feedmix_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS feed_mix_batches (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id       INT UNSIGNED  NOT NULL,
  mix_name      VARCHAR(64)   NOT NULL,
  qty_kg        DECIMAL(12,2) NOT NULL DEFAULT 0,
  unit_cost     DECIMAL(12,2) NOT NULL DEFAULT 0,
  total_cost    DECIMAL(14,2) NOT NULL DEFAULT 0,
  note          VARCHAR(255)  NULL,
  mixed_on      DATE          NOT NULL,
  created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_feedmixbatch_farm (farm_id, mixed_on),
  CONSTRAINT fk_feedmixbatch_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS feed_mix_batch_components (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  batch_id      INT UNSIGNED  NOT NULL,
  feed_type     VARCHAR(64)   NOT NULL,
  qty_kg        DECIMAL(10,2) NOT NULL DEFAULT 0,
  unit_cost     DECIMAL(12,2) NOT NULL DEFAULT 0,
  line_cost     DECIMAL(14,2) NOT NULL DEFAULT 0,
  KEY idx_fmbc_batch (batch_id),
  CONSTRAINT fk_fmbc_batch FOREIGN KEY (batch_id) REFERENCES feed_mix_batches(id) ON DELETE CASCADE
) ENGINE=InnoDB;