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 edc:
    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.track_uri_list = []
        self.track_name_list = []
        self.artist_name_list = []
        self.album_name_list = []
        self.release_date_list = []
        self.database_name = "Enormous Feed"

    def call_refresh(self):         # NOTE  Euan's Mk.2
        global refreshCaller
        while True:
            self.spotify_token = refreshCaller.refresh()
            if self.spotify_token == -1:
                print(
                    "==============================\n[!] Error refreshing token, retrying in 30 Seconds\n==============================")
                time.sleep(30)
            else:
                break

    def check_the_database(self):
        self.database = self.dbpath + r"\\" + self.database_name + ".db"
        self.add_database_records_to_lists()
        self.check_data_in_list()
        print()

    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 update_track_list_table(self, rec):
        conn = self.create_connection(self.database)
        with conn:
            cur = conn.cursor()
            try:
                cur.execute("UPDATE Track_List SET ALBUM_NAME = '" + self.album_name + "' WHERE URI = '" +
                            self.track_uri_list[rec] + "'")
                conn.commit()
            except Error as e5:
                print("update_track_list_table")
                print(e5)
                return
            
    def add_database_records_to_lists(self):
        self.item = 0
        conn = self.create_connection(self.database)
        cur = conn.cursor()
        try:
            with conn:
                cur.execute("SELECT * FROM Track_List ORDER BY ARTIST_NAME ASC")
                rows = cur.fetchall()
                self.trw = len(rows)
                rw = 1
                for row in rows:
                    print("Working on Item " + str(rw) + " of " + str(self.trw), end='\r')
                    self.track_uri_list.append(row[0])
                    self.album_name_list.append(row[3])
                    rw+=1
                    continue
        except Error as e2:
            print("Check_Database")
            print(e2)
        return
        
    def check_data_in_list(self):
        print()
        rec=0
        while rec < self.trw:
            uri = self.track_uri_list[rec]
            if uri == '':
                print ("No URI")
                self.get_track_details(rec)
            album = self.album_name_list[rec]
            if album == '':
                self.get_track_details(rec)
            if album is None:
                self.get_track_details(rec)
            rec+=1
            if rec > 99:
                if str(rec)[-2:] == "00":
                    self.call_refresh()
            continue

    def get_track_details(self, rec):
        uriid = self.track_uri_list[rec][14:]
        query = "https://api.spotify.com/v1/tracks/{}".format(uriid)
        response = requests.request("get", url=query, headers={"Content-type": "application/json", "Authorization": "Bearer {}".format(self.spotify_token)})
        json_data = json.loads(response.text)
        self.album_name = json_data["album"]["name"]
        self.album_name = self.album_name.replace("'", "")
        self.track_length = json_data["items"][0]["track"]["duration_ms"]
        print(self.album_name)
        print(self.track_length)
        print()
        self.update_track_list_table(rec)

a = edc()
a.call_refresh()
a.check_the_database()