-- =====================================================================
--  RETAIL PHARMACY PLATFORM
--  Phase 4 Schema Pack A — Partner identity v1.0
--
--  Apply AFTER pack K (auth).
--  Target: MySQL 8.0+ / InnoDB / utf8mb4
--
--  =================================================================
--  WHY A PARTNER IS NOT A STORE USER
--  =================================================================
--
--  Every principal so far resolves to ONE store, and that single fact
--  is what keeps one chemist's business away from another's. A partner
--  breaks that shape: he looks after forty shops across forty different
--  tenants, and none of them is "his".
--
--  The tempting shortcut is to give him a store user account, or one
--  per shop. That would quietly turn the isolation rule from "one
--  session, one store" into "one session, whichever store the request
--  asked for", and every guarantee downstream would rest on each
--  handler remembering to check. It is the wrong fix and it weakens the
--  thing most worth protecting.
--
--  So a partner is a DIFFERENT KIND of principal, with an explicit list
--  of stores and a deliberately narrow set of things he may see.
--
--  =================================================================
--  WHAT A PARTNER MAY NEVER SEE
--  =================================================================
--
--  Not one customer name. Not one prescription. Not one bill line.
--
--  A chemist's single biggest fear about cloud software is
--  disintermediation — that whoever runs the platform will end up
--  owning his customers. A partner browsing patient records would be
--  that fear proved right, and it would also be health data handed to a
--  third party under the DPDP Act.
--
--  He sees what he needs to keep stores alive and get paid: whether the
--  shop is billing, whether backups and syncs are working, how much
--  support it is consuming, and what he has earned. Counts and health,
--  never contents.
-- =====================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;


-- ---------------------------------------------------------------------
--  `partner` ALREADY EXISTS, from the Phase 0 core pack.
--
--  I wrote this pack as if it did not, and the migrator refused it —
--  correctly, naming the statement and the reason. That is the whole
--  point of a declared order with a real runner: the collision surfaced
--  in three seconds instead of on a store.
--
--  So this pack EXTENDS the existing table rather than redefining it.
--  Its commission_pct is the revenue share; there is no reason for two
--  columns meaning the same thing, and a second one would eventually
--  disagree with the first.
-- ---------------------------------------------------------------------
ALTER TABLE partner
  ADD COLUMN status ENUM('ACTIVE','SUSPENDED','ENDED') NOT NULL DEFAULT 'ACTIVE'
      COMMENT 'Suspended keeps the history and removes the access' AFTER agreement_to,
  ADD KEY ix_p_status (status, is_active);


CREATE TABLE partner_user (
  id              CHAR(26)      NOT NULL,
  partner_id      CHAR(26)      NOT NULL,
  login_id        VARCHAR(80)   NOT NULL,
  full_name       VARCHAR(150)  NOT NULL,
  mobile          VARCHAR(15)       NULL,
  password_hash   VARCHAR(255)  NOT NULL,
  -- STAFF can look; OWNER can also change a store's update ring and
  -- pause updates. Neither can touch anything a shop trades on.
  role_code       ENUM('PARTNER_OWNER','PARTNER_STAFF') NOT NULL DEFAULT 'PARTNER_STAFF',
  failed_attempts SMALLINT      NOT NULL DEFAULT 0,
  locked_until    DATETIME(3)       NULL,
  last_login_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_pu_login (login_id),
  KEY ix_pu_partner (partner_id, is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


-- ---------------------------------------------------------------------
--  Which stores a partner looks after. An explicit list, revocable the
--  moment an agreement ends — a partner who has been let go must lose
--  access on the day, not at the next password change.
-- ---------------------------------------------------------------------
CREATE TABLE partner_store (
  id              CHAR(26)      NOT NULL,
  partner_id      CHAR(26)      NOT NULL,
  store_id        CHAR(26)      NOT NULL,
  onboarded_on    DATE              NULL,
  -- The chemist can withdraw this. It is his shop, and a partner
  -- relationship he did not choose is not one he should be stuck with.
  consent_given   TINYINT(1)    NOT NULL DEFAULT 1,
  consent_note    VARCHAR(200)      NULL,
  ended_on        DATE              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_ps (partner_id, store_id),
  KEY ix_ps_store (store_id, is_active),
  KEY ix_ps_active (partner_id, is_active, consent_given)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


SET FOREIGN_KEY_CHECKS = 1;

-- =====================================================================
--  INVARIANTS asserted in tests:
--
--   1. A partner sees only stores explicitly assigned to him.
--   2. A partner can NEVER read a customer, a bill line or a
--      prescription — for any store, including his own.
--   3. A partner cannot write anything a shop trades on.
--   4. A store user cannot use partner routes, and vice versa.
--   5. Ending an assignment removes access immediately.
--   6. A chemist withdrawing consent removes access immediately.
-- =====================================================================