-- ═══════════════════════════════════════════════════════════════════════════
-- rsflow.dspera · Customer Receipts + EFD registry (money-in side)
-- Tanzania VAT law: every receipt of taxable supply must be issued via EFD
-- (Electronic Fiscal Device). We track receipt_type = EFD_VAT | EFD_NON_VAT
-- | NORMAL so VAT returns can reconcile Output VAT vs EFD Z-reports.
--
-- Also extends lpos with VAT-capture columns so the payables side (supplier
-- EFD tax invoices → Input VAT recoverable) is schema-ready for the next
-- slice — the JournalPosting engine is upgraded now to split input VAT
-- whenever lpo.vat_amount > 0.
-- ═══════════════════════════════════════════════════════════════════════════

DROP TABLE IF EXISTS receipts;
DROP TABLE IF EXISTS efd_devices;

CREATE TABLE efd_devices (
  c0d              INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  device_serial    VARCHAR(30) NOT NULL,                        -- physical serial (unique)
  device_model     VARCHAR(80) NOT NULL DEFAULT '',
  tra_registration VARCHAR(30) NOT NULL DEFAULT '',             -- TRA device registration reference
  location         VARCHAR(120) NOT NULL DEFAULT 'HQ',
  currency         VARCHAR(10) NOT NULL DEFAULT 'TZS',
  is_active        TINYINT(1) NOT NULL DEFAULT 1,
  created_by       INT NOT NULL DEFAULT 0,
  created_on       TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_serial (device_serial)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE receipts (
  c0d                   INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  receipt_no            VARCHAR(30) NOT NULL,                   -- internal (RCP-YYYY-NNNN)
  -- Type & fiscal category -----------------------------------------------
  receipt_type          VARCHAR(20) NOT NULL DEFAULT 'NORMAL',  -- EFD_VAT | EFD_NON_VAT | NORMAL
  sale_id               INT NOT NULL DEFAULT 0,                 -- FK to sales (0 = standalone / advance)
  customer_id           INT NOT NULL DEFAULT 0,
  customer_name         VARCHAR(200) NOT NULL DEFAULT '',
  project_id            INT NOT NULL DEFAULT 0,
  -- Money -----------------------------------------------------------------
  amount                DECIMAL(20,2) NOT NULL DEFAULT 0.00,    -- total received (inclusive of VAT when EFD_VAT)
  currency              VARCHAR(10) NOT NULL DEFAULT 'TZS',
  bank_account_id       INT NOT NULL DEFAULT 0,
  payment_method        VARCHAR(20) NOT NULL DEFAULT 'BANK_TRANSFER', -- BANK_TRANSFER|CHEQUE|CASH|MOBILE_MONEY|POS
  bank_reference        VARCHAR(60) NOT NULL DEFAULT '',        -- TT ref / cheque no / MoMo ref
  receipt_date          DATE NOT NULL,
  -- EFD fiscal fields (populated when receipt_type IN EFD_VAT | EFD_NON_VAT) --
  efd_device_id         INT NOT NULL DEFAULT 0,                 -- FK efd_devices
  efd_serial            VARCHAR(30) NOT NULL DEFAULT '',        -- denormalized for read-perf
  efd_receipt_number    VARCHAR(50) NOT NULL DEFAULT '',        -- printed receipt no (fiscal)
  efd_z_number          VARCHAR(30) NOT NULL DEFAULT '',        -- Z-report daily reference
  efd_signature         VARCHAR(120) NOT NULL DEFAULT '',       -- EFD signature/hash on print
  efd_issued_at         DATETIME     DEFAULT NULL,              -- timestamp on the fiscal print
  efd_output_vat        DECIMAL(20,2) NOT NULL DEFAULT 0.00,    -- VAT portion (only for EFD_VAT)
  -- Journal --------------------------------------------------------------
  journal_entry_id      INT NOT NULL DEFAULT 0,
  narrative             TEXT,
  received_by           INT NOT NULL DEFAULT 0,
  received_by_name      VARCHAR(120) NOT NULL DEFAULT '',
  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_receipt_no (receipt_no),
  KEY ix_sale (sale_id),
  KEY ix_customer (customer_id),
  KEY ix_bank (bank_account_id),
  KEY ix_receipt_type (receipt_type),
  KEY ix_receipt_date (receipt_date),
  KEY ix_je (journal_entry_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── Extend lpos with VAT-capture columns (supplier EFD tax invoice) ──────
-- When paying a supplier's bill, we capture whether they issued an EFD tax
-- invoice AND if VAT was included. If vat_amount > 0, JournalPosting splits
-- Dr Expense (net) + Dr 8210 Input VAT + Cr Bank so Input VAT is claimable.

ALTER TABLE lpos
    ADD COLUMN supplier_receipt_type   VARCHAR(20)  NOT NULL DEFAULT 'NORMAL'  AFTER retired_on, -- SUPPLIER_EFD_VAT | SUPPLIER_EFD_NON_VAT | NORMAL
    ADD COLUMN supplier_efd_number     VARCHAR(50)  NOT NULL DEFAULT ''        AFTER supplier_receipt_type,
    ADD COLUMN supplier_tin            VARCHAR(30)  NOT NULL DEFAULT ''        AFTER supplier_efd_number,
    ADD COLUMN supplier_efd_date       DATE                  DEFAULT NULL      AFTER supplier_tin,
    ADD COLUMN net_amount              DECIMAL(20,2) NOT NULL DEFAULT 0.00     AFTER supplier_efd_date, -- amount minus VAT
    ADD COLUMN vat_amount              DECIMAL(20,2) NOT NULL DEFAULT 0.00     AFTER net_amount,        -- input VAT (recoverable)
    ADD COLUMN vat_inclusive_flag      TINYINT(1)   NOT NULL DEFAULT 0         AFTER vat_amount;        -- 1 = the amount column already includes VAT

-- Seed 8210 Input VAT (Asset — VAT claimable from TRA) --------------------

INSERT IGNORE INTO chart_of_accounts (account_code, account_name, account_type, is_active, currency)
VALUES ('8210', 'Input VAT (Recoverable)', 'ASSET', 1, 'TZS');

-- Seed one EFD device so the receipts flow has something to attach to -----

INSERT IGNORE INTO efd_devices (device_serial, device_model, tra_registration, location, currency)
VALUES ('EFD-XY-0000123', 'Incotex 133F', 'TRA-2026-08917', 'HQ Reception', 'TZS');
