-- Fighter Jet Game Database Schema
-- Database: ahmeuesz_fighterjet

-- Users table
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    firebase_uid VARCHAR(128) UNIQUE,
    email VARCHAR(255) NOT NULL UNIQUE,
    username VARCHAR(50) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email (email),
    INDEX idx_firebase_uid (firebase_uid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Matches table
CREATE TABLE IF NOT EXISTS matches (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    score INT NOT NULL DEFAULT 0,
    enemies_destroyed INT NOT NULL DEFAULT 0,
    play_time_seconds INT NOT NULL DEFAULT 0,
    game_mode ENUM('offline', 'online') DEFAULT 'offline',
    room_code VARCHAR(10) DEFAULT NULL,
    played_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_score (score DESC),
    INDEX idx_played_at (played_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- User stats view (for quick profile stats)
CREATE OR REPLACE VIEW user_stats AS
SELECT 
    u.id as user_id,
    u.username,
    COUNT(m.id) as total_games,
    COALESCE(MAX(m.score), 0) as high_score,
    COALESCE(SUM(m.score), 0) as total_score,
    COALESCE(SUM(m.enemies_destroyed), 0) as total_enemies_destroyed,
    COALESCE(SUM(m.play_time_seconds), 0) as total_play_time
FROM users u
LEFT JOIN matches m ON u.id = m.user_id
GROUP BY u.id;

-- Leaderboard view (top 100 by high score)
CREATE OR REPLACE VIEW leaderboard AS
SELECT 
    u.id as user_id,
    u.username,
    MAX(m.score) as high_score,
    COUNT(m.id) as games_played
FROM users u
JOIN matches m ON u.id = m.user_id
GROUP BY u.id
ORDER BY high_score DESC
LIMIT 100;