-- ===============================
-- DATABASE : ipsc
-- ===============================
CREATE DATABASE IF NOT EXISTS ipsc
CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci;

USE ipsc;

-- ===============================
-- TABLE : event
-- เก็บข้อมูลการแข่งขัน (มีแค่ 1 แถว)
-- ===============================
CREATE TABLE IF NOT EXISTS event (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nday INT NOT NULL,
    month_th VARCHAR(50) NOT NULL,
    year INT NOT NULL,
    player_count INT NOT NULL DEFAULT 0,
    target_count INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO event (nday, month_th, year, player_count, target_count)
VALUES (1, 'มกราคม', 2568, 0, 0);

-- ===============================
-- TABLE : players
-- ===============================
CREATE TABLE IF NOT EXISTS players (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(10) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    image VARCHAR(100) DEFAULT 'user.png',
    time INT DEFAULT NULL COMMENT 'เวลาแข่งขัน (วินาที)',

    status ENUM('waiting','ready','running','finished','dq')
        DEFAULT 'waiting',

    start_time DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ===============================
-- TABLE : target_log
-- เก็บ log การล้มเป้า
-- ===============================
CREATE TABLE IF NOT EXISTS target_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    player_id INT NOT NULL,
    target_id VARCHAR(20) NOT NULL,
    hit_time DATETIME NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_player (player_id),
    UNIQUE KEY uniq_target (player_id, target_id),

    CONSTRAINT fk_target_player
        FOREIGN KEY (player_id)
        REFERENCES players(id)
        ON DELETE CASCADE
);

-- ===============================
-- INDEX เพิ่มประสิทธิภาพ
-- ===============================
CREATE INDEX idx_players_status ON players(status);
CREATE INDEX idx_players_time ON players(time);

-- ===============================
-- (กรณีฐานข้อมูลเดิม) แก้ ENUM status
-- ===============================
ALTER TABLE players
MODIFY status ENUM('waiting','ready','running','finished','dq')
DEFAULT 'waiting';
