import re
import requests
import os
import configparser
import pymysql
from bs4 import BeautifulSoup
from datetime import *

db = None
config = None
GAME_COLS = []
DRIVE_COLS = []
PLAY_COLS = []
DRIVE_STARTS = {}
GAMER = ['http://www.pro-football-reference.com/boxscores/201601100min.htm']  # for test runs
GAMES = [
    'http://www.pro-football-reference.com/boxscores/201101080sea.htm',
    'http://www.pro-football-reference.com/boxscores/201101160chi.htm',
    'http://www.pro-football-reference.com/boxscores/201301060was.htm',
    'http://www.pro-football-reference.com/boxscores/201301130atl.htm',
    'http://www.pro-football-reference.com/boxscores/201401110sea.htm',
    'http://www.pro-football-reference.com/boxscores/201401190sea.htm',
    'http://www.pro-football-reference.com/boxscores/201402020den.htm',
    'http://www.pro-football-reference.com/boxscores/201501100sea.htm',
    'http://www.pro-football-reference.com/boxscores/201501180sea.htm',
    'http://www.pro-football-reference.com/boxscores/201502010sea.htm',
    'http://www.pro-football-reference.com/boxscores/201601100min.htm',
    'http://www.pro-football-reference.com/boxscores/201601170car.htm']

# TODO: unit testing


def read_config():
    ''' config.ini stores the user cred for the DB '''
    global config
    config = configparser.ConfigParser()
    configFile = os.path.join(os.path.dirname(__file__), "config.ini")
    config.read(configFile)
    return config


def init_db():
    global db
    user = config['mysql']['user']
    pwd = config['mysql']['pwd']
    host = config['mysql']['host']
    db_name = config['mysql']['db_name']
    db = pymysql.connect(host=host, passwd=pwd, user=user, db=db_name)
    # use column names to allow for updates easily
    load_game_columns()
    load_drive_columns()
    load_play_columns()
    return db


def load_game_columns():
    cursor = db.cursor()
    cursor.execute("SHOW columns FROM game")
    for row in cursor.fetchall():
        GAME_COLS.append(row[0])


def load_drive_columns():
    cursor = db.cursor()
    cursor.execute("SHOW columns FROM drive")
    for row in cursor.fetchall()[4:]:
        DRIVE_COLS.append(row[0])


def load_play_columns():
    cursor = db.cursor()
    cursor.execute("SHOW columns FROM play")
    for row in cursor.fetchall()[5:-4]:
        PLAY_COLS.append(row[0])


def scrape_game(game_url):
    p = re.compile('.*/(.*)\.htm')
    temp_match = p.search(game_url)
    if temp_match is None:
        print "Uh oh"  # TODO: need some control here
    game_id = temp_match.group(1)  # ex. 201401190sea
    req = requests.get(game_url)
    fill_game_table(game_id, req)
    fill_drive_table(game_id, req)
    fill_play_table(game_id, req)


def find_date_from_id(game_id):
    ''' extract the YYYYMMDD from game_id ex: 201401190sea '''
    date_str = game_id[:8]
    date_obj = datetime.strptime(date_str, '%Y%m%d')
    day_of_week = format(date_obj, '%A')
    return {'gamedate': date_str, 'weekday': day_of_week}


def fill_game_table(game_id, req):
    game_dict = {}
    game_dict.update(find_date_from_id(game_id))
    game_dict.update(grab_score_info(req))
    game_dict.update(grab_game_info(game_id, req, game_dict['home_team']))
    update_db_with_dict('game', {'game_id': game_id}, game_dict)


