import pandas as pd # data processing
import sqlite3 #SQL library to extract data from sql database

#display max columns and rows, to be able to inspect big data tables
pd.set_option('display.max_columns', None)
pd.set_option('display.max_rows', None)

# conntects to google drive if ran in colab
# ignored if ran outside of colab

if 'google.colab' in str(get_ipython()):
  from google.colab import drive
  drive.mount('/content/drive')

# connect to SQL database in google drive if ran in colab. 
# connet to SQL database in notebook folder if ran outside of colab

if 'google.colab' in str(get_ipython()):
    path = '/content/drive/My Drive/Colab Notebooks/Python_Data_Programming_Coursework/'
else:
    path = str()
database = path + 'database.sqlite'
conn = sqlite3.connect(database)

# get overview of tables in SQL database
tables = pd.read_sql("""SELECT *
                      FROM sqlite_master
                      WHERE type='table';""", conn)
tables

# extract Country table from database
countries = pd.read_sql("""SELECT *
                        FROM Country
                        """, conn)
countries

# extract League table from database
leagues = pd.read_sql("""SELECT *
                      FROM League
                       """, conn)
leagues

# extract Team table from database
teams = pd.read_sql("""SELECT *
                        FROM Team
                        """, conn)
teams.head(5)

# extract Team Attributes table from database
team_attributes = pd.read_sql("""SELECT *
                        FROM Team_Attributes
                        """, conn)
team_attributes.head(5)

# extract Match table from database, limited to 5 matches, just to get a quick understanding of the table. 
matches = pd.read_sql("""SELECT *
                        FROM Match
                        JOIN Country ON Match.country_id = Country.id
                        WHERE Country.name = 'England'
                        LIMIT 5
                        """, conn)
matches

# extract Player Attributes table from database
Player_Attributes = pd.read_sql("""SELECT *
                        FROM Player_attributes
                        """, conn)
Player_Attributes.head(5)

epl_matches = pd.read_sql("""SELECT
                                    season, 
                                    stage as 'match_day', 
                                    date as 'match_date',
                                    Country.name as 'country_name',
                                    League.name as 'league_name',
                                    tb1.team_long_name as 'home_team',
                                    tb2.team_long_name as 'away_team',
                                    home_team_goal,
                                    away_team_goal,
                                    home_team_api_id,
                                    away_team_api_id,
                                    match_api_id,
                                    home_player_1,
                                    home_player_2,
                                    home_player_3,
                                    home_player_4,
                                    home_player_5,
                                    home_player_6,
                                    home_player_7,
                                    home_player_8,
                                    home_player_9,
                                    home_player_10,
                                    home_player_11,
                                    away_player_1,
                                    away_player_2,
                                    away_player_3,
                                    away_player_4,
                                    away_player_5,
                                    away_player_6,
                                    away_player_7,
                                    away_player_8,
                                    away_player_9,
                                    away_player_10,
                                    away_player_11
                        FROM Match
                        
                        JOIN Country ON Match.country_id = Country.id 
                        JOIN League ON Match.league_id = League.id

                        JOIN Team tb1 ON Match.home_team_api_id = tb1.team_api_id
                        JOIN Team tb2 ON Match.away_team_api_id = tb2.team_api_id
            
                  
                        WHERE Country.name = 'England'
                         
                        """, conn)
epl_matches.head(5)

# unique dates of team data information
list(team_attributes.date.unique())

#create team data dictionary to ensure no data leakage
dictionary_season = {'2010-02-22 00:00:00': '2010/2011',
                     '2011-02-22 00:00:00': '2011/2012',
                     '2012-02-22 00:00:00': '2012/2013',
                     '2013-09-20 00:00:00': '2013/2014',
                     '2014-09-19 00:00:00': '2014/2015',
                    '2015-09-10 00:00:00': '2015/2016'
                     }

# add season column to team attributes tablle, based on above dictionary mapping
team_attributes['season'] = team_attributes ['date'].map(dictionary_season)

