-- =============================================================================
-- frontal-lobe2 : NFL player props -- RotoWire (all 22 markets, 8 books)
-- =============================================================================
-- Source: https://www.rotowire.com/betting/nfl/player-props.php
-- Confirmed 2026-09-25:
--   - robots.txt allows /betting/ (only /users/login.php and /account/ disallowed)
--   - the real prop lines are RotoWire's OWN first-party betting tables, embedded
--     directly as JSON inside the page's static HTML (no JS execution needed to
--     read them -- a plain HTTP GET returns the data already in the response body)
--   - `isPaywalled = true` in the page's JS only gates RotoWire's own CSV *export*
--     button ("must be a paid subscriber" to click Export). It does NOT hide or
--     restrict the underlying data, which is visible to any visitor and is what
--     this schema loads.
--   - The page ALSO embeds a separate third-party "Props.Cash" demo widget with
--     its own weather-context feed (an unrelated Val Town endpoint). That widget
--     is NOT used as a source here -- only RotoWire's first-party tables are.
--
-- 22 markets confirmed populated (field counts from the raw page, 2026-09-25):
--   TD scorer:  firsttd, anytd, lasttd, twotd, threetd
--   Passing:    comp, passatt, passyds, passtd, intsthrown
--   Rushing:    rushyds, longrush
--   Receiving:  recs, recyds, rushrec (combined rush+rec yards)
--   Defense:    tackle, solo, assist, sack
--   Kicking:    kickpts, fgm, xpm
--
-- 8 sportsbooks tracked (not every book posts every market -- columns are
-- nullable and a book's absence on a given market/game is normal, not a bug):
--   betr, betrivers, caesars, draftkings, fanduel, fanatics, mgm, hardrock,
--   thescore, circasports
-- (note: some prop blocks show "fanduel", others show "fanatics" in the same
--  book-name slot -- RotoWire appears to have transitioned sportsbook partners
--  mid-market-rollout; both are stored as distinct book codes, not merged.)
--
-- GRAIN: one row per (player, game, book, market, pull). Lines move during the
-- week, so this is append-only like odds_snapshots in the CFB schema -- never
-- update or delete a row, always insert a new snapshot. Closing-line value (CLV)
-- analysis depends on keeping every snapshot, not just the latest.
--
-- Engine: DuckDB.
-- =============================================================================

CREATE SCHEMA IF NOT EXISTS gold;

CREATE TABLE IF NOT EXISTS gold.nfl_prop_market_catalog (
    market_slug    VARCHAR PRIMARY KEY,      -- e.g. 'passyds'
    market_name    VARCHAR NOT NULL,         -- e.g. 'Pass Yards'
    category       VARCHAR NOT NULL,         -- td_scorer | passing | rushing | receiving | defense | kicking
    line_type      VARCHAR NOT NULL          -- 'over_under' (has a numeric line) | 'moneyline_only' (TD scorer props)
);

INSERT OR REPLACE INTO gold.nfl_prop_market_catalog VALUES
    ('firsttd',    'First TD',            'td_scorer', 'moneyline_only'),
    ('anytd',      'Anytime TD',          'td_scorer', 'moneyline_only'),
    ('lasttd',     'Last TD',             'td_scorer', 'moneyline_only'),
    ('twotd',      'Score 2+ TDs',        'td_scorer', 'moneyline_only'),
    ('threetd',    'Score 3+ TDs',        'td_scorer', 'moneyline_only'),
    ('comp',       'Completions',        'passing',   'over_under'),
    ('passatt',    'Pass Attempts',      'passing',   'over_under'),
    ('passyds',    'Pass Yards',         'passing',   'over_under'),
    ('passtd',     'Pass TDs',           'passing',   'over_under'),
    ('intsthrown', 'Ints Thrown',        'passing',   'over_under'),
    ('rushyds',    'Rush Yards',         'rushing',   'over_under'),
    ('longrush',   'Longest Rush',       'rushing',   'over_under'),
    ('recs',       'Receptions',         'receiving', 'over_under'),
    ('recyds',     'Rec Yards',          'receiving', 'over_under'),
    ('rushrec',    'Rush + Rec Yards',   'receiving', 'over_under'),
    ('tackle',     'Total Tackles',      'defense',   'over_under'),
    ('solo',       'Solo Tackles',       'defense',   'over_under'),
    ('assist',     'Assisted Tackles',   'defense',   'over_under'),
    ('sack',       'Sacks',              'defense',   'over_under'),
    ('kickpts',    'Kicking Points',     'kicking',   'over_under'),
    ('fgm',        'Field Goals Made',   'kicking',   'over_under'),
    ('xpm',        'Extra Points Made',  'kicking',   'over_under');

-- Scoped to the books the user actually wants tracked (draftkings, fanduel,
-- caesars). RotoWire's source page also carries betr, betrivers, fanatics, mgm,
-- hardrock, thescore, circasports -- omitted here by request, not by limitation.
-- bet365 was requested too but is NOT present on this source at all (confirmed
-- 2026-09-25); it would need a different data source if required later.
--
-- KNOWN TEAM-CODE VARIANCE: RotoWire uses 'LAR' for the LA Rams; our schedule
-- table (nflverse-sourced) uses 'LA'. Confirmed this causes is_pregame to be
-- NULL for that one team (no match found, not a wrong guess) rather than
-- silently mapping it incorrectly. Fix properly (a small team-code alias
-- table) if this team's line movement is needed; not blocking otherwise.
CREATE TABLE IF NOT EXISTS gold.nfl_prop_book_catalog (
    book_slug   VARCHAR PRIMARY KEY,        -- e.g. 'draftkings'
    book_name   VARCHAR NOT NULL            -- e.g. 'DraftKings'
);
INSERT OR REPLACE INTO gold.nfl_prop_book_catalog VALUES
    ('draftkings',   'DraftKings'),
    ('fanduel',      'FanDuel'),
    ('caesars',      'Caesars');

-- One row per player/game/book/market/pull. Append-only.
CREATE TABLE IF NOT EXISTS gold.nfl_prop_line_rotowire (
    pull_id            VARCHAR NOT NULL,     -- one id per script run (this snapshot)
    fetched_at_utc     TIMESTAMPTZ NOT NULL, -- TIMESTAMPTZ, not TIMESTAMP: a naive TIMESTAMP
                                              -- silently stored LOCAL time under a column
                                              -- literally named _utc (confirmed real bug,
                                              -- fixed here -- old rows before this fix are
                                              -- off by the host's UTC offset)
    game_id            VARCHAR,              -- rotowire gameID
    player_id          VARCHAR,              -- rotowire playerID
    player_name        VARCHAR,
    team               VARCHAR,
    opponent           VARCHAR,              -- as shown, e.g. '@MIA' or 'SEA' (home/away encoded)
    market_slug        VARCHAR NOT NULL,     -- FK -> nfl_prop_market_catalog
    book_slug          VARCHAR NOT NULL,     -- FK -> nfl_prop_book_catalog
    line               DOUBLE,               -- numeric line (NULL for moneyline-only TD props)
    over_price_american INTEGER,             -- NULL for TD scorer props (no over/under side)
    under_price_american INTEGER,
    moneyline_american  INTEGER,             -- for TD scorer props: the single moneyline (e.g. firsttd odds)
    is_pregame          BOOLEAN,             -- true if fetched before that game's kickoff;
                                              -- NULL if kickoff time unknown at load time.
                                              -- Required so movement analysis never mixes
                                              -- pre-game and live in-game lines (confirmed
                                              -- real contamination: a fake "recyds 60.5 ->
                                              -- 174.5" move was actually two in-game snapshots
                                              -- of a game already underway)
    source_name        VARCHAR NOT NULL DEFAULT 'rotowire',
    PRIMARY KEY (pull_id, player_id, game_id, market_slug, book_slug)
);

CREATE INDEX IF NOT EXISTS ix_propline_rw_lookup
    ON gold.nfl_prop_line_rotowire (player_id, market_slug, fetched_at_utc);
CREATE INDEX IF NOT EXISTS ix_propline_rw_game
    ON gold.nfl_prop_line_rotowire (game_id, market_slug);

-- Latest snapshot per player/market/book -- what you'd actually compare against
-- your own model right now, without re-deriving "most recent" every query.
CREATE OR REPLACE VIEW gold.v_nfl_prop_line_latest AS
SELECT p.*
FROM gold.nfl_prop_line_rotowire p
WHERE p.fetched_at_utc = (
    SELECT max(p2.fetched_at_utc)
    FROM gold.nfl_prop_line_rotowire p2
    WHERE p2.player_id = p.player_id
      AND p2.market_slug = p.market_slug
      AND p2.book_slug = p.book_slug
      AND p2.game_id = p.game_id
);

-- Best (highest) over price and best (highest) under price across books, per
-- player/market/game -- the actual best-price shopping view for placing a bet.
CREATE OR REPLACE VIEW gold.v_nfl_prop_best_price AS
SELECT game_id, player_id, ANY_VALUE(player_name) AS player_name, market_slug,
       max(line)                                        AS line,             -- lines mostly agree across books; max as a representative value
       max(over_price_american)                         AS best_over_price,
       arg_max(book_slug, over_price_american)           AS best_over_book,
       max(under_price_american)                        AS best_under_price,
       arg_max(book_slug, under_price_american)          AS best_under_book,
       max(moneyline_american)                          AS best_moneyline_price,
       arg_max(book_slug, moneyline_american)            AS best_moneyline_book,
       count(DISTINCT book_slug)                         AS num_books
FROM gold.v_nfl_prop_line_latest
GROUP BY game_id, player_id, market_slug;

-- Line movement: first-seen vs latest, per player/market/book. This is the CLV
-- input -- compare where your model fired to the closing number, not just today's.
--
-- PRE-GAME ONLY (is_pregame = true), on purpose. Confirmed real contamination
-- otherwise: comparing two in-game snapshots of a game already underway
-- produced a fake "Drake London recyds 60.5 -> 174.5" move -- that's live
-- re-pricing during the game, not pre-game market movement. Never compare
-- across that boundary.
CREATE OR REPLACE VIEW gold.v_nfl_prop_line_movement AS
SELECT player_id, ANY_VALUE(player_name) AS player_name, market_slug, book_slug,
       min(fetched_at_utc)                                  AS first_seen_at,
       max(fetched_at_utc)                                  AS last_seen_at,
       arg_min(line, fetched_at_utc)                         AS opening_line,
       arg_max(line, fetched_at_utc)                         AS current_line,
       arg_max(line, fetched_at_utc) - arg_min(line, fetched_at_utc) AS line_delta,
       count(DISTINCT fetched_at_utc)                       AS num_snapshots
FROM gold.nfl_prop_line_rotowire
WHERE line IS NOT NULL AND is_pregame = true
GROUP BY player_id, market_slug, book_slug
HAVING count(DISTINCT fetched_at_utc) > 1;