-- =====================================================================
--  RETAIL PHARMACY PLATFORM
--  Phase 1 Schema Pack A — Purchase Automation v1.0
--
--  Apply AFTER schema-phase0-core-v1.sql.
--  Target: MySQL 8.0+ / InnoDB / utf8mb4
--
--  WHY THIS MODULE IS FIRST
--  Purchase entry is the #1 recurring support call in this category and
--  the single biggest lever on cost to serve (SOW §7, rule S3). If the
--  chemist hand-keys inward stock, he calls us weekly and the ₹500/month
--  model collapses.
--
--  WHAT THE E-INVOICE STANDARD ACTUALLY GIVES US — verified against the
--  notified schema, not assumed:
--
--    PRESENT and mandatory:  SlNo, HsnCd, UnitPrice, TotAmt, AssAmt,
--                            GstRt, TotItemVal
--    PRESENT and optional :  PrdDesc, Barcde, Qty, FreeQty, Unit,
--                            Discount, CesRt, CesAmt, PrdSlNo,
--                            BchDtls{Nm, ExpDt, WrDt}
--    ABSENT ENTIRELY      :  MRP
--
--  Two consequences the design must absorb:
--
--  1. MRP IS NOT IN THE SCHEMA. A pharmacy cannot post inward stock
--     without a per-batch MRP — it is what the counter bills against and
--     what DPCO caps. E-invoice alone can never complete a pharmacy
--     purchase. MRP must come from the distributor's own file, the
--     printed bill, or the chemist.
--
--  2. BchDtls IS OPTIONAL. Batch and expiry are not in the mandatory
--     field list, so a distributor is free to omit them and many will.
--     Never assume they arrive.
--
--  So the honest target is NOT "zero typing". It is: auto-populate the
--  15 fields the standard does carry, and put the 2 it cannot into a
--  single fast confirmation screen. That is still the difference between
--  40 minutes of typing per invoice and 40 seconds of confirming.
--
--  Anyone who scopes this module as "fully automatic" has not read the
--  schema. Design for the gaps now, not in month eight.
-- =====================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;


-- =====================================================================
--  SECTION A — INBOUND DOCUMENT STAGING
--  Raw documents land here untouched. Parsing and matching happen
--  afterwards, so a parser bug never destroys the source document and
--  a reparse is always possible.
-- =====================================================================

