/*

  WUSI - Wurm Unlimited Skills Import

  This creates SQL statements from a Wurm Online skills.txt character skill dump,

  to be imported into a Wurm Unlimited server's database.

  If you have problems, you can post here:

  http://forum.wurmonline.com/index.php?/topic/133161-wusi-wurm-unlimited-skills-import/

  There's also more thorough instructions, but here's a short rundown.

  Usage:

   - Paste a dump on pastebin or any site that returns the raw contents,

     you could just link to the text file instead for example.

   - Set _localServerID to your local server id,

     that's either the ID in the Local server tab of your server config GUI,

     or you can get it from wurmlogin.db->SERVERS.server,

     that's the server column, in the SERVERS table, in wurmlogin.db.

   - Set _playerSkillIDs to wurmlogin.db->IDS.playerSkillIds,

     that's the playerSkillIds column, in the IDS table, in wurmlogin.db.

   - Set the _ownerID to the player character's unique ID/wurmID.

​

  The errors about 'Skills' and 'Characteristics' can be ignored, they

  are dummy entries to build the skill tree, and can be left skipped.

  The malformed line about Religion: 0.0 0.0 0 can be ignored, it is a

  dummy entry with no values, the line will be simply be skipped. The

  other entry, that is the proper Religion skill, will be parsed and used.

  The character you want to import skills for MUST already exist.

  Now either copy&paste the output into a file, or into the SQLite3

  shell directly. You must be in the wurmplayers.db. But you will also

  need to update the playerSkillIds value in the wurmlogin.db. The value

  you should use will be at the bottom of the output, as SQL comment.

        The MIT License (MIT)

        Copyright (c) 2015 Daniel Monzert <d.mon at gmx.net>.

        Permission is hereby granted, free of charge, to any person obtaining a copy

        of this software and associated documentation files (the "Software"), to deal

        in the Software without restriction, including without limitation the rights

        to use, copy, modify, merge, publish, distribute, sublicense, and/or sell

        copies of the Software, and to permit persons to whom the Software is

        furnished to do so, subject to the following conditions:

        The above copyright notice and this permission notice shall be included in

        all copies or substantial portions of the Software.

        THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR

        IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,

        FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE

        AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER

        LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,

        OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN

        THE SOFTWARE.

* */

​

using System;

using System.Collections.Generic;

using System.Net;

using System.Text;

​

public class Program

{

    /* 

     * CONFIGURE THESE VARIABLES FOR YOURSELF

     *

     * * */

    // paste your skill dump at pastebin, and use the raw version url of it here.

    static string _pastebin = "http://pastebin.com/raw.php?i=Mpj6Etjz";

    // your local server id that you can find in your config GUI.

    static long _localServerID = 24048;

    // your current playerSkillIDcount value, SELECT playerSkillIDs FROM ids WHERE server = LOCALSERVERID

    static long _playerSkillIDs = 10200;

    // the player character that is the owner of the skills.

    static long _ownerID = 100703006720;

    /*

     * PROBABLY DON'T NEED TO CHANGE ANYTHING BELOW

     * UNLESS YOU KNOW WHAT YOU'RE DOING

     *

     * * */

    public static void Main()

