-- =============================================
-- MINI APP ECOSYSTEM - DATABASE SCHEMA
-- Phase 1: QuickPlay + ColorX
-- =============================================

CREATE DATABASE IF NOT EXISTS miniapp_ecosystem CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE miniapp_ecosystem;

-- USERS TABLE
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    telegram_id BIGINT UNIQUE NOT NULL,
    username VARCHAR(100),
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    photo_url TEXT,
    balance DECIMAL(15,2) DEFAULT 0.00,
    total_deposited DECIMAL(15,2) DEFAULT 0.00,
    total_games INT DEFAULT 0,
    total_wins INT DEFAULT 0,
    total_won DECIMAL(15,2) DEFAULT 0.00,
    vip_level INT DEFAULT 0,
    xp INT DEFAULT 0,
    streak INT DEFAULT 0,
    last_login DATE,
    last_daily_bonus DATE,
    referral_code VARCHAR(20) UNIQUE,
    referred_by INT DEFAULT NULL,
    status ENUM('active','banned') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- TRANSACTIONS TABLE
CREATE TABLE IF NOT EXISTS transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    app_id VARCHAR(20) DEFAULT 'shared',
    type ENUM('deposit','withdraw','game_bet','game_win','daily_bonus','referral_bonus','vip_bonus') NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    balance_before DECIMAL(15,2) NOT NULL,
    balance_after DECIMAL(15,2) NOT NULL,
    description VARCHAR(255),
    status ENUM('pending','completed','failed') DEFAULT 'completed',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- GAME HISTORY TABLE (per app)
CREATE TABLE IF NOT EXISTS game_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    app_id VARCHAR(20) NOT NULL,
    game_type VARCHAR(50) NOT NULL,
    bet_amount DECIMAL(15,2) NOT NULL,
    result VARCHAR(255),
    win_amount DECIMAL(15,2) DEFAULT 0,
    is_win TINYINT(1) DEFAULT 0,
    house_edge DECIMAL(5,2) DEFAULT 4.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- LEADERBOARD TABLE (per app weekly)
CREATE TABLE IF NOT EXISTS leaderboard (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    app_id VARCHAR(20) NOT NULL,
    week_start DATE NOT NULL,
    score DECIMAL(15,2) DEFAULT 0,
    rank_position INT DEFAULT 0,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_user_app_week (user_id, app_id, week_start),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- REFERRALS TABLE
CREATE TABLE IF NOT EXISTS referrals (
    id INT AUTO_INCREMENT PRIMARY KEY,
    referrer_id INT NOT NULL,
    referred_id INT NOT NULL,
    bonus_paid TINYINT(1) DEFAULT 0,
    bonus_amount DECIMAL(15,2) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (referrer_id) REFERENCES users(id),
    FOREIGN KEY (referred_id) REFERENCES users(id)
);

-- STAR PACKAGES TABLE
CREATE TABLE IF NOT EXISTS star_packages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    stars_amount INT NOT NULL,
    coins_amount INT NOT NULL,
    bonus_coins INT DEFAULT 0,
    label VARCHAR(50),
    is_popular TINYINT(1) DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1
);

-- INSERT DEFAULT STAR PACKAGES
INSERT INTO star_packages (stars_amount, coins_amount, bonus_coins, label, is_popular) VALUES
(10, 100, 0, 'Starter Pack', 0),
(50, 550, 50, 'Popular Pack', 1),
(100, 1200, 200, 'Best Value', 0),
(200, 2800, 700, 'Pro Pack', 0),
(400, 6000, 2000, 'VIP Pack', 0);

-- GAME SETTINGS TABLE
CREATE TABLE IF NOT EXISTS game_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    app_id VARCHAR(20) NOT NULL,
    game_type VARCHAR(50) NOT NULL,
    house_edge DECIMAL(5,2) DEFAULT 4.00,
    min_bet DECIMAL(10,2) DEFAULT 10.00,
    max_bet DECIMAL(10,2) DEFAULT 10000.00,
    is_active TINYINT(1) DEFAULT 1,
    UNIQUE KEY unique_app_game (app_id, game_type)
);

-- INSERT DEFAULT GAME SETTINGS
INSERT INTO game_settings (app_id, game_type, house_edge, min_bet, max_bet) VALUES
('quickplay', 'dice', 4.00, 10, 10000),
('quickplay', 'coinflip', 4.00, 10, 10000),
('quickplay', 'wheel', 6.00, 10, 10000),
('colorx', 'colorprediction', 6.00, 10, 10000),
('colorx', 'numberbet', 5.00, 10, 10000);

-- VIP LEVELS TABLE
CREATE TABLE IF NOT EXISTS vip_levels (
    level INT PRIMARY KEY,
    name VARCHAR(50),
    xp_required INT,
    daily_bonus INT DEFAULT 0,
    cashback_percent DECIMAL(5,2) DEFAULT 0
);

INSERT INTO vip_levels (level, name, xp_required, daily_bonus, cashback_percent) VALUES
(0, 'Starter', 0, 50, 0),
(1, 'Bronze', 1000, 100, 1),
(2, 'Silver', 5000, 200, 2),
(3, 'Gold', 15000, 500, 3),
(4, 'Platinum', 50000, 1000, 5),
(5, 'Diamond', 150000, 2500, 8);

-- DEPOSITS TABLE (Telegram Stars)
CREATE TABLE IF NOT EXISTS deposits (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    telegram_payment_id VARCHAR(100),
    stars_amount INT NOT NULL,
    coins_credited INT NOT NULL,
    status ENUM('pending','completed','failed') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- INDEXES FOR PERFORMANCE
CREATE INDEX idx_transactions_user ON transactions(user_id);
CREATE INDEX idx_game_history_user ON game_history(user_id, app_id);
CREATE INDEX idx_leaderboard_app_week ON leaderboard(app_id, week_start);
CREATE INDEX idx_users_telegram ON users(telegram_id);
CREATE INDEX idx_users_referral ON users(referral_code);
