#| eval: false
#| echo: true

# Install core packages
pip install nfl-data-py pandas numpy

# Storage and database packages
pip install pyarrow fastparquet sqlalchemy

# Additional utilities
pip install requests beautifulsoup4 tqdm

#| label: load-pbp-py
#| message: false
#| warning: false
#| cache: true

import nfl_data_py as nfl
import pandas as pd

# Load single season
pbp_2023 = nfl.import_pbp_data([2023])

# Load multiple seasons
pbp_multi = nfl.import_pbp_data([2021, 2022, 2023])

# Display basic info
print(f"2023 season: {len(pbp_2023):,} plays, {len(pbp_2023.columns)} columns")
print(f"Multi-season: {len(pbp_multi):,} plays\n")

# Sample data
print(pbp_2023[['game_id', 'week', 'posteam', 'defteam',
                'desc', 'epa', 'wpa']].head())

#| label: roster-data-py
#| message: false
#| warning: false
#| cache: true

# Load roster data
rosters = nfl.import_rosters([2023])

# Load seasonal statistics
player_stats = nfl.import_seasonal_data([2023])

# Load draft picks
draft_picks = nfl.import_draft_picks([2023])

# Example: Find all quarterbacks
qbs = rosters[rosters['position'] == 'QB'][
    ['full_name', 'team', 'position', 'depth_chart_position', 'years_exp']
]

print(f"Found {len(qbs)} quarterbacks in 2023\n")
print(qbs.head())

#| eval: false
#| echo: true

import requests
import json

# Example: Fetch current week scores
url = "https://site.api.espn.com/apis/site/v2/sports/football/nfl/scoreboard"

response = requests.get(url)
data = response.json()

# Extract game information
games = data['events']

#| eval: false
#| echo: true

# Install collegefootballdata
pip install cfbd

import cfbd
from cfbd.rest import ApiException

# Configure API
configuration = cfbd.Configuration()
configuration.api_key['Authorization'] = 'YOUR_API_KEY'
api_instance = cfbd.GamesApi(cfbd.ApiClient(configuration))

# Get games
games = api_instance.get_games(year=2023)

#| eval: false
#| echo: true

import requests

# Fetch college football scoreboard
cfb_url = "https://site.api.espn.com/apis/site/v2/sports/football/college-football/scoreboard"

response = requests.get(cfb_url)
cfb_data = response.json()

# Get team rankings
rankings_url = "https://site.api.espn.com/apis/site/v2/sports/football/college-football/rankings"
rankings = requests.get(rankings_url).json()

#| eval: false
#| echo: true

# Save to CSV
pbp_2023.to_csv("data/pbp_2023.csv", index=False)

# Load from CSV
pbp_loaded = pd.read_csv("data/pbp_2023.csv")

# Compressed CSV
pbp_2023.to_csv("data/pbp_2023.csv.gz",
                index=False, compression='gzip')

#| eval: false
#| echo: true

import pyarrow.parquet as pq
import pyarrow as pa

# Save to Parquet
pbp_2023.to_parquet("data/pbp_2023.parquet")

# Load from Parquet
pbp_loaded = pd.read_parquet("data/pbp_2023.parquet")

# Partitioned Parquet (by season)
pbp_multi.to_parquet("data/pbp_partitioned",
                     partition_cols=['season'],
                     engine='pyarrow')

# Read partitioned data
pbp_all = pd.read_parquet("data/pbp_partitioned")

#| label: storage-benchmark-py
#| message: false
#| warning: false
#| cache: true

import nfl_data_py as nfl
import pandas as pd
import time
import os

# Load sample data
pbp = nfl.import_pbp_data([2023])

# Benchmark CSV
start = time.time()
pbp.to_csv("temp_test.csv", index=False)
csv_write_time = time.time() - start

start = time.time()
csv_data = pd.read_csv("temp_test.csv")
csv_read_time = time.time() - start

csv_size = os.path.getsize("temp_test.csv") / 1024**2

# Benchmark Parquet
start = time.time()
pbp.to_parquet("temp_test.parquet")
parquet_write_time = time.time() - start

start = time.time()
parquet_data = pd.read_parquet("temp_test.parquet")
parquet_read_time = time.time() - start

parquet_size = os.path.getsize("temp_test.parquet") / 1024**2

