-- SIEM-lite vollständiges MariaDB-kompatibles Datenbankschema
-- Stand: Partitionierung event_logs/event_logs_raw, 3h-Partitionen, Raw-XML-Auslagerung,
-- Baseline-Buckets, UEBA, SOC/Risk, UI-Bewertungen.
--
-- Getestet/ausgelegt für MariaDB/MySQL InnoDB.
--
-- WICHTIG:
-- 1. Dieses Script löscht bestehende Tabellen.
-- 2. Partitionierung erfolgt nach DATETIME(6)-Spalte ts.
-- 3. MariaDB erlaubt ON UPDATE UTC_TIMESTAMP(6) nicht zuverlässig.
--    Deshalb nutzt dieses Schema DEFAULT CURRENT_TIMESTAMP(6) / ON UPDATE CURRENT_TIMESTAMP(6).
-- 4. Stelle für echte UTC-Speicherung im Go-DSN zusätzlich die DB-Session auf UTC:
--      ?parseTime=true&loc=UTC&time_zone=%27%2B00%3A00%27
--    oder setze beim Verbindungsaufbau SET time_zone = '+00:00'.
-- 5. Spalten in zusammengesetzten Keys wurden auf VARCHAR(191) begrenzt,
--    damit utf8mb4 und 3072-Byte-Key-Limit keine Fehler erzeugen.

SET NAMES utf8mb4;
SET time_zone = '+00:00';

SET FOREIGN_KEY_CHECKS = 0;

DROP PROCEDURE IF EXISTS ensure_siem_partitions;

DROP TABLE IF EXISTS event_count_buckets;
DROP TABLE IF EXISTS ueba_context_buckets;
DROP TABLE IF EXISTS host_risk_scores;
DROP TABLE IF EXISTS baseline_exclusions;
DROP TABLE IF EXISTS baseline_event_stats;
DROP TABLE IF EXISTS detection_suppressions;
DROP TABLE IF EXISTS detections;
DROP TABLE IF EXISTS detection_rules;
DROP TABLE IF EXISTS user_privilege_baseline;
DROP TABLE IF EXISTS user_source_ip_seen;
DROP TABLE IF EXISTS ueba_user_baseline;
DROP TABLE IF EXISTS privileged_users;
DROP TABLE IF EXISTS event_logs_raw;
DROP TABLE IF EXISTS event_logs;
DROP TABLE IF EXISTS agents;

SET FOREIGN_KEY_CHECKS = 1;

-- ---------------------------------------------------------------------
-- Agents
-- ---------------------------------------------------------------------

