# -*- coding: utf-8 -*-
"""
Created on Thu Feb  7 13:43:47 2019

@author: cpace
"""

import MySQLdb
import sys
import pandas as pd
from Modules import pnp as pnp
import json
import numpy as np
 
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.
cur = db.cursor()

# crop = str(sys.argv[1]) #"Soybeans"
# contractmonth = str(sys.argv[2]) #"Jul"
# contractyear = int(sys.argv[3]) #2019

crop = "Corn" 
contractmonth = "Mar"
contractyear = 2024

def dirlabel(row):
    if row['Close'] < row['Open']:
        return 'Decreasing'
    else:
        return 'Increasing'

a = pnp.pnp(crop, contractmonth, contractyear)

sql = f"select date, open, high, low, close from MarketHistory where Date between '{a.startDate}' and '{a.endDate}' and crop = '{crop}' and contract = '{contractmonth}' and year = {contractyear};"
cur.execute(sql)
data = pd.DataFrame(list(cur.fetchall()))
data.rename(columns={0:'Date', 1:'Open', 2:'High', 3:'Low', 4:'Close'}, inplace=True)
#data['Date'] = pd.to_datetime(data['Date'])
#data['Date'] = data['Date'].apply(mdates.date2num)
data['Direction'] = data.apply(lambda row: dirlabel(row),axis=1)
data['MaxBestFit'] = a.maxBestFit
data['MinBestFit'] = a.minBestFit
data['UpYearLow'] = a.upBestFitLow
data['DownYearHigh'] = a.downBestFitHigh
# data.to_csv('ohlc.csv',index=False, encoding='utf-8')
dataSeries = data[['Open', 'High', 'Low', 'Close']]


#***************** Standard Deviations ********************************

# data = [Year, Minimum Close, Maximum Close, Start Price, Stop Price]

bestfitlowmatrix = a.getminBestFit(a.data, a.data[:,3].astype(float))
bestfithighmatrix = a.getmaxBestFit(a.data, a.data[:,3].astype(float))

upyearminmatrix = a.getminBestFit(a.getupyears(a.data), a.data[:,3].astype(float))
downyearmaxmatrix = a.getmaxBestFit(a.getdownyears(a.data),a.data[:,3].astype(float))

bestfitlowdev = np.std(a.data[:,1].astype(float) - bestfitlowmatrix)
bestfithighdev = np.std(a.data[:,2].astype(float) - bestfithighmatrix)

uplowdev = np.std(a.data[:,3].astype(float) - upyearminmatrix)
downhighdev = np.std(a.data[:,3].astype(float) - downyearmaxmatrix)

bestlowplusstd = a.minBestFit + bestfitlowdev
besthighminusstd = a.maxBestFit - bestfithighdev
downhighminusstd = a.downBestFitHigh - downhighdev
uplowplusstd = a.upBestFitLow + uplowdev

out = json.dumps({'startdate' : str(a.startDate), 'enddate' : str(a.endDate), \
                  'latestdate' : str(a.latestDate), 'latestprice' : str(a.latestPrice), \
                  'startprice' : str(a.startPrice), 'bestlowplus' : str(a.minBestFit + bestfitlowdev), \
                  'besthighminus' : str(a.maxBestFit - bestfithighdev), \
                  'uplowplus' : str(a.upBestFitLow + uplowdev), \
                  'downhighminus' : str(a.downBestFitHigh - downhighdev), \
                  'data': data.to_json(orient='columns')})
print(out)

a.closeconn()