-- ============================================
-- 🗄️ ساختار دیتابیس ربات SMS Bomber
-- ============================================

CREATE DATABASE IF NOT EXISTS `sms_bot` 
CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE `sms_bot`;

-- جدول کاربران
CREATE TABLE IF NOT EXISTS `users` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` BIGINT UNSIGNED NOT NULL UNIQUE COMMENT 'آیدی عددی تلگرام',
    `username` VARCHAR(100) DEFAULT 'none',
    `first_name` VARCHAR(100) NOT NULL,
    `diamonds` INT UNSIGNED DEFAULT 3 COMMENT 'موجودی الماس',
    `is_vip` TINYINT(1) DEFAULT 0,
    `vip_expire` DATETIME NULL,
    `is_blocked` TINYINT(1) DEFAULT 0,
    `invite_code` VARCHAR(20) NOT NULL UNIQUE,
    `invited_by` BIGINT UNSIGNED NULL,
    `total_invites` INT UNSIGNED DEFAULT 0,
    `last_daily` DATETIME NULL,
    `total_sms_sent` INT UNSIGNED DEFAULT 0,
    `joined_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `last_activity` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX `idx_user_id` (`user_id`),
    INDEX `idx_invite_code` (`invite_code`),
    INDEX `idx_is_vip` (`is_vip`),
    INDEX `idx_is_blocked` (`is_blocked`),
    INDEX `idx_diamonds` (`diamonds`),
    INDEX `idx_joined` (`joined_at`),
    INDEX `idx_username` (`username`),
    INDEX `idx_first_name` (`first_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- جدول تنظیمات
CREATE TABLE IF NOT EXISTS `settings` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `key_name` VARCHAR(100) NOT NULL UNIQUE,
    `value` TEXT,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX `idx_key` (`key_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- جدول State کاربران (موقت)
CREATE TABLE IF NOT EXISTS `user_states` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` BIGINT UNSIGNED NOT NULL,
    `state` VARCHAR(50) NOT NULL,
    `data` JSON,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `expires_at` DATETIME NOT NULL,
    
    UNIQUE KEY `uk_user` (`user_id`),
    INDEX `idx_expires` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- جدول تراکنش‌های الماس
CREATE TABLE IF NOT EXISTS `diamond_transactions` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `from_user_id` BIGINT UNSIGNED NULL,
    `to_user_id` BIGINT UNSIGNED NOT NULL,
    `amount` INT NOT NULL,
    `type` ENUM('transfer', 'daily', 'invite', 'reward', 'admin_gift', 'purchase', 'sms_cost') NOT NULL,
    `description` VARCHAR(255),
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    INDEX `idx_from` (`from_user_id`),
    INDEX `idx_to` (`to_user_id`),
    INDEX `idx_type` (`type`),
    INDEX `idx_date` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- جدول لاگ فعالیت‌ها
CREATE TABLE IF NOT EXISTS `activity_logs` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` BIGINT UNSIGNED NULL,
    `action` VARCHAR(100) NOT NULL,
    `details` TEXT,
    `ip_address` VARCHAR(45),
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    INDEX `idx_user` (`user_id`),
    INDEX `idx_action` (`action`),
    INDEX `idx_date` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- جدول SMS های ارسال شده
CREATE TABLE IF NOT EXISTS `sms_logs` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` BIGINT UNSIGNED NOT NULL,
    `phone` VARCHAR(20) NOT NULL,
    `type` ENUM('normal', 'vip') NOT NULL,
    `services_count` INT UNSIGNED DEFAULT 0,
    `duration` INT UNSIGNED DEFAULT 0 COMMENT 'مدت زمان به ثانیه',
    `status` ENUM('success', 'stopped', 'error') DEFAULT 'success',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    INDEX `idx_user` (`user_id`),
    INDEX `idx_phone` (`phone`),
    INDEX `idx_type` (`type`),
    INDEX `idx_date` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- جدول ادمین‌ها
CREATE TABLE IF NOT EXISTS `admins` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `username` VARCHAR(50) NOT NULL UNIQUE,
    `password_hash` VARCHAR(255) NOT NULL,
    `telegram_id` BIGINT UNSIGNED NULL,
    `last_login` DATETIME NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    INDEX `idx_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 📥 داده‌های اولیه
-- ============================================

-- تنظیمات پیش‌فرض
INSERT INTO `settings` (`key_name`, `value`) VALUES
('vip_price', '100'),
('vip_duration_days', '30'),
('daily_diamond_min', '1'),
('daily_diamond_max', '2'),
('invite_bonus', '1'),
('sms_cost', '1'),
('initial_diamonds', '3'),
('maintenance_mode', '0'),
('welcome_message', '🎉 به ربات خوش آمدید!'),
('support_message', '🛠 برای پشتیبانی به آیدی زیر پیام دهید:\n@support_admin')
ON DUPLICATE KEY UPDATE `value` = VALUES(`value`);

-- ادمین پیش‌فرض (رمز: admin123)
INSERT INTO `admins` (`username`, `password_hash`) VALUES
('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2u0hWQ1GKqm')
ON DUPLICATE KEY UPDATE `password_hash` = VALUES(`password_hash`);

-- ============================================
-- 🧹 پاکسازی خودکار (Event Scheduler)
-- ============================================

SET GLOBAL event_scheduler = ON;

-- پاک کردن State های منقضی شده (هر ساعت)
CREATE EVENT IF NOT EXISTS `cleanup_states`
ON SCHEDULE EVERY 1 HOUR
DO DELETE FROM `user_states` WHERE `expires_at` < NOW();

-- بروزرسانی کاربران VIP منقضی شده (هر روز)
CREATE EVENT IF NOT EXISTS `expire_vip`
ON SCHEDULE EVERY 1 DAY
DO UPDATE `users` SET `is_vip` = 0 
WHERE `is_vip` = 1 AND `vip_expire` < NOW();