CREATE TABLE agents (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    hostname VARCHAR(191) NOT NULL,
    api_key_hash CHAR(64) NOT NULL,
    first_seen DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    last_seen DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    last_ip VARCHAR(64) NOT NULL DEFAULT '',
    is_enabled TINYINT(1) NOT NULL DEFAULT 1,

    PRIMARY KEY (id),
    UNIQUE KEY uq_agents_hostname (hostname),
    KEY idx_agents_enabled_last_seen (is_enabled, last_seen),
    KEY idx_agents_last_seen (last_seen)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Event Logs, normalisierte Haupttabelle
-- ---------------------------------------------------------------------

CREATE TABLE event_logs (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    ts DATETIME(6) NOT NULL,
    agent_id BIGINT UNSIGNED NOT NULL,
    hostname VARCHAR(191) NOT NULL,
    channel_name VARCHAR(128) NOT NULL,
    event_id INT UNSIGNED NOT NULL,
    source VARCHAR(191) NOT NULL DEFAULT '',

    computer VARCHAR(191) NOT NULL DEFAULT '',
    provider_name VARCHAR(191) NOT NULL DEFAULT '',

    level_value INT UNSIGNED NOT NULL DEFAULT 0,
    task_value INT UNSIGNED NOT NULL DEFAULT 0,
    opcode_value INT UNSIGNED NOT NULL DEFAULT 0,
    keywords VARCHAR(191) NOT NULL DEFAULT '',

    target_user VARCHAR(191) NOT NULL DEFAULT '',
    target_user_norm VARCHAR(191) NOT NULL DEFAULT '',
    target_domain VARCHAR(191) NOT NULL DEFAULT '',

    subject_user VARCHAR(191) NOT NULL DEFAULT '',
    subject_user_norm VARCHAR(191) NOT NULL DEFAULT '',
    subject_domain VARCHAR(191) NOT NULL DEFAULT '',

    workstation VARCHAR(191) NOT NULL DEFAULT '',
    src_ip VARCHAR(64) NOT NULL DEFAULT '',
    src_port VARCHAR(32) NOT NULL DEFAULT '',
    logon_type VARCHAR(32) NOT NULL DEFAULT '',
    process_name VARCHAR(768) NOT NULL DEFAULT '',
    authentication_package VARCHAR(128) NOT NULL DEFAULT '',
    logon_process VARCHAR(128) NOT NULL DEFAULT '',
    status_text VARCHAR(128) NOT NULL DEFAULT '',
    sub_status_text VARCHAR(128) NOT NULL DEFAULT '',
    failure_reason VARCHAR(768) NOT NULL DEFAULT '',

    received_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    msg_sha256 CHAR(64) NOT NULL,

    -- Optionaler Kompatibilitätsrest. Raw XML gehört in event_logs_raw.
    msg MEDIUMTEXT NULL,

    PRIMARY KEY (id, ts),

    KEY idx_event_logs_id (id),
    KEY idx_event_logs_agent_ts (agent_id, ts),
    KEY idx_event_logs_ts_host_channel_event (ts, hostname, channel_name, event_id),
    KEY idx_event_logs_hostname_ts (hostname, ts),
    KEY idx_event_logs_channel_event_ts (channel_name, event_id, ts),
    KEY idx_event_logs_target_user_norm_ts (target_user_norm, ts),
    KEY idx_event_logs_subject_user_norm_ts (subject_user_norm, ts),
    KEY idx_event_logs_src_ip_ts (src_ip, ts),

    KEY idx_event_logs_security_logon_user (
        channel_name,
        event_id,
        ts,
        target_user_norm,
        hostname,
        src_ip
    ),

    KEY idx_event_logs_ueba_context (
        channel_name,
        event_id,
        ts,
        hostname,
        target_user_norm,
        src_ip,
        workstation
    ),

    KEY idx_event_logs_password_spray (
        channel_name,
        event_id,
        ts,
        src_ip,
        target_user_norm,
        hostname
    ),

    KEY idx_event_logs_dedupe_lookup (
        hostname,
        channel_name,
        event_id,
        ts,
        msg_sha256
    )
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
PARTITION BY RANGE COLUMNS(ts) (
    PARTITION pmax VALUES LESS THAN (MAXVALUE)
);

-- ---------------------------------------------------------------------
-- Raw XML Tabelle
-- ---------------------------------------------------------------------

CREATE TABLE event_logs_raw (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    event_log_id BIGINT UNSIGNED NOT NULL,
    ts DATETIME(6) NOT NULL,
    msg_sha256 CHAR(64) NOT NULL,
    msg LONGBLOB NOT NULL,
    is_gzip TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),

    PRIMARY KEY (id, ts),

    KEY idx_event_logs_raw_id (id),
    KEY idx_event_logs_raw_event_log_id (event_log_id),
    KEY idx_event_logs_raw_ts (ts),
    KEY idx_event_logs_raw_sha (msg_sha256),
    UNIQUE KEY uq_event_logs_raw_event_ts (event_log_id, ts)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
PARTITION BY RANGE COLUMNS(ts) (
    PARTITION pmax VALUES LESS THAN (MAXVALUE)
);

-- ---------------------------------------------------------------------
-- Detection-Regeln
-- ---------------------------------------------------------------------

CREATE TABLE detection_rules (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(191) NOT NULL,
    description TEXT NULL,
    severity ENUM('info','low','medium','high','critical') NOT NULL DEFAULT 'medium',
    channel VARCHAR(512) NOT NULL DEFAULT 'Security',
    event_ids VARCHAR(1024) NOT NULL,

    match_field VARCHAR(128) NOT NULL DEFAULT '',
    match_operator ENUM('', 'eq', 'contains', 'in') NOT NULL DEFAULT '',
    match_value TEXT NULL,

    threshold_count INT NOT NULL DEFAULT 1,
    threshold_window_seconds INT NOT NULL DEFAULT 0,
    suppress_for_seconds INT NOT NULL DEFAULT 3600,

    enabled TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),

    PRIMARY KEY (id),
    UNIQUE KEY uq_detection_rules_name (name),
    KEY idx_detection_rules_enabled (enabled)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Detections
-- ---------------------------------------------------------------------

CREATE TABLE detections (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    rule_name VARCHAR(191) NOT NULL,
    severity ENUM('info','low','medium','high','critical') NOT NULL DEFAULT 'medium',
    hostname VARCHAR(191) NOT NULL DEFAULT '',
    channel_name VARCHAR(128) NOT NULL DEFAULT '',
    event_id INT UNSIGNED NOT NULL DEFAULT 0,
    score DOUBLE NOT NULL DEFAULT 0,

    window_start DATETIME(6) NOT NULL,
    window_end DATETIME(6) NOT NULL,

    summary TEXT NOT NULL,
    details_json LONGTEXT NULL CHECK (JSON_VALID(details_json)),

    status ENUM(
        'open',
        'acknowledged',
        'investigating',
        'plausible',
        'legitimate',
        'false_positive',
        'resolved',
        'suppressed',
        'confirmed_incident'
    ) NOT NULL DEFAULT 'open',

    analyst_note TEXT NULL,
    reviewed_by VARCHAR(191) NOT NULL DEFAULT '',
    reviewed_at DATETIME(6) NULL,
    is_false_positive TINYINT(1) NOT NULL DEFAULT 0,
    is_legitimate TINYINT(1) NOT NULL DEFAULT 0,

    created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),

    PRIMARY KEY (id),

    UNIQUE KEY uq_detection_dedupe (
        rule_name,
        hostname,
        channel_name,
        event_id,
        window_start,
        window_end
    ),

    KEY idx_detections_created (created_at),
    KEY idx_detections_status_created (status, created_at),
    KEY idx_detections_severity_created (severity, created_at),
    KEY idx_detections_host_created (hostname, created_at),
    KEY idx_detections_rule_host_created (rule_name, hostname, created_at),
    KEY idx_detections_created_status_severity_host (
        created_at,
        status,
        severity,
        hostname
    ),
    KEY idx_detections_window (
        hostname,
        window_start,
        window_end
    ),
    KEY idx_detections_soc (
        status,
        severity,
        hostname,
        created_at
    )
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Detection Suppressions
-- ---------------------------------------------------------------------

CREATE TABLE detection_suppressions (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    rule_name VARCHAR(191) NOT NULL,
    hostname VARCHAR(191) NOT NULL DEFAULT '',
    channel_name VARCHAR(128) NOT NULL DEFAULT '',
    event_id INT UNSIGNED NOT NULL DEFAULT 0,

    reason TEXT NULL,
    created_by VARCHAR(191) NOT NULL DEFAULT '',
    created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    expires_at DATETIME(6) NULL,
    enabled TINYINT(1) NOT NULL DEFAULT 1,

    PRIMARY KEY (id),
    KEY idx_detection_suppressions_lookup (
        enabled,
        rule_name,
        hostname,
        channel_name,
        event_id,
        expires_at
    ),
    KEY idx_detection_suppressions_expires (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Event Count Buckets für Baseline
-- ---------------------------------------------------------------------

CREATE TABLE event_count_buckets (
    bucket_start DATETIME(6) NOT NULL,
    bucket_end DATETIME(6) NOT NULL,

    hostname VARCHAR(191) NOT NULL,
    channel_name VARCHAR(128) NOT NULL,
    event_id INT UNSIGNED NOT NULL,

    cnt BIGINT UNSIGNED NOT NULL DEFAULT 0,

    first_event_ts DATETIME(6) NULL,
    last_event_ts DATETIME(6) NULL,

    finalized TINYINT(1) NOT NULL DEFAULT 0,
    anomaly_checked_at DATETIME(6) NULL,
    learned_at DATETIME(6) NULL,

    updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),

    PRIMARY KEY (bucket_start, hostname, channel_name, event_id),

    KEY idx_event_count_buckets_lookup (
        hostname,
        channel_name,
        event_id,
        bucket_start
    ),

    KEY idx_event_count_buckets_anomaly (
        finalized,
        anomaly_checked_at,
        bucket_start
    ),

    KEY idx_event_count_buckets_learn (
        finalized,
        learned_at,
        bucket_start
    ),

    KEY idx_event_count_buckets_time (
        bucket_start,
        bucket_end
    )
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Baseline Stats
-- ---------------------------------------------------------------------

CREATE TABLE baseline_event_stats (
    hostname VARCHAR(191) NOT NULL,
    channel_name VARCHAR(128) NOT NULL,
    event_id INT UNSIGNED NOT NULL,
    hour_of_day TINYINT UNSIGNED NOT NULL,
    day_of_week TINYINT UNSIGNED NOT NULL,

    avg_count DOUBLE NOT NULL DEFAULT 0,
    m2_count DOUBLE NOT NULL DEFAULT 0,
    stddev_count DOUBLE NOT NULL DEFAULT 0,
    sample_count INT NOT NULL DEFAULT 0,

    first_seen DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    last_updated DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),

    PRIMARY KEY (
        hostname,
        channel_name,
        event_id,
        hour_of_day,
        day_of_week
    ),

    KEY idx_baseline_event_stats_event (
        channel_name,
        event_id,
        hour_of_day,
        day_of_week
    ),

    KEY idx_baseline_event_stats_updated (last_updated)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE baseline_exclusions (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    hostname VARCHAR(191) NOT NULL DEFAULT '',
    channel_name VARCHAR(128) NOT NULL DEFAULT '',
    event_id INT UNSIGNED NOT NULL DEFAULT 0,

    reason TEXT NULL,
    created_by VARCHAR(191) NOT NULL DEFAULT '',
    created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    expires_at DATETIME(6) NULL,
    enabled TINYINT(1) NOT NULL DEFAULT 1,

    PRIMARY KEY (id),

    KEY idx_baseline_exclusions_lookup (
        enabled,
        hostname,
        channel_name,
        event_id,
        expires_at
    ),
    KEY idx_baseline_exclusions_expires (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- UEBA Tabellen
-- ---------------------------------------------------------------------

CREATE TABLE ueba_user_baseline (
    username VARCHAR(191) NOT NULL,
    hostname VARCHAR(191) NOT NULL,
    src_ip VARCHAR(64) NOT NULL,
    workstation VARCHAR(191) NOT NULL,

    first_seen DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    last_seen DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    seen_count BIGINT UNSIGNED NOT NULL DEFAULT 0,

    PRIMARY KEY (username, hostname, src_ip, workstation),

    KEY idx_ueba_user_baseline_last_seen (last_seen),
    KEY idx_ueba_user_baseline_user_last_seen (username, last_seen),
    KEY idx_ueba_user_baseline_host_last_seen (hostname, last_seen)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE user_source_ip_seen (
    username VARCHAR(191) NOT NULL,
    src_ip VARCHAR(64) NOT NULL,
    hostname VARCHAR(191) NOT NULL,

    first_seen DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    last_seen DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    seen_count BIGINT UNSIGNED NOT NULL DEFAULT 0,

    PRIMARY KEY (username, src_ip, hostname),

    KEY idx_user_source_ip_seen_last_seen (last_seen),
    KEY idx_user_source_ip_seen_user_last_seen (username, last_seen),
    KEY idx_user_source_ip_seen_src_ip_last_seen (src_ip, last_seen)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE user_privilege_baseline (
    username VARCHAR(191) NOT NULL,

    first_seen DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    last_seen DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    seen_count BIGINT UNSIGNED NOT NULL DEFAULT 0,

    PRIMARY KEY (username),
    KEY idx_user_privilege_baseline_last_seen (last_seen)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE ueba_context_buckets (
    bucket_start DATETIME(6) NOT NULL,
    bucket_end DATETIME(6) NOT NULL,

    username VARCHAR(191) NOT NULL,
    hostname VARCHAR(191) NOT NULL,
    src_ip VARCHAR(64) NOT NULL,
    workstation VARCHAR(191) NOT NULL,

    cnt BIGINT UNSIGNED NOT NULL DEFAULT 0,
    first_event_ts DATETIME(6) NULL,
    last_event_ts DATETIME(6) NULL,

    finalized TINYINT(1) NOT NULL DEFAULT 0,
    checked_at DATETIME(6) NULL,
    learned_at DATETIME(6) NULL,

    updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),

    PRIMARY KEY (
        bucket_start,
        username,
        hostname,
        src_ip,
        workstation
    ),

    KEY idx_ueba_context_check (
        finalized,
        checked_at,
        bucket_start
    ),

    KEY idx_ueba_context_learn (
        finalized,
        learned_at,
        bucket_start
    ),

    KEY idx_ueba_context_lookup (
        username,
        hostname,
        src_ip,
        workstation,
        bucket_start
    )
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Privileged Users
-- ---------------------------------------------------------------------

CREATE TABLE privileged_users (
    username VARCHAR(191) NOT NULL,
    reason TEXT NULL,
    enabled TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),

    PRIMARY KEY (username),
    KEY idx_privileged_users_enabled (enabled)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Host Risk Scores / SOC
-- ---------------------------------------------------------------------

CREATE TABLE host_risk_scores (
    hostname VARCHAR(191) NOT NULL,
    risk_score DOUBLE NOT NULL DEFAULT 0,
    severity ENUM('info','low','medium','high','critical') NOT NULL DEFAULT 'info',

    open_detections INT NOT NULL DEFAULT 0,
    high_detections INT NOT NULL DEFAULT 0,
    critical_detections INT NOT NULL DEFAULT 0,
    confirmed_incidents INT NOT NULL DEFAULT 0,

    last_detection_at DATETIME(6) NULL,
    updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),

    PRIMARY KEY (hostname),
    KEY idx_host_risk_scores_risk (risk_score),
    KEY idx_host_risk_scores_severity_risk (severity, risk_score),
    KEY idx_host_risk_scores_updated (updated_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Partition Management Stored Procedure
-- Erzeugt 3h-Partitionen von UTC now - 6h bis UTC now + 24h.
-- ---------------------------------------------------------------------

DELIMITER $$

CREATE PROCEDURE ensure_siem_partitions()
BEGIN
    DECLARE v_start DATETIME;
    DECLARE v_end DATETIME;
    DECLARE v_current DATETIME;
    DECLARE v_part_end DATETIME;
    DECLARE v_part_name VARCHAR(32);
    DECLARE v_exists INT DEFAULT 0;

    SET v_start = FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(UTC_TIMESTAMP()) / 10800) * 10800);
    SET v_start = DATE_SUB(v_start, INTERVAL 6 HOUR);
    SET v_end = DATE_ADD(FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(UTC_TIMESTAMP()) / 10800) * 10800), INTERVAL 27 HOUR);

    SET v_current = v_start;

    WHILE v_current < v_end DO
        SET v_part_end = DATE_ADD(v_current, INTERVAL 3 HOUR);
        SET v_part_name = CONCAT('p', DATE_FORMAT(v_current, '%Y%m%d%H'));

        SELECT COUNT(*)
          INTO v_exists
        FROM information_schema.PARTITIONS
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME = 'event_logs'
          AND PARTITION_NAME = v_part_name;

        IF v_exists = 0 THEN
            SET @sql_event_logs = CONCAT(
                'ALTER TABLE event_logs REORGANIZE PARTITION pmax INTO (',
                'PARTITION ', v_part_name, ' VALUES LESS THAN (''', DATE_FORMAT(v_part_end, '%Y-%m-%d %H:%i:%s'), '''),',
                'PARTITION pmax VALUES LESS THAN (MAXVALUE))'
            );

            PREPARE stmt_event_logs FROM @sql_event_logs;
            EXECUTE stmt_event_logs;
            DEALLOCATE PREPARE stmt_event_logs;
        END IF;

        SELECT COUNT(*)
          INTO v_exists
        FROM information_schema.PARTITIONS
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME = 'event_logs_raw'
          AND PARTITION_NAME = v_part_name;

        IF v_exists = 0 THEN
            SET @sql_event_logs_raw = CONCAT(
                'ALTER TABLE event_logs_raw REORGANIZE PARTITION pmax INTO (',
                'PARTITION ', v_part_name, ' VALUES LESS THAN (''', DATE_FORMAT(v_part_end, '%Y-%m-%d %H:%i:%s'), '''),',
                'PARTITION pmax VALUES LESS THAN (MAXVALUE))'
            );

            PREPARE stmt_event_logs_raw FROM @sql_event_logs_raw;
            EXECUTE stmt_event_logs_raw;
            DEALLOCATE PREPARE stmt_event_logs_raw;
        END IF;

        SET v_current = v_part_end;
    END WHILE;
END$$

DELIMITER ;

CALL ensure_siem_partitions();
-- ---------------------------------------------------------------------
-- Initiale Beispielregeln
-- Optional. Kann gelöscht werden, wenn Regeln nur über UI gepflegt werden sollen.
-- ---------------------------------------------------------------------

INSERT INTO detection_rules
(
    name,
    description,
    severity,
    channel,
    event_ids,
    match_field,
    match_operator,
    match_value,
    threshold_count,
    threshold_window_seconds,
    suppress_for_seconds,
    enabled
)
VALUES

-- ============================================================
-- Kritische Manipulationen / Spurenverwischung
-- ============================================================

(
    'v1_security_log_cleared',
    'Security Log wurde gelöscht. Das ist fast immer sicherheitsrelevant.',
    'high',
    'Security',
    '1102',
    '',
    '',
    '',
    1,
    0,
    86400,
    1
),

(
    'v1_audit_policy_changed',
    'Audit Policy wurde geändert.',
    'high',
    'Security',
    '4719',
    '',
    '',
    '',
    1,
    0,
    3600,
    1
),

(
    'v1_system_audit_policy_changed',
    'System Audit Policy wurde geändert.',
    'high',
    'Security',
    '4902,4904,4905,4906,4907,4908,4912',
    '',
    '',
    '',
    1,
    0,
    3600,
    1
),

(
    'v1_special_logon',
    'Anmeldung mit speziellen Rechten erkannt.',
    'medium',
    'Security',
    '4672',
    '',
    '',
    '',
    20,
    300,
    1800,
    1
),

(
    'v1_privileged_service_called',
    'Privilegierter Dienst wurde aufgerufen.',
    'medium',
    'Security',
    '4673,4674',
    '',
    '',
    '',
    10,
    300,
    1800,
    1
),

-- ============================================================
-- Benutzerverwaltung
-- ============================================================

(
    'v1_user_created',
    'Neuer AD-Benutzer wurde erstellt.',
    'medium',
    'Security',
    '4720',
    '',
    '',
    '',
    1,
    0,
    1800,
    1
),

(
    'v1_user_enabled',
    'AD-Benutzer wurde aktiviert.',
    'medium',
    'Security',
    '4722',
    '',
    '',
    '',
    1,
    0,
    1800,
    1
),

(
    'v1_user_disabled',
    'AD-Benutzer wurde deaktiviert.',
    'low',
    'Security',
    '4725',
    '',
    '',
    '',
    1,
    0,
    1800,
    1
),

(
    'v1_user_deleted',
    'AD-Benutzer wurde gelöscht.',
    'medium',
    'Security',
    '4726',
    '',
    '',
    '',
    1,
    0,
    3600,
    1
),

(
    'v1_user_changed',
    'AD-Benutzerobjekt wurde geändert.',
    'low',
    'Security',
    '4738',
    '',
    '',
    '',
    1,
    0,
    1800,
    1
),

(
    'v1_password_changed_by_user',
    'Benutzer hat eigenes Passwort geändert.',
    'low',
    'Security',
    '4723',
    '',
    '',
    '',
    1,
    0,
    900,
    1
),

(
    'v1_password_reset',
    'Passwort wurde durch Admin oder berechtigten Benutzer zurückgesetzt.',
    'medium',
    'Security',
    '4724',
    '',
    '',
    '',
    1,
    0,
    1800,
    1
),

(
    'v1_account_lockout',
    'Benutzerkonto wurde gesperrt.',
    'low',
    'Security',
    '4740',
    '',
    '',
    '',
    1,
    0,
    900,
    1
),

(
    'v1_account_lockout_spike',
    'Viele Account Lockouts innerhalb kurzer Zeit.',
    'medium',
    'Security',
    '4740',
    '',
    '',
    '',
    5,
    300,
    1800,
    1
),

-- ============================================================
-- Gruppenverwaltung allgemein
-- ============================================================

(
    'v1_security_group_created',
    'Security-Gruppe wurde erstellt.',
    'medium',
    'Security',
    '4727,4731,4754',
    '',
    '',
    '',
    1,
    0,
    1800,
    1
),

(
    'v1_security_group_deleted',
    'Security-Gruppe wurde gelöscht.',
    'medium',
    'Security',
    '4730,4734,4758',
    '',
    '',
    '',
    1,
    0,
    3600,
    1
),

(
    'v1_security_group_changed',
    'Security-Gruppe wurde geändert.',
    'medium',
    'Security',
    '4735,4737,4755',
    '',
    '',
    '',
    1,
    0,
    1800,
    1
),

(
    'v1_group_member_added',
    'Mitglied wurde zu einer Security-Gruppe hinzugefügt.',
    'medium',
    'Security',
    '4728,4732,4756',
    '',
    '',
    '',
    1,
    0,
    1800,
    1
),

(
    'v1_group_member_removed',
    'Mitglied wurde aus einer Security-Gruppe entfernt.',
    'medium',
    'Security',
    '4729,4733,4757',
    '',
    '',
    '',
    1,
    0,
    1800,
    1
),

-- ============================================================
-- Privilegierte Gruppen
-- Achtung: target_user ist hier normalerweise der Gruppenname.
-- ============================================================

(
    'v1_privileged_group_member_added',
    'Mitglied wurde zu einer privilegierten AD-Gruppe hinzugefügt.',
    'high',
    'Security',
    '4728,4732,4756',
    'target_user',
    'in',
    'Domain Admins,Enterprise Admins,Schema Admins,Administrators,Account Operators,Server Operators,Backup Operators,Print Operators,DNSAdmins,Group Policy Creator Owners,Cert Publishers,Key Admins,Enterprise Key Admins',
    1,
    0,
    3600,
    1
),

(
    'v1_privileged_group_member_removed',
    'Mitglied wurde aus einer privilegierten AD-Gruppe entfernt.',
    'medium',
    'Security',
    '4729,4733,4757',
    'target_user',
    'in',
    'Domain Admins,Enterprise Admins,Schema Admins,Administrators,Account Operators,Server Operators,Backup Operators,Print Operators,DNSAdmins,Group Policy Creator Owners,Cert Publishers,Key Admins,Enterprise Key Admins',
    1,
    0,
    3600,
    1
),

(
    'v1_domain_admins_changed',
    'Änderung an Domain Admins erkannt.',
    'high',
    'Security',
    '4728,4729,4735,4737',
    'target_user',
    'eq',
    'Domain Admins',
    1,
    0,
    3600,
    1
),

(
    'v1_enterprise_admins_changed',
    'Änderung an Enterprise Admins erkannt.',
    'high',
    'Security',
    '4728,4729,4735,4737',
    'target_user',
    'eq',
    'Enterprise Admins',
    1,
    0,
    3600,
    1
),

(
    'v1_schema_admins_changed',
    'Änderung an Schema Admins erkannt.',
    'high',
    'Security',
    '4728,4729,4735,4737',
    'target_user',
    'eq',
    'Schema Admins',
    1,
    0,
    3600,
    1
),

(
    'v1_dnsadmins_changed',
    'Änderung an DNSAdmins erkannt. DNSAdmins können in vielen Umgebungen kritisch sein.',
    'high',
    'Security',
    '4728,4729,4732,4733,4756,4757',
    'target_user',
    'eq',
    'DNSAdmins',
    1,
    0,
    3600,
    1
),

-- ============================================================
-- Computer-Konten
-- ============================================================

(
    'v1_computer_account_created',
    'Computer-Konto wurde erstellt.',
    'low',
    'Security',
    '4741',
    '',
    '',
    '',
    1,
    0,
    1800,
    1
),

(
    'v1_computer_account_changed',
    'Computer-Konto wurde geändert.',
    'low',
    'Security',
    '4742',
    '',
    '',
    '',
    1,
    0,
    1800,
    1
),

(
    'v1_computer_account_deleted',
    'Computer-Konto wurde gelöscht.',
    'medium',
    'Security',
    '4743',
    '',
    '',
    '',
    1,
    0,
    3600,
    1
),

-- ============================================================
-- Kerberos / Authentifizierung
-- ============================================================

(
    'v1_kerberos_preauth_failure',
    'Kerberos PreAuth Fehler erkannt.',
    'low',
    'Security',
    '4771',
    '',
    '',
    '',
    1,
    0,
    300,
    1
),

(
    'v1_kerberos_preauth_spike',
    'Viele Kerberos PreAuth Fehler. Möglicher Password-Spray oder Bruteforce.',
    'high',
    'Security',
    '4771',
    '',
    '',
    '',
    20,
    300,
    1800,
    1
),

(
    'v1_kerberos_tgt_failed_spike',
    'Viele fehlgeschlagene Kerberos-TGT-Anfragen.',
    'medium',
    'Security',
    '4768',
    '',
    '',
    '',
    30,
    300,
    1800,
    1
),

(
    'v1_kerberos_service_ticket_spike',
    'Viele Kerberos-Service-Ticket-Anfragen. Kann auf Kerberoasting oder ungewöhnliche Service-Nutzung hindeuten.',
    'medium',
    'Security',
    '4769',
    '',
    '',
    '',
    100,
    300,
    1800,
    1
),

(
    'v1_ntlm_authentication_spike',
    'Viele NTLM-Authentifizierungen erkannt.',
    'medium',
    'Security',
    '4776',
    '',
    '',
    '',
    50,
    300,
    1800,
    1
),

(
    'v1_logon_failed_spike',
    'Viele fehlgeschlagene Logons.',
    'medium',
    'Security',
    '4625',
    '',
    '',
    '',
    25,
    300,
    1800,
    1
),

(
    'v1_explicit_credentials_used',
    'Anmeldung mit expliziten Anmeldeinformationen erkannt.',
    'medium',
    'Security',
    '4648',
    '',
    '',
    '',
    1,
    0,
    900,
    1
),

(
    'v1_many_explicit_credentials_used',
    'Viele Anmeldungen mit expliziten Anmeldeinformationen.',
    'high',
    'Security',
    '4648',
    '',
    '',
    '',
    20,
    300,
    1800,
    1
),

-- ============================================================
-- Dienste, Tasks, Persistenz
-- ============================================================

(
    'v1_service_installed_security',
    'Neuer Dienst laut Security Log installiert.',
    'high',
    'Security',
    '4697',
    '',
    '',
    '',
    1,
    0,
    3600,
    1
),

(
    'v1_service_installed_system',
    'Neuer Dienst laut System Log installiert.',
    'high',
    'System',
    '7045',
    '',
    '',
    '',
    1,
    0,
    3600,
    1
),

(
    'v1_scheduled_task_created',
    'Geplanter Task wurde erstellt.',
    'high',
    'Security',
    '4698',
    '',
    '',
    '',
    1,
    0,
    3600,
    1
),

(
    'v1_scheduled_task_updated',
    'Geplanter Task wurde geändert.',
    'medium',
    'Security',
    '4702',
    '',
    '',
    '',
    1,
    0,
    1800,
    1
),

(
    'v1_scheduled_task_deleted',
    'Geplanter Task wurde gelöscht.',
    'medium',
    'Security',
    '4699',
    '',
    '',
    '',
    1,
    0,
    1800,
    1
),

(
    'v1_scheduled_task_enabled_disabled',
    'Geplanter Task wurde aktiviert oder deaktiviert.',
    'medium',
    'Security',
    '4700,4701',
    '',
    '',
    '',
    1,
    0,
    1800,
    1
),

-- ============================================================
-- Prozess / PowerShell / Script Logging
-- Nur aktivieren, wenn diese Events bei dir tatsächlich gesammelt werden.
-- ============================================================

(
    'v1_process_created',
    'Neuer Prozess wurde erstellt. Nur sinnvoll bei aktiviertem Process Creation Auditing.',
    'low',
    'Security',
    '4688',
    '',
    '',
    '',
    1,
    0,
    300,
    0
),

(
    'v1_suspicious_powershell_process',
    'Verdächtiger PowerShell-Aufruf im Prozess-Event.',
    'high',
    'Security',
    '4688',
    'msg',
    'contains',
    'powershell',
    1,
    0,
    1800,
    0
),

(
    'v1_powershell_script_block',
    'PowerShell Script Block Logging Event erkannt.',
    'medium',
    'Windows PowerShell,Microsoft-Windows-PowerShell/Operational',
    '4104',
    '',
    '',
    '',
    1,
    0,
    900,
    0
),

(
    'v1_suspicious_powershell_encodedcommand',
    'PowerShell EncodedCommand erkannt.',
    'high',
    'Windows PowerShell,Microsoft-Windows-PowerShell/Operational,Security',
    '4104,4688',
    'msg',
    'contains',
    'EncodedCommand',
    1,
    0,
    1800,
    0
),

(
    'v1_suspicious_powershell_downloadstring',
    'PowerShell DownloadString erkannt.',
    'high',
    'Windows PowerShell,Microsoft-Windows-PowerShell/Operational,Security',
    '4104,4688',
    'msg',
    'contains',
    'DownloadString',
    1,
    0,
    1800,
    0
),

(
    'v1_suspicious_powershell_iex',
    'PowerShell Invoke-Expression / IEX erkannt.',
    'high',
    'Windows PowerShell,Microsoft-Windows-PowerShell/Operational,Security',
    '4104,4688',
    'msg',
    'contains',
    'Invoke-Expression',
    1,
    0,
    1800,
    0
),

-- ============================================================
-- GPO / AD Objektänderungen
-- Hinweis: 5136/5137/5141 kommen aus Directory Service Changes.
-- Dafür muss das passende Auditing auf DCs aktiv sein.
-- ============================================================

(
    'v1_directory_object_modified',
    'AD-Objekt wurde geändert.',
    'medium',
    'Security',
    '5136',
    '',
    '',
    '',
    1,
    0,
    900,
    0
),

(
    'v1_directory_object_created',
    'AD-Objekt wurde erstellt.',
    'medium',
    'Security',
    '5137',
    '',
    '',
    '',
    1,
    0,
    900,
    0
),

(
    'v1_directory_object_deleted',
    'AD-Objekt wurde gelöscht.',
    'medium',
    'Security',
    '5141',
    '',
    '',
    '',
    1,
    0,
    1800,
    0
),

(
    'v1_gpo_changed',
    'Mögliche GPO-Änderung erkannt.',
    'high',
    'Security',
    '5136',
    'msg',
    'contains',
    'CN=Policies,CN=System',
    1,
    0,
    3600,
    0
),

(
    'v1_adminsdholder_changed',
    'AdminSDHolder wurde geändert. Sehr sicherheitsrelevant.',
    'high',
    'Security',
    '5136',
    'msg',
    'contains',
    'CN=AdminSDHolder',
    1,
    0,
    86400,
    0
),

(
    'v1_domain_root_changed',
    'Domain-Root-Objekt wurde geändert.',
    'high',
    'Security',
    '5136',
    'msg',
    'contains',
    'DC=',
    1,
    0,
    3600,
    0
),

-- ============================================================
-- Objektzugriff / ACL
-- Hinweis: 4662/4670 brauchen passende SACLs.
-- ============================================================

(
    'v1_object_permissions_changed',
    'Berechtigungen eines Objekts wurden geändert.',
    'high',
    'Security',
    '4670',
    '',
    '',
    '',
    1,
    0,
    3600,
    0
),

(
    'v1_directory_service_object_access',
    'Directory-Service-Objektzugriff erkannt.',
    'medium',
    'Security',
    '4662',
    '',
    '',
    '',
    10,
    300,
    1800,
    0
),

-- ============================================================
-- Share / SMB / SYSVOL / administrative Zugriffe
-- Hinweis: Kann sehr laut werden.
-- ============================================================

(
    'v1_network_share_accessed',
    'Netzwerkfreigabe wurde genutzt.',
    'low',
    'Security',
    '5140',
    '',
    '',
    '',
    50,
    300,
    900,
    0
),

(
    'v1_network_share_object_checked',
    'Detaillierter Netzwerkfreigabezugriff erkannt.',
    'low',
    'Security',
    '5145',
    '',
    '',
    '',
    100,
    300,
    900,
    0
),

(
    'v1_sysvol_access_spike',
    'Viele Zugriffe auf SYSVOL erkannt.',
    'low',
    'Security',
    '5140,5145',
    'msg',
    'contains',
    'SYSVOL',
    100,
    300,
    900,
    0
),

(
    'v1_admin_share_access',
    'Zugriff auf administrative Freigabe erkannt.',
    'medium',
    'Security',
    '5140,5145',
    'msg',
    'contains',
    'ADMIN$',
    1,
    0,
    1800,
    0
),

(
    'v1_c_share_access',
    'Zugriff auf C$ erkannt.',
    'medium',
    'Security',
    '5140,5145',
    'msg',
    'contains',
    'C$',
    1,
    0,
    1800,
    0
),

-- ============================================================
-- System / Neustart / Agent-Kontext
-- ============================================================

(
    'v1_system_startup',
    'System wurde gestartet.',
    'low',
    'System',
    '6005',
    '',
    '',
    '',
    1,
    0,
    300,
    0
),

(
    'v1_system_shutdown',
    'System wurde heruntergefahren.',
    'low',
    'System',
    '6006',
    '',
    '',
    '',
    1,
    0,
    300,
    0
),

(
    'v1_planned_shutdown_or_restart',
    'Geplanter Shutdown oder Neustart erkannt.',
    'low',
    'System',
    '1074',
    '',
    '',
    '',
    1,
    0,
    900,
    1
),

(
    'v1_unexpected_shutdown',
    'Unerwarteter Shutdown erkannt.',
    'medium',
    'System',
    '6008',
    '',
    '',
    '',
    1,
    0,
    1800,
    1
),

(
    'v1_reboot_spike_dynamic',
    'Viele Neustart-/Shutdown-Events in kurzer Zeit.',
    'medium',
    'System',
    '1074,6005,6006,6008',
    '',
    '',
    '',
    3,
    900,
    1800,
    0
),

-- ============================================================
-- Windows Defender / Security Center
-- Event IDs können je nach Version/Quelle variieren.
-- ============================================================

(
    'v1_defender_malware_detected',
    'Microsoft Defender hat Malware erkannt.',
    'high',
    'Microsoft-Windows-Windows Defender/Operational',
    '1116',
    '',
    '',
    '',
    1,
    0,
    3600,
    0
),

(
    'v1_defender_malware_remediated',
    'Microsoft Defender hat Malware bereinigt.',
    'medium',
    'Microsoft-Windows-Windows Defender/Operational',
    '1117',
    '',
    '',
    '',
    1,
    0,
    1800,
    0
),

(
    'v1_defender_action_failed',
    'Microsoft Defender Aktion fehlgeschlagen.',
    'high',
    'Microsoft-Windows-Windows Defender/Operational',
    '1118,1119',
    '',
    '',
    '',
    1,
    0,
    3600,
    0
),

(
    'v1_defender_disabled_or_config_changed',
    'Defender-Konfiguration wurde geändert oder Schutz deaktiviert.',
    'high',
    'Microsoft-Windows-Windows Defender/Operational',
    '5007,5013',
    '',
    '',
    '',
    1,
    0,
    3600,
    0
),

-- ============================================================
-- Zertifikatsdienste / AD CS
-- Nur aktivieren, wenn AD CS vorhanden und Events gesammelt werden.
-- ============================================================

(
    'v1_certificate_request_issued',
    'Zertifikat wurde ausgestellt.',
    'medium',
    'Security',
    '4886,4887',
    '',
    '',
    '',
    1,
    0,
    1800,
    0
),

(
    'v1_certificate_template_changed',
    'Zertifikatstemplate wurde geändert.',
    'high',
    'Security',
    '4898,4899,4900',
    '',
    '',
    '',
    1,
    0,
    86400,
    0
),

(
    'v1_cert_services_config_changed',
    'AD-CS-Konfiguration wurde geändert.',
    'high',
    'Security',
    '4882,4885,4890,4891,4892',
    '',
    '',
    '',
    1,
    0,
    86400,
    0
),

(
    'v2_security_log_cleared',
    'Security Log wurde gelöscht. Sehr wahrscheinlich Spurenverwischung.',
    'high',
    'Security',
    '1102',
    '', '', '',
    1, 0, 86400,
    1
),

(
    'v2_audit_policy_changed',
    'Audit Policy wurde geändert.',
    'high',
    'Security',
    '4719',
    '', '', '',
    1, 0, 3600,
    1
),

(
    'v2_system_audit_policy_changed',
    'System Audit Policy wurde geändert.',
    'high',
    'Security',
    '4902,4904,4905,4906,4907,4908,4912',
    '', '', '',
    1, 0, 3600,
    1
),

-- ============================================================
-- PRIVILEGIERTE GRUPPEN (WICHTIGSTER BLOCK!)
-- ============================================================

(
    'v2_privileged_group_member_added',
    'Mitglied wurde zu privilegierter AD-Gruppe hinzugefügt.',
    'high',
    'Security',
    '4728,4732,4756',
    'target_user',
    'in',
    'Domain Admins,Enterprise Admins,Schema Admins,Administrators,DNSAdmins',
    1, 0, 3600,
    1
),

(
    'v2_privileged_group_member_removed',
    'Mitglied wurde aus privilegierter AD-Gruppe entfernt.',
    'medium',
    'Security',
    '4729,4733,4757',
    'target_user',
    'in',
    'Domain Admins,Enterprise Admins,Schema Admins,Administrators,DNSAdmins',
    1, 0, 3600,
    1
),

(
    'v2_domain_admins_changed',
    'Änderung an Domain Admins.',
    'high',
    'Security',
    '4728,4729,4735,4737',
    'target_user',
    'eq',
    'Domain Admins',
    1, 0, 3600,
    1
),

(
    'v2_enterprise_admins_changed',
    'Änderung an Enterprise Admins.',
    'high',
    'Security',
    '4728,4729,4735,4737',
    'target_user',
    'eq',
    'Enterprise Admins',
    1, 0, 3600,
    1
),

(
    'v2_schema_admins_changed',
    'Änderung an Schema Admins.',
    'high',
    'Security',
    '4728,4729,4735,4737',
    'target_user',
    'eq',
    'Schema Admins',
    1, 0, 3600,
    1
),

(
    'v2_dnsadmins_changed',
    'Änderung an DNSAdmins erkannt.',
    'high',
    'Security',
    '4728,4729,4732,4733,4756,4757',
    'target_user',
    'eq',
    'DNSAdmins',
    1, 0, 3600,
    1
),

-- ============================================================
-- USER MANAGEMENT
-- ============================================================

(
    'v2_user_created',
    'Neuer AD-Benutzer wurde erstellt.',
    'medium',
    'Security',
    '4720',
    '', '', '',
    1, 0, 1800,
    1
),

(
    'v2_user_enabled',
    'AD-Benutzer wurde aktiviert.',
    'medium',
    'Security',
    '4722',
    '', '', '',
    1, 0, 1800,
    1
),

(
    'v2_user_deleted',
    'AD-Benutzer wurde gelöscht.',
    'medium',
    'Security',
    '4726',
    '', '', '',
    1, 0, 3600,
    1
),

(
    'v2_password_reset',
    'Passwort wurde zurückgesetzt.',
    'medium',
    'Security',
    '4724',
    '', '', '',
    1, 0, 1800,
    1
),

-- ============================================================
-- AUTHENTIFIZIERUNG (ANOMALIEN)
-- ============================================================

(
    'v2_account_lockout_spike',
    'Viele Account Lockouts.',
    'medium',
    'Security',
    '4740',
    '', '', '',
    5, 300, 1800,
    1
),

(
    'v2_kerberos_preauth_spike',
    'Viele Kerberos PreAuth Fehler.',
    'high',
    'Security',
    '4771',
    '', '', '',
    20, 300, 1800,
    1
),

(
    'v2_kerberos_service_ticket_spike',
    'Viele Kerberos Service Tickets (Kerberoasting möglich).',
    'medium',
    'Security',
    '4769',
    '', '', '',
    100, 300, 1800,
    1
),

(
    'v2_ntlm_authentication_spike',
    'Viele NTLM Authentifizierungen.',
    'medium',
    'Security',
    '4776',
    '', '', '',
    50, 300, 1800,
    1
),

(
    'v2_logon_failed_spike',
    'Viele fehlgeschlagene Logons.',
    'medium',
    'Security',
    '4625',
    '', '', '',
    25, 300, 1800,
    1
),

(
    'v2_many_explicit_credentials_used',
    'Viele Logons mit expliziten Credentials.',
    'high',
    'Security',
    '4648',
    '', '', '',
    20, 300, 1800,
    1
),

-- ============================================================
-- PERSISTENZ / LATERAL MOVEMENT
-- ============================================================

(
    'v2_service_installed_security',
    'Neuer Dienst (Security Log).',
    'high',
    'Security',
    '4697',
    '', '', '',
    1, 0, 3600,
    1
),

(
    'v2_service_installed_system',
    'Neuer Dienst (System Log).',
    'high',
    'System',
    '7045',
    '', '', '',
    1, 0, 3600,
    1
),

(
    'v2_scheduled_task_created',
    'Scheduled Task erstellt.',
    'high',
    'Security',
    '4698',
    '', '', '',
    1, 0, 3600,
    1
),

(
    'v2_scheduled_task_updated',
    'Scheduled Task geändert.',
    'medium',
    'Security',
    '4702',
    '', '', '',
    1, 0, 1800,
    1
),

(
    'v2_scheduled_task_deleted',
    'Scheduled Task gelöscht.',
    'medium',
    'Security',
    '4699',
    '', '', '',
    1, 0, 1800,
    1
),

(
    'v2_scheduled_task_enabled_disabled',
    'Scheduled Task aktiviert/deaktiviert.',
    'medium',
    'Security',
    '4700,4701',
    '', '', '',
    1, 0, 1800,
    1
),

-- ============================================================
-- SYSTEM EVENTS
-- ============================================================

(
    'v2_unexpected_shutdown',
    'Unerwarteter Shutdown erkannt.',
    'medium',
    'System',
    '6008',
    '', '', '',
    1, 0, 1800,
    1
)
ON DUPLICATE KEY UPDATE
    description = VALUES(description),
    severity = VALUES(severity),
    channel = VALUES(channel),
    event_ids = VALUES(event_ids),
    match_field = VALUES(match_field),
    match_operator = VALUES(match_operator),
    match_value = VALUES(match_value),
    threshold_count = VALUES(threshold_count),
    threshold_window_seconds = VALUES(threshold_window_seconds),
    suppress_for_seconds = VALUES(suppress_for_seconds),
    enabled = VALUES(enabled);

UPDATE detection_rules
SET enabled = 0
WHERE name LIKE 'v1_%';

-- ---------------------------------------------------------------------
-- Diagnose-Queries nach Restore
-- ---------------------------------------------------------------------
-- SELECT TABLE_NAME, PARTITION_NAME, PARTITION_DESCRIPTION, TABLE_ROWS
-- FROM information_schema.PARTITIONS
-- WHERE TABLE_SCHEMA = DATABASE()
--   AND TABLE_NAME IN ('event_logs', 'event_logs_raw')
-- ORDER BY TABLE_NAME, PARTITION_ORDINAL_POSITION;
--
-- SHOW INDEX FROM event_logs;
-- SHOW INDEX FROM event_logs_raw;
-- SHOW INDEX FROM detections;