sql/nfl_player_usage_schema.sql

-- =============================================================================
-- frontal-lobe2 : player usage & situational-confidence pond (Phase 1)
-- =============================================================================
-- Built the way the user built it: identify TWO stats first -- the spine -- then
-- layer every other stat on top of that spine. The layers are only meaningful
-- because the spine decides who we are even asking about.
--
--   LAYER 1  THE SPINE -- position-gated opportunity. "Who does he go to?"
--              if WR or TE  -> TARGETS
--              if RB or FB  -> RUSH ATTEMPTS
--            Nothing else. A WR's confidence is measured in targets and a
--            back's in carries, because those are the touches the coach
--            actually assigns. Mixing them into one "touches" number lets a
--            checkdown back outrank a WR1 and tells you nothing.
--
--   LAYER 2  STRESS -- the user's core insight, quoted:
--              "the higher the stress level the more they go to who they know"
--            So the signal is not how big a share he gets. It is whether his
--            share GROWS as the situation tightens. A coach spreading the ball
--            around on 1st-and-10 in the 2nd quarter is calling an offense. A
--            coach going to the same man on 3rd-and-4 at the 8 with a
--            three-point lead in the 4th is telling you who he trusts.
--            Measured as: share under high stress MINUS share under low
--            stress. That delta is the indicator.
--
--   LAYER 3  THE DEPTH CHART -- the coach's own published hierarchy, compared
--            to what the ball actually says. Out-earning your listed slot is
--            the most perishable signal here, because the chart is public and
--            slow while usage is real and fast.
--
--   LAYER 4  WHERE -- field zone (Inside 20/10/5), pass location, air yards.
--            "in the red zone, where were the passes thrown."
--
--   LAYER 5  RETURN -- Yards From Scrimmage / yards per touch. PFR's
--            /years/2026/scrimmage.htm. "productive players once they touch
--            the ball." Reused from silver.nfl_player_scrimmage_week so a
--            touch means one thing in this lake.
--
-- EVERY SHARE IS INTRA-TEAM. Per the user: the measurement is confidence
-- "within his own team", not across the NFL. A WR on a pass-heavy offense
-- out-targets a trusted WR1 on a run-first team without either fact saying
-- anything about the two coaches. So every denominator below is that player's
-- OWN TEAM's total in that same game.
--
-- SOURCES -- nflverse only (CC-BY-4.0, nightly). No new vendor needed:
--   pbp           play_by_play_2026.csv.gz  -> every target and every carry,
--                 with down, distance, clock, score, field position. Same file
--                 ingest_nfl_td_logs.py already pulls.
--   depth_charts  depth_charts_2026.csv     -> the stated hierarchy,
--                 point-in-time (197 snapshots Mar->Sep 2026, verified).
--
-- PFR is never fetched (403 Cloudflare + ToS). docs/PFR_SOURCE_NOTE.md.
-- ffverse/ffopportunity expected-points is verified and real but deliberately
-- NOT used in Phase 1: it is GPL-3.0, weekly rather than nightly, and a model
-- output rather than an observed fact. (game_id, play_id) is preserved below
-- so it can be joined 1:1 as a Phase 2 enrichment.
--
-- Engine: DuckDB. Depends on sql/nfl_pfr_schema.sql and
-- sql/nfl_schedule_schema.sql.
-- =============================================================================

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

-- =============================================================================
-- BASE TABLE: one row per OPPORTUNITY -- a target or a carry.
--
-- GRAIN: (game_id, play_id, opportunity_type). Same event-grain philosophy as
-- silver.nfl_td_log: store the decision once, express every chart as a view.
-- A single play can be only one of the two, so in practice this is one row per
-- play, but the composite key keeps it honest.
--
-- An "opportunity" is a coaching decision to put the ball in a named player's
-- hands. Sacks, scrambles, throwaways and spikes have no intended recipient in
-- the data and are excluded -- they are not decisions to trust anyone.
-- =============================================================================
CREATE TABLE IF NOT EXISTS silver.nfl_opportunity_event (
    season                  INTEGER NOT NULL,
    week                    INTEGER NOT NULL,
    season_type             VARCHAR,
    game_id                 VARCHAR NOT NULL,
    play_id                 INTEGER NOT NULL,
    opportunity_type        VARCHAR NOT NULL,      -- 'target' | 'rush'
    -- who
    posteam                 VARCHAR NOT NULL,      -- the offense; whose coach we are reading
    defteam                 VARCHAR,
    player_id               VARCHAR,               -- receiver on a target, rusher on a carry
    player_name             VARCHAR,
    passer_player_id        VARCHAR,               -- NULL on a rush
    passer_player_name      VARCHAR,

    -- ---- LAYER 4: WHERE ------------------------------------------------
    yardline_100            INTEGER,               -- yards from the opponent goal line
    is_inside_20            BOOLEAN,               -- PFR "Inside 20"
    is_inside_10            BOOLEAN,               -- PFR "Inside 10"
    is_inside_5             BOOLEAN,               -- PFR "Inside 5"
    is_goal_to_go           BOOLEAN,
    air_yards               INTEGER,               -- targets only
    pass_location           VARCHAR,               -- left | middle | right (targets)
    run_location            VARCHAR,               -- left | middle | right (rushes)
    run_gap                 VARCHAR,               -- guard | tackle | end (rushes)

    -- ---- LAYER 2: STRESS ----------------------------------------------
    -- The situation as the play was called. All four inputs come straight
    -- from pbp; none are inferred.
    down                    INTEGER,
    ydstogo                 INTEGER,
    qtr                     INTEGER,
    game_seconds_remaining  INTEGER,
    score_differential      INTEGER,               -- offense minus defense, pre-play
    -- The stress ladder. Documented explicitly so it can be argued with
    -- rather than reverse-engineered:
    --   3 MAX      -- goal to go, inside the 5, 4th down, or a one-score
    --                 game in the last 5 minutes. No margin for error.
    --   2 HIGH     -- red zone, 3rd down, or 2 yards or fewer to go.
    --                 Conversion or scoring pressure.
    --   1 MODERATE -- 2nd and long, or trailing by 9-16 (two-score chase).
    --   0 NEUTRAL  -- everything else. Open field, script territory.
    stress_level            INTEGER NOT NULL,
    is_high_stress          BOOLEAN NOT NULL,      -- stress_level >= 2
    is_two_minute           BOOLEAN,               -- last 2 min of either half
    -- QB run type. A designed keeper or sneak is a CALLED PLAY -- the coach
    -- chose the quarterback over his back, which is a confidence statement and
    -- belongs in the pool. A scramble is a collapsed pocket: nobody decided to
    -- give him the ball. Flagged, not deleted, so either reading is available.
    is_qb_scramble          BOOLEAN,
    is_designed_run         BOOLEAN,               -- rush that was not a scramble

    -- ---- outcome (Layer 5 inputs) --------------------------------------
    completed               BOOLEAN,               -- complete_pass; NULL-ish on rush (true)
    yards_gained            INTEGER,
    yards_after_catch       INTEGER,
    touchdown               BOOLEAN,
    first_down              BOOLEAN,
    turnover                BOOLEAN,               -- interception on a target
    play_description        VARCHAR,
    ingest_run_id           VARCHAR NOT NULL,
    PRIMARY KEY (season, game_id, play_id, opportunity_type)
);

