-- =====================================================================
--  RETAIL PHARMACY PLATFORM
--  Phase 2 Schema Pack H — Messaging providers & spend control v1.0
--
--  Apply AFTER pack F (messaging) and pack G (public surface).
--  Target: MySQL 8.0+ / InnoDB / utf8mb4
--
--  =================================================================
--  WHY A SPEND CAP IS A SCHEMA OBJECT AND NOT A SETTING
--  =================================================================
--
--  The OTP endpoint is public. Rate limits stop one number being
--  hammered, but a distributed flood of first-time requests still ends
--  with somebody's WhatsApp bill. The chemist did not authorise that
--  spend and cannot see it happening.
--
--  So every send is priced and counted, and the cap is enforced in the
--  same transaction as the send. A cap held only in a config file is a
--  cap nobody can audit after the money is gone.
--
--  =================================================================
--  AUTHENTICATION IS NOT MARKETING
--  =================================================================
--
--  A marketing cap that blocks an OTP locks a patient out of ordering
--  his own medicine. So the caps are SEPARATE, and authentication has
--  its own — higher, and still finite, because "unlimited" is how the
--  bill arrives.
-- =====================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE message_provider (
  id                CHAR(26)      NOT NULL,
  tenant_id         CHAR(26)          NULL COMMENT 'NULL = platform default',
  store_id          CHAR(26)          NULL,
  channel           ENUM('WHATSAPP','SMS','EMAIL') NOT NULL,
  provider_code     VARCHAR(30)   NOT NULL COMMENT 'META_CLOUD | GUPSHUP | SES | ...',
  -- Credentials are referenced, never stored here. A support engineer
  -- reading this table must not walk away with the ability to send.
  credential_ref    VARCHAR(120)  NOT NULL COMMENT 'Key in the secret store',
  sender_id         VARCHAR(60)       NULL COMMENT 'Phone number id / sender mask',
  cost_per_message  DECIMAL(8,4)  NOT NULL DEFAULT 0 COMMENT 'Rupees, for the ledger',
  is_primary        TINYINT(1)    NOT NULL DEFAULT 1,
  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),
  KEY ix_mp_lookup (store_id, channel, is_active, is_primary),
  KEY ix_mp_tenant (tenant_id, channel, is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


-- ---------------------------------------------------------------------
--  Caps, per store, per day. Separate for authentication and marketing.
-- ---------------------------------------------------------------------
CREATE TABLE message_budget (
  id                CHAR(26)      NOT NULL,
  store_id          CHAR(26)      NOT NULL,
  daily_auth_cap    INT UNSIGNED  NOT NULL DEFAULT 300
                    COMMENT 'OTPs. Higher, and still finite.',
  daily_mktg_cap    INT UNSIGNED  NOT NULL DEFAULT 200,
  daily_cost_cap    DECIMAL(10,2) NOT NULL DEFAULT 250.00
                    COMMENT 'Rupees. The backstop when a per-message count is not the right unit.',
  -- Set by the chemist, visible to him, and the reason a runaway costs
  -- him one day rather than one month.
  alert_at_pct      TINYINT       NOT NULL DEFAULT 80,
  is_suspended      TINYINT(1)    NOT NULL DEFAULT 0,
  suspended_reason  VARCHAR(200)      NULL,
  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_mb (store_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


-- ---------------------------------------------------------------------
--  Every send priced and counted. Append-only, like every other ledger.
-- ---------------------------------------------------------------------
CREATE TABLE message_spend (
  id                CHAR(26)      NOT NULL,
  store_id          CHAR(26)      NOT NULL,
  spend_date        DATE          NOT NULL,
  channel           ENUM('WHATSAPP','SMS','EMAIL') NOT NULL,
  category          ENUM('AUTHENTICATION','UTILITY','MARKETING') NOT NULL,
  provider_code     VARCHAR(30)   NOT NULL,
  messages          INT UNSIGNED  NOT NULL DEFAULT 1,
  cost              DECIMAL(10,4) NOT NULL DEFAULT 0,
  outcome           ENUM('SENT','FAILED','REFUSED_CAP','REFUSED_TEMPLATE') NOT NULL,
  message_id        CHAR(26)          NULL,
  created_at        DATETIME(3)   NOT NULL,
  PRIMARY KEY (id),
  KEY ix_ms_day (store_id, spend_date, category),
  KEY ix_ms_outcome (store_id, outcome, spend_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


-- ---------------------------------------------------------------------
--  Template registry. An unapproved template does not send — it is
--  rejected by Meta, and finding that out at dispatch time means a
--  customer never got the message and nobody noticed.
-- ---------------------------------------------------------------------
--  OVERLAP, ACKNOWLEDGED.
--
--  message_template already has an approval_status column, added in pack
--  F before this layer existed. I did not notice until the first
--  integration test failed on a category mismatch.
--
--  They are not quite the same thing: approval is granted by a PROVIDER,
--  for a template name registered with that provider, in a language —
--  so a shop on WhatsApp and SMS has two approvals for one message. That
--  is what this table holds, and it is what the dispatcher reads.
--
--  message_template.approval_status is now the stale copy. It should be
--  dropped in a later pack once nothing reads it; it is left alone here
--  because a column drop on a live store is not something to bundle into
--  an unrelated change.
--
--  Note also the two vocabularies: ours says TRANSACTIONAL, Meta says
--  UTILITY. This table speaks the provider's language, because that is
--  the word that gets submitted for approval.
CREATE TABLE provider_template (
  id                CHAR(26)      NOT NULL,
  provider_code     VARCHAR(30)   NOT NULL,
  template_code     VARCHAR(60)   NOT NULL COMMENT 'Our name for it',
  external_name     VARCHAR(120)  NOT NULL COMMENT 'The name registered with the provider',
  language          VARCHAR(10)   NOT NULL DEFAULT 'en',
  category          ENUM('AUTHENTICATION','UTILITY','MARKETING') NOT NULL,
  slots             SMALLINT      NOT NULL DEFAULT 0 COMMENT 'Variable count the provider expects',
  status            ENUM('DRAFT','SUBMITTED','APPROVED','REJECTED','PAUSED')
                    NOT NULL DEFAULT 'DRAFT',
  rejection_reason  VARCHAR(300)      NULL,
  submitted_at      DATETIME(3)       NULL,
  approved_at       DATETIME(3)       NULL,
  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_pt (provider_code, template_code, language),
  KEY ix_pt_status (status, category)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

SET FOREIGN_KEY_CHECKS = 1;

-- =====================================================================
--  INVARIANTS asserted in tests:
--
--   1. A send against an unapproved template never reaches the provider.
--   2. The slot count must match, or the customer gets "Hi , your bill".
--   3. Marketing stops at its cap; authentication is unaffected by it.
--   4. Authentication has its own cap, because unlimited is how the
--      bill arrives.
--   5. Every attempt is priced and counted, including refusals.
--   6. A provider outage retries; a provider REJECTION does not.
--   7. Credentials never appear in any row or any log line.
-- =====================================================================