-- ===========================================================================
-- Migration: Equipment & maintenance
-- Date: 2026-07-30
--
-- A register of individual durable machines (incubators, generators, pumps,
-- feeders…) and keeping them running — separate from Inventory, which tracks
-- consumable stock. Covers:
--   * equipment           — the asset register (status, purchase, depreciation)
--   * equipment_maintenance — service/breakdown records; a completed one with a
--                           cost posts a Maintenance expense to the ledger
--                           (Dr Maintenance, Cr Cash), exactly like Fleet.
--   * equipment_pm        — preventive-maintenance schedules with a next-due date.
-- Idempotent.
-- ===========================================================================

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,        -- %/year (alt to useful life)
  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;