__author__ = 'Willow'
import mysql.connector
import csv

class WDatabase:

    def __init__(self):
        self.dbhost = '138.201.105.247'
        self.dbuser = 'willow'
        self.dbpass = 'ASDFqwer!@#$1234'
        self.dbname = 'oddmatcher'
        '''
        self.dbhost = 'localhost'
        self.dbuser = 'root'
        self.dbpass = 'ASDFqwer!@#$1234'
        #self.dbpass = ''
        '''
        self.cnx_object = mysql.connector.connect(user=self.dbuser, password=self.dbpass, host=self.dbhost, database=self.dbname)

    def truncateTable(self):
        cursor = self.cnx_object.cursor(buffered=True)
        sql = "TRUNCATE TABLE bookmaker_event"
        cursor.execute(sql)
        sql = "TRUNCATE TABLE exchange_event"
        cursor.execute(sql)
        sql = "TRUNCATE TABLE match_log"
        cursor.execute(sql)
        sql = "TRUNCATE TABLE match_result"
        cursor.execute(sql)
        sql = "TRUNCATE TABLE adm_report"
        cursor.execute(sql)
        sql = "UPDATE adm_config SET last_b_id=0, last_e_id=0 WHERE id=1"
        cursor.execute(sql)
        self.cnx_object.commit()
        cursor.close()

    def truncateEventTable(self, siteType, siteId):
        cursor = self.cnx_object.cursor(buffered=True)
        if siteType == 1:
            table = 'bookmaker_event'
        else:
            table = 'exchange_event'
        sql = "DELETE FROM "+table+" WHERE siteid = "+str(siteId)
        cursor.execute(sql)
        self.cnx_object.commit()
        cursor.close()

    def truncateOddTable(self, siteType, siteId):
        cursor = self.cnx_object.cursor(buffered=True)
        if siteType == 1:
            sql = "DELETE FROM bookmaker_odd WHERE eventid IN (SELECT id FROM bookmaker_event WHERE siteid="+str(siteId)+")"
        else:
            sql = "DELETE FROM exchange_odd WHERE eventid IN (SELECT id FROM exchange_event WHERE siteid="+str(siteId)+")"
        cursor.execute(sql)
        self.cnx_object.commit()
        cursor.close()

    def setUpdateFlag(self):
        try:
            cursor = self.cnx_object.cursor(buffered=True)
            sql ='UPDATE bookmaker_event SET update_flag=0 WHERE 1=1'
            cursor.execute(sql)
            sql ='UPDATE exchange_event SET update_flag=0 WHERE 1=1'
            cursor.execute(sql)
            self.cnx_object.commit()
            cursor.close()
        except Exception as e:
            print("ok")

    def deleteRemovedEvents(self):
        try:
            cursor = self.cnx_object.cursor(buffered=True)
            sql = "DELETE FROM `match_result` WHERE bookmaker_event IN (SELECT id FROM bookmaker_event WHERE update_flag=0 )"
            cursor.execute(sql)
            self.cnx_object.commit()

            sql = "DELETE FROM `match_result` WHERE exchange_event IN (SELECT id FROM exchange_event WHERE update_flag=0 )"
            cursor.execute(sql)
            self.cnx_object.commit()

            sql = "DELETE FROM `match_log` WHERE bookmaker IN (SELECT id FROM bookmaker_event WHERE update_flag=0 )"
            cursor.execute(sql)
            self.cnx_object.commit()

            sql = "DELETE FROM `match_log` WHERE exchange IN (SELECT id FROM exchange_event WHERE update_flag=0 )"
            cursor.execute(sql)
            self.cnx_object.commit()

            sql = "DELETE FROM `exchange_event` WHERE update_flag=0"
            cursor.execute(sql)
            self.cnx_object.commit()

            sql = "DELETE FROM `bookmaker_event` WHERE update_flag=0"
            cursor.execute(sql)
            self.cnx_object.commit()
            cursor.close()
        except Exception as e:
            self.log(0, "Database: deleteRemovedEvents >>exception " +str(e))
        cursor.close()

    def deleteOldEvents(self):
        try:
            cursor = self.cnx_object.cursor(buffered=True)
            #sql = "DELETE FROM `match_result` WHERE match_result.datetime < NOW() - INTERVAL 8 HOUR"
            sql = "DELETE FROM `match_result` WHERE match_result.datetime < NOW()"
            sql = "DELETE FROM match_result WHERE id IN (SELECT * FROM (SELECT id FROM match_result WHERE match_result.datetime < NOW() order by id) AS X);"
            cursor.execute(sql)
            self.cnx_object.commit()
            sql = "DELETE FROM `exchange_event` WHERE exchange_event.datetime < NOW()"
            sql = "DELETE FROM exchange_event WHERE id IN (SELECT * FROM (SELECT id FROM exchange_event WHERE exchange_event.datetime < NOW() order by id) AS X)"
            cursor.execute(sql)
            self.cnx_object.commit()
            sql = "DELETE FROM `bookmaker_event` WHERE bookmaker_event.datetime < NOW()"
            sql = "DELETE FROM bookmaker_event WHERE id IN (SELECT * FROM (SELECT id FROM bookmaker_event WHERE bookmaker_event.datetime < NOW() order by id) AS X)"
            cursor.execute(sql)
            self.cnx_object.commit()
            sql = "DELETE FROM `match_log` WHERE match_log.datetime < NOW()"
            sql = "DELETE FROM match_log WHERE id IN (SELECT * FROM (SELECT id FROM match_log WHERE match_log.datetime < NOW() order by id) AS X) "
            cursor.execute(sql)
            self.cnx_object.commit()
            cursor.close()
        except Exception as e:
            self.log(0, "Database: deleteOldEvents >>exception " +str(e))
            cursor.close()


    def insertLeagueMatch(self, siteId, sportId, name, rowId=-1):
        '''
        cursor = self.cnx_object.cursor(buffered=True)
        sql = 'SELECT * FROM cfg_league_match WHERE s_id=%s AND league="%s" AND g_id=%s' % (siteId, name, sportId)
        cursor.execute(sql)
        if cursor.rowcount < 1:
            sql = 'INSERT INTO cfg_league_match(s_id, l_id, g_id, league) VALUES (%s, %s, %s, "%s")' % (siteId, rowId, sportId, name)
            cursor.execute(sql)
            self.cnx_object.commit()
        cursor.close()
        '''
        return

    def insertLeague(self,sportId, name):
        row_no = -1
        cursor = self.cnx_object.cursor(buffered=True)
        sql = 'SELECT id FROM cfg_leagues WHERE league="%s" AND g_id=%s' % (name, sportId)
        cursor.execute(sql)
        if cursor.rowcount < 1:
            sql = ("INSERT INTO cfg_leagues"
                       "(g_id, league)"
                       "VALUES (%s, %s)")
            cursor.execute(sql , (sportId, name))
            self.cnx_object.commit()
            row_no = cursor.lastrowid
        else:
            row_no = cursor.fetchone()[0]
        return row_no
        cursor.close()

    def insertEvent(self, name, league, datetime, siteid, gametype, sitetype, eventId):
        try:
            name = str(name).replace("\\", "")
            if str(league).lower().__contains__("fantasy"):
                return
            cursor = self.cnx_object.cursor(buffered=True)
            table = 'bookmaker_event'
            if sitetype == 1:
                table = 'bookmaker_event'
            else:
                table = 'exchange_event'
            sql = 'SELECT * FROM '+table+' WHERE name="'+name+'" AND siteid = '+str(siteid) +' AND gametype = ' + str(gametype)
            cursor.execute(sql)

            if cursor.rowcount < 1:
                sql = ("INSERT INTO "+table+" "
                       "(name, league, datetime, siteid, gametype, eventId, update_flag) "
                       "VALUES (%s, %s, %s, %s, %s, %s, %s)")
                cursor.execute(sql , (name, league, datetime, siteid, gametype, eventId, 1))
                self.cnx_object.commit()
            else:
                sql ='UPDATE '+table+' SET update_flag=1 WHERE datetime="%s" AND name="%s" AND siteid = %s AND gametype = %s' % (datetime, name, siteid, gametype)
                self.cnx_object.commit()
                cursor.execute(sql)
            row_no = cursor.lastrowid
            cursor.close()
            return row_no
        except Exception as e:
            self.log(0, "Database: insertEvent >>exception " +str(e))
        cursor.close()
        return None

    '''
    description: Under / Over 4.5
    name:      : Under
    '''
    def insertOdd(self, sitetype, eventid, oddtype, description, name, odd, liquidity = 0):
        cursor1 = self.cnx_object.cursor(buffered=True)
        try:
            if sitetype == 1:
                sql ='UPDATE match_result SET backodd_update=1, backodd=%s WHERE bookmaker_event = %s AND runner="%s" AND market=%s' % (odd, eventid, name, oddtype)
                cursor1.execute(sql)
                #table = 'bookmaker_odd'
                #sql = ("INSERT INTO bookmaker_odd(eventid, oddtype, description, name, odd) VALUES (%s, %s, %s, %s, %s)")
                #cursor1.execute(sql , (eventid, oddtype, description,name, odd))
            else:
                sql ='UPDATE match_result SET layodd_update=1, layodd=%s, liquidity=%s WHERE exchange_event = %s AND runner="%s" AND market=%s' % (odd, liquidity, eventid, name, oddtype)
                cursor1.execute(sql)
            self.cnx_object.commit()
            cursor1.close()
        except Exception as e:
            self.log(0, "Database: insertOdd >>exception " +str(e))
        cursor1.close()
        return None


    def getSiteInfo(self, name):
        try:
            cursor = self.cnx_object.cursor(buffered=True)
            sql = "SELECT * FROM cfg_bookies WHERE link='"+name+"'"
            cursor.execute(sql)
            ret = cursor.fetchone()
            return ret
        except Exception as e:
            self.log(0, "Database: getSiteInfo >>exception " +str(e))
        return None

    def getSports(self):
        try:
            cursor = self.cnx_object.cursor(buffered=True)
            sql = "SELECT * FROM cfg_games"
            cursor.execute(sql)
            ret = []
            results = cursor.fetchall()
            for row in results:
                sport = dict()
                sport[row[0]] = row
                ret.append(sport)
            return ret
        except Exception as e:
            self.log(0, "Database: getSports >>exception " +str(e))
        return None


    def getOdds(self, siteid):
        try:
            cursor = self.cnx_object.cursor(buffered=True)
            sql = "SELECT * FROM cfg_parse JOIN cfg_oddtype ON cfg_parse.oddId = cfg_oddtype.id WHERE cfg_parse.siteId='" + str(siteid) +"'"
            cursor.execute(sql)
            ret = []
            results = cursor.fetchall()
            for row in results:
                temp = dict()
                temp["id"] = row[2]
                temp["pattern"] = row[3]
                temp["sportId"] = row[5]
                ret.append(temp)
            return ret
        except Exception as e:
            self.log(0, "Database: getOdds >>exception " +str(e))
        return None

    def loadSeparator(self, siteid):
        try:
            # Getting Team Separtor Letter
            cursor = self.cnx_object.cursor(buffered=True)
            sql = "SELECT * FROM cfg_team_sep WHERE siteId='"+str(siteid)+"'"
            cursor.execute(sql)
            row = cursor.fetchone()
            return row[2]
        except Exception as e:
            self.log(0, "Database: loadSeparator >>exception " +str(e))
        cursor.close()
        return None

    # Read all matched events from 'matched_event' table by siteID
    # id, name, league, datetime, siteid, gametype, eventId  (Table structure)
    def getMatchedEvents(self, siteId, siteType):
        try:
            ret = []

            cursor = self.cnx_object.cursor(buffered=True)
            if siteType == 1:
                sql = "UPDATE match_result SET backodd_update=0 WHERE bookmaker='"+str(siteId)+"'"
            else:
                sql = "UPDATE match_result SET layodd_update=0 WHERE exchange='"+str(siteId)+"'"
            cursor.execute(sql)
            self.cnx_object.commit()

            if siteType == 1:
                table_name = "bookmaker_event"
                sql = "SELECT DISTINCT(bookmaker_event) FROM match_result WHERE bookmaker='"+str(siteId)+"'"
                #sql = "SELECT DISTINCT(id) FROM bookmaker_event WHERE siteid='"+str(siteId)+"'"
            else:
                table_name = "exchange_event"
                sql = "SELECT DISTINCT(exchange_event) FROM match_result WHERE exchange='"+str(siteId)+"'"
            cursor.execute(sql)
            rows = cursor.fetchall()
            for row in rows:
                sql = 'SELECT * FROM %s WHERE id="%s" ' % (table_name, row[0])
                cursor.execute(sql)
                ret.append(cursor.fetchone())
            return ret
        except Exception as e:
            self.log(0, "Database: getMatchedEvents >>exception " +str(e))
        cursor.close()

    def updateLastIds(self):
        cursor = self.cnx_object.cursor(buffered=True)
        sql = "SELECT id FROM bookmaker_event ORDER BY id DESC LIMIT 0,1"
        cursor.execute(sql)
        row = cursor.fetchone()
        if row == None:
            b_lastId = 0
        else:
            b_lastId = row[0]
        sql = "SELECT id FROM exchange_event ORDER BY id DESC LIMIT 0,1"
        cursor.execute(sql)
        row = cursor.fetchone()
        if row == None:
            e_lastId = 0
        else:
            e_lastId = row[0]
        sql ='UPDATE adm_config SET last_b_id=%s, last_e_id=%s WHERE id = %s' % (b_lastId, e_lastId, 1)
        cursor.execute(sql)
        self.cnx_object.commit()
        cursor.close()

    def executeSQL(self, sql):
        cursor = self.cnx_object.cursor(buffered=True)
        cursor.execute(sql)
        self.cnx_object.commit()
        cursor.close()

    # Sisal, Lottomatica
    def getMatchedLeagues(self, siteId, siteType):
        try:
            ret = []
            cursor = self.cnx_object.cursor(buffered=True)
            if siteType == 1:
                table_name = "bookmaker_event";
                sql = "SELECT DISTINCT(league) FROM bookmaker_event WHERE id IN ( SELECT DISTINCT(bookmaker_event) FROM match_result WHERE bookmaker=%s)" %(siteId)
                #sql = "SELECT DISTINCT(id) FROM bookmaker_event WHERE siteid='"+str(siteId)+"'"
            else:
                table_name = "exchange_event";
                sql = "SELECT DISTINCT(league) FROM exchange_event WHERE id IN ( SELECT DISTINCT(exchange_event) FROM match_result WHERE exchange=%s)" % (siteId)
            cursor.execute(sql)
            rows = cursor.fetchall()
            for row in rows:
                if str(row[0]).__contains__('"'):
                    sql = "SELECT * FROM %s WHERE league = '%s' AND siteid=%s" % ( table_name, str(row[0]) , siteId)
                else:
                    sql = 'SELECT * FROM %s WHERE league = "%s" AND siteid=%s' % ( table_name, str(row[0]) ,siteId)
                cursor.execute(sql)
                record = cursor.fetchone()
                ret.append(record)
            return ret
        except Exception as e:
            self.log(0, "Database: getMatchedLeagues >>exception " +str(e))
        cursor.close()

    def updateEventReport(self, siteId, f_count, t_count, b_count, takeTime, endTime):
        try:
            cursor = self.cnx_object.cursor(buffered=True)
            sql ="SELECT * FROM adm_report WHERE siteId="+str(siteId)
            cursor.execute(sql)
            if cursor.rowcount < 1:
                sql = ("INSERT INTO adm_report (siteId, f_count, t_count, b_count, event_taken_time,last_event_time) VALUES (%s, %s, %s, %s, %s, %s)")
                cursor.execute(sql , (siteId, f_count, t_count,b_count, takeTime, endTime))
            else:
                sql ='UPDATE adm_report SET f_count=%s, t_count=%s, b_count=%s, event_taken_time=%s, last_event_time ="%s", e_update_count=e_update_count + 1 WHERE siteId=%s ' % (f_count, t_count, b_count, takeTime, endTime, siteId)
                cursor.execute(sql)
            self.cnx_object.commit()
        except Exception as e:
            self.log(0, "Database: updateEventReport >>exception " +str(e))
        cursor.close()

    def updateOddReport(self,siteType, siteId, m_count, odd_count, takenTime, endTime):
        cursor = self.cnx_object.cursor(buffered=True)
        try:

            sql ="SELECT * FROM adm_report WHERE siteId="+str(siteId)
            cursor.execute(sql)
            if cursor.rowcount < 1:
                sql = ("INSERT INTO adm_report (siteId, m_count, oddcount, odd_taken_time,last_odd_time) VALUES (%s, %s, %s, %s, %s)")
                cursor.execute(sql , (siteId, m_count, odd_count, takenTime, endTime))
            else:
                sql ='UPDATE adm_report SET m_count=%s, oddcount=%s, odd_taken_time=%s, last_odd_time ="%s", o_update_count=o_update_count+1 WHERE siteId=%s ' % (m_count, odd_count,takenTime, endTime, siteId)
                cursor.execute(sql)
            self.cnx_object.commit()


            if siteType == 1:
                sql = "UPDATE match_result SET backodd=0 WHERE id IN (SELECT * FROM (SELECT id FROM match_result WHERE bookmaker='%s' AND backodd_update = 0) AS X)" % (siteId)
            else:
                sql = "UPDATE match_result SET layodd=0 WHERE id IN (SELECT * FROM (SELECT id FROM match_result WHERE exchange='%s' AND layodd_update = 0) AS X)" % (siteId)

            cursor.execute(sql)
            self.cnx_object.commit()

        except Exception as e:
            self.log(0, "Database: updateOddReport >>exception " +str(e))
        cursor.close()

    def getTimerConfig(self):
        try:
            cursor = self.cnx_object.cursor(buffered=True)
            sql ='SELECT e_timer, o_timer FROM adm_config WHERE id = 1'
            cursor.execute(sql)
            row = cursor.fetchone()
            cursor.close()
            if row != None:
                return row
            else:
                return None
        except Exception as e:
            self.log(0, "Database: getTimerConfig >>exception " +str(e))
        cursor.close()
        return None


    def getActive(self, siteId):
        try:
            cursor = self.cnx_object.cursor(buffered=True)
            sql ='SELECT active FROM cfg_bookies WHERE id = ' + str(siteId)
            cursor.execute(sql)
            row = cursor.fetchone()
            cursor.close()
            return row[0]
        except Exception as e:
            self.log(0, "Database: getActive >>exception " +str(e))
        cursor.close()
        return 0

    def log(self, type, message):
        print(message)