-- ===========================================================================
-- Migration: Fleet module (vehicles, drivers, trips, fuel, maintenance)
-- Date: 2026-07-29
--
-- Manages a farm's vehicles and their running: the vehicle register, the
-- drivers, trips (with mileage), fuel logs and maintenance/service records.
-- 'fleet' is a normal toggleable farm module, gated by the role system like
-- every other module. GPS is stored "ready" (a tracker id + last known point)
-- rather than a live integration. Idempotent.
-- ===========================================================================

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,            -- number plate
  label         VARCHAR(120)  NULL,                -- friendly name ("Delivery pickup")
  type          VARCHAR(40)   NULL,                -- pickup / truck / van / motorcycle / tractor …
  make          VARCHAR(60)   NULL,
  model         VARCHAR(60)   NULL,
  year_made     SMALLINT UNSIGNED NULL,
  capacity      VARCHAR(60)   NULL,                -- free text (e.g. "2 tonnes", "3 seats")
  fuel_type     VARCHAR(30)   NULL,                -- petrol / diesel / electric …
  odometer      INT UNSIGNED  NOT NULL DEFAULT 0,  -- current reading (km)
  gps_device_id VARCHAR(80)   NULL,                -- tracker id, if any (GPS-ready)
  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,                -- optional link to an HR record
  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,  -- tolls, allowances, etc.
  customer       VARCHAR(120) NULL,                 -- optional delivery reference
  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,             -- service / repair / inspection / tyres …
  description       VARCHAR(300) NULL,
  cost              DECIMAL(12,2) NOT NULL DEFAULT 0,
  odometer          INT UNSIGNED NULL,
  next_service_date DATE         NULL,             -- for the "due soon" reminder
  provider          VARCHAR(120) NULL,             -- garage / mechanic
  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;