# coding=utf-8
__author__ = 'Willow'
import mysql.connector
import threading
import csv
import re
from datetime import datetime
import jaro
import itertools


class DutchMatcher:

    def __init__(self):
        self.dbhost = 'localhost'
        self.dbuser = 'root'
        self.dbpass = 'ASDFqwer!@#$1234'
        self.dbpass = ''
        self.dbname = 'oddmatcher'

        self.dbhost = '138.201.105.247'
        self.dbuser = 'willow'
        self.dbpass = 'ASDFqwer!@#$1234'
        #self.dbpass = ''
        self.dbname = 'oddmatcher'

        self.cnx_object = mysql.connector.connect(user=self.dbuser, password=self.dbpass, host=self.dbhost, database=self.dbname)

        #Last Id
        self.last_b_id = 0
        self.last_e_id = 0
        self.getLastIds()

        self.runnerTypes = {1: {
                               1: ["1", "X", "2"],
                               2: ["goal", "nogoal"],
                               3: [
                                   ["under15", "over15"],
                                   ["under25", "over25"],
                                   ["under35", "over35"],
                                   ["under45", "over45"]
                                   ]},
                            2: {4: ["1", "2"]},
                            3: {5: ["1", "2"]}
                          }

    def getLastIds(self):
        cursor = self.cnx_object.cursor(buffered=True)
        sql ='SELECT last_b_id, last_e_id FROM adm_config WHERE id = 1'
        cursor.execute(sql)
        row = cursor.fetchone()
        if row != None:
            self.last_b_id = row[0]
            self.last_e_id = row[1]

    def start(self):
        cursor = self.cnx_object.cursor(buffered=True)
        # 새로 추가된 매치들의 exchange_event 들을 얻는다
        sql = "SELECT DISTINCT(exchange_event) FROM match_result WHERE exchange_event IN (SELECT DISTINCT(exchange_event) FROM match_result WHERE bookmaker_event>%s) AND exchange_event>%s" % (self.last_b_id, self.last_e_id)
        cursor.execute(sql)
        rows = cursor.fetchall()
        for row in rows:
            # 매 exchange_event 에 관한 bookmaker_event 들을 얻는다.
            sql = "SELECT DISTINCT(bookmaker_event) FROM match_result WHERE exchange_event=%s" % (row[0])
            cursor.execute(sql)
            subrows = cursor.fetchall()
            # 같은 exchange_event 에 대응하는 bookmaker_event 들이 3개 이상이면 그것들의 조합을 계산하고 매 조합에 관해서 순렬을 계산한다.
            if cursor.rowcount >=3:
                # 조합생성
                results = itertools.combinations(subrows, 3)
                for x in list(results):
                    # 순렬 생성
                    for y in list(itertools.permutations(x)):
                        ids = []
                        logRows = []
                        for z in y:
                            ids.append(z[0])
                            sql = "SELECT * FROM match_result WHERE bookmaker_event=%s AND exchange_event=%s LIMIT 1" % (z[0], row[0])
                            cursor.execute(sql)
                            logRows.append(cursor.fetchone())
                        self.insertMatch(ids, logRows)
        cursor.close()

    def insertMatch(self, bookmakerIds, logRows):
        cursor = self.cnx_object.cursor(buffered=True)
        try:
            # 세개의 행들중에서 같은 bookmaker에 해당한 이벤트들이 존재하면 귀한한다.
            if logRows[0][6] == logRows[1][6] or logRows[1][6] == logRows[2][6] or logRows[0][6] == logRows[2][6]:
                return
            sql = "SELECT * FROM `match_dutch_result` WHERE bookmaker1_event=%s AND bookmaker2_event = %s AND bookmaker3_event = %s" % (bookmakerIds[0], bookmakerIds[1], bookmakerIds[2])
            cursor.execute(sql)
            if cursor.rowcount < 1:
                sql = ("INSERT INTO `match_dutch_result` "
                       "(`datetime`, "
                       "`sport`, "
                       "`eventname`,  "
                       "`bookmaker1`, "
                       "`bookmaker1_event`, "
                       "`bookmaker1_active`, "
                       "`bookmaker2`, "
                       "`bookmaker2_event`, "
                       "`bookmaker2_active`, "
                       "`bookmaker3`, "
                       "`bookmaker3_event`, "
                       "`bookmaker3_active`, "
                       "`market` ,"
                       "`runner1`, "
                       "`runner2`, "
                       "`runner3`, "
                       "`logid`) "
                       "VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
                       )
                insertData = []
                for key in dict(self.runnerTypes[int(logRows[0][2])]).keys():
                    item = self.runnerTypes[int(logRows[0][2])][key]
                    if key != 3:
                        item3 = ""
                        if len(item)>=3:
                            item3 = item[2]
                        insertData.append((logRows[0][1], logRows[0][2], logRows[0][3],
                                           logRows[0][6], logRows[0][8], logRows[0][9],
                                           logRows[1][6], logRows[1][8], logRows[1][9],
                                           logRows[2][6], logRows[2][8], logRows[2][9],
                                           key, item[0], item[1], item3, logRows[0][19]
                                           ))
                    else:
                        for subitem in item:
                            insertData.append((logRows[0][1], logRows[0][2], logRows[0][3],
                                               logRows[0][6], logRows[0][8], logRows[0][9],
                                               logRows[1][6], logRows[1][8], logRows[1][9],
                                               logRows[2][6], logRows[2][8], logRows[2][9],
                                               key, subitem[0], subitem[1], None, logRows[0][19]
                                               ))
                cursor.executemany(sql, insertData)
                self.cnx_object.commit()
        except Exception as e:
            print(str(e))
        cursor.close()