from urllib.parse import urlparse
import pymysql.cursors
import sys
from datetime import datetime as dt


def Init():
    '''
    url = urlparse('mysql://root:root@localhost:3306/koheso_bookpad')

    conn = pymysql.connector.connect(
        host = url.hostname or 'localhost',
        port = url.port or 3306,
        user = url.username or 'root',
        password = url.password or '',
        database = url.path[1:],
    )
    '''
    conn = pymysql.connect(host='localhost',
                    user='root',
                    password='root',
                    db='koheso_bookpad',
                    charset='utf8mb4',
                    # cursorclassを指定することで
                    # Select結果をtupleではなくdictionaryで受け取れる
                    cursorclass=pymysql.cursors.DictCursor)

    


    return conn

def setMatchList(match_list):

    conn = Init()
    
    for match in match_list:
        print(match['xeid'])
        
        
        # SQLを実行する
        with conn.cursor() as cursor:

            #存在チェック
            sql ='SELECT id FROM matches WHERE xeid = %s'
            cursor.execute(sql, (match['xeid']))
            count = len(cursor.fetchall())
            if count == 0:

                sql = "INSERT IGNORE INTO matches ( \
                    sports,xeid,year,season,match_date, \
                    country,league,match_time,home_team, \
                    away_team,home_total_score,away_total_score,home_first_half_score,away_first_half_score, \
                    home_second_half_score,away_second_half_score,home_odds,x_odds,away_odds, \
                    bs,link,created,modified \
                ) VALUES (\
                %s,%s,%s,%s,%s, \
                %s,%s,%s,%s, \
                %s,%s,%s,%s,%s, \
                %s,%s,%s,%s,%s, \
                %s,%s,%s,%s \
                )"
                cursor.execute(sql, (
                    match['sports'],match['xeid'],match['year'],match['season'],match['match_date'], 
                    match['country'],match['league'],match['match_time'],match['home_team'], 
                    match['away_team'],match['home_total_score'],match['away_total_score'],match['home_first_half_score'],match['away_first_half_score'], 
                    match['home_second_half_score'],match['away_second_half_score'],match['home_odds'],match['x_odds'],match['away_odds'], 
                    match['bs'],match['link'],dt.now(),dt.now()
                ))

                conn.commit()

def checkMatchList():

    conn = Init()

    # SQLを実行する
    with conn.cursor() as cursor:

        #試合詳細を保存していない試合を抽出
        sql ='SELECT id,xeid,link FROM matches WHERE home_first_half_score = %s'
        cursor.execute(sql, (-1))

        # Select結果を取り出す
        results = cursor.fetchall()

        return results

        #for row in results:
        #    print(row)
        #    # => {'name': 'Cookie', 'id': 3}

def updateMatchDetail(match_dict):

    conn = Init()

    # SQLを実行する
    with conn.cursor() as cursor:

        #試合詳細を保存していない試合を抽出
        sql ="UPDATE matches SET \
                home_total_score = %s, \
                away_total_score = %s, \
                home_first_half_score = %s, \
                away_first_half_score = %s, \
                home_second_half_score = %s, \
                away_second_half_score = %s, \
                home_odds = %s, \
                x_odds = %s, \
                away_odds = %s, \
                modified = %s \
            WHERE id = %s"
            
            
        cursor.execute(sql, (
            match_dict['home_total_score'],
            match_dict['away_total_score'],
            match_dict['home_first_half_score'],
            match_dict['away_first_half_score'],
            match_dict['home_second_half_score'],
            match_dict['away_second_half_score'],
            match_dict['home_odds'],
            match_dict['x_odds'],
            match_dict['away_odds'],
            dt.now(),
            match_dict['id'],
        ))

        conn.commit()

if __name__ == '__main__':

    results = checkMatchList()

    for row in results:
        print(row['link'])

'''
    conn = Init()

    match_list = []
    match_dict = {}
    match_dict['sports'] = 'sports_name'
    match_dict['xeid'] = 'xeid'
    match_dict['year'] = 2017
    match_dict['season'] = 'compention'
    match_dict['match_date'] = 'match_date'

    match_dict['country'] = 'country'
    match_dict['league'] = 'compention'
    match_dict['match_time'] = 'match_time'
    match_dict['match_name'] = 'match_name'
    match_dict['home_team'] = 'home_team'

    match_dict['away_team'] = 'away_team'
    match_dict['home_total_score'] = 0
    match_dict['away_total_score'] = 0
    match_dict['home_first_half_score'] = 0
    match_dict['away_first_half_score'] = 0

    match_dict['home_second_half_score'] = 0
    match_dict['away_second_half_score'] = 0
    match_dict['home_odds'] = 0
    match_dict['x_odds'] = 0
    match_dict['away_odds'] = 0

    match_dict['bs'] = 0
    match_dict['link'] = ""

    match_list.append(match_dict)
    setMatchList(match_list)

'''