nhl-schedule/migrations/Version20251220194258.php

<?php

declare(strict_types=1);

namespace DoctrineMigrations;

use Doctrine\DBAL\Schema\Schema;
use Doctrine\Migrations\AbstractMigration;
use function App\Stubs\getCSV; # should be copied here

final class Version20251220194258 extends AbstractMigration
{
    public function getDescription(): string
    {
        return 'Create basic tables';
    }

    public function up(Schema $schema): void
    {
        $this->addSql(<<<SQL
            CREATE TABLE "provider"(
                "id" UUID NOT NULL PRIMARY KEY,
                "name" VARCHAR(255) NOT NULL
            );
        SQL);

        $this->addSql(<<<SQL
            CREATE TABLE "league"(
                "id" UUID NOT NULL PRIMARY KEY,
                "provider_id" UUID NOT NULL,
                "provider_league_id" VARCHAR(255) NOT NULL,
                "name" VARCHAR(255) NOT NULL,
                                 
                CONSTRAINT "fk_league_provider"
                    FOREIGN KEY ("provider_id")
                    REFERENCES "provider" ("id")
            );
        SQL);

        $this->addSql(<<<SQL
            CREATE INDEX "idx_league_provider_id"
                ON "league" ("provider_id");
        SQL);

        $this->addSql(<<<SQL
            CREATE TABLE "season"(
                "id" UUID NOT NULL PRIMARY KEY,
                "provider_id" UUID NOT NULL,
                "provider_season_id" VARCHAR(255) NOT NULL,
                "year" INT NOT NULL,
                "kind" VARCHAR(255) NOT NULL,
                "league_id" UUID NOT NULL,
                
                CONSTRAINT "fk_season_provider"
                    FOREIGN KEY ("provider_id")
                    REFERENCES "provider" ("id"),

                CONSTRAINT "fk_season_league"
                    FOREIGN KEY ("league_id")
                    REFERENCES "league" ("id")
            );
        SQL);
        $this->addSql(<<<SQL
            CREATE INDEX "idx_season_provider_id"
                ON "season" ("provider_id");
        SQL);

        $this->addSql(<<<SQL
            CREATE INDEX "idx_season_league_id"
                ON "season" ("league_id");
        SQL);

        $this->addSql(<<<SQL
            CREATE TABLE "team"(
                "id" UUID NOT NULL PRIMARY KEY,
                "provider_id" UUID NOT NULL,
                "provider_team_id" VARCHAR(255) NOT NULL,
                "name" VARCHAR(255) NOT NULL,
                "alias" VARCHAR(255) NOT NULL,
                "active" BOOLEAN NOT NULL DEFAULT TRUE,
                
                CONSTRAINT "fk_team_provider"
                    FOREIGN KEY ("provider_id")
                    REFERENCES "provider" ("id")
            );
        SQL);

        $this->addSql(<<<SQL
            CREATE INDEX "idx_team_provider_id"
                ON "team" ("provider_id");
        SQL);

        $this->addSql(<<<SQL
            CREATE TABLE "game"(
                "id" UUID NOT NULL PRIMARY KEY,
                "provider_id" UUID NOT NULL,
                "provider_game_id" VARCHAR(255) NOT NULL,
                "start_time_scheduled" TIMESTAMPTZ NOT NULL,
                "end_time_scheduled" TIMESTAMPTZ,
                "season_id" UUID NOT NULL,
                "home_team_id" UUID NOT NULL,
                "away_team_id" UUID NOT NULL,
                "venue" VARCHAR(255) NOT NULL,
                
                CONSTRAINT "fk_game_provider"
                    FOREIGN KEY ("provider_id")
                    REFERENCES "provider" ("id"),
               
               CONSTRAINT "fk_game_season"
                    FOREIGN KEY ("season_id")
                    REFERENCES "season" ("id"),

               CONSTRAINT "fk_game_home_team"
                    FOREIGN KEY ("home_team_id")
                    REFERENCES "team" ("id"),
                    
               CONSTRAINT "fk_game_away_team"
                    FOREIGN KEY ("away_team_id")
                    REFERENCES "team" ("id")
            );
        SQL);

        $this->addSql(<<<SQL
            CREATE INDEX "idx_game_provider_id"
                ON "game" ("provider_id");
        SQL);

        $this->addSql(<<<SQL
            CREATE INDEX "idx_game_season_id"
                ON "game" ("season_id");
        SQL);

        $this->addSql(<<<SQL
            CREATE INDEX "idx_game_home_team_id"
                ON "game" ("home_team_id");
        SQL);

        $this->addSql(<<<SQL
            CREATE INDEX "idx_game_away_team_id"
                ON "game" ("away_team_id");
        SQL);

        $this->loadBootstrapData();
    }

