import pymysql.cursors
import authenticate
import larka_config as config
import log_parser

connection = pymysql.connect(host=config.HOST,
                             user=config.USER,
                             password=config.PASSWORD,
                             db=config.DATABASE,
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)


def check_exists(user):
    with connection.cursor() as cursor:
        # Read a single record
        sql = "SELECT `uid` FROM `user` WHERE `username`=%s"
        cursor.execute(sql, (user,))
        result = cursor.fetchone()
    if result is None:
        result = {}
    return result


def get_user_id(user):
    with connection.cursor() as cursor:
        # Read a single record
        sql = "SELECT `uid` FROM `user` WHERE `username`=%s"
        cursor.execute(sql, (user,))
        result = cursor.fetchone()
    return result["uid"]


def get_user_cred(uid):
    with connection.cursor() as cursor:
        sql = "SELECT `salt`, `hash` from `user` WHERE `uid`=%s"
        cursor.execute(sql, (uid,))
        result = cursor.fetchone()
    return result["salt"], result["hash"]


def create_user(username, clienthash):
    serversalt, serverhash = authenticate.myhash(clienthash)
    with connection.cursor() as cursor:
        sql = "INSERT INTO `user` (`username`, `salt`, `hash`) VALUES (%s, %s, %s)"
        cursor.execute(sql, (username, serversalt, serverhash))
        connection.commit()


def cedit_save(userkey, cursorposition, content):
    with connection.cursor() as cursor:
        # check whether userkey exists
        sql_check = "SELECT * FROM `cedit` WHERE `userkey`=%s"
        cursor.execute(sql_check, (userkey,))
        result = cursor.fetchone()
        if result is None:
            sql = "INSERT INTO `cedit` (`userkey`, `lastposition`, `content`) VALUES (%s,%s,%s)"
            cursor.execute(sql, (userkey, cursorposition, content))
        else:
            sql = "UPDATE `cedit` SET `lastposition`=%s, `content`=%s WHERE `userkey`=%s"
            cursor.execute(sql, (cursorposition, content, userkey))
        connection.commit()
    return {"status": userkey, "message": "saved"}


def cedit_restore(userkey):
    with connection.cursor() as cursor:
        sql = "SELECT `lastposition`, `content`, `lastsaved` FROM `cedit` WHERE `userkey`=%s"
        cursor.execute(sql, (userkey,))
        result = cursor.fetchone()
        if result is None:
            return {"error": "no such session key " + userkey}
    return {"lastposition": result["lastposition"], "content": result["content"], "lastsaved": result["lastsaved"].isoformat()}


def cedit_checkkey(userkey):
    with connection.cursor() as cursor:
        # check whether userkey exists
        sql_check = "SELECT * FROM `cedit` WHERE `userkey`=%s"
        cursor.execute(sql_check, (userkey,))
        result = cursor.fetchone()
        if result is None:
            return {"status": 1, "message": "key not in use"}
    return {"status": 0, "message": "key in use"}


def create_profile(data):

    d = []
    # check if data, else null
    try:
        d.append(data["firstname"])
    except KeyError:
        d.append("NULL")
    try:
        d.append(data["middlename"])
    except KeyError:
        d.append("NULL")
    try:
        d.append(data["lastname"])
    except KeyError:
        d.append("NULL")

    d.append(data["yob"])  # not null

    try:
        sex = data["sex"]
    except KeyError:
        sex = "X"
    if sex == "male":
        d.append("M")
    elif sex == "female":
        d.append("F")
    else:
        d.append("X")

    d.append(data["hel"])

    tlesc = data["tles"] + " " + data["tlesu"]
    d.append(tlesc)

    tlisc = data["tlis"] + " " + data["tlisu"]
    d.append(tlisc)

    try:
        if (data["cefr"] == "undefined"):
            d.append("X")
        else:
            d.append(data["cefr"])
    except KeyError:
        d.append("X")

    d.append(data["mothertongues"])

    try:
        othertongues = ",".join([o + " " + l for o, l in zip(data["othertongues"].split(","), data["othertonguelevels"].split(","))])
        d.append(othertongues)
    except KeyError:
        d.append("NULL")

    d.append(data["roles"])

    try:
        d.append(data["teachingsubject"])
    except KeyError:
        d.append("NULL")
    try:
        d.append(data["teachinglevel"])
    except KeyError:
        d.append("NULL")
    try:
        d.append(data["teacherid"])
    except KeyError:
        d.append("NULL")

    d.append(data["userid"])

    with connection.cursor() as cursor:
        # Create a new record
        sql = "INSERT INTO `profile` (`firstname`,`middlename`,`lastname`,`birthyear`,`sex`," \
              "`highest_ed`,`time_learning_swedish`,`time_living_sweden`,`self_cefr`," \
              "`mothertongue`,`othertongue`," \
              "`role`,`teaching_what`, `teaching_level`,`teacher_id`," \
              "`uid`) VALUES (%s, %s, %s, %s, %s," \
              " %s, %s, %s, %s," \
              " %s, %s," \
              " %s, %s, %s, %s," \
              " %s)"
        cursor.execute(sql, d)
        connection.commit()

def log(table, data):
    if table == "wordguess":
        values = log_parser.parse_wordguess(data)
        sql = "INSERT INTO `wordguess` (`sessionid`, `target`, `pos`, `level`, `language`, `letters`, `score`, `endstate`, `time_taken`) VALUES (%s, %s, %s,%s, %s, %s,%s, %s, %s)"
        with connection.cursor() as cursor:
            cursor.execute(sql, values)
            connection.commit()
    if table == "exe_linguist":
        values = log_parser.parse_exe_linguist(data)
    if table == "liwrix":
        values = log_parser.parse_liwrix(data)
        sql = "INSERT INTO `liwrix` (`uid`, `target`, `answer`, `timetaken`) VALUES (%s, %s, %s, %s)"
        with connection.cursor() as cursor:
            cursor.execute(sql, values)
            connection.commit()
    return {"Message": "Logged data"}