SWDB/install_user.py

#!/usr/bin/python3

import urllib3
import sys
import os
import requests
import urllib.request
import sqlite3
import wget
import json

FILE = "/home/ivalentin/.SWEX/IVV0-8114048.json"

def readFile(file):
    with open(file) as json_file:
        data = json.load(json_file)
    return data

"""
Opens the database file and deletes from the user tables

:param name: The path to the sqlite database.
:returns: Connection to the database.
:raises IntegrityError: The queryes couldn't bre executed.
:raises IOError: The sqlite file couldn't be created.
"""
def openDatabase(name):
    global OPT_CREATE_DB
    print('Configuring database...')
    try:
        db = sqlite3.connect(name)
        cursor = db.cursor()
        cursor.execute('''DELETE FROM scenario''')
        cursor.execute('''DELETE FROM defense''')
        cursor.execute('''DELETE FROM unit''')
        cursor.execute('''DELETE FROM unit_skill''')
        cursor.execute('''DELETE FROM rune''')
        cursor.execute('''DELETE FROM player''')
        cursor.execute('''DELETE FROM building''')
        cursor.execute('''DELETE FROM decoration''')
        cursor.execute('''DELETE FROM inventory''')
        cursor.execute('''DELETE FROM summon_special''')
        cursor.execute('''DELETE FROM guild''')
        cursor.execute('''DELETE FROM guild_member''')
        cursor.execute('''DELETE FROM rune_craft''')
        db.commit()
        cursor.close()
        return db
    except sqlite3.IntegrityError as e:
        print("Error executing statement: " + str(e))
        raise
    except IOError as e:
        print("I/O Error creating database " + name + ": " + str(e))
        raise


"""
Inserts a row into the database.

:param db: Connection to the database.
:param table: Name of the table to insert into.
:param values: List of values to insert.
:raises IntegrityError: The insert query was unsuccesfull.
"""
def insert(db, table, values):
    cursor = db.cursor()
    placeholders = ''
    for x in range(0, len(values)):
        placeholders = placeholders + '?, '
    placeholders = placeholders[:len(placeholders) - 2]
    query = 'INSERT INTO ' + table + ' VALUES (' + placeholders + ')'
    try:
        cursor.execute(query, values)
    except sqlite3.IntegrityError as e:
        print('Error Inserting into ' + table + ' with values (' + str(values) + '): ' + str(e))
        raise
    cursor.close;

"""
Parses player (table player).

:param db: Sqlite database connection.
:param data: Data in SWEX json file.
"""
def parsePlayer(db, data):
    print("Parsing player...")
    uid = data["wizard_info"]["wizard_id"]
    name = data["wizard_info"]["wizard_name"]
    mana = data["wizard_info"]["wizard_mana"]
    crystal = data["wizard_info"]["wizard_crystal"]
    country = data["wizard_info"]["wizard_last_country"]
    lang = data["wizard_info"]["wizard_last_lang"]
    level = data["wizard_info"]["wizard_level"]
    experience = data["wizard_info"]["experience"]
    energy = data["wizard_info"]["wizard_energy"]
    energy_max = data["wizard_info"]["energy_max"]
    energy_per_min = data["wizard_info"]["energy_per_min"]
    arena_energy = data["wizard_info"]["arena_energy"]
    arena_energy_max = data["wizard_info"]["arena_energy_max"]
    rep = data["wizard_info"]["rep_unit_id"]
    social_point = data["wizard_info"]["social_point_current"]
    honor_point = data["wizard_info"]["honor_point"]
    guild_point = data["wizard_info"]["guild_point"]
    darkportal_energy = data["wizard_info"]["darkportal_energy"]
    darkportal_energy_max = data["wizard_info"]["darkportal_energy_max"]
    dimension_energy = data["dimension_hole_info"]["energy"]
    dimension_energy_max = data["dimension_hole_info"]["energy_max"]
    costume_point = data["wizard_info"]["costume_point"]
    costume_point_max = data["wizard_info"]["costume_point_max"]
    honor_medal = data["wizard_info"]["honor_medal"]
    honor_mark = data["wizard_info"]["honor_mark"]
    event_coin = data["wizard_info"]["event_coin"]
    storage_slots = data["unit_depository_slots"]["number"]
    island_upgrade = 0
    for island in data["island_info"]:
        if island["id"] <= 7 and island["open"] == 1:
            island_upgrade = island["id"]
    insert(db, "player", (uid, name, mana, crystal, country, lang, level, experience, energy, energy_max, energy_per_min, arena_energy, arena_energy_max, rep, social_point, honor_point, guild_point, darkportal_energy, darkportal_energy_max, dimension_energy, dimension_energy_max, costume_point, costume_point_max, honor_medal, honor_medal, event_coin, storage_slots, island_upgrade))
    db.commit()

