-- =====================================================================
--  RETAIL PHARMACY PLATFORM
--  Phase 1 Schema Pack D — Data Migration v1.0
--
--  Apply AFTER packs A and C.
--  Target: MySQL 8.0+ / InnoDB / utf8mb4
--
--  =================================================================
--  READ THIS BEFORE WRITING ANY IMPORT CODE
--  =================================================================
--
--  This module deliberately does NOT read the incumbent's database
--  files. No DBF reader, no reverse-engineered table layouts, no
--  decryption of anyone's proprietary format. Not now, not later, not
--  "just for testing".
--
--  Two reasons, and the second is the serious one:
--
--  1. Reverse-engineering a competitor's file format runs into their
--     licence terms and into trade-secret law, regardless of the fact
--     that the DATA belongs to the chemist.
--
--  2. This company now employs the incumbent's former sales head. That
--     single fact changes the risk profile completely. A plaintiff does
--     not have to prove we used their confidential material — they only
--     have to make it plausible to a judge deciding on an injunction.
--     A parser for their file format, sitting in our repository, is the
--     exhibit that makes that argument for them. An injunction at month
--     eight would cost more than this entire build.
--
--  THE SAFE ROUTE, which is also the one that actually works:
--  import through channels the chemist controls and can run himself.
--
--    * Exports the incumbent's own software produces (Excel / CSV),
--      run by the chemist, from his own licensed installation
--    * Tally XML, where the chemist already pushes his books there
--    * His own GSTN data — returns, 2B, e-invoices
--    * A published CSV template for anything else
--
--  This is not a weaker product. The chemist runs three exports and
--  uploads them; we never touch a file we have no right to parse.
--  Have counsel confirm the export path in writing before go-live.
-- =====================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;


-- ---------------------------------------------------------------------
-- D.1 Import batch — one uploaded file
-- ---------------------------------------------------------------------
CREATE TABLE import_batch (
  id              CHAR(26)      NOT NULL,
  tenant_id       CHAR(26)      NOT NULL,
  store_id        CHAR(26)      NOT NULL,
  profile_code    VARCHAR(30)   NOT NULL COMMENT 'ITEM_STOCK | CUSTOMER | SUPPLIER | LEDGER_OPENING',
  source_label    VARCHAR(100)      NULL COMMENT 'What the chemist said it is',
  file_name       VARCHAR(200)      NULL,
  file_sha256     CHAR(64)      NOT NULL COMMENT 'Same file twice is the same batch',
  row_count       INT UNSIGNED  NOT NULL DEFAULT 0,
  -- Lifecycle. DRY_RUN is mandatory; a batch can only be committed from
  -- VALIDATED. There is no path that writes live data without a preview.
  status          ENUM('UPLOADED','VALIDATING','VALIDATED','COMMITTING',
                       'COMMITTED','FAILED','ABANDONED') NOT NULL DEFAULT 'UPLOADED',
  rows_ok         INT UNSIGNED  NOT NULL DEFAULT 0,
  rows_warning    INT UNSIGNED  NOT NULL DEFAULT 0,
  rows_error      INT UNSIGNED  NOT NULL DEFAULT 0,
  rows_committed  INT UNSIGNED  NOT NULL DEFAULT 0,
  auto_mapped     INT UNSIGNED  NOT NULL DEFAULT 0,
  manual_mapped   INT UNSIGNED  NOT NULL DEFAULT 0,
  value_total     DECIMAL(16,2) NOT NULL DEFAULT 0 COMMENT 'For the reconciliation report',
  fail_reason     VARCHAR(500)      NULL,
  uploaded_by     CHAR(26)          NULL,
  uploaded_at     DATETIME(3)   NOT NULL,
  validated_at    DATETIME(3)       NULL,
  committed_at    DATETIME(3)       NULL,
  created_at      DATETIME(3)   NOT NULL,
  updated_at      DATETIME(3)   NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_ib_file (store_id, file_sha256, profile_code),
  KEY ix_ib_status (store_id, status, uploaded_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


-- ---------------------------------------------------------------------
-- D.2 Staged row — the file as received, plus what we made of it
-- ---------------------------------------------------------------------
CREATE TABLE import_row (
  id              CHAR(26)      NOT NULL,
  batch_id        CHAR(26)      NOT NULL,
  store_id        CHAR(26)      NOT NULL,
  row_no          INT UNSIGNED  NOT NULL COMMENT 'Line in the file, for the error report',
  raw_json        TEXT          NOT NULL COMMENT 'Original cells, untouched',
  -- Normalised interpretation
  norm_json       TEXT              NULL,
  matched_item_id CHAR(26)          NULL,
  match_confidence DECIMAL(5,2)     NULL,
  match_method    VARCHAR(30)       NULL,
  severity        ENUM('OK','WARNING','ERROR') NOT NULL DEFAULT 'OK',
  messages        VARCHAR(1000)     NULL,
  is_committed    TINYINT(1)    NOT NULL DEFAULT 0,
  created_row_id  CHAR(26)          NULL COMMENT 'batch / customer / supplier created',
  created_at      DATETIME(3)   NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_ir (batch_id, row_no),
  KEY ix_ir_sev (batch_id, severity),
  KEY ix_ir_commit (batch_id, is_committed),
  CONSTRAINT fk_ir_batch FOREIGN KEY (batch_id) REFERENCES import_batch(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


-- ---------------------------------------------------------------------
-- D.3 Column mapping — every export names its columns differently.
--     Learned once per source layout, then reused for every store that
--     uploads the same shape of file.
-- ---------------------------------------------------------------------
CREATE TABLE import_column_map (
  id              CHAR(26)      NOT NULL,
  profile_code    VARCHAR(30)   NOT NULL,
  header_norm     VARCHAR(120)  NOT NULL COMMENT 'Lowercased, punctuation-stripped header',
  target_field    VARCHAR(40)   NOT NULL,
  confirm_count   INT UNSIGNED  NOT NULL DEFAULT 1,
  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_icm (profile_code, header_norm),
  KEY ix_icm_target (target_field)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


SET FOREIGN_KEY_CHECKS = 1;

-- =====================================================================
--  ACCEPTANCE TARGETS
--
--    Item auto-map rate on a real export        >= 85%
--    Rows lost silently                          0   (every row is
--                                                     committed, warned,
--                                                     or errored — never
--                                                     dropped)
--    Opening stock value vs the chemist's own
--      closing figure                            reconciles to the rupee
--    Opening trial balance                       Dr = Cr, exactly
--    Re-uploading the same file                  changes nothing
--    Migration time, 3,000 SKUs                  < 30 minutes end to end
--
--  THE NON-NEGOTIABLE ONE IS "rows lost silently". A chemist who finds
--  out three weeks later that 60 SKUs never came across will never trust
--  the system again, and he will tell the market.
-- =====================================================================