-- DDL untuk Monitoring & Keamanan Sistem
-- Berdasarkan RANCANGAN_MONITORING_KEAMANAN.md

CREATE TABLE `sys_performance_logs` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `url` varchar(255) NOT NULL,
  `method` varchar(10) NOT NULL,
  `execution_time` float NOT NULL COMMENT 'dalam detik (presisi tinggi)',
  `memory_usage` varchar(20) NOT NULL COMMENT 'dalam MB',
  `query_count` int(11) NOT NULL,
  `ip_address` varchar(45) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `sys_db_table_snapshots` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `table_name` VARCHAR(255) NOT NULL,
    `row_count` BIGINT NOT NULL COMMENT 'Monthly Peak Row Count',
    `size_mb` FLOAT NOT NULL,
    `snapshot_month` INT NOT NULL,
    `snapshot_year` INT NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `sys_auth_logs` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `nip` varchar(100) DEFAULT NULL, -- Menyesuaikan dengan username/NIP di users
  `ip_address` varchar(45) NOT NULL,
  `user_agent` text NOT NULL,
  `activity` enum('LOGIN_SUCCESS','LOGIN_FAILED','LOGOUT','SESSION_EXPIRED') NOT NULL,
  `reason` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_nip` (`nip`),
  KEY `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `sys_activity_logs` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `nip` varchar(100) NOT NULL,
  `module` varchar(50) NOT NULL,
  `action` enum('INSERT','UPDATE','DELETE','EXPORT','PRINT') NOT NULL,
  `table_name` varchar(50) NOT NULL,
  `data_before` longtext DEFAULT NULL,
  `data_after` longtext DEFAULT NULL,
  `ip_address` varchar(45) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_nip` (`nip`),
  KEY `idx_module` (`module`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `ci_sessions` (
  `id` varchar(128) NOT NULL,
  `ip_address` varchar(45) NOT NULL,
  `timestamp` timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL,
  `data` blob NOT NULL,
  KEY `ci_sessions_timestamp` (`timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;