import os
import sqlite3
from xdg.BaseDirectory import xdg_data_home


class Sqlite:
    db_directory = os.path.join(xdg_data_home, 'lonestar.fm')
    if not os.path.exists(db_directory):
        os.makedirs(db_directory)
    db = os.path.join(db_directory, 'scrobbles.db')
    conn = sqlite3.connect(db)
    cur = conn.cursor()

    def close(self):
        self.conn.close()


class Sync(Sqlite):
    def __init__(self):
        table = '''CREATE TABLE IF NOT EXISTS Tracks (
                Date TEXT, Artist TEXT, Title TEXT, Album TEXT,
                Duration INT, Timestamp INT, mbid TEXT)'''
        self.cur.execute(table)
        self.conn.commit()

    def write_into(self,
                   date, artist, title, album,
                   duration, timestamp, mbid):
        self.cur.execute(
                'SELECT * FROM Tracks WHERE (Title=? AND Timestamp=?)',
                (title, timestamp))
        entry = self.cur.fetchone()
        if not entry:
            insert_query = '''INSERT INTO Tracks(
                    Date, Artist, Title, Album,
                    Duration, Timestamp, mbid) VALUES (?,?,?,?,?,?,?)'''
            self.cur.execute(
                    insert_query,
                    (date, artist, title, album, duration, timestamp, mbid))
            self.conn.commit()
        else:
            print("Item already exists!")

    def select_all(self):
        self.cur.execute('SELECT * FROM Tracks')
        result = self.cur.fetchall()
        return result

    def drop_track(self, title, timestamp):
        query = 'DELETE FROM Tracks WHERE (Title=? AND Timestamp=?)'
        self.cur.execute(query, (title, timestamp))
        self.conn.commit()
        return True


class Compare(Sqlite):
    def __init__(self):
        tables = ['LastFM', 'LoneStar']
        for table in tables:
            self.create_table(table)

    def create_table(self, table_name):
        table = f'''CREATE TABLE IF NOT EXISTS {table_name} (
                Artist TEXT, Title TEXT, Album TEXT, Timestamp INT)'''
        self.cur.execute(table)
        self.conn.commit()

    def write_into(self, table_name, artist, title, album, timestamp):
        self.cur.execute(
                f'SELECT * FROM {table_name} WHERE (Title=? AND Timestamp=?)',
                (title, timestamp))
        entry = self.cur.fetchone()
        if not entry:
            query = f'''INSERT INTO {table_name} (
                    Artist, Title, Album, Timestamp) VALUES (?,?,?,?)'''
            self.cur.execute(query, (artist, title, album, timestamp))
            self.conn.commit()
        else:
            print("Item already exists!")

    def compare(self):
        # query = 'SELECT * FROM Lastfm EXCEPT SELECT * FROM Lonestar'
        query = '''
                CREATE TABLE Difference AS
                SELECT * FROM LastFM
                EXCEPT
                SELECT * FROM LoneStar
                '''
        self.cur.execute(query)
        self.conn.commit()
        # result = self.cur.fetchall()
        # return result

    def read_difference(self):
        query = 'SELECT * FROM Difference'
        self.cur.execute(query)
        result = self.cur.fetchall()
        return result

    def drop_table(self, table_name):
        self.cur.execute(f'DROP TABLE {table_name}')
        self.conn.commit()