-- ArNi CRM – Backup-Modul (eigene Backups, unabhängig von ISPConfig).
CREATE TABLE IF NOT EXISTS `crm_server_backups` (
  `id`              INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`            VARCHAR(190) NOT NULL,
  `content_db`      TINYINT(1) NOT NULL DEFAULT 1,
  `content_web`     TINYINT(1) NOT NULL DEFAULT 0,
  `content_config`  TINYINT(1) NOT NULL DEFAULT 0,
  `db_names`        TEXT NULL,           -- Zeilen-/Komma-getrennt; leer = alle
  `web_paths`       TEXT NULL,           -- eine je Zeile (absolute Pfade)
  `config_paths`    TEXT NULL,           -- eine je Zeile (absolute Pfade)
  `destination`     VARCHAR(16) NOT NULL DEFAULT 'local',  -- local|ftp|sftp|gdrive
  `dest_path`       VARCHAR(500) NULL,
  `dest_host`       VARCHAR(190) NULL,
  `dest_port`       INT NULL,
  `dest_user`       VARCHAR(190) NULL,
  `dest_pass_enc`   TEXT NULL,           -- verschlüsselt (app.key)
  `dest_remote`     VARCHAR(190) NULL,   -- rclone-Remote (gdrive)
  `archive_pass_enc` TEXT NULL,          -- 7z-Passwort, verschlüsselt
  `retention`       INT NOT NULL DEFAULT 7,  -- behalte N neueste (lokal), 0 = unbegrenzt
  `active`          TINYINT(1) NOT NULL DEFAULT 1,
  `created_by`      VARCHAR(120) NULL,
  `created_at`      DATETIME NOT NULL,
  `updated_at`      DATETIME NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `crm_server_backup_runs` (
  `id`           INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `backup_id`    INT UNSIGNED NOT NULL,
  `started_at`   DATETIME NOT NULL,
  `finished_at`  DATETIME NULL,
  `status`       VARCHAR(16) NOT NULL DEFAULT 'running', -- running|success|failed
  `size_bytes`   BIGINT UNSIGNED NULL,
  `archive_name` VARCHAR(255) NULL,
  `location`     VARCHAR(700) NULL,
  `log`          LONGTEXT NULL,
  `trigger`      VARCHAR(16) NOT NULL DEFAULT 'manual',
  `created_at`   DATETIME NOT NULL,
  PRIMARY KEY (`id`), KEY `idx_backup` (`backup_id`), KEY `idx_started` (`started_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;