-- ---------------------------------------------------------------------------
-- Migration: vaccination programs, inventory depreciation/losses, customer
-- directory, expenses items column
-- ---------------------------------------------------------------------------
-- schema.sql already carries these for FRESH installs. Run this ONCE against
-- an EXISTING database (paste into phpMyAdmin's SQL tab, or
-- `mysql -u <user> -p <db> < sql/migration-2026-07-vax-inventory-customers.sql`).
-- Nothing here touches existing rows beyond adding nullable/defaulted columns.
-- ---------------------------------------------------------------------------

-- Vaccination programs (reusable templates — see schema.sql for how "apply
-- to a flock" works; these tables only hold the templates themselves)
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,
  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: optional straight-line depreciation fields
ALTER TABLE inventory_items
  ADD COLUMN purchased_on       DATE         NULL AFTER unit_cost,
  ADD COLUMN useful_life_months INT UNSIGNED NULL AFTER purchased_on;

-- Inventory: losses/write-offs against a specific item
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,
  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;

-- Customer directory (optional — sales still store name/phone/address as
-- plain text on the invoice, never a foreign key to this table)
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,
  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;

-- Expenses: what was actually paid for
ALTER TABLE expenses
  ADD COLUMN items VARCHAR(255) NULL AFTER payee;

-- Refresh the view so it carries the new `items` column through to Expenses'
-- auto-merged payroll/feed-purchase rows too
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, NULL AS items,
         net AS amount, 'Bank transfer' AS method, 'paid' AS status,
         run_on AS spent_on, 'payroll' AS source
    FROM payroll_runs
  UNION ALL
  SELECT NULL AS id, farm_id, 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, 'Cash' AS method, 'paid' AS status,
         purchased_on AS spent_on, 'feed' AS source
    FROM feed_purchases;