SELECT
    c.account,
    a.username,
    c.name,
    CASE 
        WHEN c.race = 1 THEN 'Human'
        WHEN c.race = 2 THEN 'Orc'
        WHEN c.race = 3 THEN 'Dwarf'
        WHEN c.race = 4 THEN 'Night Elf'
        WHEN c.race = 5 THEN 'Undead'
        WHEN c.race = 6 THEN 'Tauren'
        WHEN c.race = 7 THEN 'Gnome'
        WHEN c.race = 8 THEN 'Troll'
        WHEN c.race = 9 THEN 'Goblin'
        WHEN c.race = 10 THEN 'Blood Elf'
        WHEN c.race = 11 THEN 'Draenei'
        ELSE 'Unknown'
    END AS race_name,
    CASE
        WHEN c.class = 1 THEN 'Warrior'
        WHEN c.class = 2 THEN 'Paladin'
        WHEN c.class = 3 THEN 'Hunter'
        WHEN c.class = 4 THEN 'Rogue'
        WHEN c.class = 5 THEN 'Priest'
        WHEN c.class = 6 THEN 'Death Knight'
        WHEN c.class = 7 THEN 'Shaman'
        WHEN c.class = 8 THEN 'Mage'
        WHEN c.class = 9 THEN 'Warlock'
        WHEN c.class = 10 THEN 'Monk'
        WHEN c.class = 11 THEN 'Druid'
        WHEN c.class = 12 THEN 'Demon Hunter'
        ELSE 'Unknown'
    END AS class_name,
    CASE
        WHEN c.gender = 0 THEN 'Male'
        WHEN c.gender = 1 THEN 'Female'
        ELSE 'Unknown'
    END AS gender_name,
    c.level,
    c.xp,
    CONCAT(FLOOR(c.money / 10000), 'g ', 
        FLOOR((c.money % 10000) / 100), 's ', 
        c.money % 100, 'c') AS money,
    CASE
        WHEN c.online = 1 THEN DATE_FORMAT(NOW(), '%W %D %M %Y at %l:%i %p')
        ELSE DATE_FORMAT(FROM_UNIXTIME(c.logout_time), '%W %D %M %Y at %l:%i %p')
    END AS last_seen,
    CASE 
        WHEN c.drunk = 0 THEN 'Sober'
        WHEN c.drunk BETWEEN 1 AND 49 THEN 'Tipsy'
        WHEN c.drunk BETWEEN 50 AND 89 THEN 'Drunk'
        WHEN c.drunk BETWEEN 90 AND 100 THEN 'Smashed'
        ELSE 'Unknown'
    END AS sobriety,
    CASE cs.skill
        WHEN 171 THEN 'Alchemy'
        WHEN 164 THEN 'Blacksmithing'
        WHEN 333 THEN 'Enchanting'
        WHEN 202 THEN 'Engineering'
        WHEN 182 THEN 'Herbalism'
        WHEN 773 THEN 'Inscription'
        WHEN 755 THEN 'Jewelcrafting'
        WHEN 165 THEN 'Leatherworking'
        WHEN 186 THEN 'Mining'
        WHEN 393 THEN 'Skinning'
        WHEN 197 THEN 'Tailoring'
        WHEN 185 THEN 'Cooking'
        WHEN 129 THEN 'First Aid'
        WHEN 356 THEN 'Fishing'
        ELSE 'Unknown'
    END AS profession_name,
    CONCAT(cs.value, '/', cs.max) AS skill_level,
    CASE 
        WHEN cs.max BETWEEN 1 AND 75 THEN 'Apprentice'
        WHEN cs.max BETWEEN 76 AND 150 THEN 'Journeyman'
        WHEN cs.max BETWEEN 151 AND 225 THEN 'Expert'
        WHEN cs.max BETWEEN 226 AND 300 THEN 'Artisan'
        WHEN cs.max BETWEEN 301 AND 375 THEN 'Master'
        WHEN cs.max BETWEEN 376 AND 450 THEN 'Grand Master'
        ELSE 'Unknown'
    END AS skill_category
FROM
    characters AS c
LEFT JOIN
    auth.account AS a ON c.account = a.id
LEFT JOIN 
    character_skills AS cs ON c.guid = cs.guid
WHERE
    username != 'TRINITY'
    AND (cs.skill IS NULL OR cs.skill IN (171, 164, 333, 202, 182, 773, 755, 165, 186, 393, 197, 185, 129, 356))
ORDER BY
    c.account ASC,
    c.name ASC,
    CASE 
        WHEN cs.skill IN (171, 164, 333, 202, 182, 773, 755, 165, 186, 393, 197) THEN 1
        ELSE 2
    END ASC,
    profession_name ASC;