import xml.etree.ElementTree as ET
import sqlite3

con = sqlite3.connect('15.2_trackdb.sqlite')
cur = con.cursor()

cur.executescript('''
DROP TABLE IF EXISTS Artist;
DROP TABLE IF EXISTS Album;
DROP TABLE IF EXISTS Genre;
DROP TABLE IF EXISTS Track;
CREATE TABLE Artist(
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    name TEXT UNIQUE);
CREATE TABLE Album(
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    artist_id INTEGER,
    title TEXT UNIQUE);
CREATE TABLE Genre(
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    name TEXT UNIQUE);
CREATE TABLE Track(
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    album_id INTEGER, genre_id INTEGER,
    title TEXT UNIQUE,
    len INTEGER, rating INTEGER, count INTEGER);
''')

def lookup(d,key):
    found = False
    for child in d:
        if found : return child.text
        if child.tag=='key' and child.text==key : found=True
    return None

fname = input('Enter file name:')
if len(fname)<1 : fname = '15.2_Library.xml'

stuff = ET.parse(fname)
tracks = stuff.findall('dict/dict/dict')
print('Dict Count:',len(tracks))

for track in tracks:
    if (lookup(track,'Track ID') is None): continue
    name = lookup(track,'Name')
    artist = lookup(track,'Artist')
    album = lookup(track,'Album')
    genre = lookup(track,'Genre')
    count = lookup(track,'Play Count')
    rating = lookup(track,'Rating')
    length = lookup(track,'Total Time')
    if name is None or artist is None or album is None or genre is None:
        continue
    #print(name,artist,album,count,rating,length)
    cur.execute('''INSERT OR IGNORE INTO Artist (name)
        VALUES (?)''',(artist,))
    cur.execute('SELECT id FROM Artist WHERE name=?',(artist,))
    artist_id = cur.fetchone()[0]

    cur.execute('''INSERT OR IGNORE INTO Album (title,artist_id)
        VALUES (?,?)''',(album,artist_id))
    cur.execute('SELECT id FROM Album WHERE title=?',(album,))
    album_id = cur.fetchone()[0]

    cur.execute('''INSERT OR IGNORE INTO Genre (name)
        VALUES (?)''',(genre,))
    cur.execute('SELECT id FROM Genre WHERE name=?',(genre,))
    genre_id = cur.fetchone()[0]

    cur.execute('''INSERT OR REPLACE INTO Track
        (album_id,genre_id,title, len, rating, count)
        VALUES (?,?,?,?,?,?)''',
        (album_id,genre_id, name,length,rating,count))

    con.commit()

cur.close()
