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
        # spotify:playlist:6zJX8n1W09LUPH1Mxr8riyspotify:playlist:72JrWIELA8g2BBIYuRwXKG
        self.playlist_id = '72JrWIELA8g2BBIYuRwXKG'
        self.spotify_token = ""
        self.here_i_am = os.path.dirname(os.path.abspath(__file__))
        #self.database = self.here_i_am + r"\Tracks - " + self.playlist_name + " by " + spotify_user_id + ".db"
        
    def do_the_business(self):
        tic = time.perf_counter()
        while True:
            self.get_track_count_from_playlist()
            self.database = self.here_i_am + r"\Tracks - " + self.playlist_name + " by " + spotify_user_id + ".db"
            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("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); ")
        cur.execute("CREATE TABLE IF NOT EXISTS dupe_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); ")
        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.track_length, self.release_date, self.added_at, self.playlist_name)
            sql = ''' INSERT INTO tracks(URI,TRACK_NAME,ARTIST_NAME,ALBUM_NAME,TRACK_LENGTH,RELEASE_DATE,ADDED_AT,PLAYLIST)
                     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.track_length, self.release_date, self.added_at, self.playlist_name)
            sql = ''' INSERT INTO dupe_tracks(URI,TRACK_NAME,ARTIST_NAME,ALBUM_NAME,TRACK_LENGTH,RELEASE_DATE,ADDED_AT,PLAYLIST)
                     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"])
        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):
            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.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()
                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()