-- =====================================================================
-- OpenAI match predictions + comparison views
-- Run against the "bookie" schema (Postgres).
-- Safe to run multiple times (IF NOT EXISTS / CREATE OR REPLACE).
-- =====================================================================

-- ---------------------------------------------------------------------
-- 1) Table: one row PER TEAM PER EXECUTION of the OpenAI predictor.
--    History is kept (no unique constraint) so you can inspect how
--    predictions change across runs; the views below always pick the
--    most recent row per (match, team).
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS bookie.match_prediction_openai (
    prediction_id            SERIAL PRIMARY KEY,
    match_id                 INT NOT NULL REFERENCES bookie.match(match_id),
    team_id                  INT NOT NULL REFERENCES bookie.team(team_id),

    predicted_goals          NUMERIC(6,2),
    goals_low                NUMERIC(6,2),
    goals_high               NUMERIC(6,2),

    predicted_shots_on_target NUMERIC(6,2),
    shots_on_target_low       NUMERIC(6,2),
    shots_on_target_high      NUMERIC(6,2),

    predicted_corners        NUMERIC(6,2),
    corners_low              NUMERIC(6,2),
    corners_high             NUMERIC(6,2),

    predicted_fouls          NUMERIC(6,2),
    fouls_low                NUMERIC(6,2),
    fouls_high               NUMERIC(6,2),

    predicted_yellow_cards   NUMERIC(6,2),
    yellow_cards_low         NUMERIC(6,2),
    yellow_cards_high        NUMERIC(6,2),

    predicted_red_cards      NUMERIC(6,2),
    red_cards_low            NUMERIC(6,2),
    red_cards_high           NUMERIC(6,2),

    confidence               TEXT,
    reasoning                TEXT,
    model                    TEXT,
    predicted_at             TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_match_prediction_openai_match_team
    ON bookie.match_prediction_openai (match_id, team_id, predicted_at DESC);

-- ---------------------------------------------------------------------
-- 2) Latest OpenAI prediction per (match, team).
-- ---------------------------------------------------------------------
CREATE OR REPLACE VIEW bookie.v_match_prediction_openai_latest AS
SELECT DISTINCT ON (match_id, team_id) *
FROM bookie.match_prediction_openai
ORDER BY match_id, team_id, predicted_at DESC;

-- ---------------------------------------------------------------------
-- 3) Side-by-side comparison: LLM (bookie.match_prediction) vs OpenAI
--    vs ACTUAL, one row per (match, team). Actual goals come from the
--    final score; the other actuals come from bookie.match_team_stats.
-- ---------------------------------------------------------------------
CREATE OR REPLACE VIEW bookie.v_prediction_comparison AS
SELECT
    m.match_id,
    t.team_id,
    t.name                                            AS team_name,
    m.kickoff_at,
    m.status,
    (m.home_team_id = t.team_id)                      AS is_home,
    opp.name                                          AS opponent_name,

    -- goals
    lp.predicted_goals                                AS llm_goals,
    op.predicted_goals                                AS openai_goals,
    CASE WHEN m.home_team_id = t.team_id THEN m.home_score_ft ELSE m.away_score_ft END AS actual_goals,

    -- shots on target
    lp.predicted_shots_on_target                      AS llm_shots_on_target,
    op.predicted_shots_on_target                      AS openai_shots_on_target,
    mts.shots_on_target                               AS actual_shots_on_target,

    -- corners
    lp.predicted_corners                              AS llm_corners,
    op.predicted_corners                              AS openai_corners,
    mts.corners                                       AS actual_corners,

    -- fouls
    lp.predicted_fouls                                AS llm_fouls,
    op.predicted_fouls                                AS openai_fouls,
    mts.fouls                                         AS actual_fouls,

    -- yellow cards
    lp.predicted_yellow_cards                         AS llm_yellow_cards,
    op.predicted_yellow_cards                         AS openai_yellow_cards,
    mts.yellow_cards                                  AS actual_yellow_cards,

    op.confidence                                     AS openai_confidence,
    op.model                                          AS openai_model
FROM bookie.match m
JOIN bookie.team t
    ON t.team_id IN (m.home_team_id, m.away_team_id)
JOIN bookie.team opp
    ON opp.team_id = CASE WHEN m.home_team_id = t.team_id THEN m.away_team_id ELSE m.home_team_id END
LEFT JOIN bookie.match_prediction lp
    ON lp.match_id = m.match_id AND lp.team_id = t.team_id
LEFT JOIN bookie.v_match_prediction_openai_latest op
    ON op.match_id = m.match_id AND op.team_id = t.team_id
LEFT JOIN bookie.match_team_stats mts
    ON mts.match_id = m.match_id AND mts.team_id = t.team_id
WHERE lp.prediction_id IS NOT NULL OR op.prediction_id IS NOT NULL;

-- ---------------------------------------------------------------------
-- 4) Accuracy summary: mean absolute error of each model vs actual,
--    per metric, over finished matches only. Lower = better.
-- ---------------------------------------------------------------------
CREATE OR REPLACE VIEW bookie.v_prediction_accuracy AS
WITH c AS (
    SELECT * FROM bookie.v_prediction_comparison WHERE status = 'finished'
)
SELECT metric,
       ROUND(AVG(ABS(llm_pred    - actual)), 3) AS llm_mae,
       ROUND(AVG(ABS(openai_pred - actual)), 3) AS openai_mae,
       COUNT(*) FILTER (WHERE llm_pred    IS NOT NULL AND actual IS NOT NULL) AS llm_samples,
       COUNT(*) FILTER (WHERE openai_pred IS NOT NULL AND actual IS NOT NULL) AS openai_samples
FROM (
    SELECT 'goals'           AS metric, llm_goals           AS llm_pred, openai_goals           AS openai_pred, actual_goals           AS actual FROM c
    UNION ALL
    SELECT 'shots_on_target',           llm_shots_on_target,            openai_shots_on_target,            actual_shots_on_target            FROM c
    UNION ALL
    SELECT 'corners',                   llm_corners,                    openai_corners,                    actual_corners                    FROM c
    UNION ALL
    SELECT 'fouls',                     llm_fouls,                      openai_fouls,                      actual_fouls                      FROM c
    UNION ALL
    SELECT 'yellow_cards',              llm_yellow_cards,               openai_yellow_cards,               actual_yellow_cards               FROM c
) x
GROUP BY metric
ORDER BY metric;