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

class dupefinder:
    def __init__(self):
        self.here_i_am = os.path.dirname(os.path.abspath(__file__))
        ############ SET YOUR VALUES HERE #################
        self.database_name = "Endless Feed"
        self.limit = 0.9
        ############ SET YOUR VALUES HERE #################
    def find_the_dupes(self): 
        tic = time.perf_counter()
        now = datetime.datetime.now()
        os.system('cls')
        print("Current Run Time:= " + now.strftime("%d-%m-%Y %H:%M:%S"))
        print()
        print("Dupe Finder v.1.3.0 02/10/2022 \U00002694 Viking Utilities \U00002694")
        print("\033[91m########################### Latest Update ################################\033[0m")
        print("\033[33mv.1.3.0 SHows Only NEW duplicates. SET Reviewed equal to 1 in New_Dupes_Table to mark as done.")
        print("Currently set to  " + str(self.limit) + " (" + str(self.limit*100) + "%) match... Set this value at line 9. ")
        print("Also set database name correctly at line 8.\033[0m")
        print("\033[91m##########################################################################\033[0m")
        self.database = self.here_i_am + r"\\" + self.database_name + ".db"
        self.create_connection(self.database)
        self.check_dupe_tables()
        self.xfer_new_reviewed()
        self.flex_new_dupe_table()
        self.compare_tracks()
        self.hoover()
        toc = time.perf_counter()
        ty_res = time.gmtime(toc - tic)
        res = time.strftime("  %H  Hours :  %M  Minutes :  %S  Seconds", ty_res)
        print()
        print(f"Dupe Finder Took {res}")
        print()

    def create_connection(self, db_file):  # NOTE Connect to the database
        self.conn = None
        self.conn = sqlite3.connect(db_file)
        return self.conn

    def xfer_new_reviewed(self):
        cur = self.conn.cursor()
        cur.execute("SELECT * FROM New_Dupe_Tracks WHERE REVIEWED = 1")
        rows = cur.fetchall()
        for row in rows:
            revuri = row[0]
            revname = row[1]
            revartist = row[1]
            revrev = row[3]
            revtrack = (revuri, revname, revartist, revrev)
            sql = ''' INSERT OR IGNORE INTO Reviewed_Dupe_Tracks (URI,TRACK_NAME,ARTIST_NAME,REVIEWED)
            VALUES(?,?,?,?) '''
            cur.execute(sql, revtrack)
            self.conn.commit()

    def flex_new_dupe_table(self):
        cur = self.conn.cursor()
        cur.execute("DROP TABLE IF EXISTS New_Dupe_Tracks")
        self.conn.commit()
        cur.execute("CREATE TABLE IF NOT EXISTS New_Dupe_Tracks (URI text PRIMARY KEY, TRACK_NAME text, ARTIST_NAME text, REVIEWED integer);")
        self.conn.commit()

    def compare_tracks(self):
        cur = self.conn.cursor()
        cur.execute("SELECT * FROM Track_List ORDER BY TRACK_NAME ASC")
        rows = cur.fetchall()
        i = 0
        while i < self.total_records:
            with alive_bar(self.total_records, title="Comparing Database Tracks") as bar:
                for row in rows:
                    self.track1uri = row[0]
                    self.track1name = row[1]
                    self.track1artist = row[2]
                    cur2 = self.conn.cursor()
                    cur2.execute("SELECT * FROM Track_List ORDER BY TRACK_NAME ASC")
                    rows2 = cur2.fetchall()
                    for row2 in rows2:
                        self.track2uri = row2[0]
                        self.track2name = row2[1]
                        self.track2artist = row2[2]
                        if self.track1uri == self.track2uri:
                            continue
                        self.string_similarity(self.track1name, self.track2name)
                        if self.result_ratio > self.limit:
                            self.string_similarity(self.track1artist, self.track2artist)
                            if self.result_ratio > self.limit:
                                cur4 = self.conn.cursor()
                                cur4.execute("SELECT * FROM Reviewed_Dupe_Tracks where URI = '" + self.track1uri + "' OR URI = '" + self.track2uri + "'")
                                row = cur4.fetchone()
                                if row is None:
                                    track1 = (self.track1uri, self.track1name, self.track1artist, 0)  # NOTE Set the SQL Values
                                    track2 = (self.track2uri, self.track2name, self.track2artist, 0)  # NOTE Set the SQL Values
                                    sql = ''' INSERT OR IGNORE INTO New_Dupe_Tracks (URI,TRACK_NAME,ARTIST_NAME,REVIEWED)
                                    VALUES(?,?,?,?) '''
                                    sql2 = ''' INSERT OR IGNORE INTO New_Dupe_Tracks (URI,TRACK_NAME,ARTIST_NAME,REVIEWED)
                                    VALUES(?,?,?,?) '''
                                    cur3 = self.conn.cursor()
                                    cur3.execute(sql, track1)
                                    cur3.execute(sql2, track2) 
                                    self.conn.commit()
                    i+=1
                    bar()
                return

    def string_similarity(self, str1, str2):
        result = difflib.SequenceMatcher(a=str1.lower(), b=str2.lower())
        self.result_ratio = result.ratio()
        return self.result_ratio

    def check_dupe_tables(self):
        cur = self.conn.cursor()
        cur.execute("SELECT COUNT (*) FROM Track_List")
        self.total_records = int(cur.fetchone()[0])
        cur.execute("CREATE TABLE IF NOT EXISTS New_Dupe_Tracks (URI text PRIMARY KEY, TRACK_NAME text, ARTIST_NAME text, REVIEWED integer);")
        cur.execute("CREATE TABLE IF NOT EXISTS Reviewed_Dupe_Tracks (URI text PRIMARY KEY, TRACK_NAME text, ARTIST_NAME text, REVIEWED integer);")
        self.conn.commit()

    def hoover(self):
        cur = self.conn.cursor()
        cur.execute("VACUUM")
        self.conn.commit()

a = dupefinder()
a.find_the_dupes()