from datetime import datetime
import os
import json
import requests
import time
import sqlite3
from sqlite3 import Error
from secrets import spotify_user_id
from refresh import Refresh

refreshCaller = Refresh()
job_done = False

class vis_baby_sister:
    def __init__(self):
        self.user_id = spotify_user_id  
        self.spotify_token = ""              
        self.here_i_am = os.path.dirname(os.path.abspath(__file__))
        # NOTE: <== ADDNEW playlist URI.
        self.playlist_id = '6QiYdcp66TpiYC95V3kz8Q'
        self.added_records = 0

    def call_refresh(self):
        global refreshCaller
        while True:
            self.spotify_token = refreshCaller.refresh()
            if self.spotify_token == -1:
                print("[X] Error refreshing token, retrying in 30 Seconds [X]")
                time.sleep(30)
            else:
                break

    def start_here(self):
        print("Unnamed as of yet: v26.05.2022 Viking Utilities.")
        self.get_the_playlist()

    def create_connection(self, db_file):
        conn = None                        
        try:
            conn = sqlite3.connect(db_file) 
            return conn                  
        except Error as e1:
            print("Create_Connection")        
            print(e1)          
        return conn

    def get_the_playlist(self):                             
        self.get_track_count_from_playlist()
        ## print("Reading playlist '" + self.playlist_name + "' ")

        ## NOTE: HARD CODE MASTER DB NAME
        self.database = self.here_i_am + r"\\" + "Sound_Of_The_XXX.db"          
        
        while True:          
            self.get_track_from_playlist()
            if job_done:
                print (str(self.added_records) + " Tracks added to the MASTER database:")
                print("[NOTE] Manually add new tracks to 'MASTER' playlist BEFORE running Violet again !! [NOTE]")
                return

    def get_track_count_from_playlist(self):
        ## NOTE: Get the playlist name and track count.
        query = "https://api.spotify.com/v1/playlists/{}".format(self.playlist_id)
        response = requests.request("get", url=query, headers={"Content-type": "application/json", "Authorization": "Bearer {}".format(self.spotify_token)})
        json_data = json.loads(response.text)
        self.playlist_name = (json_data["name"])
        self.total = (json_data["tracks"]["total"])
        return                                                          

    def get_track_from_playlist(self):                                 
        global  job_done
        self.call_refresh()
        print(str(self.total) + " Tracks to add from playlist '" + self.playlist_name + "'" )
        t = 0
        while t < self.total:
                for t in range(self.total):
                    self.offset = t
                    query = "https://api.spotify.com/v1/playlists/{}/tracks".format(self.playlist_id)
                    response = requests.request("get", url=query, headers={"Content-type": "application/json","Authorization":"Bearer {}".format(self.spotify_token)}, params={"limit":1,"offset":self.offset})
                    json_data = json.loads(response.text)
                    try:
                        self.track_uri = json_data["items"][0]["track"]["uri"]
                        self.track_name = json_data["items"][0]["track"]["name"]
                        self.artist_name = json_data["items"][0]["track"]["artists"][0]["name"]
                        self.album_name = json_data["items"][0]["track"]["album"]["name"]
                        self.release_date = json_data["items"][0]["track"]["album"]["release_date"]
                        flag = False
                        if flag == False:
                            self.last_uri = self.track_uri
                            self.last_track = self.track_name
                            print("New track: " + self.last_track)
                            self.last_artist = self.artist_name
                            flag = True
                        if response.status_code == 200:
                            self.check_database()
                            t = t + 1
                            continue
                    except:
                        File_object = open(self.here_i_am + r"\\Rosemary Errors - " + spotify_user_id + ".txt", "a")
                        File_object.writelines(["Playlist Name - " + str(self.playlist_name) + " Track - " + str(t+1) + " Total Tracks - " + str(self.total) + "\n"])
                        File_object.writelines(["Last URI - " + str(self.last_uri) + " Last Track - " + str(self.last_track) + " Last Artist - " + str(self.last_artist) + "\n\n"])
                        File_object.close()
                        t = t - 2  
                        continue  
                
        job_done = True
        return 
        
    def check_database(self):
        ## NOTE: See if the track is already in the database.
        ## NOTE: If it is, set PRESENT flag. If not then add it.
        global conn
        conn = self.create_connection(self.database)   
        cur =  conn.cursor()                           
        try:
            with conn:                                                         
                cur.execute("SELECT * FROM Track_List WHERE URI = '" + self.track_uri + "'")    
                data = cur.fetchall()

                if len(data)== 0:
                    self.added_records = self.added_records + 1
                    self.add_to_db(conn)                
                else:                 
                    cur.execute("UPDATE Track_List SET PRESENT = True WHERE URI = '" + self.track_uri + "'")   
                    conn.commit()                       
                    return                       
        except Error as e2:
            print("Check_Database")         
            print(e2)
        return

    def add_to_db(self, conn):
        ## NOTE: Add the new track to the database.
        self.present = True
        with conn:
            try:
                tracks = (self.track_uri, self.track_name, self.artist_name, self.album_name,self.release_date, self.play_count, self.present, self.played)
                ## NOTE: Set the SQL Values
                sql = ''' INSERT OR IGNORE INTO 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()
                tracks2 = (self.track_uri, self.track_name, self.artist_name, self.album_name)
                sql2 = ''' INSERT OR IGNORE INTO Last_Run_Additions (URI,TRACK_NAME,ARTIST_NAME,ALBUM_NAME) VALUES(?,?,?,?) '''
                cur = conn.cursor()
                cur.execute(sql2, tracks2)
                conn.commit()
                return cur.lastrowid                
            except sqlite3.IntegrityError as e3:    
                print("Add_To_DB")                         
                print(e3)                           
                return cur.lastrowid               

a = vis_baby_sister()
a.call_refresh()
a.start_here()