-- =====================================================================
--  RETAIL PHARMACY PLATFORM
--  Phase 2 Schema Pack F — Messaging & Refills v1.0
--
--  Apply AFTER Phase 2 pack E.
--  Target: MySQL 8.0+ / InnoDB / utf8mb4
--
--  =================================================================
--  TWO EXTERNAL RULES THIS SCHEMA HAS TO OBEY
--  =================================================================
--
--  1. WHATSAPP. Business-initiated messages must use a template that
--     Meta has approved in advance. Approval takes days and can be
--     refused. Free-form text is only permitted inside the 24-hour
--     window after the customer last messaged you. A build that
--     assumes it can send arbitrary text will fail its first
--     compliance review, so templates are a first-class table here,
--     not a string in the code.
--
--  2. DPDP ACT 2023. A pharmacy customer's purchase history is health
--     data. Transactional messages (his own invoice, his own order
--     status) rest on the transaction. Marketing — refill reminders,
--     offers — needs separate, recorded, revocable consent. Those are
--     two different permissions and this schema keeps them apart.
--
--  The refill reminder is the commercial heart of the subscription:
--  it is what turns one purchase into twelve. It is also the message
--  most likely to annoy someone into leaving. Both facts argue for
--  the same thing — send it only to people who agreed, at a civilised
--  hour, and make stopping it trivially easy.
-- =====================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;


-- ---------------------------------------------------------------------
-- F.1 Message template — mirrors what Meta approved
-- ---------------------------------------------------------------------
CREATE TABLE message_template (
  id              CHAR(26)      NOT NULL,
  code            VARCHAR(60)   NOT NULL COMMENT 'ORDER_RECEIVED, PAY_LINK, INVOICE, REFILL_DUE, ...',
  channel         ENUM('WHATSAPP','EMAIL','SMS') NOT NULL,
  -- Category decides which consent applies. Getting this wrong is how a
  -- marketing message goes out under a transactional permission.
  category        ENUM('TRANSACTIONAL','MARKETING','AUTHENTICATION') NOT NULL,
  provider_name   VARCHAR(80)       NULL COMMENT 'Name registered with the provider',
  lang            VARCHAR(10)   NOT NULL DEFAULT 'en',
  body            TEXT          NOT NULL COMMENT 'Slots as {{1}}, {{2}} — provider order',
  var_map         VARCHAR(500)      NULL COMMENT 'Comma-separated payload keys, in slot order',
  subject         VARCHAR(200)      NULL COMMENT 'Email only',
  -- A template cannot be used until the provider has approved it.
  approval_status ENUM('DRAFT','SUBMITTED','APPROVED','REJECTED','PAUSED')
                  NOT NULL DEFAULT 'DRAFT',
  approval_note   VARCHAR(300)      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_mt (code, channel, lang),
  KEY ix_mt_status (approval_status, is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


-- ---------------------------------------------------------------------
-- F.2 Consent events — append-only.
--
--  Under DPDP the question is never "does he consent" but "prove when
--  he consented, to what, and how he was told he could stop". A boolean
--  on the customer row answers none of that, so the boolean is the
--  cache and this is the record.
-- ---------------------------------------------------------------------
CREATE TABLE consent_event (
  id            CHAR(26)      NOT NULL,
  tenant_id     CHAR(26)      NOT NULL,
  customer_id   CHAR(26)      NOT NULL,
  purpose       ENUM('TRANSACTIONAL','MARKETING','REFILL_REMINDER') NOT NULL,
  action        ENUM('GRANTED','WITHDRAWN','ERASURE_REQUESTED') NOT NULL,
  source        VARCHAR(30)   NOT NULL COMMENT 'COUNTER | STOREFRONT | WHATSAPP_STOP | SUPPORT',
  evidence      VARCHAR(300)      NULL COMMENT 'What was shown or said, verbatim where possible',
  actor_id      CHAR(26)          NULL,
  created_at    DATETIME(3)   NOT NULL,
  PRIMARY KEY (id),
  KEY ix_ce_cust (customer_id, purpose, created_at),
  KEY ix_ce_action (action, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


-- ---------------------------------------------------------------------
-- F.3 Refill schedule
--
--  Predicted from what he actually bought: quantity divided by the
--  daily dose implied by his own repeat interval. Not from a drug
--  database — the platform does not tell anyone how to take medicine.
--  It only notices that the last box should be running out.
-- ---------------------------------------------------------------------
CREATE TABLE refill_schedule (
  id                CHAR(26)      NOT NULL,
  tenant_id         CHAR(26)      NOT NULL,
  store_id          CHAR(26)      NOT NULL,
  customer_id       CHAR(26)      NOT NULL,
  item_id           CHAR(26)      NOT NULL,
  last_bill_id      CHAR(26)          NULL,
  last_purchase_on  DATE          NOT NULL,
  last_qty          DECIMAL(14,3) NOT NULL,
  avg_interval_days SMALLINT          NULL COMMENT 'From his own repeat pattern; NULL until 2 purchases',
  purchases_seen    SMALLINT      NOT NULL DEFAULT 1,
  due_on            DATE              NULL,
  remind_on         DATE              NULL COMMENT 'A few days before due_on',
  status            ENUM('ACTIVE','REMINDED','FULFILLED','SNOOZED','STOPPED')
                    NOT NULL DEFAULT 'ACTIVE',
  reminded_at       DATETIME(3)       NULL,
  created_at        DATETIME(3)   NOT NULL,
  updated_at        DATETIME(3)   NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_rs (customer_id, item_id),
  KEY ix_rs_due (store_id, status, remind_on),
  KEY ix_rs_cust (customer_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


-- ---------------------------------------------------------------------
-- F.4 Extra columns on the outbox for delivery discipline
-- ---------------------------------------------------------------------
ALTER TABLE message_outbox
  ADD COLUMN category    ENUM('TRANSACTIONAL','MARKETING','AUTHENTICATION')
             NOT NULL DEFAULT 'TRANSACTIONAL' AFTER kind,
  ADD COLUMN customer_id CHAR(26) NULL AFTER ref_id,
  ADD COLUMN not_before  DATETIME(3) NULL
             COMMENT 'Quiet-hours deferral for marketing' AFTER queued_at,
  ADD COLUMN failure_kind ENUM('NONE','TRANSIENT','PERMANENT') NOT NULL DEFAULT 'NONE'
             AFTER last_error,
  ADD KEY ix_mo_send (status, not_before, queued_at),
  ADD KEY ix_mo_cust (customer_id, category, queued_at);


SET FOREIGN_KEY_CHECKS = 1;

-- =====================================================================
--  INVARIANTS asserted in tests:
--
--   1. A MARKETING message is never sent without recorded marketing
--      consent, whatever the outbox row says.
--   2. A TRANSACTIONAL message is never blocked by quiet hours. His
--      invoice is his, at any hour.
--   3. "STOP" withdraws marketing consent, writes a consent_event, and
--      cancels queued marketing — but never his invoices.
--   4. A PERMANENT failure (bad number) is not retried.
--   5. An erasure request stops everything for that customer.
--   6. No template without provider approval is ever dispatched.
-- =====================================================================