-- =====================================================================
-- Inspection views for Dixon-Coles model + betting odds tables (bookie)
-- Assumes tables already exist. Safe to re-run (CREATE OR REPLACE).
--
-- psql -U <user> -d <db> -f sql/model_and_odds_views.sql
-- =====================================================================

-- Predictions with match & team context
CREATE OR REPLACE VIEW bookie.v_match_prediction_detail AS
SELECT
    mp.prediction_id,
    mp.match_id,
    m.kickoff_at,
    m.status,
    ht.name                                           AS home_team,
    at.name                                           AS away_team,
    l.name                                            AS league,
    l.country,
    t.name                                            AS team_name,
    (m.home_team_id = t.team_id)                      AS is_home,
    mp.predicted_goals,
    mp.predicted_shots_total,
    mp.predicted_shots_on_target,
    mp.predicted_corners,
    mp.predicted_fouls,
    mp.predicted_yellow_cards,
    mp.model_trained_at,
    mp.half_life_days,
    mp.predicted_at
FROM bookie.match_prediction mp
JOIN bookie.match m ON m.match_id = mp.match_id
JOIN bookie.team t ON t.team_id = mp.team_id
JOIN bookie.team ht ON ht.team_id = m.home_team_id
JOIN bookie.team at ON at.team_id = m.away_team_id
JOIN bookie.matchday md ON md.matchday_id = m.matchday_id
JOIN bookie.season s ON s.season_id = md.season_id
JOIN bookie.league l ON l.league_id = s.league_id
ORDER BY m.kickoff_at DESC, mp.match_id, is_home DESC;

-- League Dixon-Coles parameters
CREATE OR REPLACE VIEW bookie.v_league_model_param_detail AS
SELECT
    l.league_id,
    l.name                                            AS league_name,
    l.country,
    l.tier_level,
    p.home_advantage,
    p.rho,
    p.avg_home_goals,
    p.avg_away_goals,
    p.matches_used,
    p.half_life_days,
    p.trained_at,
    (SELECT COUNT(*) FROM bookie.team_strength ts WHERE ts.league_id = l.league_id) AS team_strength_count
FROM bookie.league l
LEFT JOIN bookie.league_model_param p ON p.league_id = l.league_id
ORDER BY l.country, l.name;

-- Team attack/defense with readable multipliers
CREATE OR REPLACE VIEW bookie.v_team_strength_detail AS
SELECT
    ts.team_strength_id,
    ts.team_id,
    t.name                                            AS team_name,
    ts.league_id,
    l.name                                            AS league_name,
    l.country,
    ts.log_attack,
    ts.log_defense,
    ROUND(EXP(ts.log_attack)::numeric, 4)             AS attack_factor,
    ROUND(EXP(ts.log_defense)::numeric, 4)            AS defense_factor,
    ts.matches_used,
    ts.trained_at
FROM bookie.team_strength ts
JOIN bookie.team t ON t.team_id = ts.team_id
JOIN bookie.league l ON l.league_id = ts.league_id
ORDER BY l.country, l.name, attack_factor DESC;

-- 1X2 odds per bookmaker with match context
CREATE OR REPLACE VIEW bookie.v_match_odds_detail AS
SELECT
    mo.match_odds_id,
    mo.match_id,
    m.kickoff_at,
    m.status,
    ht.name                                           AS home_team,
    at.name                                           AS away_team,
    l.name                                            AS league,
    mo.bookmaker,
    mo.home_odds,
    mo.draw_odds,
    mo.away_odds,
    ROUND((1 / mo.home_odds)::numeric, 4)             AS home_implied,
    ROUND((1 / mo.draw_odds)::numeric, 4)             AS draw_implied,
    ROUND((1 / mo.away_odds)::numeric, 4)             AS away_implied,
    mo.total_line,
    mo.over_odds,
    mo.under_odds,
    CASE WHEN mo.over_odds IS NOT NULL THEN ROUND((1 / mo.over_odds)::numeric, 4) END AS over_implied,
    CASE WHEN mo.under_odds IS NOT NULL THEN ROUND((1 / mo.under_odds)::numeric, 4) END AS under_implied,
    mo.fetched_at
FROM bookie.match_odds mo
JOIN bookie.match m ON m.match_id = mo.match_id
JOIN bookie.team ht ON ht.team_id = m.home_team_id
JOIN bookie.team at ON at.team_id = m.away_team_id
JOIN bookie.matchday md ON md.matchday_id = m.matchday_id
JOIN bookie.season s ON s.season_id = md.season_id
JOIN bookie.league l ON l.league_id = s.league_id
ORDER BY m.kickoff_at DESC, mo.match_id, mo.bookmaker;

