import snowflake.connector
import pandas as pd

USER = '<your user id>'
PASSWORD = '<your snowflake password>'
ACCOUNT = 'orchard'

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


cur = cnx.cursor()

all_tracks = pd.read_csv('prophet_tracks.csv', delimiter=',')

track_list_params = [ tuple(x) for x in all_tracks.values ]
#print track_list_params


dfDict = {}

sql_text = (
	"SELECT download_activity_date, sum(units) FROM FACTS.PROD.FACT_ANALYTICS "
	"WHERE trackid=%s AND storeid = 286 AND labelid = %s AND "
	"download_activity_date>= '2015-01-01' GROUP BY 1 ORDER BY 1 asc")

df_track_lookup = {}

for track_params in track_list_params:
	print "Querying " + str(track_params)
	dfDict[track_params[0]] = (pd.read_sql(sql_text, cnx, params=track_params))


import pprint
pprint.pprint(dfDict)

import matplotlib
from fbprophet import Prophet
import matplotlib.pyplot as plt
plt.style.use('fivethirtyeight')

for id in dfDict:
	df = dfDict[id]
	
	#parse date column to datetime
	df['DOWNLOAD_ACTIVITY_DATE']=df['DOWNLOAD_ACTIVITY_DATE'].astype('datetime64[ns]')
	
	#rename columns to prophet standard
	df = df.rename(columns={'DOWNLOAD_ACTIVITY_DATE': 'ds','SUM(UNITS)': 'y'})
                        
	ax = df.set_index('ds').plot(figsize=(12, 8))
	ax.set_ylabel('Spotify Streams')
	ax.set_xlabel('Date')
	
	#define model
	my_model = Prophet(interval_width=0.8)

	#fit model
	my_model.fit(df)

	#make rows for future dates
	future_dates = my_model.make_future_dataframe(periods=14)
	print future_dates.tail()
	
	#create predictions
	forecast = my_model.predict(future_dates)
	print forecast[['ds', 'yhat', 'yhat_lower', 'yhat_upper']].tail(14)

	my_model.plot(forecast,
              uncertainty=True)
             
	my_model.plot_components(forecast)


	#plt.show()
	
	forecasted_dates = forecast[['ds', 'yhat']].tail(14).copy()
	forecasted_dates.insert(0,'id',id)
	
	#print forecasted_dates.head(5)
	
	forecasted_dates.to_csv('prophet_results.csv',header=False, index = False, mode = 'a')