-- MySQL şeması: madencil_ish
-- Bu dosyayı çalıştırmadan önce veritabanını manuel olarak oluşturun:
-- CREATE DATABASE madencil_ish CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- Sonra: USE madencil_ish;

SET NAMES utf8mb4;
SET time_zone = '+00:00';

-- Kullanıcılar (oyuncular)
CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Oyunlar
CREATE TABLE games (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    game_token CHAR(32) NOT NULL, -- tahmin edilmesi zor benzersiz token
    round_count TINYINT UNSIGNED NOT NULL,
    current_round TINYINT UNSIGNED NOT NULL DEFAULT 0,
    status ENUM('waiting','in_progress','round_finished','finished') NOT NULL DEFAULT 'waiting',
    selected_categories VARCHAR(255) NOT NULL, -- kategori id listesi, örn: "1,2,3"
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    started_at TIMESTAMP NULL DEFAULT NULL,
    finished_at TIMESTAMP NULL DEFAULT NULL,
    UNIQUE KEY uniq_game_token (game_token)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Oyuna katılan oyuncular
CREATE TABLE game_players (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    game_id INT UNSIGNED NOT NULL,
    user_id INT UNSIGNED NOT NULL,
    total_score INT NOT NULL DEFAULT 0,
    joined_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_game_user (game_id, user_id),
    CONSTRAINT fk_game_players_game
        FOREIGN KEY (game_id) REFERENCES games(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_game_players_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Turlar
CREATE TABLE rounds (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    game_id INT UNSIGNED NOT NULL,
    round_number TINYINT UNSIGNED NOT NULL,
    letter CHAR(1) NOT NULL,
    status ENUM('pending','active','completed') NOT NULL DEFAULT 'pending',
    started_at TIMESTAMP NULL DEFAULT NULL,
    ended_at TIMESTAMP NULL DEFAULT NULL,
    last_one_left_user_id INT UNSIGNED NULL DEFAULT NULL,
    last_one_left_at TIMESTAMP NULL DEFAULT NULL,
    UNIQUE KEY uniq_game_round (game_id, round_number),
    CONSTRAINT fk_rounds_game
        FOREIGN KEY (game_id) REFERENCES games(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_rounds_last_user
        FOREIGN KEY (last_one_left_user_id) REFERENCES users(id)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Kategoriler (sabit sözlük)
CREATE TABLE categories (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    sort_order TINYINT UNSIGNED NOT NULL DEFAULT 0,
    UNIQUE KEY uniq_category_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Oyun için seçilen kategoriler (çoktan çoğa)
CREATE TABLE game_categories (
    game_id INT UNSIGNED NOT NULL,
    category_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (game_id, category_id),
    CONSTRAINT fk_game_categories_game
        FOREIGN KEY (game_id) REFERENCES games(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_game_categories_category
        FOREIGN KEY (category_id) REFERENCES categories(id)
        ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Cevaplar
CREATE TABLE answers (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    game_id INT UNSIGNED NOT NULL,
    round_id INT UNSIGNED NOT NULL,
    user_id INT UNSIGNED NOT NULL,
    category_id INT UNSIGNED NOT NULL,
    answer_text VARCHAR(255) NULL,
    is_valid TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_answer (round_id, user_id, category_id),
    CONSTRAINT fk_answers_game
        FOREIGN KEY (game_id) REFERENCES games(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_answers_round
        FOREIGN KEY (round_id) REFERENCES rounds(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_answers_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_answers_category
        FOREIGN KEY (category_id) REFERENCES categories(id)
        ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Puanlar (tur bazlı)
CREATE TABLE scores (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    game_id INT UNSIGNED NOT NULL,
    round_id INT UNSIGNED NOT NULL,
    user_id INT UNSIGNED NOT NULL,
    category_id INT UNSIGNED NOT NULL,
    score TINYINT UNSIGNED NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_score (round_id, user_id, category_id),
    CONSTRAINT fk_scores_game
        FOREIGN KEY (game_id) REFERENCES games(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_scores_round
        FOREIGN KEY (round_id) REFERENCES rounds(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_scores_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_scores_category
        FOREIGN KEY (category_id) REFERENCES categories(id)
        ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Cevap oylamaları (doğru / yanlış)
CREATE TABLE answer_votes (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    answer_id INT UNSIGNED NOT NULL,
    voter_user_id INT UNSIGNED NOT NULL,
    is_correct TINYINT(1) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_vote (answer_id, voter_user_id),
    CONSTRAINT fk_answer_votes_answer
        FOREIGN KEY (answer_id) REFERENCES answers(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_answer_votes_user
        FOREIGN KEY (voter_user_id) REFERENCES users(id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Örnek kategoriler
INSERT INTO categories (name, is_active, sort_order) VALUES
('İsim', 1, 1),
('Şehir', 1, 2),
('Hayvan', 1, 3),
('Eşya', 1, 4),
('Bitki', 1, 5),
('Ülke', 1, 6);

