SWDB/application/bin/log-profile.py

#!/usr/bin/python3

import sys
import os
import sqlite3
import json
import math

"""
Reads the API KEY, that must be passed as first command line argument.

:returns: Recovered API KEY.
:raises Exception: Th KEY couldn't be red.
"""
def readKey():
    try:
        #print(sys.argv[1])
        key = sys.argv[1]
        return key
    except Exception as e:
        print("Error parsing API KEY: " + str(e))
        raise

"""
Reads the JSON data, that must be passed as second command line argument.

:returns: Recovered data, in JSON format.
:raises Exception: The data couldn't be red or converted to JSON.
"""
def readData():
    try:
        #print(sys.argv[1])
        #data = json.loads(sys.argv[1])
        with open(sys.argv[2], 'r') as f:
            content = f.read()
        data = json.loads(content)
        return data
    except Exception as e:
        print("Error parsing data: " + str(e))
        raise

"""
Verifies that the API key matches the player data and that it exists in th DB.

:param db: Connection to the database.
:returns: Connection to the database.
:param data: Data in json format.
:param key: API KEY.
:returns: True if key and player match, False otherwise.
:raises IntegrityError: The queryes couldn't bre executed.
"""
def verifyKey(db, data, key):
    print('Verifying KEY...')
    status = False
    try:
        uid = data["wizard_info"]["wizard_id"]
        cursor = db.cursor()
        cursor.execute('SELECT count(uid) AS c FROM player WHERE uid = ? AND api_key = ?;', (uid, key))
        if cursor.fetchone()[0] == 1:
            status = True
        cursor.close()
    except sqlite3.IntegrityError as e:
        print("Error executing statement: " + str(e))
        raise
    return status

"""
Opens the database file and cleans the user tables.

It also disables referencial integrity.

: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():
    kdb = os.path.dirname(os.path.realpath(sys.argv[0])) + '/../sw.sqlite'
    udb = os.path.dirname(os.path.realpath(sys.argv[0])) + '/../../data/sw.sqlite'
    print('Configuring database...')
    try:
        db = sqlite3.connect(kdb)
        cursor = db.cursor()
        cursor.execute("attach '" + udb + "' as data;")
        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

"""
Clears the user tables.

It also disables referencial integrity.

:param db: Sqlite database connection.
:param data: Data in json format.
:raises IntegrityError: The queryes couldn't bre executed.
"""
def clearData(db, data):
    print('Clearing previous data...')
    try:
        uid = str(data["wizard_info"]["wizard_id"])
        print('UID: ' + str(uid))
        cursor = db.cursor()
        cursor.execute('PRAGMA foreign_keys = OFF;')
        cursor.execute('DELETE FROM scenario WHERE uid = ?', [uid])
        cursor.execute('DELETE FROM defense WHERE uid = ?', [uid])
        cursor.execute('DELETE FROM unit_skill WHERE unit IN (SELECT id FROM unit WHERE uid = ?)', [uid])
        cursor.execute('DELETE FROM unit WHERE uid = ?', [uid])
        cursor.execute('DELETE FROM rune WHERE uid = ?', [uid])
        cursor.execute('DELETE FROM building WHERE uid = ?', [uid])
        cursor.execute('DELETE FROM decoration WHERE uid = ?', [uid])
        cursor.execute('DELETE FROM inventory WHERE uid = ?', [uid])
        cursor.execute('DELETE FROM summon_special') # For every player
        cursor.execute('DELETE FROM guild') # TODO: Dont delete all guilds
        cursor.execute('DELETE FROM guild_member') # TODO: Dont delete all guilds, fix table
        cursor.execute('DELETE FROM rune_craft WHERE uid = ?', [uid])
        db.commit()
        cursor.close();
    except sqlite3.IntegrityError as e:
        print("Error executing statement: " + str(e))
        raise
    return


"""
Closes the database.

Before doing so, it also disables referencial integrity.

