-- =====================================================================
--  RETAIL PHARMACY PLATFORM
--  Phase 2 Schema Pack G — Public surface hardening v1.0
--
--  Apply AFTER Phase 2 packs E and F.
--  Target: MySQL 8.0+ / InnoDB / utf8mb4
--
--  =================================================================
--  THIS IS THE ONLY PART THAT FACES THE OPEN INTERNET
--  =================================================================
--
--  Every other surface sits behind a login. The storefront cannot: a
--  patient ordering medicine at 10pm has no account and will not make
--  one. So the controls have to come from somewhere other than
--  authentication.
--
--  Three specific risks, and what answers each:
--
--   1. ENUMERATION. Order numbers are guessable, so anyone could walk
--      them and read a stranger's prescription. Answered by a per-order
--      secret that never appears in a URL we generate for anyone else,
--      and by never confirming whether a number exists.
--
--   2. ABUSE. An open ordering endpoint is a free way to spam a
--      chemist's counter, or to send OTPs to a number that did not ask
--      for them. Answered by rate limits on the mobile AND the IP, and
--      by a per-shop daily ceiling.
--
--   3. LEAKAGE. A public response that includes a customer name, a
--      previous order, or "this mobile is already registered" hands
--      over information nobody asked to publish.
--
--  =================================================================
--  WHY THE OTP TABLE STORES A HASH
--  =================================================================
--
--  A six-digit code is not a secret worth much, but the table is worth
--  a great deal to anyone who reads the database: it maps mobile
--  numbers to the pharmacies they order from, which is health data by
--  implication. The code is hashed, and the row is deleted once used.
-- =====================================================================

SET NAMES utf8mb4;

CREATE TABLE public_otp (
  id            CHAR(26)      NOT NULL,
  store_id      CHAR(26)      NOT NULL,
  mobile        VARCHAR(15)   NOT NULL,
  code_hash     CHAR(64)      NOT NULL COMMENT 'Never the code itself',
  purpose       ENUM('ORDER','TRACK') NOT NULL DEFAULT 'ORDER',
  attempts      TINYINT       NOT NULL DEFAULT 0,
  issued_at     DATETIME(3)   NOT NULL,
  expires_at    DATETIME(3)   NOT NULL,
  consumed_at   DATETIME(3)       NULL,
  ip_address    VARCHAR(45)       NULL,
  PRIMARY KEY (id),
  KEY ix_po_lookup (store_id, mobile, purpose, expires_at),
  KEY ix_po_expiry (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


-- ---------------------------------------------------------------------
--  A short-lived pass, issued only after an OTP is proven.
--  It is scoped to ONE mobile at ONE shop and nothing else.
-- ---------------------------------------------------------------------
CREATE TABLE public_session (
  id            CHAR(26)      NOT NULL,
  token_hash    CHAR(64)      NOT NULL,
  store_id      CHAR(26)      NOT NULL,
  mobile        VARCHAR(15)   NOT NULL,
  issued_at     DATETIME(3)   NOT NULL,
  expires_at    DATETIME(3)   NOT NULL,
  revoked_at    DATETIME(3)       NULL,
  ip_address    VARCHAR(45)       NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_ps_token (token_hash),
  KEY ix_ps_mobile (store_id, mobile, expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


-- ---------------------------------------------------------------------
--  Rate limiting. Counted per subject per window, so one abusive
--  mobile does not lock out a whole apartment block behind one NAT,
--  and one abusive IP cannot be dodged by changing the number.
-- ---------------------------------------------------------------------
CREATE TABLE rate_bucket (
  id            CHAR(26)      NOT NULL,
  scope         VARCHAR(30)   NOT NULL COMMENT 'otp.request | order.place | track.attempt',
  subject_type  ENUM('MOBILE','IP','STORE') NOT NULL,
  subject       VARCHAR(64)   NOT NULL,
  window_start  DATETIME(3)   NOT NULL,
  window_secs   INT UNSIGNED  NOT NULL,
  hits          INT UNSIGNED  NOT NULL DEFAULT 0,
  blocked_until DATETIME(3)       NULL,
  updated_at    DATETIME(3)   NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_rb (scope, subject_type, subject, window_start),
  KEY ix_rb_block (subject_type, subject, blocked_until)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


-- ---------------------------------------------------------------------
--  Per-order secret, so a tracking link cannot be guessed from the
--  order number and cannot be walked from one order to the next.
-- ---------------------------------------------------------------------
ALTER TABLE online_order
  ADD COLUMN track_secret CHAR(32) NULL
      COMMENT 'Random; included only in messages to the ordering mobile' AFTER order_no,
  ADD KEY ix_oo_track (order_no, track_secret);

-- =====================================================================
--  INVARIANTS asserted in tests:
--
--   1. Placing an order requires a proven mobile.
--   2. An order cannot be read with only its order number.
--   3. A tracking pass for one mobile cannot read another's order.
--   4. OTP requests are rate limited per mobile AND per IP.
--   5. A wrong OTP is limited; guessing is not viable.
--   6. Codes are stored hashed and consumed on use.
--   7. No public response reveals whether a mobile is a known customer.
-- =====================================================================