-- =====================================================================
-- ArNi CRM – OpenVPN-Verwaltung
-- Tabellen für VPN-Benutzer, Verbindungen, Gerätehistorie und Audit-Log.
-- Idempotent (CREATE TABLE IF NOT EXISTS). Prefix: crm_
-- =====================================================================

-- VPN-Benutzer (Quelle der Wahrheit im CRM; PKI/Config erzeugt der Wrapper).
CREATE TABLE IF NOT EXISTS `crm_vpn_users` (
  `id`               INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `username`         VARCHAR(64)  NOT NULL,
  `display_name`     VARCHAR(190) DEFAULT NULL,
  `email`            VARCHAR(190) DEFAULT NULL,
  `description`      TEXT         DEFAULT NULL,
  `active`           TINYINT(1)   NOT NULL DEFAULT 1,
  `expires_at`       DATE         DEFAULT NULL,
  `last_login_at`    DATETIME     DEFAULT NULL,
  `last_ip`          VARCHAR(45)  DEFAULT NULL,
  `last_country`     VARCHAR(4)   DEFAULT NULL,
  `cert_serial`      VARCHAR(64)  DEFAULT NULL,
  `cert_expires_at`  DATETIME     DEFAULT NULL,
  `cert_status`      VARCHAR(16)  DEFAULT NULL,   -- valid | revoked | expired | none
  `created_by`       VARCHAR(120) DEFAULT NULL,
  `created_at`       DATETIME     NOT NULL,
  `updated_at`       DATETIME     DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_username` (`username`),
  KEY `idx_active` (`active`),
  KEY `idx_expires` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Verbindungs-Historie (eine Zeile je Sitzung).
CREATE TABLE IF NOT EXISTS `crm_vpn_connections` (
  `id`              INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `vpn_user_id`     INT UNSIGNED DEFAULT NULL,
  `username`        VARCHAR(64)  NOT NULL,
  `public_ip`       VARCHAR(45)  DEFAULT NULL,
  `vpn_ip`          VARCHAR(45)  DEFAULT NULL,
  `country`         VARCHAR(4)   DEFAULT NULL,
  `device`          VARCHAR(190) DEFAULT NULL,
  `connected_at`    DATETIME     NOT NULL,
  `disconnected_at` DATETIME     DEFAULT NULL,
  `bytes_in`        BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `bytes_out`       BIGINT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_user` (`vpn_user_id`),
  KEY `idx_username` (`username`),
  KEY `idx_connected` (`connected_at`),
  KEY `idx_open` (`disconnected_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Bekannte Geräte / öffentliche IPs je Benutzer.
CREATE TABLE IF NOT EXISTS `crm_vpn_devices` (
  `id`           INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `vpn_user_id`  INT UNSIGNED NOT NULL,
  `device`       VARCHAR(190) DEFAULT NULL,
  `public_ip`    VARCHAR(45)  DEFAULT NULL,
  `country`      VARCHAR(4)   DEFAULT NULL,
  `first_seen_at` DATETIME    DEFAULT NULL,
  `last_seen_at` DATETIME     DEFAULT NULL,
  `seen_count`   INT UNSIGNED NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_dev` (`vpn_user_id`, `device`, `public_ip`),
  KEY `idx_user` (`vpn_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Audit-Log aller OpenVPN-Aktionen.
CREATE TABLE IF NOT EXISTS `crm_vpn_audit` (
  `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id`    INT UNSIGNED DEFAULT NULL,
  `user_name`  VARCHAR(120) DEFAULT NULL,
  `action`     VARCHAR(64)  NOT NULL,
  `target`     VARCHAR(190) DEFAULT NULL,
  `result`     VARCHAR(16)  DEFAULT NULL,   -- ok | error
  `detail`     TEXT         DEFAULT NULL,
  `ip`         VARCHAR(45)  DEFAULT NULL,
  `created_at` DATETIME     NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;