-- M33 malware detection stack (ADR-0072).
-- Five tables backing /jabali-admin/security?tab=malware.
-- Schema only — settings singleton seed is a literal INSERT (no SELECT
-- from app-populated tables, per feedback_migration_data_seed_ordering).

CREATE TABLE malware_quarantine (
  id              VARCHAR(26) NOT NULL PRIMARY KEY,
  user_id         VARCHAR(26) NULL,
  original_path   VARCHAR(2048) NOT NULL,
  quarantine_path VARCHAR(2048) NOT NULL,
  signature       VARCHAR(255) NOT NULL,
  scanner         ENUM('maldet','clamav','yara','tetragon') NOT NULL,
  sha256          CHAR(64) NOT NULL,
  size_bytes      BIGINT UNSIGNED NOT NULL DEFAULT 0,
  detected_at     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  restored_at     TIMESTAMP NULL,
  restored_reason VARCHAR(500) NULL,
  restored_by     VARCHAR(26) NULL,
  deleted_at      TIMESTAMP NULL,
  INDEX idx_quarantine_user (user_id, detected_at),
  INDEX idx_quarantine_active (deleted_at, restored_at, detected_at),
  INDEX idx_quarantine_sha (sha256)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE malware_events (
  id           VARCHAR(26) NOT NULL PRIMARY KEY,
  event_type   VARCHAR(64) NOT NULL,
  source       ENUM('maldet','clamav','yara','tetragon') NOT NULL,
  severity     ENUM('info','warn','critical') NOT NULL DEFAULT 'warn',
  user_id      VARCHAR(26) NULL,
  target_path  VARCHAR(2048) NULL,
  target_pid   INT UNSIGNED NULL,
  signature    VARCHAR(255) NULL,
  raw_json     JSON NOT NULL,
  notified     TINYINT(1) NOT NULL DEFAULT 0,
  created_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_events_created (created_at),
  INDEX idx_events_source_severity (source, severity, created_at),
  INDEX idx_events_user (user_id, created_at),
  INDEX idx_events_notified (notified, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE yara_custom_rules (
  id           VARCHAR(26) NOT NULL PRIMARY KEY,
  filename     VARCHAR(255) NOT NULL,
  enabled      TINYINT(1) NOT NULL DEFAULT 1,
  uploaded_by  VARCHAR(26) NOT NULL,
  sha256       CHAR(64) NOT NULL,
  created_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uk_yara_filename (filename)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE tetragon_policy_state (
  policy_name  VARCHAR(128) NOT NULL PRIMARY KEY,
  enabled      TINYINT(1) NOT NULL DEFAULT 1,
  updated_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  updated_by   VARCHAR(26) NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE malware_settings (
  id                     TINYINT UNSIGNED NOT NULL PRIMARY KEY DEFAULT 1,
  schedule_cron          VARCHAR(64) NOT NULL DEFAULT '0 3 * * *',
  quarantine_retain_days INT UNSIGNED NOT NULL DEFAULT 30,
  max_scan_mb            INT UNSIGNED NOT NULL DEFAULT 256,
  notify_threshold       ENUM('info','warn','critical') NOT NULL DEFAULT 'warn',
  realtime_enabled       TINYINT(1) NOT NULL DEFAULT 1,
  tetragon_enabled       TINYINT(1) NOT NULL DEFAULT 1,
  CONSTRAINT chk_malware_settings_singleton CHECK (id = 1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO malware_settings (id) VALUES (1);
