CREATE TABLE IF NOT EXISTS `admins` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `username` VARCHAR(50) NOT NULL UNIQUE,
    `password` VARCHAR(255) NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS `users` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `username` VARCHAR(50) NOT NULL UNIQUE,
    `password` VARCHAR(255) NOT NULL,
    `data_limit` BIGINT DEFAULT 0, -- 0 means unlimited or handled by admin
    `data_used` BIGINT DEFAULT 0,
    `expiry_date` DATETIME DEFAULT NULL,
    `status` ENUM('active', 'blocked') DEFAULT 'active',
    `device_id` VARCHAR(255) DEFAULT NULL,
    `last_connected` TIMESTAMP NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS `servers` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL,
    `flag` VARCHAR(50),
    `server_ip` VARCHAR(100),
    `proxy_ip` VARCHAR(100),
    `server_port` INT,
    `ssl_port` INT,
    `proxy_port` INT,
    `server_user` VARCHAR(100),
    `server_pass` VARCHAR(100),
    `info` TEXT,
    `slow_chave` VARCHAR(255),
    `nameserver` VARCHAR(255),
    `server_message` TEXT,
    `udp_acc` TEXT,
    `tunnel_type` VARCHAR(50),
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS `payloads` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL,
    `payload` TEXT,
    `sni` VARCHAR(255),
    `info` TEXT,
    `slow_dns` VARCHAR(255),
    `web_proxy` VARCHAR(100),
    `web_port` INT,
    `is_ssl` TINYINT(1) DEFAULT 0,
    `is_payload_ssl` TINYINT(1) DEFAULT 0,
    `is_slow` TINYINT(1) DEFAULT 0,
    `is_inject` TINYINT(1) DEFAULT 0,
    `is_direct` TINYINT(1) DEFAULT 0,
    `is_web` TINYINT(1) DEFAULT 0,
    `udp_use` TINYINT(1) DEFAULT 0,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS `usage_logs` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT,
    `server_id` INT,
    `data_downloaded` BIGINT DEFAULT 0,
    `data_uploaded` BIGINT DEFAULT 0,
    `recorded_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`server_id`) REFERENCES `servers`(`id`) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS `settings` (
    `setting_key` VARCHAR(50) PRIMARY KEY,
    `setting_value` TEXT
);

CREATE TABLE IF NOT EXISTS `admin_password_history` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `admin_id` INT NOT NULL,
    `changed_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `ip_address` VARCHAR(45),
    `user_agent` VARCHAR(255),
    `status` ENUM('success', 'failed') DEFAULT 'success',
    `reason` VARCHAR(255),
    FOREIGN KEY (`admin_id`) REFERENCES `admins`(`id`) ON DELETE CASCADE,
    INDEX `idx_admin_id` (`admin_id`),
    INDEX `idx_changed_at` (`changed_at`)
);

CREATE TABLE IF NOT EXISTS `admin_rate_limit` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `admin_id` INT NOT NULL,
    `ip_address` VARCHAR(45),
    `attempt_count` INT DEFAULT 1,
    `first_attempt_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `last_attempt_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `unique_admin_ip` (`admin_id`, `ip_address`),
    FOREIGN KEY (`admin_id`) REFERENCES `admins`(`id`) ON DELETE CASCADE
);

-- Reseller Management Tables
CREATE TABLE IF NOT EXISTS `resellers` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `username` VARCHAR(50) NOT NULL UNIQUE,
    `password` VARCHAR(255) NOT NULL,
    `email` VARCHAR(100),
    `business_name` VARCHAR(100),
    `phone` VARCHAR(20),
    `commission_rate` DECIMAL(5, 2) DEFAULT 20.00, -- Commission percentage
    `status` ENUM('active', 'inactive', 'suspended') DEFAULT 'active',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS `reseller_credits` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `reseller_id` INT NOT NULL,
    `total_credits` DECIMAL(10, 2) DEFAULT 0.00,
    `available_credits` DECIMAL(10, 2) DEFAULT 0.00,
    `used_credits` DECIMAL(10, 2) DEFAULT 0.00,
    `last_updated` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`reseller_id`) REFERENCES `resellers`(`id`) ON DELETE CASCADE,
    UNIQUE KEY `unique_reseller` (`reseller_id`)
);

CREATE TABLE IF NOT EXISTS `reseller_credit_history` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `reseller_id` INT NOT NULL,
    `transaction_type` ENUM('purchase', 'usage', 'refund', 'adjustment') DEFAULT 'usage',
    `credits` DECIMAL(10, 2) NOT NULL,
    `amount` DECIMAL(10, 2),
    `description` TEXT,
    `reference_id` VARCHAR(100),
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`reseller_id`) REFERENCES `resellers`(`id`) ON DELETE CASCADE,
    INDEX `idx_reseller_id` (`reseller_id`),
    INDEX `idx_created_at` (`created_at`)
);

