import sqlite3
from sqlite3 import Error
import csv

dbase = "Enormous Feed.db"
rowdb = 1
conn = None
dburi = 0000
dbtrack_name = "None"
dbartist_name = "None"
try:
    conn = sqlite3.connect(dbase)
except Error as e1:
    print("Create_Connection")
    print(e1)

try:
    with open('AllTracks.csv', 'r') as csvfile:
        reader2 = csv.reader(csvfile)
        reader = csv.DictReader(csvfile)
        print(reader)
        #print(reader2)
        for row in reader:
            try:
                rowdb +=1
                dburi = row['URI']
                dbtrack_name = row['TRACK_NAME']
                dbartist_name = row['ARTIST_NAME']
                dbalbum_name = row['ALBUM_NAME']
                dbrelease_date = row['RELEASE_DATE']
                dbplay_count = row['PLAY_COUNT']
                dbpresent = row['PRESENT']
                dbqueued = row['QUEUED']
                print(rowdb, end='\r')
                try:
                    dbtrack_name = dbtrack_name.replace("'", "")
                    dbartist_name = dbartist_name.replace("'", "")
                    hyphen = str(dbtrack_name).find(" - ")
                    if hyphen != -1:
                        dbtrack_name = dbtrack_name[:hyphen]
                    sqbrack = str(dbtrack_name).find("[")
                    if sqbrack != -1:
                        dbtrack_name = dbtrack_name[:sqbrack]
                    feat = str(dbtrack_name).find(" (feat. ")
                    if feat != -1:
                        dbtrack_name = dbtrack_name[:feat]
                    remaster = str(dbtrack_name).find("(Remastered)")
                    if remaster != -1:
                        dbtrack_name = dbtrack_name[:remaster]
                    digrem = str(dbtrack_name).find("(Digitally")
                    if digrem != -1:
                        dbtrack_name = dbtrack_name[:digrem]
                    dubstep = str(dbtrack_name).find("(Dubstep")
                    if dubstep != -1:
                        dbtrack_name = dbtrack_name[:dubstep]
                    dbtrack_name = dbtrack_name.strip()
                    dbartist_name = dbartist_name.strip()
                    dbalbum_name = dbalbum_name.strip()
                except:
                    print()
                    print(dburi)
                    exit()
                cur = conn.cursor()
                try:
                    with conn:
                        cur.execute("CREATE TABLE IF NOT EXISTS Track_List (URI text PRIMARY KEY, TRACK_NAME text, ARTIST_NAME text, ALBUM_NAME text, RELEASE_DATE text, PLAY_COUNT integer, PRESENT integer, QUEUED integer);")
                        conn.commit()
                        cur.execute("SELECT * FROM Track_List WHERE URI = '" + dburi + "'")
                        data = cur.fetchall()
                        if len(data) == 0:
                            dbpresent = True
                            dbqueued = False
                            with conn:
                                try:
                                    tracks = (dburi, dbtrack_name, dbartist_name, dbalbum_name, dbrelease_date, dbplay_count, dbpresent, dbqueued)  # NOTE Set the SQL Values
                                    sql = ''' INSERT OR IGNORE INTO Track_List (URI,TRACK_NAME,ARTIST_NAME,ALBUM_NAME,RELEASE_DATE,PLAY_COUNT,PRESENT,QUEUED) VALUES(?,?,?,?,?,?,?,?) '''
                                    cur = conn.cursor()
                                    cur.execute(sql, tracks)
                                    conn.commit()
                                except sqlite3.IntegrityError as e3:
                                    print("Add_To_DB")
                                    print(e3)
                        else:
                            cur.execute("UPDATE Track_List SET PRESENT = True WHERE URI = '" + dburi + "'")
                            conn.commit()
                except Error as e2:
                    print("Check_Database")
                    print(e2)
            except:
                print()
                print(dburi)
                exit()
except:
    print()
    print("Failed after ")
    print(reader)
    print(rowdb)
    print(str(dburi) + " - " + str(dbtrack_name) + " - " + str(dbartist_name))