import simplejson as json
from pysqlite2 import dbapi2 as sqlite

import os

# Connexion à la base de données sqlite
connection = sqlite.connect('rbfscores.sqlite')
cursor = connection.cursor()

# Création de la table users
cursor.execute('CREATE TABLE users (id INTEGER PRIMARY KEY ASC, name)')

fh = open('users.json')
users = json.load(fh)
for data in users:
    cursor.execute('INSERT INTO users VALUES (?, ?)', (data["id"], data["name"]))

connection.commit()

# Création de la table des chansons
cursor.execute('CREATE TABLE songs (id INTEGER PRIMARY KEY ASC, song, artist, album, image, track INTEGER, album_year INTEGER, genre, song_length INTEGER, pack, type, released, xbox_cost REAL, ps3_cost REAL, wii_cost REAL, guitar NUMERIC, bass NUMERIC, drums NUMERIC, keys NUMERIC, vocals NUMERIC, pro_guitar NUMERIC, pro_bass NUMERIC, pro_drums NUMERIC, pro_keys NUMERIC, harmonies NUMERIC, topic INTEGER)')

fh = open('songs.json')
songs = json.load(fh)
maxid = 0
# Bug ? certaines chansons n’ont pas d’id ni de difficultés pour les instruments
# FIXME
# On fait une première passe pour déterminer l’id le plus élevé et on en crée un
# Les difficultés sont mises à -1 par défaut
# FIXME
for data in songs:
    if data["id"] and maxid < data["id"]:
        maxid = int(data["id"])
maxid += 1
for data in songs:
    for item in ("guitar",
                 "bass",
                 "drums",
                 "keys",
                 "vocals",
                 "pro_guitar",
                 "pro_bass",
                 "pro_drums",
                 "pro_keys",
                 "harmonies"):
        if not data[item]:
            data[item] = -1
    if not data["id"]:
        data["id"] = maxid
        maxid += 1
    cursor.execute('INSERT INTO songs VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',
            (data["id"],
             data["song"],
             data["artist"],
             data["album"],
             data["image"],
             data["track"],
             data["album_year"],
             data["genre"],
             data["song_length"],
             data["pack"],
             data["type"],
             data["released"],
             data["xbox_cost"],
             data["ps3_cost"],
             data["wii_cost"],
             data["guitar"],
             data["bass"],
             data["drums"],
             data["keys"],
             data["vocals"],
             data["pro_guitar"],
             data["pro_bass"],
             data["pro_drums"],
             data["pro_keys"],
             data["harmonies"],
             data["topic"]))

connection.commit()

# Création de la table des scores
cursor.execute('CREATE TABLE songs_scores (id INTEGER, instr INTEGER, u INTEGER, sys INTEGER, l INTEGER, s INTEGER, stars INTEGER, r INTEGER, es INTEGER, er INTEGER)')

fh = open('songs_scores.json')
scores = json.load(fh)
for data in scores:
    cursor.execute('INSERT INTO songs_scores VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',
            (data["id"],
             data["instr"],
             data["u"],
             data["sys"],
             data["l"],
             data["s"],
             data["stars"],
             data["r"],
             data["es"],
             data["er"]))

connection.commit()

# Création de la table des cutoffs
cursor.execute('CREATE TABLE songs_stars (id INTEGER, instr INTEGER, min INTEGER, max INTEGER, stars INTEGER)')

fh = open('songs_stars.json')
stars = json.load(fh)
for data in stars:
    cursor.execute('INSERT INTO songs_stars VALUES (?, ?, ?, ?, ?)',
            (data["id"],
             data["instr"],
             data["min"],
             data["max"],
             data["stars"]))

connection.commit()

# Création de la table des scores en carrière
cursor.execute('CREATE TABLE career_scores (u INTEGER, sys INTEGER, instr INTEGER, s INTEGER, r INTEGER, ro INTEGER, so INTEGER)')

fh = open('career_scores.json')
stars = json.load(fh)
for data in stars:
    cursor.execute('INSERT INTO career_scores VALUES (?, ?, ?, ?, ?, ?, ?)',
            (data["u"],
             data["sys"],
             data["instr"],
             data["r"],
             data["s"],
             data["ro"],
             data["so"]))

connection.commit()