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

@author: cpace
"""

from Modules.dataseries import dataseries
from Modules.pnp import pnp
import numpy as np
import matplotlib.pyplot as plt
import MySQLdb
import datetime
from matplotlib.ticker import PercentFormatter

crop = 'Gasoline'
month = 'Aug'
pnpline = 'downhigh'
totalyearsincluded = 15
final = []
d = []
high = []
loser = 0
winner = 0
sumwins = 0
sumdays = 0
percenthigh = 0
nextdaywin = 0
nextdaywinnings = 0
nextdaydrawdown = 0
maxdrawdown = 0
days = []

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):
    count = 1
    looking = True
    df = dataseries(crop, month, year).dataseries
    a = pnp(crop, month, year)
    downhigh = a.downBestFitHigh
    besthigh = a.maxBestFit
    bestlow = a.minBestFit
    uplow = a.upBestFitLow
    
    if pnpline == 'downhigh':
        line = a.downBestFitHigh
    elif pnpline == 'besthigh':
        line = a.maxBestFit
    elif pnpline == 'bestlow':
        line = a.minBestFit
    elif pnpline == 'uplow':
        line = a.upBestFitLow

    
    while count < len(df)-1:
       if df.Close[count] > line >= df.Close[count-1]: #rises thru downhigh and closes above it
           abovehigh = []
           start = df.Close[count]
           startcount = count + 1
           while df.Close[startcount] > line:   #while it stays above downhigh
               abovehigh.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.High[count+1] - df.Open[count+1], df.Open[count+1] - df.Low[count+1]])
           else:
               final.append([year, abovehigh.index(max(abovehigh)) + 1, max(abovehigh)/bestlow, max(abovehigh) - start, df.High[count+1] - df.Open[count+1], df.Open[count+1] - df.Low[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])
        days.append(final[i][1])
    
    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]
        
#    if final[i][3]:
#        high.append(final[i][3])
        
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 Peak Profit - ' + str(round(sumwins/winner,3)))
    print('Avg Days to Peak Profit - ' + 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()