-- =====================================================================
--  RETAIL PHARMACY PLATFORM
--  Phase 1 Schema Pack I — Receivables & Credit Control v1.0
--
--  Apply AFTER packs A, C, D, G.
--  Target: MySQL 8.0+ / InnoDB / utf8mb4
--
--  =================================================================
--  A HOLE THIS PACK CLOSES
--  =================================================================
--
--  The billing engine already accepts CREDIT as a payment mode. Until
--  now nothing managed what happened next: no receipt, no allocation,
--  no ageing, and — worse — no credit limit check at the counter. A
--  chemist could bill ₹80,000 to a customer with a ₹5,000 limit and the
--  software would say nothing.
--
--  That matters more here than in most retail. A large share of an
--  Indian chemist's turnover is on account: the nursing home down the
--  road, the doctor's clinic, the regular family with a chronic
--  prescription. Uncollected credit is the commonest way a profitable
--  shop runs out of cash.
--
--  =================================================================
--  ALLOCATION IS THE PART EVERYONE GETS WRONG
--  =================================================================
--
--  A customer pays ₹5,000 against ₹18,000 spread over nine bills. Which
--  bills are settled? If the software cannot answer that, then ageing
--  is fiction, the oldest debt never surfaces, and the chemist chases
--  the wrong people.
--
--  So a receipt does not simply reduce a balance. It is allocated to
--  specific invoices, oldest first by default, and any remainder sits
--  visibly as money on account rather than quietly vanishing into a
--  running total.
-- =====================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;


-- ---------------------------------------------------------------------
-- I.1 Receipt — money in from a customer
-- ---------------------------------------------------------------------
CREATE TABLE receipt (
  id                CHAR(26)      NOT NULL,
  tenant_id         CHAR(26)      NOT NULL,
  store_id          CHAR(26)      NOT NULL,
  counter_id        CHAR(26)          NULL,
  event_id          CHAR(26)      NOT NULL,
  receipt_no        VARCHAR(30)   NOT NULL,
  fy_code           VARCHAR(9)    NOT NULL,
  receipt_date      DATE          NOT NULL,
  receipt_ts        DATETIME(3)   NOT NULL,
  customer_id       CHAR(26)      NOT NULL,
  mode              ENUM('CASH','UPI','CARD','CHEQUE','BANK_TRANSFER') NOT NULL,
  amount            DECIMAL(14,2) NOT NULL,
  -- Early-settlement discount. A real one, given to get money in, and it
  -- IS booked — unlike a trade discount at the counter, which is netted
  -- at source and never appears in the ledger.
  settlement_disc   DECIMAL(14,2) NOT NULL DEFAULT 0,
  allocated_amount  DECIMAL(14,2) NOT NULL DEFAULT 0,
  on_account        DECIMAL(14,2) NOT NULL DEFAULT 0
                    COMMENT 'Unallocated remainder — visible, never silently absorbed',
  instrument_no     VARCHAR(40)       NULL COMMENT 'Cheque or UTR',
  instrument_date   DATE              NULL,
  bank_name         VARCHAR(120)      NULL,
  -- Cheques bounce. Until cleared, this money is a promise.
  clearing_status   ENUM('NA','PENDING','CLEARED','BOUNCED') NOT NULL DEFAULT 'NA',
  bounced_reason    VARCHAR(200)      NULL,
  cleared_on        DATE              NULL,
  is_cancelled      TINYINT(1)    NOT NULL DEFAULT 0,
  note              VARCHAR(300)      NULL,
  user_id           CHAR(26)          NULL,
  created_at        DATETIME(3)   NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_rcpt_no (store_id, fy_code, receipt_no),
  KEY ix_rcpt_customer (customer_id, receipt_date),
  KEY ix_rcpt_date (store_id, receipt_date),
  KEY ix_rcpt_clearing (store_id, clearing_status, instrument_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


-- ---------------------------------------------------------------------
-- I.2 Allocation — which invoices a receipt actually settles
-- ---------------------------------------------------------------------
CREATE TABLE receipt_allocation (
  id              CHAR(26)      NOT NULL,
  receipt_id      CHAR(26)      NOT NULL,
  store_id        CHAR(26)      NOT NULL,
  sale_bill_id    CHAR(26)      NOT NULL,
  bill_no         VARCHAR(30)   NOT NULL,
  bill_date       DATE          NOT NULL,
  amount          DECIMAL(14,2) NOT NULL,
  created_at      DATETIME(3)   NOT NULL,
  PRIMARY KEY (id),
  KEY ix_ra_receipt (receipt_id),
  KEY ix_ra_bill (sale_bill_id),
  CONSTRAINT fk_ra_receipt FOREIGN KEY (receipt_id) REFERENCES receipt(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


-- ---------------------------------------------------------------------
-- I.3 Credit control decisions — every override, recorded
--
--  A limit that anyone can wave through without trace is not a limit.
--  When the ₹80,000 goes bad, the chemist needs to know who let it.
-- ---------------------------------------------------------------------
CREATE TABLE credit_decision (
  id              CHAR(26)      NOT NULL,
  tenant_id       CHAR(26)      NOT NULL,
  store_id        CHAR(26)      NOT NULL,
  customer_id     CHAR(26)      NOT NULL,
  decision        ENUM('ALLOWED','BLOCKED','OVERRIDDEN') NOT NULL,
  reason          VARCHAR(60)   NOT NULL COMMENT 'OVER_LIMIT | OVERDUE | BOUNCED_CHEQUE | OK',
  bill_amount     DECIMAL(14,2) NOT NULL DEFAULT 0,
  outstanding     DECIMAL(14,2) NOT NULL DEFAULT 0,
  credit_limit    DECIMAL(14,2) NOT NULL DEFAULT 0,
  oldest_days     SMALLINT      NOT NULL DEFAULT 0,
  overridden_by   CHAR(26)          NULL,
  override_note   VARCHAR(300)      NULL,
  created_at      DATETIME(3)   NOT NULL,
  PRIMARY KEY (id),
  KEY ix_cd_customer (customer_id, created_at),
  KEY ix_cd_override (store_id, decision, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


SET FOREIGN_KEY_CHECKS = 1;

-- =====================================================================
--  INVARIANTS asserted in tests:
--
--   1. A receipt's allocations plus its on-account remainder always
--      equal the receipt amount. Money never disappears into a total.
--   2. Allocation is oldest-first by default, so ageing means something.
--   3. A bill can never be allocated more than it is worth.
--   4. A bounced cheque puts the debt back exactly where it was.
--   5. The counter refuses credit beyond the limit, and any override is
--      recorded against the person who made it.
--   6. Ageing buckets sum to the customer's outstanding balance.
-- =====================================================================