:param db: Connection to the database.
:returns: Connection to the database.
:raises IntegrityError: The queryes couldn't bre executed.
"""
def closeDatabase(name):
    print('Closing database connection...')
    try:
        cursor = db.cursor()
        cursor.execute('PRAGMA foreign_keys = ON;')
        cursor.close()
        db.commit()
        db.close()
    except sqlite3.IntegrityError as e:
        print("Error executing statement: " + 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()
    db.execute('PRAGMA foreign_keys = OFF;')
    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 json format.
"""
def parsePlayer(db, data):
    print("Parsing player...")
    cursor = db.cursor()
    uid = data["wizard_info"]["wizard_id"]
    name = data["wizard_info"]["wizard_name"]
    # TODO: Generate data
    mail = data["wizard_info"]["wizard_name"]
    password = data["wizard_info"]["wizard_name"]
    api_key = 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"]
    query = '''
        UPDATE player
        SET 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 = ?
        WHERE uid = ?;
    '''
    values = (
        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,        uid
    )
    try:
        cursor.execute(query, values)
        db.commit()
    except sqlite3.IntegrityError as e:
        print('Error updating player table with: ' + str(query) + ' <== ' + str(values) + ' || Error message:' +  str(e))
        raise

"""
Parses scenarios (table scenario).

:param db: Sqlite database connection.
:param data: Data in json format.
"""
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 json format.
"""
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 json format.
"""
def parseBuildings(db, data):
    print("Parsing buildings...")
    uid = data["wizard_info"]["wizard_id"]
    for bui in data["building_list"]:
        id = bui["building_id"]
        building = bui["building_master_id"]
        gain = bui["gain_per_hour"]
        insert(db, "building", (uid, id, building, gain))
    db.commit()

"""
Parses decoration buildings (table dcoration).

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

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

:param db: Sqlite database connection.
:param data: Data in json format.
"""
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", (id, skill_id, skill_level))
    db.commit()

