import datetime, os, sqlite3, time
from alive_progress import alive_bar
from sqlite3 import Error

class bigun_check:
    def __init__(self):
        self.here_i_am = os.path.dirname(os.path.abspath(__file__))
        self.database_name = "All Playlists Tracks"
        self.playlist_list = []

    def start_the_check(self):
        tic = time.perf_counter()
        os.system('cls')
        current_datetime = datetime.datetime.now().strftime("%d-%m-%Y_%I-%M_%p")
        self.str_current_datetime = str(current_datetime)
        print("Checking ALL the Spotify Database Tables")
        print("This Could Take A While!!!")
        self.database = self.here_i_am + r"\\" + self.database_name + ".db"
        self.create_mega_track_table()
        self.do_the_big_job()
        toc = time.perf_counter()
        ty_res = time.gmtime(toc - tic)
        res = time.strftime("%H Hours :%M Minutes :%S Seconds", ty_res)
        print(f"Script Took {res}")

    def create_mega_track_table(self):
        pl = 1
        conn = self.create_connection(self.database)
        cur = conn.cursor()
        try:
            self.playlist_list.append("URI")
            self.playlist_list.append("TRACK_NAME")
            self.playlist_list.append("ARTIST_NAME")
            cur.execute("SELECT COUNT (name) from sqlite_master WHERE type='table';")
            self.total_playlist_to_check = cur.fetchone()[0]
            while pl < self.total_playlist_to_check:
                cur.execute("SELECT name FROM sqlite_master WHERE type='table';")
                pl_details = cur.fetchall()[pl]
                self.playlist_list.append(pl_details[0])
                pl=pl+1
            cur.execute('''CREATE TABLE IF NOT EXISTS A_Very_Big_Picture {}'''.format(tuple(self.playlist_list)))
            conn.commit()
        except sqlite3.Error as e:
            print("create_mega_track_table 1")
            print(e)
        return

    def do_the_big_job(self):
        pllist = 3
        conn =self.create_connection(self.database)
        cur = conn.cursor()
        try:
            while pllist < self.total_playlist_to_check:
                if (self.playlist_list[pllist])[0:8] == "playlist":
                    pllist=pllist+1
                    continue
                cur.execute("SELECT COUNT (*) FROM '" + self.playlist_list[pllist] +"'")
                self.tracks_to_check = cur.fetchone()[0]
                with alive_bar(self.tracks_to_check, title="Working On...  "+self.playlist_list[pllist]) as bar:
                    cur.execute("SELECT * FROM '" + self.playlist_list[pllist] + "'")
                    rows = cur.fetchall()
                    if rows == None:
                        pllist = pllist + 1
                        continue
                    for row in rows:
                        pluri = row[0]
                        pltrackname = row[1]
                        plartistname = row[2]
                        cur.execute("SELECT * FROM A_Very_Big_Picture WHERE URI = '" + pluri + "'")
                        urirow = cur.fetchone()
                        if urirow == None:
                            track = (pluri, pltrackname, plartistname)  # NOTE Set the SQL Values
                            sql = ''' INSERT OR IGNORE INTO A_Very_Big_Picture (URI,TRACK_NAME,ARTIST_NAME) VALUES(?,?,?) '''
                            cur = conn.cursor()
                            cur.execute(sql, track)
                            conn.commit()
                        cur.execute("UPDATE A_Very_Big_Picture SET '" + self.playlist_list[pllist] + "' = 'YES' WHERE URI = '" + pluri + "'")
                        conn.commit()
                        bar()
                pllist=pllist+1
        except sqlite3.Error as err2:
            print("do_the_big_job")
            print(err2)
        return        

    def create_connection(self, db_file):   # NOTE Connect to the database
        conn = None
        try:
            conn = sqlite3.connect(db_file)
            return conn
        except Error as e1:
            print("create_connection")
            print(e1)
        return conn

a = bigun_check()
a.start_the_check()