import sqlite3

def create_database(db_path):
    con = sqlite3.connect(db_path)
    cur = con.cursor()
    cur.execute('''CREATE TABLE players
                   (id integer PRIMARY KEY,
                    pid text,
                    team_id integer,
                    name text,
                    country integer,
                    age integer,
                    height integer,
                    weight integer,
                    value integer,
                    value_adjusted integer,
                    wage integer,
                    form integer,
                    goals integer,
                    assists integer,
                    matches integer,
                    nt_goals integer,
                    nt_assists integer,
                    nt_matches integer,
                    nt_status integer,
                    exp integer,
                    tw integer,
                    disc integer,
                    best_note integer,
                    main_order text,
                    best_note_order text,
                    avg_note integer,
                    all_notes text,
                    all_orders text,
                    match_ids text
                   )''')
    con.commit()

def add_players_to_db(players, db_path):
    con = sqlite3.connect(db_path)
    cur = con.cursor()
    for key, val in sorted(players.items()):
        all_notes = list_to_str(val['all_notes'])
        all_orders = list_to_str(val['all_orders'], is_orders = True)
        match_ids = list_to_str(val['match_ids'])
        query = '''INSERT INTO players (pid, team_id, name, country, age, height, weight, value, value_adjusted,
                   wage, form, goals, assists, matches, nt_goals, nt_assists, nt_matches, nt_status, exp, tw, disc,
                   best_note, main_order, avg_note, best_note_order, all_notes, all_orders, match_ids)
                   VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'''
        cur.execute(query, (key, val['team_id'], val['name'], val['country'], val['age'], val['height'],
                            val['weight'], val['value'], val['value_adjusted'], val['wage'], val['form'],
                            val['goals'], val['assists'], val['matches'], val['nt_goals'], val['nt_assists'],
                            val['nt_matches'], val['nt_status'], val['exp'], val['tw'], val['disc'], val['best_note'], val['main_order'],
                            val['avg_note'], val['best_note_order'], all_notes, all_orders, match_ids))
    con.commit()