nfl_data_import.py

import nfl_data_py as nfl
import pandas as pd
from database import DatabaseConnector
from models import Team, Player

# Map nflverse team abbreviations to the full string names
TEAM_MAP = {
    "ARI": "Arizona Cardinals", "ATL": "Atlanta Falcons", "BAL": "Baltimore Ravens", 
    "BUF": "Buffalo Bills", "CAR": "Carolina Panthers", "CHI": "Chicago Bears", 
    "CIN": "Cincinnati Bengals", "CLE": "Cleveland Browns", "DAL": "Dallas Cowboys", 
    "DEN": "Denver Broncos", "DET": "Detroit Lions", "GB": "Green Bay Packers", 
    "HOU": "Houston Texans", "IND": "Indianapolis Colts", "JAX": "Jacksonville Jaguars", 
    "KC": "Kansas City Chiefs", "LV": "Las Vegas Raiders", "LAC": "Los Angeles Chargers", 
    "LA": "Los Angeles Rams", "MIA": "Miami Dolphins", "MIN": "Minnesota Vikings", 
    "NE": "New England Patriots", "NO": "New Orleans Saints", "NYG": "New York Giants", 
    "NYJ": "New York Jets", "PHI": "Philadelphia Eagles", "PIT": "Pittsburgh Steelers", 
    "SF": "San Francisco 49ers", "SEA": "Seattle Seahawks", "TB": "Tampa Bay Buccaneers", 
    "TEN": "Tennessee Titans", "WAS": "Washington Commanders"
}

def populate_database(target_year=2025):
    # Initialize Database Session
    db = DatabaseConnector()
    session = db.session
    
    # Fetch Data using nfl_data_py
    print(f"Downloading {target_year} roster data...")
    # import_rosters returns a pandas DataFrame
    rosters_df = nfl.import_seasonal_rosters([target_year])
    
    # Filter for active players and drop rows with missing team or position data
    active_roster = rosters_df[(rosters_df['status'] == 'ACT')].dropna(subset=['team', 'position'])

    print(f"Processing {len(active_roster)} active players...")

    processed_teams = set()

    # Transform and Load
    for index, row in active_roster.iterrows():
        team_abbr = row['team']
        
        if team_abbr not in TEAM_MAP:
            continue
            
        full_team_name = TEAM_MAP[team_abbr]

        # Handle the Team Record
        if full_team_name not in processed_teams:
            team_record = session.query(Team).filter_by(team_name=full_team_name).first()
            if not team_record:
                team_record = Team(team_name=full_team_name)
                session.add(team_record)
                session.commit() # Commit so the player can reference the foreign key
            processed_teams.add(full_team_name)

        # Handle missing first/last names by splitting the full name if necessary
        first_name = row.get('first_name')
        last_name = row.get('last_name')
        
        if pd.isna(first_name) or pd.isna(last_name):
            name_parts = str(row['player_name']).split(' ', 1)
            first_name = name_parts[0]
            last_name = name_parts[1] if len(name_parts) > 1 else ""

        # Create the Player record
        new_player = Player(
            first_name=str(first_name),
            last_name=str(last_name),
            position=str(row['position']),
            team_name=full_team_name
        )
        
        session.merge(new_player)

    # Final commit for all players
    try:
        session.commit()
        print("Database successfully populated with nfl_data_py roster data.")
    except Exception as e:
        session.rollback()
        print(f"Database insertion failed: {e}")
    finally:
        session.close()

if __name__ == "__main__":
    populate_database(2024)