-- ============================================================================
-- SCLS — Security Command Logging System
-- Migration 06: Encrypted audit log, decryption trail, rate limits, tool registry
-- Engine DB · Idempotent · MariaDB 10.11+
-- ============================================================================

USE engine;

-- ────────────────────────────────────────────────────────────────────────────
-- 1. sec_audit_log — Main encrypted audit table
--    Stores encrypted command/tool execution statements with per-node keys.
--    Partitioned monthly on started_at for efficient range pruning.
-- ────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS sec_audit_log (
    id                   BIGINT UNSIGNED AUTO_INCREMENT,
    event_uuid           CHAR(36)          NOT NULL,
    node_id              VARCHAR(10)       NOT NULL,
    tool_id              CHAR(4)           NOT NULL,
    tool_name            VARCHAR(50)       NOT NULL,
    tool_category        VARCHAR(20)       DEFAULT 'general',
    encrypted_statement  MEDIUMTEXT        NOT NULL,
    encryption_iv        CHAR(32)          NOT NULL,
    encryption_version   TINYINT UNSIGNED  DEFAULT 1,
    hmac_signature       CHAR(64)          NOT NULL,
    session_id           VARCHAR(50)       NULL,
    admin_id             VARCHAR(50)       NULL,
    trust_score          DECIMAL(3,2)      NULL,
    exit_code            SMALLINT          NULL,
    duration_ms          INT UNSIGNED      NULL,
    cpu_percent          DECIMAL(5,2)      NULL,
    memory_mb            DECIMAL(8,2)      NULL,
    source_ip            VARBINARY(16)     NULL,
    network_zone         VARCHAR(20)       NULL,
    status               ENUM('pending','success','failed','timeout','killed') DEFAULT 'pending',
    severity             ENUM('info','low','medium','high','critical')         DEFAULT 'info',
    flags                INT UNSIGNED      DEFAULT 0,
    started_at           DATETIME(3)       NOT NULL,
    completed_at         DATETIME(3)       NULL,
    created_at           DATETIME          NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at           DATETIME          NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (id, started_at),

    INDEX idx_node_time       (node_id, started_at),
    INDEX idx_tool_time       (tool_id, started_at),
    INDEX idx_session         (session_id),
    INDEX idx_event_uuid      (event_uuid),
    INDEX idx_status_severity (status, severity),
    INDEX idx_created         (created_at),
    INDEX idx_trust_score     (trust_score),
    INDEX idx_admin           (admin_id)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  PARTITION BY RANGE (YEAR(started_at) * 100 + MONTH(started_at)) (
    PARTITION p2026_07 VALUES LESS THAN (202608),
    PARTITION p2026_08 VALUES LESS THAN (202609),
    PARTITION p2026_09 VALUES LESS THAN (202610),
    PARTITION p2026_10 VALUES LESS THAN (202611),
    PARTITION p2026_11 VALUES LESS THAN (202612),
    PARTITION p2026_12 VALUES LESS THAN (202701),
    PARTITION pfuture  VALUES LESS THAN MAXVALUE
);


-- ────────────────────────────────────────────────────────────────────────────
-- 2. sec_audit_decryptions — Decryption audit trail
--    Every time an admin decrypts an audit entry, it is recorded here.
-- ────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS sec_audit_decryptions (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    audit_id        BIGINT UNSIGNED       NOT NULL,
    admin_id        VARCHAR(50)           NOT NULL,
    reason          VARCHAR(500)          NOT NULL,
    ip_address      VARCHAR(45)           NOT NULL,
    user_agent      VARCHAR(300)          NULL,
    decrypted_at    DATETIME              NOT NULL DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_audit_id    (audit_id),
    INDEX idx_admin_id    (admin_id),
    INDEX idx_decrypted   (decrypted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ────────────────────────────────────────────────────────────────────────────
-- 3. sec_audit_rate_limits — Per-admin decryption rate limiting
--    Sliding-window counters to prevent bulk decryption abuse.
-- ────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS sec_audit_rate_limits (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    admin_id        VARCHAR(50)           NOT NULL,
    window_start    DATETIME              NOT NULL,
    request_count   INT UNSIGNED          DEFAULT 1,
    total_lifetime  BIGINT UNSIGNED       DEFAULT 1,
    last_request    DATETIME              NOT NULL DEFAULT CURRENT_TIMESTAMP,

    UNIQUE KEY uk_admin_window (admin_id, window_start),
    INDEX idx_admin_last (admin_id, last_request)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ────────────────────────────────────────────────────────────────────────────
-- 4. sec_tool_registry — Canonical tool catalog
--    Seed data for every registered security/system tool across the fleet.
-- ────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS sec_tool_registry (
    tool_id         CHAR(4)               NOT NULL PRIMARY KEY,
    tool_name       VARCHAR(50)           NOT NULL,
    tool_category   VARCHAR(20)           NOT NULL DEFAULT 'general',
    description     VARCHAR(200)          NULL,
    is_active       TINYINT UNSIGNED      DEFAULT 1,
    created_at      DATETIME              NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME              NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_category  (tool_category),
    INDEX idx_active    (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ────────────────────────────────────────────────────────────────────────────
-- Seed data: tool registry
-- Uses INSERT IGNORE so re-running is safe (idempotent).
-- ────────────────────────────────────────────────────────────────────────────
INSERT IGNORE INTO sec_tool_registry (tool_id, tool_name, tool_category, description) VALUES
    ('a1f0', 'postfix-qmgr-aux',      'system',  'Postfix queue-manager auxiliary maintenance'),
    ('b2e0', 'oomd-report.sh',        'defense', 'OOM kill reporter and memory pressure watchdog'),
    ('c3d0', 'common.sh',             'system',  'Shared utility functions library'),
    ('d4c0', 'svcbeat',               'system',  'Service heartbeat daemon'),
    ('d4c2', 'runtime-health.sh',     'system',  'Runtime health check and metrics collector'),
    ('e5b0', 'svcfile',               'defense', 'File integrity monitoring service'),
    ('e5b1', 'svcfw',                 'defense', 'Firewall rule enforcement service'),
    ('f6a0', 'svcpass',               'defense', 'Credential rotation and password enforcement'),
    ('f6a1', 'security-audit.sh',     'defense', 'Full node security audit sweep'),
    ('f6a9', 'log_to_db.sh',          'defense', 'Security event logger to engine database'),
    ('1a00', 'network-control.sh',    'recon',   'Network interface and route diagnostics'),
    ('2b00', 'data-backup.sh',        'system',  'Encrypted backup to off-site vault'),
    ('4d00', 'phoenix-core.sh',       'defense', 'Phoenix self-healing recovery core'),
    ('7a00', 'tvault',                'system',  'Token vault management daemon'),
    ('9d00', 'security-analyzer.sh',  'defense', 'Behavioral anomaly and threat analyzer'),
    ('3c00', 'fleet_audit.sh',        'system',  'Cross-fleet audit aggregation tool');
