-- =============================================================================
-- SAPPS Backend – Migration untuk phpMyAdmin
-- IP Analytics: HANYA mencatat IP yang bisa/tidak bisa akses DB. Bukan block/whitelist.
-- Pilih database, jalankan per BAGIAN. Jika objek sudah ada, abaikan.
-- =============================================================================

-- ========== BAGIAN 1: Tabel IP Tracking (jika belum ada) ==========

-- 1.1 connection_attempts
CREATE TABLE IF NOT EXISTS `connection_attempts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ip_address` varchar(45) NOT NULL,
  `user_agent` text,
  `isp_name` varchar(100) DEFAULT NULL,
  `isp_detected` varchar(50) DEFAULT NULL,
  `country` varchar(50) DEFAULT NULL,
  `city` varchar(100) DEFAULT NULL,
  `connection_status` enum('success','failed','timeout') NOT NULL,
  `error_type` varchar(100) DEFAULT NULL,
  `error_message` text,
  `error_code` varchar(50) DEFAULT NULL,
  `attempt_timestamp` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `response_time_ms` int(11) DEFAULT NULL,
  `endpoint_attempted` varchar(255) DEFAULT NULL,
  `request_method` varchar(10) DEFAULT NULL,
  `asn` varchar(50) DEFAULT NULL,
  `asn_org` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_connection_attempts_ip` (`ip_address`),
  KEY `idx_connection_attempts_status` (`connection_status`),
  KEY `idx_connection_attempts_timestamp` (`attempt_timestamp`),
  KEY `idx_connection_attempts_isp` (`isp_detected`),
  KEY `idx_connection_attempts_endpoint` (`endpoint_attempted`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 1.2 isp_statistics
CREATE TABLE IF NOT EXISTS `isp_statistics` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `isp_name` varchar(50) NOT NULL,
  `total_attempts` int(11) DEFAULT 0,
  `successful_attempts` int(11) DEFAULT 0,
  `failed_attempts` int(11) DEFAULT 0,
  `timeout_count` int(11) DEFAULT 0,
  `blocked_count` int(11) DEFAULT 0,
  `avg_response_time_ms` decimal(10,2) DEFAULT 0.00,
  `success_rate` decimal(5,2) DEFAULT 0.00,
  `last_success` datetime DEFAULT NULL,
  `last_failure` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_isp_statistics_name` (`isp_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 1.3 blocked_ips (OPSIONAL: tidak dipakai untuk fitur "hanya mencatat", bukan block/whitelist. Bisa dilewati.)
CREATE TABLE IF NOT EXISTS `blocked_ips` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ip_address` varchar(45) NOT NULL,
  `isp_name` varchar(50) DEFAULT NULL,
  `block_reason` varchar(255) DEFAULT NULL,
  `first_blocked` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_attempt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `attempt_count` int(11) DEFAULT 1,
  `status` enum('temporary','permanent','resolved','whitelisted') DEFAULT 'temporary',
  `notes` text,
  PRIMARY KEY (`id`),
  KEY `idx_blocked_ips_ip` (`ip_address`),
  KEY `idx_blocked_ips_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- ========== BAGIAN 2: Composite index (optimasi getRecentFailures) ==========
-- Jalankan setelah BAGIAN 1. Jika error "Duplicate key name", indeks sudah ada — abaikan.

CREATE INDEX `idx_connection_attempts_status_timestamp` ON `connection_attempts` (`connection_status`, `attempt_timestamp`);