import MySQLdb
import datetime
from operator import itemgetter


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()


def getdollarsperincrement():
#if True:
    dollardict = {}
    sql = 'SELECT MarketCommodity.DBName, MarketCommodity.DollarsPerIncrement \
            FROM MarketCommodity'
    dbcur.execute(sql)
    out = dbcur.fetchall()
    if len(out) > 0:
        for i in range(len(out)):
            dollardict[out[i][0]] = out[i][1]
    return dollardict

def getshorttrades(crop, contract, startdate, enddate, dollars, targetprofit):
    sql = 'select \
    	MarketHistory.year, profitdata.profit, profitdata.profitdate as profitdate, min(MarketHistory.Date) as drawdowndate, (profitdata.startprice - max(MarketHistory.High))*' + str(dollars[crop]) + ' as drawdown, profitdata.profitdate - profitdata.startdate as daystoprofit, min(MarketHistory.Date) - profitdata.startdate as daystodrawdown \
    from \
    	MarketHistory, \
    	(select \
    		year, min(profit.date) as profitdate, (profit.profit) as profit, profit.startprice as startprice, profit.startdate as startdate \
    	from \
    		(select \
    			startprice.year, lowestprice.date as date, startprice.startprice as startprice, lowestprice.lowprice as lowprice, ' + str(dollars[crop]) + '*(startprice.startprice - lowestprice.lowprice) as profit, startprice.startdate as startdate \
    		from \
    			(select year, MarketHistory.Date, MarketHistory.Low as lowprice from MarketHistory where crop = "' + str(crop) + '" and contract = "' + str(contract) + '" and date > concat(year, "-", "' + str(startdate) + '") and date <= concat(year, "-", "' + str(enddate) + '")) lowestprice, \
    			(select year, min(MarketHistory.Date) as startdate, MarketHistory.Close as startprice from MarketHistory where crop = "' + str(crop) + '" and contract = "' + str(contract) + '" and date >= concat(year, "-", "' + str(startdate) + '") group by year) startprice \
    		where \
    			startprice.year = lowestprice.year) profit \
    	where \
    		(profit.profit) >= ' + str(targetprofit) + ' \
    	group by \
    		year) profitdata \
    where \
    	crop = "' + str(crop) + '" and contract = "' + str(contract) + '" and MarketHistory.Date > concat(MarketHistory.year, "-", "' + str(startdate) + '") and MarketHistory.Date <= concat(MarketHistory.year, "-", "' + str(enddate) + '") and MarketHistory.Date <= profitdata.profitdate and MarketHistory.year = profitdata.year and MarketHistory.year >= 2004 \
    group by \
    	year'
    dbcur.execute(sql)
    out = dbcur.fetchall()
    return out

def getlongtrades(crop, contract, startdate, enddate, dollars, targetprofit):
    sql = 'select \
    	MarketHistory.year, profitdata.profit, profitdata.profitdate as profitdate, min(MarketHistory.Date) as drawdowndate, (min(MarketHistory.Low) - profitdata.startprice)*' + str(dollars[crop]) + ' as drawdown, profitdata.profitdate - profitdata.startdate as daystoprofit, min(MarketHistory.Date) - profitdata.startdate as daystodrawdown \
    from \
    	MarketHistory, \
    	(select \
    		year, min(profit.date) as profitdate, (profit.profit) as profit, profit.startprice as startprice, profit.startdate as startdate \
    	from \
    		(select \
    			startprice.year, highestprice.date as date, startprice.startprice as startprice, highestprice.highprice as highprice, ' + str(dollars[crop]) + '*(highestprice.highprice - startprice.startprice) as profit, startprice.startdate as startdate \
    		from \
    			(select year, MarketHistory.Date, MarketHistory.High as highprice from MarketHistory where crop = "' + str(crop) + '" and contract = "' + str(contract) + '" and date > concat(year, "-", "' + str(startdate) + '") and date <= concat(year, "-", "' + str(enddate) + '")) highestprice, \
    			(select year, min(MarketHistory.Date) as startdate, MarketHistory.Close as startprice from MarketHistory where crop = "' + str(crop) + '" and contract = "' + str(contract) + '" and date >= concat(year, "-", "' + str(startdate) + '") group by year) startprice \
    		where \
    			startprice.year = highestprice.year) profit \
    	where \
    		(profit.profit) >= ' + str(targetprofit) + ' \
    	group by \
    		year) profitdata \
    where \
    	crop = "' + str(crop) + '" and contract = "' + str(contract) + '" and MarketHistory.Date > concat(MarketHistory.year, "-", "' + str(startdate) + '") and MarketHistory.Date <= concat(MarketHistory.year, "-", "' + str(enddate) + '") and MarketHistory.Date <= profitdata.profitdate and MarketHistory.year = profitdata.year and MarketHistory.year >= 2004 \
    group by \
    	year'
    dbcur.execute(sql)
    out = dbcur.fetchall()
    return out

def gettradewindows(start = (str(datetime.date.today().month) + '-' + str(datetime.date.today().day))):
    sql = 'select start, end, commodity, month, position, winrate, mean from MarketTradeWindows where start = "' + str(start) + '"'
    dbcur.execute(sql)
    out = dbcur.fetchall()
    return out

dollars = getdollarsperincrement()
finalfinal = []

for starts in ['6-26', '6-27', '6-28', '7-1', '7-2', '7-3', '7-5']:
    windows = gettradewindows(start = starts)
    for window in windows:
        final = []
        startdate = window[0]
        enddate = window[1]
        crop = window[2]
        contract = window[3]
        position = window[4]
    #    crop = 'Copper'
    #    contract = 'Jul'
    #    startdate = '5-6'
    #    enddate = '5-10'
    #    position = 'short'
        
        targetprofit = 60
        
        while targetprofit < 2560:
            targetprofit += 100
            try:
                if position == 'long':
                    a = getlongtrades(crop, contract, startdate, enddate, dollars, targetprofit)
                else:
                    a = getshorttrades(crop, contract, startdate, enddate, dollars, targetprofit)
                winrate = round(len(a)/15,2)
                avgdrawdown = round(sum([pair[4] for pair in a])/len(a),0)
                maxdrawdown = round(min([pair[4] for pair in a]),0) - 60
                avgdaytoprofit = round(sum([pair[5] for pair in a])/len(a),0)
                avgdaytodrawdown = round(sum([pair[6] for pair in a])/len(a),0)
                maxdaytoprofit = round(max([pair[5] for pair in a]),0)
                maxdaytodrawdown = round(max([pair[6] for pair in a]),0)
                final.append([crop, contract, startdate, enddate, position, targetprofit, winrate*100, maxdrawdown,targetprofit*winrate+maxdrawdown*(1-winrate), avgdaytoprofit, avgdaytodrawdown, maxdaytoprofit, maxdaytodrawdown])
            except:
                continue
        
        finalfinal.append(max(final,key=itemgetter(8)))
        
            
dbcur.executemany("""insert into MarketHighestTrades (Crop, Contract, StartDate, EndDate, Position, TargetProfit, WinRate, MaxDrawdown, AvgTradeWin, AvgDayToProfit, AvgDayToDrawdown, MaxDayToProfit, MaxDayToDrawdown) values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)""", finalfinal)
db.commit()

dbcur.execute("""delete from MarketHighestTrades where winrate < 70""")
dbcur.execute("""delete from MarketHighestTrades where avgtradewin < 0""")
db.commit()