-- ডাটাবেজ স্কিমা ফাইল
-- এই ফাইলটি শেয়ার হোস্টিংয়ে ইম্পোর্ট করুন

-- ডাটাবেজ তৈরি করুন (যদি না থাকে)
CREATE DATABASE IF NOT EXISTS unlimited_mail_bot;
USE unlimited_mail_bot;

-- ইউজার টেবিল
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    telegram_id VARCHAR(50) UNIQUE NOT NULL,
    username VARCHAR(100),
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    balance DECIMAL(10,2) DEFAULT 0.00,
    holding_balance DECIMAL(10,2) DEFAULT 0.00,
    referral_code VARCHAR(20) UNIQUE,
    referred_by VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    status ENUM('active', 'blocked', 'pending') DEFAULT 'active',
    INDEX idx_telegram_id (telegram_id),
    INDEX idx_referral_code (referral_code)
);

-- Gmail সাবমিশন টেবিল
CREATE TABLE IF NOT EXISTS gmail_submissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    gmail_address VARCHAR(255) NOT NULL,
    password VARCHAR(255),
    recovery_email VARCHAR(255),
    status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
    admin_note TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    approved_at TIMESTAMP NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_status (status)
);

-- উইথড্রয়েল টেবিল
CREATE TABLE IF NOT EXISTS withdrawals (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    method ENUM('bkash', 'nagad', 'binance_pay') NOT NULL,
    account_number VARCHAR(50) NOT NULL,
    transaction_id VARCHAR(100),
    status ENUM('pending', 'paid', 'cancelled') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    processed_at TIMESTAMP NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_status (status)
);

-- রেফারাল টেবিল
CREATE TABLE IF NOT EXISTS referrals (
    id INT AUTO_INCREMENT PRIMARY KEY,
    referrer_id INT NOT NULL,
    referred_id INT NOT NULL,
    bonus_amount DECIMAL(10,2) DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (referrer_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (referred_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_referral (referrer_id, referred_id),
    INDEX idx_referrer_id (referrer_id),
    INDEX idx_referred_id (referred_id)
);

-- সেটিংস টেবিল
CREATE TABLE IF NOT EXISTS settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    `key` VARCHAR(100) UNIQUE NOT NULL,
    `value` TEXT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_key (`key`)
);

-- ডিফল্ট সেটিংস ইনসার্ট করুন
INSERT INTO settings (`key`, `value`) VALUES
('gmail_price', '10.00'),
('referral_bonus', '5.00'),
('min_withdrawal', '50.00'),
('create_gmail_enabled', '1'),
('tutorial_video_url', ''),
('default_password', ''),
('default_recovery_email', ''),
('announcement_text', ''),
('announcement_photo', ''),
('announcement_button_text', ''),
('announcement_button_url', '')
ON DUPLICATE KEY UPDATE `value` = VALUES(`value`);

-- ট্রিগার: নতুন ইউজার তৈরি হলে রেফারাল কোড জেনারেট করুন
DELIMITER $$
CREATE TRIGGER generate_referral_code
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    IF NEW.referral_code IS NULL THEN
        SET NEW.referral_code = CONCAT('REF', LPAD(NEW.id, 6, '0'));
    END IF;
END$$
DELIMITER ;

-- ওয়েবহুক সেটিংস টেবিল
CREATE TABLE IF NOT EXISTS webhook_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    bot_token VARCHAR(255) NOT NULL,
    webhook_url TEXT,
    status ENUM('connected', 'disconnected') DEFAULT 'disconnected',
    setup_date TIMESTAMP NULL,
    last_test TIMESTAMP NULL,
    test_status ENUM('success', 'failed') DEFAULT 'failed',
    INDEX idx_bot_token (bot_token)
);

-- অ্যাডমিন ইউজার টেবিল
CREATE TABLE IF NOT EXISTS admin_users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    name VARCHAR(255),
    role ENUM('admin', 'super_admin') DEFAULT 'admin',
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    last_login TIMESTAMP NULL,
    INDEX idx_email (email),
    INDEX idx_status (status)
);

-- ডিফল্ট ওয়েবহুক সেটিংস ইনসার্ট করুন
INSERT INTO webhook_settings (bot_token, webhook_url, status) VALUES
('YOUR_BOT_TOKEN_HERE', '', 'disconnected')
ON DUPLICATE KEY UPDATE bot_token = VALUES(bot_token);

-- ডিফল্ট অ্যাডমিন ইউজার ইনসার্ট করুন (admin@gmail.com / admin123)
INSERT INTO admin_users (email, password_hash, name, role, status) VALUES
('admin@gmail.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Admin User', 'super_admin', 'active')
ON DUPLICATE KEY UPDATE email = VALUES(email);

-- ট্রিগার: রেফারাল বোনাস যোগ করুন
DELIMITER $$
CREATE TRIGGER add_referral_bonus
AFTER INSERT ON users
FOR EACH ROW
BEGIN
    DECLARE referrer_exists INT DEFAULT 0;
    
    -- চেক করুন রেফারার আছে কিনা
    SELECT COUNT(*) INTO referrer_exists 
    FROM users 
    WHERE telegram_id = NEW.referred_by AND status = 'active';
    
    IF referrer_exists > 0 AND NEW.referred_by IS NOT NULL THEN
        -- রেফারারের ব্যালেন্স আপডেট করুন
        UPDATE users 
        SET balance = balance + (SELECT CAST(`value` AS DECIMAL(10,2)) FROM settings WHERE `key` = 'referral_bonus')
        WHERE telegram_id = NEW.referred_by;
        
        -- রেফারাল রেকর্ড তৈরি করুন
        INSERT INTO referrals (referrer_id, referred_id, bonus_amount)
        SELECT u.id, NEW.id, CAST(s.`value` AS DECIMAL(10,2))
        FROM users u, settings s
        WHERE u.telegram_id = NEW.referred_by AND s.`key` = 'referral_bonus';
    END IF;
END$$
DELIMITER ;
