-- ╭━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╮
-- ┃     👑 LX SIGNAL BOT DATABASE    ┃
-- ╰━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╯

CREATE TABLE IF NOT EXISTS `users` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` BIGINT UNIQUE NOT NULL,
    `first_name` VARCHAR(255) DEFAULT '',
    `last_name` VARCHAR(255) DEFAULT '',
    `username` VARCHAR(255) DEFAULT '',
    `language` VARCHAR(10) DEFAULT 'en',
    `ref_balance` DECIMAL(10,2) DEFAULT 0.00,
    `aff_balance` DECIMAL(10,2) DEFAULT 0.00,
    `live_signal_limit` INT DEFAULT 5,
    `referrer_id` BIGINT DEFAULT NULL,
    `referral_count` INT DEFAULT 0,
    `affiliate_count` INT DEFAULT 0,
    `is_banned` TINYINT(1) DEFAULT 0,
    `ban_reason` TEXT DEFAULT NULL,
    `last_signal_time` DATETIME DEFAULT NULL,
    `last_signal_market` VARCHAR(50) DEFAULT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `bot_settings` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `setting_key` VARCHAR(100) UNIQUE NOT NULL,
    `setting_value` TEXT,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `channels` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `channel_id` VARCHAR(100) NOT NULL,
    `channel_username` VARCHAR(255) NOT NULL,
    `channel_name` VARCHAR(255) DEFAULT '',
    `is_active` TINYINT(1) DEFAULT 1,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `redeem_codes` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `code` VARCHAR(100) UNIQUE NOT NULL,
    `type` ENUM('referral', 'affiliate', 'signal', 'custom') NOT NULL,
    `ref_amount` DECIMAL(10,2) DEFAULT 0.00,
    `aff_amount` DECIMAL(10,2) DEFAULT 0.00,
    `signal_amount` INT DEFAULT 0,
    `max_uses` INT DEFAULT 1,
    `used_count` INT DEFAULT 0,
    `is_active` TINYINT(1) DEFAULT 1,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `redeem_history` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` BIGINT NOT NULL,
    `code_id` INT NOT NULL,
    `redeemed_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`code_id`) REFERENCES `redeem_codes`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `affiliate_submissions` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` BIGINT NOT NULL,
    `trader_id` VARCHAR(100) NOT NULL,
    `proof_file_id` VARCHAR(255) NOT NULL,
    `status` ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
    `reviewed_by` BIGINT DEFAULT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `withdrawals` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` BIGINT NOT NULL,
    `type` ENUM('referral', 'affiliate') NOT NULL,
    `amount` DECIMAL(10,2) NOT NULL,
    `binance_id` VARCHAR(255) NOT NULL,
    `status` ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
    `reviewed_by` BIGINT DEFAULT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `support_tickets` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `ticket_id` VARCHAR(20) UNIQUE NOT NULL,
    `user_id` BIGINT NOT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `support_messages` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `ticket_id` VARCHAR(20) NOT NULL,
    `sender_type` ENUM('user', 'admin') NOT NULL,
    `message_type` ENUM('text', 'photo', 'video', 'document') NOT NULL,
    `message_text` TEXT,
    `file_id` VARCHAR(255) DEFAULT NULL,
    `reply_to_id` INT DEFAULT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`reply_to_id`) REFERENCES `support_messages`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `leaderboard` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` BIGINT NOT NULL,
    `month` INT NOT NULL,
    `year` INT NOT NULL,
    `referral_count` INT DEFAULT 0,
    `affiliate_count` INT DEFAULT 0,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY `user_month_year` (`user_id`, `month`, `year`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `daily_stats` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `date` DATE UNIQUE NOT NULL,
    `new_users` INT DEFAULT 0,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Default Settings
INSERT INTO `bot_settings` (`setting_key`, `setting_value`) VALUES
('welcome_text', '👑 Welcome to LX Signal Bot!\n\n🚀 Your premium trading companion'),
('welcome_media', ''),
('welcome_media_type', 'photo'),
('text_format', 'HTML'),
('join_channel_text', '📢 Join Our Channel'),
('join_channel_url', 'https://t.me/yourchannel'),
('referral_amount', '0.50'),
('affiliate_amount', '5.00'),
('min_ref_withdraw', '10.00'),
('max_ref_withdraw', '100.00'),
('min_aff_withdraw', '20.00'),
('max_aff_withdraw', '500.00'),
('quotex_affiliate_link', 'https://quotex.com/affiliate'),
('admin_ids', ''),
('default_signal_limit', '5')
ON DUPLICATE KEY UPDATE `setting_key` = `setting_key`;
