import os
import pandas as pd
import numpy as np
import pandas.io.data as web
import datetime
import math
import sys

from pandas.io import sql

import MySQLdb
from audioop import avg

from app.processor.DBParameter import *

class ImpliedVolatilityIndexProcessor:
    
    # BMHS
    # {0}: Ticker; {1}: Date
    HIGHER_STRIKE_BACK_EXPIRY = """
select root, Underlying_Price, Strike, Type, Expiry_Days, IV, Bid_IV, Ask_IV from IV_DB.IV where
Quote_Time like '{1}%' and 
root = '{0}' and Expiry_Days =
(select min(a.Expiry_Days) from
(select Expiry_Days, Strike from IV_DB.IV where
Quote_Time like '{1}%' and 
root = '{0}' and 
Strike > Underlying_Price and
Expiry_Days >= 30 and
Strike > Underlying_Price
order by Expiry_Days) as a) and 
Strike =
-- find strike for BMHS
(select min(a.Strike) from
(select Expiry_Days, Strike from IV_DB.IV where
Quote_Time like '{1}%' and 
root = '{0}' and 
Strike > Underlying_Price and
Expiry_Days =
(select min(a.Expiry_Days) from
(select Expiry_Days, Strike from IV_DB.IV where
Quote_Time like '{1}%' and 
root = '{0}' and 
Strike > Underlying_Price and
Expiry_Days >= 30 and
Strike > Underlying_Price
order by Expiry_Days) as a) and
Strike > Underlying_Price
order by Expiry_Days) as a)
"""

    #FMHS
    HIGHER_STRIKE_FRONT_EXPIRY = """
select root, Underlying_Price, Strike, Type, Expiry_Days, IV, Bid_IV, Ask_IV from IV_DB.IV where
Quote_Time like '{1}%' and 
root = '{0}' and Expiry_Days =
(select max(a.Expiry_Days) from
(select Expiry_Days, Strike from IV_DB.IV where
Quote_Time like '{1}%' and 
root = '{0}' and 
Strike > Underlying_Price and
Expiry_Days <= 30 and
Strike > Underlying_Price
order by Expiry_Days) as a) and 
Strike =
-- find strike for FMHS
(select min(a.Strike) from
(select Expiry_Days, Strike from IV_DB.IV where
Quote_Time like '{1}%' and 
root = '{0}' and 
Strike > Underlying_Price and
Expiry_Days =
(select max(a.Expiry_Days) from
(select Expiry_Days, Strike from IV_DB.IV where
Quote_Time like '{1}%' and 
root = '{0}' and 
Strike > Underlying_Price and
Expiry_Days <= 30 and
Strike > Underlying_Price
order by Expiry_Days) as a) and
Strike > Underlying_Price
order by Expiry_Days) as a)
"""

    #BMLS
    LOWER_STRIKE_BACK_EXPIRY = """
select root, Underlying_Price, Strike, Type, Expiry_Days, IV, Bid_IV, Ask_IV from IV_DB.IV where
Quote_Time like '{1}%' and 
root = '{0}' and Expiry_Days =
(select min(a.Expiry_Days) from
(select Expiry_Days, Strike from IV_DB.IV where
Quote_Time like '{1}%' and 
root = '{0}' and 
Strike < Underlying_Price and
Expiry_Days >= 30 and
Strike < Underlying_Price
order by Expiry_Days) as a) and 
Strike =
-- find strike for BMLS
(select max(a.Strike) from
(select Expiry_Days, Strike from IV_DB.IV where
Quote_Time like '{1}%' and 
root = '{0}' and 
Strike < Underlying_Price and
Expiry_Days =
(select min(a.Expiry_Days) from
(select Expiry_Days, Strike from IV_DB.IV where
Quote_Time like '{1}%' and 
root = '{0}' and 
Strike < Underlying_Price and
Expiry_Days >= 30 and
Strike < Underlying_Price
order by Expiry_Days) as a) and
Strike < Underlying_Price
order by Expiry_Days) as a)
"""

    #FMLS
    LOWER_STRIKE_FRONT_EXPIRY = """
select root, Underlying_Price, Strike, Type, Expiry_Days, IV, Bid_IV, Ask_IV from IV_DB.IV where
Quote_Time like '{1}%' and 
root = '{0}' and Expiry_Days =
(select max(a.Expiry_Days) from
(select Expiry_Days, Strike from IV_DB.IV where
Quote_Time like '{1}%' and 
root = '{0}' and 
Strike < Underlying_Price and
Expiry_Days <= 30 and
Strike < Underlying_Price
order by Expiry_Days) as a) and 
Strike =
-- find strike for FMLS
(select max(a.Strike) from
(select Expiry_Days, Strike from IV_DB.IV where
Quote_Time like '{1}%' and 
root = '{0}' and 
Strike < Underlying_Price and
Expiry_Days =
(select max(a.Expiry_Days) from
(select Expiry_Days, Strike from IV_DB.IV where
Quote_Time like '{1}%' and 
root = '{0}' and 
Strike < Underlying_Price and
Expiry_Days <= 30 and
Strike < Underlying_Price
order by Expiry_Days) as a) and
Strike < Underlying_Price
order by Expiry_Days) as a)
"""


    @staticmethod
    def calculate(ticker, dateStr):
        vBMHS = ImpliedVolatilityIndexProcessor.readFromMySQL(ImpliedVolatilityIndexProcessor.HIGHER_STRIKE_BACK_EXPIRY, (ticker, dateStr))
        vFMHS = ImpliedVolatilityIndexProcessor.readFromMySQL(ImpliedVolatilityIndexProcessor.HIGHER_STRIKE_FRONT_EXPIRY, (ticker, dateStr))
        vBMLS = ImpliedVolatilityIndexProcessor.readFromMySQL(ImpliedVolatilityIndexProcessor.LOWER_STRIKE_BACK_EXPIRY, (ticker, dateStr))
        vFMLS = ImpliedVolatilityIndexProcessor.readFromMySQL(ImpliedVolatilityIndexProcessor.LOWER_STRIKE_FRONT_EXPIRY, (ticker, dateStr))
        
        #print vFMHS["valueIV"], vFMLS["valueIV"], vBMHS["valueIV"], vBMLS["valueIV"]
        #print vFMHS["expiryDays"], vFMLS["expiryDays"], vBMHS["expiryDays"], vBMLS["expiryDays"]
        
        bitFlag = ImpliedVolatilityIndexProcessor.setBitFlag(vBMHS["valueIV"], vFMHS["valueIV"], vBMLS["valueIV"], vFMLS["valueIV"])
        #print bitFlag
        
        avgHSValue = ImpliedVolatilityIndexProcessor.calExpiryLevel(vFMHS, vBMHS)
        avgLSValue = ImpliedVolatilityIndexProcessor.calExpiryLevel(vFMLS, vBMLS)
        
        avgHS = avgHSValue["avgValue"]
        avgLS = avgLSValue["avgValue"]
        #print avgHS, avgLS
        
        if avgHS > 0 and avgLS > 0:
            if avgHSValue["strike"] == avgLSValue["strike"]:
                indexIV = avgLS
            else:
                indexIV = avgLS + (avgHS - avgLS) * ((avgHSValue["stockPrice"] - avgLSValue["strike"]) / (avgHSValue["strike"] - avgLSValue["strike"]))
        elif avgHS > 0:
            indexIV = avgHS
        elif avgLS > 0:
            indexIV = avgLS
        else:
            indexIV = -1
        
        dfcolumns = ["Ticker", 'QuoteDate', 'UnderlyingPrice', 'IVIndexValue', 'bitFlag']
        theResult = pd.DataFrame([[ticker, dateStr, vFMHS["stockPrice"], indexIV, bitFlag]], columns=dfcolumns)
        theResult.set_index(['Ticker', 'QuoteDate'], inplace=True)
        
        DBParameter.runSQLByMySQL("DELETE FROM IV_Index WHERE ticker='" + ticker + "' AND quoteDate='" + dateStr + "'")
        ImpliedVolatilityIndexProcessor.saveToMySQL(theResult)

        return indexIV

    
    @staticmethod
    def setBitFlag(vBMHS, vFMHS, vBMLS, vFMLS):
        bitFlag = 0;
        if vBMHS > 0: bitFlag |= 2**3 
        if vFMHS > 0: bitFlag |= 2**2 
        if vBMLS > 0: bitFlag |= 2**1 
        if vFMLS > 0: bitFlag |= 2**0 
        
        return bitFlag

    @staticmethod
    def calExpiryLevel(vFM, vBM):
        avg = -1
        avg30 = -1
        stockPrice = 0
        strike = 0
        
        if vFM["valueIV"] > 0 and vBM["valueIV"] > 0:
            if vFM["expiryDays"] == vBM["expiryDays"]:
                avg = vFM["valueIV"]
                avg30 = vFM["valueIV"]
                stockPrice = vFM["stockPrice"]
                strike = vFM["strike"]
            else:
                avg = 0.5 * (vFM["valueIV"] + vBM["valueIV"])
                avg30 = 0.5 * (vFM["valueIV"] * math.sqrt(30. / vFM["expiryDays"]) + vBM["valueIV"] * math.sqrt(30. / vBM["expiryDays"]))
                stockPrice = vFM["stockPrice"]
                strike = 0.5 * (vFM["strike"] + vBM["strike"])
        elif vFM["valueIV"] > 0:
            avg = vFM["valueIV"]
            avg30 = vFM["valueIV"]
            stockPrice = vFM["stockPrice"]
            strike = vFM["strike"]
        elif vBM["valueIV"] > 0:
            avg = vBM["valueIV"]
            avg30 = vBM["valueIV"]
            stockPrice = vBM["stockPrice"]
            strike = vBM["strike"]
        else:
            avg = -1
            avg30 = -1
        
        rtnValue = {}
        rtnValue["avgValue"] = 0.5 * (avg + avg30)
        rtnValue["stockPrice"] = stockPrice
        rtnValue["strike"] = strike
            
        return rtnValue

    
    @staticmethod
    def saveToMySQL(theResult):
        #db = MySQLdb.connect("zte.ca","root","SCxU2QnaD4","IV_DB")
        db = MySQLdb.connect(DBParameter.HOST_NAME, DBParameter.USER_NAME, DBParameter.PASSWORD, DBParameter.DB_NAME)
        #cursor = db.cursor()

        #theResult.to_sql(name='IV_Index', con=db, if_exists = 'replace', index=True, flavor='mysql')
        theResult.to_sql(name='IV_Index', con=db, if_exists = 'append', index=True, flavor='mysql')

        db.close()
        
    # parameters: ("SPY", "2016-03-04")
    @staticmethod
    def readFromMySQL(sqlStr, parameters):
        #conn = MySQLdb.connect("zte.ca","root","SCxU2QnaD4","IV_DB")
        conn = MySQLdb.connect(DBParameter.HOST_NAME, DBParameter.USER_NAME, DBParameter.PASSWORD, DBParameter.DB_NAME)
        
        stockPrice = 0.
        strike = 0.
        expiryDays = 0
        
        totalValue = 0.
        totalCount = 0
        with conn: 
            cur = conn.cursor(MySQLdb.cursors.DictCursor)
            
            formatSQL = sqlStr.format(parameters[0], parameters[1])
            cur.execute(formatSQL)

            rows = cur.fetchall()
            
            for row in rows:
                #print row["root"], row["Underlying_Price"], row["Strike"], row["Type"], row["Expiry_Days"], row["IV"], row["Bid_IV"], row["Ask_IV"]
                
                stockPrice = row["Underlying_Price"]
                strike = row["Strike"]
                expiryDays = row["Expiry_Days"]

                bidIV = row["Bid_IV"]
                askIV = row["Ask_IV"]
                
                if bidIV > 0 and askIV > 0:
                    totalValue += (bidIV + askIV) / 2.
                    totalCount += 1
                elif bidIV > 0:
                    totalValue += bidIV
                    totalCount += 1
                elif askIV > 0:
                    totalValue += askIV
                    totalCount += 1
        
        conn.close()
            
        if totalCount > 0:
            totalValue = totalValue / totalCount
        else:
            totalValue = -1.
        
        returnData = {}
        returnData["valueIV"] = totalValue
        returnData["stockPrice"] = stockPrice
        returnData["strike"] = strike
        returnData["expiryDays"] = expiryDays
        
        return returnData