import sqlite3
from sqlite3 import Error


def create_connection(db_file):
    """ create a database connection to the SQLite database
        specified by db_file
    :param db_file: database file
    :return: Connection object or None
    """
    conn = None
    try:
        conn = sqlite3.connect(db_file)
        return conn
    except Error as e:
        print(e)

    return conn


def create_table(conn, create_table_sql):
    """ create a table from the create_table_sql statement
    :param conn: Connection object
    :param create_table_sql: a CREATE TABLE statement
    :return:
    """
    try:
        c = conn.cursor()
        c.execute(create_table_sql)
    except Error as e:
        print(e)

def create_user(conn, user):
    """
    Create a new user into users table
    :param conn:
    :param user:
    :return: user id
    """
    sql = ''' INSERT INTO ranks(id,xp,lvl)
              VALUES(?,?,?) '''
    cur = conn.cursor()
    cur.execute(sql, user)
    return cur.lastrowid

def create_guild(conn, guild):
    """
    Create a new guild table
    :param conn:
    :param guild:
    :return:
    """

    sql = ''' INSERT INTO guilds(id,prefix)
              VALUES(?,?) '''
    cur = conn.cursor()
    cur.execute(sql, guild)
    return cur.lastrowid

def update_user(conn, user):
    """
    update xp and lvl of user
    :param conn:
    :param task:
    :return: project id
    """
    sql = ''' UPDATE ranks
              SET xp = ? ,
                  lvl = ?
              WHERE id = ?'''
    cur = conn.cursor()
    cur.execute(sql, user)
    conn.commit()

def select_user(conn, user):
    """
    Get user xp and lvl in a array
    :param conn: the Connection object
    :param user:
    :return xp, lvl:
    """
    cur = conn.cursor()
    sql = """SELECT * FROM ranks"""
    cur.execute(sql)

    rows = cur.fetchall()

    xpandlvl = []
    #print('cur: ', cur)
    for row in rows:
        xpandlvl.append(row)
        #print(row)

    print(xpandlvl)
    return xpandlvl

def check_user(conn, user):
    """
    Checks if user exists
    :param conn: the Connection object
    :param user:
    :return boolean:
    """
    cur = conn.cursor()
    cur.execute("SELECT ? FROM ranks", (user,))

    rows = cur.fetchall()

    print('k', rows)

    try:
        if rows[1]:
           return bool(rows[1])
        else:
            return False
    except Exception as e:
      print('Nope.', e)
      return False

def connect():
    database = r"database.db"

    sql_create_ranks_table = """ CREATE TABLE IF NOT EXISTS ranks (
                                        id integer PRIMARY KEY,
                                        xp integer NOT NULL,
                                        lvl integer NOT NULL
                                    ); """

    sql_create_guilds_table = """CREATE TABLE IF NOT EXISTS guilds (
                                    id integer PRIMARY KEY,
                                    prefix text NOT NULL
                                );"""

    # create a database connection
    conn = create_connection(database)

    # create tables
    if conn is not None:
        # create ranks table
        create_table(conn, sql_create_ranks_table)

        # create guilds table
        create_table(conn, sql_create_guilds_table)

    else:
        print("Error! cannot create the database connection.")

def getConn():
    database = r"database.db"

    try:
    # create a database connection
        conn = create_connection(database)
        with conn:
          return conn
    except Error as e:
        print(e)


if __name__ == '__main__':
    connect()