-- Football Predictor — MySQL schema
-- Run once:  mysql -u USER -p DBNAME < db/schema.sql
-- (or paste into phpMyAdmin on cPanel)

SET NAMES utf8mb4;
SET foreign_key_checks = 0;

CREATE TABLE IF NOT EXISTS leagues (
    id        INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    division  VARCHAR(8)   NOT NULL,          -- football-data code, e.g. E0
    code      VARCHAR(16)  NOT NULL,          -- our slug, e.g. ENG1
    name      VARCHAR(80)  NOT NULL,
    country   VARCHAR(60)  NOT NULL,
    UNIQUE KEY uq_div (division)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS teams (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    league_id  INT UNSIGNED NOT NULL,
    name       VARCHAR(100) NOT NULL,         -- exactly as football-data spells it
    UNIQUE KEY uq_team (league_id, name),
    CONSTRAINT fk_team_league FOREIGN KEY (league_id) REFERENCES leagues(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS matches (
    id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    league_id     INT UNSIGNED NOT NULL,
    season        VARCHAR(8)   NOT NULL,      -- e.g. 2627
    match_date    DATE         NOT NULL,
    match_time    TIME         NULL,
    home_team_id  INT UNSIGNED NOT NULL,
    away_team_id  INT UNSIGNED NOT NULL,
    status        ENUM('scheduled','played') NOT NULL DEFAULT 'scheduled',
    fthg          TINYINT UNSIGNED NULL,      -- full-time home goals
    ftag          TINYINT UNSIGNED NULL,      -- full-time away goals
    ftr           CHAR(1)      NULL,          -- H / D / A
    -- Pre-match / closing bookmaker odds (decimal). Used for value flags.
    odds_home     DECIMAL(6,2) NULL,
    odds_draw     DECIMAL(6,2) NULL,
    odds_away     DECIMAL(6,2) NULL,
    odds_source   VARCHAR(20)  NULL,          -- which column set we used
    created_at    TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    updated_at    TIMESTAMP    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_match (league_id, match_date, home_team_id, away_team_id),
    KEY idx_status (status),
    KEY idx_league_date (league_id, match_date),
    CONSTRAINT fk_match_league FOREIGN KEY (league_id) REFERENCES leagues(id),
    CONSTRAINT fk_match_home   FOREIGN KEY (home_team_id) REFERENCES teams(id),
    CONSTRAINT fk_match_away   FOREIGN KEY (away_team_id) REFERENCES teams(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS predictions (
    id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    match_id      BIGINT UNSIGNED NOT NULL,
    model         VARCHAR(30)  NOT NULL DEFAULT 'dixon_coles',
    p_home        DECIMAL(5,4) NOT NULL,      -- P(home win)
    p_draw        DECIMAL(5,4) NOT NULL,
    p_away        DECIMAL(5,4) NOT NULL,
    xg_home       DECIMAL(5,3) NOT NULL,      -- expected goals home
    xg_away       DECIMAL(5,3) NOT NULL,
    top_scores    JSON         NULL,          -- [{"h":2,"a":1,"p":0.09}, ...]
    value_home    DECIMAL(5,4) NULL,          -- edge vs bookmaker (model - implied), NULL if no odds
    value_draw    DECIMAL(5,4) NULL,
    value_away    DECIMAL(5,4) NULL,
    generated_at  TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_pred (match_id, model),
    CONSTRAINT fk_pred_match FOREIGN KEY (match_id) REFERENCES matches(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET foreign_key_checks = 1;