sql/nfl_pfr_schema.sql

-- =============================================================================
-- frontal-lobe2 : NFL "Pro-Football-Reference style" schema section
-- =============================================================================
-- Recreates the chart/table design of the PFR 2026 season pages:
--   passing.htm | rushing.htm | receiving.htm | scrimmage.htm
--   defense.htm | scoring.htm | kicking.htm
-- for BOTH players and teams.
--
-- SCOPE: 2026 season only (current season). Season is still a column everywhere so
-- scaling to more seasons later is a data change, not a schema change.
--
-- GRAIN: one row per player (or team) PER WEEK PER GAME. This is deliberate --
-- season totals are a derived view (see bottom), not the base table. Prop markets
-- are priced per game, so week grain is required to go head-to-head with a book's
-- line on a specific matchup.
--
-- SOURCE OF COLUMNS: verified against real nflverse 2026 files on 2026-09-24
-- (weeks 1-2 present, REG season). NOT scraped from PFR -- PFR sits behind a
-- Cloudflare bot challenge (HTTP 403 on every path incl. robots.txt) and its ToS
-- prohibits harvesting. nflverse republishes the same underlying stats as open
-- data, including a direct mirror of PFR advanced stats. See docs/PFR_SOURCE_NOTE.md.
--
-- Engine: DuckDB.
-- =============================================================================

CREATE SCHEMA IF NOT EXISTS bronze;
CREATE SCHEMA IF NOT EXISTS silver;
CREATE SCHEMA IF NOT EXISTS gold;

-- =============================================================================
-- BRONZE: ingestion audit. One row per loader run so any silver row can be traced
-- back to the exact pull that produced it (ingest_run_id on every fact table).
-- =============================================================================
CREATE TABLE IF NOT EXISTS bronze.ingestion_runs (
    run_id          VARCHAR PRIMARY KEY,
    source_name     VARCHAR NOT NULL,          -- 'nflverse' | 'nflverse_pbp'
    source_kind     VARCHAR NOT NULL,          -- 'api' | 'html_snapshot'
    league          VARCHAR,
    started_at      TIMESTAMP NOT NULL,
    completed_at    TIMESTAMP,
    row_count       INTEGER,
    status          VARCHAR NOT NULL,          -- success | failed
    error_message   VARCHAR
);

-- =============================================================================
-- SHARED DIMENSIONS
-- =============================================================================

-- Player identity. nflverse gpsis/player_id is the join key; pfr_player_id lets us
-- reconcile against anything PFR-derived (advanced stats arrive keyed that way).
CREATE TABLE IF NOT EXISTS silver.nfl_player (
    player_id           VARCHAR PRIMARY KEY,      -- nflverse player_id
    pfr_player_id       VARCHAR,                  -- PFR id, from advstats feeds
    player_name         VARCHAR,                  -- short/abbrev form
    player_display_name VARCHAR NOT NULL,
    position            VARCHAR,
    position_group      VARCHAR,
    headshot_url        VARCHAR
);
CREATE INDEX IF NOT EXISTS ix_nfl_player_pfr ON silver.nfl_player (pfr_player_id);

-- Game spine. Every stat row hangs off (season, week, game_id).
CREATE TABLE IF NOT EXISTS silver.nfl_game (
    game_id         VARCHAR PRIMARY KEY,          -- nflverse game_id
    pfr_game_id     VARCHAR,
    season          INTEGER NOT NULL,
    week            INTEGER NOT NULL,
    season_type     VARCHAR NOT NULL,             -- REG | POST
    home_team       VARCHAR,
    away_team       VARCHAR,
    kickoff_utc     TIMESTAMP
);
CREATE INDEX IF NOT EXISTS ix_nfl_game_sw ON silver.nfl_game (season, week);

-- =============================================================================
-- PLAYER TABLES -- one per PFR page, matching that page's chart
-- =============================================================================

