-- ============================================================
-- BeezifiPay — full schema
-- Reflects all migrations 0001 – 0011.
-- Safe to run on a brand-new empty database.
-- ============================================================

CREATE DATABASE IF NOT EXISTS BeezifiPay
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE BeezifiPay;

-- ── Migration tracking ────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS schema_migrations (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  filename    VARCHAR(255) NOT NULL UNIQUE,
  applied_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- ── Legacy demo tables (read-only, used by /demo route) ──────────────────────

CREATE TABLE IF NOT EXISTS merchants (
  id              INT AUTO_INCREMENT PRIMARY KEY,
  name            VARCHAR(150) NOT NULL,
  store_code      VARCHAR(40)  NOT NULL UNIQUE,
  contact_name    VARCHAR(120) NOT NULL,
  business_number VARCHAR(40)  NOT NULL,
  created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS shoppers (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  full_name     VARCHAR(150) NOT NULL,
  mobile_number VARCHAR(40)  NOT NULL UNIQUE,
  auth_pin      VARCHAR(12)  NOT NULL,
  created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS payment_methods (
  id           INT AUTO_INCREMENT PRIMARY KEY,
  shopper_id   INT NOT NULL,
  label        VARCHAR(120) NOT NULL,
  network      VARCHAR(60)  NOT NULL,
  masked_account VARCHAR(40) NOT NULL,
  token_ref    VARCHAR(80)  NOT NULL,
  billing_type VARCHAR(40)  NOT NULL,
  is_default   BOOLEAN NOT NULL DEFAULT FALSE,
  created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_pm_shopper
    FOREIGN KEY (shopper_id) REFERENCES shoppers(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS checkout_sessions (
  id                INT AUTO_INCREMENT PRIMARY KEY,
  session_code      VARCHAR(40) NOT NULL UNIQUE,
  merchant_id       INT NOT NULL,
  shopper_id        INT NOT NULL,
  relay_device_code VARCHAR(40) NOT NULL,
  subtotal          DECIMAL(10, 2) NOT NULL,
  tax               DECIMAL(10, 2) NOT NULL,
  total             DECIMAL(10, 2) NOT NULL,
  status            ENUM('draft','pending_approval','settled','declined') NOT NULL DEFAULT 'draft',
  payment_method_id INT NULL,
  approved_at       DATETIME NULL,
  settled_at        DATETIME NULL,
  created_at        TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at        TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_cs_merchant FOREIGN KEY (merchant_id)       REFERENCES merchants(id),
  CONSTRAINT fk_cs_shopper  FOREIGN KEY (shopper_id)        REFERENCES shoppers(id),
  CONSTRAINT fk_cs_pm       FOREIGN KEY (payment_method_id) REFERENCES payment_methods(id)
);

CREATE TABLE IF NOT EXISTS checkout_line_items (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  checkout_id INT NOT NULL,
  item_name   VARCHAR(180)   NOT NULL,
  quantity    DECIMAL(10, 2) NOT NULL,
  unit_price  DECIMAL(10, 2) NOT NULL,
  line_total  DECIMAL(10, 2) NOT NULL,
  CONSTRAINT fk_cli_checkout
    FOREIGN KEY (checkout_id) REFERENCES checkout_sessions(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS session_events (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  checkout_id   INT NOT NULL,
  actor_type    VARCHAR(60)  NOT NULL,
  actor_label   VARCHAR(120) NOT NULL,
  event_type    VARCHAR(120) NOT NULL,
  event_payload JSON NOT NULL,
  created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_se_checkout
    FOREIGN KEY (checkout_id) REFERENCES checkout_sessions(id) ON DELETE CASCADE
);

-- ── Merchant users & payer accounts ──────────────────────────────────────────

CREATE TABLE IF NOT EXISTS merchant_users (
  id                    BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email                 VARCHAR(320) NOT NULL UNIQUE,
  password_hash         VARCHAR(255) NOT NULL,
  full_name             VARCHAR(150) NOT NULL,
  email_verified_at     DATETIME NULL,
  is_active             BOOLEAN NOT NULL DEFAULT TRUE,
  failed_login_attempts TINYINT UNSIGNED NOT NULL DEFAULT 0,
  locked_until          DATETIME NULL,
  created_at            DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at            DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_mu_email (email)
);

CREATE TABLE IF NOT EXISTS payer_accounts (
  id                    BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email                 VARCHAR(320) NOT NULL UNIQUE,
  phone                 VARCHAR(30) NULL,
  password_hash         VARCHAR(255) NOT NULL,
  full_name             VARCHAR(150) NOT NULL,
  email_verified_at     DATETIME NULL,
  phone_verified_at     DATETIME NULL,
  is_active             BOOLEAN NOT NULL DEFAULT TRUE,
  failed_login_attempts TINYINT UNSIGNED NOT NULL DEFAULT 0,
  locked_until          DATETIME NULL,
  created_at            DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at            DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_pa_email (email)
);

-- ── Sessions (express-mysql-session) ─────────────────────────────────────────

CREATE TABLE IF NOT EXISTS user_sessions (
  session_id VARCHAR(128) NOT NULL PRIMARY KEY,
  expires    BIGINT UNSIGNED NOT NULL,
  data       MEDIUMTEXT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_us_expires (expires)
);

-- ── Email verification & password reset ──────────────────────────────────────

CREATE TABLE IF NOT EXISTS email_verifications (
  id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  account_type ENUM('merchant','payer') NOT NULL,
  account_id   BIGINT UNSIGNED NOT NULL,
  token        VARCHAR(128) NOT NULL UNIQUE,
  expires_at   DATETIME NOT NULL,
  used_at      DATETIME NULL,
  created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_ev_token   (token),
  INDEX idx_ev_account (account_type, account_id)
);

CREATE TABLE IF NOT EXISTS password_resets (
  id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  account_type ENUM('merchant','payer') NOT NULL,
  account_id   BIGINT UNSIGNED NOT NULL,
  token_hash   VARCHAR(128) NOT NULL UNIQUE,
  expires_at   DATETIME NOT NULL,
  used_at      DATETIME NULL,
  created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_pr_token_hash (token_hash),
  INDEX idx_pr_account    (account_type, account_id)
);

-- ── Merchant organisations, memberships & locations ──────────────────────────

CREATE TABLE IF NOT EXISTS merchant_organizations (
  id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name       VARCHAR(150) NOT NULL,
  slug       VARCHAR(80)  NOT NULL UNIQUE,
  tax_id     VARCHAR(60)  NULL,
  website    VARCHAR(255) NULL,
  status     ENUM('pending','active','suspended') NOT NULL DEFAULT 'pending',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_mo_slug   (slug),
  INDEX idx_mo_status (status)
);

CREATE TABLE IF NOT EXISTS merchant_memberships (
  id                      BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  organization_id         BIGINT UNSIGNED NOT NULL,
  user_id                 BIGINT UNSIGNED NOT NULL,
  role                    ENUM('owner','admin','staff') NOT NULL DEFAULT 'staff',
  invited_by              BIGINT UNSIGNED NULL,
  invitation_token        VARCHAR(128) NULL UNIQUE,
  invitation_expires_at   DATETIME NULL,
  accepted_at             DATETIME NULL,
  created_at              DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_mm_org_user (organization_id, user_id),
  FOREIGN KEY (organization_id) REFERENCES merchant_organizations(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id)         REFERENCES merchant_users(id) ON DELETE CASCADE,
  FOREIGN KEY (invited_by)      REFERENCES merchant_users(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS merchant_locations (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  organization_id BIGINT UNSIGNED NOT NULL,
  name            VARCHAR(150) NOT NULL,
  location_code   VARCHAR(40)  NOT NULL UNIQUE,
  address_line1   VARCHAR(200) NULL,
  address_city    VARCHAR(100) NULL,
  address_state   VARCHAR(50)  NULL,
  address_zip     VARCHAR(20)  NULL,
  address_country CHAR(2)      NOT NULL DEFAULT 'US',
  phone           VARCHAR(30)  NULL,
  is_active       BOOLEAN NOT NULL DEFAULT TRUE,
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (organization_id) REFERENCES merchant_organizations(id) ON DELETE CASCADE,
  INDEX idx_ml_org  (organization_id),
  INDEX idx_ml_code (location_code)
);

-- ── Payer payment method vault ────────────────────────────────────────────────
-- processor_token is the Stripe PaymentMethod ID — raw card data is never stored.

CREATE TABLE IF NOT EXISTS payer_payment_methods (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  payer_id        BIGINT UNSIGNED NOT NULL,
  label           VARCHAR(120) NOT NULL,
  network         VARCHAR(60)  NOT NULL,
  masked_account  VARCHAR(40)  NOT NULL,
  processor       VARCHAR(40)  NOT NULL DEFAULT 'stripe',
  processor_token VARCHAR(255) NOT NULL,
  billing_type    ENUM('debit','credit','prepaid','other') NOT NULL DEFAULT 'other',
  expires_month   TINYINT UNSIGNED  NULL,
  expires_year    SMALLINT UNSIGNED NULL,
  is_default      BOOLEAN NOT NULL DEFAULT FALSE,
  is_active       BOOLEAN NOT NULL DEFAULT TRUE,
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (payer_id) REFERENCES payer_accounts(id) ON DELETE CASCADE,
  INDEX idx_ppm_payer (payer_id),
  UNIQUE KEY uq_ppm_token (processor, processor_token)
);

-- ── Checkout requests (incorporates POS mode from migration 0010) ─────────────
-- checkout_mode: 'standard' = payer email required; 'pos' = payer claims via QR.
-- payer_account_id is NULL for POS checkouts until the payer scans and claims it.

CREATE TABLE IF NOT EXISTS checkout_requests (
  id                       BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  session_code             VARCHAR(40) NOT NULL UNIQUE,
  idempotency_key          VARCHAR(128) NULL UNIQUE,
  organization_id          BIGINT UNSIGNED NOT NULL,
  location_id              BIGINT UNSIGNED NULL,
  created_by_user_id       BIGINT UNSIGNED NOT NULL,
  checkout_mode            ENUM('standard','pos') NOT NULL DEFAULT 'standard',
  payer_account_id         BIGINT UNSIGNED NULL,
  payer_payment_method_id  BIGINT UNSIGNED NULL,
  currency                 CHAR(3)        NOT NULL DEFAULT 'USD',
  subtotal                 DECIMAL(12, 2) NOT NULL,
  tax                      DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
  tip                      DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
  discount                 DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
  total                    DECIMAL(12, 2) NOT NULL,
  status                   ENUM(
                             'draft',
                             'pending_delivery',
                             'delivered',
                             'pending_approval',
                             'approved',
                             'declined',
                             'processing',
                             'settled',
                             'failed',
                             'refunded',
                             'expired',
                             'canceled'
                           ) NOT NULL DEFAULT 'draft',
  processor_authorization_code VARCHAR(255) NULL,
  processor_transaction_id     VARCHAR(255) NULL,
  decline_reason               VARCHAR(255) NULL,
  merchant_notes               TEXT NULL,
  expires_at                   DATETIME NULL,
  approved_at                  DATETIME NULL,
  settled_at                   DATETIME NULL,
  created_at                   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at                   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (organization_id)         REFERENCES merchant_organizations(id),
  FOREIGN KEY (location_id)             REFERENCES merchant_locations(id) ON DELETE SET NULL,
  FOREIGN KEY (created_by_user_id)      REFERENCES merchant_users(id),
  FOREIGN KEY (payer_account_id)        REFERENCES payer_accounts(id),
  FOREIGN KEY (payer_payment_method_id) REFERENCES payer_payment_methods(id) ON DELETE SET NULL,
  INDEX idx_cr_org     (organization_id),
  INDEX idx_cr_payer   (payer_account_id),
  INDEX idx_cr_status  (status),
  INDEX idx_cr_created (created_at)
);

CREATE TABLE IF NOT EXISTS checkout_request_items (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  checkout_id BIGINT UNSIGNED NOT NULL,
  item_name   VARCHAR(180)   NOT NULL,
  description VARCHAR(255)   NULL,
  quantity    DECIMAL(10, 3) NOT NULL,
  unit_price  DECIMAL(12, 2) NOT NULL,
  line_total  DECIMAL(12, 2) NOT NULL,
  FOREIGN KEY (checkout_id) REFERENCES checkout_requests(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS payment_attempts (
  id                        BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  checkout_id               BIGINT UNSIGNED NOT NULL,
  payer_payment_method_id   BIGINT UNSIGNED NOT NULL,
  amount                    DECIMAL(12, 2) NOT NULL,
  currency                  CHAR(3) NOT NULL DEFAULT 'USD',
  status                    ENUM('pending','authorized','settled','failed','voided') NOT NULL DEFAULT 'pending',
  processor                 VARCHAR(40)  NOT NULL DEFAULT 'stripe',
  processor_payment_intent_id VARCHAR(255) NULL,
  processor_charge_id       VARCHAR(255) NULL,
  processor_response        JSON NULL,
  failure_code              VARCHAR(60)  NULL,
  failure_message           VARCHAR(255) NULL,
  created_at                DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at                DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (checkout_id)             REFERENCES checkout_requests(id),
  FOREIGN KEY (payer_payment_method_id) REFERENCES payer_payment_methods(id),
  INDEX idx_pa_checkout (checkout_id)
);

CREATE TABLE IF NOT EXISTS refunds (
  id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  checkout_id         BIGINT UNSIGNED NOT NULL,
  payment_attempt_id  BIGINT UNSIGNED NOT NULL,
  amount              DECIMAL(12, 2) NOT NULL,
  reason              ENUM('duplicate','fraudulent','requested_by_customer','other') NOT NULL DEFAULT 'other',
  notes               TEXT NULL,
  status              ENUM('pending','succeeded','failed') NOT NULL DEFAULT 'pending',
  processor_refund_id VARCHAR(255) NULL,
  initiated_by        BIGINT UNSIGNED NULL,
  created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (checkout_id)        REFERENCES checkout_requests(id),
  FOREIGN KEY (payment_attempt_id) REFERENCES payment_attempts(id),
  FOREIGN KEY (initiated_by)       REFERENCES merchant_users(id) ON DELETE SET NULL
);

-- ── Immutable audit log ───────────────────────────────────────────────────────
-- Never UPDATE or DELETE rows from this table.

CREATE TABLE IF NOT EXISTS audit_logs (
  id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  event_type   VARCHAR(120) NOT NULL,
  actor_type   ENUM('merchant_user','payer','system','admin') NOT NULL,
  actor_id     BIGINT UNSIGNED NULL,
  actor_email  VARCHAR(320) NULL,
  target_type  VARCHAR(60)  NULL,
  target_id    BIGINT UNSIGNED NULL,
  context      JSON NULL,
  ip_address   VARCHAR(45)  NULL,
  user_agent   VARCHAR(512) NULL,
  created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_al_event   (event_type),
  INDEX idx_al_actor   (actor_type, actor_id),
  INDEX idx_al_target  (target_type, target_id),
  INDEX idx_al_created (created_at)
);

-- ── Notifications ─────────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS notifications (
  id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  recipient_type ENUM('merchant_user','payer') NOT NULL,
  recipient_id   BIGINT UNSIGNED NOT NULL,
  channel        ENUM('email','sms','push') NOT NULL,
  template_name  VARCHAR(80)  NOT NULL,
  subject        VARCHAR(255) NULL,
  body           TEXT NULL,
  context        JSON NULL,
  status         ENUM('pending','sent','failed') NOT NULL DEFAULT 'pending',
  retry_count    TINYINT UNSIGNED NOT NULL DEFAULT 0,
  last_error     TEXT NULL,
  scheduled_at   DATETIME NULL,
  sent_at        DATETIME NULL,
  created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_notif_recipient (recipient_type, recipient_id),
  INDEX idx_notif_status    (status),
  INDEX idx_notif_scheduled (scheduled_at)
);

-- ── Webhooks ──────────────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS webhooks (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  organization_id BIGINT UNSIGNED NOT NULL,
  url             VARCHAR(512) NOT NULL,
  secret_hash     VARCHAR(128) NOT NULL,
  events          JSON NOT NULL,
  is_active       BOOLEAN NOT NULL DEFAULT TRUE,
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (organization_id) REFERENCES merchant_organizations(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS webhook_deliveries (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  webhook_id    BIGINT UNSIGNED NOT NULL,
  event_type    VARCHAR(120) NOT NULL,
  payload       JSON NOT NULL,
  status        ENUM('pending','delivered','failed') NOT NULL DEFAULT 'pending',
  http_status   SMALLINT UNSIGNED NULL,
  response_body TEXT NULL,
  attempt_count TINYINT UNSIGNED NOT NULL DEFAULT 0,
  next_retry_at DATETIME NULL,
  delivered_at  DATETIME NULL,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (webhook_id) REFERENCES webhooks(id) ON DELETE CASCADE,
  INDEX idx_wd_status (status),
  INDEX idx_wd_retry  (next_retry_at)
);

-- ── Phone verification (OTP) ──────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS phone_verifications (
  id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  payer_id   BIGINT UNSIGNED NOT NULL,
  phone      VARCHAR(30)  NOT NULL,
  code_hash  VARCHAR(128) NOT NULL,
  expires_at DATETIME NOT NULL,
  used_at    DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (payer_id) REFERENCES payer_accounts(id) ON DELETE CASCADE,
  INDEX idx_pv_payer   (payer_id),
  INDEX idx_pv_expires (expires_at)
);

-- ── Public REST API credentials ───────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS merchant_api_keys (
  id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  organization_id     BIGINT UNSIGNED NOT NULL,
  created_by_user_id  BIGINT UNSIGNED NULL,
  name                VARCHAR(120) NOT NULL DEFAULT 'Default',
  type                ENUM('secret','publishable') NOT NULL DEFAULT 'secret',
  environment         ENUM('live','test') NOT NULL DEFAULT 'live',
  key_prefix          VARCHAR(12)  NOT NULL,
  key_hash            VARCHAR(128) NOT NULL UNIQUE,
  key_preview         VARCHAR(24)  NOT NULL,
  is_active           BOOLEAN NOT NULL DEFAULT TRUE,
  last_used_at        DATETIME NULL,
  expires_at          DATETIME NULL,
  created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (organization_id)    REFERENCES merchant_organizations(id) ON DELETE CASCADE,
  FOREIGN KEY (created_by_user_id) REFERENCES merchant_users(id) ON DELETE SET NULL,
  INDEX idx_mak_org  (organization_id),
  INDEX idx_mak_hash (key_hash)
);

CREATE TABLE IF NOT EXISTS payer_api_tokens (
  id               BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  payer_account_id BIGINT UNSIGNED NOT NULL,
  token_hash       VARCHAR(128) NOT NULL UNIQUE,
  token_preview    VARCHAR(24)  NOT NULL,
  is_active        BOOLEAN NOT NULL DEFAULT TRUE,
  expires_at       DATETIME NOT NULL,
  last_used_at     DATETIME NULL,
  ip_address       VARCHAR(45)  NULL,
  user_agent       VARCHAR(512) NULL,
  created_at       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (payer_account_id) REFERENCES payer_accounts(id) ON DELETE CASCADE,
  INDEX idx_pat_payer (payer_account_id),
  INDEX idx_pat_hash  (token_hash)
);

CREATE TABLE IF NOT EXISTS api_idempotency_keys (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  idempotency_key VARCHAR(255) NOT NULL,
  owner_type      ENUM('merchant_org','payer') NOT NULL,
  owner_id        BIGINT UNSIGNED NOT NULL,
  method          VARCHAR(10)  NOT NULL,
  path            VARCHAR(255) NOT NULL,
  response_status SMALLINT UNSIGNED NOT NULL,
  response_body   MEDIUMTEXT NOT NULL,
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  expires_at      DATETIME NOT NULL,
  UNIQUE KEY uq_idem       (idempotency_key, owner_type, owner_id),
  INDEX idx_idem_expires   (expires_at)
);
