CREATE TABLE IF NOT EXISTS user_profiles (
    user_id BIGINT UNSIGNED NOT NULL,
    display_name VARCHAR(32) NOT NULL,
    bio VARCHAR(280) NOT NULL DEFAULT '',
    looking_for ENUM('squad','duo','friends','competitive','casual') NOT NULL DEFAULT 'squad',
    profile_visibility ENUM('public','members') NOT NULL DEFAULT 'public',
    reputation INT NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    PRIMARY KEY (user_id),
    CONSTRAINT fk_user_profiles_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS user_games (
    user_id BIGINT UNSIGNED NOT NULL,
    game_key VARCHAR(32) NOT NULL,
    skill_level ENUM('new','casual','experienced','competitive') NOT NULL DEFAULT 'casual',
    is_primary TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL,
    PRIMARY KEY (user_id, game_key),
    KEY idx_user_games_game (game_key),
    KEY idx_user_games_primary (user_id, is_primary),
    CONSTRAINT fk_user_games_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;
