-- ============================================================================
-- PFMIS — Poultry Farm Management Information System
-- Database schema for MariaDB 10.4+ / MySQL 8.0+
-- Charset utf8mb4 throughout; InnoDB for FK support and transactions.
--
-- This file creates TABLES only — it does NOT create or switch databases, so
-- it imports cleanly into an already-created database (which is the only thing
-- shared hosting like cPanel lets you do — the database is made in cPanel's
-- MySQL wizard, with an account prefix such as `youracct_pfmis`).
--   • cPanel / phpMyAdmin: select your database on the left, then Import.
--   • Local dev:  mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS pfmis
--                   CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci"
--                 mysql -u root -p pfmis < sql/schema.sql
-- ============================================================================

-- ---------------------------------------------------------------------------
-- Farms — top of the hierarchy. Every record ties back to a farm.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS farms (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  name          VARCHAR(120)  NOT NULL,
  -- a branch points at its head farm; NULL = a head or standalone farm. Each
  -- branch is still a full, independently-scoped farm with its own data.
  -- ON DELETE SET NULL: deleting a head farm turns its branches into standalone
  -- farms rather than orphaning them behind a dangling parent_id.
  parent_id     INT UNSIGNED  NULL,
  logo_path     VARCHAR(255)  NULL,
  -- suspended farms are blocked from logging in / making requests (e.g. for
  -- non-payment); a platform admin can still enter them for support.
  status        ENUM('active','suspended') NOT NULL DEFAULT 'active',
  currency      CHAR(3)       NOT NULL DEFAULT 'UGX',
  -- comma-separated subset of layer/broiler/breeder — a farm can run more than
  -- one operation type at once, so this isn't a single-value ENUM
  kind          VARCHAR(40)   NOT NULL DEFAULT 'layer',
  -- JSON array of app modules this farm may use; overrides its plan's default.
  -- NULL = inherit the plan (or all). Effective set enforced in Auth.
  modules       JSON          NULL,
  live_birds    INT UNSIGNED  NOT NULL DEFAULT 0,
  capacity      INT UNSIGNED  NOT NULL DEFAULT 0,
  locale        VARCHAR(40)   NOT NULL DEFAULT 'English (UG)',
  timezone      VARCHAR(60)   NOT NULL DEFAULT 'Africa/Kampala',
  -- exact farm coordinates, entered by hand in Settings — powers the
  -- Dashboard's live temperature/humidity (fetched client-side straight from
  -- Open-Meteo's free API, no key needed, so nothing is stored server-side
  -- beyond the coordinates themselves). NULL until a farm sets them.
  latitude      DECIMAL(9,6)  NULL,
  longitude     DECIMAL(9,6)  NULL,
  phone         VARCHAR(255)  NULL, -- one or more numbers, comma-separated, where SMS alerts (mortality, feed reorder, vaccination due) are sent
  notify_sms       TINYINT(1) NOT NULL DEFAULT 0,
  notify_email     TINYINT(1) NOT NULL DEFAULT 0,
  notify_whatsapp  TINYINT(1) NOT NULL DEFAULT 0,
  created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_farms_parent (parent_id),
  CONSTRAINT fk_farms_parent FOREIGN KEY (parent_id) REFERENCES farms(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- Flocks — a batch of birds placed in a house.
-- The UI uses a string business code (e.g. FL-2401) as the flock identifier,
-- so we keep `code` UNIQUE and reference it from child records for fidelity
-- with the front end, while still having a numeric PK for joins.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS flocks (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id       INT UNSIGNED  NOT NULL,
  code          VARCHAR(32)   NOT NULL,
  house         VARCHAR(32)   NOT NULL,
  breed         VARCHAR(64)   NOT NULL,
  status        ENUM('growing','laying','molting','spent') NOT NULL DEFAULT 'growing',
  age_days      INT UNSIGNED  NOT NULL DEFAULT 0,
  qty           INT UNSIGNED  NOT NULL DEFAULT 0,
  placed_on     DATE          NULL,
  source        VARCHAR(120)  NULL,
  lay_rate      DECIMAL(5,2)  NOT NULL DEFAULT 0,   -- %
  mortality_pct DECIMAL(5,3)  NOT NULL DEFAULT 0,   -- % daily
  fcr           DECIMAL(5,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_flock_code (code),
  KEY idx_flock_farm (farm_id),
  CONSTRAINT fk_flock_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- Daily entries — production numbers per house per day (one row per house/day).
-- Keyed by house rather than flock: houses are the stable, physical unit a
-- worker records against, while flocks come and go (placed, transferred,
-- split) — after a bird transfer, "whichever flock is in this house today"
-- can change without the house itself changing. flock_code is kept purely as
-- an informational reference to whichever flock occupied the house when the
-- entry was recorded. Feed columns are the aggregate; the per-component
-- breakdown lands in daily_entry_feed so we keep normalization AND the UI's
-- "breakdown" string.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS daily_entries (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id       INT UNSIGNED  NOT NULL,
  house         VARCHAR(32)   NOT NULL,
  flock_code    VARCHAR(32)   NULL,
  entry_date    DATE          NOT NULL,
  mortality     INT UNSIGNED  NOT NULL DEFAULT 0,
  culls         INT UNSIGNED  NOT NULL DEFAULT 0,
  water_l       INT UNSIGNED  NOT NULL DEFAULT 0,
  avg_weight_g  INT UNSIGNED  NOT NULL DEFAULT 0,
  -- no eggs column: egg_collections is the single source of truth for egg
  -- counts (with grade detail this table never had), not duplicated here
  feed_kg       DECIMAL(10,2) NOT NULL DEFAULT 0,
  feed_cost     DECIMAL(14,2) NOT NULL DEFAULT 0,
  loss_pct      DECIMAL(6,3)  NOT NULL DEFAULT 0,
  breakdown     VARCHAR(255)  NOT NULL DEFAULT '',
  created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_entry (farm_id, house, entry_date),  -- one entry per house per day
  KEY idx_entry_farm_date (farm_id, entry_date),
  CONSTRAINT fk_entry_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- per-component feed consumption for a daily entry. `feed_type` is a shared
-- free-text label: for `source`='raw' rows (historical, from before feed
-- mixing existed) it names a feed_stock type; for `source`='mix' rows
-- (current) it names a feed_mixes mix — birds are fed the mix, not the raw
-- ingredients directly, so Daily Entry now draws from feed_mixes, not
-- feed_stock (see DailyEntryController::feed()). The two are kept in the same
-- column/table for continuity with existing history, but every cascade that
-- touches this table (delete a feed type, delete a mix, rename either from
-- Configurations) filters by `source` so a mix and a raw type that happen to
-- share a literal name can never cross-contaminate each other's records.
CREATE TABLE IF NOT EXISTS daily_entry_feed (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  entry_id      INT UNSIGNED  NOT NULL,
  feed_type     VARCHAR(64)   NOT NULL,
  source        ENUM('raw','mix') NOT NULL DEFAULT 'raw',
  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_def_entry (entry_id),
  CONSTRAINT fk_def_entry FOREIGN KEY (entry_id) REFERENCES daily_entries(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- Feed stock — inventory of RAW feed types with unit cost. No longer drawn
-- down by Daily Entry directly — it's the input to Feed Mixing (see
-- feed_mixes below), which blends measured amounts of these into a stored,
-- costed mix that Daily Entry then draws from.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS feed_stock (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id       INT UNSIGNED  NOT NULL,
  feed_type     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,   -- UGX per kg
  -- most recent purchase's supplier/date, kept in sync by purchase() on every
  -- new delivery (same "blended/rolling" treatment as unit_cost above) and
  -- still directly correctable here, same as qty/cost/reorder — the full,
  -- immutable history of every individual delivery lives in feed_purchases
  supplier      VARCHAR(160)  NULL,
  purchased_on  DATE          NULL,
  created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_feed (farm_id, feed_type),
  KEY idx_feed_farm (farm_id),
  CONSTRAINT fk_feed_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- Feed purchases — one row per "Record purchase" event (feed_stock above only
-- holds the current running total per type, not individual purchase history).
-- Unioned into v_expenses_all below so a purchase shows up as a real expense
-- automatically, the same way a payroll run does. `payment_status` covers how
-- the delivery was actually settled — not every delivery is cash-in-hand: a
-- supplier may extend credit (an unpaid liability until settled) or the feed
-- may simply be donated (free, nothing ever owed). `invoice_no` is the
-- supplier's own invoice/receipt reference, if the farm has one to record.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS feed_purchases (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id       INT UNSIGNED  NOT NULL,
  feed_type     VARCHAR(64)   NOT NULL,
  qty_kg        DECIMAL(12,2) NOT NULL DEFAULT 0,
  unit_cost     DECIMAL(12,2) NOT NULL DEFAULT 0,
  line_cost     DECIMAL(14,2) NOT NULL DEFAULT 0,
  payment_status ENUM('paid','credit','donated') NOT NULL DEFAULT 'paid',
  -- when a 'credit' purchase was later settled (paid off). NULL = still owed.
  -- payment_status keeps its original value so the ledger still credits
  -- Accounts Payable at purchase; the separate settlement entry clears it.
  settled_on    DATE          NULL,
  invoice_no    VARCHAR(60)   NULL,
  supplier      VARCHAR(160)  NULL,
  purchased_on  DATE          NOT NULL,
  created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_feedpur_farm (farm_id, purchased_on),
  CONSTRAINT fk_feedpur_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- Feed mixes — inventory of MIXED feed, blended from measured amounts of raw
-- feed_stock components via a mixing batch (see feed_mix_batches below).
-- Daily Entry draws feed from here, not from feed_stock directly — birds are
-- fed the mix, not the raw ingredients. Mirrors feed_stock's own shape
-- (qty/reorder/cost) so the rest of the app (Daily Entry's draw-down, the
-- delete cascade) can reuse the exact same pattern.
-- ---------------------------------------------------------------------------
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,   -- blended UGX/kg, weighted across every batch ever mixed into this name
  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;

-- ---------------------------------------------------------------------------
-- Feed mix batches — one row per "Mix a batch" event (feed_mixes above only
-- holds the current running total per mix, not individual mixing history —
-- same relationship feed_purchases has to feed_stock). No cash changes hands
-- here (the raw feed was already expensed when it was bought — see
-- feed_purchases), so batches are never unioned into Expenses; this is purely
-- an internal stock movement + cost trace.
-- ---------------------------------------------------------------------------
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,   -- total output of this batch (sum of its raw components)
  unit_cost     DECIMAL(12,2) NOT NULL DEFAULT 0,   -- this batch's own blended cost/kg
  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;

-- per-component raw feed consumption for a single mixing batch — the recipe
-- actually used, traceable after the fact (same normalization pattern as
-- daily_entry_feed under daily_entries)
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;

-- ---------------------------------------------------------------------------
-- Egg collections — logged gradings; a running tally is derived via SUM().
-- `store` names which egg store the collected eggs went into (e.g. "Cold
-- Room") — a managed list ('eggStores'), nullable for the same "imports
-- cleanly onto pre-existing data" reason every other retrofitted column here
-- is nullable. See sql/migration-2026-07-egg-stores.sql for the full design.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS egg_collections (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id       INT UNSIGNED  NOT NULL,
  house         VARCHAR(32)   NOT NULL,
  flock_code    VARCHAR(32)   NOT NULL,
  collected_on  DATE          NOT NULL,
  store         VARCHAR(64)   NULL,
  total         INT UNSIGNED  NOT NULL DEFAULT 0,
  grade_twin    INT UNSIGNED  NOT NULL DEFAULT 0,   -- twin-yolk eggs, graded ahead of grade_a
  grade_a       INT UNSIGNED  NOT NULL DEFAULT 0,
  grade_b       INT UNSIGNED  NOT NULL DEFAULT 0,
  cracked       INT UNSIGNED  NOT NULL DEFAULT 0,
  dirty         INT UNSIGNED  NOT NULL DEFAULT 0,
  rejected      INT UNSIGNED  NOT NULL DEFAULT 0,
  avg_weight_g  DECIMAL(6,2)  NOT NULL DEFAULT 0,
  created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_egg_farm (farm_id, collected_on),
  KEY idx_egg_farm_store (farm_id, store),
  CONSTRAINT fk_egg_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- Health events
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS health_events (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id       INT UNSIGNED  NOT NULL,
  flock_code    VARCHAR(32)   NOT NULL,
  disease       VARCHAR(160)  NOT NULL,
  affected      INT UNSIGNED  NOT NULL DEFAULT 0,
  onset_date    DATE          NULL,
  vet           VARCHAR(120)  NULL,
  symptoms      VARCHAR(255)  NULL,
  status        ENUM('under treatment','monitoring','resolved') NOT NULL DEFAULT 'under treatment',
  created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_health_farm (farm_id),
  CONSTRAINT fk_health_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- Vaccinations
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS vaccinations (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id       INT UNSIGNED  NOT NULL,
  flock_code    VARCHAR(32)   NOT NULL,
  vaccine       VARCHAR(120)  NOT NULL,
  route         VARCHAR(60)   NULL,
  dose          VARCHAR(40)   NULL,
  scheduled_on  DATE          NULL,
  status        ENUM('scheduled','due','done') NOT NULL DEFAULT 'scheduled',
  -- open-ended recurrence (e.g. "ND/IB monthly during laying") — NULL means a
  -- one-time dose, same as every row before this existed. Marking a row with
  -- this set 'done' auto-creates the next occurrence this many days later
  -- (see VaccinationController::scheduleNext()), so an indefinite repeat never
  -- has to be re-added by hand each cycle.
  repeat_every_days INT UNSIGNED NULL,
  created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_vacc_farm (farm_id),
  CONSTRAINT fk_vacc_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- Vaccination programs — a reusable, named template listing which vaccine
-- (with route/dose) is due at which age in days, so scheduling a flock's
-- whole vaccination calendar is "apply this program" instead of adding every
-- entry by hand. Applying one is done client-side (computes scheduled_on =
-- flock.placed_on + age_days per item, then creates ordinary `vaccinations`
-- rows through the existing endpoint) — these tables only hold the templates.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS vaccination_programs (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id       INT UNSIGNED  NOT NULL,
  name          VARCHAR(120)  NOT NULL,
  created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_vaxprog_farm (farm_id),
  CONSTRAINT fk_vaxprog_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS vaccination_program_items (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  program_id    INT UNSIGNED  NOT NULL,
  vaccine       VARCHAR(120)  NOT NULL,
  route         VARCHAR(60)   NULL,
  dose          VARCHAR(40)   NULL,
  age_days      INT UNSIGNED  NOT NULL DEFAULT 0,
  -- carried onto the vaccinations row this item generates when the program is
  -- applied to a flock — see vaccinations.repeat_every_days above
  repeat_every_days INT UNSIGNED NULL,
  sort_order    INT UNSIGNED  NOT NULL DEFAULT 0,
  KEY idx_vaxprogitem_program (program_id),
  CONSTRAINT fk_vaxprogitem_program FOREIGN KEY (program_id) REFERENCES vaccination_programs(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- Inventory (general stock — packaging, equipment, medication, etc.)
-- `purchased_on` plus EITHER `useful_life_months` OR `depreciation_rate` are
-- optional and only meaningful for depreciable equipment (not consumables) —
-- when set, the frontend computes straight-line current value live (never
-- cached, same philosophy as every other derived total in this app):
-- useful_life_months set  -> cost - (cost ÷ useful life months) × months elapsed
-- depreciation_rate set   -> cost - (cost × rate/100 ÷ 12) × months elapsed
-- (depreciation_rate is a %/year straight-line rate — a second, equivalent way
-- to express the same useful-life idea, not a different depreciation model;
-- useful_life_months wins if both are somehow set). Leaving both blank means
-- "not tracked", and the item's value is just its unit cost, unchanged.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS inventory_items (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id       INT UNSIGNED  NOT NULL,
  sku           VARCHAR(40)   NOT NULL,
  name          VARCHAR(160)  NOT NULL,
  category      VARCHAR(60)   NOT NULL,
  qty           DECIMAL(12,2) NOT NULL DEFAULT 0,
  reorder_level DECIMAL(12,2) NOT NULL DEFAULT 0,
  unit          VARCHAR(20)   NOT NULL DEFAULT 'pcs',
  -- opt-in batch/expiry tracking: when 1, qty is the SUM of inventory_batches
  -- and stock is drawn first-expiry-first-out. See migration-2026-07-inventory-batches.sql.
  track_batches TINYINT(1)    NOT NULL DEFAULT 0,
  unit_cost     DECIMAL(12,2) NOT NULL DEFAULT 0,
  purchased_on       DATE          NULL,
  useful_life_months INT UNSIGNED  NULL,
  depreciation_rate  DECIMAL(5,2)  NULL, -- %/year, e.g. 20.00
  created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_inv_sku (farm_id, sku),
  KEY idx_inv_farm (farm_id),
  CONSTRAINT fk_inv_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- Inventory usage — losses/write-offs (damaged, expired, lost, stolen, other)
-- that reduce a specific item's stock, the same "usage log that draws down a
-- real stock" pattern as bird_usage/egg_usage/feed_stock, just keyed to one
-- inventory item instead of a farm-wide pool (each item is distinct, not
-- fungible like eggs across grades).
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS inventory_usage (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id       INT UNSIGNED  NOT NULL,
  item_id       INT UNSIGNED  NOT NULL,
  qty           DECIMAL(12,2) NOT NULL,
  reason        ENUM('damaged','expired','lost','stolen','other') NOT NULL,
  note          VARCHAR(255)  NULL,
  -- for a batched item, the per-lot draw this loss took (FEFO), so an edit/delete
  -- restores the same lots. NULL for a non-batched item.
  batch_breakdown JSON        NULL,
  used_on       DATE          NOT NULL,
  created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_invusage_farm (farm_id, used_on),
  KEY idx_invusage_item (item_id),
  CONSTRAINT fk_invusage_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE,
  CONSTRAINT fk_invusage_item FOREIGN KEY (item_id) REFERENCES inventory_items(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Batches (lots) of a batch-tracked inventory item, each with its own expiry.
-- A batched item's qty is the sum of these. See migration-2026-07-inventory-batches.sql.
CREATE TABLE IF NOT EXISTS inventory_batches (
  id          INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id     INT UNSIGNED  NOT NULL,
  item_id     INT UNSIGNED  NOT NULL,
  lot_no      VARCHAR(60)   NULL,
  qty         DECIMAL(12,2) NOT NULL DEFAULT 0,
  expiry_date DATE          NULL,
  received_on DATE          NULL,
  supplier    VARCHAR(120)  NULL,
  grn_id      INT UNSIGNED  NULL,
  note        VARCHAR(255)  NULL,
  created_at  TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_invb_item (item_id),
  KEY idx_invb_farm_expiry (farm_id, expiry_date),
  KEY idx_invb_grn (grn_id),
  CONSTRAINT fk_invb_item FOREIGN KEY (item_id) REFERENCES inventory_items(id) ON DELETE CASCADE,
  CONSTRAINT fk_invb_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- Customers — an optional directory of regular/repeat customers a sale can
-- pick from instead of retyping their details. Sales still store the
-- customer's name/phone/address as plain text on the invoice itself (not a
-- foreign key) so a one-off/walk-in customer never has to be added here
-- first, and so an invoice's details stay frozen even if the customer record
-- is later edited or removed.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS customers (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id       INT UNSIGNED  NOT NULL,
  name          VARCHAR(160)  NOT NULL,
  phone         VARCHAR(40)   NULL,
  email         VARCHAR(160)  NULL,          -- for CRM email campaigns
  address       VARCHAR(255)  NULL,
  created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_customers_farm (farm_id),
  CONSTRAINT fk_customers_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- Suppliers — the mirror image of customers: an optional directory of
-- regular/repeat suppliers a feed purchase or expense can pick from instead
-- of retyping their details every time. Same reasoning as customers: never
-- referenced by a foreign key from feed_purchases/expenses (both keep the
-- supplier/payee name as plain text), so a one-time supplier never has to be
-- added here first, and a past record stays frozen even if this directory
-- entry is later edited or removed.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS suppliers (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id       INT UNSIGNED  NOT NULL,
  name          VARCHAR(160)  NOT NULL,
  phone         VARCHAR(40)   NULL,
  address       VARCHAR(255)  NULL,
  created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_suppliers_farm (farm_id),
  CONSTRAINT fk_suppliers_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- Sales (invoices)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS sales (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id       INT UNSIGNED  NOT NULL,
  invoice_no    VARCHAR(40)   NOT NULL,
  customer      VARCHAR(160)  NOT NULL,
  customer_phone   VARCHAR(40)  NULL,
  customer_address VARCHAR(255) NULL,
  amount        DECIMAL(14,2) NOT NULL DEFAULT 0, -- sum of this invoice's sale_items.line_total
  amount_paid   DECIMAL(14,2) NOT NULL DEFAULT 0,
  status        ENUM('paid','credit','partial') NOT NULL DEFAULT 'paid',
  sold_on       DATE          NOT NULL,
  -- unguessable public handle for the printed QR code's /verify link. The QR
  -- must NOT expose the sequential id, or anyone could walk /verify/sale/1,2,3…
  -- and harvest every farm's customers and amounts. Set once at create time.
  verify_token  CHAR(32)      NULL,
  created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_invoice (farm_id, invoice_no),
  UNIQUE KEY uq_sales_verify_token (verify_token),
  KEY idx_sales_farm (farm_id, sold_on),
  CONSTRAINT fk_sales_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- Sale line items — an invoice can list several items, each with its own
-- qty/price, instead of the one-item-per-invoice model sales used to have.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS sale_items (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  sale_id       INT UNSIGNED  NOT NULL,
  item          VARCHAR(120)  NOT NULL,
  qty           INT UNSIGNED  NOT NULL DEFAULT 0,
  unit_price    DECIMAL(14,2) NOT NULL DEFAULT 0,
  line_total    DECIMAL(14,2) NOT NULL DEFAULT 0,
  sort_order    INT UNSIGNED  NOT NULL DEFAULT 0,
  -- which egg grade this line was sold from (cracked/dirty often go at a
  -- discount) — meaningless for non-egg items, so NULL there. Mirrors
  -- egg_usage.grade; see that table's comment for the 'mixed' convention.
  grade         ENUM('gradeTwin','gradeA','gradeB','cracked','dirty','rejected','mixed') NULL,
  -- which egg store this line was sold from — meaningless for non-egg items,
  -- so NULL there, same treatment as grade above
  store         VARCHAR(64) NULL,
  KEY idx_sale_items_sale (sale_id),
  CONSTRAINT fk_sale_items_sale FOREIGN KEY (sale_id) REFERENCES sales(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- Delivery notes — a document accompanying goods leaving the farm (items +
-- quantities only, never prices). Raised against a sale (copies its customer +
-- items) or standalone; optional Fleet vehicle/driver; pending -> delivered
-- with a signature line. Under the Sales permission. See
-- migration-2026-07-delivery-notes.sql.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS delivery_notes (
  id              INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id         INT UNSIGNED  NOT NULL,
  ref_no          VARCHAR(40)   NOT NULL,
  sale_id         INT UNSIGNED  NULL,
  customer        VARCHAR(160)  NOT NULL,
  customer_phone  VARCHAR(40)   NULL,
  delivery_address VARCHAR(255) NULL,
  delivery_date   DATE          NOT NULL,
  vehicle_id      INT UNSIGNED  NULL,
  driver_id       INT UNSIGNED  NULL,
  status          ENUM('pending','delivered','cancelled') NOT NULL DEFAULT 'pending',
  received_by     VARCHAR(120)  NULL,
  delivered_at    DATETIME      NULL,
  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_dn_farm (farm_id),
  KEY idx_dn_sale (sale_id),
  CONSTRAINT fk_dn_farm    FOREIGN KEY (farm_id)    REFERENCES farms(id)         ON DELETE CASCADE,
  CONSTRAINT fk_dn_sale    FOREIGN KEY (sale_id)    REFERENCES sales(id)         ON DELETE SET NULL,
  CONSTRAINT fk_dn_vehicle FOREIGN KEY (vehicle_id) REFERENCES vehicles(id)      ON DELETE SET NULL,
  CONSTRAINT fk_dn_driver  FOREIGN KEY (driver_id)  REFERENCES fleet_drivers(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS delivery_note_items (
  id          INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  dn_id       INT UNSIGNED  NOT NULL,
  description VARCHAR(200)  NOT NULL,
  qty         DECIMAL(12,2) NOT NULL DEFAULT 0,
  unit        VARCHAR(40)   NULL,
  sort_order  INT UNSIGNED  NOT NULL DEFAULT 0,
  KEY idx_dni_dn (dn_id),
  CONSTRAINT fk_dni_dn FOREIGN KEY (dn_id) REFERENCES delivery_notes(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- Egg bookings — a customer deposit taken in advance (often when there's a
-- shortage at the time), before any eggs actually leave the farm. Deliberately
-- NEVER touches egg_collections/egg_usage while 'pending' — the cash is real
-- and tracked (as a liability, not Revenue — see Dashboard's "Customer
-- Deposits" figure), but nothing has left stock yet, so nothing should be
-- deducted. A customer often collects in several installments rather than
-- all at once (e.g. 200 trays booked, taken as 50 + 50 + 100 over separate
-- visits) — `qty_fulfilled`/`amount_paid_applied` are running totals across
-- every such partial fulfillment (see egg_booking_fulfillments below), and
-- the booking only flips to 'fulfilled' once qty_fulfilled reaches qty.
-- Each fulfillment (BookingController::fulfill) converts that installment
-- into a real `sales` + `sale_items` row at that moment — stock is validated
-- and deducted THEN, applying whatever's left of the deposit as that sale's
-- payment. Cancelling a booking (e.g. the customer never returns) just flips
-- its status; refunding the cash, if any, is a real-world step outside this table.
-- ---------------------------------------------------------------------------
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,
  qty_fulfilled INT UNSIGNED  NOT NULL DEFAULT 0,  -- running total supplied so far, across every partial fulfillment
  unit_price    DECIMAL(14,2) NOT NULL DEFAULT 0,
  amount        DECIMAL(14,2) NOT NULL DEFAULT 0,  -- qty * unit_price, server-computed like sales.amount
  amount_paid   DECIMAL(14,2) NOT NULL DEFAULT 0,  -- the deposit actually collected today
  amount_paid_applied DECIMAL(14,2) NOT NULL DEFAULT 0,  -- how much of that deposit has been credited to a fulfillment sale so far
  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,  -- most recent fulfillment's sale — full history is in egg_booking_fulfillments
  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;

-- one row per partial (or full) fulfillment of a booking — the real history
-- that qty_fulfilled/amount_paid_applied above are just running totals of
CREATE TABLE IF NOT EXISTS egg_booking_fulfillments (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  booking_id    INT UNSIGNED  NOT NULL,
  sale_id       INT UNSIGNED  NULL,
  qty           INT UNSIGNED  NOT NULL DEFAULT 0,
  fulfilled_on  DATE          NOT NULL,
  created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_bookingfulfillments_booking (booking_id),
  CONSTRAINT fk_bookingfulfillments_booking FOREIGN KEY (booking_id) REFERENCES egg_bookings(id) ON DELETE CASCADE,
  CONSTRAINT fk_bookingfulfillments_sale FOREIGN KEY (sale_id) REFERENCES sales(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- Egg usage — every way eggs leave stock (sold, donated, consumed on-farm, or
-- other). Current POOLED stock = SUM(egg_collections.total) - SUM(egg_usage.qty),
-- computed on read rather than cached, same as every other derived total in
-- this app. `grade` optionally attributes a usage row to a specific grade
-- bucket (e.g. selling cracked eggs at a discount) — 'mixed' is the default/
-- legacy meaning "drawn from the pooled stock, not attributed to one grade",
-- exactly how every row behaved before grades existed here, so old rows and
-- anyone who doesn't care to specify keep working unchanged. Per-grade
-- available stock = that grade's SUM(egg_collections.<grade column>) minus
-- SUM(egg_usage.qty) WHERE grade = that same grade — see
-- EggUsageController::availableForGrade(). `sale_id` is set only for
-- reason='sold' rows, which SalesController creates automatically (one row
-- per egg line item, each carrying that line's own grade) — never written
-- directly by the user.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS egg_usage (
  id          INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id     INT UNSIGNED NOT NULL,
  qty         INT UNSIGNED NOT NULL,
  reason      ENUM('sold','donated','consumed','missing','hatching','other') NOT NULL,
  grade       ENUM('gradeTwin','gradeA','gradeB','cracked','dirty','rejected','mixed') NOT NULL DEFAULT 'mixed',
  -- which egg store this usage was drawn from (managed list 'eggStores'),
  -- nullable for pre-existing rows — see egg_collections.store above
  store       VARCHAR(64) NULL,
  note        VARCHAR(255) NULL,
  used_on     DATE NOT NULL,
  sale_id     INT UNSIGNED NULL,
  created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_egg_usage_farm (farm_id, used_on),
  KEY idx_eggusage_farm_store (farm_id, store),
  CONSTRAINT fk_egg_usage_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE,
  CONSTRAINT fk_egg_usage_sale FOREIGN KEY (sale_id) REFERENCES sales(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- Hatchery — incubation "sets" from egg-set to hatch, with candling events.
-- An 'own' set draws eggs from stock (egg_usage, reason 'hatching', linked by
-- egg_usage_id); a 'bought' set records supplier + per-egg cost. Hatching can
-- place chicks as a new flock (flock_id). See migration-2026-07-hatchery.sql.
-- ---------------------------------------------------------------------------
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,
  source            ENUM('own','bought') NOT NULL DEFAULT 'own',
  breed             VARCHAR(64)   NOT NULL,
  store             VARCHAR(64)   NULL,
  machine           VARCHAR(80)   NULL,
  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,
  egg_unit_cost     DECIMAL(10,2) NULL,
  eggs_removed      INT UNSIGNED  NOT NULL DEFAULT 0,
  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,
  dead_in_shell     INT UNSIGNED  NULL,
  flock_id          INT UNSIGNED  NULL,
  egg_usage_id      INT UNSIGNED  NULL,
  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,
  eggs_checked INT UNSIGNED NULL,
  removed      INT UNSIGNED NOT NULL DEFAULT 0,
  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;

-- ---------------------------------------------------------------------------
-- Biosecurity — visitor/vehicle register, disinfection log, configurable
-- checklist templates run as pass/fail inspections, and incidents. Standalone
-- module. See migration-2026-07-biosecurity.sql.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS biosecurity_visitors (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id       INT UNSIGNED NOT NULL,
  visitor_name  VARCHAR(120) NOT NULL,
  company       VARCHAR(120) NULL,
  purpose       VARCHAR(160) NULL,
  phone         VARCHAR(40)  NULL,
  from_location VARCHAR(160) NULL,
  vehicle_reg   VARCHAR(40)  NULL,
  visited_on    DATE         NOT NULL,
  time_in       VARCHAR(5)   NULL,
  time_out      VARCHAR(5)   NULL,
  disinfected   TINYINT(1)   NOT NULL DEFAULT 0,
  note          VARCHAR(255) NULL,
  created_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_bvis_farm (farm_id, visited_on),
  CONSTRAINT fk_bvis_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS biosecurity_disinfections (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id       INT UNSIGNED NOT NULL,
  location      VARCHAR(120) NOT NULL,
  chemical      VARCHAR(120) NULL,
  concentration VARCHAR(60)  NULL,
  done_by       VARCHAR(120) NULL,
  done_on       DATE         NOT NULL,
  time          VARCHAR(5)   NULL,
  note          VARCHAR(255) NULL,
  created_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_bdis_farm (farm_id, done_on),
  CONSTRAINT fk_bdis_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS biosecurity_incidents (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id       INT UNSIGNED NOT NULL,
  incident_type VARCHAR(120) NOT NULL,
  severity      ENUM('low','medium','high') NOT NULL DEFAULT 'medium',
  occurred_on   DATE         NOT NULL,
  description   VARCHAR(500) NULL,
  action_taken  VARCHAR(500) NULL,
  status        ENUM('open','resolved') NOT NULL DEFAULT 'open',
  resolved_on   DATE         NULL,
  created_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_binc_farm (farm_id, status),
  CONSTRAINT fk_binc_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS biosecurity_checklists (
  id         INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id    INT UNSIGNED NOT NULL,
  name       VARCHAR(120) NOT NULL,
  active     TINYINT(1)   NOT NULL DEFAULT 1,
  created_at TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_bchk_farm (farm_id),
  CONSTRAINT fk_bchk_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS biosecurity_checklist_items (
  id          INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  template_id INT UNSIGNED NOT NULL,
  farm_id     INT UNSIGNED NOT NULL,
  label       VARCHAR(200) NOT NULL,
  sort_order  INT UNSIGNED NOT NULL DEFAULT 0,
  KEY idx_bchki_tpl (template_id),
  CONSTRAINT fk_bchki_tpl  FOREIGN KEY (template_id) REFERENCES biosecurity_checklists(id) ON DELETE CASCADE,
  CONSTRAINT fk_bchki_farm FOREIGN KEY (farm_id)     REFERENCES farms(id)                  ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS biosecurity_inspections (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id       INT UNSIGNED NOT NULL,
  template_id   INT UNSIGNED NULL,
  template_name VARCHAR(120) NULL,
  inspected_on  DATE         NOT NULL,
  inspector     VARCHAR(120) NULL,
  passed        INT UNSIGNED NOT NULL DEFAULT 0,
  failed        INT UNSIGNED NOT NULL DEFAULT 0,
  na            INT UNSIGNED NOT NULL DEFAULT 0,
  score_pct     DECIMAL(5,1) NULL,
  note          VARCHAR(255) NULL,
  created_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_bins_farm (farm_id, inspected_on),
  CONSTRAINT fk_bins_farm FOREIGN KEY (farm_id)     REFERENCES farms(id)                  ON DELETE CASCADE,
  CONSTRAINT fk_bins_tpl  FOREIGN KEY (template_id) REFERENCES biosecurity_checklists(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS biosecurity_inspection_results (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  inspection_id INT UNSIGNED NOT NULL,
  farm_id       INT UNSIGNED NOT NULL,
  label         VARCHAR(200) NOT NULL,
  result        ENUM('pass','fail','na') NOT NULL DEFAULT 'pass',
  note          VARCHAR(255) NULL,
  KEY idx_binsr_ins (inspection_id),
  CONSTRAINT fk_binsr_ins  FOREIGN KEY (inspection_id) REFERENCES biosecurity_inspections(id) ON DELETE CASCADE,
  CONSTRAINT fk_binsr_farm FOREIGN KEY (farm_id)       REFERENCES farms(id)                   ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- Equipment & maintenance — a register of individual durable machines and
-- keeping them running (separate from Inventory's consumable stock). A
-- completed maintenance record with a cost posts a Maintenance expense to the
-- ledger, exactly like Fleet. See migration-2026-07-equipment.sql.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS equipment (
  id                 INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id            INT UNSIGNED  NOT NULL,
  name               VARCHAR(120)  NOT NULL,
  type               VARCHAR(60)   NULL,
  serial_no          VARCHAR(80)   NULL,
  location           VARCHAR(120)  NULL,
  manufacturer       VARCHAR(120)  NULL,
  model              VARCHAR(120)  NULL,
  purchased_on       DATE          NULL,
  purchase_cost      DECIMAL(14,2) NULL,
  useful_life_months INT UNSIGNED  NULL,
  depreciation_rate  DECIMAL(5,2)  NULL,
  status             ENUM('running','maintenance','down','retired') NOT NULL DEFAULT 'running',
  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_equip_farm (farm_id),
  CONSTRAINT fk_equip_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS equipment_maintenance (
  id             INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id        INT UNSIGNED  NOT NULL,
  equipment_id   INT UNSIGNED  NOT NULL,
  kind           ENUM('preventive','repair','inspection','other') NOT NULL DEFAULT 'repair',
  service_date   DATE          NOT NULL,
  description    VARCHAR(255)  NULL,
  cost           DECIMAL(14,2) NOT NULL DEFAULT 0,
  provider       VARCHAR(120)  NULL,
  downtime_hours DECIMAL(8,2)  NULL,
  next_due_date  DATE          NULL,
  status         ENUM('scheduled','done') NOT NULL DEFAULT 'done',
  notes          VARCHAR(500)  NULL,
  created_at     TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_emaint_farm (farm_id, service_date),
  KEY idx_emaint_equip (equipment_id),
  CONSTRAINT fk_emaint_farm  FOREIGN KEY (farm_id)      REFERENCES farms(id)     ON DELETE CASCADE,
  CONSTRAINT fk_emaint_equip FOREIGN KEY (equipment_id) REFERENCES equipment(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS equipment_pm (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id       INT UNSIGNED NOT NULL,
  equipment_id  INT UNSIGNED NOT NULL,
  task          VARCHAR(160) NOT NULL,
  interval_days INT UNSIGNED NOT NULL DEFAULT 30,
  last_done_on  DATE         NULL,
  next_due_on   DATE         NOT NULL,
  active        TINYINT(1)   NOT NULL DEFAULT 1,
  notes         VARCHAR(255) NULL,
  created_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_epm_farm (farm_id, next_due_on),
  KEY idx_epm_equip (equipment_id),
  CONSTRAINT fk_epm_farm  FOREIGN KEY (farm_id)      REFERENCES farms(id)     ON DELETE CASCADE,
  CONSTRAINT fk_epm_equip FOREIGN KEY (equipment_id) REFERENCES equipment(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- Document management — uploaded farm documents (licences, permits, contracts,
-- SOPs, receipts) with categories + expiry tracking. File bytes live on the
-- filesystem outside the web root (backend/storage/documents); only this
-- metadata is in the DB. See migration-2026-07-documents.sql.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS documents (
  id          INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id     INT UNSIGNED  NOT NULL,
  title       VARCHAR(160)  NOT NULL,
  category    VARCHAR(60)   NULL,
  doc_no      VARCHAR(80)   NULL,
  issuer      VARCHAR(120)  NULL,
  issued_on   DATE          NULL,
  expires_on  DATE          NULL,
  file_name   VARCHAR(200)  NOT NULL,
  stored_name VARCHAR(200)  NOT NULL,
  mime        VARCHAR(120)  NULL,
  size_bytes  INT UNSIGNED  NOT NULL DEFAULT 0,
  uploaded_by VARCHAR(120)  NULL,
  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_doc_farm (farm_id),
  KEY idx_doc_expiry (farm_id, expires_on),
  CONSTRAINT fk_doc_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- CRM / marketing — campaigns (SMS/email blasts to a customer segment, sent via
-- the EgoSMS gateway / SMTP, with per-recipient status), follow-up tasks, and
-- complaints. Builds on the Customers directory. See migration-2026-07-crm.sql.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS crm_campaigns (
  id           INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id      INT UNSIGNED  NOT NULL,
  name         VARCHAR(120)  NOT NULL,
  channel      ENUM('sms','email') NOT NULL DEFAULT 'sms',
  audience     VARCHAR(30)   NOT NULL DEFAULT 'all',
  subject      VARCHAR(160)  NULL,
  message      TEXT          NOT NULL,
  status       ENUM('draft','sent') NOT NULL DEFAULT 'draft',
  total        INT UNSIGNED  NOT NULL DEFAULT 0,
  sent_count   INT UNSIGNED  NOT NULL DEFAULT 0,
  failed_count INT UNSIGNED  NOT NULL DEFAULT 0,
  sent_at      DATETIME      NULL,
  created_at   TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_crmc_farm (farm_id),
  CONSTRAINT fk_crmc_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS crm_campaign_recipients (
  id          INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  campaign_id INT UNSIGNED  NOT NULL,
  farm_id     INT UNSIGNED  NOT NULL,
  customer_id INT UNSIGNED  NULL,
  name        VARCHAR(160)  NULL,
  contact     VARCHAR(160)  NULL,
  status      ENUM('pending','sent','failed','skipped') NOT NULL DEFAULT 'pending',
  detail      VARCHAR(255)  NULL,
  sent_at     DATETIME      NULL,
  KEY idx_crmr_campaign (campaign_id),
  CONSTRAINT fk_crmr_campaign FOREIGN KEY (campaign_id) REFERENCES crm_campaigns(id) ON DELETE CASCADE,
  CONSTRAINT fk_crmr_farm     FOREIGN KEY (farm_id)     REFERENCES farms(id)         ON DELETE CASCADE,
  CONSTRAINT fk_crmr_customer FOREIGN KEY (customer_id) REFERENCES customers(id)     ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS crm_followups (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id       INT UNSIGNED NOT NULL,
  customer_id   INT UNSIGNED NULL,
  customer_name VARCHAR(160) NULL,
  title         VARCHAR(200) NOT NULL,
  due_on        DATE         NOT NULL,
  assigned_to   VARCHAR(120) NULL,
  status        ENUM('open','done') NOT NULL DEFAULT 'open',
  done_on       DATE         NULL,
  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_crmf_farm (farm_id, status, due_on),
  KEY idx_crmf_customer (customer_id),
  CONSTRAINT fk_crmf_farm     FOREIGN KEY (farm_id)     REFERENCES farms(id)     ON DELETE CASCADE,
  CONSTRAINT fk_crmf_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS crm_complaints (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id       INT UNSIGNED NOT NULL,
  customer_id   INT UNSIGNED NULL,
  customer_name VARCHAR(160) NULL,
  subject       VARCHAR(200) NOT NULL,
  severity      ENUM('low','medium','high') NOT NULL DEFAULT 'medium',
  description   VARCHAR(500) NULL,
  action_taken  VARCHAR(500) NULL,
  status        ENUM('open','resolved') NOT NULL DEFAULT 'open',
  occurred_on   DATE         NOT NULL,
  resolved_on   DATE         NULL,
  created_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_crmx_farm (farm_id, status),
  KEY idx_crmx_customer (customer_id),
  CONSTRAINT fk_crmx_farm     FOREIGN KEY (farm_id)     REFERENCES farms(id)     ON DELETE CASCADE,
  CONSTRAINT fk_crmx_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- Egg store transfers — moves pooled stock between two egg stores (e.g. Cold
-- Room -> Shop Front). A transfer never changes the farm-wide total (it
-- credits one store and debits another by the same qty), so it's a pure
-- ledger, not a collection or usage event — see the comment atop
-- sql/migration-2026-07-egg-stores.sql for the read-time qty-per-store formula.
-- ---------------------------------------------------------------------------
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,
  -- 'mixed' (default) moves pooled/ungraded stock, same as before this column
  -- existed; naming a real grade attributes the move to that grade's bucket
  -- in both stores — see sql/migration-2026-07-egg-store-transfer-grade.sql
  grade         ENUM('gradeTwin','gradeA','gradeB','cracked','dirty','rejected','mixed') NOT NULL DEFAULT 'mixed',
  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;

-- ---------------------------------------------------------------------------
-- Bird usage — manual ways LIVE birds leave the flock other than dying:
-- donated or consumed on-farm (mirrors egg_usage's manual reasons). Unlike
-- eggs, "sold" has no row here at all — the number of birds a sale represents
-- is derived client-side from the sale's own line items (see
-- lib/metrics.js#birdsSoldFrom), the same way it's already computed for the
-- Dashboard/Topbar Live Birds figure, so there's nothing to sync. "Died" also
-- has no row here — that's daily_entries.mortality + culls, already recorded
-- through Daily Entry, not re-entered a second time through this table.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS bird_usage (
  id          INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id     INT UNSIGNED NOT NULL,
  qty         INT UNSIGNED NOT NULL,
  reason      ENUM('donated','consumed','missing','other') NOT NULL,
  note        VARCHAR(255) NULL,
  used_on     DATE NOT NULL,
  created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_bird_usage_farm (farm_id, used_on),
  CONSTRAINT fk_bird_usage_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- Farm payment accounts — how customers can pay this farm (bank details,
-- mobile money, ...), printed on every invoice/receipt. Free-text method +
-- details rather than rigid bank-name/account-number columns, since the
-- right fields vary a lot by payment method (a bank transfer needs an
-- account number; mobile money just needs a phone number and a name).
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS farm_payment_accounts (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id       INT UNSIGNED  NOT NULL,
  method        VARCHAR(80)   NOT NULL,   -- e.g. "Bank Transfer", "Mobile Money (MTN)"
  details       VARCHAR(255)  NOT NULL,   -- e.g. "Stanbic Bank · Acc 9030012345 · HOLA Farm Ltd"
  sort_order    INT           NOT NULL DEFAULT 0,
  created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_fpa_farm (farm_id, sort_order),
  CONSTRAINT fk_fpa_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- Expenses (manual). Payroll runs surface as expenses at read time (see view).
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS expenses (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id       INT UNSIGNED  NOT NULL,
  ref_no        VARCHAR(40)   NOT NULL,
  category      VARCHAR(60)   NOT NULL,
  payee         VARCHAR(160)  NOT NULL,
  items         VARCHAR(255)  NULL, -- free-text description of what was paid for, e.g. "300 egg trays, wire mesh"
  amount        DECIMAL(14,2) NOT NULL DEFAULT 0,
  method        VARCHAR(40)   NOT NULL DEFAULT 'Cash',
  status        ENUM('paid','pending') NOT NULL DEFAULT 'paid',
  spent_on      DATE          NOT NULL,
  created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_expense (farm_id, ref_no),
  KEY idx_exp_farm (farm_id, spent_on),
  CONSTRAINT fk_exp_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- Employees + Payroll
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS employees (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id       INT UNSIGNED  NOT NULL,
  name          VARCHAR(160)  NOT NULL,
  role          VARCHAR(80)   NOT NULL,
  department    VARCHAR(80)   NOT NULL,
  phone         VARCHAR(40)   NULL,
  email         VARCHAR(160)  NULL,
  salary        DECIMAL(14,2) NOT NULL DEFAULT 0,   -- gross monthly
  status        ENUM('present','absent','leave') NOT NULL DEFAULT 'present',
  created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_emp_farm (farm_id),
  CONSTRAINT fk_emp_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Deeper HR — attendance, leave (pending -> approved/rejected) and performance
-- reviews, all against employees. See migration-2026-07-hr-plus.sql.
CREATE TABLE IF NOT EXISTS hr_attendance (
  id          INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id     INT UNSIGNED NOT NULL,
  employee_id INT UNSIGNED NOT NULL,
  work_date   DATE         NOT NULL,
  status      ENUM('present','absent','late','half_day','leave') NOT NULL DEFAULT 'present',
  hours       DECIMAL(5,2) NULL,
  note        VARCHAR(255) NULL,
  created_at  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_att (farm_id, employee_id, work_date),
  KEY idx_att_farm (farm_id, work_date),
  CONSTRAINT fk_att_farm FOREIGN KEY (farm_id)     REFERENCES farms(id)     ON DELETE CASCADE,
  CONSTRAINT fk_att_emp  FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS hr_leave (
  id          INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id     INT UNSIGNED NOT NULL,
  employee_id INT UNSIGNED NOT NULL,
  leave_type  VARCHAR(40)  NOT NULL DEFAULT 'annual',
  from_date   DATE         NOT NULL,
  to_date     DATE         NOT NULL,
  days        DECIMAL(4,1) NOT NULL DEFAULT 1,
  reason      VARCHAR(255) NULL,
  status      ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  decided_by  VARCHAR(120) NULL,
  decided_on  DATE         NULL,
  created_at  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_leave_farm (farm_id, status),
  KEY idx_leave_emp (employee_id),
  CONSTRAINT fk_leave_farm FOREIGN KEY (farm_id)     REFERENCES farms(id)     ON DELETE CASCADE,
  CONSTRAINT fk_leave_emp  FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS hr_reviews (
  id           INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id      INT UNSIGNED NOT NULL,
  employee_id  INT UNSIGNED NOT NULL,
  period       VARCHAR(40)  NOT NULL,
  review_date  DATE         NOT NULL,
  reviewer     VARCHAR(120) NULL,
  rating       TINYINT UNSIGNED NULL,
  strengths    VARCHAR(500) NULL,
  improvements VARCHAR(500) NULL,
  notes        VARCHAR(500) NULL,
  created_at   TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_rev_farm (farm_id),
  KEY idx_rev_emp (employee_id),
  CONSTRAINT fk_rev_farm FOREIGN KEY (farm_id)     REFERENCES farms(id)     ON DELETE CASCADE,
  CONSTRAINT fk_rev_emp  FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS payroll_runs (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id       INT UNSIGNED  NOT NULL,
  ref_no        VARCHAR(40)   NOT NULL,
  period        VARCHAR(40)   NOT NULL,          -- e.g. "July 2026"
  item          VARCHAR(120)  NULL,              -- optional description, e.g. "Monthly salaries"
  method        VARCHAR(40)   NULL,              -- how staff were paid: Cash / Bank transfer / Mobile Money
  headcount     INT UNSIGNED  NOT NULL DEFAULT 0,
  gross         DECIMAL(14,2) NOT NULL DEFAULT 0,
  deductions    DECIMAL(14,2) NOT NULL DEFAULT 0,
  advance_recovered DECIMAL(14,2) NOT NULL DEFAULT 0, -- salary advances recovered in this run
  net           DECIMAL(14,2) NOT NULL DEFAULT 0,     -- cash actually paid = gross - deductions - advance_recovered
  run_on        DATE          NOT NULL,
  status        VARCHAR(20)   NOT NULL DEFAULT 'processed',
  created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_payroll (farm_id, ref_no),
  KEY idx_pay_farm (farm_id),
  CONSTRAINT fk_pay_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- Payroll run lines — one row per employee actually paid in a run, so who's
-- included and their individual additions/deductions are real, not just an
-- aggregate percentage applied to everyone. employee_id is SET NULL (not
-- cascaded) if that employee is later deleted, and employee_name is snapshotted
-- at run time — a payroll run is a historical record of what was actually paid
-- and must stay readable even after the roster changes.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS payroll_run_lines (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  run_id        INT UNSIGNED  NOT NULL,
  employee_id   INT UNSIGNED  NULL,
  employee_name VARCHAR(160)  NOT NULL,
  base_salary   DECIMAL(14,2) NOT NULL DEFAULT 0,
  additions     DECIMAL(14,2) NOT NULL DEFAULT 0,
  deductions    DECIMAL(14,2) NOT NULL DEFAULT 0,
  advance_recovery DECIMAL(14,2) NOT NULL DEFAULT 0, -- salary advance recovered from this employee this run
  net           DECIMAL(14,2) NOT NULL DEFAULT 0,
  note          VARCHAR(255)  NULL,
  KEY idx_prl_run (run_id),
  KEY idx_prl_employee (employee_id),
  CONSTRAINT fk_prl_run FOREIGN KEY (run_id) REFERENCES payroll_runs(id) ON DELETE CASCADE,
  CONSTRAINT fk_prl_employee FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- Payroll run line items — the itemized breakdown behind each line's flat
-- `additions`/`deductions` totals above (e.g. "Overtime" + "Bonus" as two
-- separate addition items, "NSSF" + "Loan repayment" as two deduction items).
-- Those totals on payroll_run_lines stay as server-computed sums of these
-- rows so existing reads (reports, the runs table) don't need to change.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS payroll_run_line_items (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  run_line_id   INT UNSIGNED  NOT NULL,
  kind          ENUM('addition','deduction') NOT NULL,
  label         VARCHAR(120)  NOT NULL,
  amount        DECIMAL(14,2) NOT NULL,
  KEY idx_prli_line (run_line_id),
  CONSTRAINT fk_prli_line FOREIGN KEY (run_line_id) REFERENCES payroll_run_lines(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- Salary advances — money paid to a staff member ahead of payday, recovered
-- from later payroll. A receivable (asset), NOT an expense: `recovered` grows
-- as payroll runs claw it back, and `status` flips to 'cleared' once fully
-- repaid. Outstanding = amount - recovered.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS salary_advances (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id       INT UNSIGNED  NOT NULL,
  employee_id   INT UNSIGNED  NULL,
  employee_name VARCHAR(160)  NOT NULL,
  amount        DECIMAL(14,2) NOT NULL,
  recovered     DECIMAL(14,2) NOT NULL DEFAULT 0,
  method        VARCHAR(40)   NULL,           -- how it was paid out: Cash / Bank transfer / Mobile Money
  advanced_on   DATE          NOT NULL,
  note          VARCHAR(255)  NULL,
  status        VARCHAR(20)   NOT NULL DEFAULT 'open',  -- open | cleared | void
  created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_adv_farm (farm_id),
  KEY idx_adv_employee (employee_id),
  CONSTRAINT fk_adv_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE,
  CONSTRAINT fk_adv_employee FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Exact allocation of each payroll run's advance recovery to specific advances,
-- so reversing a run restores precisely what it clawed back (FK-cascades on run
-- delete; the destroy handler reads these to un-recover before they vanish).
CREATE TABLE IF NOT EXISTS payroll_advance_recoveries (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  run_id        INT UNSIGNED  NOT NULL,
  advance_id    INT UNSIGNED  NOT NULL,
  amount        DECIMAL(14,2) NOT NULL,
  KEY idx_par_run (run_id),
  KEY idx_par_advance (advance_id),
  CONSTRAINT fk_par_run FOREIGN KEY (run_id) REFERENCES payroll_runs(id) ON DELETE CASCADE,
  CONSTRAINT fk_par_advance FOREIGN KEY (advance_id) REFERENCES salary_advances(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- Managed lists — the dropdown option sets edited in Settings.
-- Stored as (list_key, value) rows; grouped by key on read.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS managed_lists (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id       INT UNSIGNED  NOT NULL,
  list_key      VARCHAR(48)   NOT NULL,   -- e.g. 'breeds','feedTypes','vets','eggStores'
  value         VARCHAR(120)  NOT NULL,
  sort_order    INT           NOT NULL DEFAULT 0,
  UNIQUE KEY uq_list_value (farm_id, list_key, value),
  KEY idx_list_key (farm_id, list_key),
  CONSTRAINT fk_managed_lists_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- Roles — a named set of per-module permissions ('none'|'view'|'edit'),
-- stored as JSON so the module list can grow without a schema change.
-- is_system marks the built-in "Admin" role, which can't be edited or deleted
-- (so a farm can never end up with nobody able to manage users/roles).
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS roles (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id       INT UNSIGNED  NOT NULL,
  name          VARCHAR(60)   NOT NULL,
  description   VARCHAR(160)  NULL,
  is_system     TINYINT(1)    NOT NULL DEFAULT 0,
  permissions   JSON          NOT NULL,
  created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_role_name (farm_id, name),
  CONSTRAINT fk_roles_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- Users — real login accounts. One farm each; a role governs what they can
-- see/do within it. No ON DELETE CASCADE from roles → users on purpose: a
-- role that still has users assigned can't be deleted (FK error surfaces as
-- a friendly message via Http::friendly()).
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS users (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id       INT UNSIGNED  NOT NULL,
  role_id       INT UNSIGNED  NOT NULL,
  name          VARCHAR(120)  NOT NULL,
  email         VARCHAR(160)  NOT NULL,
  phone         VARCHAR(40)   NULL,           -- for SMS OTP login
  password_hash VARCHAR(255)  NOT NULL,
  status        ENUM('active','suspended') NOT NULL DEFAULT 'active',
  -- a platform operator who manages ALL farms (create/suspend/delete, manage
  -- users, and impersonate). Above the per-farm role system; set by hand for
  -- the first operator, then via the admin dashboard. 0 for ordinary users.
  is_platform_admin TINYINT(1) NOT NULL DEFAULT 0,
  -- for a platform admin: JSON array of allowed Platform sections
  -- (farms/billing/support/system/migrate/admins). NULL = full (super admin).
  platform_perms JSON         NULL,
  -- optional authenticator-app 2FA: base32 shared secret (NULL when off) and
  -- whether the second login step is enforced. See migration-2026-07-2fa.sql.
  totp_secret   VARCHAR(64)   NULL,
  totp_enabled  TINYINT(1)    NOT NULL DEFAULT 0,
  last_login_at TIMESTAMP     NULL,
  created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_user_email (email),
  KEY idx_users_farm (farm_id),
  CONSTRAINT fk_users_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE,
  CONSTRAINT fk_users_role FOREIGN KEY (role_id) REFERENCES roles(id)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- User ↔ farm memberships — grants a user access to a specific farm WITH a
-- role in that farm, so one person can be assigned to several farms (a head
-- and its branches, or any set) and switch between them. users.farm_id stays
-- the user's home/default farm; a matching row here is seeded for it. Switching
-- into a farm is only allowed when a membership row exists (enforced in Auth),
-- and the user's effective role for that farm is this row's role_id.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS user_farms (
  id         INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  user_id    INT UNSIGNED NOT NULL,
  farm_id    INT UNSIGNED NOT NULL,
  role_id    INT UNSIGNED NOT NULL,
  created_at TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_user_farm (user_id, farm_id),
  KEY idx_uf_user (user_id),
  KEY idx_uf_farm (farm_id),
  CONSTRAINT fk_uf_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_uf_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE,
  CONSTRAINT fk_uf_role FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- Sessions — bearer tokens issued at login. A row per active login; deleting
-- it (logout) or letting it pass expires_at ends that session immediately.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS sessions (
  token         CHAR(64)      PRIMARY KEY,
  user_id       INT UNSIGNED  NOT NULL,
  -- when a platform admin is "inside" a farm (impersonating for setup/support),
  -- this holds that farm's id so every farm-scoped request resolves to it;
  -- NULL the rest of the time. Cleared if the farm is deleted.
  acting_farm_id INT UNSIGNED NULL,
  created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  expires_at    TIMESTAMP     NOT NULL,
  KEY idx_sessions_user (user_id),
  CONSTRAINT fk_sessions_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_sessions_acting_farm FOREIGN KEY (acting_farm_id) REFERENCES farms(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- Admin audit log — a platform-global (NOT farm-scoped) trail of operator
-- actions: provisioning, suspend/reactivate, delete, user changes, and
-- impersonation start/stop. Kept out of per-farm backups on purpose.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS admin_audit_log (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  admin_user_id INT UNSIGNED  NULL,   -- the operator; NULL-able so the log survives a user delete
  action        VARCHAR(40)   NOT NULL,
  farm_id       INT UNSIGNED  NULL,
  detail        VARCHAR(255)  NULL,
  created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_audit_created (created_at)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- Password resets — a short-lived token for the "forgot password" flow. Until
-- a mailer is configured the token is issued but NEVER returned in the API
-- response (that would let anyone who knows an email take over the account);
-- self-service reset therefore completes only via a real emailed link, and an
-- admin can reset any farm user's password from Settings in the meantime.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS password_resets (
  token         CHAR(64)      PRIMARY KEY,
  user_id       INT UNSIGNED  NOT NULL,
  created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  expires_at    TIMESTAMP     NOT NULL,
  KEY idx_pwreset_user (user_id),
  CONSTRAINT fk_pwreset_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- Rate limiting — a fixed-window counter per "bucket" (e.g. login:<ip>:<email>),
-- used to throttle authentication endpoints against brute-force / reset-spam.
-- A bucket resets itself once expires_at passes, so no separate sweep is needed.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS rate_limits (
  bucket        VARCHAR(191)  PRIMARY KEY,
  hits          INT UNSIGNED  NOT NULL DEFAULT 0,
  expires_at    DATETIME      NOT NULL
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- SMS send log — every attempt (success or failure) from src/Core/Sms.php,
-- for two reasons: an audit trail of what was actually texted, and the
-- dedup check that stops the same underlying event (a specific vaccination,
-- sale, or daily entry) from texting a farm more than once. `ref_id` is the
-- triggering record's own id (nullable — a manual/ad-hoc send has none).
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS sms_log (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id       INT UNSIGNED  NOT NULL,
  kind          VARCHAR(30)   NOT NULL, -- 'mortality','feed_reorder','sale_receipt','vaccination_due'
  ref_id        INT UNSIGNED  NULL,
  phone         VARCHAR(30)   NOT NULL,
  message       VARCHAR(400)  NOT NULL,
  status        VARCHAR(20)   NOT NULL, -- 'OK' or 'Failed', straight from the gateway
  response      VARCHAR(255)  NULL,     -- the gateway's own message, for troubleshooting
  sent_at       TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_sms_farm (farm_id, sent_at),
  KEY idx_sms_dedupe (farm_id, kind, ref_id),
  CONSTRAINT fk_sms_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- Per-farm, per-trigger SMS settings (Configurations page) — each of the 4
-- triggers (mortality, feed_reorder, sale_receipt, vaccination_due) can be
-- independently turned off and given its own custom wording, layered under
-- the farm's general "SMS alerts" toggle in Settings (which still gates all
-- of them). A farm gets a row here only once it customizes a trigger — no
-- row means "enabled, built-in default wording" (see Sms::triggerConfig()).
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS sms_triggers (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id       INT UNSIGNED  NOT NULL,
  kind          VARCHAR(30)   NOT NULL, -- 'mortality','feed_reorder','sale_receipt','vaccination_due'
  enabled       TINYINT(1)    NOT NULL DEFAULT 1,
  message       VARCHAR(500)  NULL, -- custom {token} template; NULL falls back to the built-in default
  created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_sms_trigger_farm_kind (farm_id, kind),
  CONSTRAINT fk_sms_trigger_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- A read-time view that unions manual expenses with payroll runs and feed
-- purchases, so both auto-appear in Expenses instead of needing to be
-- re-entered by hand. `source` flags which rows are read-only (derived).
-- ---------------------------------------------------------------------------
CREATE OR REPLACE VIEW v_expenses_all AS
  SELECT id, farm_id, ref_no, category, payee, items, amount, method, status, spent_on, 'manual' AS source
    FROM expenses
  UNION ALL
  SELECT NULL AS id, farm_id, ref_no, 'Payroll' AS category,
         CONCAT('Staff wages - ', period) AS payee, item AS items,
         net AS amount, COALESCE(NULLIF(method, ''), 'Bank transfer') AS method, 'paid' AS status,
         run_on AS spent_on, 'payroll' AS source
    FROM payroll_runs
  UNION ALL
  SELECT id AS id, farm_id,
         COALESCE(NULLIF(invoice_no, ''), CONCAT('FP-', id)) AS ref_no,
         'Feed' AS category,
         IF(supplier IS NOT NULL AND supplier <> '', supplier, CONCAT(feed_type, ' purchase')) AS payee,
         CONCAT(qty_kg, 'kg ', feed_type) AS items,
         line_cost AS amount,
         CASE WHEN payment_status = 'donated' THEN 'Donated'
              WHEN payment_status = 'credit' AND settled_on IS NULL THEN 'Credit'
              ELSE 'Cash' END AS method,
         CASE WHEN payment_status = 'credit' AND settled_on IS NULL THEN 'pending' ELSE 'paid' END AS status,
         purchased_on AS spent_on, 'feed' AS source
    FROM feed_purchases;

-- ===========================================================================
-- Mini accounting — a real double-entry ledger sitting UNDER the transactions
-- users already record. Sales, Expenses, Payroll, Feed purchases and Booking
-- deposits each auto-post one balanced journal entry the moment they're saved
-- (see App\Core\Ledger, called from inside each source controller's own
-- transaction, so a posting failure rolls the source write back too). The
-- ledger is the single source of truth for the Trial Balance / Balance Sheet /
-- Cashbook / General Ledger reports — nothing there is a re-sum of the source
-- tables, it's all aggregated from journal_entry_lines, so the books always
-- tie out (Σ debits = Σ credits by construction).
-- ===========================================================================

-- Chart of accounts — one per farm, seeded with a default poultry chart on
-- first use (Ledger::seedChart). `role` is a stable semantic key the
-- auto-poster resolves accounts by (e.g. 'cash', 'accounts_receivable',
-- 'sales_income') so it never hard-codes account names; `map_key` links an
-- expense account to the expense category it represents ('Feed', 'Utilities',
-- 'Payroll', …), so a brand-new category auto-creates a matching account.
CREATE TABLE IF NOT EXISTS chart_of_accounts (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id       INT UNSIGNED  NOT NULL,
  code          VARCHAR(20)   NOT NULL,
  name          VARCHAR(120)  NOT NULL,
  type          ENUM('asset','liability','equity','income','expense') NOT NULL,
  role          VARCHAR(48)   NULL,   -- fixed system accounts only (cash, accounts_receivable, …)
  map_key       VARCHAR(80)   NULL,   -- expense-category link (Feed / Utilities / Payroll / …)
  is_system     TINYINT(1)    NOT NULL DEFAULT 0,  -- seeded/auto-created accounts the user can't delete out from under the poster
  sort_order    INT UNSIGNED  NOT NULL DEFAULT 0,
  active        TINYINT(1)    NOT NULL DEFAULT 1,
  created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_coa_code (farm_id, code),
  KEY idx_coa_farm (farm_id),
  KEY idx_coa_role (farm_id, role),
  KEY idx_coa_mapkey (farm_id, map_key),
  CONSTRAINT fk_coa_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Journal entry header. `source_type`+`source_id` link back to the transaction
-- that generated it (sale/expense/payroll/feed/booking/sale_payment/…), so an
-- edit re-posts idempotently (delete + reinsert this entry) and a delete
-- reverses cleanly. A manual entry (opening balances, adjustments) leaves both
-- NULL. MySQL allows many NULLs in a UNIQUE key, so several manual entries
-- coexist while each source transaction still maps to at most one entry.
CREATE TABLE IF NOT EXISTS journal_entries (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id       INT UNSIGNED  NOT NULL,
  entry_no      VARCHAR(40)   NOT NULL,
  entry_date    DATE          NOT NULL,
  memo          VARCHAR(255)  NULL,
  source_type   VARCHAR(30)   NULL,
  source_id     INT UNSIGNED  NULL,
  is_system     TINYINT(1)    NOT NULL DEFAULT 0,  -- 1 = auto-posted (read-only in the UI); 0 = a hand-keyed manual entry
  created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_je_source (farm_id, source_type, source_id),
  KEY idx_je_farm (farm_id, entry_date),
  CONSTRAINT fk_je_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- One debit-or-credit posting against one account. Every line carries exactly
-- one non-zero side; the entry as a whole must balance (enforced in Ledger::post).
CREATE TABLE IF NOT EXISTS journal_entry_lines (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  entry_id      INT UNSIGNED  NOT NULL,
  account_id    INT UNSIGNED  NOT NULL,
  debit         DECIMAL(14,2) NOT NULL DEFAULT 0,
  credit        DECIMAL(14,2) NOT NULL DEFAULT 0,
  memo          VARCHAR(255)  NULL,
  sort_order    INT UNSIGNED  NOT NULL DEFAULT 0,
  KEY idx_jel_entry (entry_id),
  KEY idx_jel_account (account_id),
  CONSTRAINT fk_jel_entry FOREIGN KEY (entry_id) REFERENCES journal_entries(id) ON DELETE CASCADE,
  CONSTRAINT fk_jel_account FOREIGN KEY (account_id) REFERENCES chart_of_accounts(id)
) ENGINE=InnoDB;

-- Budgets — an annual planned amount per income/expense account, compared
-- against ledger actuals in the Budgets screen. One row per (account, year).
-- See migration-2026-07-budgets.sql.
CREATE TABLE IF NOT EXISTS budgets (
  id          INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id     INT UNSIGNED  NOT NULL,
  account_id  INT UNSIGNED  NOT NULL,
  fiscal_year SMALLINT UNSIGNED NOT NULL,
  amount      DECIMAL(14,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_budget (farm_id, account_id, fiscal_year),
  KEY idx_budget_farm_year (farm_id, fiscal_year),
  CONSTRAINT fk_budget_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE,
  CONSTRAINT fk_budget_account FOREIGN KEY (account_id) REFERENCES chart_of_accounts(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Payments received against a credit/partial SALE after it was first invoiced —
-- the receipt history that keeps Accounts Receivable honest over time. Each row
-- also auto-posts Dr Cash/Bank, Cr Accounts Receivable.
CREATE TABLE IF NOT EXISTS sale_payments (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id       INT UNSIGNED  NOT NULL,
  sale_id       INT UNSIGNED  NOT NULL,
  amount        DECIMAL(14,2) NOT NULL DEFAULT 0,
  method        VARCHAR(40)   NOT NULL DEFAULT 'Cash',
  paid_on       DATE          NOT NULL,
  note          VARCHAR(255)  NULL,
  created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_sp_farm (farm_id),
  KEY idx_sp_sale (sale_id),
  CONSTRAINT fk_sp_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE,
  CONSTRAINT fk_sp_sale FOREIGN KEY (sale_id) REFERENCES sales(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Mirror of sale_payments for settling a PENDING expense later (Dr Accounts
-- Payable, Cr Cash/Bank).
CREATE TABLE IF NOT EXISTS expense_payments (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id       INT UNSIGNED  NOT NULL,
  expense_id    INT UNSIGNED  NOT NULL,
  amount        DECIMAL(14,2) NOT NULL DEFAULT 0,
  method        VARCHAR(40)   NOT NULL DEFAULT 'Cash',
  paid_on       DATE          NOT NULL,
  note          VARCHAR(255)  NULL,
  created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_ep_farm (farm_id),
  KEY idx_ep_expense (expense_id),
  CONSTRAINT fk_ep_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE,
  CONSTRAINT fk_ep_expense FOREIGN KEY (expense_id) REFERENCES expenses(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ===========================================================================
-- Billing & subscriptions (platform-level SaaS layer, managed by an operator
-- from the Platform admin dashboard). A farm is put on a `plan` (the pricing
-- catalogue), which seeds a `subscription` holding that farm's EFFECTIVE terms
-- — every value overridable per farm. The subscription drives the trial →
-- active → past_due → suspended lifecycle (a suspended subscription flips
-- farms.status = 'suspended', which blocks login). SMS is billed either PREPAID
-- (subscriptions.sms_balance credits, decremented per send in App\Core\Sms and
-- blocked at zero) or POSTPAID (usage counted from sms_log, invoiced at each
-- renewal). Payments are recorded by hand (no gateway). See App\Core\Billing.
-- ===========================================================================
CREATE TABLE IF NOT EXISTS plans (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  name          VARCHAR(80)   NOT NULL,
  description   VARCHAR(255)  NULL,
  price         DECIMAL(12,2) NOT NULL DEFAULT 0,      -- subscription price per cycle
  currency      CHAR(3)       NOT NULL DEFAULT 'UGX',
  billing_cycle ENUM('monthly','quarterly','annual') NOT NULL DEFAULT 'monthly',
  trial_days    INT UNSIGNED  NOT NULL DEFAULT 0,
  max_birds     INT UNSIGNED  NULL,                    -- NULL = unlimited
  max_users     INT UNSIGNED  NULL,                    -- NULL = unlimited
  sms_mode      ENUM('prepaid','postpaid') NOT NULL DEFAULT 'postpaid',
  sms_rate      DECIMAL(10,2) NOT NULL DEFAULT 0,      -- price charged to the farm per SMS
  sms_included  INT UNSIGNED  NOT NULL DEFAULT 0,      -- free SMS allowance per cycle (postpaid)
  modules       JSON          NULL,                    -- default app modules for farms on this plan; NULL = all
  is_active     TINYINT(1)    NOT NULL DEFAULT 1,
  created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS subscriptions (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id       INT UNSIGNED  NOT NULL,
  plan_id       INT UNSIGNED  NULL,                    -- NULL once fully custom / plan deleted
  status        ENUM('trial','active','past_due','suspended','cancelled') NOT NULL DEFAULT 'trial',
  price         DECIMAL(12,2) NOT NULL DEFAULT 0,      -- effective price (override of plan)
  currency      CHAR(3)       NOT NULL DEFAULT 'UGX',
  billing_cycle ENUM('monthly','quarterly','annual') NOT NULL DEFAULT 'monthly',
  trial_ends_on DATE          NULL,
  period_start  DATE          NULL,
  period_end    DATE          NULL,                    -- next due / renewal date
  max_birds     INT UNSIGNED  NULL,
  max_users     INT UNSIGNED  NULL,
  sms_mode      ENUM('prepaid','postpaid') NOT NULL DEFAULT 'postpaid',
  sms_rate      DECIMAL(10,2) NOT NULL DEFAULT 0,
  sms_included  INT UNSIGNED  NOT NULL DEFAULT 0,
  sms_balance   INT           NOT NULL DEFAULT 0,      -- prepaid credit balance (SMS count); signed to tolerate a race
  notes         VARCHAR(255)  NULL,
  created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_sub_farm (farm_id),
  KEY idx_sub_plan (plan_id),
  CONSTRAINT fk_sub_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE,
  CONSTRAINT fk_sub_plan FOREIGN KEY (plan_id) REFERENCES plans(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS billing_invoices (
  id            INT UNSIGNED  PRIMARY KEY AUTO_INCREMENT,
  farm_id       INT UNSIGNED  NOT NULL,
  invoice_no    VARCHAR(40)   NOT NULL,
  type          ENUM('subscription','sms','manual') NOT NULL DEFAULT 'subscription',
  description   VARCHAR(255)  NULL,
  period_start  DATE          NULL,
  period_end    DATE          NULL,
  amount        DECIMAL(12,2) NOT NULL DEFAULT 0,
  currency      CHAR(3)       NOT NULL DEFAULT 'UGX',
  status        ENUM('unpaid','paid','void') NOT NULL DEFAULT 'unpaid',
  issued_on     DATE          NOT NULL,
  due_on        DATE          NULL,
  paid_on       DATE          NULL,
  -- once paid, the id of the matching row created in the farm's `expenses` table
  -- (so the charge shows in Expenses + Trial Balance). No FK: a dangling id after
  -- a manual expense delete is fine and stops the sync resurrecting it.
  expense_id    INT UNSIGNED  NULL,
  created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_binv (farm_id, invoice_no),
  KEY idx_binv_status (farm_id, status),
  CONSTRAINT fk_binv_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS billing_payments (
  id            INT UNSIGNED  PRIMARY KEY AUTO_INCREMENT,
  farm_id       INT UNSIGNED  NOT NULL,
  invoice_id    INT UNSIGNED  NULL,
  amount        DECIMAL(12,2) NOT NULL DEFAULT 0,
  method        VARCHAR(40)   NULL,
  reference     VARCHAR(80)   NULL,
  paid_on       DATE          NOT NULL,
  note          VARCHAR(255)  NULL,
  recorded_by   INT UNSIGNED  NULL,
  created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_bpay_farm (farm_id, paid_on),
  CONSTRAINT fk_bpay_farm    FOREIGN KEY (farm_id)    REFERENCES farms(id)            ON DELETE CASCADE,
  CONSTRAINT fk_bpay_invoice FOREIGN KEY (invoice_id) REFERENCES billing_invoices(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS sms_credit_ledger (
  id            INT UNSIGNED  PRIMARY KEY AUTO_INCREMENT,
  farm_id       INT UNSIGNED  NOT NULL,
  delta         INT           NOT NULL,                -- +top-up / -usage / +/- adjustment
  balance_after INT           NOT NULL,
  reason        VARCHAR(30)   NOT NULL,                -- 'topup' | 'usage' | 'adjustment'
  sms_log_id    INT UNSIGNED  NULL,
  note          VARCHAR(255)  NULL,
  created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_smscredit_farm (farm_id, created_at),
  CONSTRAINT fk_smscredit_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ===========================================================================
-- Support live chat — one ongoing conversation per farm. Farm users chat from a
-- floating widget; the platform operator answers every farm's thread from the
-- Platform → Support dashboard. "Live" = short-interval polling (no WebSockets
-- on this stack). Unread is derived from each side's last-read message id.
-- ===========================================================================
CREATE TABLE IF NOT EXISTS support_threads (
  id                 INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id            INT UNSIGNED NOT NULL,
  status             ENUM('open','closed') NOT NULL DEFAULT 'open',
  last_message_at    TIMESTAMP    NULL,
  admin_last_read_id INT UNSIGNED NOT NULL DEFAULT 0,
  farm_last_read_id  INT UNSIGNED 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_support_farm (farm_id),
  CONSTRAINT fk_support_thread_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS support_messages (
  id            INT UNSIGNED  PRIMARY KEY AUTO_INCREMENT,
  thread_id     INT UNSIGNED  NOT NULL,
  farm_id       INT UNSIGNED  NOT NULL,
  sender        ENUM('farm','admin') NOT NULL,
  user_id       INT UNSIGNED  NULL,
  user_name     VARCHAR(120)  NULL,
  body          VARCHAR(2000) NOT NULL,
  created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_support_msg_thread (thread_id, id),
  KEY idx_support_msg_farm (farm_id, id),
  CONSTRAINT fk_support_msg_thread FOREIGN KEY (thread_id) REFERENCES support_threads(id) ON DELETE CASCADE,
  CONSTRAINT fk_support_msg_farm   FOREIGN KEY (farm_id)   REFERENCES farms(id)            ON DELETE CASCADE
) ENGINE=InnoDB;

-- ===========================================================================
-- Online mobile-money payments (Yo! Uganda) — a farm pays its subscription
-- invoices / buys prepaid SMS credit from its own dashboard. Each row is a
-- payment intent (pending → succeeded/failed); `applied` guards the money
-- effect from running twice. See App\Core\YoPayments + App\Controllers\PaymentController.
-- ===========================================================================
-- Scheduled-job run log — each cron/HTTP job records a row when it runs; the
-- admin System page reads the latest per job to flag a stale/failed schedule.
CREATE TABLE IF NOT EXISTS cron_runs (
  id          INT UNSIGNED  PRIMARY KEY AUTO_INCREMENT,
  job         VARCHAR(50)   NOT NULL,
  status      ENUM('ok','error') NOT NULL DEFAULT 'ok',
  detail      VARCHAR(255)  NULL,
  duration_ms INT UNSIGNED  NULL,
  ran_at      TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_cron_job (job, id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS payments (
  id            INT UNSIGNED  PRIMARY KEY AUTO_INCREMENT,
  farm_id       INT UNSIGNED  NOT NULL,
  kind          ENUM('invoice','sms') NOT NULL,
  invoice_id    INT UNSIGNED  NULL,
  credits       INT UNSIGNED  NULL,
  amount        DECIMAL(12,2) NOT NULL,
  currency      CHAR(3)       NOT NULL DEFAULT 'UGX',
  phone         VARCHAR(30)   NOT NULL,
  external_ref  VARCHAR(40)   NOT NULL,
  provider_ref  VARCHAR(64)   NULL,
  status        ENUM('pending','succeeded','failed') NOT NULL DEFAULT 'pending',
  applied       TINYINT(1)    NOT NULL DEFAULT 0,
  message       VARCHAR(255)  NULL,
  created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_payment_ref (external_ref),
  KEY idx_payment_farm (farm_id, created_at),
  KEY idx_payment_invoice (invoice_id),
  CONSTRAINT fk_payment_farm    FOREIGN KEY (farm_id)    REFERENCES farms(id)            ON DELETE CASCADE,
  CONSTRAINT fk_payment_invoice FOREIGN KEY (invoice_id) REFERENCES billing_invoices(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- WebAuthn / passkey sign-in — an optional biometric layer on top of the
-- email+password login (see migration-2026-07-webauthn.sql). A device holds
-- the private key; the server stores only the public key. Password login is
-- unchanged and stays the fallback.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS webauthn_credentials (
  id            INT UNSIGNED  PRIMARY KEY AUTO_INCREMENT,
  user_id       INT UNSIGNED  NOT NULL,
  credential_id VARCHAR(512)  NOT NULL,
  public_key    TEXT          NOT NULL,
  sign_count    INT UNSIGNED  NOT NULL DEFAULT 0,
  label         VARCHAR(120)  NULL,
  transports    VARCHAR(191)  NULL,
  created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_used_at  TIMESTAMP     NULL,
  UNIQUE KEY uq_webauthn_cred (credential_id(191)),
  KEY idx_webauthn_user (user_id),
  CONSTRAINT fk_webauthn_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS webauthn_challenges (
  id          INT UNSIGNED  PRIMARY KEY AUTO_INCREMENT,
  handle      CHAR(64)      NOT NULL,
  challenge   VARCHAR(255)  NOT NULL,
  purpose     ENUM('register','login') NOT NULL,
  user_id     INT UNSIGNED  NULL,
  expires_at  TIMESTAMP     NOT NULL,
  created_at  TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_webauthn_handle (handle),
  KEY idx_webauthn_expires (expires_at)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- Platform key/value settings the super admin edits from the dashboard (no
-- redeploy). First use: the SMTP config for outgoing email, under key 'smtp'.
-- See migration-2026-07-email.sql.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS app_settings (
  name       VARCHAR(64) PRIMARY KEY,
  value      TEXT        NULL,
  updated_at TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- Two-factor auth (authenticator app / TOTP) — see migration-2026-07-2fa.sql.
-- Recovery codes are one-time bypass codes; the challenges table bridges the
-- two steps of a 2FA login (password step -> code step).
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS totp_recovery_codes (
  id         INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  user_id    INT UNSIGNED NOT NULL,
  code_hash  VARCHAR(255) NOT NULL,
  used_at    TIMESTAMP    NULL,
  created_at TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_totp_recovery_user (user_id),
  CONSTRAINT fk_totp_recovery_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS two_factor_challenges (
  id         INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  token      CHAR(64)     NOT NULL,
  user_id    INT UNSIGNED NOT NULL,
  attempts   TINYINT UNSIGNED NOT NULL DEFAULT 0,
  expires_at TIMESTAMP    NOT NULL,
  created_at TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_2fa_token (token),
  KEY idx_2fa_expires (expires_at),
  CONSTRAINT fk_2fa_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Personal API tokens — long-lived bearer tokens (prefix 'pfmis_pat_') a user
-- creates to call the API as themselves. Only the sha256 hash is stored. See
-- migration-2026-07-api-tokens.sql.
CREATE TABLE IF NOT EXISTS api_tokens (
  id           INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  user_id      INT UNSIGNED  NOT NULL,
  name         VARCHAR(120)  NOT NULL,
  token_hash   CHAR(64)      NOT NULL,
  prefix       VARCHAR(20)   NOT NULL,
  last_used_at TIMESTAMP     NULL,
  expires_at   TIMESTAMP     NULL,
  created_at   TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_apitoken_hash (token_hash),
  KEY idx_apitoken_user (user_id),
  CONSTRAINT fk_apitoken_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Access requests — public "request access" submissions a platform admin
-- reviews and provisions. See migration-2026-07-access-requests.sql.
CREATE TABLE IF NOT EXISTS access_requests (
  id           INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_name    VARCHAR(160) NOT NULL,
  contact_name VARCHAR(120) NULL,
  email        VARCHAR(160) NOT NULL,
  phone        VARCHAR(40)  NULL,
  note         VARCHAR(500) NULL,
  status       ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  farm_id      INT UNSIGNED NULL,
  reviewed_by  VARCHAR(160) NULL,
  reviewed_at  TIMESTAMP    NULL,
  created_at   TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_accreq_status (status, created_at),
  CONSTRAINT fk_accreq_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Short-lived anti-CSRF state values for the Google OAuth login flow.
-- See migration-2026-07-google-sso.sql.
CREATE TABLE IF NOT EXISTS oauth_states (
  state      CHAR(64)  PRIMARY KEY,
  expires_at TIMESTAMP NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_oauth_expires (expires_at)
) ENGINE=InnoDB;

-- One-time login codes (email/SMS OTP sign-in). See migration-2026-07-otp-login.sql.
CREATE TABLE IF NOT EXISTS login_otps (
  id         INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  user_id    INT UNSIGNED NOT NULL,
  code_hash  CHAR(64)     NOT NULL,
  channel    ENUM('email','sms') NOT NULL DEFAULT 'email',
  attempts   TINYINT UNSIGNED NOT NULL DEFAULT 0,
  expires_at TIMESTAMP    NOT NULL,
  created_at TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_otp_user (user_id),
  KEY idx_otp_expires (expires_at),
  CONSTRAINT fk_otp_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- Cameras — a farm's CCTV feeds viewable inside PFMIS (see
-- migration-2026-07-cameras.sql). provider 'hikconnect' fetches a browser-
-- playable HLS URL + snapshot server-side via the platform's Hik-Connect Open
-- Platform account; 'manual' uses directly-pasted HLS/snapshot URLs.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS cameras (
  id            INT UNSIGNED  PRIMARY KEY AUTO_INCREMENT,
  farm_id       INT UNSIGNED  NOT NULL,
  name          VARCHAR(120)  NOT NULL,
  house         VARCHAR(60)   NULL,
  provider      ENUM('hikconnect','manual') NOT NULL DEFAULT 'hikconnect',
  device_serial VARCHAR(64)   NULL,
  channel_no    INT UNSIGNED  NOT NULL DEFAULT 1,
  verify_code   VARCHAR(64)   NULL,
  stream_url    VARCHAR(500)  NULL,
  snapshot_url  VARCHAR(500)  NULL,
  status        ENUM('active','inactive') NOT NULL DEFAULT 'active',
  sort_order    INT           NOT NULL DEFAULT 0,
  created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_cameras_farm (farm_id, sort_order),
  CONSTRAINT fk_cameras_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS hik_token (
  id           TINYINT UNSIGNED PRIMARY KEY DEFAULT 1,
  access_token VARCHAR(255)  NOT NULL,
  area_domain  VARCHAR(191)  NULL,
  expires_at   TIMESTAMP     NOT NULL,
  updated_at   TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- Fleet — a farm's vehicles and their running (see migration-2026-07-fleet.sql):
-- the vehicle register, drivers, trips (mileage), fuel logs and maintenance.
-- 'fleet' is a normal toggleable farm module.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS vehicles (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL,
  reg_no VARCHAR(30) NOT NULL, label VARCHAR(120) NULL, type VARCHAR(40) NULL,
  make VARCHAR(60) NULL, model VARCHAR(60) NULL, year_made SMALLINT UNSIGNED NULL,
  capacity VARCHAR(60) NULL, fuel_type VARCHAR(30) NULL, odometer INT UNSIGNED NOT NULL DEFAULT 0,
  gps_device_id VARCHAR(80) NULL, last_lat DECIMAL(10,6) NULL, last_lng DECIMAL(10,6) NULL, last_seen TIMESTAMP NULL,
  status ENUM('active','maintenance','inactive') NOT NULL DEFAULT 'active', acquired_on DATE NULL, notes VARCHAR(500) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_vehicle_reg (farm_id, reg_no), KEY idx_vehicle_farm (farm_id),
  CONSTRAINT fk_vehicle_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS fleet_drivers (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL,
  name VARCHAR(120) NOT NULL, phone VARCHAR(30) NULL, license_no VARCHAR(60) NULL, license_expiry DATE NULL,
  employee_id INT UNSIGNED NULL, status ENUM('active','inactive') NOT NULL DEFAULT 'active', 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_driver_farm (farm_id),
  CONSTRAINT fk_driver_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE,
  CONSTRAINT fk_driver_emp FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS fleet_trips (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, vehicle_id INT UNSIGNED NOT NULL, driver_id INT UNSIGNED NULL,
  trip_date DATE NOT NULL, origin VARCHAR(120) NULL, destination VARCHAR(120) NULL, purpose VARCHAR(160) NULL,
  start_odometer INT UNSIGNED NULL, end_odometer INT UNSIGNED NULL, distance_km DECIMAL(10,1) NULL,
  cost DECIMAL(12,2) NOT NULL DEFAULT 0, customer VARCHAR(120) NULL,
  status ENUM('planned','completed','cancelled') NOT NULL DEFAULT 'completed', 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_trip_farm (farm_id, trip_date), KEY idx_trip_vehicle (vehicle_id),
  CONSTRAINT fk_trip_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE,
  CONSTRAINT fk_trip_vehicle FOREIGN KEY (vehicle_id) REFERENCES vehicles(id) ON DELETE CASCADE,
  CONSTRAINT fk_trip_driver FOREIGN KEY (driver_id) REFERENCES fleet_drivers(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS fleet_fuel (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, vehicle_id INT UNSIGNED NOT NULL, driver_id INT UNSIGNED NULL,
  fuel_date DATE NOT NULL, litres DECIMAL(10,2) NOT NULL DEFAULT 0, unit_cost DECIMAL(12,2) NOT NULL DEFAULT 0, total_cost DECIMAL(12,2) NOT NULL DEFAULT 0,
  odometer INT UNSIGNED NULL, station VARCHAR(120) NULL, 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_fuel_farm (farm_id, fuel_date), KEY idx_fuel_vehicle (vehicle_id),
  CONSTRAINT fk_fuel_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE,
  CONSTRAINT fk_fuel_vehicle FOREIGN KEY (vehicle_id) REFERENCES vehicles(id) ON DELETE CASCADE,
  CONSTRAINT fk_fuel_driver FOREIGN KEY (driver_id) REFERENCES fleet_drivers(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS fleet_maintenance (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, vehicle_id INT UNSIGNED NOT NULL,
  service_date DATE NOT NULL, service_type VARCHAR(40) NULL, description VARCHAR(300) NULL, cost DECIMAL(12,2) NOT NULL DEFAULT 0,
  odometer INT UNSIGNED NULL, next_service_date DATE NULL, provider VARCHAR(120) NULL,
  status ENUM('scheduled','done') NOT NULL DEFAULT 'done', 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_maint_farm (farm_id, service_date), KEY idx_maint_vehicle (vehicle_id), KEY idx_maint_next (farm_id, next_service_date),
  CONSTRAINT fk_maint_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE,
  CONSTRAINT fk_maint_vehicle FOREIGN KEY (vehicle_id) REFERENCES vehicles(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- Procurement — purchase requests, RFQs, purchase orders, goods received (GRN)
-- and goods issued (GIN). See migration-2026-07-procurement.sql. Reuses the
-- suppliers directory. Standalone (no stock/ledger posting yet). 'procurement'
-- is a normal toggleable farm module.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS purchase_requests (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, ref_no VARCHAR(20) NOT NULL,
  request_date DATE NOT NULL, needed_by DATE NULL, requested_by VARCHAR(120) NULL, department VARCHAR(80) NULL,
  priority ENUM('low','normal','high') NOT NULL DEFAULT 'normal',
  status ENUM('draft','submitted','approved','rejected','ordered') NOT NULL DEFAULT 'draft',
  approver VARCHAR(120) NULL, approved_on DATE NULL, decision_note VARCHAR(300) NULL, notes VARCHAR(500) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_pr_ref (farm_id, ref_no), KEY idx_pr_farm (farm_id, request_date),
  CONSTRAINT fk_pr_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS purchase_request_items (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, pr_id INT UNSIGNED NOT NULL, description VARCHAR(200) NOT NULL,
  qty DECIMAL(12,2) NOT NULL DEFAULT 0, unit VARCHAR(30) NULL, est_unit_cost DECIMAL(12,2) NOT NULL DEFAULT 0,
  KEY idx_pri_pr (pr_id), CONSTRAINT fk_pri_pr FOREIGN KEY (pr_id) REFERENCES purchase_requests(id) ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS rfqs (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, ref_no VARCHAR(20) NOT NULL,
  rfq_date DATE NOT NULL, supplier_id INT UNSIGNED NULL, status ENUM('draft','sent','quoted','closed') NOT NULL DEFAULT 'draft',
  valid_until DATE NULL, notes VARCHAR(500) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_rfq_ref (farm_id, ref_no), KEY idx_rfq_farm (farm_id, rfq_date),
  CONSTRAINT fk_rfq_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE,
  CONSTRAINT fk_rfq_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers(id) ON DELETE SET NULL
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS rfq_items (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, rfq_id INT UNSIGNED NOT NULL, description VARCHAR(200) NOT NULL,
  qty DECIMAL(12,2) NOT NULL DEFAULT 0, unit VARCHAR(30) NULL, quoted_unit_price DECIMAL(12,2) NULL,
  KEY idx_rfqi_rfq (rfq_id), CONSTRAINT fk_rfqi_rfq FOREIGN KEY (rfq_id) REFERENCES rfqs(id) ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS purchase_orders (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, ref_no VARCHAR(20) NOT NULL,
  supplier_id INT UNSIGNED NULL, pr_id INT UNSIGNED NULL, order_date DATE NOT NULL, expected_date DATE NULL,
  status ENUM('draft','approved','sent','partial','received','cancelled') NOT NULL DEFAULT 'draft',
  currency CHAR(3) NOT NULL DEFAULT 'UGX', amount DECIMAL(14,2) NOT NULL DEFAULT 0,
  approver VARCHAR(120) NULL, approved_on DATE NULL, received_on DATE NULL, notes VARCHAR(500) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_po_ref (farm_id, ref_no), KEY idx_po_farm (farm_id, order_date), KEY idx_po_supplier (supplier_id),
  CONSTRAINT fk_po_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE,
  CONSTRAINT fk_po_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers(id) ON DELETE SET NULL,
  CONSTRAINT fk_po_pr FOREIGN KEY (pr_id) REFERENCES purchase_requests(id) ON DELETE SET NULL
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS purchase_order_items (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, po_id INT UNSIGNED NOT NULL, description VARCHAR(200) NOT NULL,
  qty DECIMAL(12,2) NOT NULL DEFAULT 0, unit VARCHAR(30) NULL,
  stock_type ENUM('feed','inventory') NULL, stock_ref VARCHAR(120) NULL,
  unit_price DECIMAL(12,2) NOT NULL DEFAULT 0,
  line_total DECIMAL(14,2) NOT NULL DEFAULT 0, qty_received DECIMAL(12,2) NOT NULL DEFAULT 0,
  KEY idx_poi_po (po_id), CONSTRAINT fk_poi_po FOREIGN KEY (po_id) REFERENCES purchase_orders(id) ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS goods_received (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, ref_no VARCHAR(20) NOT NULL,
  po_id INT UNSIGNED NULL, supplier_id INT UNSIGNED NULL, received_date DATE NOT NULL, received_by VARCHAR(120) NULL,
  rating TINYINT UNSIGNED NULL, status ENUM('partial','complete') NOT NULL DEFAULT 'complete', notes VARCHAR(500) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_grn_ref (farm_id, ref_no), KEY idx_grn_farm (farm_id, received_date), KEY idx_grn_supplier (supplier_id),
  CONSTRAINT fk_grn_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE,
  CONSTRAINT fk_grn_po FOREIGN KEY (po_id) REFERENCES purchase_orders(id) ON DELETE SET NULL,
  CONSTRAINT fk_grn_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers(id) ON DELETE SET NULL
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS goods_received_items (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, grn_id INT UNSIGNED NOT NULL, po_item_id INT UNSIGNED NULL,
  description VARCHAR(200) NOT NULL, qty DECIMAL(12,2) NOT NULL DEFAULT 0, unit VARCHAR(30) NULL,
  KEY idx_grni_grn (grn_id),
  CONSTRAINT fk_grni_grn FOREIGN KEY (grn_id) REFERENCES goods_received(id) ON DELETE CASCADE,
  CONSTRAINT fk_grni_poi FOREIGN KEY (po_item_id) REFERENCES purchase_order_items(id) ON DELETE SET NULL
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS goods_issued (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, ref_no VARCHAR(20) NOT NULL,
  issue_date DATE NOT NULL, issued_to VARCHAR(120) NULL, department VARCHAR(80) NULL, purpose VARCHAR(200) NULL, notes VARCHAR(500) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_gin_ref (farm_id, ref_no), KEY idx_gin_farm (farm_id, issue_date),
  CONSTRAINT fk_gin_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS goods_issued_items (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, gin_id INT UNSIGNED NOT NULL, description VARCHAR(200) NOT NULL,
  qty DECIMAL(12,2) NOT NULL DEFAULT 0, unit VARCHAR(30) NULL,
  KEY idx_gini_gin (gin_id), CONSTRAINT fk_gini_gin FOREIGN KEY (gin_id) REFERENCES goods_issued(id) ON DELETE CASCADE
) ENGINE=InnoDB;
