-- =====================================================================
--  RETAIL PHARMACY PLATFORM
--  Phase 1 Schema Pack C — Accounting & GST Returns v1.0
--
--  Apply AFTER schema-phase0-core-v1.sql and schema-phase1a-purchase.sql.
--  Target: MySQL 8.0+ / InnoDB / utf8mb4
--
--  WHY THIS IS NOT OPTIONAL
--  A chemist does not run two systems. He runs one, and his CA gets the
--  output. Ship without accounting and every deal stalls at "then what
--  do I give my CA?" — which is the same conversation that keeps him on
--  Marg, where the books already work.
--
--  POSTING IS DELIBERATELY OUT OF THE COUNTER HOT PATH.
--  Billing writes an event and returns in single-digit milliseconds
--  (Phase 1B, measured p95 6.4 ms). Accounting is posted afterwards by a
--  worker reading the event log. Double-entry maths must never sit
--  between a customer and their printed bill.
-- =====================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;


-- ---------------------------------------------------------------------
-- C.1 Chart of accounts
-- ---------------------------------------------------------------------
CREATE TABLE ledger_account (
  id            CHAR(26)      NOT NULL,
  tenant_id     CHAR(26)          NULL COMMENT 'NULL = platform-standard account',
  code          VARCHAR(20)   NOT NULL,
  name          VARCHAR(150)  NOT NULL,
  acct_group    VARCHAR(60)   NOT NULL COMMENT 'Current Assets, Duties & Taxes, Direct Income, ...',
  acct_type     ENUM('ASSET','LIABILITY','EQUITY','INCOME','EXPENSE') NOT NULL,
  -- Which side increases this account. Getting this wrong inverts the
  -- balance sheet in a way that is very hard to spot after the fact.
  normal_side   ENUM('DR','CR') NOT NULL,
  is_system     TINYINT(1)    NOT NULL DEFAULT 0 COMMENT 'Cannot be renamed or deleted by the chemist',
  party_type    ENUM('NONE','CUSTOMER','SUPPLIER') NOT NULL DEFAULT 'NONE',
  party_id      CHAR(26)          NULL,
  opening_bal   DECIMAL(16,2) NOT NULL DEFAULT 0,
  opening_side  ENUM('DR','CR') NOT NULL DEFAULT 'DR',
  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_la_code (tenant_id, code),
  KEY ix_la_type (acct_type, is_active),
  KEY ix_la_party (party_type, party_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


-- ---------------------------------------------------------------------
-- C.2 Voucher header
-- ---------------------------------------------------------------------
CREATE TABLE voucher (
  id              CHAR(26)      NOT NULL,
  tenant_id       CHAR(26)      NOT NULL,
  store_id        CHAR(26)      NOT NULL,
  event_id        CHAR(26)          NULL COMMENT 'Originating event_log row',
  voucher_no      VARCHAR(30)   NOT NULL,
  voucher_type    ENUM('SALE','SALE_RETURN','PURCHASE','PURCHASE_RETURN','RECEIPT',
                       'PAYMENT','JOURNAL','CONTRA','OPENING') NOT NULL,
  voucher_date    DATE          NOT NULL,
  fy_code         VARCHAR(9)    NOT NULL,
  narration       VARCHAR(300)      NULL,
  source_doc_type VARCHAR(30)       NULL,
  source_doc_id   CHAR(26)          NULL,
  total_debit     DECIMAL(16,2) NOT NULL DEFAULT 0,
  total_credit    DECIMAL(16,2) NOT NULL DEFAULT 0,
  is_cancelled    TINYINT(1)    NOT NULL DEFAULT 0,
  created_at      DATETIME(3)   NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_v_no (store_id, fy_code, voucher_type, voucher_no),
  UNIQUE KEY uk_v_source (source_doc_type, source_doc_id, voucher_type)
                COMMENT 'One voucher per source document — blocks double posting',
  KEY ix_v_date (store_id, voucher_date),
  KEY ix_v_event (event_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


-- ---------------------------------------------------------------------
-- C.3 Voucher line
-- ---------------------------------------------------------------------
CREATE TABLE voucher_line (
  id            CHAR(26)      NOT NULL,
  voucher_id    CHAR(26)      NOT NULL,
  store_id      CHAR(26)      NOT NULL,
  line_no       SMALLINT      NOT NULL,
  account_id    CHAR(26)      NOT NULL,
  debit         DECIMAL(16,2) NOT NULL DEFAULT 0,
  credit        DECIMAL(16,2) NOT NULL DEFAULT 0,
  narration     VARCHAR(300)      NULL,
  voucher_date  DATE          NOT NULL COMMENT 'Denormalised — every ledger query filters on it',
  PRIMARY KEY (id),
  UNIQUE KEY uk_vl (voucher_id, line_no),
  KEY ix_vl_account (account_id, voucher_date),
  KEY ix_vl_store (store_id, voucher_date),
  CONSTRAINT fk_vl_voucher FOREIGN KEY (voucher_id) REFERENCES voucher(id),
  CONSTRAINT fk_vl_account FOREIGN KEY (account_id) REFERENCES ledger_account(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


-- ---------------------------------------------------------------------
-- C.4 Posting cursor — the worker's position in the event log.
--     Posting is idempotent via uk_v_source, so a cursor that rewinds is
--     safe; one that runs ahead is not. Advance only after commit.
-- ---------------------------------------------------------------------
CREATE TABLE posting_cursor (
  id              CHAR(26)      NOT NULL,
  store_id        CHAR(26)      NOT NULL,
  last_event_id   CHAR(26)          NULL,
  last_posted_at  DATETIME(3)       NULL,
  vouchers_posted BIGINT UNSIGNED NOT NULL DEFAULT 0,
  last_error      VARCHAR(500)      NULL,
  created_at      DATETIME(3)   NOT NULL,
  updated_at      DATETIME(3)   NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_pc (store_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


-- ---------------------------------------------------------------------
-- C.5 GST return filing record.
--     What was filed, when, and off which figures. When a notice arrives
--     eighteen months later, this is the only way to reconstruct what the
--     books said on filing day.
-- ---------------------------------------------------------------------
CREATE TABLE gst_return (
  id              CHAR(26)      NOT NULL,
  tenant_id       CHAR(26)      NOT NULL,
  store_id        CHAR(26)      NOT NULL,
  gstin           VARCHAR(15)   NOT NULL,
  return_type     ENUM('GSTR1','GSTR3B','GSTR4','CMP08') NOT NULL,
  ret_period      CHAR(6)       NOT NULL COMMENT 'MMYYYY',
  status          ENUM('DRAFT','GENERATED','FILED','REVISED') NOT NULL DEFAULT 'DRAFT',
  total_taxable   DECIMAL(16,2) NOT NULL DEFAULT 0,
  total_cgst      DECIMAL(16,2) NOT NULL DEFAULT 0,
  total_sgst      DECIMAL(16,2) NOT NULL DEFAULT 0,
  total_igst      DECIMAL(16,2) NOT NULL DEFAULT 0,
  total_cess      DECIMAL(16,2) NOT NULL DEFAULT 0,
  invoice_count   INT UNSIGNED  NOT NULL DEFAULT 0,
  payload_json    LONGTEXT          NULL COMMENT 'Exactly what was generated',
  generated_at    DATETIME(3)       NULL,
  filed_at        DATETIME(3)       NULL,
  arn             VARCHAR(30)       NULL,
  created_at      DATETIME(3)   NOT NULL,
  updated_at      DATETIME(3)   NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_gr (store_id, gstin, return_type, ret_period),
  KEY ix_gr_status (status, ret_period)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


SET FOREIGN_KEY_CHECKS = 1;

-- =====================================================================
--  INVARIANTS enforced by the posting engine and asserted in tests:
--
--   1. Every voucher balances: SUM(debit) = SUM(credit), to the paisa.
--   2. The trial balance sums to zero across all accounts.
--   3. One voucher per source document (uk_v_source) — reposting the
--      same bill can never double-count revenue.
--   4. GSTR-1 outward taxable value reconciles to the sale register for
--      the same period, to the paisa.
--   5. Counter and online sales appear in ONE GSTR-1 (principle P8).
--      Two returns per store would defeat the whole single-platform
--      argument.
-- =====================================================================
-- a stray edit