import matplotlib.pyplot as plt
import numpy as np
from goodies import as_table

%matplotlib inline

import sqlite3

sql_query = """
SELECT country1, teams.title as country2, winner  
FROM ( SELECT teams.title as country1, team2_id, winner 
       FROM (SELECT team1_id, team2_id, winner 
             FROM games 
             INNER JOIN rounds on games.round_id=rounds.id 
             WHERE rounds.title='Final') t1 
       INNER JOIN teams on teams.id=t1.team1_id) t2 
INNER JOIN teams on teams.id=t2.team2_id;
"""

db = sqlite3.connect('../data/worldcup.db')
cur = db.execute(sql_query)

arr = np.fromiter(cur, dtype=[('country1', 'S30'),
                              ('country2', 'S30'),
                              ('winner', 'i2')])

arr['country1'] = np.char.replace(arr['country1'], 'West Germany (-1989)', 'Germany')
arr['country2'] = np.char.replace(arr['country2'], 'West Germany (-1989)', 'Germany')

as_table(arr, maxrows=100)

countries = np.unique(np.concatenate([arr['country1'], arr['country2']]))
winners = np.where(arr['winner']==1, arr['country1'], arr['country2'])
loosers = np.where(arr['winner']==2, arr['country1'], arr['country2'])

wins = [np.sum(winners==c) for c in countries]
losses = [np.sum(loosers==c) for c in countries]

stats = np.rec.fromarrays((countries, wins, losses),
                          names='country,wins,losses')

i = np.argsort(stats, order=('wins', 'losses', 'country'))[::-1]
stats = stats[i]
x = np.arange(len(stats))
plt.bar(x, stats['wins'])
plt.xticks(x+0.5, stats['country'], rotation=90)
plt.ylabel('wins in\nworld cup', rotation=0, labelpad=30)


plt.bar(x, stats['wins']+stats['losses'], fc='w', ec='k')
plt.bar(x, stats['wins'], ec='none', fc='k')
plt.xticks(x+0.5, stats['country'], rotation=90)
ax = plt.gca()
[ax.spines[s].set_visible(False) for s in ['top', 'left', 'right']]
#ax.spines['left'].set_position(('outward',20))
ax.xaxis.set_ticks_position('none')
ax.yaxis.set_ticks_position('left')
plt.ylabel('wins vs\nfinals', rotation=0, labelpad=30)
ax.set_axis_bgcolor('0.9')
ax.set_axisbelow(True)
plt.grid(axis='y', ls='-', color='w',zorder=200)
plt.title('FIFA World Cup (1930-2014)')
plt.figtext(0.15,-0.35, 'Data source: openfootball.github.io', size=9)