#!/usr/bin/env python3
import sqlite3
from os import listdir
from os.path import isfile, join
from gen_song_SQL import create

#CONSTANTS
path = './music_csv'
sqlfile = 'MUSIC.SQL'
databasefile = 'database'

def main():
    print('foo')
    #Open file and write the introduction and CREATE table queries
    f = open(sqlfile, "w")
    f.write(introduction())
    f.write(tableSQL())

    #Create queries from all the files store in music_csv
    files = [f for f in listdir(path) if isfile(join(path, f))]
    for file in files:

        if file[0] == '.':
            continue
        #Generate queries
        fullpath = path + '/' + file
        SQL = create(fullpath)

        #Commend which file these queries are coming from
        f.write(fileComment(file))

        #Write the queries into the main SQL file
        f.write(SQL)

    #Done writing into the SQL file
    f.close()

    #Open a new file to be the SQLite database and reopen the SQL file for reading
    db_file = open(databasefile, "w")
    sql_file = open(sqlfile, "r")
    script = sql_file.read()

    #Connect to the database
    db = sqlite3.connect(databasefile)

    #Execute all the queries in the SQL file
    cursor = db.cursor()
    cursor.executescript(script)
    db.commit()

    #Close the database
    db.close()

    #Close the database file and sql file
    db_file.close()
    sql_file.close()


#Creates a string describing what files the queries are coming from
def fileComment(path):
    return f'''
/*Queries generated from {path}*/
    '''

#Creates a string with the introduction for the autogenerated sql file
def introduction():
    return '''\
/* MUSIC.SQL
This file sets up the game database with songs.
Read in the SQL file with SQL lite to generate the database and
save the database file within Application.persistentDataPath in
Unity as with the filename "database" to setup databse.
*/
'''

#Returns a multiline string with all the DDL queries to create tables
def tableSQL():
    return '''
CREATE TABLE IF NOT EXISTS songs
  	(id INTEGER PRIMARY KEY,
  	name STRING,
  	location STRING,
  	length INTEGER,
  	tempo INTEGER);

CREATE TABLE IF NOT EXISTS song_difficulty
  	(id INTEGER PRIMARY KEY,
  	song_id INTEGER,
    difficulty INTEGER,
  	CONSTRAINT fk_song FOREIGN KEY (song_id) REFERENCES songs(id));

CREATE TABLE IF NOT EXISTS notes
  	(id INTEGER PRIMARY KEY,
  	song_diff_id INTEGER,
  	hit_time INTEGER,
    length REAL,
  	CONSTRAINT fk_song_diff FOREIGN KEY (song_diff_id) REFERENCES song_difficulty(id));

CREATE TABLE IF NOT EXISTS top_ten
    (id INTEGER PRIMARY KEY,
    song_diff_id INTEGER,
    rank INTEGER,
    score INTEGER,
    name STRING,
    CONSTRAINT fk_song_diff FOREIGN KEY (song_diff_id) REFERENCES song_difficulty(id));
'''

if __name__ == "__main__":
    main()
