from datetime import datetime
import sys, os, json, requests, time, sqlite3
sys.path.insert(1, 'j:\\Spotty\secrets_and_database')
from alive_progress import alive_bar
from sqlite3 import Error
from endless_playlist_secrets import master_id, spotify_user_id
from refresh import Refresh

refreshCaller = Refresh()
job_done = False

class genre4:
    def __init__(self):
        self.spotify_token = ""
        self.here_i_am = os.path.dirname(os.path.abspath(__file__))
        self.dbpath = 'j:\\Spotty\secrets_and_database'
        self.database_name = "Missing Enormous Feed"
        self.genre_list = []
        self.col_list = []

    def genre_change(self):
        self.database = self.dbpath + r"\\" + self.database_name + ".db"
        self.get_the_genres()
        self.change_the_genres()

    def get_the_genres(self):
        conn = self.create_connection(self.database)
        with conn:
            cur = conn.cursor()
            try:
                cur.execute("SELECT * FROM Genre")
                rows = cur.fetchall()
                for row in rows:
                    self.genre_list.append(row[0])
            except Error as e5:
                print("get_the_genres")
                print(e5)
                return

    def change_the_genres(self):
        gen = 0
        conn = self.create_connection(self.database)
        with conn:
            cur = conn.cursor()
            try:
                while gen < len(self.genre_list):
                    print(self.genre_list[gen])
                    cur.execute("SELECT * FROM Artist_Genre")
                    colnames = cur.description
                    for head in colnames:
                        self.col_list.append(head[0])
                    rows = cur.fetchall()
                    for row in rows:
                        spotgen = 2
                        while spotgen < 18:
                            genstr = str(row[spotgen])
                            x = genstr.find(self.genre_list[gen])
                            if x != -1:
                                cur.execute("UPDATE Artist_Genre SET '" + str(self.col_list[spotgen]) + "' = '" + str(self.genre_list[gen].upper()) + "' WHERE ARTIST_NAME = '" + str(row[0]) + "'")
                                conn.commit()
                            spotgen+=1
                    gen+=1
            except Error as e1:
                print("change the Genre")
                print(e1)
            return conn                        

    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 = genre4()
a.genre_change()