# -*- coding: utf-8 -*-

"""

Created on Sat Oct 15 16:35:36 2016

@author: david

"""

############

# Setup

############

import os

import pandas as pd

import sqlite3

import MySQLdb # It works from command line, but not here.

os.chdir('/home/david/Desktop/Documents/GitRepos/python-espncricinfo')

os.listdir()

############

# Basic tests

############

# Get this module from https://github.com/dwillis/python-espncricinfo

from espncricinfo.summary import Summary

s = Summary()

s.match_ids

from espncricinfo.match import Match

m = Match('64148')

m.description

############

# Import 49 matches from 2015 world cup; create big, clunky match_df

############

from match_ids import match_ids

match_data = {}

for i in match_ids:

m = Match(i)

match_data[i]=m

print(i)

'''

MORE ATTRIBUTES NOT USED:

date

present_datetime_local

present_datetime_gmt

weather_location_code

start_datetime_local

start_datetime_gmt

scheduled_overs

innings_list

innings

latest_batting

latest_bowling

latest_innings

latest_innings_fow

'''

############

# Set pandas display

############

pd.set_option('display.max_rows', 20)

pd.set_option('display.max_columns', 10)

pd.set_option('display.width', 1000)

pd.set_option('display.max_colwidth', -1)

############

# Explore bunches of junk available in match_data

############

bunch_of_stuff_df = pd.DataFrame({

})

bunch_of_stuff_df[['ground_id','ground_name', 'team_2_id','team_2_abbreviation']]

# This will be the basis for the players_df table

bunch_of_stuff_df[['ground_id','ground_name']]

# This will be the basis for the ground_df table

bunch_of_stuff_df[['team_1']]

# Lots of info here

bunch_of_stuff_df[['team_1_innings', 'team_1_run_rate', 'team_1_overs_batted',

'team_1_batting_result', 'match_winner']]

# Some info about match result

bunch_of_stuff_df[['batting_first']]

############

# main match_df data frame

############

match_df = pd.DataFrame({

})

# Append match_officials to match_df

match_officials = [[match_data[x].officials[i]['object_id'] for i in range(5)] for x in match_ids]

match_officials = [[match_ids[i]] + match_officials[i] for i in range(len(match_officials))]

match_officials_df = pd.DataFrame({

'reserve_umpire' : [match_officials[i][5] for i in range(len(match_officials))]})

match_df = pd.concat([match_df, match_officials_df], axis=1)

# Append team_1_next_match and team_2_next_match to match_df

team_ids = team_df[['team_id']].astype(float) # This isn't working

team_ids = ['1','2','3','4','5','6','7','8','9','25','27','29','30','40'] # Lame fix! Grrr

list(match_df["match_id"][(match_df["team_1_id"] == '2') |

(match_df["team_2_id"] == '2')]) #list all match ids for each team

next_match = {i : list(match_df["match_id"][(match_df["team_1_id"] == i) |

(match_df["team_2_id"] == i)]) for i in team_ids}

def find_next_match(match_id,team_id):

return 'NULL'

find_next_match(656467, '30') # test works

find_next_match(656477, '30') # test works

match_df[['match_id','team_1_id']]

match_df['team_1_id'][('match_id' == 656467)]

team_id = match_df["team_1_id"][(match_df["match_id"] == 656467)] # <--- Fix this so it ONLY returns team_id and not other junk

find_next_match(656467,team_id)

team_1_next_match = []

team_2_next_match = []

#for i in match_df['match_id']:

for i in range(48):

print(i)

#team_1_id = match_df[(match_df["match_id"] == i)]["team_1_id"] # <--- Fix this !!!

#team_2_id = match_df[(match_df["match_id"] == i)]["team_2_id"][0] # <--- Fix this !!!

team_1_id = match_df["team_1_id"][i]

team_2_id = match_df["team_2_id"][i]

match_id = match_df["match_id"][i]

team_1_next_match.append(str(find_next_match(match_id,team_1_id)))

team_2_next_match.append(str(find_next_match(match_id,team_2_id)))

match_df['team_1_next_match'] = team_1_next_match

match_df['team_2_next_match'] = team_2_next_match

############

# Create team_df table showing team_id and team_abbreviation

############

team_df_1 = bunch_of_stuff_df[['team_1_id','team_1_abbreviation']]

team_df_2 = bunch_of_stuff_df[['team_2_id','team_2_abbreviation']]

team_df_1 = team_df_1.rename(index=str,

columns={"team_1_id": "team_id", "team_1_abbreviation": "team_abbreviation"})

team_df_2 = team_df_2.rename(index=str,

columns={"team_2_id": "team_id", "team_2_abbreviation": "team_abbreviation"})

team_df = team_df_1.append(team_df_2, ignore_index = True).drop_duplicates()

team_df[['team_id']] = team_df[['team_id']].astype(float)

team_df = team_df.sort('team_id')

team_df[['team_id']] = team_df[['team_id']].astype(object)

team_df.dtypes

############

# Create ground_df table showing ground_id and other info

############

ground_df = bunch_of_stuff_df[['ground_id','ground_name', 'continent',

'town_area', 'town_name', 'town_id']].drop_duplicates()

############

# Create officials_df table showing officials for each game

############

officials_df = pd.DataFrame({

}).drop_duplicates()

############

# Create players_df table listing all players

############

team_id = [

for i in range(11) for x in match_ids]

player_id = [

for i in range(11) for x in match_ids]

player_name = [

for i in range(11) for x in match_ids]

dob = [

for i in range(11) for x in match_ids]

age_years = [

for i in range(11) for x in match_ids]

player_primary_role = [

for i in range(11) for x in match_ids]

players_df = pd.DataFrame({

}).drop_duplicates()

############

# Create database

############

conn = sqlite3.connect("cwc.sqlite")

match_df.to_sql(name='matches', con=conn, if_exists = 'replace')

team_df.to_sql(name='teams', con=conn, if_exists = 'replace')

officials_df.to_sql(name='officials', con=conn, if_exists = 'replace')

players_df.to_sql(name='players', con=conn, if_exists = 'replace')

ground_df.to_sql(name='grounds', con=conn, if_exists = 'replace')

conn.close()