"""
Parses inventory data (table inventory).

:param db: Sqlite database connection.
:param data: Data in json format.
"""
def parseInventory(db, data):
    print("Parsing inventory...")
    uid = data["wizard_info"]["wizard_id"]
    for item in data["inventory_info"]:
        id = item["item_master_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 json format.
"""
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 json format.
"""
def parseRunes(db, data):
    print("Parsing runes...")
    STAT_HP = 1
    STAT_HP_PCT = 2
    STAT_ATK = 3
    STAT_ATK_PCT = 4
    STAT_DEF = 5
    STAT_DEF_PCT = 6
    STAT_SPD = 8
    STAT_CRIT_RATE_PCT = 9
    STAT_CRIT_DMG_PCT = 10
    STAT_RESIST_PCT = 11
    STAT_ACCURACY_PCT = 12
    MAIN_STAT_VALUES = {
        # [stat][stars][level]: value
        STAT_HP: {
            1: [40, 85, 130, 175, 220, 265, 310, 355, 400, 445, 490, 535, 580, 625, 670, 804],
            2: [70, 130, 190, 250, 310, 370, 430, 490, 550, 610, 670, 730, 790, 850, 910, 1092],
            3: [100, 175, 250, 325, 400, 475, 550, 625, 700, 775, 850, 925, 1000, 1075, 1150, 1380],
            4: [160, 250, 340, 430, 520, 610, 700, 790, 880, 970, 1060, 1150, 1240, 1330, 1420, 1704],
            5: [270, 375, 480, 585, 690, 795, 900, 1005, 1110, 1215, 1320, 1425, 1530, 1635, 1740, 2088],
            6: [360, 480, 600, 720, 840, 960, 1080, 1200, 1320, 1440, 1560, 1680, 1800, 1920, 2040, 2448],
        },
        STAT_HP_PCT: {
            1: [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 18],
            2: [2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 19],
            3: [4, 6, 8, 10, 12, 14, 16, 18, 20, 22, 24, 26, 28, 30, 32, 38],
            4: [5, 7, 9, 11, 13, 16, 18, 20, 22, 24, 27, 29, 31, 33, 36, 43],
            5: [8, 10, 12, 15, 17, 20, 22, 24, 27, 29, 32, 34, 37, 40, 43, 51],
            6: [11, 14, 17, 20, 23, 26, 29, 32, 35, 38, 41, 44, 47, 50, 53, 63],
        },
        STAT_ATK: {
            1: [3, 6, 9, 12, 15, 18, 21, 24, 27, 30, 33, 36, 39, 42, 45, 54],
            2: [5, 9, 13, 17, 21, 25, 29, 33, 37, 41, 45, 49, 53, 57, 61, 73],
            3: [7, 12, 17, 22, 27, 32, 37, 42, 47, 52, 57, 62, 67, 72, 77, 92],
            4: [10, 16, 22, 28, 34, 40, 46, 52, 58, 64, 70, 76, 82, 88, 94, 112],
            5: [15, 22, 29, 36, 43, 50, 57, 64, 71, 78, 85, 92, 99, 106, 113, 135],
            6: [22, 30, 38, 46, 54, 62, 70, 78, 86, 94, 102, 110, 118, 126, 134, 160],
        },
        STAT_ATK_PCT: {
            1: [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 18],
            2: [2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 19],
            3: [4, 6, 8, 10, 12, 14, 16, 18, 20, 22, 24, 26, 28, 30, 32, 38],
            4: [5, 7, 9, 11, 13, 16, 18, 20, 22, 24, 27, 29, 31, 33, 36, 43],
            5: [8, 10, 12, 15, 17, 20, 22, 24, 27, 29, 32, 34, 37, 40, 43, 51],
            6: [11, 14, 17, 20, 23, 26, 29, 32, 35, 38, 41, 44, 47, 50, 53, 63],
        },
        STAT_DEF: {
            1: [3, 6, 9, 12, 15, 18, 21, 24, 27, 30, 33, 36, 39, 42, 45, 54],
            2: [5, 9, 13, 17, 21, 25, 29, 33, 37, 41, 45, 49, 53, 57, 61, 73],
            3: [7, 12, 17, 22, 27, 32, 37, 42, 47, 52, 57, 62, 67, 72, 77, 92],
            4: [10, 16, 22, 28, 34, 40, 46, 52, 58, 64, 70, 76, 82, 88, 94, 112],
            5: [15, 22, 29, 36, 43, 50, 57, 64, 71, 78, 85, 92, 99, 106, 113, 135],
            6: [22, 30, 38, 46, 54, 62, 70, 78, 86, 94, 102, 110, 118, 126, 134, 160],
        },
        STAT_DEF_PCT: {
            1: [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 18],
            2: [2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 19],
            3: [4, 6, 8, 10, 12, 14, 16, 18, 20, 22, 24, 26, 28, 30, 32, 38],
            4: [5, 7, 9, 11, 13, 16, 18, 20, 22, 24, 27, 29, 31, 33, 36, 43],
            5: [8, 10, 12, 15, 17, 20, 22, 24, 27, 29, 32, 34, 37, 40, 43, 51],
            6: [11, 14, 17, 20, 23, 26, 29, 32, 35, 38, 41, 44, 47, 50, 53, 63],
        },
        STAT_SPD: {
            1: [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 18],
            2: [2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 19],
            3: [3, 4, 5, 6, 8, 9, 10, 12, 13, 14, 16, 17, 18, 19, 21, 25],
            4: [4, 5, 7, 8, 10, 11, 13, 14, 16, 17, 19, 20, 22, 23, 25, 30],
            5: [5, 7, 9, 11, 13, 15, 17, 19, 21, 23, 25, 27, 29, 31, 33, 39],
            6: [7, 9, 11, 13, 15, 17, 19, 21, 23, 25, 27, 29, 31, 33, 35, 42],
        },
        STAT_CRIT_RATE_PCT: {
            1: [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 18],
            2: [2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 19],
            3: [3, 5, 7, 9, 11, 13, 15, 17, 19, 21, 23, 25, 27, 29, 31, 37],
            4: [4, 6, 8, 11, 13, 15, 17, 19, 22, 24, 26, 28, 30, 33, 35, 41],
            5: [5, 7, 10, 12, 15, 17, 19, 22, 24, 27, 29, 31, 34, 36, 39, 47],
            6: [7, 10, 13, 16, 19, 22, 25, 28, 31, 34, 37, 40, 43, 46, 49, 58],
        },
        STAT_CRIT_DMG_PCT: {
            1: [2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 19],
            2: [3, 5, 7, 9, 11, 13, 15, 17, 19, 21, 23, 25, 27, 29, 31, 37],
            3: [4, 6, 9, 11, 13, 16, 18, 20, 22, 25, 27, 29, 32, 34, 36, 43],
            4: [6, 9, 12, 15, 18, 21, 24, 27, 30, 33, 36, 39, 42, 45, 48, 57],
            5: [8, 11, 15, 18, 21, 25, 28, 31, 34, 38, 41, 44, 48, 51, 54, 65],
            6: [11, 15, 19, 23, 27, 31, 35, 39, 43, 47, 51, 55, 59, 63, 67, 80],
        },
        STAT_RESIST_PCT: {
            1: [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 18],
            2: [2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 19],
            3: [4, 6, 8, 10, 12, 14, 16, 18, 20, 22, 24, 26, 28, 30, 32, 38],
            4: [6, 8, 10, 13, 15, 17, 19, 21, 24, 26, 28, 30, 32, 35, 37, 44],
            5: [9, 11, 14, 16, 19, 21, 23, 26, 28, 31, 33, 35, 38, 40, 43, 51],
            6: [12, 15, 18, 21, 24, 27, 30, 33, 36, 39, 42, 45, 48, 51, 54, 64],
        },
        STAT_ACCURACY_PCT: {
            1: [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 18],
            2: [2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 19],
            3: [4, 6, 8, 10, 12, 14, 16, 18, 20, 22, 24, 26, 28, 30, 32, 38],
            4: [6, 8, 10, 13, 15, 17, 19, 21, 24, 26, 28, 30, 32, 35, 37, 44],
            5: [9, 11, 14, 16, 19, 21, 23, 26, 28, 31, 33, 35, 38, 40, 43, 51],
            6: [12, 15, 18, 21, 24, 27, 30, 33, 36, 39, 42, 45, 48, 51, 54, 64],
        },
    }
    SUBSTAT_INCREMENTS = {
        # [stat][stars]: value
        # Max possible substat value can be found by multiplying by 5
        STAT_HP: {
            1: 60,
            2: 105,
            3: 165,
            4: 225,
            5: 300,
            6: 375,
        },
        STAT_HP_PCT: {
            1: 2,
            2: 3,
            3: 5,
            4: 6,
            5: 7,
            6: 8,
        },
        STAT_ATK: {
            1: 4,
            2: 5,
            3: 8,
            4: 10,
            5: 15,
            6: 20,
        },
        STAT_ATK_PCT: {
            1: 2,
            2: 3,
            3: 5,
            4: 6,
            5: 7,
            6: 8,
        },
        STAT_DEF: {
            1: 4,
            2: 5,
            3: 8,
            4: 10,
            5: 15,
            6: 20,
        },
        STAT_DEF_PCT: {
            1: 2,
            2: 3,
            3: 5,
            4: 6,
            5: 7,
            6: 8,
        },
        STAT_SPD: {
            1: 1,
            2: 2,
            3: 3,
            4: 4,
            5: 5,
            6: 6,
        },
        STAT_CRIT_RATE_PCT: {
            1: 1,
            2: 2,
            3: 3,
            4: 4,
            5: 5,
            6: 6,
        },
        STAT_CRIT_DMG_PCT: {
            1: 2,
            2: 3,
            3: 4,
            4: 5,
            5: 6,
            6: 7,
        },
        STAT_RESIST_PCT: {
            1: 2,
            2: 3,
            3: 5,
            4: 6,
            5: 7,
            6: 8,
        },
        STAT_ACCURACY_PCT: {
            1: 2,
            2: 3,
            3: 5,
            4: 6,
            5: 7,
            6: 8,
        },
    }
    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 = 5
        elif level >= 9:
            quality = 4
        elif level >= 6:
            quality = 3
        elif level >= 3:
            quality = 2
        else:
            quality = 1
        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

        # Calculate efficiences
        efficiency = 0
        substats = []
        efficiency += float(MAIN_STAT_VALUES[main_stat][stars][15]) / float(MAIN_STAT_VALUES[main_stat][6][15])
        if innate_stat is not None:
            efficiency += innate_stat_value / float(SUBSTAT_INCREMENTS[innate_stat][6] * 5)
        if substat_1 is not None:
            substats.append(substat_1)
            efficiency += substat_1_value / float(SUBSTAT_INCREMENTS[substat_1][6] * 5)
        if substat_2 is not None:
            substats.append(substat_2)
            efficiency += substat_2_value / float(SUBSTAT_INCREMENTS[substat_2][6] * 5)
        if substat_3 is not None:
            substats.append(substat_3)
            efficiency += substat_3_value / float(SUBSTAT_INCREMENTS[substat_3][6] * 5)
        if substat_4 is not None:
            substats.append(substat_4)
            efficiency += substat_4_value / float(SUBSTAT_INCREMENTS[substat_4][6] * 5)
        efficiency = efficiency / 2.8 * 100
        max_efficiency = get_max_efficiency(substats, efficiency, level, stars, original_quality)
        if efficiency > 100:
            efficiency = 100;
        if max_efficiency > 100:
            max_efficiency = 100;
        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

            # Calculate efficiences
            efficiency = 0
            substats = []
            efficiency += float(MAIN_STAT_VALUES[main_stat][stars][15]) / float(MAIN_STAT_VALUES[main_stat][6][15])
            if innate_stat is not None:
                efficiency += innate_stat_value / float(SUBSTAT_INCREMENTS[innate_stat][6] * 5)
            if substat_1 is not None:
                substats.append(substat_1)
                efficiency += substat_1_value / float(SUBSTAT_INCREMENTS[substat_1][6] * 5)
            if substat_2 is not None:
                substats.append(substat_2)
                efficiency += substat_2_value / float(SUBSTAT_INCREMENTS[substat_2][6] * 5)
            if substat_3 is not None:
                substats.append(substat_3)
                efficiency += substat_3_value / float(SUBSTAT_INCREMENTS[substat_3][6] * 5)
            if substat_4 is not None:
                substats.append(substat_4)
                efficiency += substat_4_value / float(SUBSTAT_INCREMENTS[substat_4][6] * 5)
            efficiency = efficiency / 2.8 * 100
            max_efficiency = get_max_efficiency(substats, efficiency, level, stars, original_quality)
            if efficiency > 100:
                efficiency = 100;
            if max_efficiency > 100:
                max_efficiency = 100;
            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()

"""
calculates the max efficiency of a rune.

:param substats: Array of substat types, dont include empty ones.
:param efficiency: Current rune efficiency.
:param level: Current rune level.
:param stars: Rune stars.
:param quality: Rune original quality.
:return Max. efficiency.
"""
def get_max_efficiency(substats, efficiency, level, stars, quality):
    STAT_HP = 1
    STAT_HP_PCT = 2
    STAT_ATK = 3
    STAT_ATK_PCT = 4
    STAT_DEF = 5
    STAT_DEF_PCT = 6
    STAT_SPD = 8
    STAT_CRIT_RATE_PCT = 9
    STAT_CRIT_DMG_PCT = 10
    STAT_RESIST_PCT = 11
    STAT_ACCURACY_PCT = 12
    SUBSTAT_INCREMENTS = {
        # [stat][stars]: value
        STAT_HP: {
            1: 60,
            2: 105,
            3: 165,
            4: 225,
            5: 300,
            6: 375,
        },
        STAT_HP_PCT: {
            1: 2,
            2: 3,
            3: 5,
            4: 6,
            5: 7,
            6: 8,
        },
        STAT_ATK: {
            1: 4,
            2: 5,
            3: 8,
            4: 10,
            5: 15,
            6: 20,
        },
        STAT_ATK_PCT: {
            1: 2,
            2: 3,
            3: 5,
            4: 6,
            5: 7,
            6: 8,
        },
        STAT_DEF: {
            1: 4,
            2: 5,
            3: 8,
            4: 10,
            5: 15,
            6: 20,
        },
        STAT_DEF_PCT: {
            1: 2,
            2: 3,
            3: 5,
            4: 6,
            5: 7,
            6: 8,
        },
        STAT_SPD: {
            1: 1,
            2: 2,
            3: 3,
            4: 4,
            5: 5,
            6: 6,
        },
        STAT_CRIT_RATE_PCT: {
            1: 1,
            2: 2,
            3: 3,
            4: 4,
            5: 5,
            6: 6,
        },
        STAT_CRIT_DMG_PCT: {
            1: 2,
            2: 3,
            3: 4,
            4: 5,
            5: 6,
            6: 7,
        },
        STAT_RESIST_PCT: {
            1: 2,
            2: 3,
            3: 5,
            4: 6,
            5: 7,
            6: 8,
        },
        STAT_ACCURACY_PCT: {
            1: 2,
            2: 3,
            3: 5,
            4: 6,
            5: 7,
            6: 8,
        },
    }
    UPGRADE_VALUES = {
        rune_type: {
            stars: value/level_data[6]
            for stars, value in level_data.items()
        }
        for rune_type, level_data in SUBSTAT_INCREMENTS.items()
    }
    #substat_upgrades_remaining = (4 - math.floor((min(level, 12) / 3))) - (3 - quality)
    substat_upgrades_remaining = (4 - math.floor((min(level, 12) / 3))) - max((3 - quality), 0)
    new_stats = max(min(4 - len(substats), substat_upgrades_remaining), 0)
    old_stats = substat_upgrades_remaining - new_stats

    if old_stats > 0:
        # we can repeatedly upgrade the most value of the existing stats
        best_stat = max(
            0,  # ensure max() doesn't error if we only have one stat
            *[UPGRADE_VALUES[stat][stars] for stat in substats]
        )
        efficiency += best_stat * old_stats * 0.2 / 2.8 * 100
    if new_stats:
        # add the top N stats
        available_upgrades = sorted(
            [
                upgrade_value[stars]
                for stat, upgrade_value in UPGRADE_VALUES.items()
                if stat not in substats
            ],
            reverse=True
        )
        efficiency += sum(available_upgrades[:new_stats]) * 0.2 / 2.8 * 100

    return efficiency

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

:param db: Sqlite database connection.
:param data: Data in json format.
"""
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 json format.
"""
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()

"""
Deletes teams whose members no longer exists.

:param db: Sqlite database connection.
"""
def reconfigureTeams(db):
    print("Fixing teams...")
    uid = data["wizard_info"]["wizard_id"]
    cursor = db.cursor()
    cursor.execute("DELETE FROM team WHERE id IN (SELECT DISTINCT team FROM team_unit WHERE unit NOT IN (SELECT DISTINCT id FROM unit WHERE uid = " + str(uid) + ")) AND uid = " + str(uid) + ";")
    cursor.execute("DELETE FROM team_unit WHERE unit NOT IN (SELECT DISTINCT id FROM unit) OR team NOT IN (SELECT DISTINCT id FROM team);")
    db.commit()
    cursor.close()

"""
Begin script
"""
data = readData()
key = readKey()
db = openDatabase()
if verifyKey(db, data, key) == False:
    print("Invalid API KEY...")
    sys.exit(-1)
else:
    clearData(db, data)
    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)
    reconfigureTeams(db)
    closeDatabase(db)
sys.exit(0);