-- ═══════════════════════════════════════════════════════════════════════════
-- rsflow.dspera · Goods · Client Purchase Orders (customer contracts)
-- The client-facing spine — captures what the customer contracted for at
-- what price. Invoices are drafted FROM Client POs; supplier LPOs are
-- sourced against them (future slice). Margin = sell − cog per line.
-- ═══════════════════════════════════════════════════════════════════════════

DROP TABLE IF EXISTS goods_client_po_items;
DROP TABLE IF EXISTS goods_client_pos;

CREATE TABLE goods_client_pos (
  c0d                    INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  client_po_no           VARCHAR(30) NOT NULL,                    -- our internal CPO-YYYY-NNNN
  customer_po_ref        VARCHAR(60) NOT NULL DEFAULT '',         -- customer's own PO number
  project_id             INT NOT NULL DEFAULT 0,
  customer_id            INT NOT NULL,
  customer_name          VARCHAR(200) NOT NULL DEFAULT '',
  source_module          VARCHAR(20) NOT NULL DEFAULT 'goods',    -- goods|services|works (same table serves all 3)
  po_date                DATE NOT NULL,
  delivery_by            DATE DEFAULT NULL,
  currency               VARCHAR(10) NOT NULL DEFAULT 'TZS',
  vat_rate               DECIMAL(6,2) NOT NULL DEFAULT 18.00,
  subtotal_sell          DECIMAL(20,2) NOT NULL DEFAULT 0.00,     -- Σ line_total_sell
  subtotal_cog           DECIMAL(20,2) NOT NULL DEFAULT 0.00,     -- Σ line_total_cog (planned)
  vat_amount             DECIMAL(20,2) NOT NULL DEFAULT 0.00,
  total                  DECIMAL(20,2) NOT NULL DEFAULT 0.00,
  default_margin_pct     DECIMAL(6,2)  NOT NULL DEFAULT 25.00,    -- suggests unit_price_cog from unit_price_sell
  -- Lifecycle -----------------------------------------------------------
  status                 VARCHAR(20) NOT NULL DEFAULT 'DRAFT',    -- DRAFT|CONFIRMED|SOURCING|INVOICING|CLOSED|CANCELLED
  payment_terms          VARCHAR(60) NOT NULL DEFAULT '',         -- e.g. '30 days net'
  delivery_poc           VARCHAR(120) NOT NULL DEFAULT '',
  delivery_poc_phone     VARCHAR(40)  NOT NULL DEFAULT '',
  delivery_address       TEXT,
  notes                  TEXT,
  linked_tender_id       INT NOT NULL DEFAULT 0,
  -- Audit ---------------------------------------------------------------
  created_by             INT NOT NULL DEFAULT 0,
  created_by_name        VARCHAR(120) NOT NULL DEFAULT '',
  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_client_po_no (client_po_no),
  KEY ix_project (project_id),
  KEY ix_customer (customer_id),
  KEY ix_status (status),
  KEY ix_po_date (po_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE goods_client_po_items (
  c0d              INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  client_po_id     INT NOT NULL,
  item_no          INT NOT NULL DEFAULT 1,
  description      VARCHAR(255) NOT NULL,
  qty              DECIMAL(20,4) NOT NULL DEFAULT 1.0000,
  unit             VARCHAR(20)  NOT NULL DEFAULT '',
  unit_price_sell  DECIMAL(20,2) NOT NULL DEFAULT 0.00,
  unit_price_cog   DECIMAL(20,2) NOT NULL DEFAULT 0.00,    -- planned cost (for margin calc)
  line_total_sell  DECIMAL(20,2) NOT NULL DEFAULT 0.00,
  line_total_cog   DECIMAL(20,2) NOT NULL DEFAULT 0.00,
  qty_invoiced     DECIMAL(20,4) NOT NULL DEFAULT 0.0000,   -- rolled up from sales_items later
  qty_delivered    DECIMAL(20,4) NOT NULL DEFAULT 0.0000,   -- rolled up from GRN (future)
  notes            TEXT,
  created_on       TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY ix_client_po (client_po_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Link sales → client_po so we can trace revenue back to the contracted PO
ALTER TABLE sales
    ADD COLUMN client_po_id INT NOT NULL DEFAULT 0 AFTER project_id,
    ADD KEY ix_client_po (client_po_id);
