/* RAIET v1.37 — SA-MP AUTO server resolver foundation. */
ALTER TABLE lobbies
    ADD COLUMN game_mode VARCHAR(24) NULL AFTER region,
    ADD COLUMN server_selection_mode VARCHAR(16) NOT NULL DEFAULT 'custom' AFTER note,
    ADD COLUMN server_catalog_id BIGINT UNSIGNED NULL AFTER server_port,
    ADD COLUMN server_resolved_at DATETIME NULL AFTER server_catalog_id;

CREATE TABLE game_server_catalog (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    game_key VARCHAR(32) NOT NULL,
    mode_key VARCHAR(32) NOT NULL,
    server_name VARCHAR(120) NOT NULL,
    host VARCHAR(253) NOT NULL,
    port SMALLINT UNSIGNED NOT NULL,
    connection_method VARCHAR(40) NOT NULL DEFAULT 'standard_samp',
    is_verified TINYINT(1) NOT NULL DEFAULT 0,
    is_enabled TINYINT(1) NOT NULL DEFAULT 1,
    priority SMALLINT NOT NULL DEFAULT 0,
    last_query_ok TINYINT(1) NULL,
    last_players SMALLINT UNSIGNED NULL,
    last_max_players SMALLINT UNSIGNED NULL,
    last_passworded TINYINT(1) NULL,
    last_hostname VARCHAR(160) NULL,
    last_gamemode VARCHAR(100) NULL,
    last_language VARCHAR(80) NULL,
    last_ping_ms SMALLINT UNSIGNED NULL,
    last_checked_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_game_server (game_key, host, port),
    KEY idx_game_mode_enabled (game_key, mode_key, is_verified, is_enabled),
    KEY idx_server_last_checked (last_checked_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
