import MySQLdb
import numpy as np
import sys
from similaritymeasures import Similarity
measures = Similarity()


 
db = MySQLdb.connect(host="a2nlmysql13plsk.secureserver.net",  # your host 
                     user="coreypace",       # username
                     passwd="Aud12rey",     # password
                     db="FarmBusiness")   # name of the database
 
# Create a Cursor object to execute queries.
cur = db.cursor()

baseyear = int(sys.argv[1]) #2018
contractmonth = str(sys.argv[2]) #"Jul"
crop = str(sys.argv[3]) #"Corn"
thresh = int(sys.argv[4]) #0 number of rank ordered similar years to include

output = []

yearsql = "select distinct year from MarketHistory where year <> " + str(baseyear) + " and contract = '" + contractmonth + "' and crop = '" + crop + "' order by year asc;"
cur.execute(yearsql)
compareyear = np.asarray(list(cur.fetchall()))
rsquare = np.zeros([compareyear.shape[0],2],dtype=float,order='C')

for row in range(compareyear.shape[0]):    
    sql = "select fromtable.date, fromtable.priceindex, totable.priceindex " + \
    "from (select concat(month(Date),'-',day(Date)) as date, priceindex from MarketHistory where year = " + str(baseyear) + \
    " and contract = '" + str(contractmonth) + "' and crop='" + str(crop) + "') as fromtable, " + \
    "(select concat(month(Date),'-',day(Date)) as date, priceindex from MarketHistory where year = " + \
    str(compareyear[row][0]) + " and contract = '" + str(contractmonth) + "' and crop='" + str(crop) + "') as totable" + " where fromtable.date = totable.date;"
    
    
    cur.execute(sql)
    data = np.asarray(list(cur.fetchall()))
    
    base = data[:,1].astype(float)
    test = data[:,2].astype(float)
    
    sim = measures.euclidean_distance(base,test)
    rsquare[row] = [compareyear[row][0],sim]

rsquare1 = rsquare[rsquare[:,1].argsort()]

for row in range(thresh):
    output.append(int(rsquare1[row][0]))

print(output)
#output = json.dumps({str("Year"):str(output)})

db.close()

#output = json.dumps({str("Days"):float(iv), "IV":int(days)})