CREATE INDEX IF NOT EXISTS ix_opp_player  ON silver.nfl_opportunity_event (season, player_id, week);
CREATE INDEX IF NOT EXISTS ix_opp_team    ON silver.nfl_opportunity_event (season, posteam, week);
CREATE INDEX IF NOT EXISTS ix_opp_stress  ON silver.nfl_opportunity_event (season, posteam, is_high_stress);
CREATE INDEX IF NOT EXISTS ix_opp_type    ON silver.nfl_opportunity_event (season, posteam, opportunity_type);

-- =============================================================================
-- BASE TABLE: depth chart snapshots (Layer 3) -- the coach's stated hierarchy.
--
-- This source is a POINT-IN-TIME snapshot series, not a weekly table. A depth
-- chart read today says nothing about who the WR2 was in week 1, so every
-- join below goes through v_nfl_depth_chart_pregame, which takes the latest
-- snapshot at or before that game's kickoff. Same point-in-time discipline the
-- props table enforces with is_pregame -- and for the same reason: otherwise a
-- mid-week promotion leaks backwards into a metric describing a past decision.
--
-- snapshot_at is TIMESTAMPTZ deliberately. The source publishes ISO-8601 with
-- a literal Z; storing that naive is exactly the bug already found and fixed
-- on gold.nfl_prop_line_rotowire, where local time sat in a column named _utc.
-- =============================================================================
CREATE TABLE IF NOT EXISTS silver.nfl_depth_chart_snapshot (
    snapshot_at             TIMESTAMPTZ NOT NULL,
    team                    VARCHAR NOT NULL,
    pos_grp                 VARCHAR,               -- '3WR 1TE' = offense
    pos_abb                 VARCHAR NOT NULL,      -- QB | RB | WR | TE | FB | OL...
    pos_name                VARCHAR,
    pos_slot                INTEGER,
    pos_rank                INTEGER NOT NULL,      -- 1 = starter at that spot
    gsis_id                 VARCHAR,               -- joins silver.nfl_player.player_id
    espn_id                 VARCHAR,
    player_name             VARCHAR NOT NULL,
    ingest_run_id           VARCHAR NOT NULL,
    PRIMARY KEY (snapshot_at, team, pos_abb, pos_rank, player_name)
);
CREATE INDEX IF NOT EXISTS ix_depth_team_at ON silver.nfl_depth_chart_snapshot (team, snapshot_at);
CREATE INDEX IF NOT EXISTS ix_depth_gsis    ON silver.nfl_depth_chart_snapshot (gsis_id, snapshot_at);

