-- ===== USERS TABLE =====
CREATE TABLE IF NOT EXISTS users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    telegram_id BIGINT UNIQUE NOT NULL,
    username VARCHAR(255),
    first_name VARCHAR(255),
    last_name VARCHAR(255),
    phone VARCHAR(20),
    email VARCHAR(255) UNIQUE,
    coupons_count INT DEFAULT 0,
    total_bets INT DEFAULT 0,
    total_wins INT DEFAULT 0,
    balance DECIMAL(10,2) DEFAULT 0,
    referral_code VARCHAR(50) UNIQUE,
    referred_by INT,
    is_banned BOOLEAN DEFAULT false,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (referred_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX (telegram_id),
    INDEX (referral_code),
    INDEX (created_at)
);

-- ===== TOURNAMENTS TABLE =====
CREATE TABLE IF NOT EXISTS tournaments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    tournament_date DATE NOT NULL,
    tournament_type ENUM('most_wins', 'all_games') DEFAULT 'most_wins',
    total_games INT DEFAULT 8,
    prize_pool DECIMAL(10,2) DEFAULT 0,
    status ENUM('upcoming', 'active', 'finished', 'cancelled') DEFAULT 'upcoming',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX (tournament_date),
    INDEX (status)
);

-- ===== GAMES TABLE =====
CREATE TABLE IF NOT EXISTS games (
    id INT PRIMARY KEY AUTO_INCREMENT,
    tournament_id INT NOT NULL,
    match_number INT NOT NULL,
    team_a VARCHAR(255) NOT NULL,
    team_b VARCHAR(255) NOT NULL,
    match_date DATETIME,
    league VARCHAR(255),
    odds_1 DECIMAL(4,2),
    odds_x DECIMAL(4,2),
    odds_2 DECIMAL(4,2),
    result ENUM('1', 'X', '2', 'cancelled', 'pending'),
    final_score VARCHAR(20),
    status ENUM('scheduled', 'live', 'finished', 'cancelled') DEFAULT 'scheduled',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (tournament_id) REFERENCES tournaments(id) ON DELETE CASCADE,
    UNIQUE KEY unique_tournament_match (tournament_id, match_number),
    INDEX (tournament_id),
    INDEX (status)
);

-- ===== COUPONS TABLE =====
CREATE TABLE IF NOT EXISTS coupons (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    tournament_id INT NOT NULL,
    coupon_code VARCHAR(50) UNIQUE,
    total_predictions INT DEFAULT 0,
    correct_predictions INT DEFAULT 0,
    status ENUM('pending', 'won', 'lost', 'partial') DEFAULT 'pending',
    is_used BOOLEAN DEFAULT false,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (tournament_id) REFERENCES tournaments(id) ON DELETE CASCADE,
    INDEX (user_id),
    INDEX (tournament_id),
    INDEX (status),
    INDEX (created_at)
);

-- ===== PREDICTIONS TABLE =====
CREATE TABLE IF NOT EXISTS predictions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    coupon_id INT NOT NULL,
    game_id INT NOT NULL,
    user_prediction ENUM('1', 'X', '2') NOT NULL,
    is_correct BOOLEAN DEFAULT false,
    prediction_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (coupon_id) REFERENCES coupons(id) ON DELETE CASCADE,
    FOREIGN KEY (game_id) REFERENCES games(id) ON DELETE CASCADE,
    UNIQUE KEY unique_coupon_game (coupon_id, game_id),
    INDEX (coupon_id),
    INDEX (game_id),
    INDEX (is_correct)
);

-- ===== LEADERBOARD TABLE (Cached) =====
CREATE TABLE IF NOT EXISTS leaderboard (
    id INT PRIMARY KEY AUTO_INCREMENT,
    tournament_id INT NOT NULL,
    user_id INT NOT NULL,
    correct_wins INT DEFAULT 0,
    rank INT,
    is_winner BOOLEAN DEFAULT false,
    prize_amount DECIMAL(10,2) DEFAULT 0,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (tournament_id) REFERENCES tournaments(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_tournament_user (tournament_id, user_id),
    INDEX (tournament_id),
    INDEX (correct_wins),
    INDEX (rank)
);

-- ===== NOTIFICATIONS TABLE =====
CREATE TABLE IF NOT EXISTS notifications (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    tournament_id INT,
    coupon_id INT,
    type ENUM('game_result', 'coupon_result', 'new_tournament', 'referral_bonus', 'promo', 'system') DEFAULT 'system',
    title VARCHAR(255),
    message TEXT,
    is_read BOOLEAN DEFAULT false,
    sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (tournament_id) REFERENCES tournaments(id) ON DELETE SET NULL,
    FOREIGN KEY (coupon_id) REFERENCES coupons(id) ON DELETE SET NULL,
    INDEX (user_id),
    INDEX (is_read),
    INDEX (sent_at)
);

-- ===== PROMO CODES TABLE =====
CREATE TABLE IF NOT EXISTS promo_codes (
    id INT PRIMARY KEY AUTO_INCREMENT,
    code VARCHAR(50) UNIQUE NOT NULL,
    discount_type ENUM('percentage', 'fixed') DEFAULT 'fixed',
    discount_value DECIMAL(10,2) NOT NULL,
    bonus_coupons INT DEFAULT 0,
    max_uses INT,
    current_uses INT DEFAULT 0,
    is_active BOOLEAN DEFAULT true,
    expiry_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX (code),
    INDEX (is_active)
);

-- ===== REFERRAL BONUS TABLE =====
CREATE TABLE IF NOT EXISTS referral_bonuses (
    id INT PRIMARY KEY AUTO_INCREMENT,
    referrer_id INT NOT NULL,
    referred_id INT NOT NULL,
    bonus_coupons INT DEFAULT 1,
    is_claimed BOOLEAN DEFAULT false,
    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 (referrer_id),
    INDEX (is_claimed)
);

-- ===== USER SESSIONS TABLE =====
CREATE TABLE IF NOT EXISTS user_sessions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    session_token VARCHAR(255) UNIQUE,
    ip_address VARCHAR(45),
    user_agent TEXT,
    last_activity TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at DATETIME,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX (user_id),
    INDEX (session_token)
);

-- ===== ADMIN LOGS TABLE =====
CREATE TABLE IF NOT EXISTS admin_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    admin_id INT,
    action VARCHAR(255),
    description TEXT,
    table_name VARCHAR(100),
    record_id INT,
    old_value JSON,
    new_value JSON,
    ip_address VARCHAR(45),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX (admin_id),
    INDEX (created_at),
    INDEX (action)
);

-- ===== PAYMENTS TABLE =====
CREATE TABLE IF NOT EXISTS payments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    amount DECIMAL(10,2),
    gateway VARCHAR(50),
    transaction_id VARCHAR(255) UNIQUE,
    status ENUM('pending', 'success', 'failed', 'cancelled') DEFAULT 'pending',
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX (user_id),
    INDEX (status),
    INDEX (created_at)
);

-- ===== STATISTICS TABLE =====
CREATE TABLE IF NOT EXISTS statistics (
    id INT PRIMARY KEY AUTO_INCREMENT,
    tournament_id INT NOT NULL,
    total_participants INT DEFAULT 0,
    total_coupons INT DEFAULT 0,
    total_correct_predictions INT DEFAULT 0,
    total_incorrect_predictions INT DEFAULT 0,
    average_correct_per_user DECIMAL(5,2) DEFAULT 0,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (tournament_id) REFERENCES tournaments(id) ON DELETE CASCADE,
    UNIQUE KEY unique_tournament_stats (tournament_id),
    INDEX (tournament_id)
);