-- Cybersecurity Monitoring System - skema database
-- Impor lewat phpMyAdmin (tab Import) atau: mysql -u root -p < database/database.sql
-- Semua waktu disimpan dalam UTC.

CREATE DATABASE IF NOT EXISTS cybersecurity_monitoring
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE cybersecurity_monitoring;

SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS audit_logs, incident_reports, alerts, file_integrity_logs, system_metrics,
  login_logs, attack_attempts, security_events, sensor_status, settings, users;
SET FOREIGN_KEY_CHECKS = 1;

CREATE TABLE users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(120) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('admin','analyst','viewer') NOT NULL DEFAULT 'viewer',
  status ENUM('active','disabled') NOT NULL DEFAULT 'active',
  last_login_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_users_username (username),
  UNIQUE KEY uq_users_email (email)
) ENGINE=InnoDB;

CREATE TABLE sensor_status (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  sensor_code VARCHAR(50) NOT NULL,
  hostname VARCHAR(100) NOT NULL DEFAULT '',
  os VARCHAR(30) NOT NULL DEFAULT 'unknown',
  sensor_type VARCHAR(30) NOT NULL DEFAULT 'generic',
  api_key_hash CHAR(64) NOT NULL,
  last_seen_at DATETIME NULL,
  status ENUM('online','offline','unknown') NOT NULL DEFAULT 'unknown',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_sensor_code (sensor_code),
  KEY idx_sensor_status (status, last_seen_at)
) ENGINE=InnoDB;

CREATE TABLE security_events (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  incident_code VARCHAR(30) NOT NULL,
  event_time DATETIME NOT NULL,
  event_type VARCHAR(40) NOT NULL,
  source VARCHAR(60) NOT NULL DEFAULT 'engine',
  ip_address VARCHAR(45) NULL,
  risk_level ENUM('normal','warning','high','critical') NOT NULL DEFAULT 'normal',
  summary VARCHAR(500) NOT NULL,
  evidence VARCHAR(500) NULL COMMENT 'cuplikan log yang sudah diredaksi',
  recommendation VARCHAR(500) NULL,
  handling_status ENUM('new','investigating','resolved','false_positive') NOT NULL DEFAULT 'new',
  sensor_id INT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_event_incident_code (incident_code),
  KEY idx_event_time (event_time),
  KEY idx_event_type (event_type, event_time),
  KEY idx_event_ip (ip_address),
  KEY idx_event_risk (risk_level, event_time),
  CONSTRAINT fk_event_sensor FOREIGN KEY (sensor_id) REFERENCES sensor_status(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE attack_attempts (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  event_id BIGINT UNSIGNED NOT NULL,
  attack_type VARCHAR(40) NOT NULL,
  endpoint VARCHAR(255) NULL,
  indicator VARCHAR(255) NOT NULL,
  detected_at DATETIME NOT NULL,
  verification_status ENUM('suspected','confirmed','false_positive') NOT NULL DEFAULT 'suspected',
  KEY idx_attack_type (attack_type, detected_at),
  CONSTRAINT fk_attack_event FOREIGN KEY (event_id) REFERENCES security_events(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE login_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(100) NOT NULL,
  ip_address VARCHAR(45) NULL,
  success TINYINT(1) NOT NULL,
  user_agent VARCHAR(255) NULL,
  source VARCHAR(40) NOT NULL DEFAULT 'dashboard',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_login_ip_time (ip_address, success, created_at),
  KEY idx_login_user_time (username, created_at)
) ENGINE=InnoDB;

CREATE TABLE system_metrics (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  sensor_id INT UNSIGNED NOT NULL,
  cpu_percent DECIMAL(5,2) NULL,
  mem_percent DECIMAL(5,2) NULL,
  disk_percent DECIMAL(5,2) NULL,
  services_json VARCHAR(1000) NULL,
  collected_at DATETIME NOT NULL,
  KEY idx_metric_sensor_time (sensor_id, collected_at),
  CONSTRAINT fk_metric_sensor FOREIGN KEY (sensor_id) REFERENCES sensor_status(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE file_integrity_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  sensor_id INT UNSIGNED NOT NULL,
  file_path VARCHAR(500) NOT NULL,
  baseline_hash CHAR(64) NULL,
  current_hash CHAR(64) NULL,
  status ENUM('ok','modified','missing','new') NOT NULL DEFAULT 'ok',
  checked_at DATETIME NOT NULL,
  KEY idx_fim_sensor_path (sensor_id, file_path(191), checked_at),
  CONSTRAINT fk_fim_sensor FOREIGN KEY (sensor_id) REFERENCES sensor_status(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE alerts (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  event_id BIGINT UNSIGNED NULL,
  priority ENUM('info','warning','high','critical') NOT NULL DEFAULT 'warning',
  message VARCHAR(500) NOT NULL,
  is_read TINYINT(1) NOT NULL DEFAULT 0,
  emailed TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_alert_read (is_read, created_at),
  CONSTRAINT fk_alert_event FOREIGN KEY (event_id) REFERENCES security_events(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE incident_reports (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  event_id BIGINT UNSIGNED NULL,
  incident_code VARCHAR(30) NOT NULL,
  title VARCHAR(200) NOT NULL,
  category VARCHAR(40) NOT NULL DEFAULT 'other',
  risk_level ENUM('normal','warning','high','critical') NOT NULL DEFAULT 'warning',
  chronology TEXT NULL,
  actions_taken TEXT NULL,
  handler_id INT UNSIGNED NULL,
  status ENUM('open','investigating','resolved','closed') NOT NULL DEFAULT 'open',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_incident_code (incident_code),
  KEY idx_incident_status (status, created_at),
  CONSTRAINT fk_incident_event FOREIGN KEY (event_id) REFERENCES security_events(id) ON DELETE SET NULL,
  CONSTRAINT fk_incident_handler FOREIGN KEY (handler_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE audit_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NULL,
  action VARCHAR(60) NOT NULL,
  detail VARCHAR(500) NULL,
  ip_address VARCHAR(45) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_audit_time (created_at),
  CONSTRAINT fk_audit_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE settings (
  setting_key VARCHAR(60) PRIMARY KEY,
  setting_value VARCHAR(255) NOT NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

INSERT INTO settings (setting_key, setting_value) VALUES
 ('brute_force_threshold','5'),
 ('brute_force_window_min','10'),
 ('port_scan_threshold','15'),
 ('port_scan_window_sec','60'),
 ('traffic_spike_factor','3'),
 ('traffic_spike_min','100'),
 ('sensor_offline_sec','180'),
 ('work_hours_start','6'),
 ('work_hours_end','22'),
 ('alert_email',''),
 ('retention_days','90');

-- Akun database minimum (opsional, ganti password sebelum dijalankan):
-- CREATE USER 'csm_app'@'localhost' IDENTIFIED BY 'GANTI_PASSWORD_KUAT';
-- GRANT SELECT, INSERT, UPDATE, DELETE ON cybersecurity_monitoring.* TO 'csm_app'@'localhost';
-- FLUSH PRIVILEGES;