    public function down(Schema $schema): void
    {
        $this->addSql(/** @lang SQL */ "DROP TABLE IF EXISTS game");
        $this->addSql(/** @lang SQL */ "DROP TABLE IF EXISTS season");
        $this->addSql(/** @lang SQL */ "DROP TABLE IF EXISTS league");
        $this->addSql(/** @lang SQL */ "DROP TABLE IF EXISTS team");
        $this->addSql(/** @lang SQL */ "DROP TABLE IF EXISTS provider");
    }

    private function loadBootstrapData(): void
    {
        $values = [];
        $params = [];
        foreach (getCSV(__DIR__.'/../stubs/Version20251220194258/providers.csv') as $row) {
            $values[] = "(?, ?)";
            $params[] = $row['id'];
            $params[] = $row['name'];
        }

        $this->addSql(
            'INSERT INTO "provider" ("id", "name") VALUES '
            . implode(', ', $values)
            . ' ON CONFLICT ("id") DO NOTHING',
            $params
        );

        $values = [];
        $params = [];
        foreach (getCSV(__DIR__.'/../stubs/Version20251220194258/leagues.csv') as $row) {
            $values[] = "(?, ?, ?, ?)";
            $params[] = $row['id'];
            $params[] = $row['provider_id'];
            $params[] = $row['provider_league_id'];
            $params[] = $row['name'];
        }

        $this->addSql(
            'INSERT INTO "league" ("id", "provider_id", "provider_league_id", "name") VALUES '
            . implode(', ', $values)
            . ' ON CONFLICT ("id") DO NOTHING',
            $params
        );

        $values = [];
        $params = [];
        foreach (getCSV(__DIR__.'/../stubs/Version20251220194258/seasons.csv') as $row) {
            $values[] = "(?, ?, ?, ?, ?, ?)";
            $params[] = $row['id'];
            $params[] = $row['provider_id'];
            $params[] = $row['provider_season_id'];
            $params[] = $row['year'];
            $params[] = $row['kind'];
            $params[] = $row['league_id'];
        }

        $this->addSql(
            'INSERT INTO "season" ("id", "provider_id", "provider_season_id", "year", "kind", "league_id") VALUES '
            . implode(', ', $values)
            . ' ON CONFLICT ("id") DO NOTHING',
            $params
        );

        $values = [];
        $params = [];
        foreach (getCSV(__DIR__.'/../stubs/Version20251220194258/teams.csv') as $row) {
            $values[] = "(?, ?, ?, ?, ?, ?)";
            $params[] = $row['id'];
            $params[] = $row['provider_id'];
            $params[] = $row['provider_team_id'];
            $params[] = $row['name'];
            $params[] = $row['alias'];
            $params[] = $row['active'];
        }

        $this->addSql(
            'INSERT INTO "team" ("id", "provider_id", "provider_team_id", "name", "alias", "active") VALUES '
            . implode(', ', $values)
            . ' ON CONFLICT ("id") DO NOTHING',
            $params
        );
    }
}