-- =====================================================================
--  RETAIL PHARMACY PLATFORM
--  Schema Pack K — Authentication & API v1.0
--
--  Apply AFTER schema-phase0-core-v1.sql.
--  Target: MySQL 8.0+ / InnoDB / utf8mb4
--
--  =================================================================
--  WHAT THIS CLOSES
--  =================================================================
--
--  app_user has existed since Phase 0, with roles and a password hash
--  column. Nothing has ever checked either. Every module built so far
--  trusts its caller completely: pass a store id and you get that
--  store's data, whoever you are.
--
--  That is fine while the only caller is a test suite. It stops being
--  fine the moment a screen is wired to a URL.
--
--  =================================================================
--  THE ONE THAT MATTERS MOST
--  =================================================================
--
--  Tenant isolation is enforced in the ROUTER, not in each handler.
--
--  Nineteen modules take a $storeId in their constructor and trust it.
--  If isolation depended on every handler remembering to check, then
--  one forgotten check in one endpoint, two years from now, exposes one
--  chemist's entire business to another. So the router resolves the
--  store from the SESSION, never from the request, and hands handlers a
--  scope they cannot widen.
--
--  A handler that wants a different store cannot ask for one.
-- =====================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;


-- ---------------------------------------------------------------------
-- K.1 Session — server-side, so it can be revoked
--
--  The cookie holds a random id and nothing else. No role, no store, no
--  user: a cookie a client can read is a cookie a client will
--  eventually forge.
-- ---------------------------------------------------------------------
CREATE TABLE user_session (
  id              CHAR(26)      NOT NULL,
  -- SHA-256 of the cookie value. A stolen database must not yield
  -- usable session cookies.
  token_hash      CHAR(64)      NOT NULL,
  user_id         CHAR(26)      NOT NULL,
  tenant_id       CHAR(26)          NULL COMMENT 'NULL for platform staff',
  store_id        CHAR(26)          NULL COMMENT 'The store this session is scoped to',
  counter_id      CHAR(26)          NULL,
  role_code       VARCHAR(30)   NOT NULL COMMENT 'Snapshotted at login',
  csrf_token      CHAR(64)      NOT NULL,
  ip_address      VARCHAR(45)       NULL,
  user_agent      VARCHAR(300)      NULL,
  issued_at       DATETIME(3)   NOT NULL,
  last_seen_at    DATETIME(3)   NOT NULL,
  expires_at      DATETIME(3)   NOT NULL,
  revoked_at      DATETIME(3)       NULL,
  revoked_reason  VARCHAR(120)      NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_us_token (token_hash),
  KEY ix_us_user (user_id, revoked_at),
  KEY ix_us_expiry (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


-- ---------------------------------------------------------------------
-- K.2 Machine tokens — the Counter Helper and the sync daemon
--
--  These are NOT user sessions. A helper token can print; it cannot
--  read a customer list. Separate table, separate scopes, so the two
--  can never be confused by a future change.
-- ---------------------------------------------------------------------
CREATE TABLE api_token (
  id              CHAR(26)      NOT NULL,
  token_hash      CHAR(64)      NOT NULL,
  token_type      ENUM('NODE','HELPER','PARTNER','PLATFORM') NOT NULL,
  label           VARCHAR(120)  NOT NULL,
  tenant_id       CHAR(26)          NULL,
  store_id        CHAR(26)          NULL,
  counter_id      CHAR(26)          NULL,
  scopes          VARCHAR(400)  NOT NULL COMMENT 'Comma separated; never "*"',
  last_used_at    DATETIME(3)       NULL,
  expires_at      DATETIME(3)       NULL,
  revoked_at      DATETIME(3)       NULL,
  created_by      CHAR(26)          NULL,
  created_at      DATETIME(3)   NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_at_token (token_hash),
  KEY ix_at_scope (token_type, store_id, revoked_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


-- ---------------------------------------------------------------------
-- K.3 Login attempts — throttling, and the record of who tried
-- ---------------------------------------------------------------------
CREATE TABLE login_attempt (
  id            CHAR(26)      NOT NULL,
  login_id      VARCHAR(80)   NOT NULL,
  ip_address    VARCHAR(45)       NULL,
  succeeded     TINYINT(1)    NOT NULL DEFAULT 0,
  failure       VARCHAR(40)       NULL COMMENT 'BAD_PASSWORD | NO_USER | LOCKED | INACTIVE',
  attempted_at  DATETIME(3)   NOT NULL,
  PRIMARY KEY (id),
  KEY ix_la_login (login_id, attempted_at),
  KEY ix_la_ip (ip_address, attempted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


-- ---------------------------------------------------------------------
-- K.4 API audit — every state change, who made it, from where
--
--  Reads are not logged; there would be millions and nobody would ever
--  look. Writes are, because "who cancelled that bill" is a question
--  that gets asked and currently has no answer.
-- ---------------------------------------------------------------------
CREATE TABLE api_audit (
  id            CHAR(26)      NOT NULL,
  tenant_id     CHAR(26)          NULL,
  store_id      CHAR(26)          NULL,
  actor_type    ENUM('USER','NODE','HELPER','PUBLIC') NOT NULL,
  actor_id      CHAR(26)          NULL,
  role_code     VARCHAR(30)       NULL,
  route         VARCHAR(120)  NOT NULL,
  method        VARCHAR(8)    NOT NULL,
  status_code   SMALLINT      NOT NULL,
  entity_type   VARCHAR(40)       NULL,
  entity_id     CHAR(26)          NULL,
  ip_address    VARCHAR(45)       NULL,
  duration_ms   INT UNSIGNED  NOT NULL DEFAULT 0,
  error         VARCHAR(300)      NULL,
  created_at    DATETIME(3)   NOT NULL,
  PRIMARY KEY (id),
  KEY ix_aa_store (store_id, created_at),
  KEY ix_aa_actor (actor_id, created_at),
  KEY ix_aa_route (route, status_code, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


SET FOREIGN_KEY_CHECKS = 1;

-- =====================================================================
--  INVARIANTS asserted in tests:
--
--   1. No route may be reached without an authenticated principal,
--      except those explicitly marked public.
--   2. A user of tenant A can never read or write tenant B's data,
--      even when passing B's ids explicitly.
--   3. Scope comes from the SESSION, never from the request.
--   4. Role permissions are enforced centrally; a handler cannot be
--      reached by a role the route does not allow.
--   5. Repeated bad passwords lock the account.
--   6. A browser POST without a valid CSRF token is rejected.
--   7. Session id regenerates on login (no fixation).
--   8. A helper token cannot call a user route, and vice versa.
--   9. No response ever contains a password hash or a token.
-- =====================================================================