-- ===========================================================================
-- Migration: Delivery notes
-- Date: 2026-07-29
--
-- A delivery note accompanies goods leaving the farm. It can be raised against
-- a sale (copying its customer + items) or stand alone (ad-hoc delivery with no
-- invoice). It lists items and quantities only — never prices — records who it's
-- going to and when, an optional Fleet vehicle/driver, and a pending -> delivered
-- status with a signature line. Sits under the Sales permission. Idempotent.
--
--   delivery_notes       — one header per delivery.
--   delivery_note_items  — the items/quantities being delivered.
-- ===========================================================================

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,            -- DN-0001, sequential per farm
  -- the sale this delivers, if any (NULL for a standalone/ad-hoc delivery).
  -- SET NULL on delete so removing a sale doesn't wipe its delivery record.
  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,
  -- optional Fleet links (both SET NULL if the vehicle/driver is later removed)
  vehicle_id      INT UNSIGNED  NULL,
  driver_id       INT UNSIGNED  NULL,
  status          ENUM('pending','delivered','cancelled') NOT NULL DEFAULT 'pending',
  received_by     VARCHAR(120)  NULL,                -- who signed for it on delivery
  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;