# Imports

import json
import requests
import spotipy
import mysql.connector

from spotipy.oauth2 import SpotifyClientCredentials
from musixmatch import Musixmatch

# Establish connection to Database

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="spotify"
)

mycursor = mydb.cursor()

# Pulling top ten tracks from Spotify Playlist

spotify = spotipy.Spotify(client_credentials_manager=SpotifyClientCredentials())
album_image = spotify.playlist_tracks(playlist_id='37i9dQZEVXbLRQDuF5jeBp',
                              fields='items.track')

# Verifying table exists if so it clears the rows (assumes the required columns and only the required columns already exist)
# But if table does not exist it creates the table and required columns

stmt = "SHOW TABLES LIKE 'spotipy'"
mycursor.execute(stmt)
result = mycursor.fetchone()



if result:
    mycursor.execute("TRUNCATE TABLE spotipy")
    print('Table Already Exists!')
else:
    mycursor.execute("CREATE TABLE spotipy (id VARCHAR(255), album_url VARCHAR(255), album_image VARCHAR(255), album_release_date VARCHAR(255), lyrics_body VARCHAR(255), album_name VARCHAR(255), track_name VARCHAR(255), artist_name VARCHAR(255))")
    print('Table Created!')

# Spotify Variables - Inserting the desired filtered json variables into rows and columns of desired table

id = 0

for item in album_image['items'][:10]:
    id += 1
    album_url = item['track']['external_urls']['spotify']
    album_image = item['track']['album']['images'][0]['url']
    album_release_date = item['track']['album']['release_date']

    # Insert to Table

    sql = "INSERT INTO spotipy (id, album_url, album_image, album_release_date) VALUES (%s, %s, %s, %s)"

    val = (id, album_url, album_image, album_release_date)
    mycursor.execute(sql, val)

# iTunes Variables

id = 0

url = 'https://rss.itunes.apple.com/api/v1/us/itunes-music/top-songs/all/10/explicit.json'
r = requests.get(url)

with open('/Users/JT/Documents/University Documents/CI_Capstone/itunes_api.json', 'wb') as f:
    f.write(r.content)

file = '/Users/JT/Documents/University Documents/CI_Capstone/itunes_api.json'
json_data = open(file).read()
json_obj = json.loads(json_data)

for each in json_obj['feed']['results']:
    id += 1
    album_name = each['collectionName']
    track_name = each['name']
    artist_name = each['artistName']

    # MusixMatch Variables

    musixmatch = Musixmatch('8620dcf1040f40448795da297ed8375b')
    lyrics = musixmatch.matcher_lyrics_get(track_name, artist_name)

    lyrics = lyrics["message"]["body"]["lyrics"]["lyrics_body"]
    lyrics_body = (lyrics)

    # Update Table

    mycursor.execute("""Update spotipy set lyrics_body=%s, album_name=%s, track_name=%s, artist_name=%s where id=%s""", (lyrics_body, album_name, track_name, artist_name, id))

    mydb.commit()