def fill_drive_table(game_id, req):
    '''
        the <table> holding the drive info are not marked by a searchable ID or class name
        but they are only ones with a LOS field. So search for that and then backtrack up
        the DOM tree until the team and table are found
    '''
    raw_src = req.text
    soup = BeautifulSoup(raw_src, "html.parser")
    tokens = soup.find_all("th", text='LOS')
    for token in tokens:
        drive_table = None
        drive_div = None
        temp_tok = token.parent  # tr
        if temp_tok is not None:
            temp_tok = temp_tok.parent  # thead
            if temp_tok is not None:
                drive_table = temp_tok.parent  # table
                if drive_table is not None:
                    temp_tok = drive_table.parent  # div inner
                    if temp_tok is not None:
                        drive_div = temp_tok.parent  # div outer
        if drive_div is not None:
            # need to confirm which team these drives are for
            off_team = drive_div.find("h2")
            off_team = off_team.get_text().strip()
            off_team = off_team[:off_team.find('Drives')].strip()
        if drive_table is not None:
            i = 1
            for row in drive_table.find_all("tr")[1:]:
                dataset = [td for td in row.find_all("td")]
                drive_tbl_data = [td.get_text().strip() for td in dataset[1:-1]]
                drive_dict = dict(zip(DRIVE_COLS, drive_tbl_data))
                primary_dict = {'game_id': game_id, 'off_team': off_team, 'team_drive_num': str(i)}
                update_db_with_dict('drive', primary_dict, drive_dict)
                store_drive_plays(dataset, i, off_team)
                i += 1


def store_drive_plays(dataset, drive_num, drive_team):
    '''
        the play by play data has no metadata in it about the team driving so capture it here.
        the last column of each row, "View Drive" is a link to the plays in the pbp table.
        "View Drive" is a JS event specificying the first & last plays of the drive.
    '''
    td = dataset[-1]
    if td is None:
        return
    event = td.find('a')
    if not event:
        return
    event = event['onclick']
    if not event:
        return
    p = re.compile('.*?(\d+).*?(\d+).*')
    temp_match = p.search(event)
    if temp_match is None:
        print "Uh oh"  # TODO: need some control here
    first_play = temp_match.group(1)
    last_play = temp_match.group(2)
    DRIVE_STARTS[first_play] = (drive_team, drive_num, last_play)


def fill_play_table(game_id, req):
    raw_src = req.text
    soup = BeautifulSoup(raw_src, "html.parser")
    table = soup.find("table", attrs={"id": "pbp_data"})
    play_num = 1
    drive_num = 0
    drive_ends = -1
    off_team = ''
    team_drive_num = 0
    st_flag = '1'
    home_win = '50.0'
    if table:
        for row in table.find_all("tr")[1:]:
            if row.has_attr('class') and 'thead' in row['class']:
                continue  # thead rows are for change of quarter, or other non-play events.

            #  all play rows are marked with anchor following the pattern: name=pbp_INT
            #  where INT is the play number corresponding to the drive plays noted above
            play_row = row.find_all('a', href=False)
            if play_row:
                pbp_num = play_row[0]['name']
                pbp_num = pbp_num[pbp_num.find('_') + 1:]
            full_data = [td.get_text().strip() for td in row.find_all("td")]
            dataset = full_data[:-3]  # skip last 3
            if len(dataset):  # extra check for non-play heading rows
                play_dict = dict(zip(PLAY_COLS, dataset))
                if play_dict['quarter'] == 'OT':
                    play_dict['quarter'] = '5'
                new_home_win = full_data[-1]
                if new_home_win != '':  # on timeouts no WE is given in the table
                    home_win = new_home_win
                play_dict['home_win'] = home_win
                # check for change class 'pos_change' or 'is_scoring'
                if row.has_attr("class"):
                    if 'pos_change' in row['class']:
                        play_dict['cop_flag'] = '1'
                    if 'is_scoring' in row['class']:
                        play_dict['score_flag'] = '1'
                primary_dict = {'game_id': game_id, 'play_num': str(play_num)}
                if play_dict['down'] == '':  # kick off
                    play_dict['down'] = '0'
                    play_dict['togo'] = '0'
                if pbp_num in DRIVE_STARTS:  # a new drive has begun
                    (off_team, team_drive_num, drive_ends) = DRIVE_STARTS[pbp_num]
                    drive_num += 1
                    st_flag = '0'
                play_dict['drive_num'] = str(drive_num)
                play_dict['st_flag'] = st_flag
                play_dict['off_team'] = off_team
                play_dict['team_drive_num'] = str(team_drive_num)
                if pbp_num == drive_ends:
                    st_flag = '1'
                update_db_with_dict('play', primary_dict, play_dict)
                play_num += 1


