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

refreshCaller = Refresh()
job_done = False

os.system('cls')
print("[•_•] Datify - Get Any Playlist Tracks and Put them in a Database.")
print("Version 2.3.0 : 13-MAR-2021 @ 16:30 (c) Viking Design. (^o.o^)")
print("Enter the desired Playlist id in the self.playlist_id variable below")
print("In the format self.playlist_id = '5CHUxOspd44zpbkLTi2RLD'")
print("User: " + spotify_user_id)

class Datify:
    def __init__(self):
        self.user_id = spotify_user_id
        # spotify:playlist:4WnZKRxeDdKjc6lvhTIs0W
        self.playlist_id = '2KJilK9PSYEaeTkdPYlkKo'
        self.spotify_token = ""
        self.here_i_am = os.path.dirname(os.path.abspath(__file__))
        
    def do_the_business(self):
        tic = time.perf_counter()
        while True:
            self.get_track_count_from_playlist()
            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

    def check_database(self):                             # NOTE: Create Database and Table If Not Exist
        global database, conn        
        database = self.here_i_am + r"\\" + self.playlist_name + ".db"
        sql_create_tracks_table = """ CREATE TABLE IF NOT EXISTS tracks (URI text PRIMARY KEY, TRACK_NAME text, ARTIST_NAME text, ALBUM_NAME text, TRACK_LENGTH text, RELEASE_DATE text, ADDED_AT text, PLAYLIST text); """
        conn = self.create_connection(database)
        if conn is not None:
            self.create_table(conn, sql_create_tracks_table)
        else:
            print("Error! cannot create the database connection.")
                    
    def create_connection(self, db_file):           # NOTE: Create ConnectionTo Database
        conn = None
        try:
            conn = sqlite3.connect(db_file)
            return conn
        except Error as e:
            print(e)
        return conn

    def create_table(self, conn, create_table_sql): # NOTE: Create DAtabase Table
        try:
            c = conn.cursor()
            c.execute(create_table_sql)
            c.close()
            #self.add_to_db()
        except Error as e:
            print(e)

    def add_to_db(self):                            #NOTE: Add New Track To Database
            with conn:

                tracks = (self.track_uri, self.track_name, self.artist_name, self.album_name, self.track_length, self.release_date, self.added_at, self.playlist_name)  # NOTE Set the SQL Values
                sql = ''' INSERT OR IGNORE INTO tracks(URI,TRACK_NAME,ARTIST_NAME,ALBUM_NAME,TRACK_LENGTH,RELEASE_DATE,ADDED_AT,PLAYLIST)
                VALUES(?,?,?,?,?,?,?,?) '''
                cur = conn.cursor()                 
                cur.execute(sql, tracks)            
                conn.commit()


    def get_track_count_from_playlist(self):
        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   
        print(str(self.total) + " Tracks to add to database from " + self.playlist_name + " playlist" )
        t = 0
        while t < self.total:
            with alive_bar(self.total, title=self.playlist_name) as bar:
                for t in range (self.total):
                    if str(t)[-2:] == "00":
                        self.call_refresh()
                        if str(t)[-3:] == "000":
                            time.sleep(60)
                    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":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.track_length = json_data["items"][0]["track"]["duration_ms"]
                    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.added_at = json_data["items"][0]["added_at"]       
                    if response.status_code == 200:
                        self.add_to_db()
                        #self.check_database()
                        t += 1
                        bar()
        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()