from collections import Counter

import datetime as dt

import numpy as np

import pandas as pd

import sqlite3

conn = sqlite3.connect('../input/database.sqlite')
c = conn.cursor()

ids = [i[0] for i in c.execute('SELECT id FROM League').fetchall()]
names = [i[0] for i in c.execute('SELECT name FROM League').fetchall()]
id_league = {i: n for (i, n) in zip(ids, names)}
id_league

ids = [i[0] for i in c.execute('SELECT id FROM Country').fetchall()]
names = [i[0] for i in c.execute('SELECT name FROM Country').fetchall()]
id_country = {i: n for (i, n) in zip(ids, names)}

c.execute('PRAGMA TABLE_INFO(Player_Stats)').fetchall()

cols = ', '.join(['home_player_Y' + str(i) for i in range(1, 12)])
c.execute('SELECT {0:s} FROM Match'.format(cols))
Y_array = c.fetchall()
Y = np.array([a for row in Y_array for a in row])
from collections import Counter
Counter(Y)

EA_stats = {'player': ', '.join(['overall_rating']), 'goalie': ', '.join(['gk_diving', 'gk_handling', 'gk_kicking', 'gk_positioning', 'gk_reflexes'])}
def getTeamScores(match_id, team, EA_stats, printout=False, group='forward_mid_defense_goalie'):
    """ Return the cumulative average team scores for 
    a given EA Sports FIFA statistic. If particular EA
    stats are not in the database that stat is taken as
    the overall player rating. If any positional stat is
    unavailable (i.e. no goalie information) that stat is
    taken as the average of the others for that team.
    team : str
        'home' or 'away'
    EA_stat : dict
        Names of statistics to cumulate for goalie and players.
        e.g. {'player': 'overall_rating, heading_accuracy',
              'goalie': 'gk_diving, gk_handling'}
    printout : boolean
        Option to print out debug information,
        defaults to False.
    group : str
        How to group scores:
        'forward_mid_defense_goalie': output 4 values
        'all': output 1 value (currently not implemented)
    """
    if team == 'home':
        player_cols = ', '.join(['home_player_' + str(i) for i in range(1, 12)])
        player_Y_cols = np.array(['home_player_Y' + str(i) for i in range(1, 12)])
    elif team == 'away':
        player_cols = ', '.join(['away_player_' + str(i) for i in range(1, 12)])
        player_Y_cols = np.array(['away_player_Y' + str(i) for i in range(1, 12)])
    c.execute('SELECT {0:s} FROM Match WHERE id={1:d}'.format(player_cols, match_id))
    player_api_id = np.array(c.fetchall()[0])
    if False not in [p == 0 or p == None for p in player_api_id]:
        return {'F': np.array([np.nan]), 'M': np.array([np.nan]), 'D': np.array([np.nan]), 'G': np.array([np.nan])}
    empty_mask = player_api_id != np.array(None)
    player_api_id = player_api_id[empty_mask]
    player_Y_cols = ', '.join(player_Y_cols[empty_mask])
    c.execute('SELECT {0:s} FROM Match WHERE id={1:d}'.format(player_Y_cols, match_id))
    player_Y = c.fetchall()[0]
    def givePosition(Y):
        """ Input the Y position of the player (as opposed
        to the lateral X position) and return the categorical
        position. """
        if Y == 1:
            return 'G'
        elif Y == 3:
            return 'D'
        elif Y == 5 or Y == 6 or Y == 7:
            return 'M'
        elif Y == 8 or Y == 9 or Y == 10 or (Y == 11):
            return 'F'
        else:
            return 'NaN'
    player_pos = np.array([givePosition(Y) for Y in player_Y])
    def toDatetime(datetime):
        """ Convert string date to datetime object. """
        return dt.datetime.strptime(datetime, '%Y-%m-%d %H:%M:%S')
    c.execute('SELECT date FROM Match WHERE id={}'.format(match_id))
    match_date = toDatetime(c.fetchall()[0][0])
    def getBestDate(player_id, match_date):
        """ Find most suitable player stats to use based
        on date of match and return the corresponding row
        id from the Player_Stats table. """
        c.execute('SELECT id FROM Player_Stats WHERE player_api_id={}'.format(player_id))
        ids = np.array([i[0] for i in c.fetchall()])
        c.execute('SELECT date_stat FROM Player_Stats WHERE player_api_id={}'.format(player_id))
        dates = [toDatetime(d[0]) for d in c.fetchall()]
        dates_delta = np.array([abs(d - match_date) for d in dates])
        return ids[dates_delta == dates_delta.min()][0]
    def fill_empty_stats(stats, stat_names):
        """ Input the incomplete EA player stats and corresponing
        names, return the filled in stats list. Filling with
        overall_rating or averaging otherwise (i.e. for goalies
        where there is no overall_rating stat). """
        if not np.sum([s == 0 or s == None for s in stats]):
            return stats
        stats_dict = {sn: s for (sn, s) in zip(stat_names, stats)}
        try:
            fill = stats_dict['overall_rating']
        except:
            fill = np.mean([s for s in stats if s != 0 and s != None])
        filled_stats = []
        for s in stats:
            if s == None or s == 0:
                filled_stats.append(fill)
            else:
                filled_stats.append(s)
        return filled_stats
    positions = ('G', 'D', 'M', 'F')
    average_stats = {}
    for position in positions:
        if position == 'G':
            stats = EA_stats['goalie']
        else:
            stats = EA_stats['player']
        position_ids = player_api_id[player_pos == position]
        average_stats[position] = np.zeros(len(stats.split(',')))
        for player_id in position_ids:
            best_date_id = getBestDate(player_id, match_date)
            c.execute('SELECT {0:s} FROM Player_Stats WHERE id={1:d}'.format(stats, best_date_id))
            query = np.array(c.fetchall()[0])
            query = fill_empty_stats(query, stats.split(', '))
            if sum([q == None or q == 0 for q in query]):
                raise LookupError('Found null EA stats entry at stat_id={}'.format(best_date_id))
            average_stats[position] += query
        average_stats[position] /= len(position_ids)
    try:
        average_stats['G'] = np.array([average_stats['G'].mean()])
    except:
        pass
    insert_value = np.mean([v[0] for v in average_stats.values() if not np.isnan(v)])
    for (k, v) in average_stats.items():
        if np.isnan(v[0]):
            average_stats[k] = np.array([insert_value])
    return average_stats