-- =============================================================================
-- LAYER 1 -- THE SPINE
-- =============================================================================
-- Team opportunity totals: the denominator for every share in this pond. Split
-- by type, because the spine is position-gated -- a WR's share is of the
-- team's TARGETS, a back's is of the team's CARRIES. One definition, one place.
-- QB DESIGNED RUNS ARE IN THE RUSH POOL. Per the user, and he is right:
--
--   "in that situation the team is inside of the 5 yardline therefore qb if he
--    runs is counted as a running back in my system, because the actual rb is
--    competing with the qb in an area where he should get the ball and that too
--    is a measurement of the coaches confidence"
--
-- An earlier version of this view excluded QB carries, on the theory that the
-- goal-line sneak was a structural artifact crushing every back's stress share.
-- That was wrong, and it deleted the signal instead of measuring it. If a coach
-- is inside the 5 -- the one place a starting back should get the ball -- and
-- calls a keeper instead, that IS the confidence statement. A back's share
-- falling under stress because his quarterback takes the sneak is the finding,
-- not a bug obscuring one.
--
-- The one distinction kept: a DESIGNED run (sneak, keeper, draw) is a called
-- play, so it counts. A SCRAMBLE is a collapsed pocket -- nobody chose to give
-- him the ball -- so it does not. Both stay in the event table, flagged, so
-- this choice is auditable rather than baked in.
--
-- The user's own cross-check on whether this unfairly buries starters:
--   "it will not effect the starting RB overall because if you look at the
--    rushing inside the 20 and success rate then more than likely it is going
--    to the starting rb"
-- That check is v_nfl_redzone_tiers (inside-20 opportunities and TDs), which is
-- exactly why the tiers live alongside the stress split rather than inside it.
CREATE OR REPLACE VIEW silver.v_nfl_team_opportunity_volume AS
WITH e AS (
    SELECT o.*, COALESCE(p.position, 'UNK') AS position
    FROM silver.nfl_opportunity_event o
    LEFT JOIN silver.nfl_player p ON p.player_id = o.player_id
    -- scrambles are not a coaching decision; everything else counts
    WHERE o.opportunity_type = 'target' OR COALESCE(o.is_designed_run, true)
)
SELECT season, week, game_id, posteam AS team,
       count(*) FILTER (WHERE opportunity_type = 'target')        AS team_targets,
       count(*) FILTER (WHERE opportunity_type = 'rush')          AS team_rushes,
       count(*) FILTER (WHERE opportunity_type = 'target' AND is_high_stress)
                                                                   AS team_targets_stress,
       count(*) FILTER (WHERE opportunity_type = 'rush' AND is_high_stress)
                                                                   AS team_rushes_stress,
       count(*) FILTER (WHERE opportunity_type = 'target' AND NOT is_high_stress)
                                                                   AS team_targets_calm,
       count(*) FILTER (WHERE opportunity_type = 'rush' AND NOT is_high_stress)
                                                                   AS team_rushes_calm,
       count(*) FILTER (WHERE is_inside_20)                        AS team_opps_inside_20,
       count(*) FILTER (WHERE is_inside_10)                        AS team_opps_inside_10,
       count(*) FILTER (WHERE is_inside_5)                         AS team_opps_inside_5,
       count(*) FILTER (WHERE is_goal_to_go)                       AS team_opps_g2g,
       count(*) FILTER (WHERE stress_level = 3)                    AS team_opps_max_stress,
       -- the QB's slice of the pool, surfaced rather than hidden: this is the
       -- number that explains a back's stress share, so it should be readable
       -- next to it instead of buried in a denominator
       count(*) FILTER (WHERE opportunity_type = 'rush' AND position = 'QB')
                                                                   AS team_qb_designed_runs,
       count(*) FILTER (WHERE opportunity_type = 'rush' AND position = 'QB'
                          AND is_inside_5)                         AS team_qb_runs_inside_5,
       count(*) FILTER (WHERE opportunity_type = 'rush' AND position = 'QB'
                          AND is_high_stress)                      AS team_qb_runs_stress
FROM e
GROUP BY season, week, game_id, posteam;

-- THE SPINE ITSELF. One row per player per game, carrying only the
-- position-appropriate opportunity metric and its intra-team share.
--
-- spine_metric names which stat is being counted so nothing downstream has to
-- guess, and so a mis-gated position is visible rather than silent.
CREATE OR REPLACE VIEW silver.v_nfl_opportunity_spine AS
WITH counted AS (
    SELECT e.season, e.week, e.game_id, e.posteam AS team, e.player_id,
           ANY_VALUE(e.player_name)                                  AS player_name,
           count(*) FILTER (WHERE e.opportunity_type = 'target')      AS targets,
           count(*) FILTER (WHERE e.opportunity_type = 'rush')        AS rushes,
           count(*) FILTER (WHERE e.opportunity_type = 'target' AND e.is_high_stress)
                                                                       AS targets_stress,
           count(*) FILTER (WHERE e.opportunity_type = 'rush'   AND e.is_high_stress)
                                                                       AS rushes_stress,
           count(*) FILTER (WHERE e.opportunity_type = 'target' AND NOT e.is_high_stress)
                                                                       AS targets_calm,
           count(*) FILTER (WHERE e.opportunity_type = 'rush'   AND NOT e.is_high_stress)
                                                                       AS rushes_calm
    FROM silver.nfl_opportunity_event e
    WHERE e.player_id IS NOT NULL
      -- scrambles excluded from a player's own count too, matching the
      -- denominator in v_nfl_team_opportunity_volume
      AND (e.opportunity_type = 'target' OR COALESCE(e.is_designed_run, true))
    GROUP BY e.season, e.week, e.game_id, e.posteam, e.player_id
)
-- THE GATE
--   WR, TE  -> targets
--   RB, FB  -> rush attempts
--   QB      -> rush attempts, per the user: "qb if he runs is counted as a
--              running back in my system". Labeled rush_attempts_qb so a
--              quarterback appearing high on a rushing leaderboard is obviously
--              a quarterback and not a mislabel -- when Hurts tops Philadelphia
--              inside the 5, that is the actual finding, not an error.
--
-- No inference. An earlier version fell back to "whichever stat he accumulated
-- more of" for unlabeled positions, which silently pulled QBs in under a name
-- that looked like a real gate. Guessing the gate defeats having one.
SELECT c.season, c.week, c.game_id, c.team, c.player_id, c.player_name,
       p.position,
       CASE WHEN p.position = 'QB'          THEN 'rush_attempts_qb'
            WHEN p.position IN ('RB', 'FB') THEN 'rush_attempts'
            ELSE 'targets' END                                  AS spine_metric,
       CASE WHEN p.position IN ('RB', 'FB', 'QB') THEN c.rushes
            ELSE c.targets END                                  AS spine_opportunities,
       CASE WHEN p.position IN ('RB', 'FB', 'QB') THEN c.rushes_stress
            ELSE c.targets_stress END                           AS spine_opps_stress,
       CASE WHEN p.position IN ('RB', 'FB', 'QB') THEN c.rushes_calm
            ELSE c.targets_calm END                             AS spine_opps_calm,
       -- the matching team denominator for that same metric
       CASE WHEN p.position IN ('RB', 'FB', 'QB') THEN v.team_rushes
            ELSE v.team_targets END                             AS team_spine_opportunities,
       CASE WHEN p.position IN ('RB', 'FB', 'QB') THEN v.team_rushes_stress
            ELSE v.team_targets_stress END                       AS team_spine_opps_stress,
       CASE WHEN p.position IN ('RB', 'FB', 'QB') THEN v.team_rushes_calm
            ELSE v.team_targets_calm END                         AS team_spine_opps_calm,
       -- both raw counts kept: a receiving back's targets still matter, they
       -- are just not his spine
       c.targets, c.rushes,
       -- the QB's share of this team's rush pool, carried on every row. This is
       -- the number that EXPLAINS a back's stress share, so it should be
       -- readable beside it rather than buried in a denominator.
       v.team_qb_designed_runs, v.team_qb_runs_inside_5, v.team_qb_runs_stress
