-- =====================================================================
--  RETAIL PHARMACY PLATFORM
--  Phase 2 Schema Pack J — Settlement accounts v1.0
--
--  Apply AFTER pack E (orders).
--  Target: MySQL 8.0+ / InnoDB / utf8mb4
--
--  =================================================================
--  THE MONEY HAD NOWHERE TO GO
--  =================================================================
--
--  OrderEngine::createPayment() takes a $vendorAccount argument and
--  passes it to the aggregator as the shop's side of the split. Nothing
--  in the schema held one, and no caller could produce one — so a
--  storefront advertising online payment reached a customer, took his
--  chit, confirmed it, and then had no account to pay the shop into.
--
--  Found by asking where the argument comes from. Nowhere.
--
--  =================================================================
--  WHY THE SELLER OF RECORD MATTERS HERE
--  =================================================================
--
--  The chemist is the seller. The customer's money must reach HIS
--  account, and Caresoft must never hold it — pooling funds turns a
--  software company into an unlicensed payment operator. So this table
--  records the chemist's own account, registered with a licensed
--  aggregator, and the split is instructed at the aggregator.
--
--  We store the aggregator's REFERENCE for the account, not the bank
--  details. A support engineer reading this table must not walk away
--  with a chemist's account number.
-- =====================================================================

SET NAMES utf8mb4;

CREATE TABLE settlement_account (
  id                  CHAR(26)      NOT NULL,
  tenant_id           CHAR(26)      NOT NULL,
  store_id            CHAR(26)      NOT NULL,
  provider            VARCHAR(30)   NOT NULL COMMENT 'CASHFREE | RAZORPAY | ...',
  -- The aggregator's id for the chemist's account. Not his bank details:
  -- those live with the aggregator, who is licensed to hold them.
  vendor_account_ref  VARCHAR(120)  NOT NULL,
  legal_name          VARCHAR(200)  NOT NULL COMMENT 'As registered with the aggregator',
  -- Last four only, so a chemist can recognise the account on screen
  -- without the number being here to leak.
  account_last4       CHAR(4)           NULL,
  -- KYC is the aggregator's decision, mirrored here so setup can refuse
  -- to advertise online payment before it has passed.
  kyc_status          ENUM('NOT_STARTED','SUBMITTED','VERIFIED','REJECTED')
                      NOT NULL DEFAULT 'NOT_STARTED',
  kyc_note            VARCHAR(300)      NULL,
  verified_at         DATETIME(3)       NULL,
  is_active           TINYINT(1)    NOT NULL DEFAULT 1,
  row_ver             BIGINT UNSIGNED NOT NULL DEFAULT 0,
  created_at          DATETIME(3)   NOT NULL,
  updated_at          DATETIME(3)   NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_sa (store_id, provider),
  KEY ix_sa_kyc (kyc_status, is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- =====================================================================
--  INVARIANTS asserted in tests:
--
--   1. A storefront cannot advertise online payment without a VERIFIED
--      settlement account.
--   2. A shop with no verified account can still trade cash on delivery
--      — losing online payment must not close the storefront.
--   3. No bank account number is stored, only the aggregator's reference
--      and the last four digits.
--   4. createPayment resolves the account from here, not from a caller
--      that had no way to know it.
-- =====================================================================