# Create comparison
comparison = pd.DataFrame({
    'Format': ['CSV', 'Parquet'],
    'Size (MB)': [csv_size, parquet_size],
    'Write Time (s)': [csv_write_time, parquet_write_time],
    'Read Time (s)': [csv_read_time, parquet_read_time]
})

print("\nStorage Format Comparison - 2023 NFL Play-by-Play Data")
print(comparison.to_string(index=False))

# Clean up
os.remove("temp_test.csv")
os.remove("temp_test.parquet")

#| eval: false
#| echo: true

from sqlalchemy import create_engine
import sqlite3

# Create database engine
engine = create_engine('sqlite:///data/football.db')

# Write data to database
pbp_2023.to_sql('pbp_2023', engine, if_exists='replace', index=False)
rosters.to_sql('rosters', engine, if_exists='replace', index=False)

# Query data using pandas
query = """
    SELECT posteam,
           COUNT(*) as plays,
           AVG(epa) as avg_epa
    FROM pbp_2023
    WHERE play_type IN ('pass', 'run')
    GROUP BY posteam
    ORDER BY avg_epa DESC
"""

result = pd.read_sql(query, engine)

# Create indexes for performance
with engine.connect() as conn:
    conn.execute("CREATE INDEX IF NOT EXISTS idx_posteam ON pbp_2023(posteam)")
    conn.execute("CREATE INDEX IF NOT EXISTS idx_play_type ON pbp_2023(play_type)")

#| eval: false
#| echo: true

from sqlalchemy import create_engine

# Connect to PostgreSQL
engine = create_engine(
    'postgresql://postgres:your_password@localhost:5432/football'
)

# Write data
pbp_2023.to_sql('pbp_2023', engine, if_exists='replace', index=False)

# Create materialized view
with engine.connect() as conn:
    conn.execute("""
        CREATE MATERIALIZED VIEW team_epa AS
        SELECT
            posteam,
            season,
            COUNT(*) as plays,
            AVG(epa) as avg_epa,
            STDDEV(epa) as sd_epa
        FROM pbp_2023
        WHERE play_type IN ('pass', 'run')
        GROUP BY posteam, season
    """)

#| eval: false
#| echo: true

import nfl_data_py as nfl
import pandas as pd
import logging
from pathlib import Path
from datetime import datetime, timedelta

# Set up logging
logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(levelname)s - %(message)s'
)
logger = logging.getLogger(__name__)

logger.info("Starting NFL data pipeline...")

# Define data directory
data_dir = Path("data/nfl")
data_dir.mkdir(parents=True, exist_ok=True)

def update_pbp_data(seasons=[2023], force=False):
    """Update play-by-play data"""

    logger.info(f"Updating play-by-play data for seasons: {seasons}")

    parquet_file = data_dir / "pbp_data.parquet"

    # Check if update is needed
    if not force and parquet_file.exists():
        file_time = datetime.fromtimestamp(parquet_file.stat().st_mtime)
        if datetime.now() - file_time < timedelta(hours=24):
            logger.info("Data is recent (< 24 hours old). Skipping update.")
            return pd.read_parquet(parquet_file)

    try:
        # Download data
        pbp = nfl.import_pbp_data(seasons)

        # Data validation
        logger.info("Validating data...")

        # Check for required columns
        required_cols = ['game_id', 'play_id', 'posteam', 'epa', 'wpa']
        missing_cols = set(required_cols) - set(pbp.columns)

        if missing_cols:
            logger.error(f"Missing required columns: {missing_cols}")
            raise ValueError("Data validation failed")

        # Check for reasonable data ranges
        extreme_epa = pbp[(pbp['epa'] < -20) | (pbp['epa'] > 20)]['epa'].notna()
        if extreme_epa.any():
            logger.warning("Found extreme EPA values outside expected range")

        # Save data
        logger.info(f"Saving data to {parquet_file}")
        pbp.to_parquet(parquet_file)

        logger.info(f"Pipeline completed successfully. Processed {len(pbp):,} plays.")

        return pbp

    except Exception as e:
        logger.error(f"Pipeline failed: {str(e)}")
        raise