FROM counted c
LEFT JOIN silver.nfl_player p ON p.player_id = c.player_id
JOIN silver.v_nfl_team_opportunity_volume v
  ON v.game_id = c.game_id AND v.team = c.team
WHERE p.position IN ('WR', 'TE', 'RB', 'FB', 'QB');

-- =============================================================================
-- LAYER 2 -- STRESS. The heart of the indicator.
--
-- "the higher the stress level the more they go to who they know"
--
-- confidence_delta = his share of the team's spine opportunities when the
-- situation is tight, MINUS his share when it is not. Positive means the coach
-- concentrates on him under pressure. That is the measurement.
--
-- Read alongside spine_opps_stress: a delta computed off 2 high-stress
-- opportunities is noise, which is why the raw counts travel with it and why
-- the indicator applies shrinkage rather than trusting the ratio directly.
-- =============================================================================
CREATE OR REPLACE VIEW silver.v_nfl_stress_split AS
SELECT season, team, player_id,
       ANY_VALUE(player_name)                                    AS player_name,
       ANY_VALUE(spine_metric)                                   AS spine_metric,
       count(DISTINCT game_id)                                    AS games,
       sum(spine_opportunities)                                   AS spine_opps,
       sum(team_spine_opportunities)                              AS team_spine_opps,
       sum(spine_opps_stress)                                     AS spine_opps_stress,
       sum(team_spine_opps_stress)                                AS team_spine_opps_stress,
       sum(spine_opps_calm)                                       AS spine_opps_calm,
       sum(team_spine_opps_calm)                                  AS team_spine_opps_calm,
       -- overall intra-team share of his own position's opportunity pool
       round(1.0 * sum(spine_opportunities)
             / NULLIF(sum(team_spine_opportunities), 0), 4)        AS share_overall,
       -- share when it is tight, and when it is not
       round(1.0 * sum(spine_opps_stress)
             / NULLIF(sum(team_spine_opps_stress), 0), 4)          AS share_stress,
       round(1.0 * sum(spine_opps_calm)
             / NULLIF(sum(team_spine_opps_calm), 0), 4)            AS share_calm,
       -- THE SIGNAL
       round(1.0 * sum(spine_opps_stress) / NULLIF(sum(team_spine_opps_stress), 0)
           - 1.0 * sum(spine_opps_calm)   / NULLIF(sum(team_spine_opps_calm), 0), 4)
                                                                   AS confidence_delta
FROM silver.v_nfl_opportunity_spine
GROUP BY season, team, player_id;