CREATE TABLE IF NOT EXISTS `reseller_users` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `reseller_id` INT NOT NULL,
    `user_id` INT NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`reseller_id`) REFERENCES `resellers`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    UNIQUE KEY `unique_reseller_user` (`reseller_id`, `user_id`)
);

CREATE TABLE IF NOT EXISTS `credit_packages` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL,
    `credits` DECIMAL(10, 2) NOT NULL,
    `price` DECIMAL(10, 2) NOT NULL,
    `description` TEXT,
    `is_active` TINYINT(1) DEFAULT 1,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS `user_credit_costs` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `setting_key` VARCHAR(50) UNIQUE,
    `cost_per_user` DECIMAL(10, 2) DEFAULT 1.00, -- Credits per user creation
    `cost_per_gb` DECIMAL(10, 2) DEFAULT 0.10, -- Credits per GB
    `cost_per_day` DECIMAL(10, 2) DEFAULT 0.05 -- Credits per day of extension
);

-- Default Admin Account (password: admin)
INSERT INTO `admins` (`username`, `password`) VALUES
('admin', '$2y$10$ji.XTWiFL/uAogYPPLjaj.syv7rI.H84ZAUkLHu1eNQpRAv6qi.Zi');

-- Default Credit Packages (in Philippine Peso)
INSERT INTO `credit_packages` (`name`, `credits`, `price`, `description`) VALUES
('Starter', 10.00, 275.00, 'Create 10 users'),
('Standard', 50.00, 1100.00, 'Create 50 users'),
('Professional', 150.00, 2750.00, 'Create 150 users'),
('Enterprise', 500.00, 8250.00, 'Unlimited users');

-- Default User Credit Costs
INSERT INTO `user_credit_costs` (`setting_key`, `cost_per_user`) VALUES ('user_base_cost', 1.00);

-- Default Settings
INSERT INTO `settings` (`setting_key`, `setting_value`) VALUES
('app_version', '1.0.0'),
('release_notes', 'Initial release'),
('release_notes_announcement', 'Welcome to the new VPN Configurator!'),
('aes_key', 'cinbdf665$4'),
('dev_name', 'Jay Flores'),
('dev_gcash', '09950391461');

-- AdMob Ad Watch Logs Table (for tracking user ad views and earnings)
CREATE TABLE IF NOT EXISTS `ad_watch_logs` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NOT NULL,
    `ad_type` ENUM('banner', 'interstitial', 'rewarded') NOT NULL,
    `coins_earned` INT DEFAULT 0,
    `watched_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    INDEX `idx_user_id` (`user_id`),
    INDEX `idx_ad_type` (`ad_type`),
    INDEX `idx_watched_at` (`watched_at`)
);

-- User Coins Table (earned from ads, redeemable for services)
CREATE TABLE IF NOT EXISTS `user_coins` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NOT NULL,
    `total_coins` INT DEFAULT 0,
    `available_coins` INT DEFAULT 0,
    `redeemed_coins` INT DEFAULT 0,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    UNIQUE KEY `unique_user` (`user_id`)
);

-- Coin Redemption/Exchange Table
CREATE TABLE IF NOT EXISTS `coin_exchanges` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NOT NULL,
    `exchange_type` ENUM('service_extension', 'new_account', 'cash_withdrawal') NOT NULL,
    `coins_used` INT NOT NULL,
    `details` JSON,
    `status` ENUM('pending', 'approved', 'completed', 'rejected') DEFAULT 'pending',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `completed_at` TIMESTAMP NULL,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    INDEX `idx_user_id` (`user_id`),
    INDEX `idx_exchange_type` (`exchange_type`),
    INDEX `idx_status` (`status`),
    INDEX `idx_created_at` (`created_at`)
);

-- Coin Configuration Settings Table
CREATE TABLE IF NOT EXISTS `coin_settings` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `setting_name` VARCHAR(100) UNIQUE NOT NULL,
    `setting_value` VARCHAR(255) NOT NULL,
    `description` TEXT,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Default Coin Settings
INSERT INTO `coin_settings` (`setting_name`, `setting_value`, `description`) VALUES
('coins_per_month', '100', 'Coins required to extend service by 1 month'),
('coins_per_account', '200', 'Coins required to create a new user account'),
('coins_per_cash_unit', '100', 'Coins required per cash currency unit for withdrawal'),
('min_cash_withdrawal', '10', 'Minimum cash amount for withdrawal'),
('cash_currency', 'PHP', 'Currency for cash withdrawals')
ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value);