    {

        // StringBuilder to create our SQL file.

        StringBuilder sb = new StringBuilder();

        // Gets the WU skill mapping according to SkillList.class in server.jar

        Dictionary<string, int> skills = GetSkills();

        // Use the WebClient utility to download the page content (plain text).

        WebClient web = new WebClient();

        // Downloads the text as a whole.

        string contents = web.DownloadString(_pastebin);

        // Removes carriage returns.

        contents = contents.Replace("\r", "");

        // Splits lines at line feeds.

        string[] lines = contents.Split('\n');

        // Iterates through every single line.

        for (int i = 0; i < lines.Length; i++) {

            // Gets current line while trimming leading and trailing whitespaces.

            string line = lines[i].Trim();

            // Just some minor checks for errors in the parsed text.

            if (string.IsNullOrEmpty(line))

                continue;

            // These two lines are part of the skill dump and need not be parsed.

            // Instead of starting to iterate at i = 2 or higher, just in case somebody omits those.

            if (line.StartsWith("Skills dumped at")

                    || line.StartsWith("--"))

                continue;

            // Split line at the colon character.

            string[] skillValue = line.Split(':');

            // This array should have exactly two fields.

            if (skillValue.Length != 2) {

                Console.WriteLine("--Line #{0} seems to be malformed: '{1}'. It will be skipped.", i + 1, line);

                continue;

            }

            // Grab skill name because.

            string skillName = skillValue[0];

            if (!skills.ContainsKey(skillName)) {

                Console.WriteLine("--Error on line #{0}, the skill '{1}' was not found in my definitions. It will be skipped.", i + 1, skillName);

                continue;

            }

            // Split current/max/affinities skill values.

            string[] skillValues = skillValue[1].Trim().Split(' ');

            // This array should be exactly 3 in length.

            if (skillValues.Length != 3) {

                Console.WriteLine("--Line #{0} seems to be malformed: '{1}'. Did not get min, max, and affinity values from skill line?",

                                  i + 1, line);

                continue;

            }

            // Try to get the maximum value the skill's ever been at.

            double maxValue;

            if (!double.TryParse(skillValues[1], out maxValue)) {

                Console.WriteLine("--Line #{0} seems to be malformed: '{1}'. I cannot parse the max skill value. It will be skipped.",

                                  i + 1, line);

                continue;

            }

            // Sanity check for skill values, need to be within 1.0 and 100.0.

            if (maxValue < 1d || maxValue > 100d) {

                Console.WriteLine("--Line #{0} seems to be malformed: '{1}'. Invalid skill value, needs to be in range from 1.0 to 100.0. It will be skipped.",

                                  i + 1, line);

                continue;

            }

            // Try to get the number of affinities.

            int affinities;

            if (!int.TryParse(skillValues[2], out affinities)) {

                Console.WriteLine("--Line #{0} seems to be malformed: '{1}'. I cannot parse the number of affinities. It will be skipped.",

                                  i + 1, line);

                continue;

            }

            // Sanity check the number of affinities... just in case.

            if (affinities < 0 || affinities > 5) {

                Console.WriteLine("--Line #{0} seems to be malformed: '{1}'. Can't have less than 0 or more than 5 affinities. It will be skipped.",

                                  i + 1, line);

                continue;

            }

            // If this skill had one or more affinities, create SQL statement.

            if (affinities > 0) {

                // Set or re-adjust number of affinities.

                sb.AppendFormat("INSERT OR REPLACE INTO affinities (wurmid, skill, number) " +

                                "VALUES ({0}, {1}, {2});{3}",

                                _ownerID, skills[skillName], affinities, Environment.NewLine);

            }

            // Build SQL statement.

            if (skillName.Equals("Faith") || skillName.Equals("Favor") || skillName.Equals("Alignment")) {

                // Although shown like skills, they're set on the player instead of saved as a skill.

                sb.AppendFormat("UPDATE players SET {0} = {1} WHERE wurmid = {2};{3}",

                                skillName, maxValue, _ownerID, Environment.NewLine);

            }

            else {

                // There may or may not be an entry for a skill, so we either need to insert a new one,

                // or technically "delete the old one and make a new insert" for the skill to be imported.

                sb.AppendFormat("INSERT OR REPLACE INTO skills (id, owner, number, value, minvalue, lastused) " +

                            "VALUES (COALESCE((SELECT id FROM skills WHERE owner = {0} AND number = {1}), {2}), " +

                            "{0}, {1}, {3}, {3}, {4});{5}",

                                _ownerID /*{0}*/, skills[skillName] /*{1}*/, GetNextPlayerSkillID() /*{2}*/,

                                maxValue /*{3}*/, GetUnixDate() /*{4}*/, Environment.NewLine /*{5}*/);

            }

        }

        Console.WriteLine(sb.ToString());

        Console.WriteLine("--Your new playerSkillIDs value is: {0}", _playerSkillIDs);

    }

