-- Fiora Games Database Schema

-- Create database
CREATE DATABASE IF NOT EXISTS fiora_games;
USE fiora_games;

-- Categories Table
CREATE TABLE IF NOT EXISTS categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    slug VARCHAR(50) NOT NULL UNIQUE,
    description TEXT,
    icon VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Games Table
CREATE TABLE IF NOT EXISTS games (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) NOT NULL UNIQUE,
    description TEXT NOT NULL,
    instructions TEXT,
    thumbnail VARCHAR(255) NOT NULL,
    game_url VARCHAR(255) NOT NULL,
    width INT NOT NULL DEFAULT 800,
    height INT NOT NULL DEFAULT 600,
    category_id INT,
    tags VARCHAR(255),
    is_featured TINYINT(1) NOT NULL DEFAULT 0,
    is_published TINYINT(1) NOT NULL DEFAULT 1,
    play_count INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
);

-- Users Table
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    role ENUM('user', 'admin') NOT NULL DEFAULT 'user',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Comments Table
CREATE TABLE IF NOT EXISTS comments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    game_id INT NOT NULL,
    user_id INT,
    guest_name VARCHAR(50),
    comment TEXT NOT NULL,
    is_approved TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (game_id) REFERENCES games(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

-- Ratings Table
CREATE TABLE IF NOT EXISTS ratings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    game_id INT NOT NULL,
    user_id INT,
    rating TINYINT NOT NULL CHECK (rating BETWEEN 1 AND 5),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (game_id) REFERENCES games(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE (game_id, user_id)
);

-- Favorites Table
CREATE TABLE IF NOT EXISTS favorites (
    id INT AUTO_INCREMENT PRIMARY KEY,
    game_id INT NOT NULL,
    user_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (game_id) REFERENCES games(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE (game_id, user_id)
);

-- Cache Table
CREATE TABLE IF NOT EXISTS cache (
    id INT AUTO_INCREMENT PRIMARY KEY,
    cache_key VARCHAR(255) NOT NULL,
    cache_type VARCHAR(50) NOT NULL,
    cache_data LONGTEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (cache_key, cache_type)
);

-- Insert sample categories
INSERT INTO categories (name, slug, description, icon) VALUES
('Action', 'action', 'Fast-paced games that require quick reflexes and good hand-eye coordination.', 'fas fa-bolt'),
('Adventure', 'adventure', 'Games that focus on exploration, puzzle-solving, and narrative.', 'fas fa-mountain'),
('Arcade', 'arcade', 'Classic arcade-style games with simple gameplay and increasing difficulty.', 'fas fa-gamepad'),
('Puzzle', 'puzzle', 'Games that challenge your problem-solving skills and logical thinking.', 'fas fa-puzzle-piece'),
('Racing', 'racing', 'High-speed racing games featuring cars, bikes, and other vehicles.', 'fas fa-car'),
('Sports', 'sports', 'Games based on real-world sports and athletic competitions.', 'fas fa-futbol'),
('Strategy', 'strategy', 'Games that require careful planning and strategic thinking to win.', 'fas fa-chess'),
('Shooting', 'shooting', 'Action games focused on shooting mechanics and precision.', 'fas fa-crosshairs'),
('Multiplayer', 'multiplayer', 'Games designed for two or more players to play together.', 'fas fa-users'),
('io', 'io', 'Multiplayer online games where players compete in real-time.', 'fas fa-globe'),
('Card', 'card', 'Games played with a deck of cards or card-based mechanics.', 'fas fa-clone'),
('Board', 'board', 'Digital adaptations of traditional board games.', 'fas fa-dice'),
('Platformer', 'platformer', 'Games where the character jumps between platforms and obstacles.', 'fas fa-running'),
('Simulation', 'simulation', 'Games that simulate real-world activities and scenarios.', 'fas fa-laptop-code'),
('Idle', 'idle', 'Games that progress even when you\'re not actively playing.', 'fas fa-hourglass-half');

-- Insert sample games
INSERT INTO games (name, slug, description, instructions, thumbnail, game_url, width, height, category_id, tags, is_featured, play_count) VALUES
('Space Invaders', 'space-invaders', 'Classic arcade shooter game where you defend Earth against alien invaders. Shoot down waves of descending aliens before they reach the bottom of the screen.', 'Use arrow keys to move left and right. Press spacebar to shoot. Avoid getting hit by enemy projectiles and prevent aliens from reaching the bottom.', '/img/games/space-invaders.jpg', '/games/space-invaders/index.html', 800, 600, 3, 'arcade,shooter,classic', 1, 12500),

('Puzzle Blocks', 'puzzle-blocks', 'Addictive puzzle game where you arrange falling blocks to create complete rows. Once a row is filled, it disappears, earning you points and creating more space.', 'Use arrow keys to move and rotate blocks. Down arrow speeds up descent. Create complete horizontal lines to clear them and score points.', '/img/games/puzzle-blocks.jpg', '/games/puzzle-blocks/index.html', 600, 800, 4, 'puzzle,blocks,tetris', 1, 9870),

('Car Drift Racer', 'car-drift-racer', 'Exciting racing game with realistic drift mechanics. Race against time on challenging tracks and perform spectacular drifts to earn bonus points.', 'Arrow keys or WASD to control the car. Hold spacebar while turning to drift. Collect power-ups for speed boosts and special abilities.', '/img/games/car-drift-racer.jpg', '/games/car-drift-racer/index.html', 1024, 768, 5, 'racing,cars,3d', 1, 8750),

('Soccer Stars', 'soccer-stars', 'Multiplayer soccer game where you control a team of disc-shaped players. Take turns to flick your players and score goals against your opponent.', 'Click and drag to aim, release to shoot. Score by getting the ball into the opponent\'s goal. First to 5 goals wins the match.', '/img/games/soccer-stars.jpg', '/games/soccer-stars/index.html', 800, 600, 6, 'sports,soccer,multiplayer', 0, 6430),

('Zombie Survival', 'zombie-survival', 'Post-apocalyptic survival game where you fight against hordes of zombies. Collect weapons, ammunition, and supplies to stay alive as long as possible.', 'WASD to move, mouse to aim and shoot. R to reload, E to interact with objects. Survive as many waves as possible.', '/img/games/zombie-survival.jpg', '/games/zombie-survival/index.html', 1024, 768, 1, 'action,zombie,survival', 1, 11200),

('Treasure Hunter', 'treasure-hunter', 'Adventure game where you explore mysterious islands and ancient ruins in search of hidden treasures. Solve puzzles and avoid traps to progress.', 'Arrow keys to move, spacebar to jump. E to interact with objects. Collect all treasure in each level to unlock the next one.', '/img/games/treasure-hunter.jpg', '/games/treasure-hunter/index.html', 800, 600, 2, 'adventure,puzzle,exploration', 0, 5280),

('Master Chess', 'master-chess', 'Classic chess game with multiple difficulty levels. Play against the computer or challenge a friend in local multiplayer mode.', 'Click on a piece to select it, then click on a valid square to move. The game follows standard chess rules.', '/img/games/master-chess.jpg', '/games/master-chess/index.html', 700, 700, 7, 'strategy,board,classic', 0, 4930),

('Snake.io', 'snake-io', 'Multiplayer snake game where you compete against other players online. Grow your snake by collecting food and eliminating opponents.', 'Use mouse to control direction. Collect glowing orbs to grow. Eliminate other players by making them run into your snake.', '/img/games/snake-io.jpg', '/games/snake-io/index.html', 800, 600, 10, 'io,multiplayer,snake', 1, 14650);

-- Insert sample admin user (password: admin123)
INSERT INTO users (username, email, password, role) VALUES
('admin', 'admin@fioragames.com', '$2y$10$8KzO3iJxpwM9OWBHBQDm5OzKkNbDAkEX50OZUUGGfQPJJ4gplmPBi', 'admin'); 