-- =============================================================================
-- LAYER 4 -- WHERE. Field-zone tiers, shaped like PFR's red-zone pages
-- (/years/2026/redzone-passing.htm, redzone-rushing.htm): three nested tiers,
-- each with opportunities, yards and TDs.
--
-- The "%" column on those PFR pages is a TEAM share, which is also the only
-- version that answers this pond's question -- a league-wide red-zone
-- percentage just ranks offenses. Named _intra so that is unambiguous.
--
-- _shrunk columns exist because red-zone samples are genuinely tiny. Confirmed
-- on real weeks 1-2 data: teams had between 0 and 18 inside-20 opportunities
-- for the entire season so far. A raw share on a denominator of 5 is mostly
-- noise -- 2-of-5 reads as 40% and would outrank a far more clearly trusted
-- player at 6-of-18. Adding k=4 to the denominator pulls small samples toward
-- zero until volume earns the number, and fades to irrelevance as real
-- denominators grow. Raw and shrunk are both exposed so the adjustment is
-- always visible and reversible.
-- =============================================================================
CREATE OR REPLACE VIEW silver.v_nfl_redzone_tiers AS
WITH pl AS (
    SELECT season, posteam AS team, player_id,
           ANY_VALUE(player_name) AS player_name,
           ANY_VALUE(opportunity_type) AS opportunity_type,
           count(*) FILTER (WHERE is_inside_20)                     AS opp_i20,
           sum(CASE WHEN is_inside_20 THEN yards_gained ELSE 0 END)  AS yds_i20,
           count(*) FILTER (WHERE is_inside_20 AND touchdown)        AS td_i20,
           count(*) FILTER (WHERE is_inside_10)                     AS opp_i10,
           sum(CASE WHEN is_inside_10 THEN yards_gained ELSE 0 END)  AS yds_i10,
           count(*) FILTER (WHERE is_inside_10 AND touchdown)        AS td_i10,
           count(*) FILTER (WHERE is_inside_5)                      AS opp_i5,
           sum(CASE WHEN is_inside_5 THEN yards_gained ELSE 0 END)   AS yds_i5,
           count(*) FILTER (WHERE is_inside_5 AND touchdown)         AS td_i5,
           count(*) FILTER (WHERE is_goal_to_go)                    AS opp_g2g,
           count(*) FILTER (WHERE is_goal_to_go AND touchdown)       AS td_g2g
    FROM silver.nfl_opportunity_event
    WHERE player_id IS NOT NULL
    GROUP BY season, posteam, player_id
), tm AS (
    SELECT season, posteam AS team,
           count(*) FILTER (WHERE is_inside_20)  AS t_i20,
           count(*) FILTER (WHERE is_inside_10)  AS t_i10,
           count(*) FILTER (WHERE is_inside_5)   AS t_i5,
           count(*) FILTER (WHERE is_goal_to_go) AS t_g2g
    FROM silver.nfl_opportunity_event
    GROUP BY season, posteam
)
SELECT pl.season, pl.team, pl.player_id, pl.player_name, pl.opportunity_type,
       tm.t_i20 AS team_opp_i20, tm.t_i10 AS team_opp_i10, tm.t_i5 AS team_opp_i5,
       pl.opp_i20, pl.yds_i20, pl.td_i20,
       round(1.0 * pl.opp_i20 / NULLIF(tm.t_i20, 0), 4)     AS pct_i20_intra,
       round(1.0 * pl.opp_i20 / NULLIF(tm.t_i20 + 4, 0), 4) AS pct_i20_shrunk,
       pl.opp_i10, pl.yds_i10, pl.td_i10,
       round(1.0 * pl.opp_i10 / NULLIF(tm.t_i10, 0), 4)     AS pct_i10_intra,
       round(1.0 * pl.opp_i10 / NULLIF(tm.t_i10 + 4, 0), 4) AS pct_i10_shrunk,
       pl.opp_i5, pl.yds_i5, pl.td_i5,
       round(1.0 * pl.opp_i5 / NULLIF(tm.t_i5, 0), 4)       AS pct_i5_intra,
       round(1.0 * pl.opp_i5 / NULLIF(tm.t_i5 + 4, 0), 4)   AS pct_i5_shrunk,
       pl.opp_g2g, pl.td_g2g,
       round(1.0 * pl.opp_g2g / NULLIF(tm.t_g2g + 4, 0), 4) AS pct_g2g_shrunk
FROM pl JOIN tm ON tm.season = pl.season AND tm.team = pl.team
WHERE pl.opp_i20 > 0;

-- Where on the field the ball went: pass location x air-yard depth for
-- targets, run location x gap for carries. One view, one row per bucket.
CREATE OR REPLACE VIEW silver.v_nfl_opportunity_location_map AS
SELECT season, posteam AS team, player_id,
       ANY_VALUE(player_name) AS player_name,
       opportunity_type,
       COALESCE(pass_location, run_location, 'unknown') AS field_side,
       CASE WHEN opportunity_type = 'rush' THEN COALESCE(run_gap, 'no_gap_recorded')
            WHEN air_yards IS NULL   THEN 'unknown'
            WHEN air_yards < 0       THEN 'behind_los'
            WHEN air_yards <= 5      THEN 'short_0_5'
            WHEN air_yards <= 10     THEN 'short_6_10'
            WHEN air_yards <= 20     THEN 'intermediate_11_20'
            ELSE 'deep_21_plus' END                    AS depth_or_gap,
       count(*)                                        AS opportunities,
       count(*) FILTER (WHERE is_high_stress)           AS opportunities_stress,
       sum(yards_gained)                               AS yards,
       count(*) FILTER (WHERE touchdown)                AS tds
FROM silver.nfl_opportunity_event
WHERE player_id IS NOT NULL
GROUP BY season, posteam, player_id, opportunity_type, field_side, depth_or_gap;

-- =============================================================================
-- LAYER 3 -- the depth chart, resolved point-in-time per game.
-- Kickoff derived the way ingest_rotowire_props.py does it: schedule gametime
-- is published in US/Eastern, converted to a real instant.
-- =============================================================================
CREATE OR REPLACE VIEW silver.v_nfl_depth_chart_pregame AS
WITH kick AS (
    SELECT game_id, season, week, home_team AS team,
           timezone('America/New_York', game_date + CAST(game_time_local AS TIME)) AS kickoff_utc
    FROM silver.nfl_schedule
    UNION ALL
    SELECT game_id, season, week, away_team,
           timezone('America/New_York', game_date + CAST(game_time_local AS TIME))
    FROM silver.nfl_schedule
), best AS (
    SELECT k.game_id, k.season, k.week, k.team, k.kickoff_utc,
           max(d.snapshot_at) AS snapshot_at
    FROM kick k
    JOIN silver.nfl_depth_chart_snapshot d
      ON d.team = k.team AND d.snapshot_at <= k.kickoff_utc
    GROUP BY k.game_id, k.season, k.week, k.team, k.kickoff_utc
)
SELECT b.game_id, b.season, b.week, b.team, b.kickoff_utc, b.snapshot_at,
       d.pos_abb, d.pos_name, d.pos_rank, d.gsis_id AS player_id, d.player_name
