import os, sys
import json
import requests
import time
import sqlite3
#from datetime import datetime
sys.path.insert(1, 'j:\\Spotty\secrets_and_database')
from refresh import Refresh

refreshCaller = Refresh()
job_done = False

os.system('cls')
print("[•_•] Datify plus for RVRB Starred- Get added favouriteracks and add to Enormous Cynthia.")

class Datify:
    def __init__(self):
        self.user_id = 'daftjoe'
        # spotify:playlist:72JrWIELA8g2BBIYuRwXKG = RVRB Starred
        # spotify:playlist:0CMRn8FQQBZI7uy7dwI7V6 = Temp Review
        self.playlist_id = '72JrWIELA8g2BBIYuRwXKG'
        self.spotify_token = ""
        self.database = r"j:\Spotty\secrets_and_database\\Enormous Feed.db"
        
    def do_the_business(self):
        tic = time.perf_counter()
        while True:
            self.get_track_count_from_playlist()
            self.create_connection(self.database)
            self.check_database()
            self.get_track_from_playlist()
            if job_done:
                toc = time.perf_counter()
                ty_res = time.gmtime(toc - tic)
                res = time.strftime("%H Hours :%M Minutes :%S Seconds",ty_res)
                print(f"Datify Took {res} To Add {self.total} Records To The {self.playlist_name} Database" )
                return

    # NOTE: Create ConnectionTo Database
    def create_connection(self, db_file):
        self.conn = None
        self.conn = sqlite3.connect(db_file)
        return self.conn

    def check_database(self):                             # NOTE: Create Database and Table If Not Exist
        cur = self.conn.cursor()
        cur.execute("DROP TABLE IF EXISTS tracks")
        cur.execute("DROP TABLE IF EXISTS dupe_tracks")
        self.conn.commit()
        cur.execute("CREATE TABLE IF NOT EXISTS tracks (URI text PRIMARY KEY, TRACK_NAME text, ARTIST_NAME text, ALBUM_NAME text, RELEASE_DATE text, PLAY_COUNT integer, PRESENT integer, QUEUED integer); ")
        cur.execute("CREATE TABLE IF NOT EXISTS dupe_tracks (URI text PRIMARY KEY, TRACK_NAME text, ARTIST_NAME text, ALBUM_NAME text, RELEASE_DATE text, PLAY_COUNT integer, PRESENT integer, QUEUED integer); ")
        self.conn.commit()
                    
    def add_to_db(self): 
        try:
            cur = self.conn.cursor()                           #NOTE: Add New Track To Database
            tracks = (self.track_uri, self.track_name, self.artist_name, self.album_name, self.release_date, self.playcount, self.present, self.queued)
            sql = ''' INSERT INTO tracks(URI,TRACK_NAME,ARTIST_NAME,ALBUM_NAME,RELEASE_DATE,PLAY_COUNT,PRESENT,QUEUED)
                     VALUES(?,?,?,?,?,?,?,?) '''
            cur.execute(sql, tracks)
            self.conn.commit()
            return cur.lastrowid
        except:
            tracks = (self.track_uri, self.track_name, self.artist_name, self.album_name, self.release_date, self.playcount, self.present, self.queued)
            sql = ''' INSERT INTO dupe_tracks(URI,TRACK_NAME,ARTIST_NAME,ALBUM_NAME,RELEASE_DATE,PLAY_COUNT,PRESENT,QUEUED)
                     VALUES(?,?,?,?,?,?,?,?) '''
            cur.execute(sql, tracks)
            self.conn.commit()
            print()
            print(self.track_uri)
            return cur.lastrowid


    def get_track_count_from_playlist(self):
        global track_count
        params={"limit":50}
        query = "https://api.spotify.com/v1/users/{}/playlists".format(self.user_id)
        response = requests.request("get", url=query, headers={"Content-type": "application/json","Authorization":"Bearer {}".format(self.spotify_token)},params=params)
        response_json = response.json()
        #
        for i in response_json["items"]:
            if (i["id"]) == self.playlist_id:
                self.playlist_name = (i["name"])
                self.total = (i["tracks"]["total"])

    def get_track_from_playlist(self):
        global  job_done   
        print(str(self.total) + " Tracks to add to database from " + self.playlist_name + " playlist" )
        for i in range (self.total):
            print("Working on track " + str(i), end='\r')
            self.offset = i
            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":100,"offset":self.offset})
            response_json = response.json()
            json_data = json.loads(response.text)
            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"]
            self.playcount = 0
            self.present = 1
            self.queued = 0

            if response.status_code == 200:
                #self.check_database()
                self.add_to_db()
        job_done = True

    def call_refresh(self): #NOTE Euans 2nd Fix
        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
        
a = Datify()
a.call_refresh()
a.do_the_business()