# -*- coding: utf-8 -*-
"""
Created on Wed Feb  6 15:48:44 2019

@author: cpace
"""
import MySQLdb
from scipy import stats
import numpy as np
from time import strptime
import datetime
from dateutil.relativedelta import relativedelta

pnpdb = 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.
pnpcur = pnpdb.cursor()

class pnp:
    def __init__(self, crop, month, year):
        expiration = str(strptime(month,'%b').tm_mon-1) + '-22'
        self.startDate = datetime.date(year, strptime(month,'%b').tm_mon,1) + relativedelta(months=-4)
        self.endDate = datetime.date(year, strptime(month,'%b').tm_mon,1)
#        expiration = self.getlatestdate(crop, year, month)
        self.data = self.getdata(month, crop, year, expiration, str(self.startDate))  #Year, Minimum Close, Maximum Close, Start Price, Stop Price
        self.startPrice = self.getstartprice(crop,year,self.startDate, month)
        self.maxBestFit = self.getmaxBestFit(self.data,self.startPrice)
        self.minBestFit = self.getminBestFit(self.data,self.startPrice)
        self.downBestFitHigh = self.getdownBestFitHigh(crop, month, year)
        self.upBestFitLow = self.getupBestFitLow(crop, month, year)
        self.latestDate = self.getlatestdate(crop, year, month)
        self.latestPrice = self.getlatestprice(crop, year, month, self.latestDate)
        
    
    def regress(self, x, y, curprice):
        slope, intercept, r_value, p_value, std_err = stats.linregress(x,y)
        return intercept + slope * curprice
               
    def getdata(self, contractmonth, crop, contractyear, expiration, startdate):
        sql = "select minmax.year, minmax.minimum, minmax.maximum, startclose.start, stopclose.stop " \
        "from "\
        	"(select year, min(close) as minimum, max(close) as maximum from MarketHistory where Contract = '" + str(contractmonth) + "' and Crop = '" + str(crop) + "' and Date >= (concat(year-" + str(contractyear) + "+year('" + startdate + "'),'-',month('" + startdate + "'),'-',day('" + startdate + "'))) and Date <= (concat(year,'-' , '" + str(expiration) + "')) and year >= 1900 group by year) as minmax, " \
        	"(select year, close as start, close from MarketHistory where contract = '" + str(contractmonth) + "' and crop = '" + str(crop) + "' and Date >= (concat(year-" + str(contractyear) + "+year('" + startdate + "'),'-',month('" + startdate + "'),'-',day('" + startdate + "'))) and year >= 1900 group by year order by date asc) as startclose, " \
        	"(select year, close as stop, close from MarketHistory where contract = '" + str(contractmonth) + "' and crop = '" + str(crop) + "' and Date >= (concat(year,'-' , '" + str(expiration) + "')) group by year order by date desc) as stopclose " \
        "where startclose.year = minmax.year and stopclose.year = minmax.year order by year asc;"
        print(sql)
        pnpcur.execute(sql)
        return np.asarray(list(pnpcur.fetchall()))
        #return sql
    
    def getstartprice(self, crop, year, startdate, month):
        sql = "select min(date), close from MarketHistory where crop = '" + crop + "' and year = '" + str(year) + "' and contract = '" + month + "' and date>='" + str(startdate) + "';"
        pnpcur.execute(sql)
        return np.asarray(list(pnpcur.fetchall()))[0][1]
    
    def getlatestdate(self, crop, year, month):
        sql = "select max(date) from MarketHistory where crop = '" + crop + "' and year = '" + str(year) + "' and contract = '" + month + "';"
        pnpcur.execute(sql)
        date = list(pnpcur.fetchall())[0][0]
        if date < self.endDate:
            return date
        else:
            sql = "select max(date) from MarketHistory where crop = '" + crop + "' and year = '" + str(year) + "' and contract = '" + month + "' and date <= '" + str(self.endDate) + "';"
            pnpcur.execute(sql)
            date = list(pnpcur.fetchall())[0][0]
            return date
        
    def getlatestprice(self, crop, year, month, latestdate):
        sql = "select close from MarketHistory where crop = '" + crop + "' and year = '" + str(year) + "' and contract = '" + month + "' and date = '" + str(latestdate) + "';"
        pnpcur.execute(sql)
        return np.asarray(list(pnpcur.fetchall()))[0][0]
    
    def getmaxBestFit(self, data, startprice):
        return self.regress(data[:,3].astype(float),data[:,2].astype(float),startprice)
    
    def getminBestFit(self, data, startprice):
        return self.regress(data[:,3].astype(float),data[:,1].astype(float),startprice)
    
    def getupyears(self, data):
        return data[np.where(data[:,3]<data[:,4])[0],:]
    
    def getdownyears(self, data):
        return data[np.where(data[:,3]>data[:,4])[0],:]
    
    def getdownBestFitHigh(self, crop, month, year):
        return self.getmaxBestFit(self.getdownyears(self.data),self.getstartprice(crop,year, self.startDate, month))
    
    def getupBestFitLow(self, crop, month, year):
        return self.getminBestFit(self.getupyears(self.data),self.getstartprice(crop,year, self.startDate, month))
    
    def closeconn(self):
        return pnpdb.close()

#data = [Year, Minimum Close, Maximum Close, Start Price, Stop Price]
a = pnp('Corn', 'Mar', 2024)
#data = a.data