-- ═══════════════════════════════════════════════════════════════════════════
-- rsflow.dspera · Goods · Sourcing (RFQ → supplier quotes → award → LPO batch)
-- One RFQ per Client PO. Each RFQ invites N suppliers. Each supplier quotes
-- prices on any subset of the PO's line items. Procurement awards a supplier
-- per line, then Finalize creates one SUPPLIER lpo per awarded supplier and
-- submits ONE fin_approvals row (chain GOODS_LPO_APPROVAL) covering the
-- whole batch — PC → PM → FM → EXEC. On terminal APPROVED, every batched
-- LPO flips to APPROVED simultaneously.
-- ═══════════════════════════════════════════════════════════════════════════

DROP TABLE IF EXISTS goods_rfq_quote_items;
DROP TABLE IF EXISTS goods_rfq_quotes;
DROP TABLE IF EXISTS goods_rfqs;

CREATE TABLE goods_rfqs (
  c0d              INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  rfq_no           VARCHAR(30) NOT NULL,
  client_po_id     INT NOT NULL,
  project_id       INT NOT NULL DEFAULT 0,
  currency         VARCHAR(10) NOT NULL DEFAULT 'TZS',
  status           VARCHAR(20) NOT NULL DEFAULT 'DRAFT', -- DRAFT|OPEN|EVALUATING|AWARDED|CLOSED|CANCELLED
  approval_id      INT NOT NULL DEFAULT 0,               -- fin_approvals batch row
  notes            TEXT,
  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_rfq_no (rfq_no),
  KEY ix_client_po (client_po_id),
  KEY ix_status (status),
  KEY ix_approval (approval_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE goods_rfq_quotes (
  c0d              INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  rfq_id           INT NOT NULL,
  vendor_id        INT NOT NULL,
  vendor_name      VARCHAR(200) NOT NULL DEFAULT '',
  vendor_tin       VARCHAR(40) NOT NULL DEFAULT '',
  vendor_contact_phone VARCHAR(40) NOT NULL DEFAULT '',
  vendor_contact_email VARCHAR(120) NOT NULL DEFAULT '',
  quoted_on        DATE DEFAULT NULL,
  valid_until      DATE DEFAULT NULL,
  status           VARCHAR(20) NOT NULL DEFAULT 'INVITED', -- INVITED|QUOTED|DECLINED|AWARDED
  awarded_lpo_id   INT NOT NULL DEFAULT 0,                 -- populated on finalize
  notes            TEXT,
  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_rfq_vendor (rfq_id, vendor_id),
  KEY ix_rfq (rfq_id),
  KEY ix_vendor (vendor_id),
  KEY ix_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE goods_rfq_quote_items (
  c0d               INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  rfq_quote_id      INT NOT NULL,
  client_po_item_id INT NOT NULL,
  unit_price        DECIMAL(20,2) NOT NULL DEFAULT 0.00,
  qty_available     DECIMAL(20,4) NOT NULL DEFAULT 0.0000,
  lead_time_days    INT NOT NULL DEFAULT 0,
  notes             TEXT,
  awarded_flag      TINYINT(1) NOT NULL DEFAULT 0,
  awarded_on        TIMESTAMP NULL DEFAULT NULL,
  awarded_by        INT NOT NULL DEFAULT 0,
  created_on        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  edited_on         TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_quote_item (rfq_quote_id, client_po_item_id),
  KEY ix_quote (rfq_quote_id),
  KEY ix_client_po_item (client_po_item_id),
  KEY ix_awarded (awarded_flag)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Add source RFQ + client PO trace to lpos so we can navigate back ---------
ALTER TABLE lpos
    ADD COLUMN sourced_from_rfq_id  INT NOT NULL DEFAULT 0 AFTER approval_id,
    ADD COLUMN sourced_from_client_po_id INT NOT NULL DEFAULT 0 AFTER sourced_from_rfq_id,
    ADD KEY ix_source_rfq (sourced_from_rfq_id),
    ADD KEY ix_source_cpo (sourced_from_client_po_id);
