DROP DATABASE IF EXISTS logicprops_game;
CREATE DATABASE logicprops_game CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE logicprops_game;

CREATE TABLE sessions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    session_name VARCHAR(150) NOT NULL,
    session_code VARCHAR(10) NOT NULL UNIQUE,
    status ENUM('OPEN','CLOSED','FINISHED') DEFAULT 'OPEN',
    remote_enabled TINYINT(1) DEFAULT 0,
    remote_mode ENUM('AUTO','MANUAL') DEFAULT 'AUTO',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE players (
    id INT AUTO_INCREMENT PRIMARY KEY,
    session_id INT NOT NULL,
    player_name VARCHAR(100) NOT NULL,
    total_points INT DEFAULT 0,
    last_seen DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_player_session (session_id, player_name),
    FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE
);

CREATE TABLE templates (
    id INT AUTO_INCREMENT PRIMARY KEY,
    template_name VARCHAR(150) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE template_games (
    id INT AUTO_INCREMENT PRIMARY KEY,
    template_id INT NOT NULL,
    game_name VARCHAR(150) NOT NULL,
    game_type ENUM('QUIZ','PRICE','CODES') NOT NULL,
    sort_order INT DEFAULT 1,
    is_enabled TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (template_id) REFERENCES templates(id) ON DELETE CASCADE
);

CREATE TABLE template_quiz_questions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    template_game_id INT NOT NULL,
    question_text TEXT NOT NULL,
    answer_a VARCHAR(255) NOT NULL,
    answer_b VARCHAR(255) NOT NULL,
    answer_c VARCHAR(255) NOT NULL,
    answer_d VARCHAR(255) NOT NULL,
    correct_answer ENUM('A','B','C','D') NOT NULL,
    question_time INT DEFAULT 15,
    points_correct INT DEFAULT 100,
    sort_order INT DEFAULT 1,
    FOREIGN KEY (template_game_id) REFERENCES template_games(id) ON DELETE CASCADE
);

CREATE TABLE template_code_entries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    template_game_id INT NOT NULL,
    code_value VARCHAR(50) NOT NULL,
    code_points INT DEFAULT 10,
    FOREIGN KEY (template_game_id) REFERENCES template_games(id) ON DELETE CASCADE
);

CREATE TABLE template_price_games (
    id INT AUTO_INCREMENT PRIMARY KEY,
    template_game_id INT NOT NULL,
    correct_price DECIMAL(12,2) DEFAULT 0,
    FOREIGN KEY (template_game_id) REFERENCES template_games(id) ON DELETE CASCADE
);

CREATE TABLE session_games (
    id INT AUTO_INCREMENT PRIMARY KEY,
    session_id INT NOT NULL,
    template_game_id INT NOT NULL,
    game_name VARCHAR(150) NOT NULL,
    game_type ENUM('QUIZ','PRICE','CODES') NOT NULL,
    sort_order INT DEFAULT 1,
    is_enabled TINYINT(1) DEFAULT 1,
    status ENUM('WAITING','ACTIVE','SOLUTION','FINISHED') DEFAULT 'WAITING',
    visible_to_players TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE,
    FOREIGN KEY (template_game_id) REFERENCES template_games(id)
);

CREATE TABLE quiz_questions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    session_game_id INT NOT NULL,
    question_text TEXT NOT NULL,
    answer_a VARCHAR(255) NOT NULL,
    answer_b VARCHAR(255) NOT NULL,
    answer_c VARCHAR(255) NOT NULL,
    answer_d VARCHAR(255) NOT NULL,
    correct_answer ENUM('A','B','C','D') NOT NULL,
    question_time INT DEFAULT 15,
    points_correct INT DEFAULT 100,
    sort_order INT DEFAULT 1,
    status ENUM('WAITING','ACTIVE','SOLUTION','FINISHED') DEFAULT 'WAITING',
    opened_at DATETIME NULL,
    FOREIGN KEY (session_game_id) REFERENCES session_games(id) ON DELETE CASCADE
);

CREATE TABLE code_entries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    session_game_id INT NOT NULL,
    code_value VARCHAR(50) NOT NULL,
    code_points INT DEFAULT 10,
    FOREIGN KEY (session_game_id) REFERENCES session_games(id) ON DELETE CASCADE
);

CREATE TABLE price_games (
    id INT AUTO_INCREMENT PRIMARY KEY,
    session_game_id INT NOT NULL,
    correct_price DECIMAL(12,2) DEFAULT 0,
    winner_player_id INT NULL,
    winner_revealed TINYINT(1) DEFAULT 0,
    FOREIGN KEY (session_game_id) REFERENCES session_games(id) ON DELETE CASCADE
);

CREATE TABLE quiz_answers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    player_id INT NOT NULL,
    question_id INT NOT NULL,
    answer ENUM('A','B','C','D') NOT NULL,
    is_correct TINYINT(1) DEFAULT 0,
    response_time_ms INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_player_question (player_id, question_id)
);

CREATE TABLE price_answers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    player_id INT NOT NULL,
    session_game_id INT NOT NULL,
    submitted_price DECIMAL(12,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_player_price (player_id, session_game_id)
);

CREATE TABLE code_usage (
    id INT AUTO_INCREMENT PRIMARY KEY,
    player_id INT NOT NULL,
    code_entry_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_player_code (player_id, code_entry_id)
);

CREATE TABLE scores (
    id INT AUTO_INCREMENT PRIMARY KEY,
    player_id INT NOT NULL,
    session_game_id INT NOT NULL,
    points INT DEFAULT 0,
    reason VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE presenter_state (
    id INT PRIMARY KEY,
    session_id INT NULL,
    current_game_id INT NULL,
    mode ENUM('WAITING','QUIZ','QUIZ_SOLUTION','PRICE','PRICE_RESULT','CODES','RANKING') DEFAULT 'WAITING',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
INSERT INTO presenter_state (id, session_id, current_game_id, mode) VALUES (1, NULL, NULL, 'WAITING');