-- database_schema.sql
-- Run this SQL script on your MySQL database to configure persistent multiplayer statistics, user data, and admin tables.

CREATE TABLE IF NOT EXISTS users (
    user_id VARCHAR(50) PRIMARY KEY,
    gmail VARCHAR(100) DEFAULT NULL,
    display_name VARCHAR(100) DEFAULT 'Tapper',
    avatar_url VARCHAR(255) DEFAULT NULL,
    fcm_token VARCHAR(255) DEFAULT NULL,
    coins_balance INT DEFAULT 100,
    subscription_status VARCHAR(50) DEFAULT 'FREE', -- 'FREE', 'PREMIUM'
    subscription_expires BIGINT DEFAULT 0,
    is_banned TINYINT DEFAULT 0,
    created_at BIGINT DEFAULT 0
);

CREATE TABLE IF NOT EXISTS game_sessions (
    session_id VARCHAR(50) PRIMARY KEY,
    user_id VARCHAR(50) NOT NULL,
    game_mode VARCHAR(50) NOT NULL,
    score INT NOT NULL,
    highest_tile INT DEFAULT 0,
    total_taps INT DEFAULT 0,
    duration_seconds INT NOT NULL,
    played_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    synced TINYINT DEFAULT 1
);

CREATE TABLE IF NOT EXISTS versus_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    room_id VARCHAR(50) NOT NULL,
    player1_id VARCHAR(50) NOT NULL,
    player2_id VARCHAR(50) NOT NULL,
    player1_score INT NOT NULL,
    player2_score INT NOT NULL,
    winner_id VARCHAR(50) NOT NULL, -- 'DRAW' or winner user_id
    duration_seconds INT NOT NULL,
    played_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS leaderboard (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id VARCHAR(50) NOT NULL,
    game_mode VARCHAR(50) NOT NULL,
    score INT NOT NULL,
    period VARCHAR(50) DEFAULT 'ALL_TIME',
    updated_at BIGINT DEFAULT 0,
    UNIQUE KEY user_mode_period (user_id, game_mode, period)
);

CREATE TABLE IF NOT EXISTS versus_leaderboard (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id VARCHAR(50) NOT NULL UNIQUE,
    wins INT DEFAULT 0,
    losses INT DEFAULT 0,
    draws INT DEFAULT 0,
    win_streak INT DEFAULT 0,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS room_cards (
    user_id VARCHAR(50) PRIMARY KEY,
    card_balance INT DEFAULT 3,
    last_ad_claimed BIGINT DEFAULT 0,
    last_weekly_grant BIGINT DEFAULT 0,
    last_monthly_grant BIGINT DEFAULT 0,
    total_earned INT DEFAULT 0,
    total_spent INT DEFAULT 0,
    updated_at BIGINT DEFAULT 0
);

CREATE TABLE IF NOT EXISTS ad_config (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ad_type VARCHAR(50) NOT NULL UNIQUE, -- banner, interstitial, rewarded
    ad_unit_id VARCHAR(255) NOT NULL,
    is_enabled TINYINT DEFAULT 1,
    show_interval INT DEFAULT 3
);

-- Initialize default ad config values
INSERT INTO ad_config (ad_type, ad_unit_id, is_enabled, show_interval) 
VALUES 
('banner', 'ca-app-pub-3940256099942544/6300978111', 1, 0),
('interstitial', 'ca-app-pub-3940256099942544/1033173712', 1, 3),
('rewarded', 'ca-app-pub-3940256099942544/5224354917', 1, 0),
('admob_app_id', 'ca-app-pub-3940256099942544~3347511713', 1, 0),
('unity_game_id', '1234567', 1, 0)
ON DUPLICATE KEY UPDATE ad_unit_id=VALUES(ad_unit_id);

CREATE TABLE IF NOT EXISTS admin_logs (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    admin_username VARCHAR(50) NOT NULL,
    admin_action VARCHAR(255) NOT NULL,
    target_user_id VARCHAR(50) DEFAULT NULL,
    details TEXT DEFAULT NULL,
    performed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS notification_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    body TEXT NOT NULL,
    image_url VARCHAR(255) DEFAULT NULL,
    target_type VARCHAR(50) NOT NULL, -- ALL, SPECIFIC, SUBSCRIBED, ACTIVE_TODAY, INACTIVE_7_DAYS
    sent_count INT DEFAULT 0,
    status VARCHAR(50) DEFAULT 'Sent', -- Sent, Scheduled, Failed
    sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS coin_transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id VARCHAR(50) NOT NULL,
    amount INT NOT NULL,
    description VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS user_achievements (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id VARCHAR(50) NOT NULL,
    achievement_id VARCHAR(100) NOT NULL,
    unlocked_at BIGINT NOT NULL,
    UNIQUE KEY user_ach (user_id, achievement_id)
);

CREATE TABLE IF NOT EXISTS subscriptions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id VARCHAR(50) NOT NULL,
    plan_type VARCHAR(50) NOT NULL, -- weekly, monthly, yearly
    status VARCHAR(50) NOT NULL, -- ACTIVE, EXPIRED, CANCELLED
    price DECIMAL(10,2) DEFAULT 0.00,
    start_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expiry_date TIMESTAMP NOT NULL
);