-- PFR passing.htm  (#all_passing)
CREATE TABLE IF NOT EXISTS silver.nfl_player_passing_week (
    season                  INTEGER NOT NULL,
    week                    INTEGER NOT NULL,
    season_type             VARCHAR NOT NULL,
    game_id                 VARCHAR NOT NULL,
    player_id               VARCHAR NOT NULL,
    team                    VARCHAR NOT NULL,
    opponent_team           VARCHAR,
    completions             INTEGER,
    attempts                INTEGER,
    passing_yards           INTEGER,
    passing_tds             INTEGER,
    passing_interceptions   INTEGER,
    sacks_suffered          DOUBLE,
    sack_yards_lost         INTEGER,
    sack_fumbles            INTEGER,
    sack_fumbles_lost       INTEGER,
    passing_air_yards       INTEGER,
    passing_yards_after_catch DOUBLE,
    passing_first_downs     INTEGER,
    passing_epa             DOUBLE,
    passing_cpoe            DOUBLE,
    passing_2pt_conversions INTEGER,
    pacr                    DOUBLE,               -- passing air conversion ratio
    passing_10              INTEGER,              -- completions of 10+ air yds
    passing_16              INTEGER,
    passing_20              INTEGER,
    passing_40              INTEGER,
    ingest_run_id           VARCHAR NOT NULL,
    PRIMARY KEY (season, week, game_id, player_id)
);

-- PFR rushing.htm  (#all_rushing)
CREATE TABLE IF NOT EXISTS silver.nfl_player_rushing_week (
    season                  INTEGER NOT NULL,
    week                    INTEGER NOT NULL,
    season_type             VARCHAR NOT NULL,
    game_id                 VARCHAR NOT NULL,
    player_id               VARCHAR NOT NULL,
    team                    VARCHAR NOT NULL,
    opponent_team           VARCHAR,
    carries                 INTEGER,
    rushing_yards           INTEGER,
    rushing_tds             INTEGER,
    rushing_fumbles         INTEGER,
    rushing_fumbles_lost    INTEGER,
    rushing_first_downs     INTEGER,
    rushing_epa             DOUBLE,
    rushing_2pt_conversions INTEGER,
    rushing_10              INTEGER,              -- rushes of 10+ yds
    rushing_12              INTEGER,
    rushing_20              INTEGER,
    rushing_40              INTEGER,
    ingest_run_id           VARCHAR NOT NULL,
    PRIMARY KEY (season, week, game_id, player_id)
);

-- PFR receiving.htm  (#all_receiving)
CREATE TABLE IF NOT EXISTS silver.nfl_player_receiving_week (
    season                  INTEGER NOT NULL,
    week                    INTEGER NOT NULL,
    season_type             VARCHAR NOT NULL,
    game_id                 VARCHAR NOT NULL,
    player_id               VARCHAR NOT NULL,
    team                    VARCHAR NOT NULL,
    opponent_team           VARCHAR,
    receptions              INTEGER,
    targets                 INTEGER,
    receiving_yards         INTEGER,
    receiving_tds           INTEGER,
    receiving_fumbles       INTEGER,
    receiving_fumbles_lost  INTEGER,
    receiving_air_yards     INTEGER,
    receiving_yards_after_catch DOUBLE,
    receiving_first_downs   INTEGER,
    receiving_epa           DOUBLE,
    receiving_2pt_conversions INTEGER,
    receiving_10            INTEGER,
    receiving_16            INTEGER,
    receiving_20            INTEGER,
    receiving_40            INTEGER,
    racr                    DOUBLE,               -- receiver air conversion ratio
    target_share            DOUBLE,
    air_yards_share         DOUBLE,
    wopr                    DOUBLE,               -- weighted opportunity rating
    ingest_run_id           VARCHAR NOT NULL,
    PRIMARY KEY (season, week, game_id, player_id)
);

-- PFR scrimmage.htm  (#all_scrimmage) -- rushing + receiving combined ("all-purpose").
-- Kept as its own table because PFR presents it as its own chart with derived totals.
CREATE TABLE IF NOT EXISTS silver.nfl_player_scrimmage_week (
    season                  INTEGER NOT NULL,
    week                    INTEGER NOT NULL,
    season_type             VARCHAR NOT NULL,
    game_id                 VARCHAR NOT NULL,
    player_id               VARCHAR NOT NULL,
    team                    VARCHAR NOT NULL,
    opponent_team           VARCHAR,
    touches                 INTEGER,              -- carries + receptions
    scrimmage_yards         INTEGER,              -- rushing_yards + receiving_yards
    scrimmage_tds           INTEGER,              -- rushing_tds + receiving_tds
    yards_per_touch         DOUBLE,
    scrimmage_first_downs   INTEGER,
    scrimmage_fumbles       INTEGER,
    carries                 INTEGER,
    rushing_yards           INTEGER,
    rushing_tds             INTEGER,
    receptions              INTEGER,
    targets                 INTEGER,
    receiving_yards         INTEGER,
    receiving_tds           INTEGER,
    ingest_run_id           VARCHAR NOT NULL,
    PRIMARY KEY (season, week, game_id, player_id)
);

-- PFR defense.htm  (#all_defense)
CREATE TABLE IF NOT EXISTS silver.nfl_player_defense_week (
    season                  INTEGER NOT NULL,
    week                    INTEGER NOT NULL,
    season_type             VARCHAR NOT NULL,
    game_id                 VARCHAR NOT NULL,
    player_id               VARCHAR NOT NULL,
    team                    VARCHAR NOT NULL,
    opponent_team           VARCHAR,
    def_tackles_solo        INTEGER,
    def_tackles_with_assist INTEGER,
    def_tackle_assists      INTEGER,
    def_tackles_for_loss    INTEGER,
    def_tackles_for_loss_yards DOUBLE,
    def_fumbles_forced      INTEGER,
    def_sacks               DOUBLE,
    def_sack_yards          DOUBLE,
    def_qb_hits             INTEGER,
    def_interceptions       INTEGER,
    def_interception_yards  INTEGER,
    def_pass_defended       INTEGER,
    def_tds                 INTEGER,
    def_fumbles             INTEGER,
    def_safeties            INTEGER,
    def_punt_blocks         INTEGER,
    def_pat_blocks          INTEGER,
    def_fg_blocks           INTEGER,
    def_2pt_atts            INTEGER,
    def_2pt_made            INTEGER,
    ingest_run_id           VARCHAR NOT NULL,
    PRIMARY KEY (season, week, game_id, player_id)
);

-- PFR scoring.htm  (#all_scoring) -- every way a player put points on the board.
CREATE TABLE IF NOT EXISTS silver.nfl_player_scoring_week (
    season                  INTEGER NOT NULL,
    week                    INTEGER NOT NULL,
    season_type             VARCHAR NOT NULL,
    game_id                 VARCHAR NOT NULL,
    player_id               VARCHAR NOT NULL,
    team                    VARCHAR NOT NULL,
    opponent_team           VARCHAR,
    passing_tds             INTEGER,
    rushing_tds             INTEGER,
    receiving_tds           INTEGER,
    def_tds                 INTEGER,
    special_teams_tds       INTEGER,
    fumble_recovery_tds     INTEGER,
    pt_return_tds           INTEGER,              -- punt-team return TDs
    total_tds               INTEGER,
    passing_2pt_conversions INTEGER,
    rushing_2pt_conversions INTEGER,
    receiving_2pt_conversions INTEGER,
    def_2pt_made            INTEGER,
    pat_made                INTEGER,
    fg_made                 INTEGER,
    def_safeties            INTEGER,
    total_points            INTEGER,              -- 6*TD + 2*2pt + 1*PAT + 3*FG + 2*safety
    fantasy_points          DOUBLE,
    fantasy_points_ppr      DOUBLE,
    ingest_run_id           VARCHAR NOT NULL,
    PRIMARY KEY (season, week, game_id, player_id)
);

-- PFR kicking.htm  (#all_kicking) -- FG + PAT + punting, incl. PFR's distance buckets.
CREATE TABLE IF NOT EXISTS silver.nfl_player_kicking_week (
    season                  INTEGER NOT NULL,
    week                    INTEGER NOT NULL,
    season_type             VARCHAR NOT NULL,
    game_id                 VARCHAR NOT NULL,
    player_id               VARCHAR NOT NULL,
    team                    VARCHAR NOT NULL,
    opponent_team           VARCHAR,
    -- field goals
    fg_made                 INTEGER,
    fg_att                  INTEGER,
    fg_missed               INTEGER,
    fg_blocked              INTEGER,
    fg_long                 INTEGER,
    fg_pct                  DOUBLE,
    fg_made_0_19            INTEGER,
    fg_made_20_29           INTEGER,
    fg_made_30_39           INTEGER,
    fg_made_40_49           INTEGER,
    fg_made_50_59           INTEGER,
    fg_made_60_             INTEGER,
    fg_missed_0_19          INTEGER,
    fg_missed_20_29         INTEGER,
    fg_missed_30_39         INTEGER,
    fg_missed_40_49         INTEGER,
    fg_missed_50_59         INTEGER,
    fg_missed_60_           INTEGER,
    fg_made_distance        DOUBLE,
    fg_missed_distance      DOUBLE,
    fg_blocked_distance     DOUBLE,
    -- game-winning FG
    gwfg_made               INTEGER,
    gwfg_att                INTEGER,
    gwfg_missed             INTEGER,
    gwfg_blocked            INTEGER,
    gwfg_distance           DOUBLE,
    -- extra points
    pat_made                INTEGER,
    pat_att                 INTEGER,
    pat_missed              INTEGER,
    pat_blocked             INTEGER,
    pat_pct                 DOUBLE,
    -- punting
    pt_att                  INTEGER,
    pt_yards                INTEGER,
    pt_net_yards            INTEGER,
    pt_long                 INTEGER,
    pt_blocked              INTEGER,
    pt_inside_20            INTEGER,
    pt_out_of_bounds        INTEGER,
    pt_downed               INTEGER,
    pt_touchback            INTEGER,
    pt_fair_caught          INTEGER,
    pt_returned             INTEGER,
    pt_return_yards         INTEGER,
    ingest_run_id           VARCHAR NOT NULL,
    PRIMARY KEY (season, week, game_id, player_id)
);

-- PFR returns (punt/kick return) -- shares the kicking/special-teams family.
CREATE TABLE IF NOT EXISTS silver.nfl_player_returns_week (
    season                  INTEGER NOT NULL,
    week                    INTEGER NOT NULL,
    season_type             VARCHAR NOT NULL,
    game_id                 VARCHAR NOT NULL,
    player_id               VARCHAR NOT NULL,
    team                    VARCHAR NOT NULL,
    opponent_team           VARCHAR,
    punt_returns            INTEGER,
    punt_return_yards       INTEGER,
    kickoff_returns         INTEGER,
    kickoff_return_yards    INTEGER,
    special_teams_tds       INTEGER,
    ingest_run_id           VARCHAR NOT NULL,
    PRIMARY KEY (season, week, game_id, player_id)
);

-- =============================================================================
-- PFR ADVANCED STATS -- nflverse mirrors PFR's own advanced tables directly.
-- Keyed on pfr_player_id (that's how the source arrives); join via silver.nfl_player.
-- These are the columns a book's model may not be pricing well -- pressure rates,
-- yards after contact, broken tackles, coverage stats.
-- =============================================================================

CREATE TABLE IF NOT EXISTS silver.nfl_pfr_adv_passing_week (
    season                  INTEGER NOT NULL,
    week                    INTEGER NOT NULL,
    game_type               VARCHAR,
    game_id                 VARCHAR,
    pfr_game_id             VARCHAR,
    pfr_player_id           VARCHAR NOT NULL,
    pfr_player_name         VARCHAR,
    team                    VARCHAR NOT NULL,
    opponent                VARCHAR,
    passing_drops           INTEGER,
    passing_drop_pct        DOUBLE,
    passing_bad_throws      INTEGER,
    passing_bad_throw_pct   DOUBLE,
    times_sacked            INTEGER,
    times_blitzed           INTEGER,
    times_hurried           INTEGER,
    times_hit               INTEGER,
    times_pressured         INTEGER,
    times_pressured_pct     DOUBLE,
    ingest_run_id           VARCHAR NOT NULL,
    PRIMARY KEY (season, week, pfr_player_id, team)
);

CREATE TABLE IF NOT EXISTS silver.nfl_pfr_adv_rushing_week (
    season                  INTEGER NOT NULL,
    week                    INTEGER NOT NULL,
    game_type               VARCHAR,
    game_id                 VARCHAR,
    pfr_game_id             VARCHAR,
    pfr_player_id           VARCHAR NOT NULL,
    pfr_player_name         VARCHAR,
    team                    VARCHAR NOT NULL,
    opponent                VARCHAR,
    carries                 INTEGER,
    rushing_yards_before_contact     DOUBLE,
    rushing_yards_before_contact_avg DOUBLE,
    rushing_yards_after_contact      DOUBLE,
    rushing_yards_after_contact_avg  DOUBLE,
    rushing_broken_tackles           INTEGER,
    receiving_broken_tackles         INTEGER,
    ingest_run_id           VARCHAR NOT NULL,
    PRIMARY KEY (season, week, pfr_player_id, team)
);

CREATE TABLE IF NOT EXISTS silver.nfl_pfr_adv_receiving_week (
    season                  INTEGER NOT NULL,
    week                    INTEGER NOT NULL,
    game_type               VARCHAR,
    game_id                 VARCHAR,
    pfr_game_id             VARCHAR,
    pfr_player_id           VARCHAR NOT NULL,
    pfr_player_name         VARCHAR,
    team                    VARCHAR NOT NULL,
    opponent                VARCHAR,
    rushing_broken_tackles  INTEGER,
    receiving_broken_tackles INTEGER,
    receiving_drop          INTEGER,
    receiving_drop_pct      DOUBLE,
    receiving_int           INTEGER,
    receiving_rat           DOUBLE,
    ingest_run_id           VARCHAR NOT NULL,
    PRIMARY KEY (season, week, pfr_player_id, team)
);

CREATE TABLE IF NOT EXISTS silver.nfl_pfr_adv_defense_week (
    season                  INTEGER NOT NULL,
    week                    INTEGER NOT NULL,
    game_type               VARCHAR,
    game_id                 VARCHAR,
    pfr_game_id             VARCHAR,
    pfr_player_id           VARCHAR NOT NULL,
    pfr_player_name         VARCHAR,
    team                    VARCHAR NOT NULL,
    opponent                VARCHAR,
    def_ints                INTEGER,
    def_targets             INTEGER,
    def_completions_allowed INTEGER,
    def_completion_pct      DOUBLE,
    def_yards_allowed       INTEGER,
    def_yards_allowed_per_cmp DOUBLE,
    def_yards_allowed_per_tgt DOUBLE,
    def_receiving_td_allowed  INTEGER,
    def_passer_rating_allowed DOUBLE,
    def_adot                DOUBLE,               -- avg depth of target
    def_air_yards_completed INTEGER,
    def_yards_after_catch   DOUBLE,
    def_times_blitzed       INTEGER,
    def_times_hurried       INTEGER,
    def_times_hitqb         INTEGER,
    def_sacks               DOUBLE,
    def_pressures           INTEGER,
    def_tackles_combined    INTEGER,
    def_missed_tackles      INTEGER,
    def_missed_tackle_pct   DOUBLE,
    ingest_run_id           VARCHAR NOT NULL,
    PRIMARY KEY (season, week, pfr_player_id, team)
);

-- =============================================================================
-- TEAM TABLES -- same 7 chart families, team grain.
-- One wide weekly table mirrors the source exactly; per-chart views slice it so
-- each PFR page still has a 1:1 counterpart without duplicating storage.
-- =============================================================================

CREATE TABLE IF NOT EXISTS silver.nfl_team_week (
    season                  INTEGER NOT NULL,
    week                    INTEGER NOT NULL,
    season_type             VARCHAR NOT NULL,
    game_id                 VARCHAR NOT NULL,
    team                    VARCHAR NOT NULL,
    opponent_team           VARCHAR,
    -- passing
    completions             INTEGER,
    attempts                INTEGER,
    passing_yards           INTEGER,
    passing_tds             INTEGER,
    passing_interceptions   INTEGER,
    sacks_suffered          DOUBLE,
    sack_yards_lost         INTEGER,
    passing_air_yards       INTEGER,
    passing_yards_after_catch DOUBLE,
    passing_first_downs     INTEGER,
    passing_epa             DOUBLE,
    passing_cpoe            DOUBLE,
    -- rushing
    carries                 INTEGER,
    rushing_yards           INTEGER,
    rushing_tds             INTEGER,
    rushing_first_downs     INTEGER,
    rushing_epa             DOUBLE,
    -- receiving
    receptions              INTEGER,
    targets                 INTEGER,
    receiving_yards         INTEGER,
    receiving_tds           INTEGER,
    receiving_air_yards     INTEGER,
    receiving_yards_after_catch DOUBLE,
    receiving_first_downs   INTEGER,
    receiving_epa           DOUBLE,
    -- defense
    def_tackles_solo        INTEGER,
    def_sacks               DOUBLE,
    def_sack_yards          DOUBLE,
    def_qb_hits             INTEGER,
    def_interceptions       INTEGER,
    def_pass_defended       INTEGER,
    def_tds                 INTEGER,
    def_safeties            INTEGER,
    def_fumbles_forced      INTEGER,
    def_tackles_for_loss    INTEGER,
    -- scoring / special
    special_teams_tds       INTEGER,
    fumble_recovery_tds     INTEGER,
    fg_made                 INTEGER,
    fg_att                  INTEGER,
    fg_long                 INTEGER,
    fg_pct                  DOUBLE,
    pat_made                INTEGER,
    pat_att                 INTEGER,
    pat_pct                 DOUBLE,
    -- punting / returns
    pt_att                  INTEGER,
    pt_yards                INTEGER,
    pt_net_yards            INTEGER,
    pt_inside_20            INTEGER,
    punt_returns            INTEGER,
    punt_return_yards       INTEGER,
    kickoff_returns         INTEGER,
    kickoff_return_yards    INTEGER,
    -- discipline / ball security
    penalties               INTEGER,
    penalty_yards           INTEGER,
    fumbles_total           INTEGER,
    fumbles_lost_total      INTEGER,
    timeouts                INTEGER,
    ingest_run_id           VARCHAR NOT NULL,
    PRIMARY KEY (season, week, game_id, team)
);

-- Per-PFR-page team views (1:1 with the site's charts, no extra storage)
CREATE OR REPLACE VIEW silver.v_nfl_team_passing_week AS
SELECT season, week, season_type, game_id, team, opponent_team,
       completions, attempts, passing_yards, passing_tds, passing_interceptions,
       sacks_suffered, sack_yards_lost, passing_air_yards, passing_yards_after_catch,
       passing_first_downs, passing_epa, passing_cpoe
FROM silver.nfl_team_week;

CREATE OR REPLACE VIEW silver.v_nfl_team_rushing_week AS
SELECT season, week, season_type, game_id, team, opponent_team,
       carries, rushing_yards, rushing_tds, rushing_first_downs, rushing_epa
FROM silver.nfl_team_week;

CREATE OR REPLACE VIEW silver.v_nfl_team_receiving_week AS
SELECT season, week, season_type, game_id, team, opponent_team,
       receptions, targets, receiving_yards, receiving_tds, receiving_air_yards,
       receiving_yards_after_catch, receiving_first_downs, receiving_epa
FROM silver.nfl_team_week;

CREATE OR REPLACE VIEW silver.v_nfl_team_scrimmage_week AS
SELECT season, week, season_type, game_id, team, opponent_team,
       carries + receptions                  AS touches,
       rushing_yards + receiving_yards        AS scrimmage_yards,
       rushing_tds + receiving_tds            AS scrimmage_tds,
       rushing_first_downs + receiving_first_downs AS scrimmage_first_downs
FROM silver.nfl_team_week;

CREATE OR REPLACE VIEW silver.v_nfl_team_defense_week AS
SELECT season, week, season_type, game_id, team, opponent_team,
       def_tackles_solo, def_tackles_for_loss, def_sacks, def_sack_yards,
       def_qb_hits, def_interceptions, def_pass_defended, def_tds,
       def_safeties, def_fumbles_forced
FROM silver.nfl_team_week;

CREATE OR REPLACE VIEW silver.v_nfl_team_scoring_week AS
SELECT season, week, season_type, game_id, team, opponent_team,
       passing_tds, rushing_tds, receiving_tds, def_tds, special_teams_tds,
       fumble_recovery_tds,
       COALESCE(rushing_tds,0) + COALESCE(receiving_tds,0) + COALESCE(def_tds,0)
         + COALESCE(special_teams_tds,0)      AS total_tds,
       fg_made, pat_made, def_safeties,
       6 * (COALESCE(rushing_tds,0) + COALESCE(receiving_tds,0) + COALESCE(def_tds,0)
            + COALESCE(special_teams_tds,0))
         + 3 * COALESCE(fg_made,0) + COALESCE(pat_made,0)
         + 2 * COALESCE(def_safeties,0)        AS points_scored
FROM silver.nfl_team_week;

CREATE OR REPLACE VIEW silver.v_nfl_team_kicking_week AS
SELECT season, week, season_type, game_id, team, opponent_team,
       fg_made, fg_att, fg_long, fg_pct, pat_made, pat_att, pat_pct,
       pt_att, pt_yards, pt_net_yards, pt_inside_20
FROM silver.nfl_team_week;

-- =============================================================================
-- SEASON AGGREGATE VIEWS -- what PFR's season page shows. Derived, never stored,
-- so a late stat correction upstream can't leave totals out of sync with weeks.
-- =============================================================================

CREATE OR REPLACE VIEW silver.v_nfl_player_passing_season AS
SELECT season, season_type, player_id, ANY_VALUE(team) AS team,
       COUNT(*)                     AS games,
       SUM(completions)             AS completions,
       SUM(attempts)                AS attempts,
       SUM(passing_yards)           AS passing_yards,
       SUM(passing_tds)             AS passing_tds,
       SUM(passing_interceptions)   AS passing_interceptions,
       SUM(passing_first_downs)     AS passing_first_downs,
       SUM(sacks_suffered)          AS sacks_suffered,
       CASE WHEN SUM(attempts) > 0
            THEN 1.0 * SUM(completions) / SUM(attempts) END AS completion_pct,
       CASE WHEN SUM(attempts) > 0
            THEN 1.0 * SUM(passing_yards) / SUM(attempts) END AS yards_per_attempt,
       SUM(passing_epa)             AS passing_epa
FROM silver.nfl_player_passing_week
GROUP BY season, season_type, player_id;

CREATE OR REPLACE VIEW silver.v_nfl_player_rushing_season AS
SELECT season, season_type, player_id, ANY_VALUE(team) AS team,
       COUNT(*)                 AS games,
       SUM(carries)             AS carries,
       SUM(rushing_yards)       AS rushing_yards,
       SUM(rushing_tds)         AS rushing_tds,
       SUM(rushing_first_downs) AS rushing_first_downs,
       CASE WHEN SUM(carries) > 0
            THEN 1.0 * SUM(rushing_yards) / SUM(carries) END AS yards_per_carry,
       SUM(rushing_epa)         AS rushing_epa
FROM silver.nfl_player_rushing_week
GROUP BY season, season_type, player_id;

CREATE OR REPLACE VIEW silver.v_nfl_player_receiving_season AS
SELECT season, season_type, player_id, ANY_VALUE(team) AS team,
       COUNT(*)                   AS games,
       SUM(receptions)            AS receptions,
       SUM(targets)               AS targets,
       SUM(receiving_yards)       AS receiving_yards,
       SUM(receiving_tds)         AS receiving_tds,
       SUM(receiving_first_downs) AS receiving_first_downs,
       CASE WHEN SUM(targets) > 0
            THEN 1.0 * SUM(receptions) / SUM(targets) END AS catch_rate,
       CASE WHEN SUM(receptions) > 0
            THEN 1.0 * SUM(receiving_yards) / SUM(receptions) END AS yards_per_reception,
       AVG(target_share)          AS avg_target_share,
       SUM(receiving_epa)         AS receiving_epa
FROM silver.nfl_player_receiving_week
GROUP BY season, season_type, player_id;

-- =============================================================================
-- GOLD: the head-to-head layer. Player form vs. a book's posted prop line.
--
-- SUPERSEDED (confirmed empty, 0 rows, 2026-09-25): this was the original
-- placeholder before a real props source was scoped. gold.nfl_prop_line_rotowire
-- (sql/nfl_props_schema.sql) is the real, populated table -- 4,956+ rows,
-- verified real DK/FD/Caesars lines. Kept here (not dropped) so nothing that
-- may already reference this name breaks; do not write new code against it.
-- =============================================================================

CREATE TABLE IF NOT EXISTS gold.nfl_prop_line (
    league              VARCHAR NOT NULL DEFAULT 'nfl',
    season              INTEGER NOT NULL,
    week                INTEGER NOT NULL,
    game_id             VARCHAR,
    player_id           VARCHAR,
    book                VARCHAR NOT NULL,
    market              VARCHAR NOT NULL,        -- e.g. player_pass_yds, player_rush_yds
    line                DOUBLE,
    over_price_decimal  DOUBLE,
    under_price_decimal DOUBLE,
    fetched_at_utc      TIMESTAMP NOT NULL,
    source_name         VARCHAR NOT NULL,        -- 'the_odds_api'
    PRIMARY KEY (season, week, book, market, player_id, fetched_at_utc)
);

-- Rolling player form, point-in-time safe: only weeks strictly BEFORE the target
-- week feed the average, so a backtest can't peek at the result it's predicting.
CREATE OR REPLACE VIEW gold.v_nfl_player_form_passing AS
SELECT p.season, p.week AS as_of_week, p.player_id,
       AVG(h.passing_yards) OVER w  AS avg_passing_yards_prior,
       AVG(h.attempts)      OVER w  AS avg_attempts_prior,
       COUNT(*)             OVER w  AS games_in_window
FROM silver.nfl_player_passing_week p
JOIN silver.nfl_player_passing_week h
  ON h.player_id = p.player_id AND h.season = p.season AND h.week < p.week
WINDOW w AS (PARTITION BY p.player_id, p.season, p.week);

CREATE INDEX IF NOT EXISTS ix_pass_wk   ON silver.nfl_player_passing_week (season, week, player_id);
CREATE INDEX IF NOT EXISTS ix_rush_wk   ON silver.nfl_player_rushing_week (season, week, player_id);
CREATE INDEX IF NOT EXISTS ix_rec_wk    ON silver.nfl_player_receiving_week (season, week, player_id);
CREATE INDEX IF NOT EXISTS ix_def_wk    ON silver.nfl_player_defense_week (season, week, player_id);
CREATE INDEX IF NOT EXISTS ix_kick_wk   ON silver.nfl_player_kicking_week (season, week, player_id);
CREATE INDEX IF NOT EXISTS ix_team_wk   ON silver.nfl_team_week (season, week, team);
CREATE INDEX IF NOT EXISTS ix_propline  ON gold.nfl_prop_line (season, week, market, player_id);