-- Averaged 1X2 odds per match (what predict uses for market blending)
CREATE OR REPLACE VIEW bookie.v_match_odds_consensus AS
SELECT
    mo.match_id,
    m.kickoff_at,
    m.status,
    ht.name                                           AS home_team,
    at.name                                           AS away_team,
    l.name                                            AS league,
    COUNT(*)                                          AS bookmaker_count,
    ROUND(AVG(mo.home_odds)::numeric, 3)              AS avg_home_odds,
    ROUND(AVG(mo.draw_odds)::numeric, 3)              AS avg_draw_odds,
    ROUND(AVG(mo.away_odds)::numeric, 3)              AS avg_away_odds,
    ROUND((1 / AVG(mo.home_odds))::numeric, 4)        AS home_implied,
    ROUND((1 / AVG(mo.draw_odds))::numeric, 4)        AS draw_implied,
    ROUND((1 / AVG(mo.away_odds))::numeric, 4)        AS away_implied,
    ROUND(AVG(mo.total_line)::numeric, 2)             AS avg_total_line,
    ROUND(AVG(mo.over_odds)::numeric, 3)              AS avg_over_odds,
    ROUND(AVG(mo.under_odds)::numeric, 3)             AS avg_under_odds,
    CASE WHEN AVG(mo.over_odds) IS NOT NULL
         THEN ROUND((1 / AVG(mo.over_odds))::numeric, 4) END AS over_implied,
    CASE WHEN AVG(mo.under_odds) IS NOT NULL
         THEN ROUND((1 / AVG(mo.under_odds))::numeric, 4) END AS under_implied,
    COUNT(mo.total_line)                              AS totals_bookmaker_count,
    MAX(mo.fetched_at)                                AS latest_fetched_at
FROM bookie.match_odds mo
JOIN bookie.match m ON m.match_id = mo.match_id
JOIN bookie.team ht ON ht.team_id = m.home_team_id
JOIN bookie.team at ON at.team_id = m.away_team_id
JOIN bookie.matchday md ON md.matchday_id = m.matchday_id
JOIN bookie.season s ON s.season_id = md.season_id
JOIN bookie.league l ON l.league_id = s.league_id
GROUP BY mo.match_id, m.kickoff_at, m.status, ht.name, at.name, l.name
ORDER BY m.kickoff_at DESC;

-- Team name aliases for odds API matching
CREATE OR REPLACE VIEW bookie.v_team_odds_alias_detail AS
SELECT
    a.team_id,
    t.name                                            AS team_name,
    a.odds_api_name,
    (a.odds_api_name = t.name)                        AS exact_name_match
FROM bookie.team_odds_alias a
JOIN bookie.team t ON t.team_id = a.team_id
ORDER BY t.name;

-- Extra markets (corners/cards O/U)
CREATE OR REPLACE VIEW bookie.v_match_extra_odds_detail AS
SELECT
    eo.match_extra_odds_id,
    eo.match_id,
    m.kickoff_at,
    ht.name                                           AS home_team,
    at.name                                           AS away_team,
    eo.market,
    eo.bookmaker,
    eo.line,
    eo.over_odds,
    eo.under_odds,
    eo.fetched_at
FROM bookie.match_extra_odds eo
JOIN bookie.match m ON m.match_id = eo.match_id
JOIN bookie.team ht ON ht.team_id = m.home_team_id
JOIN bookie.team at ON at.team_id = m.away_team_id
ORDER BY m.kickoff_at DESC, eo.match_id, eo.market, eo.bookmaker;

-- One-row-per-match snapshot for pre-match review (model + odds + prediction coverage)
CREATE OR REPLACE VIEW bookie.v_prematch_model_snapshot AS
SELECT
    m.match_id,
    m.kickoff_at,
    m.status,
    ht.name                                           AS home_team,
    at.name                                           AS away_team,
    l.league_id,
    l.name                                            AS league,
    l.country,
    s.name                                            AS season,
    lmp.home_advantage,
    lmp.rho,
    lmp.avg_home_goals,
    lmp.avg_away_goals,
    lmp.matches_used                                  AS model_matches_used,
    lmp.trained_at                                    AS model_trained_at,
    hs.log_attack                                     AS home_log_attack,
    hs.log_defense                                    AS home_log_defense,
    aws.log_attack                                    AS away_log_attack,
    aws.log_defense                                   AS away_log_defense,
    oc.bookmaker_count,
    oc.avg_home_odds,
    oc.avg_draw_odds,
    oc.avg_away_odds,
    oc.home_implied,
    oc.draw_implied,
    oc.away_implied,
    oc.avg_total_line,
    oc.avg_over_odds,
    oc.avg_under_odds,
    oc.over_implied,
    oc.under_implied,
    hp.predicted_goals                                AS home_pred_goals,
    ap.predicted_goals                                AS away_pred_goals,
    hp.predicted_at                                   AS home_predicted_at,
    ap.predicted_at                                   AS away_predicted_at
FROM bookie.match m
JOIN bookie.team ht ON ht.team_id = m.home_team_id
JOIN bookie.team at ON at.team_id = m.away_team_id
JOIN bookie.matchday md ON md.matchday_id = m.matchday_id
JOIN bookie.season s ON s.season_id = md.season_id
JOIN bookie.league l ON l.league_id = s.league_id
LEFT JOIN bookie.league_model_param lmp ON lmp.league_id = l.league_id
LEFT JOIN bookie.team_strength hs ON hs.team_id = m.home_team_id AND hs.league_id = l.league_id
LEFT JOIN bookie.team_strength aws ON aws.team_id = m.away_team_id AND aws.league_id = l.league_id
LEFT JOIN bookie.v_match_odds_consensus oc ON oc.match_id = m.match_id
LEFT JOIN bookie.match_prediction hp ON hp.match_id = m.match_id AND hp.team_id = m.home_team_id
LEFT JOIN bookie.match_prediction ap ON ap.match_id = m.match_id AND ap.team_id = m.away_team_id
ORDER BY m.kickoff_at DESC;