FROM best b
JOIN silver.nfl_depth_chart_snapshot d
  ON d.team = b.team AND d.snapshot_at = b.snapshot_at
WHERE d.pos_grp = '3WR 1TE'
  AND d.pos_abb IN ('QB', 'RB', 'WR', 'TE', 'FB');

-- =============================================================================
-- COMPONENTS -- all five layers, facts only, no weighting.
--
-- Kept separate from the indicator on purpose: these are observations. If the
-- weights below are ever retuned or discarded, nothing in this view moves.
-- =============================================================================
CREATE OR REPLACE VIEW gold.v_nfl_player_usage_components AS
WITH scrim AS (
    -- LAYER 5: Yards From Scrimmage. Reused, not recomputed.
    SELECT s.season, s.team, s.player_id,
           sum(s.touches)                                       AS touches,
           sum(s.scrimmage_yards)                               AS scrimmage_yards,
           sum(s.scrimmage_tds)                                 AS scrimmage_tds,
           round(1.0 * sum(s.scrimmage_yards)
                 / NULLIF(sum(s.touches), 0), 2)                AS yards_per_touch,
           round(1.0 * sum(s.scrimmage_yards)
                 / NULLIF(sum(t.team_scrimmage_yards), 0), 4)   AS scrimmage_yards_share_intra
    FROM silver.nfl_player_scrimmage_week s
    JOIN (SELECT season, game_id, team, sum(scrimmage_yards) AS team_scrimmage_yards
          FROM silver.nfl_player_scrimmage_week GROUP BY 1,2,3) t
      ON t.game_id = s.game_id AND t.team = s.team
    WHERE s.touches > 0
    GROUP BY s.season, s.team, s.player_id
), depth AS (
    SELECT season, team, player_id,
           arg_max(pos_abb,  week) AS depth_pos,
           arg_max(pos_rank, week) AS depth_rank,
           max(week)               AS depth_as_of_week
    FROM silver.v_nfl_depth_chart_pregame
    WHERE player_id IS NOT NULL
    GROUP BY season, team, player_id
)
SELECT ss.season, ss.team, ss.player_id,
       COALESCE(ss.player_name, p.player_display_name)  AS player_name,
       p.position, ss.games,
       -- LAYER 1: the spine
       ss.spine_metric, ss.spine_opps, ss.team_spine_opps, ss.share_overall,
       -- LAYER 2: stress
       ss.spine_opps_stress, ss.team_spine_opps_stress, ss.share_stress,
       ss.spine_opps_calm, ss.team_spine_opps_calm, ss.share_calm,
       ss.confidence_delta,
       -- LAYER 3: depth chart
       d.depth_pos, d.depth_rank, d.depth_as_of_week,
       -- LAYER 4: where
       rz.opp_i20, rz.opp_i10, rz.opp_i5, rz.opp_g2g,
       rz.td_i20, rz.td_i5,
       rz.team_opp_i20,
       rz.pct_i20_intra, rz.pct_i20_shrunk, rz.pct_i10_shrunk, rz.pct_i5_shrunk,
       rz.pct_g2g_shrunk,
       -- LAYER 5: return
       sc.touches, sc.scrimmage_yards, sc.yards_per_touch,
       sc.scrimmage_yards_share_intra, sc.scrimmage_tds,
       -- intra-team usage ranks. The pos-group rank is the ONLY one comparable
       -- to depth_rank, since depth_rank is itself within-position (WR1/WR2).
       -- Ranking a TE against the team's WRs and calling the gap a surplus was
       -- a real bug caught on first run: every TE1 and RB1 showed a large
       -- negative surplus purely because receivers out-target them.
       row_number() OVER (PARTITION BY ss.season, ss.team ORDER BY ss.spine_opps DESC)
                                                        AS usage_rank_on_team,
       row_number() OVER (PARTITION BY ss.season, ss.team, d.depth_pos
                          ORDER BY ss.spine_opps DESC)  AS usage_rank_in_pos_group
FROM silver.v_nfl_stress_split ss
LEFT JOIN silver.v_nfl_redzone_tiers rz
       ON rz.season = ss.season AND rz.team = ss.team AND rz.player_id = ss.player_id
LEFT JOIN scrim sc
       ON sc.season = ss.season AND sc.team = ss.team AND sc.player_id = ss.player_id
LEFT JOIN depth d
       ON d.season = ss.season AND d.team = ss.team AND d.player_id = ss.player_id
LEFT JOIN silver.nfl_player p ON p.player_id = ss.player_id;

