-- =============================================
-- VPN System Database Schema
-- Professional Scalable VPN System
-- =============================================

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";

CREATE DATABASE IF NOT EXISTS `upnewsxy_vpn` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `upnewsxy_vpn`;

-- =============================================
-- Table: admins
-- Purpose: Admin authentication
-- =============================================
CREATE TABLE `admins` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(100) NOT NULL,
  `email` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_login` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default admin (password: AntorNusrat@@13)
-- Password hash generated with: password_hash('AntorNusrat@@13', PASSWORD_BCRYPT)
INSERT INTO `admins` (`username`, `email`, `password`) VALUES
('antunusu', 'xantuf@gmail.com', '$2y$12$92fZXCbVQeLKPrXJbN7HAuVCYlYNr3xA1FxJ7/2z.pqKfZVwYxN3S');

-- =============================================
-- Table: vpn_servers
-- Purpose: Store VPN servers with full .ovpn config
-- =============================================
CREATE TABLE `vpn_servers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `server_name` varchar(255) NOT NULL,
  `country` varchar(100) NOT NULL,
  `flag_url` varchar(500) DEFAULT NULL,
  `ovpn_config` longtext NOT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_status` (`status`),
  KEY `idx_country` (`country`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- Table: api_keys
-- Purpose: Manage API keys for different apps
-- =============================================
CREATE TABLE `api_keys` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `api_key` varchar(64) NOT NULL,
  `app_name` varchar(255) NOT NULL,
  `usage_limit` int(11) DEFAULT NULL COMMENT 'NULL = unlimited, INT = daily limit',
  `used_count` int(11) NOT NULL DEFAULT 0,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_used` timestamp NULL DEFAULT NULL,
  `reset_date` date DEFAULT NULL COMMENT 'Date when usage count was last reset',
  PRIMARY KEY (`id`),
  UNIQUE KEY `api_key` (`api_key`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- Table: api_server_access
-- Purpose: Define which API can access which server
-- =============================================
CREATE TABLE `api_server_access` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `api_key_id` int(11) NOT NULL,
  `server_id` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_access` (`api_key_id`,`server_id`),
  KEY `fk_server` (`server_id`),
  CONSTRAINT `fk_api_key` FOREIGN KEY (`api_key_id`) REFERENCES `api_keys` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_server` FOREIGN KEY (`server_id`) REFERENCES `vpn_servers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- Table: api_logs
-- Purpose: Track all API requests for analytics
-- =============================================
CREATE TABLE `api_logs` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `api_key` varchar(64) NOT NULL,
  `device_id` varchar(255) DEFAULT NULL,
  `request_type` varchar(50) NOT NULL COMMENT 'servers, connect, etc',
  `server_id` int(11) DEFAULT NULL,
  `ip_address` varchar(45) NOT NULL,
  `user_agent` varchar(500) DEFAULT NULL,
  `response_status` varchar(20) NOT NULL COMMENT 'success, error, limit_exceeded, etc',
  `request_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_api_key` (`api_key`),
  KEY `idx_request_time` (`request_time`),
  KEY `idx_device_id` (`device_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- Table: settings
-- Purpose: System-wide settings
-- =============================================
CREATE TABLE `settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `setting_key` varchar(100) NOT NULL,
  `setting_value` text,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `setting_key` (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default settings
INSERT INTO `settings` (`setting_key`, `setting_value`) VALUES
('site_name', 'VPN Management System'),
('max_api_keys', '100'),
('default_api_limit', '1000');

COMMIT;

-- =============================================
-- Indexes for Performance
-- =============================================
-- Already created above with table definitions

-- =============================================
-- Success Message
-- =============================================
SELECT 'Database created successfully!' as status;
