/* RAIET v1.22 — simple lobby foundation. */
CREATE TABLE IF NOT EXISTS lobbies (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    owner_user_id BIGINT UNSIGNED NOT NULL,
    game_key VARCHAR(32) NOT NULL,
    region VARCHAR(32) NOT NULL,
    max_players TINYINT UNSIGNED NOT NULL,
    note VARCHAR(120) NOT NULL DEFAULT '',
    status ENUM('open','closed') NOT NULL DEFAULT 'open',
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    PRIMARY KEY (id),
    KEY idx_lobbies_open_game_region (status, game_key, region, created_at),
    KEY idx_lobbies_owner_status (owner_user_id, status),
    CONSTRAINT fk_lobbies_owner FOREIGN KEY (owner_user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS lobby_languages (
    lobby_id BIGINT UNSIGNED NOT NULL,
    language_code VARCHAR(16) NOT NULL,
    PRIMARY KEY (lobby_id, language_code),
    KEY idx_lobby_languages_language (language_code, lobby_id),
    CONSTRAINT fk_lobby_languages_lobby FOREIGN KEY (lobby_id) REFERENCES lobbies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS lobby_members (
    lobby_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    role ENUM('owner','member') NOT NULL DEFAULT 'member',
    joined_at DATETIME NOT NULL,
    PRIMARY KEY (lobby_id, user_id),
    KEY idx_lobby_members_user (user_id, joined_at),
    CONSTRAINT fk_lobby_members_lobby FOREIGN KEY (lobby_id) REFERENCES lobbies(id) ON DELETE CASCADE,
    CONSTRAINT fk_lobby_members_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
