-- Create TeamGameMetric
CREATE TABLE IF NOT EXISTS "TeamGameMetric" (
  "id" BIGSERIAL PRIMARY KEY,
  "league" TEXT NOT NULL,
  "season" INTEGER NOT NULL,
  "gameKey" TEXT NOT NULL,
  "gameDate" TIMESTAMP(3) NOT NULL,
  "team" TEXT NOT NULL,
  "opponent" TEXT,
  "statKey" TEXT NOT NULL,
  "value" DOUBLE PRECISION NOT NULL,
  "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS "TeamGameMetric_league_season_statKey_idx" ON "TeamGameMetric" ("league", "season", "statKey");
CREATE INDEX IF NOT EXISTS "TeamGameMetric_league_team_statKey_gameDate_idx" ON "TeamGameMetric" ("league", "team", "statKey", "gameDate");
CREATE UNIQUE INDEX IF NOT EXISTS "TeamGameMetric_league_season_gameKey_team_statKey_key" ON "TeamGameMetric" ("league", "season", "gameKey", "team", "statKey");

-- Create UFC tables
CREATE TABLE IF NOT EXISTS "UfcEvent" (
  "id" BIGSERIAL PRIMARY KEY,
  "eventId" TEXT NOT NULL,
  "name" TEXT NOT NULL,
  "date" TIMESTAMP(3),
  "location" TEXT,
  "raw" JSONB,
  "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updatedAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE UNIQUE INDEX IF NOT EXISTS "UfcEvent_eventId_key" ON "UfcEvent" ("eventId");

CREATE TABLE IF NOT EXISTS "UfcFighter" (
  "id" BIGSERIAL PRIMARY KEY,
  "fighterId" TEXT NOT NULL,
  "name" TEXT NOT NULL,
  "nickname" TEXT,
  "heightIn" DOUBLE PRECISION,
  "weightLb" DOUBLE PRECISION,
  "reachIn" DOUBLE PRECISION,
  "stance" TEXT,
  "wins" INTEGER,
  "losses" INTEGER,
  "draws" INTEGER,
  "belt" TEXT,
  "raw" JSONB,
  "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updatedAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE UNIQUE INDEX IF NOT EXISTS "UfcFighter_fighterId_key" ON "UfcFighter" ("fighterId");

CREATE TABLE IF NOT EXISTS "UfcFight" (
  "id" BIGSERIAL PRIMARY KEY,
  "fightId" TEXT NOT NULL,
  "eventId" TEXT NOT NULL,
  "fighter1Id" TEXT NOT NULL,
  "fighter2Id" TEXT NOT NULL,
  "fighter1Name" TEXT,
  "fighter2Name" TEXT,
  "weightClass" TEXT,
  "method" TEXT,
  "round" INTEGER,
  "fightTime" TEXT,
  "result1" TEXT,
  "result2" TEXT,
  "referee" TEXT,
  "methodDetails" TEXT,
  "raw" JSONB,
  "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updatedAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE UNIQUE INDEX IF NOT EXISTS "UfcFight_fightId_key" ON "UfcFight" ("fightId");

CREATE TABLE IF NOT EXISTS "UfcOdds" (
  "id" BIGSERIAL PRIMARY KEY,
  "fightUrl" TEXT NOT NULL,
  "fighter1" TEXT,
  "fighter2" TEXT,
  "odds1" DOUBLE PRECISION,
  "odds2" DOUBLE PRECISION,
  "f1KoOdds" DOUBLE PRECISION,
  "f2KoOdds" DOUBLE PRECISION,
  "f1SubOdds" DOUBLE PRECISION,
  "f2SubOdds" DOUBLE PRECISION,
  "f1DecOdds" DOUBLE PRECISION,
  "f2DecOdds" DOUBLE PRECISION,
  "eventDate" TIMESTAMP(3),
  "addingDate" TIMESTAMP(3),
  "source" TEXT,
  "region" TEXT,
  "raw" JSONB,
  "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updatedAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE UNIQUE INDEX IF NOT EXISTS "UfcOdds_fightUrl_key" ON "UfcOdds" ("fightUrl");
