-- ═══════════════════════════════════════════════════════════════════════════
-- rsflow.dspera · Customer Delivery Notes (DN)
-- The final physical step: goods leave our warehouse and are handed to the
-- customer. Legal / TRA-compliance requirement in Tanzania — every physical
-- delivery must have a signed DN. Roll-ups cascade:
--   goods_customer_delivery_items.qty_delivered → goods_client_po_items.qty_delivered
--   When Σqty_delivered ≥ Σqty on a Client PO → goods_client_pos.status = 'CLOSED'
-- Business rule: qty_delivered ≤ (qty_received − already_delivered)  — we can
-- only deliver what we have in the warehouse (per line).
-- ═══════════════════════════════════════════════════════════════════════════

DROP TABLE IF EXISTS goods_customer_delivery_items;
DROP TABLE IF EXISTS goods_customer_deliveries;

CREATE TABLE goods_customer_deliveries (
  c0d                    INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  dn_no                  VARCHAR(30) NOT NULL,
  client_po_id           INT NOT NULL,
  project_id             INT NOT NULL DEFAULT 0,
  customer_id            INT NOT NULL DEFAULT 0,
  customer_name          VARCHAR(200) NOT NULL DEFAULT '',
  delivery_date          DATE NOT NULL,
  delivered_to           VARCHAR(160) NOT NULL DEFAULT '',   -- customer POC who signed
  delivered_to_phone     VARCHAR(40)  NOT NULL DEFAULT '',
  delivered_to_signature VARCHAR(120) NOT NULL DEFAULT '',   -- signature ref / photo ref
  delivered_by           INT NOT NULL DEFAULT 0,             -- our person who delivered
  delivered_by_name      VARCHAR(120) NOT NULL DEFAULT '',
  condition_ok           TINYINT(1) NOT NULL DEFAULT 1,
  notes                  TEXT,
  status                 VARCHAR(20) NOT NULL DEFAULT 'POSTED', -- POSTED|REVERSED|VOIDED
  created_by             INT NOT NULL DEFAULT 0,
  created_on             TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  edited_by              INT NOT NULL DEFAULT 0,
  edited_on              TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted                TINYINT(1) NOT NULL DEFAULT 0,
  UNIQUE KEY uq_dn_no (dn_no),
  KEY ix_client_po (client_po_id),
  KEY ix_customer (customer_id),
  KEY ix_project (project_id),
  KEY ix_delivery_date (delivery_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE goods_customer_delivery_items (
  c0d               INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  dn_id             INT NOT NULL,
  client_po_item_id INT NOT NULL,
  qty_delivered     DECIMAL(20,4) NOT NULL DEFAULT 0.0000,
  condition_notes   TEXT,
  created_on        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY ix_dn (dn_id),
  KEY ix_client_po_item (client_po_item_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