-- ---------------------------------------------------------------------
-- A.1 Inbound document — one row per document received, any channel
-- ---------------------------------------------------------------------
CREATE TABLE purch_inbound_doc (
  id                CHAR(26)      NOT NULL,
  tenant_id         CHAR(26)      NOT NULL,
  store_id          CHAR(26)      NOT NULL,
  channel           ENUM('EINVOICE_IRN','EINVOICE_QR','GSTR2B','DISTRIBUTOR_FILE',
                         'PDF_PARSE','EMAIL','CSV','MANUAL') NOT NULL,
  -- Provenance
  irn               VARCHAR(64)       NULL COMMENT 'Invoice Reference Number, 64 hex',
  ack_no            VARCHAR(30)       NULL,
  ack_date          DATE              NULL,
  supplier_gstin    VARCHAR(15)       NULL,
  supplier_name_raw VARCHAR(200)      NULL,
  doc_no_raw        VARCHAR(50)       NULL,
  doc_date          DATE              NULL,
  doc_total         DECIMAL(14,2)     NULL,
  -- The document exactly as received. Never edited.
  raw_payload       LONGTEXT      NOT NULL COMMENT 'JSON / XML / extracted text',
  payload_sha256    CHAR(64)      NOT NULL COMMENT 'Dedupe key — same doc twice is one row',
  source_ref        VARCHAR(200)      NULL COMMENT 'Message id, filename, API cursor',
  -- Lifecycle
  parse_status      ENUM('RECEIVED','PARSED','PARSE_FAILED','SUPERSEDED') NOT NULL DEFAULT 'RECEIVED',
  parse_error       VARCHAR(500)      NULL,
  parser_version    VARCHAR(20)       NULL,
  received_at       DATETIME(3)   NOT NULL,
  parsed_at         DATETIME(3)       NULL,
  created_at        DATETIME(3)   NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_pid_hash (store_id, payload_sha256),
  KEY ix_pid_irn (irn),
  KEY ix_pid_status (store_id, parse_status, received_at),
  KEY ix_pid_supplier (supplier_gstin, doc_date),
  CONSTRAINT fk_pid_store FOREIGN KEY (store_id) REFERENCES store(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


-- ---------------------------------------------------------------------
-- A.2 Staged line — one row per ItemList entry, normalised but NOT yet
--     matched to the catalogue. Field names mirror the e-invoice schema
--     so the mapping stays auditable.
--
--     NOTE ON CASING. Live IRP payloads are inconsistent about key case:
--     the published schema documents ExpDt and WrDt, while sample
--     payloads in circulation use Expdt and wrDt. The parser MUST read
--     keys case-insensitively. This has bitten every team that assumed
--     the documented casing.
-- ---------------------------------------------------------------------
CREATE TABLE purch_staged_line (
  id                CHAR(26)      NOT NULL,
  inbound_doc_id    CHAR(26)      NOT NULL,
  tenant_id         CHAR(26)      NOT NULL,
  store_id          CHAR(26)      NOT NULL,
  sl_no             SMALLINT      NOT NULL,
  -- Straight from the document
  prd_desc          VARCHAR(300)      NULL COMMENT 'ItemList[].PrdDesc',
  barcode           VARCHAR(50)       NULL COMMENT 'ItemList[].Barcde',
  hsn_code          VARCHAR(10)       NULL,
  qty               DECIMAL(14,3) NOT NULL DEFAULT 0,
  free_qty          DECIMAL(14,3) NOT NULL DEFAULT 0,
  unit_raw          VARCHAR(10)       NULL,
  unit_price        DECIMAL(14,4) NOT NULL DEFAULT 0 COMMENT 'Purchase rate, NOT MRP',
  discount          DECIMAL(14,2) NOT NULL DEFAULT 0,
  ass_amt           DECIMAL(14,2) NOT NULL DEFAULT 0,
  gst_rt            DECIMAL(7,4)  NOT NULL DEFAULT 0,
  cgst_amt          DECIMAL(14,2) NOT NULL DEFAULT 0,
  sgst_amt          DECIMAL(14,2) NOT NULL DEFAULT 0,
  igst_amt          DECIMAL(14,2) NOT NULL DEFAULT 0,
  cess_amt          DECIMAL(14,2) NOT NULL DEFAULT 0,
  tot_item_val      DECIMAL(14,2) NOT NULL DEFAULT 0,
  -- BchDtls — OPTIONAL in the standard. Frequently absent.
  batch_no          VARCHAR(50)       NULL,
  expiry_date       DATE              NULL,
  -- Never present in an e-invoice. Filled from another source or by the
  -- chemist. This column is the reason a confirmation screen exists.
  mrp               DECIMAL(14,4)     NULL,
  mrp_source        ENUM('EINVOICE','DISTRIBUTOR_FILE','PDF','LAST_BATCH','CHEMIST','NONE')
                    NOT NULL DEFAULT 'NONE',
  -- Matching outcome (see Section B)
  matched_item_id   CHAR(26)          NULL,
  match_confidence  DECIMAL(5,2)      NULL COMMENT '0-100',
  match_method      VARCHAR(30)       NULL,
  match_status      ENUM('PENDING','AUTO','SUGGESTED','MANUAL','UNMATCHED','REJECTED')
                    NOT NULL DEFAULT 'PENDING',
  needs_attention   TINYINT(1)    NOT NULL DEFAULT 0
                    COMMENT '1 when MRP, batch or expiry is missing — drives the confirm screen',
  posted_line_id    CHAR(26)          NULL COMMENT 'purchase_invoice_line once posted',
  created_at        DATETIME(3)   NOT NULL,
  updated_at        DATETIME(3)   NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_psl (inbound_doc_id, sl_no),
  KEY ix_psl_match (store_id, match_status, needs_attention),
  KEY ix_psl_item (matched_item_id),
  KEY ix_psl_barcode (barcode),
  CONSTRAINT fk_psl_doc FOREIGN KEY (inbound_doc_id) REFERENCES purch_inbound_doc(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


-- =====================================================================
--  SECTION B — ITEM MATCHING & ALIAS LEARNING
--
--  THIS IS THE COMPOUNDING ASSET.
--
--  A distributor writes "SUN TELMA-40 TAB 15'S" where our catalogue says
--  "Telmisartan 40mg Tablet". No amount of string cleverness gets that
--  to 100%. But the chemist only has to resolve it ONCE: the confirmed
--  pairing is stored here, and every future invoice from that supplier
--  matches instantly.
--
--  Aliases are learned per supplier, and promoted to platform scope once
--  enough independent tenants agree. Store 1 starts around 60% auto-match
--  and climbs; store 2,000 starts near 95% on day one because it inherits
--  what everyone else confirmed.
--
--  An on-premise competitor cannot build this. Their stores never pool
--  anything. This is the structural advantage — not the UI.
-- =====================================================================

-- ---------------------------------------------------------------------
-- B.1 Supplier item alias — the learned mapping
-- ---------------------------------------------------------------------
CREATE TABLE supplier_item_alias (
  id                CHAR(26)      NOT NULL,
  scope             ENUM('STORE','TENANT','PLATFORM') NOT NULL DEFAULT 'STORE',
  tenant_id         CHAR(26)          NULL COMMENT 'NULL when scope = PLATFORM',
  store_id          CHAR(26)          NULL,
  supplier_gstin    VARCHAR(15)       NULL COMMENT 'NULL = applies to any supplier',
  -- The lookup key: aggressively normalised supplier text
  raw_desc_norm     VARCHAR(255)  NOT NULL,
  raw_desc_sample   VARCHAR(300)      NULL COMMENT 'One real example, for review screens',
  item_id           CHAR(26)      NOT NULL,
  -- Evidence
  confirm_count     INT UNSIGNED  NOT NULL DEFAULT 1,
  distinct_tenants  INT UNSIGNED  NOT NULL DEFAULT 1,
  reject_count      INT UNSIGNED  NOT NULL DEFAULT 0,
  first_seen        DATETIME(3)   NOT NULL,
  last_used         DATETIME(3)       NULL,
  promoted_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_sia (scope, store_id, supplier_gstin, raw_desc_norm),
  KEY ix_sia_lookup (raw_desc_norm, scope, is_active),
  KEY ix_sia_item (item_id),
  KEY ix_sia_promote (scope, distinct_tenants, confirm_count),
  CONSTRAINT fk_sia_item FOREIGN KEY (item_id) REFERENCES item(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


-- ---------------------------------------------------------------------
-- B.2 Match audit — every decision, kept.
--     Without this we cannot tune thresholds, and we cannot answer
--     "why did it pick that item" when a chemist disputes a posting.
-- ---------------------------------------------------------------------
CREATE TABLE purch_match_log (
  id                CHAR(26)      NOT NULL,
  staged_line_id    CHAR(26)      NOT NULL,
  store_id          CHAR(26)      NOT NULL,
  candidate_item_id CHAR(26)          NULL,
  score             DECIMAL(5,2)  NOT NULL DEFAULT 0,
  method            VARCHAR(30)   NOT NULL,
  score_detail      JSON              NULL COMMENT 'Component scores, for tuning',
  rank_pos          SMALLINT      NOT NULL DEFAULT 1,
  was_chosen        TINYINT(1)    NOT NULL DEFAULT 0,
  chemist_override  TINYINT(1)    NOT NULL DEFAULT 0,
  created_at        DATETIME(3)   NOT NULL,
  PRIMARY KEY (id),
  KEY ix_pml_line (staged_line_id, rank_pos),
  KEY ix_pml_tuning (method, score, was_chosen),
  KEY ix_pml_override (store_id, chemist_override, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


-- ---------------------------------------------------------------------
-- B.3 Pack-size synonyms. Distributors write pack size a dozen ways:
--     15'S, 15S, 1*15, 10X10, STRIP OF 15. Normalising these correctly
--     is worth several points of auto-match rate on its own.
-- ---------------------------------------------------------------------
CREATE TABLE pack_synonym (
  id            CHAR(26)      NOT NULL,
  raw_token     VARCHAR(40)   NOT NULL,
  norm_token    VARCHAR(40)   NOT NULL,
  units         DECIMAL(10,3)     NULL,
  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_pack_syn (raw_token)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


-- =====================================================================
--  SECTION C — GSTR-2B RECONCILIATION
--
--  Two jobs, and the second is the one chemists will actually pay for:
--    1. Catch purchases that arrived as goods but never as a document.
--    2. Catch input tax credit the chemist is entitled to and is losing.
--
--  Marg users still do this in Excel. It is a monthly, painful, money
--  task — which makes it the strongest single reason to switch.
-- =====================================================================

-- ---------------------------------------------------------------------
-- C.1 2B period pull
-- ---------------------------------------------------------------------
CREATE TABLE gstr2b_period (
  id              CHAR(26)      NOT NULL,
  tenant_id       CHAR(26)      NOT NULL,
  store_id        CHAR(26)      NOT NULL,
  gstin           VARCHAR(15)   NOT NULL,
  ret_period      CHAR(6)       NOT NULL COMMENT 'MMYYYY',
  gen_date        DATE              NULL,
  status          ENUM('PENDING','FETCHED','RECONCILED','FAILED') NOT NULL DEFAULT 'PENDING',
  invoice_count   INT UNSIGNED  NOT NULL DEFAULT 0,
  total_taxable   DECIMAL(16,2) NOT NULL DEFAULT 0,
  total_tax       DECIMAL(16,2) NOT NULL DEFAULT 0,
  fetch_error     VARCHAR(500)      NULL,
  fetched_at      DATETIME(3)       NULL,
  reconciled_at   DATETIME(3)       NULL,
  created_at      DATETIME(3)   NOT NULL,
  updated_at      DATETIME(3)   NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_2bp (store_id, gstin, ret_period),
  KEY ix_2bp_status (status, ret_period)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


-- ---------------------------------------------------------------------
-- C.2 2B invoice row
-- ---------------------------------------------------------------------
CREATE TABLE gstr2b_invoice (
  id                CHAR(26)      NOT NULL,
  period_id         CHAR(26)      NOT NULL,
  store_id          CHAR(26)      NOT NULL,
  supplier_gstin    VARCHAR(15)   NOT NULL,
  supplier_name     VARCHAR(200)      NULL,
  inv_no            VARCHAR(50)   NOT NULL,
  inv_date          DATE          NOT NULL,
  inv_type          VARCHAR(10)       NULL COMMENT 'R, SEZ, DE, etc.',
  taxable_value     DECIMAL(14,2) NOT NULL DEFAULT 0,
  cgst              DECIMAL(14,2) NOT NULL DEFAULT 0,
  sgst              DECIMAL(14,2) NOT NULL DEFAULT 0,
  igst              DECIMAL(14,2) NOT NULL DEFAULT 0,
  cess              DECIMAL(14,2) NOT NULL DEFAULT 0,
  total_value       DECIMAL(14,2) NOT NULL DEFAULT 0,
  itc_eligible      VARCHAR(10)       NULL,
  -- Reconciliation outcome
  match_status      ENUM('UNMATCHED','EXACT','TOLERANCE','MISMATCH','MISSING_IN_BOOKS','EXTRA_IN_BOOKS')
                    NOT NULL DEFAULT 'UNMATCHED',
  matched_purch_id  CHAR(26)          NULL,
  variance_amount   DECIMAL(14,2) NOT NULL DEFAULT 0,
  variance_reason   VARCHAR(200)      NULL,
  action_taken      ENUM('NONE','ACCEPTED','DISPUTED','SUPPLIER_NOTIFIED','WRITTEN_OFF')
                    NOT NULL DEFAULT 'NONE',
  created_at        DATETIME(3)   NOT NULL,
  updated_at        DATETIME(3)   NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_2bi (period_id, supplier_gstin, inv_no, inv_date),
  KEY ix_2bi_match (store_id, match_status),
  KEY ix_2bi_purch (matched_purch_id),
  KEY ix_2bi_supplier (supplier_gstin, inv_date),
  CONSTRAINT fk_2bi_period FOREIGN KEY (period_id) REFERENCES gstr2b_period(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


-- ---------------------------------------------------------------------
-- C.3 Reconciliation exceptions — the chemist's actual worklist.
--     If this list is long and unhelpful, the feature is a burden.
--     It must be short, sorted by rupees at stake, and actionable.
-- ---------------------------------------------------------------------
CREATE TABLE recon_exception (
  id              CHAR(26)      NOT NULL,
  tenant_id       CHAR(26)      NOT NULL,
  store_id        CHAR(26)      NOT NULL,
  period_id       CHAR(26)          NULL,
  kind            ENUM('IN_2B_NOT_IN_BOOKS','IN_BOOKS_NOT_IN_2B','VALUE_MISMATCH',
                       'TAX_MISMATCH','DUPLICATE_BOOKING','GSTIN_MISMATCH',
                       'GOODS_NO_INVOICE') NOT NULL,
  severity        ENUM('INFO','WARN','ACTION') NOT NULL DEFAULT 'ACTION',
  supplier_gstin  VARCHAR(15)       NULL,
  supplier_name   VARCHAR(200)      NULL,
  doc_no          VARCHAR(50)       NULL,
  doc_date        DATE              NULL,
  amount_at_stake DECIMAL(14,2) NOT NULL DEFAULT 0 COMMENT 'Sort the worklist by this',
  detail          VARCHAR(500)      NULL,
  suggested_action VARCHAR(300)     NULL,
  status          ENUM('OPEN','IN_PROGRESS','RESOLVED','IGNORED') NOT NULL DEFAULT 'OPEN',
  resolved_by     CHAR(26)          NULL,
  resolved_at     DATETIME(3)       NULL,
  created_at      DATETIME(3)   NOT NULL,
  updated_at      DATETIME(3)   NOT NULL,
  PRIMARY KEY (id),
  KEY ix_re_worklist (store_id, status, amount_at_stake DESC),
  KEY ix_re_kind (store_id, kind, status),
  KEY ix_re_period (period_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


-- =====================================================================
--  SECTION D — OPERATIONAL METRICS
--
--  Rule S11: support contact rate is a tracked product KPI. Auto-match
--  rate is its leading indicator — it moves weeks before the support
--  calls do. Instrument it from day one or you will be guessing.
-- =====================================================================

CREATE TABLE purch_automation_stat (
  id                  CHAR(26)      NOT NULL,
  store_id            CHAR(26)      NOT NULL,
  stat_date           DATE          NOT NULL,
  docs_received       INT UNSIGNED  NOT NULL DEFAULT 0,
  docs_parsed         INT UNSIGNED  NOT NULL DEFAULT 0,
  lines_total         INT UNSIGNED  NOT NULL DEFAULT 0,
  lines_auto          INT UNSIGNED  NOT NULL DEFAULT 0,
  lines_suggested     INT UNSIGNED  NOT NULL DEFAULT 0,
  lines_manual        INT UNSIGNED  NOT NULL DEFAULT 0,
  lines_missing_mrp   INT UNSIGNED  NOT NULL DEFAULT 0,
  lines_missing_batch INT UNSIGNED  NOT NULL DEFAULT 0,
  overrides           INT UNSIGNED  NOT NULL DEFAULT 0
                      COMMENT 'Auto-match the chemist corrected — the accuracy signal',
  avg_confirm_seconds DECIMAL(8,2)      NULL,
  created_at          DATETIME(3)   NOT NULL,
  updated_at          DATETIME(3)   NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_pas (store_id, stat_date),
  KEY ix_pas_date (stat_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


SET FOREIGN_KEY_CHECKS = 1;

-- =====================================================================
--  ACCEPTANCE TARGETS for this module — these are contract, not hope.
--
--    Auto-match rate, new store, week 1        >= 60%
--    Auto-match rate, new store, week 8        >= 90%
--    Auto-match rate, mature platform, day 1   >= 85%  (inherited aliases)
--    False auto-match (chemist override) rate  <  1%
--    Confirm time per invoice, 20 lines        <  60 seconds
--    Purchase-related support contacts         <  0.2 per store per month
--
--  The override rate matters more than the match rate. A wrong auto-post
--  puts bad stock and a bad MRP on the shelf, and the chemist finds out
--  at the counter in front of a customer. Bias the thresholds toward
--  asking. Confirming is cheap; being wrong is not.
-- =====================================================================