-- =====================================================================
--  RETAIL PHARMACY PLATFORM
--  Schema Pack S — Supplier payables v1.0
--
--  Apply AFTER schema-phase1a-purchase.sql and schema-phase1c-accounting.sql.
--
--  =================================================================
--  THE LARGEST CASH OUTFLOW IN THE SHOP HAD NOWHERE TO GO
--  =================================================================
--
--  The build tracks what customers owe the shop in detail: receipts,
--  allocations, ageing, credit limits, cheque bounce handling. It
--  tracked NOTHING about what the shop owes its distributors — which is
--  the bigger number, and the one that decides whether the chemist can
--  order stock next week.
--
--  A pharmacy buys on 30-day credit from six to ten distributors and
--  pays them in a weekly run. Without this the owner cannot answer
--  "who do I pay on Friday" from the software, so he keeps a diary, and
--  the diary becomes the real system.
--
--  MIRRORS RECEIVABLES ON PURPOSE. Same allocation model, same partial
--  payment handling, same treatment of a bounced cheque. A shop that
--  learns one learns the other.
-- =====================================================================

SET NAMES utf8mb4;

-- ---------------------------------------------------------------------
--  Money going OUT to a distributor.
-- ---------------------------------------------------------------------
CREATE TABLE payment_made (
  id                CHAR(26)     NOT NULL,
  tenant_id         CHAR(26)     NOT NULL,
  store_id          CHAR(26)     NOT NULL,
  supplier_id       CHAR(26)     NOT NULL,
  payment_no        VARCHAR(30)  NOT NULL,
  payment_date      DATE         NOT NULL,

  mode              ENUM('CASH','CHEQUE','NEFT','RTGS','UPI','ADJUSTMENT') NOT NULL,
  amount            DECIMAL(14,2) NOT NULL,

  -- Cheques are still how most distributors are paid in this trade, and
  -- a cheque is not money until it clears.
  instrument_no     VARCHAR(40)      NULL,
  instrument_date   DATE             NULL,
  bank_name         VARCHAR(80)      NULL,
  status            ENUM('PENDING','CLEARED','BOUNCED','CANCELLED')
                    NOT NULL DEFAULT 'CLEARED',
  cleared_on        DATE             NULL,
  bounced_reason    VARCHAR(120)     NULL,

  -- Unallocated money is an advance: paid, not yet matched to invoices.
  allocated_amount  DECIMAL(14,2) NOT NULL DEFAULT 0,

  note              VARCHAR(300)     NULL,
  event_id          CHAR(26)         NULL,
  created_by        CHAR(26)         NULL,
  created_at        DATETIME(3)  NOT NULL,
  updated_at        DATETIME(3)  NOT NULL,

  PRIMARY KEY (id),
  UNIQUE KEY uk_payment_no (store_id, payment_no),
  KEY ix_pay_supplier (store_id, supplier_id, payment_date),
  KEY ix_pay_status (store_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
--  Which invoices a payment settled. One payment commonly covers
--  several invoices, and one invoice is often paid across two runs.
-- ---------------------------------------------------------------------
CREATE TABLE payment_allocation (
  id                  CHAR(26)     NOT NULL,
  payment_made_id     CHAR(26)     NOT NULL,
  purchase_invoice_id CHAR(26)     NOT NULL,
  amount              DECIMAL(14,2) NOT NULL,
  created_at          DATETIME(3)  NOT NULL,

  PRIMARY KEY (id),
  -- A payment may touch an invoice once. Twice is a double-count that
  -- makes the outstanding figure wrong and nobody notices for months.
  UNIQUE KEY uk_alloc (payment_made_id, purchase_invoice_id),
  KEY ix_alloc_inv (purchase_invoice_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
--  What is settled against each purchase invoice. Kept ON the invoice
--  so "what do I owe" is one indexed read, not a sum over allocations.
--  The invariant below is what keeps it honest.
-- ---------------------------------------------------------------------
ALTER TABLE purchase_invoice
  ADD COLUMN paid_amount DECIMAL(14,2) NOT NULL DEFAULT 0 AFTER net_amount,
  ADD COLUMN due_date    DATE              NULL AFTER paid_amount,
  ADD KEY ix_pi_due (store_id, due_date);

-- Existing invoices: nothing has been recorded as paid, which is true —
-- there was no way to record it.
UPDATE purchase_invoice SET paid_amount = 0 WHERE paid_amount IS NULL;

-- =====================================================================
--  INVARIANTS asserted in tests:
--
--   1. purchase_invoice.paid_amount = SUM(allocations) for that invoice,
--      counting only payments that are CLEARED or PENDING — never a
--      BOUNCED cheque.
--   2. No invoice is paid beyond its net_amount.
--   3. payment_made.allocated_amount <= payment_made.amount. The excess
--      is an advance and stays visible as one.
--   4. A bounced cheque REVERSES its allocations. A distributor whose
--      cheque bounced is owed the money again, and a shop that shows him
--      as paid will stop receiving stock.
-- =====================================================================