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

START TRANSACTION;

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

-- =============================================
-- 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: 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;
