-- SQL Implementation for IP Tracking System
-- Run this in your MySQL Database (sapharmaco_sapps)

-- 1. Table: connection_attempts
-- Mencatat setiap percobaan koneksi dengan detail lengkap
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;

-- 2. Table: isp_statistics
-- Statistik agregat per ISP untuk performa dan blocking analysis
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;

-- 3. Table: blocked_ips
-- Daftar IP yang sering gagal atau diblokir
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;