def update_roster_data(seasons=[2023]):
    """Update roster data"""

    logger.info(f"Updating roster data for seasons: {seasons}")

    rosters = nfl.import_rosters(seasons)

    roster_file = data_dir / "roster_data.parquet"
    rosters.to_parquet(roster_file)

    logger.info(f"Roster data saved: {len(rosters):,} players")

    return rosters

# Run pipeline
if __name__ == "__main__":
    pbp = update_pbp_data([2021, 2022, 2023])
    rosters = update_roster_data([2021, 2022, 2023])

    logger.info("All pipeline tasks completed")

#| eval: false
#| echo: true

import pandas as pd
import numpy as np
import logging

logger = logging.getLogger(__name__)

def validate_pbp_data(pbp):
    """Validate play-by-play data quality"""

    logger.info("Running data quality checks...")

    errors = []
    warnings = []

    # Check 1: Required columns exist
    required_cols = ['game_id', 'play_id', 'posteam', 'defteam',
                     'down', 'ydstogo', 'epa', 'wpa']

    missing_cols = set(required_cols) - set(pbp.columns)
    if missing_cols:
        errors.append(f"Missing required columns: {missing_cols}")

    # Check 2: No duplicate plays
    duplicates = pbp.duplicated(subset=['game_id', 'play_id'])
    if duplicates.any():
        errors.append(f"Found {duplicates.sum()} duplicate plays")

    # Check 3: Valid down values
    invalid_downs = pbp['down'].notna() & ~pbp['down'].isin([1, 2, 3, 4])
    if invalid_downs.any():
        warnings.append(f"Found {invalid_downs.sum()} invalid down values")

    # Check 4: EPA in reasonable range
    extreme_epa = pbp['epa'].notna() & ((pbp['epa'] < -15) | (pbp['epa'] > 15))
    if extreme_epa.any():
        warnings.append(f"Found {extreme_epa.sum()} extreme EPA values")

    # Check 5: Win probability between 0 and 1
    invalid_wp = pbp['wp'].notna() & ((pbp['wp'] < 0) | (pbp['wp'] > 1))
    if invalid_wp.any():
        errors.append(f"Found {invalid_wp.sum()} invalid WP values")

    # Check 6: Yards to go is positive
    invalid_ydstogo = pbp['ydstogo'].notna() & ((pbp['ydstogo'] < 0) | (pbp['ydstogo'] > 100))
    if invalid_ydstogo.any():
        warnings.append(f"Found {invalid_ydstogo.sum()} invalid ydstogo values")

    # Report results
    if errors:
        for error in errors:
            logger.error(error)
        raise ValueError("Data validation failed")

    if warnings:
        for warning in warnings:
            logger.warning(warning)

    logger.info("All data quality checks passed")

    return pbp

# Run validation
pbp_validated = validate_pbp_data(pbp_2023)

#| label: data-profiling-py
#| message: false
#| warning: false
#| cache: true

import nfl_data_py as nfl
import pandas as pd

pbp = nfl.import_pbp_data([2023])

# Create data profile
profile = pd.DataFrame({
    'Column': pbp.columns,
    'Type': pbp.dtypes.values,
    'Missing': pbp.isna().sum().values,
    'Missing %': (pbp.isna().sum() / len(pbp) * 100).values,
    'Unique': pbp.nunique().values
})

profile = profile.sort_values('Missing %', ascending=False)

# Display top columns with missing data
print("\nData Quality Profile - Columns with >50% missing values:")
print(profile[profile['Missing %'] > 50].head(10).to_string(index=False))

#| eval: false
#| echo: true

import boto3
import pandas as pd
from io import BytesIO

# Initialize S3 client
s3 = boto3.client('s3',
                  aws_access_key_id='YOUR_ACCESS_KEY',
                  aws_secret_access_key='YOUR_SECRET_KEY')

# Upload to S3
buffer = BytesIO()
pbp_2023.to_parquet(buffer)
buffer.seek(0)

s3.put_object(
    Bucket='my-football-data',
    Key='pbp/season=2023/data.parquet',
    Body=buffer
)

# Read from S3
obj = s3.get_object(Bucket='my-football-data',
                    Key='pbp/season=2023/data.parquet')

pbp_from_s3 = pd.read_parquet(BytesIO(obj['Body'].read()))

# List files in bucket
response = s3.list_objects_v2(Bucket='my-football-data',
                              Prefix='pbp/')
files = [obj['Key'] for obj in response.get('Contents', [])]