    // Mimics WU's behaviour in getting the next ID used as primary key in the SKILLS table of wurmplayers.db

    public static long GetNextPlayerSkillID() {

        _playerSkillIDs += 1;

        return (long)(_playerSkillIDs << 24) + (long)(_localServerID << 8) + 10L;

    }

    // Returns the current unix time stamp.

    public static long GetUnixDate() {

        return (long)DateTime.Now.Subtract(new DateTime(1970, 01, 01)).TotalSeconds;

    }

    // Initializes all the skills according to SkillList.class in server.jar

    public static Dictionary<string, int> GetSkills() {

        // Creates a new key-value collection.

        // Key: Skill name (exact spelling in skill dump text file)

        // Value: Numerical ID

        Dictionary<string, int> skills = new Dictionary<string, int>();

        // Skill mapping, do not change.

        skills.Add("Body", 1);

        skills.Add("Mind", 2);

        skills.Add("Soul", 3);

        skills.Add("Mind logic", 100);

        skills.Add("Mind speed", 101);

        skills.Add("Body strength", 102);

        skills.Add("Body stamina", 103);

        skills.Add("Body control", 104);

        skills.Add("Soul strength", 105);

        skills.Add("Soul depth", 106);

        skills.Add("Swords", 1000);

        skills.Add("Knives", 1001);

        skills.Add("Shields", 1002);

        skills.Add("Axes", 1003);

        skills.Add("Mauls", 1004);

        skills.Add("Carpentry", 1005);

        skills.Add("Woodcutting", 1007);

        skills.Add("Mining", 1008);

        skills.Add("Digging", 1009);

        skills.Add("Firemaking", 1010);

        skills.Add("Pottery", 1011);

        skills.Add("Tailoring", 1012);

        skills.Add("Masonry", 1013);

        skills.Add("Ropemaking", 1014);

        skills.Add("Smithing", 1015);

        skills.Add("Weapon smithing", 1016);

        skills.Add("Armour smithing", 1017);

        skills.Add("Cooking", 1018);

        skills.Add("Nature", 1019);

        skills.Add("Miscellaneous items", 1020);

        skills.Add("Alchemy", 1021);

        skills.Add("Toys", 1022);

        skills.Add("Fighting", 1023);

        skills.Add("Healing", 1024);

        skills.Add("Clubs", 1025);

        skills.Add("Religion", 1026);

        skills.Add("Hammers", 1027);

        skills.Add("Thievery",1028);

        skills.Add("War machines", 1029);

        skills.Add("Archery", 1030);

        skills.Add("Bowyery", 1031);

        skills.Add("Fletching", 1032);

        skills.Add("Polearms", 1033);

        skills.Add("Small Axe", 10001);

        skills.Add("Shovel", 10002);

        skills.Add("Hatchet", 10003);

        skills.Add("Rake", 10004);

        skills.Add("Longsword", 10005);

        skills.Add("Medium metal shield", 10006);

        skills.Add("Carving knife", 10007);

        skills.Add("Saw", 10008);

        skills.Add("Pickaxe", 10009);

        skills.Add("Blades smithing", 10010);

        skills.Add("Weapon heads smithing", 10011);

        skills.Add("Chain armour smithing", 10012);

        skills.Add("Plate armour smithing", 10013);

        skills.Add("Shield smithing", 10014);

        skills.Add("Blacksmithing", 10015);

        skills.Add("Cloth tailoring", 10016);

        skills.Add("Leatherworking", 10017);

        skills.Add("Tracking", 10018);

        skills.Add("Small wooden shield", 10019);

        skills.Add("Medium wooden shield", 10020);

        skills.Add("Large wooden shield", 10021);

        skills.Add("Small metal shield", 10022);

        skills.Add("Large metal shield", 10023);

        skills.Add("Large axe", 10024);

        skills.Add("Huge axe", 10025);

        skills.Add("Hammer", 10026);

        skills.Add("Shortsword", 10027);

        skills.Add("Two handed sword", 10028);

        skills.Add("Butchering knife", 10029);

        skills.Add("Stone chisel", 10030);

        skills.Add("Paving", 10031);

        skills.Add("Prospecting", 10032);

        skills.Add("Fishing", 10033);

        skills.Add("Locksmithing", 10034);

        skills.Add("Repairing", 10035);

        skills.Add("Coal-making", 10036);

        skills.Add("Dairy food making", 10037);

        skills.Add("Hot food cooking", 10038);

        skills.Add("Baking", 10039);

        skills.Add("Milling", 10040);

        skills.Add("Metallurgy", 10041);

        skills.Add("Natural substances", 10042);

        skills.Add("Jewelry smithing", 10043);

        skills.Add("Fine carpentry", 10044);

        skills.Add("Gardening", 10045);

        skills.Add("Sickle", 10046);

        skills.Add("Scythe", 10047);

        skills.Add("Forestry", 10048);

        skills.Add("Farming", 10049);

        skills.Add("Yoyo", 10050);

        skills.Add("Toy making", 10051);

        skills.Add("Weaponless fighting", 10052);

        skills.Add("Aggressive fighting", 10053);

        skills.Add("Defensive fighting", 10054);

        skills.Add("Normal fighting", 10055);

        skills.Add("First aid", 10056);

        skills.Add("Taunting", 10057);

        skills.Add("Shield bashing", 10058);

        skills.Add("Butchering", 10059);

        skills.Add("Milking", 10060);

        skills.Add("Large maul", 10061);

        skills.Add("Medium maul", 10062);

        skills.Add("Small maul", 10063);

        skills.Add("Huge club", 10064);

        skills.Add("Preaching", 10065);

        skills.Add("Prayer", 10066);

        skills.Add("Channeling", 10067);

        skills.Add("Exorcism", 10068);

        skills.Add("Artifacts", 10069);

        skills.Add("Warhammer", 10070);

        skills.Add("Foraging", 10071);

        skills.Add("Botanizing", 10072);

        skills.Add("Climbing", 10073);

        skills.Add("Stone cutting", 10074);

        skills.Add("Stealing", 10075);

        skills.Add("Lock picking", 10076);

        skills.Add("Catapults", 10077);

        skills.Add("Animal taming", 10078);

        skills.Add("Short bow", 10079);

        skills.Add("Medium bow", 10080);

        skills.Add("Long bow", 10081);

        skills.Add("Ship building", 10082);

        skills.Add("Beverages", 10083);

        skills.Add("Traps", 10084);

        skills.Add("Animal husbandry", 10085);

        skills.Add("Meditating", 10086);

        skills.Add("Puppeteering", 10087);

        skills.Add("Long spear", 10088);

        skills.Add("Halberd", 10089);

        skills.Add("Staff", 10090);

        skills.Add("Papyrusmaking", 10091);

        skills.Add("Thatching", 10092);

        skills.Add("Ballistae", 10093);

        skills.Add("Trebuchets", 10094);

        //skills.Add("PEWPEWDIE", 10095); // What is this skill? It's defined as PEWPEWDIE in code.

        skills.Add("Turrets", 10095); // ^ Is it Turrets?

        //skills.Add("Skills", 0x7FFFFFFF);

        //skills.Add("Characteristics", 0x7FFFFFFE);

        skills.Add("Faith", 0x7FFFFFFD);

        skills.Add("Favor", 0x7FFFFFFC);

        //skills.Add("Religion", 0x7FFFFFFB); // Already have Religion as key, this is presumably just a dummy anyway.

        skills.Add("Alignment", 0x7FFFFFFA);

        return skills;

    }

}