-- Public API credentials.
-- merchant_api_keys  : server-to-server auth for merchant integrations.
-- payer_api_tokens   : bearer tokens issued to payer apps after login.
-- api_idempotency_keys: deduplication store for mutating API calls.

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)
);
