-- ============================================================================
--  ArNi CRM – Server-API: Token-Verwaltung & Hilfstabellen
--  Owner: CRM (Prefix crm_api_*).  Engine/Collation wie Bestand.
--  Zweck: Bearer-Token-Auth für die schmale CRM-Server-API, über die das
--         Control Center (CC) lesend/schreibend andockt – OHNE zweiten Root-Pfad.
-- ============================================================================

SET NAMES utf8mb4;

-- ---------------------------------------------------------------------------
-- API-Tokens (nur Hash gespeichert; Klartext wird einmalig bei Erstellung gezeigt)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `crm_api_tokens` (
  `id`               int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name`             varchar(120) NOT NULL,                 -- z.B. "control-center-prod"
  `token_prefix`     varchar(24)  NOT NULL,                 -- öffentlicher Anzeige-/Suchpräfix (z.B. acc_live_xxxxxxxx)
  `token_hash`       char(64)     NOT NULL,                 -- sha256(hex) des vollständigen Tokens
  `signing_secret_enc` text       DEFAULT NULL,             -- optional: HMAC-Secret (app-verschlüsselt)
  `scopes`           text         NOT NULL,                 -- Space-getrennt, z.B. "fail2ban:read vpn:read"
  `allowed_ips`      varchar(255) DEFAULT NULL,             -- optional CIDR-Allowlist, kommagetrennt
  `rate_limit_per_min` int(10) unsigned NOT NULL DEFAULT 120,
  `active`           tinyint(1)   NOT NULL DEFAULT 1,
  `revoked`          tinyint(1)   NOT NULL DEFAULT 0,
  `expires_at`       datetime     DEFAULT NULL,
  `last_used_at`     datetime     DEFAULT NULL,
  `last_ip`          varchar(45)  DEFAULT NULL,
  `created_by`       varchar(120) DEFAULT NULL,
  `created_at`       datetime     NOT NULL DEFAULT current_timestamp(),
  `updated_at`       datetime     DEFAULT NULL ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_token_hash` (`token_hash`),
  KEY `idx_prefix` (`token_prefix`),
  KEY `idx_active` (`active`,`revoked`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Idempotenz-Schlüssel (verhindert Doppelausführung bei Retries/Timeouts)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `crm_api_idempotency` (
  `id`            bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `token_id`      int(10) unsigned NOT NULL,
  `idem_key`      varchar(120) NOT NULL,
  `method`        varchar(10)  NOT NULL,
  `path`          varchar(255) NOT NULL,
  `response_code` int(11)      DEFAULT NULL,
  `response_body` mediumtext   DEFAULT NULL,   -- gecachte Antwort (für identische Wiederholung)
  `status`        varchar(16)  NOT NULL DEFAULT 'in_progress', -- in_progress|done
  `created_at`    datetime     NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_token_key` (`token_id`,`idem_key`),
  KEY `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Rate-Limit-Zähler je Token und Minute (Fallback ohne APCu)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `crm_api_rate` (
  `token_id`    int(10) unsigned NOT NULL,
  `window_min`  datetime NOT NULL,            -- auf volle Minute gerundet
  `hits`        int(10) unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY (`token_id`,`window_min`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- (Optional) Request-Log; ansonsten genügt das zentrale crm_audit_log (CC-Schema)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `crm_api_request_log` (
  `id`          bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `token_id`    int(10) unsigned DEFAULT NULL,
  `token_name`  varchar(120) DEFAULT NULL,
  `method`      varchar(10)  NOT NULL,
  `path`        varchar(255) NOT NULL,
  `scope`       varchar(64)  DEFAULT NULL,
  `status_code` int(11)      NOT NULL,
  `duration_ms` int(11)      DEFAULT NULL,
  `ip`          varchar(45)  DEFAULT NULL,
  `created_at`  datetime     NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_token` (`token_id`),
  KEY `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;