#!/usr/bin/python3

import MySQLdb
from scipy import stats
import numpy as np
import json
import sys


def regress(x,y,curprice):
    slope, intercept, r_value, p_value, std_err = stats.linregress(x,y)
    return intercept + slope * curprice
    
 
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()

# crop = str(sys.argv[1]) #"Soybeans"
# contractmonth = str(sys.argv[2]) #"Jul"
# expiration = str(sys.argv[3]) #"06-22"
# curprice = float(sys.argv[4]) #"1079.2
# contractyear = str(sys.argv[5]) #2019
# strikes = str(sys.argv[6]) #1

crop = "Corn"
contractmonth = "Mar"
expiration = "2-22"
curprice = 614
contractyear = 2024
strikes = 0


sql = "select minmax.year, minmax.minimum, minmax.maximum, startclose.start, stopclose.stop " \
        "from "\
        	"(select year, min(close) as minimum, max(close) as maximum from MarketHistory where Contract = '" + str(contractmonth) + "' and Crop = '" + str(crop) + "' and Date >= (concat(year-" + str(contractyear) + "+year(date(now())),'-',month(date(now())),'-',day(date(now())))) and Date <= (concat(year,'-' , '" + str(expiration) + "')) and year >= 1900 group by year) as minmax, " \
        	"(select year, close as start, close from MarketHistory where contract = '" + str(contractmonth) + "' and crop = '" + str(crop) + "' and Date >= (concat(year-" + str(contractyear) + "+year(date(now())),'-',month(date(now())),'-',day(date(now())))) and year >= 1900 group by year order by date asc) as startclose, " \
        	"(select year, close as stop, close from MarketHistory where contract = '" + str(contractmonth) + "' and crop = '" + str(crop) + "' and Date >= (concat(year-" + str(contractyear) + "+year(date(now())),'-' , '" + str(expiration) + "')) group by year order by date desc) as stopclose " \
        "where startclose.year = minmax.year and stopclose.year = minmax.year order by year asc;"

cur.execute(sql)
data = np.asarray(list(cur.fetchall()))

start = data[:,3].astype(float)
end = data[:,4].astype(float)
maxclose = data[:,2].astype(float)
minclose = data[:,1].astype(float)


endpred = regress(start,end,curprice)
maxpred = regress(start,maxclose,curprice)
minpred = regress(start,minclose,curprice)

historicmaxpred = np.floor(regress(start,maxclose,start)/10-float(strikes))*10
historicminpred = np.floor(regress(start,minclose,start)/10+float(strikes))*10

mindev = np.std(start - minclose)
maxdev = np.std(maxclose - start)
enddev = np.std(end - start)

minrange = [float(minpred)-mindev, float(minpred), float(minpred) + mindev]
maxrange = [float(maxpred)-maxdev, float(maxpred), float(maxpred) + maxdev]
endrange = [float(endpred)-enddev, float(endpred), float(endpred) + enddev]

callfail = (np.greater_equal(end,historicmaxpred))
putfail = (np.greater_equal(historicminpred,end))

callsuccess = 100 - float(np.sum(np.greater_equal(end,historicmaxpred)))/float(np.size(end, axis=0))*100
putsuccess = 100 - float(np.sum(np.greater_equal(historicminpred,end)))/float(np.size(end, axis=0))*100

callfailyears = data[np.where(callfail>0)[0]][:,0]
callfailamount = end[np.where(callfail>0)[0]] - historicmaxpred[np.where(callfail>0)[0]]
if(callsuccess!= 100.0):
    avgcallfailamount = np.average(callfailamount)
else:
    avgcallfailamount = 0

putfailyears = data[np.where(putfail>0)[0]][:,0]
putfailamount = -end[np.where(putfail>0)[0]] + historicminpred[np.where(putfail>0)[0]]
if(putsuccess!=100.0):
    avgputfailamount = np.average(putfailamount)
else:
    avgputfailamount = 0

output = json.dumps({"max":maxrange, "min":minrange, "target":endrange, "callsuccess":callsuccess, "putsuccess":putsuccess, "callfailyears":callfailyears.tolist(), "putfailyears":putfailyears.tolist(), "callfailamount":callfailamount.tolist(), "putfailamount":putfailamount.tolist(),"avgcallfailamount":avgcallfailamount,"avgputfailamount":avgputfailamount})
#output = (np.array(minrange),np.array(endrange),np.array(maxrange),np.array(callsuccess),np.array(putsuccess),np.array([callfailyears,callfailamount]),np.array(avgcallfailamount))
print(output)


db.close()