df = pd.read_sql(sql='SELECT {} FROM Match'.format('id, country_id, league_id, season, stage, ' + 'date, home_team_api_id, away_team_api_id, ' + 'home_team_goal, away_team_goal'), con=conn)

df = df.dropna()
H = lambda x: x[0] > x[1]
D = lambda x: x[0] == x[1]
A = lambda x: x[0] < x[1]
(state, result) = ([], [])
for goals in df[['home_team_goal', 'away_team_goal']].values:
    r = np.array([H(goals), D(goals), A(goals)])
    state.append(r)
    if (r == [1, 0, 0]).sum() == 3:
        result.append(1)
    elif (r == [0, 1, 0]).sum() == 3:
        result.append(2)
    elif (r == [0, 0, 1]).sum() == 3:
        result.append(3)
df['game_state'] = state
df['game_result'] = result
df['date'] = pd.to_datetime(df['date'])
df['country'] = df['country_id'].map(id_country)
df['league'] = df['league_id'].map(id_league)
f = lambda x: np.mean(x)
df['home_mean_stats'] = list(map(f, df[['home_F_stats', 'home_M_stats', 'home_D_stats', 'home_G_stats']].values))
df['away_mean_stats'] = list(map(f, df[['away_F_stats', 'away_M_stats', 'away_D_stats', 'away_G_stats']].values))

df.dtypes