# -*- coding: utf-8 -*-
"""
Spyder Editor

This is a temporary script file.
"""

from Modules.dataseries import dataseries
from pandas.tseries.offsets import BDay
import numpy as np
import pandas as pd
import calendar
import datetime
import MySQLdb

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.
cursor = db.cursor()


crop = 'Gasoline'
month = 'Jul'
curyear = 2019
position = 'long'
abbr_to_num = {name: num for num, name in enumerate(calendar.month_abbr) if num}
monthnum = abbr_to_num[month]
out = []
startdate = []
enddate = []
duration = []
loopcount = 0

def getdate(str): # in form of date string 'month-day'
    mon = int(str.split('-')[0])
    day = int(str.split('-')[1])
    
    if datetime.date(curyear, mon, day) > datetime.date(curyear, monthnum, 15):
        curdate = datetime.date(curyear-1, mon, day)
    elif datetime.date(curyear, mon, day) > datetime.date(curyear, 1, 1) and datetime.date(curyear, mon, day) <= datetime.date(curyear, monthnum, 15):
        curdate = datetime.date(curyear, mon, day)
        
    return curdate

def getprice(str):
    curdate = getdate(str)
    mask = cur['Date'] == curdate
#    price = cur.query("Date == " + curdate.strftime("%Y-%m-%d")).Close[0]
    price = np.asarray(cur.loc[mask].Close)[0]
    return price
    
def test():
    out = []
    for i in range(len(data)):
        try:
            startprice = getprice(data.index[i][0])
            endprice = getprice(data.index[i][1])
            if position == 'short':
                out.append([data.index[i], startprice - endprice]) #Short
            else:
                out.append([data.index[i], endprice - startprice]) #Long
        except:
            continue
            
    return out

def getduration(i):
    first = data.index[i][0].split('-')
    second = data.index[i][1].split('-')
    try:
        if int(first[0]) <= int(second[0]):
            return ((datetime.date(2019,int(second[0]),int(second[1])) - datetime.date(2019,int(first[0]),int(first[1]))).days)
        else:
            return ((datetime.date(2020,int(second[0]),int(second[1])) - datetime.date(2019,int(first[0]),int(first[1]))).days)
    except:
        return 1000

def adjustdate(i,df,year,monthnum):
    date = datetime.date(curyear-1,monthnum,15)+datetime.timedelta(i)
    if date.weekday() > 4:
        return (date + BDay()).date()
    elif len(cur[(cur['Date']==date)]) == 0:
        return (date + BDay()).date()
    else:
        return date
  
def getdata(crop, month, year):
    sql = "select date, open, high, low, close " \
    "from "\
    	"MarketHistory " 
    sql = sql + "where year = '" + str(year) + "' and crop = '" + crop + "' and contract = '" + month + "' order by date asc;"
    cursor.execute(sql)
    data = pd.DataFrame(list(cursor.fetchall()))
    data.rename(columns={0:'Date', 1:'Open', 2:'High', 3:'Low', 4:'Close'}, inplace=True)
    return data
        
# get current contract dataset

cur = dataseries(crop, month, curyear).dataseries


# add data cols by year
            
for year in range(2018,2020):
    print(year)
    df = getdata(crop, month, year)
    npdf = np.asarray(df)
    for i in range(len(df)):
        for j in range(i+1,len(df)):
            startdate.append(str(df.Date[i].month) + '-' + str(df.Date[i].day))
            enddate.append(str(df.Date[j].month) + '-' + str(df.Date[j].day))
            if position == 'short':
                diff = df.Close[i] - df.Close[j]  # Short
            else:
                diff = df.Close[j] - df.Close[i]   # Long
            if diff <= 0:
                win = 0
            else:
                win = 1
            out.append(diff)
#    for i in range(0,364):
##        k = adjustdate(i,df,year,monthnum)
#        for j in range(i+1,365):
##            m = adjustdate(j,df,year,monthnum)
#            startdate.append(str(k.month) + '-' + str(k.day))
##            kindex = np.where(df['Date']==k)[0]
##            enddate.append(str(m.month) + '-' + str(m.day))
##            mindex = np.where(df['Date']==m)[0]
#            if position == 'short':
#                diff = df.Close[kindex] - df.Close[mindex]  # Short
#            else:
#                diff = df.Close[kindex] - df.Close[mindex]   # Long
#            if diff <= 0:
#                win = 0
#            else:
#                win = 1
#            out.append(diff)
    
    df_yr = pd.DataFrame({'Start':startdate}).join(pd.DataFrame({'End':enddate})).join(pd.DataFrame({year:out}))
    df_yr = df_yr.set_index(['Start', 'End'])
    df_yr = df_yr.drop_duplicates(keep='first')
    
    if loopcount == 0:
        data = df_yr
    else:
        data = data.join(df_yr, how='outer')
    
#    loopcount+=1
#    out = []
#    del df_yr
#    startdate = []
#    enddate = []
#
#data['mean'] = data.mean(axis=1)
#data['winrate'] = (data.select_dtypes(include='float64').gt(0).sum(axis=1)-1)/(data.count(axis=1) - 1)
#data['count'] = data.count(axis=1) - 2
#
#for i in range(len(data.index)):
#    duration.append(getduration(i))
#
#data['dur'] = duration
#
#data = data.query("count >= " + str(int(loopcount*0.75)) + " and winrate >= 0.85 and mean > 0 and dur < 60")

# --------------- Test on curyear -----------------------

#output = test()   
#
#win = 0
#sumtotal = 0
#for j in range(len(output)):
#    sumtotal += output[j][1]
#    if output[j][1] >= 0: win += 1
#
#print('Contract - ' + month)
#print('Crop - ' + crop)
#print('Total Profit - ' + str(sumtotal))
#print('Win Rate - ' + str(win/len(output)))
#print('Number of Trades - ' + str(len(output)))

db.close()