import mysql.connector
from __config import CONFIG


class MYSQLCONNECTOR():

    def __init__(self):
        self.host = CONFIG.databaseHost
        self.user = CONFIG.databaseUser
        self.passwd = CONFIG.databasePasswd
        self.database = CONFIG.databaseName
        self.mydb = mysql.connector.connect(host=self.host, user=self.user, passwd=self.passwd)
        self.mycursor = self.mydb.cursor(buffered=True)
        self.setDatabase()

    def setDatabase(self):
        query = "CREATE DATABASE  IF NOT EXISTS " + str(self.database) + ';'
        self.mycursor.execute(query)

        query = "USE " + str(self.database) + ';'
        self.mycursor.execute(query)

    def createExpiryDetailsTable(self):

        expiryTable = CONFIG.expiryDataTableName

        query='DROP TABLE __tableName__;'
        query = query.replace('__tableName__',str(expiryTable))
        try:
            self.mycursor.execute(query)
        except:
            pass

        query="""
            CREATE TABLE IF NOT EXISTS __tableName__
            (
                slno INT NOT NULL AUTO_INCREMENT,
                category VARCHAR(20) NOT NULL,
                expiryDate VARCHAR(10) NOT NULL,
                symbolPre VARCHAR(30) NOT NULL,
                isMonthly INT NOT NULL,
                PRIMARY KEY (slno)
            )
        """
        query = query.replace('__tableName__',str(expiryTable))
        self.mycursor.execute(query)


    def setexpiryDetails(self, category, expiryDate, symbolPre, isMonthly):
        
        tableName = CONFIG.expiryDataTableName

        fieldStr = "category, expiryDate, symbolPre, isMonthly"
        valueStr = "'" + str(category) + "', "
        valueStr = valueStr + "'" + str(expiryDate) + "', "
        valueStr = valueStr + "'" + str(symbolPre) + "', "
        valueStr = valueStr + "'" + str(isMonthly) + "'"
        query = "INSERT INTO " + tableName + "(" + fieldStr + ") values (" + valueStr + ");"

        self.mycursor.execute(query)
        self.mydb.commit()



    def createRangeDetailsTable(self):

        rangeTable = CONFIG.rangeDataTableName

        query='DROP TABLE __tableName__;'
        query = query.replace('__tableName__',str(rangeTable))
        try:
            self.mycursor.execute(query)
        except:
            pass

        query="""
            CREATE TABLE IF NOT EXISTS __tableName__
            (
                slno INT NOT NULL AUTO_INCREMENT,
                category VARCHAR(20) NOT NULL,
                stirkePrice INT NOT NULL,
                PRIMARY KEY (slno)
            )
        """
        query = query.replace('__tableName__',str(rangeTable))
        self.mycursor.execute(query)


    def setRangeDetails(self, category, stirkePrice):
        
        tableName = CONFIG.rangeDataTableName

        fieldStr = "category, stirkePrice"
        valueStr = "'" + str(category) + "', "
        valueStr = valueStr + "'" + str(stirkePrice) + "'"
        query = "INSERT INTO " + tableName + "(" + fieldStr + ") values (" + valueStr + ");"

        self.mycursor.execute(query)
        self.mydb.commit()

    def createDataTable(self):

        dataTable = CONFIG.dataTableName
        curDate = CONFIG.formatted_date

        query = "delete from " + str(dataTable) + " where dateToday='" + str(curDate) + "';"
        try:
            self.mycursor.execute(query)
        except:
            pass

        query="""
            CREATE TABLE IF NOT EXISTS __tableName__
            (
                dateToday VARCHAR(25) NOT NULL,
                category VARCHAR(20) NOT NULL,
                optionType VARCHAR(2) NOT NULL,
                expiryDate VARCHAR(10) NOT NULL,
                stirkePrice INT NOT NULL,
                symbol VARCHAR(50) NOT NULL,
                price DECIMAL(10, 2),
                PRIMARY KEY (dateToday, symbol)
            )
        """
        query = query.replace('__tableName__',str(dataTable))
        self.mycursor.execute(query)

    def createMailTable(self, dropTableAndCreate=False):

        mailTable = CONFIG.mailTableName

        if dropTableAndCreate:
            query = "DROP TABLE " + str(mailTable) + ";"
            try:
                self.mycursor.execute(query)
            except:
                pass

        query="""
            CREATE TABLE IF NOT EXISTS __tableName__
            (
                identifier VARCHAR(100) NOT NULL,
                entry_time_utc TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (identifier)
            )
        """
        query = query.replace('__tableName__',str(mailTable))
        self.mycursor.execute(query)

    def addIdentifierToMailTable(self, identifier):
        
        query = "INSERT INTO " + CONFIG.mailTableName + "( identifier) values ('" + identifier + "');"
        self.mycursor.execute(query)
        self.mydb.commit()

    def setDataValueWithoutPrice(self, category, optionType, expiryDate, stirkePrice, symbol):
        
        tableName = CONFIG.dataTableName
        dateToday = CONFIG.formatted_date

        fieldStr = "dateToday, category, optionType, expiryDate, stirkePrice, symbol"
        valueStr = "'" + str(dateToday) + "', "
        valueStr = valueStr + "'" + str(category) + "', "
        valueStr = valueStr + "'" + str(optionType) + "', "
        valueStr = valueStr + "'" + str(expiryDate) + "', "
        valueStr = valueStr + "'" + str(stirkePrice) + "', "
        valueStr = valueStr + "'" + str(symbol) + "'"
        query = "INSERT INTO " + tableName + "(" + fieldStr + ") values (" + valueStr + ");"

        self.mycursor.execute(query)
        self.mydb.commit()

    def getEmptyDataRows(self, category):

        tableName = CONFIG.dataTableName
        dateToday = CONFIG.formatted_date

        query = 'select  symbol from ' + str(tableName) + ' where dateToday="' + str(dateToday) + '" and category="' + str(category) + '"  and price IS NULL;'
        self.mycursor.execute(query)
        valueList = self.mycursor.fetchall()
        return valueList
    

    def setPriceDataRow(self, category, symbol, price):

        tableName = CONFIG.dataTableName
        dateToday = CONFIG.formatted_date

        query = 'update ' + str(tableName) + ' set price ="' + str(price) + '" where dateToday="' + str(dateToday) + '" and category="' + str(category) + '" and symbol="' + str(symbol) + '";'
        self.mycursor.execute(query)
        self.mydb.commit()

    def getExpiryDetails(self, category):
        
        tableName = CONFIG.expiryDataTableName
        query = 'select expiryDate,symbolPre,isMonthly from ' + str(tableName) + ' where category="' + str(category) + '";'
        self.mycursor.execute(query)
        valueList = self.mycursor.fetchall()
        return valueList
    
    def getStrikePriceDetails(self, category):
        
        tableName = CONFIG.rangeDataTableName
        query = 'select stirkePrice from ' + str(tableName) + ' where category="' + str(category) + '";'
        self.mycursor.execute(query)
        valueList = self.mycursor.fetchall()
        return valueList
    
    def getOptionPriceDetails(self, symbol, dateToday=CONFIG.formatted_date):

        tableName = CONFIG.dataTableName

        query = 'select price from ' + str(tableName) + ' where dateToday="' + str(dateToday) + '" and symbol="' + str(symbol) + '";'
        self.mycursor.execute(query)
        price = self.mycursor.fetchone()[0]
        return price
    

    def createHistoryTable(self):

        historyTable = CONFIG.historyTableName

        query="""
            CREATE TABLE IF NOT EXISTS __tableName__
            (
                dateToday VARCHAR(25) NOT NULL,
                category VARCHAR(20) NOT NULL,
                open DECIMAL(10, 2) NOT NULL,
                high DECIMAL(10, 2) NOT NULL,
                low DECIMAL(10, 2) NOT NULL,
                closePrevious DECIMAL(10, 2) NOT NULL,
                curPrice DECIMAL(10, 2) NOT NULL,
                PRIMARY KEY (dateToday, category)
            )
        """
        query = query.replace('__tableName__',str(historyTable))
        self.mycursor.execute(query)

    def setHistoryDataValue(self, dateToday, category, _open, _high, _low, closePrev, curPrice):
        
        tableName = CONFIG.historyTableName

        fieldStr = "dateToday, category, open, high, low, closePrevious, curPrice"
        valueStr = "'" + str(dateToday) + "', "
        valueStr = valueStr + "'" + str(category) + "', "
        valueStr = valueStr + "'" + str(_open) + "', "
        valueStr = valueStr + "'" + str(_high) + "', "
        valueStr = valueStr + "'" + str(_low) + "', "
        valueStr = valueStr + "'" + str(closePrev) + "', "
        valueStr = valueStr + "'" + str(curPrice) + "'"
        query = "INSERT INTO " + tableName + "(" + fieldStr + ") values (" + valueStr + ");"

        self.mycursor.execute(query)
        self.mydb.commit()

    def setHistoryDataValueForcefully(self, dateToday, category, _open, _high, _low, closePrev, curPrice):
        
        tableName = CONFIG.historyTableName

        query = 'delete from ' + str(tableName) + ' where category="' + str(category) + '" and dateToday="' + str(dateToday) + '";'
        try:
            self.mycursor.execute(query)
        except:
            pass

        fieldStr = "dateToday, category, open, high, low, closePrevious, curPrice"
        valueStr = "'" + str(dateToday) + "', "
        valueStr = valueStr + "'" + str(category) + "', "
        valueStr = valueStr + "'" + str(_open) + "', "
        valueStr = valueStr + "'" + str(_high) + "', "
        valueStr = valueStr + "'" + str(_low) + "', "
        valueStr = valueStr + "'" + str(closePrev) + "', "
        valueStr = valueStr + "'" + str(curPrice) + "'"
        query = "INSERT INTO " + tableName + "(" + fieldStr + ") values (" + valueStr + ");"

        self.mycursor.execute(query)
        self.mydb.commit()

    def getHistoricalDateDetails(self, category):
        
        tableName = CONFIG.dataTableName
        query = 'select DISTINCT(dateToday) from ' + str(tableName) + ' where category="' + str(category) + '" ORDER BY STR_TO_DATE(dateToday, "%d%b%Y-%a") DESC;'
        self.mycursor.execute(query)
        valueList = self.mycursor.fetchall()
        return valueList
    
    def getHistoryDataValue(self, category, dateToday):

        tableName = CONFIG.historyTableName
        query = 'select * from ' + str(tableName) + ' where category="' + str(category) + '" and dateToday="' + str(dateToday) + '";'
        self.mycursor.execute(query)
        valueList = self.mycursor.fetchall()
        return valueList
    

    def getHistoricalExpiryDetails(self, category, dateToday):
        
        tableName = CONFIG.dataTableName
        query = 'select distinct(expiryDate) from ' + str(tableName) + ' where category="' + str(category) + '" and dateToday="' + str(dateToday) + '" ORDER BY STR_TO_DATE(expiryDate, "%d%b%Y");'
        self.mycursor.execute(query)
        valueList = self.mycursor.fetchall()
        return valueList
    

    def getHistoricalFirstMatchSymbolDetails(self, category, dateToday, expiryDate):
        
        tableName = CONFIG.dataTableName
        query = 'select symbol,stirkePrice,optionType from ' + str(tableName) + ' where category="' + str(category) + '" and dateToday="' + str(dateToday) + '" and expiryDate="' + str(expiryDate) + '" limit 1;'
        self.mycursor.execute(query)
        valueList = self.mycursor.fetchall()[0]
        return valueList
    

    def getHistoricalStrikePriceDetails(self, category, dateToday):
        
        tableName = CONFIG.dataTableName
        query = 'select distinct(stirkePrice) from ' + str(tableName) + ' where category="' + str(category) + '" and dateToday="' + str(dateToday) + '";'
        self.mycursor.execute(query)
        valueList = self.mycursor.fetchall()
        return valueList