"""
Parses scenarios (table scenario).

:param db: Sqlite database connection.
:param data: Data in SWEX json file.
"""
def parseScenarios(db, data):
    print("Parsing scenarios...")
    uid = data["wizard_info"]["wizard_id"]
    for sce in data["scenario_list"]:
        region = sce["region_id"]
        difficulty = sce["difficulty"]
        cleared = sce["cleared"]
        max_cleared = 0
        for stage in sce["stage_list"]:
            if stage["cleared"] == 1:
                max_cleared = stage["stage_no"]
        insert(db, "scenario", (uid, region, difficulty, cleared, max_cleared))
    db.commit()

"""
Parses arena defense units (table defense).

:param db: Sqlite database connection.
:param data: Data in SWEX json file.
"""
def parseDefense(db, data):
    print("Parsing arena defense...")
    uid = data["wizard_info"]["wizard_id"]
    for defense in data["defense_unit_list"]:
        unit = defense["unit_id"]
        position = defense["pos_id"]
        insert(db, "defense", (uid, unit, position))
    db.commit()

"""
Parses buildings (table building).

:param db: Sqlite database connection.
:param data: Data in SWEX json file.
"""
def parseBuildings(db, data):
    print("Parsing buildings...")
    uid = data["wizard_info"]["wizard_id"]
    for bui in data["building_list"]:
        building = bui["wizard_id"]
        gain = bui["gain_per_hour"]
        insert(db, "building", (uid, building, gain))
    db.commit()

"""
Parses decoration buildings (table dcoration).

:param db: Sqlite database connection.
:param data: Data in SWEX json file.
"""
def parseDecorations(db, data):
    print("Parsing decorations...")
    uid = data["wizard_info"]["wizard_id"]
    for bui in data["deco_list"]:
        building = bui["master_id"]
        level = bui["level"]
        insert(db, "decoration", (uid, building, level))
    db.commit()

"""
Parses unit data (tables unit, unit_skill).

:param db: Sqlite database connection.
:param data: Data in SWEX json file.
"""
def parseUnits(db, data):
    print("Parsing monsters...")
    uid = data["wizard_info"]["wizard_id"]
    lock_list = data["unit_lock_list"]
    for mon in data["unit_list"]:
        id = mon["unit_id"]
        building = mon["building_id"]
        monster = mon["unit_master_id"]
        level = mon["unit_level"]
        stars = mon["class"]
        hp = mon["con"] * 15 # Always x15
        attack = mon["atk"]
        defense = mon["def"]
        speed = mon["spd"]
        crit_rate = mon["critical_rate"]
        crit_damage = mon["critical_damage"]
        resistance = mon["resist"]
        accuracy = mon["accuracy"]
        experience = mon["experience"]
        exp_gained = mon["exp_gained"]
        exp_gain_rate = mon["exp_gain_rate"]
        costume = mon["costume_master_id"]
        attribute = mon["attribute"]
        source = mon["source"]
        create_time = mon["create_time"]
        homunculus = mon["homunculus"]
        homunculus_name = mon["homunculus_name"]
        lock = 0
        if id in lock_list:
            lock = 1
        insert(db, "unit", (uid, id, building, monster, level, stars, hp, attack, defense, speed, crit_rate, crit_damage, resistance, accuracy, experience, exp_gained, exp_gain_rate, costume, attribute, source, create_time, homunculus, homunculus_name, lock))
        for skill in mon["skills"]:
            skill_id = skill[0]
            skill_level = skill[1]
            insert(db, "unit_skill", (uid, id, skill_id, skill_level))
    db.commit()

