-- ═══════════════════════════════════════════
-- PHASE 2 DATABASE ADDITIONS
-- ═══════════════════════════════════════════

-- Update star packages to 6 plans (1 Star = 1 Coin)
DELETE FROM star_packages;
INSERT INTO star_packages (stars_amount, coins_amount, bonus_coins, label, is_popular) VALUES
(250,  250,  0,    'Starter',  0),
(500,  500,  25,   'Basic',    0),
(1000, 1000, 100,  'Popular',  1),
(2500, 2500, 350,  'Pro',      0),
(5000, 5000, 1000, 'VIP',      0),
(10000,10000,2500, 'Elite',    0);

-- VIP Levels table (updated)
CREATE TABLE IF NOT EXISTS vip_levels (
    level INT PRIMARY KEY,
    name VARCHAR(20),
    xp_required INT DEFAULT 0,
    daily_bonus INT DEFAULT 50,
    referral_bonus_pct DECIMAL(5,2) DEFAULT 0,
    cashback_pct DECIMAL(5,2) DEFAULT 0,
    badge_img VARCHAR(100),
    color VARCHAR(20),
    perks TEXT
);

INSERT INTO vip_levels (level, name, xp_required, daily_bonus, referral_bonus_pct, cashback_pct, badge_img, color, perks) VALUES
(0, 'Bronze',  0,      50,  0,   0,   'vip/vip-0.png',  '#CD7F32', 'Daily bonus, Basic games'),
(1, 'Silver',  1000,   75,  1,   0.5, 'vip/vip-1.png',  '#C0C0C0', 'Higher daily bonus, 1% referral bonus'),
(2, 'Gold',    5000,   100, 2,   1,   'vip/vip-2.png',  '#FFD700', 'VIP daily bonus, 2% referral, 1% cashback'),
(3, 'Diamond', 15000,  150, 3,   2,   'vip/vip-3.png',  '#00D4FF', 'Premium bonus, 3% referral, 2% cashback'),
(4, 'Elite',   50000,  250, 5,   3,   'vip/vip-4.png',  '#FF6B00', 'Max bonus, 5% referral, 3% cashback, VIP games'),
(5, 'Legend',  150000, 500, 10,  5,   'vip/vip-5.png',  '#FF00FF', 'Legendary perks, 10% referral, 5% cashback, All features')
ON DUPLICATE KEY UPDATE
  name=VALUES(name), xp_required=VALUES(xp_required),
  daily_bonus=VALUES(daily_bonus), referral_bonus_pct=VALUES(referral_bonus_pct),
  cashback_pct=VALUES(cashback_pct), badge_img=VALUES(badge_img);

-- Tasks table
CREATE TABLE IF NOT EXISTS tasks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    app_id VARCHAR(20) DEFAULT 'all',
    task_type ENUM('join_channel','invite','play_games','daily_click','follow_social') NOT NULL,
    title VARCHAR(100) NOT NULL,
    description VARCHAR(255),
    reward_coins INT DEFAULT 0,
    target_value INT DEFAULT 1,
    icon_img VARCHAR(100),
    social_url VARCHAR(255),
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO tasks (app_id, task_type, title, description, reward_coins, target_value, icon_img, social_url) VALUES
('all',      'join_channel', 'Join QuickPlay Channel', 'Join our official Telegram channel', 200, 1, 'tasks/icon_telegram.png', 'https://t.me/QuickPlayOfficial'),
('all',      'join_channel', 'Follow on Twitter/X',    'Follow us on X (Twitter)',           150, 1, 'tasks/icon_twitter.png',  'https://twitter.com/QuickPlay'),
('all',      'join_channel', 'Subscribe YouTube',      'Subscribe to our YouTube channel',   150, 1, 'tasks/icon_youtube.png',  'https://youtube.com/@QuickPlay'),
('all',      'invite',       'Invite 5 Friends',       'Invite 5 friends to join',           300, 5, 'tasks/referral_icon.png', NULL),
('all',      'invite',       'Invite 10 Friends',      'Invite 10 friends to join',          750, 10,'tasks/referral_icon.png', NULL),
('all',      'invite',       'Invite 25 Friends',      'Invite 25 friends to join',          2000,25,'tasks/referral_icon.png', NULL),
('all',      'invite',       'Invite 50 Friends',      'Invite 50 friends to join',          5000,50,'tasks/referral_icon.png', NULL),
('all',      'invite',       'Invite 100 Friends',     'Invite 100 friends to join',         15000,100,'tasks/referral_icon.png',NULL),
('all',      'play_games',   'Play 10 Games',          'Play any 10 games',                  100, 10,'tasks/ctrl_teal.png',    NULL),
('all',      'play_games',   'Play 100 Games',         'Play any 100 games',                 500, 100,'tasks/ctrl_teal.png',   NULL),
('all',      'daily_click',  'Daily Check-in',         'Log in and claim daily bonus',       50,  1, 'tasks/calendar_gold.png', NULL);

-- User Tasks Progress
CREATE TABLE IF NOT EXISTS user_tasks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    task_id INT NOT NULL,
    progress INT DEFAULT 0,
    completed TINYINT(1) DEFAULT 0,
    claimed TINYINT(1) DEFAULT 0,
    completed_at TIMESTAMP NULL,
    UNIQUE KEY unique_user_task (user_id, task_id),
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (task_id) REFERENCES tasks(id)
);