def grab_game_info(game_id, req, home_team):
    raw_src = req.text
    soup = BeautifulSoup(raw_src, "html.parser")
    table = soup.find("table", attrs={"id": "game_info"})
    info_dict = {}
    if table:
        for row in table.find_all("tr")[1:]:
            # find all <td> in each <tr>, get the text, strip it, make a list
            dataset = [td.get_text().strip() for td in row.find_all("td")]
            info_dict[dataset[0]] = dataset[1]

    if len(info_dict) > 1:
        # adjust start time because West Coast is Best Coast
        temp_time = datetime.strptime(info_dict['Start Time (ET)'], '%I:%M%p')
        pac_time = temp_time + timedelta(hours=-3)
        del info_dict['Start Time (ET)']
        info_dict['start_time_pt'] = format(pac_time, '%H:%M')

        # adjust spread to reflect home team instead of favored team
        pfr_spread = info_dict['Vegas Line']
        del info_dict['Vegas Line']
        if 'Pick' in pfr_spread:
            num_spread = '0'
        else:
            num_spread = float(pfr_spread[pfr_spread.find('-'):])
            fav_team = pfr_spread[:pfr_spread.find('-')]
            if fav_team not in home_team:
                num_spread *= -1
        info_dict['spread'] = num_spread

        # adjust over_under
        pfr_ou = info_dict['Over/Under']
        del info_dict['Over/Under']
        pfr_ou = pfr_ou[:pfr_ou.find('.') + 2]
        info_dict['over_under'] = pfr_ou

        # adjust key names to match db
        for pfr_key in info_dict.keys():
            db_key = pfr_key.lower().replace(' ', '_')
            if db_key in GAME_COLS:
                info_dict[db_key] = str(info_dict.pop(pfr_key)).replace(',', '')
            else:
                del info_dict[pfr_key]
    return info_dict


def grab_score_info(req):
    raw_src = req.text
    soup = BeautifulSoup(raw_src, "html.parser")
    table = soup.find("table", attrs={"id": "linescore"})
    home_cols = ['home_team','home_1q','home_2q','home_3q','home_4q','home_ot','home_final']
    away_cols = ['away_team','away_1q','away_2q','away_3q','away_4q','away_ot','away_final']
    if table:
        trows = table.find_all("tr")
        ot_game = check_for_ot(trows[0])
        away_line = [td.get_text().strip() for td in trows[1].find_all("td")]
        home_line = [td.get_text().strip() for td in trows[2].find_all("td")]
        if len(away_line) != len(home_line):
            print "error!"  # TODO something here for check?
        home_dict = dict(zip(home_cols, home_line))
        away_dict = dict(zip(away_cols, away_line))
        if not ot_game:
            home_dict['home_final'] = home_dict.pop('home_ot')
            away_dict['away_final'] = away_dict.pop('away_ot')
        home_dict.update(away_dict)
        return home_dict
    return {}


def check_for_ot(trow):
    ''' if a game has OT then there'll be 7 columns (team name, quarter (x4), OT, Final) '''
    if len(trow.find_all("th")) == 6:
        return False
    return True


def update_db_with_dict(table, primary_dict, data_dict):
    cursor = db.cursor()
    # does primay exist?
    primary_check = ' AND '.join(["{} = '{}'".format(k, v) for k, v in primary_dict.iteritems()])
    columns = ', '.join(data_dict.keys())
    sql = "SELECT %s FROM %s WHERE %s"
    cursor.execute(sql, (columns, table, primarycheck))
    rows = cursor.fetchall()
    if len(rows) > 1:  # error!!
        return -1
    if len(rows) == 1:  # UPDATE
        up = ', '.join(["{}='{}'".format(k, v.replace("'", "")) for k, v in data_dict.iteritems()])
        sql = "UPDATE %s SET %s WHERE %s"
        if cursor.execute(sql, (table, up, primary_check)):
            cursor.close()
            db.commit()
    else:  # INSERT
        all_data_dict = data_dict.copy()
        all_data_dict.update(primary_dict)
        col_list = []
        data_list = []
        for k, v in all_data_dict.items():
            col_list.append(k)
            data_list.append(v.replace(',', '').replace("'", ""))  # trim , and '
        col_str = ', '.join(col_list)
        data_str = "'" + "', '".join(data_list) + "'"  # wrap in 's
        sql = "INSERT IGNORE INTO %s ( %s ) VALUES ( %s )"
        if cursor.execute(sql, (table, col_str, data_str)):
            cursor.close()
            db.commit()


if __name__ == '__main__':
    config = read_config()
    db = init_db()
    for game in GAMES:
        scrape_game(game)