"""
Parses inventory data (table inventory).

:param db: Sqlite database connection.
:param data: Data in SWEX json file.
"""
def parseInventory(db, data):
    print("Parsing inventory...")
    uid = data["wizard_info"]["wizard_id"]
    for item in data["inventory_info"]:
        id = item["wizard_id"]
        type = item["item_master_type"]
        # TODO: Master id?
        amount = item["item_quantity"]
        insert(db, "inventory", (uid, id, type, amount))

"""
Parses summon stone list (table summon_special).

:param db: Sqlite database connection.
:param data: Data in SWEX json file.
"""
def parseSummonSpecial(db, data):
    print("Parsing Summon stone monster list...")
    uid = data["wizard_info"]["wizard_id"]
    for unit in data["summon_special_info"]["this"]:
        insert(db, "summon_special", (0, unit))
    for unit in data["summon_special_info"]["next"]:
        insert(db, "summon_special", (1, unit))
    for unit in data["summon_special_info"]["third"]:
        insert(db, "summon_special", (2, unit))
    for unit in data["summon_special_info"]["fourth"]:
        insert(db, "summon_special", (3, unit))

"""
Parses rune data (table rune).

:param db: Sqlite database connection.
:param data: Data in SWEX json file.
"""
def parseRunes(db, data):
    print("Parsing runes...")
    uid = data["wizard_info"]["wizard_id"]
    # Unequipped runes
    for rune in data["runes"]:
        id = rune["rune_id"]
        assigned_to = rune["occupied_id"]
        if assigned_to == 0:
            assigned_to = None
        runeset = rune["set_id"]
        slot = rune["slot_no"]
        stars = rune["class"]
        ancient = 0
        level = rune["upgrade_curr"]
        original_quality = rune["extra"]
        value = rune["sell_value"]
        if stars > 10:
            ancient = 1
            stars = stars - 10
            original_quality = original_quality - 10
        if level >= 12:
            quality = 4
        elif level >= 9:
            quality = 3
        elif level >= 6:
            quality = 2
        elif level >= 3:
            quality = 1
        else:
            quality = 0
        if original_quality > quality:
            quality = original_quality
        # TODO: Calculate
        #efficiency = rune["efficiency"]
        #max_efficiency = rune["max_efficiency"]
        efficiency = 0
        max_efficiency = 0
        main_stat = rune["pri_eff"][0]
        main_stat_value = rune["pri_eff"][1]
        if rune["prefix_eff"][0] > 0:
            innate_stat = rune["prefix_eff"][0]
            innate_stat_value = rune["prefix_eff"][1]
        else:
            innate_stat = None
            innate_stat_value = 0
        if len(rune["sec_eff"]) > 0:
            substat_1 = rune["sec_eff"][0][0]
            substat_1_value = rune["sec_eff"][0][1]
            substat_1_enchant = rune["sec_eff"][0][2]
            substat_1_grind = rune["sec_eff"][0][3]
        else:
            substat_1 = None
            substat_1_value = 0
            substat_1_enchant = 0
            substat_1_grind = 0
        if len(rune["sec_eff"]) > 1:
            substat_2 = rune["sec_eff"][1][0]
            substat_2_value = rune["sec_eff"][1][1]
            substat_2_enchant = rune["sec_eff"][1][2]
            substat_2_grind = rune["sec_eff"][1][3]
        else:
            substat_2 = None
            substat_2_value = 0
            substat_2_enchant = 0
            substat_2_grind = 0
        if len(rune["sec_eff"]) > 2:
            substat_3 = rune["sec_eff"][2][0]
            substat_3_value = rune["sec_eff"][2][1]
            substat_3_enchant = rune["sec_eff"][2][2]
            substat_3_grind = rune["sec_eff"][2][3]
        else:
            substat_3 = None
            substat_3_value = 0
            substat_3_enchant = 0
            substat_3_grind = 0
        if len(rune["sec_eff"]) > 3:
            substat_4 = rune["sec_eff"][0][0]
            substat_4_value = rune["sec_eff"][3][1]
            substat_4_enchant = rune["sec_eff"][3][2]
            substat_4_grind = rune["sec_eff"][3][3]
        else:
            substat_4 = None
            substat_4_value = 0
            substat_4_enchant = 0
            substat_4_grind = 0
        insert(db, "rune", (uid, id, assigned_to, runeset, slot, stars, ancient, level, quality, original_quality, value, efficiency, max_efficiency, main_stat, main_stat_value, innate_stat, innate_stat_value, substat_1, substat_1_value, substat_1_enchant, substat_1_grind, substat_2, substat_2_value, substat_2_enchant, substat_2_grind, substat_3, substat_3_value, substat_3_enchant, substat_3_grind, substat_4, substat_4_value, substat_4_enchant, substat_4_grind))
    # Equipped runes
    for mon in data["unit_list"]:
        for rune in mon["runes"]:
            id = rune["rune_id"]
            assigned_to = rune["occupied_id"]
            if assigned_to == 0:
                assigned_to = None
            runeset = rune["set_id"]
            slot = rune["slot_no"]
            stars = rune["class"]
            ancient = 0
            level = rune["upgrade_curr"]
            original_quality = rune["extra"]
            value = rune["sell_value"]
            if stars > 10:
                ancient = 1
                stars = stars - 10
                original_quality = original_quality - 10
            if level >= 12:
                quality = 4
            elif level >= 9:
                quality = 3
            elif level >= 6:
                quality = 2
            elif level >= 3:
                quality = 1
            else:
                quality = 0
            if original_quality > quality:
                quality = original_quality
            # TODO: Calculate
            #efficiency = rune["efficiency"]
            #max_efficiency = rune["max_efficiency"]
            efficiency = 0
            max_efficiency = 0
            main_stat = rune["pri_eff"][0]
            main_stat_value = rune["pri_eff"][1]
            if rune["prefix_eff"][0] > 0:
                innate_stat = rune["prefix_eff"][0]
                innate_stat_value = rune["prefix_eff"][1]
            else:
                innate_stat = None
                innate_stat_value = 0
            if len(rune["sec_eff"]) > 0:
                substat_1 = rune["sec_eff"][0][0]
                substat_1_value = rune["sec_eff"][0][1]
                substat_1_enchant = rune["sec_eff"][0][2]
                substat_1_grind = rune["sec_eff"][0][3]
            else:
                substat_1 = None
                substat_1_value = 0
                substat_1_enchant = 0
                substat_1_grind = 0
            if len(rune["sec_eff"]) > 1:
                substat_2 = rune["sec_eff"][1][0]
                substat_2_value = rune["sec_eff"][1][1]
                substat_2_enchant = rune["sec_eff"][1][2]
                substat_2_grind = rune["sec_eff"][1][3]
            else:
                substat_2 = None
                substat_2_value = 0
                substat_2_enchant = 0
                substat_2_grind = 0
            if len(rune["sec_eff"]) > 2:
                substat_3 = rune["sec_eff"][2][0]
                substat_3_value = rune["sec_eff"][2][1]
                substat_3_enchant = rune["sec_eff"][2][2]
                substat_3_grind = rune["sec_eff"][2][3]
            else:
                substat_3 = None
                substat_3_value = 0
                substat_3_enchant = 0
                substat_3_grind = 0
            if len(rune["sec_eff"]) > 3:
                substat_4 = rune["sec_eff"][3][0]
                substat_4_value = rune["sec_eff"][3][1]
                substat_4_enchant = rune["sec_eff"][3][2]
                substat_4_grind = rune["sec_eff"][3][3]
            else:
                substat_4 = None
                substat_4_value = 0
                substat_4_enchant = 0
                substat_4_grind = 0
            insert(db, "rune", (uid, id, assigned_to, runeset, slot, stars, ancient, level, quality, original_quality, value, efficiency, max_efficiency, main_stat, main_stat_value, innate_stat, innate_stat_value, substat_1, substat_1_value, substat_1_enchant, substat_1_grind, substat_2, substat_2_value, substat_2_enchant, substat_2_grind, substat_3, substat_3_value, substat_3_enchant, substat_3_grind, substat_4, substat_4_value, substat_4_enchant, substat_4_grind))
    db.commit()

