-- Multiplayer room support for the browser Tetris game.
CREATE TABLE IF NOT EXISTS tetris_rooms (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    room_code VARCHAR(8) NOT NULL,
    status ENUM('waiting', 'active', 'closed') NOT NULL DEFAULT 'waiting',
    host_player_id BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    expires_at DATETIME NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uniq_tetris_rooms_code (room_code),
    KEY idx_tetris_rooms_status (status),
    KEY idx_tetris_rooms_updated (updated_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS tetris_room_players (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    room_id BIGINT UNSIGNED NOT NULL,
    player_token CHAR(48) NOT NULL,
    player_slot TINYINT UNSIGNED NOT NULL,
    display_name VARCHAR(48) NOT NULL DEFAULT 'Player',
    connection_status ENUM('connected', 'disconnected') NOT NULL DEFAULT 'connected',
    game_status ENUM('ready', 'playing', 'paused', 'game_over') NOT NULL DEFAULT 'ready',
    score INT UNSIGNED NOT NULL DEFAULT 0,
    lines_cleared INT UNSIGNED NOT NULL DEFAULT 0,
    level INT UNSIGNED NOT NULL DEFAULT 1,
    board_state_json JSON NULL,
    piece_state_json JSON NULL,
    meta_json JSON NULL,
    joined_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_seen_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uniq_tetris_room_players_token (player_token),
    UNIQUE KEY uniq_tetris_room_players_slot (room_id, player_slot),
    KEY idx_tetris_room_players_room (room_id),
    KEY idx_tetris_room_players_seen (last_seen_at),
    CONSTRAINT fk_tetris_room_players_room
        FOREIGN KEY (room_id) REFERENCES tetris_rooms(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;