

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
);

-- ========================================================
-- ADMIN SECURITY TABLES
-- ========================================================

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
);

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,
    `cost_per_gb` DECIMAL(10, 2) DEFAULT 0.10,
    `cost_per_day` DECIMAL(10, 2) DEFAULT 0.05
);

-- ========================================================
-- COIN SYSTEM TABLES
-- ========================================================

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`)
);

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,
    `redemption_lock` BOOLEAN DEFAULT FALSE,
    `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`),
    INDEX `idx_redemption_lock` (`redemption_lock`)
);

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`)
);

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
);

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');

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');

INSERT INTO `user_credit_costs` (`setting_key`, `cost_per_user`) VALUES
('user_base_cost', 1.00);

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);
