-- =====================================================================
-- ArNi CRM - Cronjob-Verwaltung (Multi-Server-fähig)
-- Tabellen für Server, Cronjobs, Ausführungen und Audit-Log.
-- Idempotent (CREATE TABLE IF NOT EXISTS). Prefix: crm_
-- =====================================================================

-- Registrierte Server (Architektur für mehrere Hosts; aktuell nur der lokale).
CREATE TABLE IF NOT EXISTS `crm_servers` (
  `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`       VARCHAR(120) NOT NULL,
  `hostname`   VARCHAR(190) DEFAULT NULL,
  `type`       VARCHAR(24)  NOT NULL DEFAULT 'local',  -- local | ssh
  `ssh_host`   VARCHAR(190) DEFAULT NULL,
  `ssh_user`   VARCHAR(64)  DEFAULT NULL,
  `ssh_port`   INT          DEFAULT 22,
  `is_local`   TINYINT(1)   NOT NULL DEFAULT 0,
  `active`     TINYINT(1)   NOT NULL DEFAULT 1,
  `created_at` DATETIME     NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_active` (`active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Cronjobs (CRM-verwaltet und/oder importiert).
CREATE TABLE IF NOT EXISTS `crm_server_cronjobs` (
  `id`                INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `server_id`         INT UNSIGNED NOT NULL DEFAULT 1,
  `name`              VARCHAR(190) NOT NULL,
  `description`       TEXT         DEFAULT NULL,
  `run_user`          VARCHAR(64)  NOT NULL DEFAULT 'root',
  `schedule`          VARCHAR(120) NOT NULL,           -- 5-Feld Cron-Ausdruck
  `command`           TEXT         NOT NULL,
  `working_directory` VARCHAR(255) DEFAULT NULL,
  `timeout`           INT UNSIGNED NOT NULL DEFAULT 3600, -- Sekunden
  `active`            TINYINT(1)   NOT NULL DEFAULT 1,
  `source`            VARCHAR(16)  NOT NULL DEFAULT 'managed', -- managed | imported
  `origin`            VARCHAR(255) DEFAULT NULL,        -- Herkunft bei Import (z.B. /etc/cron.d/x, crontab:user)
  `notify_on_error`   TINYINT(1)   NOT NULL DEFAULT 0,
  `notify_on_success` TINYINT(1)   NOT NULL DEFAULT 0,
  `notify_email`      VARCHAR(190) DEFAULT NULL,
  `last_run_at`       DATETIME     DEFAULT NULL,
  `last_exit_code`    INT          DEFAULT NULL,
  `last_status`       VARCHAR(16)  DEFAULT NULL,        -- success | failed | timeout | running
  `avg_runtime`       DECIMAL(10,2) DEFAULT NULL,       -- Sekunden
  `created_by`        VARCHAR(120) DEFAULT NULL,
  `created_at`        DATETIME     NOT NULL,
  `updated_at`        DATETIME     DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_server` (`server_id`),
  KEY `idx_active` (`active`),
  KEY `idx_source` (`source`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Ausführungs-Historie.
CREATE TABLE IF NOT EXISTS `crm_server_cronjob_runs` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `cronjob_id`  INT UNSIGNED NOT NULL,
  `server_id`   INT UNSIGNED NOT NULL DEFAULT 1,
  `started_at`  DATETIME     NOT NULL,
  `finished_at` DATETIME     DEFAULT NULL,
  `duration`    DECIMAL(10,2) DEFAULT NULL,             -- Sekunden
  `exit_code`   INT          DEFAULT NULL,
  `status`      VARCHAR(16)  NOT NULL DEFAULT 'running', -- running | success | failed | timeout
  `trigger`     VARCHAR(16)  NOT NULL DEFAULT 'manual',  -- manual | scheduled
  `stdout`      LONGTEXT     DEFAULT NULL,
  `stderr`      LONGTEXT     DEFAULT NULL,
  `created_at`  DATETIME     NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_cronjob` (`cronjob_id`),
  KEY `idx_started` (`started_at`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Audit-Log aller Cronjob-Aktionen.
CREATE TABLE IF NOT EXISTS `crm_server_cronjob_audit` (
  `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id`    INT UNSIGNED DEFAULT NULL,
  `user_name`  VARCHAR(120) DEFAULT NULL,
  `server_id`  INT UNSIGNED 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;