import MySQLdb
from scipy import stats
import numpy as np
import json
import sys
import matplotlib.pyplot as plt
import datetime


 
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])
# contractmonth = str(sys.argv[2])
# crop = str(sys.argv[3])
# thresh = float(sys.argv[4])

baseyear = '2024'
contractmonth = 'Mar'
crop = 'Corn'
thresh = 0.7
zz='z'

output = []

def GetFirstDayClose(year, month, crop):
    # Get the close and min date for the given symbol
    currentInitialPrice = f'select *, Week(date,3) as Weeks, dayofyear(str_to_date(concat("2024", Week(date,3), " Wednesday"), "%x %v %W")) as dayofyear from MarketHistory where Crop = "{crop}" and Contract = "{month}" and year = {year} group by Weeks order by Weeks asc;' 
    # print(currentInitialPrice)
    cur.execute(currentInitialPrice)
    out = np.asarray(list(cur.fetchall()))
    return out


def GetAvgPriceIndex(year, month, crop):
    years = ''
    if len(year) > 0:
        for x in year:
            years += f' or year = {x}'
        years = 'year = ' + years[3:]
        years = ' and (' + years[7:] + ')'
        
    # Get the average price index for every Week for given crop, year, and contract [avgPriceIndex, currentWeek, dayOfYear, avgClose]
    avgPriceIndex = f'select avg(PriceIndex), Week(date,3) as Weeks, dayofyear(str_to_date(concat("{baseyear}", Week(date,3), " Wednesday"), "%x %v %W")) as dayofyear, avg(Close) from MarketHistory where Crop = "{crop}" and Contract = "{contractmonth}" {years} group by Weeks order by Weeks asc;' 
    cur.execute(avgPriceIndex)
    avgPriceIndex = np.asarray(list(cur.fetchall()))
    return avgPriceIndex


def GetAnalogYears(year, month, crop, thresh):
    # Select all years from MarketHistory that are not the baseyear for given contract, crop, year and r^2 thresh
    yearsql = f'select distinct year from MarketHistory where year <> "{year}" and contract = "{month}" and crop = "{crop}" order by year asc;'
    cur.execute(yearsql)
    compareyear = np.asarray(list(cur.fetchall()))
    
    for row in range(compareyear.shape[0]):    
        sql = f'select fromtable.date, fromtable.close, totable.close from (select concat(month(Date),"-",day(Date)) as date, close from MarketHistory where year = "{year}" and contract = "{month}" and crop="{crop}") as fromtable, (select concat(month(Date),"-",day(Date)) as date, close from MarketHistory where year = "{compareyear[row][0]}" and contract = "{month}" and crop="{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)
        
        slope, intercept, r_value, p_value, std_err = stats.linregress(base,test)
        if(float(r_value**2) >= float(thresh) and float(slope) > 0): #r^2 > thresh and positive slope
            output.append(compareyear[row][0])
            
    return [output, data]



out = GetFirstDayClose(baseyear, contractmonth, crop)
currentWeek = out[:,14]
currentClose = out[:,9]
currentDates = out[:,5]

minDate = min(currentDates)
firstDateIndex = next(i for i, x in enumerate(currentDates) if x == minDate) # index in currentDates with min date

analogYears = []

while len(analogYears ) == 0:
    analogYearsReturn = GetAnalogYears(baseyear, contractmonth, crop, thresh) # find analog years 
    analogYears = analogYearsReturn[0]
    if thresh > 0.5:
        thresh -= 0.1
    else:
        analogYearsReturn = GetAnalogYears(baseyear, contractmonth, crop, 0) # find analog years 
        analogYears = analogYearsReturn[0]
        break

analogPriceIndex = GetAvgPriceIndex(analogYears, contractmonth, crop) # [avgPriceIndex, currentWeek, dayOfYear, avgClose]
analogPrice = [currentClose[firstDateIndex] * analogPriceIndex[:,0], analogPriceIndex[:,1]]  # find analog price for year, month, crop

x = []
zz=analogPrice[1]
for i in range(0,len(zz),1):
    wk = int(zz[i])
    wkDate = datetime.datetime.strptime(f"2024-W{wk}" + "-1", "%Y-W%W-%w").date()
    if int(wkDate.month) >= int(currentDates[firstDateIndex].month) and wkDate.month <= 12:
        x.append(datetime.date(int(baseyear)-1, int(wkDate.month), int(wkDate.day)))
    else:
        x.append(datetime.date(int(baseyear), int(wkDate.month), int(wkDate.day)))


y = analogPrice[0]
plt.scatter(x,y, label='Analog')
plt.scatter(currentDates, currentClose, label='Current')


plt.legend()
plt.title(f'{baseyear} {contractmonth} {crop}')


print(f'Thres = {thresh}')
print(f'Analog Years: {analogYears}')

    



db.close()