import base64
import datetime
import requests
import sys

import snowflake.connector

import json
import pandas as pd
from pandas import DataFrame
from pprint import pprint
import datetime


USER = 'YOUR_SNOWFLAKE_USERNAME'
PASSWORD = 'YOUR_SNOWFLAKE_PASSWORD'
ACCOUNT = 'orchard'

# Connecting to Snowflake
cnx = snowflake.connector.connect(
  user=USER,
  password=PASSWORD,
  account=ACCOUNT,
)

#PUT LABEL ID IN QUERY BELOW
sql_text = (
	"select distinct da.artistname, dt.trackname, dt.isrc       "
	"from facts.prod.fact_analytics fa                          "
	"join facts.prod.dim_track dt on dt.trackid = fa.trackid    "
	"join facts.prod.dim_artist da on fa.artistid = da.artistid "
	"where fa.labelid = '8869';                                 "
	)


label_tracks = (pd.read_sql(sql_text, cnx))

print label_tracks[:20]





client_id = 'YOUR_SPOTIFY_CLENT_ID'
client_secret = 'YOUR_SPOTIFY_CLIENT_SECRET'

access_token = None


client_credentials = client_id + ':' + client_secret

base = base64.b64encode(client_credentials)
payload = {'grant_type': 'client_credentials'}
headers = {'Authorization': 'Basic {}'.format(base.decode('utf-8'))}

r = requests.post(
            'https://accounts.spotify.com/api/token',
            data=payload, headers=headers)

json_response = r.json()

access_token = json_response['access_token']

print access_token



auth = "Bearer " + access_token
user_id = 'spotify'

response = requests.get('https://api.spotify.com/v1/users/' + user_id + '/playlists?offset=0&limit=50', headers={"Authorization": auth})

data = response.json()

ids = []

total_playlists = data.get("total")
print total_playlists , "total playlists from user " + user_id

for offset in range (0, total_playlists, 50):
	#print offset 
	response = requests.get('https://api.spotify.com/v1/users/' + user_id + '/playlists?offset={}&limit=50'.format(offset), headers={"Authorization": auth})
	data = response.json()
	
	for item in data:
		ls = data.get("items")
		for n in range(len(ls)):
			id = [ls[n].get("name"), ls[n].get("id")]
			if id not in ids: ids.append(id)

pprint(ids)

new_tracks = []

# for each playlist in list of playlists get the tracks
for playlist in ids:
	uri = playlist[1]
	p_name = playlist[0].encode('utf-8')
	response = requests.get('https://api.spotify.com/v1/users/' + user_id + '/playlists/{}/tracks'.format(uri), headers={"Authorization": auth})
	# tracks json stored in t_data:
	t_data = response.json()
	#print t_data
	ls = t_data.get("items")
	try:
		for n in range(len(ls)):
			track = []
			id = ls[n].get("track").get("id").encode('utf-8')
			track.append(id)
			added = ls[n].get("added_at").encode('utf-8')
			track.append(added)
			title = ls[n].get("track").get("name").encode('utf-8')
			track.append(title)
			isrc = ls[n].get("track").get("external_ids").get("isrc").encode('utf-8')
			track.append(isrc)
			track.append(p_name)
			print track
			new_tracks.append(track)
	except: pass


new_tracks_df = DataFrame.from_records(new_tracks, columns=['ID','ADDED','TITLE','ISRC','PLAYLIST_NAME'])

new_tracks_df = new_tracks_df.sort_values(by=['ADDED'], ascending = False)

print new_tracks_df[:50]


new_label_tracks = new_tracks_df.merge(label_tracks, on=['ISRC'])

new_label_tracks['ADDED'] =  pd.to_datetime(new_label_tracks['ADDED']).dt.date

now = datetime.datetime.now().date()

new_label_tracks['DAYS_AGO'] = (now - new_label_tracks['ADDED']).dt.days

new_label_tracks = new_label_tracks.drop(new_label_tracks[new_label_tracks.DAYS_AGO>2].index)

print(new_label_tracks.to_string())

new_label_tracks.to_csv('rtp_mode_test_june27.csv')