# -*- coding: utf-8 -*-
"""
Created on Tue Apr  2 19:33:11 2019

@author: cpace
"""

from Modules.dataseries import dataseries
from Modules.pnp import pnp
from Modules.pnpstdev import pnpstdev
import numpy as np
import matplotlib.pyplot as plt
import MySQLdb
import datetime
import calendar

crop = 'Gasoline'
month = 'Jul'
pnpline = 'uplow'
totalyearsincluded = 15
final = []
d = []
loser = 0
winner = 0
sumwins = 0
sumdays = 0
percenthigh = 0
nextdaywin = 0
nextdaywinnings = 0
nextdaydrawdown = 0
abbr_to_num = {name: num for num, name in enumerate(calendar.month_abbr) if num}
monthnum = abbr_to_num[month]
maxdrawdown = 0
high = []

print('Commodity ' + str(crop))
print('Month ' + str(month))
print('Line ' + str(pnpline))

db = MySQLdb.connect(host="a2nlmysql13plsk.secureserver.net",  # your host 
                     user="coreypace",       # username
                     passwd="Aud12rey",     # password
                     db="FarmBusiness")   # name of the database

cur = db.cursor()

def getfirstyear(commodity):
    sql = 'select FirstYear from MarketCommodity where DBName = "' + str(commodity) + '"'
    cur.execute(sql)
    firstyear = cur.fetchall()[0][0]
    return max(firstyear,datetime.datetime.now().year - totalyearsincluded)

for year in range(getfirstyear(crop),datetime.datetime.now().year+1):
    count = 1
    looking = True
    df = dataseries(crop, month, year).dataseries
    a = pnp(crop, month, year)
    b = pnpstdev(a)
    downhigh = a.downBestFitHigh
    besthigh = a.maxBestFit
    bestlow = a.minBestFit
    uplow = a.upBestFitLow
    bestlowplus = b.bestlowplusstd
    uplowplus = b.uplowplusstd
    besthighminus = b.besthighminusstd
    
    if pnpline == 'downhigh':
        line = downhigh
    elif pnpline == 'besthigh':
        line = besthigh
    elif pnpline == 'bestlow':
        line = bestlow
    elif pnpline == 'uplow':
        line = uplow
    elif pnpline == 'bestlowplus':
        line = bestlowplus
    elif pnpline == 'uplowplus':
        line = uplowplus
    elif pnpline == 'besthighminus':
        line = besthighminus

    
    while count < len(df)-1:
       if df.Close[count] < line and line <= df.Close[count-1] and df.Date[count] > datetime.date(year, monthnum - 4, 1): #rises thru downhigh and closes above it
           belowline = []
           start = df.Close[count]
           startcount = count + 1
           while df.Close[startcount] < line:   #while it stays above downhigh
               belowline.append(df.Close[startcount]) #append closing price
               if startcount == len(df)-1:  #if end of dataset
                   break
               else:
                   startcount += 1
           if startcount == count + 1:   
#              final = [year, peakabovehigh, %bestlow, maxprofit, nextdaymaxprofit, nextdaymaxdrawdown]
               final.append([year, 0, 0, 0, df.Open[count+1] - df.Low[count+1],df.High[count+1] - df.Open[count+1]])
           else:
               final.append([year, belowline.index(min(belowline)) + 1, min(belowline)/bestlow, start - min(belowline), df.Open[count+1] - df.Low[count+1], df.High[count+1] - df.Open[count+1]])
           count += 1
       elif count == len(df)-1:
           break
       else:
           count += 1
   
for i in range(len(final)):
    if final[i][1] == 0:
        loser += 1
    else:
        winner += 1
        sumwins += final[i][3]
        sumdays += final[i][1]
        percenthigh += final[i][2]
#        d.append(final[i][2])
        high.append(final[i][3])
    
    if final[i][4] > 0:
        nextdaywin += 1
        nextdaywinnings += final[i][4]
        d.append(final[i][4])
        nextdaydrawdown += final[i][5]
        
        if final[i][5] > maxdrawdown:
            maxdrawdown = final[i][5]
        
try:
    print(str(winner) + ' Wins (Closed below line at least 2 days)')
    print(str(loser) + ' Losers (Closed above line next day)')
    print(str(round(winner/(winner+loser)*100,1)) + '% Win Rate')
    print('Avg Win - ' + str(round(sumwins/winner,3)))
    print('Avg Days - ' + str(round(sumdays/winner,1)))
    print('Avg Percent of High - ' + str(round(percenthigh/winner,1)) + '%')
    print('Next Day % Winners - ' + str(round(nextdaywin/(winner+loser)*100,1)) + '%')
    print('Average Next Day Winnings - $' + str(round(nextdaywinnings/nextdaywin,3)))
    print('Average Next Day Drawdown - $' + str(round(nextdaydrawdown/nextdaywin,3)))
    print('Max Next Day Drawdown - ' + str(round(maxdrawdown,2)))
except:
    True



## An "interface" to matplotlib.axes.Axes.hist() method
#d = np.asarray(high)
#n, bins, patches = plt.hist(x=d, bins='auto', color='#0504aa',
#                            alpha=0.7, rwidth=0.85)
#binpercent = (n/sum(n)).cumsum()*100
#plt.grid(axis='y', alpha=0.75)
#plt.xlabel('$0.01 gain')
#plt.ylabel('Frequency')
#plt.title('Histogram of Jul Corn Rising Thru Up Year Low')
#maxfreq = n.max()
## Set a clean upper y-axis limit.
#plt.ylim(ymax=np.ceil(maxfreq / 10) * 10 if maxfreq % 10 else maxfreq + 10)
#
#fig, ax = plt.subplots()
#ax.bar(bins[1:], n, color="C0")
#ax2 = ax.twinx()
#ax2.plot(bins[1:], binpercent, color="C1", marker="D", ms=7)
#ax2.yaxis.set_major_formatter(PercentFormatter())
## Set the ticks to be at the edges of the bins.
#ax2.set_xticks(bins)
#ax.set(ylabel = 'Frequency')
#ax.set(xlabel = '$0.01 gain')
#ax.set_title('Pareto of Jul Corn Rising Thru Best Fit High')
#
#ax.tick_params(axis="y", colors="C0")
#ax2.tick_params(axis="y", colors="C1")
#plt.show()

cur.close()