#merge home team attributes into the match table
epl_matches = epl_matches.merge(team_attributes, how='inner', left_on=['season', 'home_team_api_id'], right_on=['season', 'team_api_id'])

#add home team marker to home team attributes column
old_column_list = []
new_column_list = []
for column in team_attributes.columns.drop('season'):
  old_column_list.append(column)
  new_column_list.append(column+str('_home_team'))
for i in range(len(old_column_list)):
  epl_matches = epl_matches.rename(columns={old_column_list[i]: new_column_list[i]})

epl_matches.head(5)

#merge away team attributes into the match table
epl_matches = epl_matches.merge(team_attributes, how='inner', left_on=['season', 'away_team_api_id'], right_on=['season', 'team_api_id'])

#add away team marker to home team attributes column
old_column_list = []
new_column_list = []
for column in team_attributes.columns.drop('season'):
  old_column_list.append(column)
  new_column_list.append(column+str('_away_team'))
for i in range(len(old_column_list)):
  epl_matches = epl_matches.rename(columns={old_column_list[i]: new_column_list[i]})

epl_matches.head(5)

team_non_attribute_columns = ['home_team_api_id', 'away_team_api_id', 'id_home_team', 'team_fifa_api_id_home_team', 'team_api_id_home_team', 'date_home_team','id_away_team', 'team_fifa_api_id_away_team', 'team_api_id_away_team', 'date_away_team']
epl_matches = epl_matches.drop(columns=team_non_attribute_columns)
epl_matches.head(5)

# check for total number of matches in dataset
total_num_matches = epl_matches.shape[0]
total_num_matches

# check for missing values
epl_matches.isna().sum()

# remove two team attribute columns, as most values missing
epl_matches = epl_matches.drop(columns=['buildUpPlayDribbling_home_team', 'buildUpPlayDribbling_away_team'])

# remove rows with missing values
epl_matches = epl_matches.dropna()
print(total_num_matches - epl_matches.shape[0],'out of',total_num_matches,'matches were deleted from the dataset due to missing team attribute or player ID information')

# number of unique update dates in player attribute table
Player_Attributes.date.unique().shape

# generate player ID column list, which will be used as keys to merge the match table with the player attributes table
team_players = ['home_player_1',
                'home_player_2', 
                'home_player_3', 
                'home_player_4', 
                'home_player_5',
                'home_player_6', 
                'home_player_7', 
                'home_player_8', 
                'home_player_9',
                'home_player_10',
                'home_player_11',
                'away_player_1', 
                'away_player_2',
                'away_player_3', 
                'away_player_4', 
                'away_player_5', 
                'away_player_6',
                'away_player_7', 
                'away_player_8', 
                'away_player_9', 
                'away_player_10',
                'away_player_11']

#below two loops ensure player ID columns have the correct formatting
for player in team_players:
  epl_matches[player] = pd.to_numeric(epl_matches[player])

for player in team_players:
  epl_matches[player] = epl_matches[player].astype('int64')

#ensure correct date formatting of date columns
epl_matches['match_date'] = pd.to_datetime(epl_matches['match_date'])
Player_Attributes['date'] = pd.to_datetime(Player_Attributes['date'])

#sort both match and player attribute tables by date ascending.
epl_matches = epl_matches.sort_values(by='match_date', ascending=True)
Player_Attributes = Player_Attributes.sort_values(by='date', ascending=True)

# merge the player attributes of all 22 starting players into the match table
for player in team_players:
  epl_matches = pd.merge_asof(epl_matches, Player_Attributes, left_on='match_date', right_on='date', left_by = player, right_by ='player_api_id', direction='backward', suffixes= ("","_"+player))

# add missing ending label for home player one column names
for i in epl_matches.columns[72:113]:
  epl_matches = epl_matches.rename(columns={i: i+str('_home_player_1')})

# overview of match table after merging in player data for 22 starting players
epl_matches.head(5)