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

This is a temporary script file.
"""

from Modules.dataseries import dataseries
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.
dbcur = db.cursor()


crop = ['Gasoline', 'Heating_Oil', 'Crude_Oil']
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]
daysofyear = []
idx = []
curyeardate = []
maxduration = 14 # max number of days for trade
startdate = []
enddate = []
out = []
loopcount = 0
bigcount = 0
curmonth = datetime.datetime.now().month

def getmonths(commodity):
    monthlist = []
    sql = 'SELECT DISTINCT MarketCommodityMonths.ShortMonth \
            FROM (MarketCommodity INNER JOIN MarketCommodityMonths ON MarketCommodity.Symbol = MarketCommodityMonths.Symbol) INNER JOIN MarketMonths ON MarketCommodityMonths.ShortMonth = MarketMonths.ShortMonth \
            WHERE MarketMonths.MonthNumber Between ' + str(curmonth) + ' And ' + str(curmonth + 3) + ' and DBName = "' + str(commodity) + '" order by monthnumber asc'
    dbcur.execute(sql)
    months = dbcur.fetchall()
    for i in range(len(months)):
        monthlist.append(months[i][0])
    return monthlist

def getfirstyear(commodity):
    sql = 'select FirstYear from MarketCommodity where DBName = "' + str(commodity) + '"'
    dbcur.execute(sql)
    return dbcur.fetchall()[0][0]


a = getfirstyear('Gasoline')
# build array of all days of year

for i in range(350):
    day = datetime.date(curyear,monthnum,15) + datetime.timedelta(i)
    daysofyear.append(day.strftime('%m-%d'))
    idx.append(int(i))
    curyeardate.append(day)

posentry = pd.DataFrame({'Day':daysofyear}).join(pd.DataFrame({'Idx':idx})).join(pd.DataFrame({'Date':curyeardate}))
posexit = pd.DataFrame({'Day':daysofyear}).join(pd.DataFrame({'Idx':idx})).join(pd.DataFrame({'Date':curyeardate}))
posentry = posentry.set_index(['Day'])
posexit = posexit.set_index(['Day'])

for crop in ['Gasoline']:
    monthslist = getmonths(crop)
    firstyear = getfirstyear(crop)
    for month in monthslist:
        posentry1 = posentry
        posexit1 = posexit
        loopcount = 0
        for year in range(firstyear,2020):
            df = dataseries(crop, month, year).dataseries
            df['Day'] = pd.to_datetime(df['Date']).dt.strftime('%m-%d')
#            df['Month'] = month
            df[str(year) + '-Date'] = pd.to_datetime(df['Date']).dt.strftime('%Y-%m-%d')
            df = df.set_index(['Day'])
            del df['Date'], df['Open'], df['High'], df['Low']
            df.columns = [year, str(year) + '-Date']
            posentry1 = posentry1.join(df, how='outer')
            posentry1[year] = posentry1[year].fillna(method='bfill').fillna(method='ffill')
            posentry1[str(year) + '-Date'] = posentry1[str(year) + '-Date'].fillna(method='bfill').fillna(method='ffill')
            posexit1 = posexit1.join(df, how='outer')
            posexit1[year] = posexit1[year].fillna(method='ffill').fillna(method='bfill')
            posexit1[str(year) + '-Date'] = posexit1[str(year) + '-Date'].fillna(method='ffill').fillna(method='bfill')
            
        posentry1 = posentry1.set_index(['Idx'])
        posexit1 = posexit1.set_index(['Idx'])
        
        for column in posentry1:
            if posentry1[column].dtype == np.float64:
                for i in range(len(posentry1)):
                    for j in range(i+1,i+maxduration):
                        if j <= 349: 
        #                    if posentry['Date'][i].weekday() <= 4 and posentry['Date'][j].weekday() <= 4:
                            startdate.append(str(posentry1['Date'][i].month) + '-' + str(posentry1['Date'][i].day))
                            enddate.append(str(posexit1['Date'][j].month) + '-' + str(posexit1['Date'][j].day))
                            if position == 'short':
                                diff = posentry1[column][i] - posexit1[column][j]  # Short
                            else:
                                diff = posexit1[column][j] - posentry1[column][i]   # 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({column:out}))
                df_yr = df_yr.set_index(['Start', 'End'])
                
                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
        
        data = data.query("winrate >= 0.80 and mean > 0 and (Start == '4-17' or Start == '4-18' or Start == '4-19')")
        data = data.reset_index()
        data['Commodity'] = crop
        data['Month'] = month
        
        if bigcount == 0:
            finaldata = data
        else:
            finaldata = finaldata.append(data)
        bigcount += 1