-- ---------------------------------------------------------------------------
-- Migration: adds Egg Bookings — a customer deposit taken in advance (e.g.
-- during a stock shortage), tracked as its own record instead of a normal
-- Sale so it never touches egg stock or counts as Revenue until the eggs are
-- actually handed over. See schema.sql's comment on the egg_bookings table.
-- ---------------------------------------------------------------------------
-- schema.sql already carries this 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-egg-bookings.sql`).
-- CREATE TABLE IF NOT EXISTS is naturally idempotent — safe to re-run.
-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS egg_bookings (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id       INT UNSIGNED  NOT NULL,
  ref_no        VARCHAR(40)   NOT NULL,
  customer      VARCHAR(160)  NOT NULL,
  customer_phone   VARCHAR(40)  NULL,
  customer_address VARCHAR(255) NULL,
  item          VARCHAR(120)  NOT NULL,
  qty           INT UNSIGNED  NOT NULL DEFAULT 0,
  unit_price    DECIMAL(14,2) NOT NULL DEFAULT 0,
  amount        DECIMAL(14,2) NOT NULL DEFAULT 0,
  amount_paid   DECIMAL(14,2) NOT NULL DEFAULT 0,
  grade         ENUM('gradeTwin','gradeA','gradeB','cracked','dirty','rejected','mixed') NULL,
  store         VARCHAR(64)   NULL,
  status        ENUM('pending','fulfilled','cancelled') NOT NULL DEFAULT 'pending',
  booked_on     DATE          NOT NULL,
  notes         VARCHAR(255)  NULL,
  sale_id       INT UNSIGNED  NULL,
  created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_booking (farm_id, ref_no),
  KEY idx_bookings_farm (farm_id, status),
  CONSTRAINT fk_bookings_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE,
  CONSTRAINT fk_bookings_sale FOREIGN KEY (sale_id) REFERENCES sales(id) ON DELETE SET NULL
) ENGINE=InnoDB;