from datetime import datetime
import shutil
import sys
import os
import sqlite3
from sqlite3 import Error

class new_violet:
    def __init__(self):
        self.here_i_am = os.path.dirname(os.path.abspath(__file__))
        ############################################
        #NOTE Change this to your database file name
        self.database_name = "FamilyFeed"
        ############################################

    def update_the_database(self):
        print("This will Update the database to the latest version so that is compatible with Vi & Cyn")
        print("Version 1.2 (21/06/2022 to  Violet v3.1.1 & Cynthia v7.0.0... Ollie inc.")
        self.database = self.here_i_am + r"\\" + self.database_name + ".db"
        self.backup_database()
        self.create_update_table()
        print("Updating Records")
        self.copy_tracks_to_new_table_after_conversion()
        self.remove_old_table()
        self.rename_table()

    def create_connection(self, db_file):  # NOTE Create a database connection
        conn = None
        try:
            conn = sqlite3.connect(db_file)
            return conn
        except Error as e1:
            print("Create_Connection")
            print(e1)
        return conn
    
    def backup_database(self):
        print("Backing Up Database. Don't Panic unless nothing happens for 2 minutes \U0001F633")
        current_datetime = datetime.now().strftime("%d-%m-%Y_%I-%M_%p")
        str_current_datetime = str(current_datetime)
        source = self.here_i_am + r"\\" + self.database_name + ".db"
        target = self.here_i_am + r"\\" + self.database_name + \
            "_Backup_" + str_current_datetime+".db"
        try:
            shutil.copy(source, target)
        except IOError as e:
            print("Unable to copy file. %s" % e)
        except:
            print("Unexpected error:", sys.exc_info())
    
    def create_update_table(self):
        global conn
        conn = self.create_connection(self.database)
        cur = conn.cursor()
        try:
            with conn:
                cur.execute("CREATE TABLE IF NOT EXISTS New_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()
                return
        except Error as e2:
            print("create_update_table")
            print(e2)
        return

    def copy_tracks_to_new_table_after_conversion(self):
        global conn
        conn = self.create_connection(self.database)
        cur = conn.cursor()
        try:
            cur.execute("SELECT COUNT(*) FROM Track_List")
            tot = cur.fetchone()
            cur.execute("SELECT rowid, * FROM Track_List")
            rows = cur.fetchall()
            for row in rows:
                rowid = row[0]
                if str(rowid)[-2:] == "00":
                    print(str(rowid) + " Records Complete")
                self.uri = row[1]
                self.track_name = row[2]
                self.artist_name = row[3]
                self.album_name = row[4]
                self.date = row[5]
                self.count = row[6]
                self.present = row[7]
                self.queued = row[8]
                self.convert_data()
                self.add_to_new_table()
        except sqlite3.IntegrityError as e1:
                print("conversion")
                print(e1)
                return cur.lastrowid


    def convert_data(self):
        self.track_name = str(self.track_name).upper()
        self.track_name = self.track_name.replace("'", "")
        self.artist_name = str(self.artist_name).upper()
        self.artist_name = self.artist_name.replace("'", "")
        self.album_name = str(self.album_name).upper()
        self.hyphen = str(self.track_name).find(" - ")
        if self.hyphen != -1:
            self.track_name = self.track_name[:self.hyphen]
        self.track_name = self.track_name.strip()
        self.artist_name = self.artist_name.strip()
        self.album_name = self.album_name.strip()

    def add_to_new_table(self):
        global conn
        conn = self.create_connection(self.database)
        cur = conn.cursor()
        try:
            tracks = (self.uri, self.track_name, self.artist_name, self.album_name,
                      self.date, self.count, self.present, self.queued)  # NOTE Set the SQL Values
            sql = ''' INSERT OR IGNORE INTO New_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()
            return cur.lastrowid
        except sqlite3.IntegrityError as e2:
            print("Add_To_New_Table")
            print(e2)
            return cur.lastrowid

    def remove_old_table(self):
        global conn
        conn = self.create_connection(self.database)
        cur = conn.cursor()
        cur.execute("DROP TABLE IF EXISTS Track_List")
        conn.commit()

    def rename_table(self):
        global conn
        conn = self.create_connection(self.database)
        cur = conn.cursor()
        cur.execute("ALTER TABLE  New_Track_List RENAME TO Track_list")
        conn.commit()
        cur.execute("VACUUM")
        conn.commit()

a = new_violet()
a.update_the_database()