-- =============================================================================
-- THE INDICATOR
--
-- READ THIS BEFORE TRUSTING THE NUMBER.
--
-- Everything above this line is an observed fact. This view is not. It is a
-- weighted heuristic. It has NOT been backtested, and no claim is made that a
-- high score predicts anything. Validating it -- does a rising score precede a
-- prop line moving -- is Phase 2 and needs a walk-forward test on
-- point-in-time data, not a correlation against this season's own totals.
--
-- The weights sit in one CTE so they change in one place, and every input is
-- exposed raw above so the score can always be taken apart.
--
-- STRESS CARRIES THE MOST WEIGHT because it is the user's actual thesis: the
-- tighter the situation, the more a coach goes to who he knows. Raw share is
-- volume, which a book already prices. The stress delta is the part that says
-- something a box score does not.
-- =============================================================================
CREATE OR REPLACE VIEW gold.v_nfl_player_usage_index AS
WITH weights AS (
    -- Change here and nowhere else. Sums to 1.0.
    SELECT 0.34::DOUBLE AS w_stress,   -- does his share GROW under pressure
           0.22::DOUBLE AS w_redzone,  -- share of the scarcest chances
           0.20::DOUBLE AS w_share,    -- baseline intra-team opportunity share
           0.12::DOUBLE AS w_depth,    -- out-earning his listed depth slot
           0.12::DOUBLE AS w_return    -- what happens once he has it
), base AS (
    SELECT c.*,
           CASE WHEN c.depth_rank IS NULL THEN NULL
                ELSE c.depth_rank - c.usage_rank_in_pos_group END AS depth_surplus,
           -- Y/Tch scaled within his own team: "efficient" must mean efficient
           -- against the teammates competing for the same touches, not against
           -- the league, which would reintroduce the cross-team comparison
           -- this pond exists to avoid.
           CASE WHEN max(c.yards_per_touch) OVER w = min(c.yards_per_touch) OVER w THEN 0.5
                ELSE (c.yards_per_touch - min(c.yards_per_touch) OVER w)
                     / NULLIF(max(c.yards_per_touch) OVER w
                              - min(c.yards_per_touch) OVER w, 0) END AS ypt_scaled_in_team,
           -- Shrink the stress delta by how many high-stress chances it rests
           -- on. A +40% delta off 2 opportunities is not a finding. At 8+
           -- high-stress opportunities this is essentially unshrunk.
           COALESCE(c.confidence_delta, 0)
             * (COALESCE(c.spine_opps_stress, 0)::DOUBLE
                / (COALESCE(c.spine_opps_stress, 0) + 6))          AS confidence_delta_shrunk
    FROM gold.v_nfl_player_usage_components c
    WINDOW w AS (PARTITION BY c.season, c.team)
), scored AS (
    SELECT b.*,
           -- stress axis: 0 delta -> 50. +0.20 (twenty points more share when
           -- tight) -> 100. Symmetric downward, so a coach visibly taking the
           -- ball OUT of someone's hands under pressure scores below 50.
           round(LEAST(GREATEST(50 + 250 * b.confidence_delta_shrunk, 0), 100), 1)
                                                                   AS stress_score,
           round(LEAST(100 * (0.5 * COALESCE(b.pct_i20_shrunk, 0)
                              + 0.3 * COALESCE(b.pct_i10_shrunk, 0)
                              + 0.2 * COALESCE(b.pct_i5_shrunk, 0)) / 0.32, 100), 1)
                                                                   AS redzone_score,
           round(LEAST(100 * COALESCE(b.share_overall, 0) / 0.35, 100), 1)
                                                                   AS share_score,
           round(50 + 50 * LEAST(GREATEST(COALESCE(b.depth_surplus, 0), -3), 3) / 3.0, 1)
                                                                   AS depth_score,
           round(LEAST(100 * (0.5 * COALESCE(b.ypt_scaled_in_team, 0)
                              + 0.5 * COALESCE(b.scrimmage_yards_share_intra, 0) / 0.40),
                       100), 1)                                    AS return_score
    FROM base b
)
SELECT s.season, s.team, s.player_id, s.player_name, s.position, s.games,
       s.spine_metric,
       round(w.w_stress  * s.stress_score
           + w.w_redzone * s.redzone_score
           + w.w_share   * s.share_score
           + w.w_depth   * s.depth_score
           + w.w_return  * s.return_score, 1)                      AS usage_index_score,
       -- the axes, readable on their own
       s.stress_score, s.redzone_score, s.share_score, s.depth_score, s.return_score,
       -- LAYER 2 raw: the actual thesis, in plain numbers
       s.share_calm, s.share_stress, s.confidence_delta,
       s.spine_opps_stress, s.team_spine_opps_stress,
       -- the classification. A single score collapses cases a bettor must
       -- treat completely differently, so the role travels with it.
       CASE
         WHEN s.spine_opps < 5                                THEN 'INSUFFICIENT SAMPLE'
         WHEN COALESCE(s.depth_surplus, 0) >= 1
              AND s.share_score >= 20                          THEN 'RISING -- OUT-EARNING DEPTH CHART'
         WHEN s.stress_score >= 60 AND s.share_score >= 40     THEN 'PRIMARY -- TRUSTED UNDER PRESSURE'
         WHEN s.stress_score >= 60 AND s.share_score <  40     THEN 'CLOSER -- SMALL ROLE, BIG MOMENTS'
         WHEN s.stress_score <  40 AND s.share_score >= 40     THEN 'VOLUME ONLY -- BENCHED WHEN IT TIGHTENS'
         WHEN s.redzone_score >= 45 AND s.share_score < 40      THEN 'RED ZONE SPECIALIST'
         WHEN s.share_score >= 20                              THEN 'ROTATIONAL CONTRIBUTOR'
         ELSE 'FRINGE'
       END                                                        AS usage_role,
       -- facts carried through so the score is never taken on faith
       s.spine_opps, s.team_spine_opps, s.share_overall,
       s.usage_rank_on_team, s.usage_rank_in_pos_group,
       s.depth_pos, s.depth_rank, s.depth_surplus, s.depth_as_of_week,
       s.opp_i20, s.opp_i10, s.opp_i5, s.opp_g2g, s.team_opp_i20,
       s.pct_i20_intra, s.pct_i20_shrunk, s.td_i20, s.td_i5,
       s.touches, s.yards_per_touch, s.scrimmage_yards,
       s.scrimmage_yards_share_intra, s.scrimmage_tds
