import sqlite3
from sqlite3 import Error

class release1:
    def __init__(self):
        self.dbpath = 'j:\\Spotty\secrets_and_database'
        self.database_name = "Enormous Feed"
        #self.database_name = "Endless Feed"
        self.row0_list = []
        self.row1_list = []
        self.row2_list = []
        self.row3_list = []
        self.row4_list = []

    def get_the_artists(self):
        self.database = self.dbpath + r"\\" + self.database_name + ".db"
        self.check_artist_genre_table()
        self.populate_the_artist_field()
        self.update_the_database_table()

    def check_artist_genre_table(self):
        conn = self.create_connection(self.database)
        with conn:
            cur = conn.cursor()
            try:
                cur.execute("CREATE TABLE IF NOT EXISTS Release_Date (URI text PRIMARY KEY, TRACK_NAME text, FULL_TRACK_NAME text, ARTIST_NAME text, FULL_ARTIST_NAME text, ALBUM_ARTIST_NAME text, ALBUM_NAME text, FULL_ALBUM_NAME text, SPOTIFY_DATE text, DISCOG_DATE text, VERIFIED text, FMDate text, dated text);")
                conn.commit()
            except Error as e5:
                print("Creae_Release_Date_table")
                print(e5)
                return
            
    def populate_the_artist_field(self):
        conn = self.create_connection(self.database)
        with conn:
            cur = conn.cursor()
            try:
                cur.execute("SELECT * FROM Track_List")
                rows = cur.fetchall()
                self.trw = len(rows)
                rw = 1
                for row in rows:
                    print("Adding Data to Lists Working on Item " +
                          str(rw) + " of " + str(self.trw), end='\r')
                    self.row0_list.append(row[0])
                    self.row1_list.append(row[1])
                    self.row2_list.append(row[2])
                    self.row3_list.append(row[3])
                    self.row4_list.append(row[4])                                        
                    rw+=1
                    continue
                print()
            except Error as e5:
                print("Check_Release_Date_table")
                print(e5)
                return
    
    def update_the_database_table(self):
        conn = self.create_connection(self.database)
        with conn:
            cur = conn.cursor()
            self.list_len = len(self.row0_list)
            x = 0
            while x < self.list_len:
                print("Adding Data to Lists Working on Item " + str(x) + " of " + str(self.list_len), end='\r')
                artist = (self.row0_list[x], self.row1_list[x], 0, self.row2_list[x], 0, 0, self.row3_list[x], 0, self.row4_list[x], 0, 0, 0, 0)
                sql2 = ''' INSERT OR IGNORE INTO Release_Date (URI,TRACK_NAME,FULL_TRACK_NAME,ARTIST_NAME,FULL_ARTIST_NAME,ALBUM_ARTIST_NAME,ALBUM_NAME,FULL_ALBUM_NAME,SPOTIFY_DATE,DISCOG_DATE,VERIFIED,FMDate,dated) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?) '''
                cur = conn.cursor()
                cur.execute(sql2, artist)
                x+=1
            print()
            print("Committing")
            conn.commit()

    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

a = release1()
a.get_the_artists()