"""
Parses rune craft item data (table rune_craft).

:param db: Sqlite database connection.
:param data: Data in SWEX json file.
"""
def parseRuneCraft(db, data):
    print("Parsing rune craft itmes...")
    uid = data["wizard_info"]["wizard_id"]
    for item in data["rune_craft_item_list"]:
        id = item["craft_item_id"]
        type = item["craft_type"]
        value = item["sell_value"]
        info = str(item["craft_type_id"])
        """
        info : 5 or 6 digit number: RRSSQ
          RR: Rune
          SS: Stat
          Q:  Quality
        """
        quality = int(info[-1:])
        stat = int(info[-4:-2])
        rune = int(info[:-4])
        insert(db, "rune_craft", (uid, id, type, quality, rune, stat, value))

"""
Parses guild data (tables guild, guild_member).

:param db: Sqlite database connection.
:param data: Data in SWEX json file.
"""
def parseGuild(db, data):
    print("Parsing guild...")
    if len(data["guild"]["guild_info"]) < 1:
        return
    guild = data["guild"]["guild_info"]
    id = guild["guild_id"]
    name = guild["name"]
    level = guild["level"]
    experience = guild["experience"]
    recruiting = guild["recruit_status"]
    members = guild["member_now"]
    leader = guild["master_wizard_id"]
    comment = guild["comment"]
    notice = guild["notice"]
    insert(db, "guild", (id, name, level, experience, recruiting, members, leader, comment, notice))
    # Single quates ahead, dirty fix
    for k, member in data["guild"]["guild_members"].items():
        m = json.loads(str(member).replace("'", '"'))        
        id = m["wizard_id"]
        guild = member["guild_id"]
        grade = member["grade"]
        name = member["wizard_name"]
        level = member["wizard_level"]
        rating = member["rating_id"]
        arena_score = member["arena_score"]
        joined = member["join_timestamp"]
        last_login = member["last_login_timestamp"]
        in_war = 0 # Checked later
        has_defense = 0 # Checked later
        insert(db, "guild_member", (id, guild, grade, name, level, rating, arena_score, joined, last_login, in_war, has_defense))
    cursor = db.cursor()
    for war in data["guildwar_member_list"]:
        cursor.execute("UPDATE guild_member SET in_war = 1 WHERE guild = ? AND id = ?;", (war["guild_id"], war["wizard_id"]))
    for defense in data["guild_member_defense_list"]:
        if len(defense["unit_list"]) > 0:
            cursor.execute("UPDATE guild_member SET has_defense = 1 WHERE id = '" + str(defense["wizard_id"]) + "';")
    db.commit()
    cursor.close()

"""
Begin script
"""
db = openDatabase('application/sw.sqlite')
data = readFile(FILE)
parsePlayer(db, data)
parseScenarios(db, data)
parseDefense(db, data)
parseBuildings(db, data)
parseDecorations(db, data)
# TODO homunculus_skill_list
parseUnits(db, data)
parseSummonSpecial(db, data)
parseInventory(db, data)
parseRunes(db, data)
parseRuneCraft(db, data)
parseGuild(db, data)