-- =====================================================================
--  RETAIL PHARMACY PLATFORM
--  Phase 3 Schema Pack B — Loyalty & Rule Governance v1.0
--
--  Apply AFTER Phase 3 pack A.
--  Target: MySQL 8.0+ / InnoDB / utf8mb4
--
--  =================================================================
--  POINTS ARE NOT EARNED ON MEDICINE
--  =================================================================
--
--  The same rule as commission, for a sharper reason. A commission on
--  medicine costs the chemist margin. Loyalty points on medicine reward
--  a patient for buying MORE of it — and the patient does not choose
--  the quantity, the prescriber does. A scheme that pays people to
--  refill early or over-buy a Schedule H drug is not a marketing
--  programme, it is an inducement, and it puts the chemist's licence
--  somewhere he never agreed to put it.
--
--  So points accrue on the cross-sell basket only: OTC, nutrition,
--  devices, everyday care. Drug lines are recorded at zero rather than
--  omitted, so the customer and the chemist can both see the zero.
--
--  =================================================================
--  THE LEDGER IS APPEND-ONLY, LIKE EVERY OTHER LEDGER HERE
--  =================================================================
--
--  A points balance held as a single number is a number somebody will
--  eventually edit. Earned, redeemed, expired and adjusted are all
--  rows; the balance is their sum. When a customer says "I had 400
--  points last month", there is an answer.
-- =====================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;


-- ---------------------------------------------------------------------
-- B.1 Scheme configuration — per tenant
-- ---------------------------------------------------------------------
CREATE TABLE loyalty_scheme (
  id                  CHAR(26)      NOT NULL,
  tenant_id           CHAR(26)      NOT NULL,
  name                VARCHAR(120)  NOT NULL,
  -- Earning
  rupees_per_point    DECIMAL(10,2) NOT NULL DEFAULT 10 COMMENT 'Spend this to earn one point',
  point_value         DECIMAL(10,4) NOT NULL DEFAULT 0.25 COMMENT 'Rupees a point is worth on redemption',
  -- Redemption guards
  min_redeem_points   INT UNSIGNED  NOT NULL DEFAULT 200,
  max_redeem_pct      DECIMAL(5,2)  NOT NULL DEFAULT 20 COMMENT 'Of the eligible basket',
  -- Points that never expire are a liability that grows forever. Points
  -- that expire without warning are a complaint. Both are handled.
  expiry_months       SMALLINT      NOT NULL DEFAULT 24,
  warn_days_before    SMALLINT      NOT NULL DEFAULT 30,
  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_ls (tenant_id, name),
  KEY ix_ls_active (tenant_id, is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


-- ---------------------------------------------------------------------
-- B.2 Points ledger — APPEND ONLY. The balance is SUM(points).
-- ---------------------------------------------------------------------
CREATE TABLE loyalty_entry (
  id              CHAR(26)      NOT NULL,
  tenant_id       CHAR(26)      NOT NULL,
  store_id        CHAR(26)      NOT NULL,
  customer_id     CHAR(26)      NOT NULL,
  entry_type      ENUM('EARN','REDEEM','EXPIRE','CLAWBACK','ADJUST') NOT NULL,
  points          INT           NOT NULL COMMENT 'Signed: positive earns, negative spends',
  entry_date      DATE          NOT NULL,
  -- Provenance, so any single point can be traced to the bill that made it
  sale_bill_id    CHAR(26)          NULL,
  credit_note_id  CHAR(26)          NULL,
  bill_no         VARCHAR(30)       NULL,
  eligible_value  DECIMAL(14,2) NOT NULL DEFAULT 0 COMMENT 'The non-medicine basket it came from',
  drug_value      DECIMAL(14,2) NOT NULL DEFAULT 0 COMMENT 'Recorded so the zero is visible',
  redeem_value    DECIMAL(14,2) NOT NULL DEFAULT 0 COMMENT 'Rupees given back on a REDEEM',
  expires_on      DATE              NULL COMMENT 'On EARN rows only',
  expired_from    CHAR(26)          NULL COMMENT 'On EXPIRE rows: the EARN it retired',
  note            VARCHAR(200)      NULL,
  user_id         CHAR(26)          NULL,
  created_at      DATETIME(3)   NOT NULL,
  PRIMARY KEY (id),
  KEY ix_le_customer (customer_id, entry_date),
  KEY ix_le_expiry (tenant_id, entry_type, expires_on),
  KEY ix_le_bill (sale_bill_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


-- ---------------------------------------------------------------------
-- B.3 Curated cross-sell rules need an approval path
--
--  The mining guards protect against a bad LEARNED rule. They do
--  nothing about a commercially aggressive CURATED one — a partner or
--  a store user pairing two high-margin items that have no business
--  being suggested together. Curation is a platform act, and it needs
--  a second pair of eyes and a name attached.
-- ---------------------------------------------------------------------
CREATE TABLE rule_proposal (
  id                CHAR(26)      NOT NULL,
  trigger_item_id   CHAR(26)      NOT NULL,
  suggest_item_id   CHAR(26)      NOT NULL,
  scope             ENUM('STORE','TENANT','PLATFORM') NOT NULL DEFAULT 'PLATFORM',
  tenant_id         CHAR(26)          NULL,
  store_id          CHAR(26)          NULL,
  rationale         VARCHAR(300)  NOT NULL COMMENT 'Why a human thinks this pairing is right',
  proposed_by       CHAR(26)      NOT NULL,
  proposed_role     VARCHAR(30)   NOT NULL COMMENT 'PLATFORM | PARTNER | STORE',
  status            ENUM('PENDING','APPROVED','REJECTED','AUTO_REJECTED') NOT NULL DEFAULT 'PENDING',
  -- Approver must differ from proposer. Enforced in code and tested.
  reviewed_by       CHAR(26)          NULL,
  reviewed_at       DATETIME(3)       NULL,
  review_note       VARCHAR(300)      NULL,
  rule_id           CHAR(26)          NULL COMMENT 'Created only on approval',
  created_at        DATETIME(3)   NOT NULL,
  updated_at        DATETIME(3)   NOT NULL,
  PRIMARY KEY (id),
  KEY ix_rp_status (status, created_at),
  KEY ix_rp_proposer (proposed_by, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


SET FOREIGN_KEY_CHECKS = 1;

-- =====================================================================
--  INVARIANTS asserted in tests:
--
--   1. No points are ever earned on a DRUG line — recorded as zero, not
--      omitted, so both parties can see it.
--   2. Balance always equals SUM(points) over the ledger.
--   3. Redemption is capped by the scheme AND by the eligible basket,
--      and can never exceed the balance.
--   4. Points may not be redeemed against medicine.
--   5. A return claws back the points that sale earned.
--   6. Expiry retires the OLDEST points first, and warns before it does.
--   7. A curated rule cannot be approved by the person who proposed it,
--      and is auto-rejected if it breaks the drug guards.
-- =====================================================================