sorarebuddy/db/migrations/00001_init.sql

-- +goose Up
    BEGIN;

   CREATE TABLE IF NOT EXISTS zones (id SERIAL PRIMARY KEY, display_name TEXT NOT NULL);

   INSERT INTO zones (display_name)
   VALUES ('Champions');

   INSERT INTO zones (display_name)
   VALUES ('Contenders');

   INSERT INTO zones (display_name)
   VALUES ('Challengers');

   CREATE TABLE IF NOT EXISTS countries (
          slug TEXT PRIMARY KEY,
          code TEXT NOT NULL,
          display_name TEXT NOT NULL,
          three_letter_code TEXT NOT NULL,
          flag_flat_64_url TEXT NOT NULL,
          flag_flat_32_url TEXT NOT NULL,
          flag_round_64_url TEXT NOT NULL,
          flag_round_32_url TEXT NOT NULL
          );

   CREATE TABLE IF NOT EXISTS competitions (
          slug TEXT PRIMARY KEY,
          display_name TEXT NOT NULL,
          country_slug TEXT NOT NULL,
          FOREIGN KEY (country_slug) REFERENCES countries (slug),
          competition_format TEXT NOT NULL,
          competition_type TEXT NOT NULL,
          picture_url TEXT NOT NULL,
          logo_url TEXT NOT NULL,
          zone_id INTEGER,
          FOREIGN KEY (zone_id) REFERENCES zones (id)
          );

   CREATE TABLE IF NOT EXISTS fixtures (
          slug TEXT PRIMARY KEY,
          display_name TEXT NOT NULL,
          state TEXT NOT NULL,
          start_date TIMESTAMPTZ NOT NULL,
          end_date TIMESTAMPTZ NOT NULL,
          game_week INTEGER NOT NULL UNIQUE
          );

   CREATE TABLE IF NOT EXISTS teams (
          slug TEXT PRIMARY KEY,
          display_name TEXT NOT NULL,
          country_slug TEXT NOT NULL,
          FOREIGN KEY (country_slug) REFERENCES countries (slug),
          domestic_league_slug TEXT,
          FOREIGN KEY (domestic_league_slug) REFERENCES competitions (slug),
          short_name TEXT NOT NULL,
          picture_url TEXT NOT NULL,
          team_type TEXT NOT NULL
          );

   CREATE TABLE IF NOT EXISTS games (
          id TEXT PRIMARY KEY,
          date TIMESTAMPTZ,
          coverage_status TEXT NOT NULL,
          low_coverage BOOLEAN NOT NULL,
          minutes INTEGER NOT NULL,
          period_type TEXT NOT NULL,
          scored BOOLEAN NOT NULL,
          status TEXT NOT NULL,
          competition_slug TEXT NOT NULL,
          FOREIGN KEY (competition_slug) REFERENCES competitions (slug),
          fixture_slug TEXT NOT NULL,
          FOREIGN KEY (fixture_slug) REFERENCES fixtures (slug),
          away_team_slug TEXT NOT NULL,
          away_goals INTEGER NOT NULL,
          away_extra_time_score INTEGER NOT NULL,
          away_penalty_score INTEGER NOT NULL,
          home_team_slug TEXT NOT NULL,
          home_goals INTEGER NOT NULL,
          home_extra_time_score INTEGER NOT NULL,
          home_penalty_score INTEGER NOT NULL,
          winner_team_slug TEXT
          );

   CREATE TABLE IF NOT EXISTS players (
          slug TEXT PRIMARY KEY,
          display_name TEXT NOT NULL,
          birth_date DATE NOT NULL,
          country_slug TEXT NOT NULL,
          FOREIGN KEY (country_slug) REFERENCES countries (slug),
          team_slug TEXT,
          FOREIGN KEY (team_slug) REFERENCES teams (slug),
          domestic_league_slug TEXT,
          FOREIGN KEY (domestic_league_slug) REFERENCES competitions (slug),
          avatar_url TEXT NOT NULL,
          field_position TEXT NOT NULL,
          status TEXT NOT NULL,
          shirt_number INTEGER NOT NULL
          );

   CREATE TABLE IF NOT EXISTS game_players (
          game_id TEXT NOT NULL,
          FOREIGN KEY (game_id) REFERENCES games (id),
          player_slug TEXT NOT NULL,
          FOREIGN KEY (player_slug) REFERENCES players (slug),
          status TEXT NOT NULL,
          team_slug TEXT NOT NULL,
          FOREIGN KEY (team_slug) REFERENCES teams (slug),
          PRIMARY KEY (game_id, player_slug)
          );

   CREATE INDEX idx_game_players_game_id_team_slug ON game_players (game_id, team_slug);

   CREATE TABLE IF NOT EXISTS game_player_scores (
          game_id TEXT NOT NULL,
          FOREIGN KEY (game_id) REFERENCES games (id),
          player_slug TEXT NOT NULL,
          FOREIGN KEY (player_slug) REFERENCES players (slug),
          FOREIGN KEY (game_id, player_slug) REFERENCES game_players (game_id, player_slug),
          PRIMARY KEY (game_id, player_slug),
          score NUMERIC NOT NULL,
          decisive_score NUMERIC NOT NULL,
          all_around_score NUMERIC NOT NULL,
          minutes_played INTEGER NOT NULL,
          game_started BOOLEAN NOT NULL,
          formation_place INTEGER NOT NULL,
          live BOOLEAN NOT NULL,
          on_game_sheet BOOLEAN NOT NULL,
          reviewed BOOLEAN NOT NULL,
          goal INTEGER NOT NULL,
          assist INTEGER NOT NULL,
          penalty_won INTEGER NOT NULL,
          clearance_off_line INTEGER NOT NULL,
          last_man_tackle INTEGER NOT NULL,
          penalty_save INTEGER NOT NULL,
          own_goal INTEGER NOT NULL,
          red_card BOOLEAN NOT NULL,
          error_lead_to_goal INTEGER NOT NULL,
          penalty_conceded INTEGER NOT NULL,
          yellow_card INTEGER NOT NULL,
          fouls INTEGER NOT NULL,
          fouled INTEGER NOT NULL,
          clean_sheet BOOLEAN NOT NULL,
          double_double BOOLEAN NOT NULL,
          triple_double BOOLEAN NOT NULL,
          triple_triple BOOLEAN NOT NULL,
          error_lead_to_shot INTEGER NOT NULL,
          saves INTEGER NOT NULL,
          saved_shot_from_inside_box INTEGER NOT NULL,
          good_high_claim INTEGER NOT NULL,
          punches INTEGER NOT NULL,
          diving_save INTEGER NOT NULL,
          diving_catch INTEGER NOT NULL,
          cross_not_claimed INTEGER NOT NULL,
          goalkeeper_smother INTEGER NOT NULL,
          six_second_violation INTEGER NOT NULL,
          keeper_sweeper INTEGER NOT NULL,
          goals_conceded INTEGER NOT NULL,
          effective_clearance INTEGER NOT NULL,
          won_tackle INTEGER NOT NULL,
          blocked_cross INTEGER NOT NULL,
          block INTEGER NOT NULL,
          possession_lost INTEGER NOT NULL,
          possession_won INTEGER NOT NULL,
          duel_lost INTEGER NOT NULL,
          duel_won INTEGER NOT NULL,
          interception INTEGER NOT NULL,
          accurate_pass INTEGER NOT NULL,
          accurate_final_third_pass INTEGER NOT NULL,
          accurate_long_ball INTEGER NOT NULL,
          long_pass_into_opposition INTEGER NOT NULL,
          missed_pass INTEGER NOT NULL,
          shot_on_target INTEGER NOT NULL,
          won_contest INTEGER NOT NULL,
          big_chance_created INTEGER NOT NULL,
          attempted_assist INTEGER NOT NULL,
          penalty_area_entries INTEGER NOT NULL,
          penalty_kick_missed INTEGER NOT NULL,
          big_chance_missed INTEGER NOT NULL
          );

   COMMIT;

-- +goose Down
     DROP TABLE IF EXISTS game_player_scores;

     DROP INDEX IF EXISTS idx_game_players_game_id_team_slug;

     DROP TABLE IF EXISTS game_players;

     DROP TABLE IF EXISTS players;

     DROP TABLE IF EXISTS games;

     DROP TABLE IF EXISTS teams;

     DROP TABLE IF EXISTS fixtures;

     DROP TABLE IF EXISTS competitions;

     DROP TABLE IF EXISTS countries;

     DROP TABLE IF EXISTS zones;