-- Withdrawals table
CREATE TABLE IF NOT EXISTS withdrawals (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    app_id VARCHAR(20) DEFAULT 'quickplay',
    amount DECIMAL(10,2) NOT NULL,
    method ENUM('ton','stars','other') DEFAULT 'ton',
    wallet_address VARCHAR(255),
    status ENUM('pending','approved','rejected','paid') DEFAULT 'pending',
    admin_note TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Referral milestones
CREATE TABLE IF NOT EXISTS referral_milestones (
    id INT AUTO_INCREMENT PRIMARY KEY,
    milestone INT NOT NULL,
    reward_coins INT NOT NULL,
    badge_title VARCHAR(50),
    UNIQUE KEY unique_milestone (milestone)
);

INSERT INTO referral_milestones (milestone, reward_coins, badge_title) VALUES
(5,   500,   'Recruiter'),
(10,  1000,  'Connector'),
(25,  3000,  'Influencer'),
(50,  7500,  'Ambassador'),
(100, 20000, 'Legend'),
(200, 50000, 'Elite Legend'),
(500, 150000,'Grand Master')
ON DUPLICATE KEY UPDATE reward_coins=VALUES(reward_coins);

-- User milestone claims
CREATE TABLE IF NOT EXISTS user_milestone_claims (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    milestone INT NOT NULL,
    claimed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_claim (user_id, milestone)
);

-- Leaderboard rewards
CREATE TABLE IF NOT EXISTS leaderboard_rewards (
    id INT AUTO_INCREMENT PRIMARY KEY,
    rank_from INT NOT NULL,
    rank_to INT NOT NULL,
    reward_coins INT NOT NULL,
    week_start DATE NOT NULL
);

-- Weekly leaderboard distributed
CREATE TABLE IF NOT EXISTS leaderboard_distributions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    app_id VARCHAR(20),
    week_start DATE NOT NULL,
    rank INT,
    reward_coins INT,
    distributed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_dist (user_id, week_start, app_id)
);

-- Game settings for new games
INSERT INTO game_settings (app_id, game_type, house_edge, min_bet, max_bet) VALUES
('crashrush', 'crash',   4.00, 10, 50000),
('crashrush', 'slots',   5.00, 10, 10000),
('crashrush', 'wheel',   5.00, 10, 10000),
('bombrush',  'mines',   4.00, 10, 50000),
('bombrush',  'cashout', 4.00, 10, 50000),
('bombrush',  'double',  4.00, 10, 50000),
('quickplay', 'spinwheel',5.00, 10, 10000),
('colorx',    'scratch', 5.00, 10, 5000)
ON DUPLICATE KEY UPDATE house_edge=VALUES(house_edge);

SELECT 'Phase 2 schema applied!' as status;

-- Tap to Earn columns
ALTER TABLE users ADD COLUMN IF NOT EXISTS total_taps INT DEFAULT 0;
ALTER TABLE users ADD COLUMN IF NOT EXISTS tap_energy INT DEFAULT 1000;
ALTER TABLE users ADD COLUMN IF NOT EXISTS max_energy INT DEFAULT 1000;
ALTER TABLE users ADD COLUMN IF NOT EXISTS tap_per_click INT DEFAULT 1;
ALTER TABLE users ADD COLUMN IF NOT EXISTS tap_level INT DEFAULT 1;
ALTER TABLE users ADD COLUMN IF NOT EXISTS tap_multiplier DECIMAL(4,2) DEFAULT 1.00;

-- VIP extra columns for 6-level plan
ALTER TABLE vip_levels ADD COLUMN IF NOT EXISTS referrals_required INT DEFAULT 0;
ALTER TABLE vip_levels ADD COLUMN IF NOT EXISTS reward_pct INT DEFAULT 0;
ALTER TABLE vip_levels ADD COLUMN IF NOT EXISTS withdrawal_min INT DEFAULT 0;
ALTER TABLE vip_levels ADD COLUMN IF NOT EXISTS withdrawal_cooldown_days INT DEFAULT 7;

-- User streak tracking
ALTER TABLE users ADD COLUMN IF NOT EXISTS streak INT DEFAULT 0;
ALTER TABLE users ADD COLUMN IF NOT EXISTS last_streak_date DATE DEFAULT NULL;

-- Stars payment coin packages
CREATE TABLE IF NOT EXISTS coin_packages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    coins_amount INT NOT NULL,
    bonus_coins INT DEFAULT 0,
    stars_amount INT NOT NULL,
    is_active TINYINT DEFAULT 1,
    sort_order INT DEFAULT 0
);
INSERT IGNORE INTO coin_packages (id, name, coins_amount, bonus_coins, stars_amount, is_active, sort_order) VALUES
(1, 'Starter Pack',   1000,   0, 50,  1, 1),
(2, 'Bronze Pack',    2500, 250, 100, 1, 2),
(3, 'Silver Pack',    5000, 750, 200, 1, 3),
(4, 'Gold Pack',     10000,2000, 350, 1, 4),
(5, 'Diamond Pack',  25000,5000, 750, 1, 5),
(6, 'Crown Pack',    50000,15000,1500,1, 6);