FROM scored s CROSS JOIN weights w
ORDER BY usage_index_score DESC;

-- =============================================================================
-- "Who is THIS coach's son" -- one team at a time, ranked within itself.
-- The pond's whole purpose.
-- =============================================================================
CREATE OR REPLACE VIEW gold.v_nfl_player_usage_by_team AS
SELECT team, season,
       row_number() OVER (PARTITION BY team, season ORDER BY usage_index_score DESC)
                                                        AS rank_on_team,
       player_name, position, spine_metric,
       depth_pos, depth_rank, depth_surplus,
       usage_index_score, usage_role,
       stress_score, redzone_score, share_score, depth_score, return_score,
       spine_opps, team_spine_opps, share_overall,
       share_calm, share_stress, confidence_delta,
       opp_i20, team_opp_i20, yards_per_touch, scrimmage_yards
FROM gold.v_nfl_player_usage_index
ORDER BY team, rank_on_team;

-- =============================================================================
-- CORRELATION: usage index x the TD log
--
-- The user's framing, and the reason the TD logs were built before this pond:
--
-- The TD log exists upstream of this pond for exactly this join: it is the
-- confirmation step for the usage read. Related stats must agree -- if the
-- usage index is right, the scoring log should echo it.
--
-- The usage index says who the coach LEANS ON.
-- silver.nfl_td_log says who actually FINISHED. Putting them side by side turns
-- an opinion into a testable claim:
--
--   agreement   -- high confidence AND scoring. The read holds.
--   opportunity -- high confidence, no TDs yet. The coach keeps going back to
--                  him and it has not cashed. Anytime-TD markets price the
--                  result, not the usage, so this is where a line lags.
--   hollow      -- TDs without confidence. Scored on volume or a broken play,
--                  not on trust. The most dangerous profile to bet on repeating,
--                  which is exactly the profile a book will advertise.
--
-- Both sides are already in this lake and both are facts. The only judgement
-- here is the label, and the counts that produce it travel with it.
-- =============================================================================
CREATE OR REPLACE VIEW gold.v_nfl_player_usage_td_correlation AS
WITH td AS (
    SELECT season, scorer_player_id AS player_id,
           ANY_VALUE(scoring_team)                          AS team,
           count(*)                                          AS tds,
           count(*) FILTER (WHERE is_red_zone)               AS tds_red_zone,
           count(*) FILTER (WHERE is_goal_to_go)             AS tds_goal_to_go,
           count(*) FILTER (WHERE is_third_down)             AS tds_third_down,
           count(*) FILTER (WHERE td_type = 'rush')          AS tds_rush,
           count(*) FILTER (WHERE td_type = 'pass')          AS tds_rec,
           count(DISTINCT game_id)                           AS games_with_td
    FROM silver.nfl_td_log
    WHERE scorer_player_id IS NOT NULL
    GROUP BY season, scorer_player_id
)
SELECT cs.season, cs.team, cs.player_id, cs.player_name, cs.position,
       cs.spine_metric, cs.usage_index_score, cs.usage_role,
       -- the confidence side
       cs.share_overall, cs.share_calm, cs.share_stress, cs.confidence_delta,
       cs.opp_i20, cs.opp_i10, cs.opp_i5, cs.opp_g2g,
       cs.pct_i20_shrunk,
       -- the finishing side (TD log)
       COALESCE(td.tds, 0)             AS tds,
       COALESCE(td.tds_red_zone, 0)    AS tds_red_zone,
       COALESCE(td.tds_goal_to_go, 0)  AS tds_goal_to_go,
       COALESCE(td.tds_rush, 0)        AS tds_rush,
       COALESCE(td.tds_rec, 0)         AS tds_rec,
       COALESCE(td.games_with_td, 0)   AS games_with_td,
       -- conversion: TDs per red-zone opportunity. The bridge between the two.
       round(1.0 * COALESCE(td.tds_red_zone, 0) / NULLIF(cs.opp_i20, 0), 3)
                                       AS td_per_redzone_opp,
       -- IF THIS THEN THAT: does the confidence read agree with the scoring?
       CASE
         WHEN cs.usage_index_score >= 55 AND COALESCE(td.tds, 0) >= 2
              THEN 'AGREEMENT -- trusted and finishing'
         WHEN cs.usage_index_score >= 55 AND COALESCE(td.tds, 0) = 0
              THEN 'OPPORTUNITY -- trusted, has not cashed yet'
         WHEN cs.usage_index_score >= 55
              THEN 'AGREEMENT -- trusted, finishing lightly'
         WHEN cs.usage_index_score < 40 AND COALESCE(td.tds, 0) >= 2
              THEN 'HOLLOW -- scoring without trust, fragile'
         WHEN COALESCE(td.tds, 0) = 0 AND cs.usage_index_score < 40
              THEN 'QUIET -- neither trusted nor scoring'
         ELSE 'MIXED'
       END                             AS correlation_flag
FROM gold.v_nfl_player_usage_index cs
LEFT JOIN td ON td.season = cs.season AND td.player_id = cs.player_id
WHERE cs.usage_role <> 'INSUFFICIENT SAMPLE'
ORDER BY cs.usage_index_score DESC;