CREATE MATERIALIZED VIEW season_batting_stats_view AS
WITH batting_stats AS (
    SELECT
        p.name,
        p.id AS player_id,
        p.sbaplayer_id,
        sg.season,
        p.team_id AS player_team_id,
        p.team_abbrev AS player_team_abbrev,

        -- Counting statistics (summed from StratPlays)
        SUM(sp.pa) AS pa,
        SUM(sp.ab) AS ab,
        SUM(sp.run) AS run,
        SUM(sp.hit) AS hit,
        SUM(sp.double) AS double,
        SUM(sp.triple) AS triple,
        SUM(sp.homerun) AS hr,
        SUM(sp.rbi) AS rbi,
        SUM(sp.bb) AS bb,
        SUM(sp.so) AS so,
        SUM(sp.bphr) AS bphr,
        SUM(sp.bpfo) AS bpfo,
        SUM(sp.bp1b) AS bp1b,
        SUM(sp.bplo) AS bplo,
        SUM(sp.gidp) AS gidp,
        SUM(sp.hbp) AS hbp,
        SUM(sp.sac) AS sac,
        SUM(sp.ibb) AS ibb,

        -- Calculated statistics using formulas
        CASE
            WHEN SUM(sp.ab) > 0
            THEN ROUND(SUM(sp.hit)::DECIMAL / SUM(sp.ab), 3)
            ELSE 0.000
        END AS avg,

        CASE
            WHEN SUM(sp.pa) > 0
            THEN ROUND((SUM(sp.hit) + SUM(sp.bb) + SUM(sp.hbp) + SUM(sp.ibb))::DECIMAL / SUM(sp.pa), 3)
            ELSE 0.000
        END AS obp,

        CASE
            WHEN SUM(sp.ab) > 0
            THEN ROUND((SUM(sp.hit) + SUM(sp.double) + 2 * SUM(sp.triple) + 3 * 
            SUM(sp.homerun))::DECIMAL / SUM(sp.ab), 3)
            ELSE 0.000
        END AS slg,

        CASE
            WHEN SUM(sp.pa) > 0 AND SUM(sp.ab) > 0
            THEN ROUND(
                ((SUM(sp.hit) + SUM(sp.bb) + SUM(sp.hbp) + SUM(sp.ibb))::DECIMAL / SUM(sp.pa)) +
                ((SUM(sp.hit) + SUM(sp.double) + 2 * SUM(sp.triple) + 3 * 
                SUM(sp.homerun))::DECIMAL / SUM(sp.ab)), 3)
            ELSE 0.000
        END AS ops,

        -- wOBA calculation (simplified version - adjust weights as needed)
        CASE
            WHEN SUM(sp.pa) > 0
            THEN ROUND((0.690 * SUM(sp.bb) + 0.722 * SUM(sp.hbp) + 0.888 * (SUM(sp.hit) - 
            SUM(sp.double) - SUM(sp.triple) - SUM(sp.homerun)) + 
            1.271 * SUM(sp.double) + 1.616 * SUM(sp.triple) + 2.101 * 
            SUM(sp.homerun))::DECIMAL / SUM(sp.pa), 3)
            ELSE 0.000
        END AS woba,

        CASE
            WHEN SUM(sp.pa) > 0
            THEN ROUND(SUM(sp.so)::DECIMAL / SUM(sp.pa) * 100, 1)
            ELSE 0.0
        END AS k_pct

    FROM stratplay sp
    JOIN stratgame sg ON sg.id = sp.game_id
    JOIN player p ON p.id = sp.batter_id
    GROUP BY p.id, sg.season
),
running_stats AS (
    SELECT
        sp.runner_id AS player_id,
        sg.season,
        SUM(sp.sb) AS sb,
        SUM(sp.cs) AS cs
    FROM stratplay sp
    JOIN stratgame sg ON sg.id = sp.game_id
    WHERE sp.runner_id IS NOT NULL
    GROUP BY sp.runner_id, sg.season
)
SELECT
    bs.*,
    COALESCE(rs.sb, 0) AS sb,
    COALESCE(rs.cs, 0) AS cs
FROM batting_stats bs
LEFT JOIN running_stats rs ON bs.player_id = rs.player_id
                            AND bs.season = rs.season;

-- Create indexes for better query performance
CREATE INDEX idx_season_batting_stats_season ON season_batting_stats_view (season);
CREATE INDEX idx_season_batting_stats_teamseason ON season_batting_stats_view (season, player_team_id);
CREATE INDEX idx_season_batting_stats_player ON season_batting_stats_view (player_id);
CREATE INDEX idx_season_batting_stats_sbaplayer ON season_batting_stats_view (sbaplayer_id);