# File: wrangle_retrosheet_pt2.py
# Date Created: 2018-11-10
# Author(s): Mahkah Wu
# Purpose: Extracts game state data from Retrosheet event files

import pandas as pd
import numpy as np
import datetime
import psycopg2
from ignore import db_cred

df = pd.DataFrame()

for i in range(1950, 2017):
    print("Appending {}: {}".format(i, datetime.datetime.now()))
    df_year = pd.read_csv('ignore\\large_data\\game_data_retrosheet2\\wrangled\\wrangled{}.csv'.format(i), usecols=range(1,41))
    df_year['year'] = i
    df = df.append(df_year)

# home_teams = df['home_team'].unique()
# Team ID's mostly match database
# Convert MLN -> ML1
team_fields = ['home_team', 'away_team', 'top_bat_team', 'bot_bat_team', 'batting_team', 'fielding_team']
print('Mapping Team IDs: {}'.format(datetime.datetime.now()))
df['game_id'] = df['game_id'].str.replace(r'MLN([0-9]+)', r'ML1\1')
for col in team_fields:
    df[col].loc[df[col] == 'MLN'] = 'ML1'

# Convert MIL -> ML4 (1970-1997)
df['game_id'] = df['game_id'].str.replace(r'MIL(19[78][0-9]+)', r'ML4\1')
df['game_id'] = df['game_id'].str.replace(r'MIL(199[0-7][0-9]+)', r'ML4\1')
for col in team_fields:
    df[col].loc[(df[col] == 'MIL') & (df['year'] >= 1970) & (df['year'] <= 1997)] = 'ML4'

# Convert ANA -> LAA (2005-2016)
df['game_id'] = df['game_id'].str.replace(r'ANA(200[5-9][0-9]+)', r'LAA\1')
df['game_id'] = df['game_id'].str.replace(r'ANA(201[0-6][0-9]+)', r'LAA\1')
for col in team_fields:
    df[col].loc[(df[col] == 'ANA') & (df['year'] >= 2005) & (df['year'] <= 2016)] = 'LAA'


# Change retrosheet player ID's to db player ID's
query = '''SELECT "playerID", "retroID" FROM master'''
conn = db_cred.connect_db()
df_player_ids = pd.read_sql(query, conn)
dict_ids = df_player_ids.set_index('retroID').to_dict()['playerID']

player_fields = ['batter_id', 'pitcher_id', 'fielder_id', 'fielder2_id',
    'fielder3_id' , 'fielder4_id' ,'fielder5_id' , 'fielder6_id', 'fielder7_id',
     'fielder8_id', 'fielder9_id']

for field in player_fields:
    print('Mapping IDs in {}: {}'.format(field, datetime.datetime.now()))
    df[field] = df[field].map(dict_ids)

df.to_csv('ignore\\large_data\\game_data_retrosheet2\\combined_retrosheet.csv', index=False)
