-- ═══════════════════════════════════════════════════════════════════════════
-- rsflow.dspera · Customer Invoices (Sales)
-- Shared across Goods/Services/Works — the source_module column tells you
-- which flow spawned the invoice. Same GOODS_CUSTOMER_INVOICE chain approves
-- all three modules' invoices for now (rename chain in a later slice if needed).
-- ═══════════════════════════════════════════════════════════════════════════

DROP TABLE IF EXISTS sales_items;
DROP TABLE IF EXISTS sales;

CREATE TABLE sales (
  c0d                        INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  sale_no                    VARCHAR(30) NOT NULL DEFAULT '',       -- allocated only on Accountant Ack (blank while draft)
  invoice_ref                VARCHAR(30) NOT NULL,                  -- internal handle used while draft (INV-DRAFT-nnnn)
  project_id                 INT NOT NULL DEFAULT 0,
  customer_id                INT NOT NULL DEFAULT 0,
  customer_name              VARCHAR(200) NOT NULL DEFAULT '',
  source_module              VARCHAR(20) NOT NULL DEFAULT 'goods',  -- goods|services|works
  invoice_date               DATE NOT NULL,
  due_date                   DATE DEFAULT NULL,
  currency                   VARCHAR(10) NOT NULL DEFAULT 'TZS',
  subtotal                   DECIMAL(20,2) NOT NULL DEFAULT 0.00,
  vat_rate                   DECIMAL(6,2)  NOT NULL DEFAULT 18.00,  -- Tanzania default
  vat_amount                 DECIMAL(20,2) NOT NULL DEFAULT 0.00,
  total                      DECIMAL(20,2) NOT NULL DEFAULT 0.00,
  amount_paid                DECIMAL(20,2) NOT NULL DEFAULT 0.00,   -- populated by receipts (later slice)
  -- Chain & workflow --------------------------------------------------------
  invoice_workflow_status    VARCHAR(30) NOT NULL DEFAULT 'DRAFT',  -- DRAFT|SUBMITTED|PC_APPROVED|PM_APPROVED|ISSUED|CANCELLED
  approval_id                INT NOT NULL DEFAULT 0,
  journal_entry_id           INT NOT NULL DEFAULT 0,                -- populated on Accountant Ack
  -- Audit -------------------------------------------------------------------
  narrative                  TEXT,
  drafted_by                 INT NOT NULL DEFAULT 0,
  drafted_by_name            VARCHAR(120) NOT NULL DEFAULT '',
  drafted_on                 TIMESTAMP NULL DEFAULT NULL,
  acknowledged_by            INT NOT NULL DEFAULT 0,
  acknowledged_by_name       VARCHAR(120) NOT NULL DEFAULT '',
  acknowledged_on            TIMESTAMP NULL DEFAULT NULL,
  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_invoice_ref (invoice_ref),
  KEY ix_sale_no (sale_no),
  KEY ix_project (project_id),
  KEY ix_customer (customer_id),
  KEY ix_status (invoice_workflow_status),
  KEY ix_source (source_module),
  KEY ix_je (journal_entry_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE sales_items (
  c0d          INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  sale_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   DECIMAL(20,2) NOT NULL DEFAULT 0.00,
  line_total   DECIMAL(20,2) NOT NULL DEFAULT 0.00,
  created_on   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY ix_sale (sale_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
