import os
import json
import requests
import time
import sqlite3
from sqlite3 import Error
from datetime import datetime
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
        self.playlist_id = '0aVpmCRFcFaFzl5GxlBswu'
        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.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"\Tracks - " + self.playlist_name + " by " + spotify_user_id + ".db"
        sql_create_tracks_table = """ CREATE TABLE IF NOT EXISTS tracks (URI text PRIMARY KEY, TRACK_NAME text, TRACK_LENGTH text, ARTIST_NAME text, ALBUM_NAME text, RELEASE_DATE text, ADDED_AT 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
        global track_id
        database = self.here_i_am + r"\Tracks - " + self.playlist_name + " by " + spotify_user_id + ".db"
        conn = self.create_connection(database)
        with conn:
            tracks = (self.track_uri, self.track_name, self.track_length, self.artist_name, self.album_name, self.release_date, self.added_at)
            track_id = self.create_track(conn, tracks)

    def create_track(self, conn, trackS):           # NOTE: Insert The Track Into Database
        try:
           sql = ''' INSERT INTO tracks(URI,TRACK_NAME,TRACK_LENGTH,ARTIST_NAME,ALBUM_NAME,RELEASE_DATE,ADDED_AT)
                  VALUES(?,?,?,?,?,?,?) '''
           cur = conn.cursor()
           cur.execute(sql, trackS)
           conn.commit()
           return cur.lastrowid
        except:
           return cur.lastrowid
        return

    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"])
        try:
            os.remove(self.here_i_am + "\Tracks - " + self.playlist_name + " by " + spotify_user_id + ".db")
        except:
            pass

    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):
            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.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.check_database()
        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()