-- Run this in Supabase SQL Editor.
-- Purpose: adapt the already-created schema `football` to the Python project.

BEGIN;

-- -----------------------------
-- 1) Add missing columns
-- -----------------------------

-- Leagues: external IDs / codes
ALTER TABLE football.leagues
  ADD COLUMN IF NOT EXISTS source_id text,
  ADD COLUMN IF NOT EXISTS code text;

CREATE UNIQUE INDEX IF NOT EXISTS ux_leagues_code
  ON football.leagues(code)
  WHERE code IS NOT NULL;

CREATE INDEX IF NOT EXISTS ix_leagues_source_id
  ON football.leagues(source_id)
  WHERE source_id IS NOT NULL;

-- Seasons: the app expects start_date/end_date/is_current
ALTER TABLE football.seasons
  ADD COLUMN IF NOT EXISTS start_date date,
  ADD COLUMN IF NOT EXISTS end_date date,
  ADD COLUMN IF NOT EXISTS is_current integer DEFAULT 1;

-- Backfill from your earlier columns if they exist
UPDATE football.seasons
SET start_date = COALESCE(start_date, date_start),
    end_date   = COALESCE(end_date,   date_end)
WHERE (start_date IS NULL OR end_date IS NULL);

-- Teams: external IDs / codes
ALTER TABLE football.teams
  ADD COLUMN IF NOT EXISTS source_id text,
  ADD COLUMN IF NOT EXISTS code text;

CREATE UNIQUE INDEX IF NOT EXISTS ux_teams_code
  ON football.teams(code)
  WHERE code IS NOT NULL;

CREATE INDEX IF NOT EXISTS ix_teams_source_id
  ON football.teams(source_id)
  WHERE source_id IS NOT NULL;

-- Matches: app expects source_id + source_url, plus name mapping to kickoff_at/ft_home/ft_away
ALTER TABLE football.matches
  ADD COLUMN IF NOT EXISTS source_id text,
  ADD COLUMN IF NOT EXISTS source_url text;

CREATE UNIQUE INDEX IF NOT EXISTS ux_matches_source_id
  ON football.matches(source_id)
  WHERE source_id IS NOT NULL;

-- Odds: extend existing odds_1x2 so the app can store extra markets later
ALTER TABLE football.odds_1x2
  ADD COLUMN IF NOT EXISTS over_2_5 double precision,
  ADD COLUMN IF NOT EXISTS under_2_5 double precision,
  ADD COLUMN IF NOT EXISTS both_score_yes double precision,
  ADD COLUMN IF NOT EXISTS both_score_no double precision;

-- -----------------------------
-- 2) Create missing tables used by agents / ML
-- -----------------------------

-- Match events (optional, but the ORM has it)
CREATE TABLE IF NOT EXISTS football.match_events (
  id          BIGSERIAL PRIMARY KEY,
  match_id    BIGINT NOT NULL REFERENCES football.matches(id) ON DELETE CASCADE,
  event_type  text NOT NULL,
  team_id     BIGINT REFERENCES football.teams(id),
  player_name text,
  minute      integer,
  description text
);
CREATE INDEX IF NOT EXISTS ix_match_events_match_id ON football.match_events(match_id);

-- Daily team form aggregates
CREATE TABLE IF NOT EXISTS football.team_form_daily (
  id             BIGSERIAL PRIMARY KEY,
  team_id        BIGINT NOT NULL REFERENCES football.teams(id) ON DELETE CASCADE,
  date           date NOT NULL,
  matches_played integer DEFAULT 0,
  wins           integer DEFAULT 0,
  draws          integer DEFAULT 0,
  losses         integer DEFAULT 0,
  goals_for      integer DEFAULT 0,
  goals_against  integer DEFAULT 0,
  points         integer DEFAULT 0,
  home_matches   integer DEFAULT 0,
  home_wins      integer DEFAULT 0,
  away_matches   integer DEFAULT 0,
  away_wins      integer DEFAULT 0,
  created_at     timestamptz DEFAULT now(),
  UNIQUE(team_id, date)
);
CREATE INDEX IF NOT EXISTS ix_team_form_daily_team_date ON football.team_form_daily(team_id, date);

-- Feature snapshots for matches
CREATE TABLE IF NOT EXISTS football.features (
  id                   BIGSERIAL PRIMARY KEY,
  match_id             BIGINT NOT NULL REFERENCES football.matches(id) ON DELETE CASCADE,
  calculated_at        timestamptz DEFAULT now(),
  home_goals_for_avg   double precision,
  home_goals_against_avg double precision,
  home_points_avg      double precision,
  home_form_last_n     double precision,
  home_elo             double precision,
  home_days_rest       double precision,
  away_goals_for_avg   double precision,
  away_goals_against_avg double precision,
  away_points_avg      double precision,
  away_form_last_n     double precision,
  away_elo             double precision,
  away_days_rest       double precision,
  h2h_home_wins        integer DEFAULT 0,
  h2h_draws            integer DEFAULT 0,
  h2h_away_wins        integer DEFAULT 0,
  feature_vector       text
);
CREATE INDEX IF NOT EXISTS ix_features_match_id ON football.features(match_id);

-- ML model registry
CREATE TABLE IF NOT EXISTS football.models (
  id              BIGSERIAL PRIMARY KEY,
  version         text NOT NULL UNIQUE,
  model_type      text NOT NULL,
  parameters      jsonb,
  train_accuracy  double precision,
  test_accuracy   double precision,
  train_logloss   double precision,
  test_logloss    double precision,
  train_brier     double precision,
  test_brier      double precision,
  artifact_path   text,
  trained_at      timestamptz DEFAULT now(),
  trained_on_matches integer,
  is_active       integer DEFAULT 1,
  notes           text
);

-- Predictions
CREATE TABLE IF NOT EXISTS football.predictions (
  id             BIGSERIAL PRIMARY KEY,
  match_id       BIGINT NOT NULL REFERENCES football.matches(id) ON DELETE CASCADE,
  model_id       BIGINT NOT NULL REFERENCES football.models(id) ON DELETE RESTRICT,
  prob_home_win  double precision NOT NULL,
  prob_draw      double precision NOT NULL,
  prob_away_win  double precision NOT NULL,
  prob_over_2_5  double precision,
  prob_under_2_5 double precision,
  prob_both_score double precision,
  calculated_at  timestamptz DEFAULT now(),
  confidence     double precision
);
CREATE INDEX IF NOT EXISTS ix_predictions_match_id ON football.predictions(match_id);
CREATE INDEX IF NOT EXISTS ix_predictions_model_id ON football.predictions(model_id);

-- LLM analysis columns
ALTER TABLE football.predictions
  ADD COLUMN IF NOT EXISTS analysis_text text,
  ADD COLUMN IF NOT EXISTS analysis_generated_at timestamptz;

CREATE INDEX IF NOT EXISTS ix_predictions_analysis_generated_at ON football.predictions(analysis_generated_at);

-- Agent runs
CREATE TABLE IF NOT EXISTS football.agent_runs (
  id               BIGSERIAL PRIMARY KEY,
  agent_name       text NOT NULL,
  status           text DEFAULT 'pending',
  started_at       timestamptz DEFAULT now(),
  finished_at      timestamptz,
  error            text,
  logs             text,
  matches_processed integer DEFAULT 0,
  records_created   integer DEFAULT 0,
  records_updated   integer DEFAULT 0
);
CREATE INDEX IF NOT EXISTS ix_agent_runs_agent_name ON football.agent_runs(agent_name);
CREATE INDEX IF NOT EXISTS ix_agent_runs_status ON football.agent_runs(status);

COMMIT;
