-- =====================================================================
--  RETAIL PHARMACY PLATFORM
--  Phase 1 Schema Pack J — Bank Reconciliation v1.0
--
--  Apply AFTER packs C and I.
--  Target: MySQL 8.0+ / InnoDB / utf8mb4
--
--  =================================================================
--  WHY THIS IS THE LAST ACCOUNTING PIECE, NOT THE LEAST
--  =================================================================
--
--  The books and the bank never agree, and most of the difference is
--  perfectly innocent: a cheque deposited on Saturday clears on Tuesday,
--  the bank took ₹236 of charges nobody entered, a customer paid by UPI
--  directly and told nobody. None of that is an error. All of it makes
--  the cash figure in the P&L wrong until somebody reconciles.
--
--  A chemist's CA asks for this first, every quarter, and it is the job
--  the chemist most reliably does not do — because in his current
--  software it means printing a statement and ticking lines with a pen.
--
--  =================================================================
--  THE DISTINCTION THAT MAKES IT USEFUL
--  =================================================================
--
--  Three different things look identical in a naive comparison:
--
--    TIMING     deposited, not yet cleared        -> nothing to do, wait
--    MISSING    real money the books never saw    -> post it
--    ERROR      amounts disagree                   -> investigate
--
--  Software that lumps these together produces a 300-line exception
--  list, which is the same as producing nothing. Classifying them is
--  the entire value of the module.
-- =====================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;


-- ---------------------------------------------------------------------
-- J.1 Bank account
-- ---------------------------------------------------------------------
CREATE TABLE bank_account (
  id                CHAR(26)      NOT NULL,
  tenant_id         CHAR(26)      NOT NULL,
  store_id          CHAR(26)      NOT NULL,
  ledger_account_id CHAR(26)          NULL COMMENT 'The BANK account these lines post against',
  bank_name         VARCHAR(120)  NOT NULL,
  branch            VARCHAR(120)      NULL,
  -- Last four only. The full number is never stored: it buys nothing
  -- operationally and it is the single most damaging field to leak.
  account_last4     CHAR(4)           NULL,
  account_label     VARCHAR(80)   NOT NULL COMMENT 'What the chemist calls it',
  ifsc              VARCHAR(11)       NULL,
  opening_balance   DECIMAL(16,2) NOT NULL DEFAULT 0,
  opening_on        DATE              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_ba (store_id, account_label),
  KEY ix_ba_active (store_id, is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


-- ---------------------------------------------------------------------
-- J.2 Imported statement line — the bank's version of events
-- ---------------------------------------------------------------------
CREATE TABLE bank_statement_line (
  id              CHAR(26)      NOT NULL,
  tenant_id       CHAR(26)      NOT NULL,
  store_id        CHAR(26)      NOT NULL,
  bank_account_id CHAR(26)      NOT NULL,
  import_batch    CHAR(26)      NOT NULL COMMENT 'One upload',
  txn_date        DATE          NOT NULL,
  value_date      DATE              NULL,
  narration       VARCHAR(400)  NOT NULL COMMENT 'Exactly as the bank wrote it',
  ref_no          VARCHAR(60)       NULL COMMENT 'Cheque no, UTR, RRN',
  debit           DECIMAL(14,2) NOT NULL DEFAULT 0 COMMENT 'Money out of the account',
  credit          DECIMAL(14,2) NOT NULL DEFAULT 0 COMMENT 'Money in',
  running_balance DECIMAL(16,2)     NULL,
  -- Same content twice is the same line. Banks re-issue statements with
  -- overlapping date ranges constantly.
  line_hash       CHAR(64)      NOT NULL,
  match_status    ENUM('UNMATCHED','MATCHED','TOLERANCE','TIMING','MISSING_IN_BOOKS',
                       'BANK_CHARGE','INTEREST','IGNORED') NOT NULL DEFAULT 'UNMATCHED',
  matched_type    VARCHAR(30)       NULL COMMENT 'RECEIPT | PAYMENT | VOUCHER',
  matched_id      CHAR(26)          NULL,
  match_method    VARCHAR(30)       NULL,
  variance        DECIMAL(14,2) NOT NULL DEFAULT 0,
  posted_voucher_id CHAR(26)        NULL COMMENT 'When we created the missing entry',
  created_at      DATETIME(3)   NOT NULL,
  updated_at      DATETIME(3)   NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_bsl (bank_account_id, line_hash),
  KEY ix_bsl_status (store_id, match_status, txn_date),
  KEY ix_bsl_date (bank_account_id, txn_date),
  KEY ix_bsl_ref (ref_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


-- ---------------------------------------------------------------------
-- J.3 Reconciliation run — the statement the CA actually wants
--
--  Balance as per books, adjusted for the timing differences, equals
--  balance as per bank. If it does not, the run is not finished, and
--  the module says so rather than presenting a tidy number that is wrong.
-- ---------------------------------------------------------------------
CREATE TABLE bank_recon (
  id                    CHAR(26)      NOT NULL,
  tenant_id             CHAR(26)      NOT NULL,
  store_id              CHAR(26)      NOT NULL,
  bank_account_id       CHAR(26)      NOT NULL,
  as_on                 DATE          NOT NULL,
  book_balance          DECIMAL(16,2) NOT NULL DEFAULT 0,
  bank_balance          DECIMAL(16,2) NOT NULL DEFAULT 0,
  -- The three reconciling categories, kept apart on purpose
  uncleared_deposits    DECIMAL(16,2) NOT NULL DEFAULT 0 COMMENT 'In books, not yet in bank',
  uncleared_withdrawals DECIMAL(16,2) NOT NULL DEFAULT 0,
  unrecorded_credits    DECIMAL(16,2) NOT NULL DEFAULT 0 COMMENT 'In bank, not in books',
  unrecorded_debits     DECIMAL(16,2) NOT NULL DEFAULT 0,
  difference            DECIMAL(16,2) NOT NULL DEFAULT 0 COMMENT 'Must be zero to be done',
  lines_total           INT UNSIGNED  NOT NULL DEFAULT 0,
  lines_matched         INT UNSIGNED  NOT NULL DEFAULT 0,
  lines_open            INT UNSIGNED  NOT NULL DEFAULT 0,
  is_balanced           TINYINT(1)    NOT NULL DEFAULT 0,
  closed_at             DATETIME(3)       NULL,
  created_at            DATETIME(3)   NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_br (bank_account_id, as_on),
  KEY ix_br_open (store_id, is_balanced, as_on)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


SET FOREIGN_KEY_CHECKS = 1;

-- =====================================================================
--  INVARIANTS asserted in tests:
--
--   1. Re-importing an overlapping statement creates no duplicate lines.
--   2. A cheque deposited but not yet cleared is TIMING, not an error.
--   3. Bank charges and interest are recognised and posted, not left as
--      unexplained exceptions.
--   4. book balance + reconciling items = bank balance, to the paisa.
--   5. A run is only "balanced" when that difference is zero.
--   6. No full bank account number is stored anywhere.
-- =====================================================================