-- ===========================================================================
-- Migration: Procurement module
-- Date: 2026-07-29
--
-- The buying workflow: purchase requests (with approval), RFQs (supplier
-- quotes), purchase orders (with approval + receiving status), goods received
-- notes (GRN, against a PO) and goods issue notes (GIN). Supplier performance
-- is derived from these. Reuses the existing `suppliers` directory. 'procurement'
-- is a normal toggleable farm module. Standalone for now — it does NOT post to
-- stock or the ledger (a later integration). Every header has line items in a
-- child table (ON DELETE CASCADE). Idempotent.
-- ===========================================================================

-- ---- Purchase Requests ----------------------------------------------------
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;

-- ---- RFQs (request for quotation) -----------------------------------------
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;

-- ---- Purchase Orders ------------------------------------------------------
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,
  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;

-- ---- Goods Received Notes (GRN) -------------------------------------------
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;

-- ---- Goods Issue Notes (GIN) ----------------------------------------------
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;