Schema before attempting migration

changes.txt

Changes i made to the "after" schema:

1) changed IntensityTypes enum to IntensityCategory

2) added model Intensity, RoundDetail, enum REcoveryType

3) updated model Round to include references to the models for the RoundDetail table as follows:

  Intensity   Intensity[]
  Recovery    Recovery[]
  RoundDetail RoundDetail[]

result.txt

the following SQL was generated:

/*
  Warnings:

  - The values [Drill,Recovery,Endurance,Tempo,Threshold,SupraThreshold,Vo2Max,Anaerobic,Sprint,Power] on the enum `IntensityType` will be removed. If these variants are still used in the database, this will fail.

*/
-- CreateEnum
CREATE TYPE "IntensityCategory" AS ENUM ('Drill', 'Recovery', 'Endurance', 'Tempo', 'Threshold', 'SupraThreshold', 'Vo2Max', 'Anaerobic', 'Sprint', 'Power');

-- CreateEnum
CREATE TYPE "RecoveryType" AS ENUM ('Effort', 'Time', 'Sendoff');

-- AlterEnum
BEGIN;
CREATE TYPE "IntensityType_new" AS ENUM ('Effort', 'Pace', 'Profile');
ALTER TABLE "Intensity" ALTER COLUMN "type" TYPE "IntensityType_new" USING ("type"::text::"IntensityType_new");
ALTER TYPE "IntensityType" RENAME TO "IntensityType_old";
ALTER TYPE "IntensityType_new" RENAME TO "IntensityType";
DROP TYPE "IntensityType_old";
COMMIT;

-- CreateTable
CREATE TABLE "Intensity" (
    "id" SERIAL NOT NULL,
    "type" "IntensityType" NOT NULL,
    "description" TEXT,
    "time" TEXT,
    "profileValue" INTEGER,
    "roundId" INTEGER NOT NULL,

    CONSTRAINT "Intensity_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "Recovery" (
    "id" SERIAL NOT NULL,
    "type" "RecoveryType" NOT NULL,
    "description" TEXT,
    "time" TEXT,
    "sendOff" TEXT,
    "roundId" INTEGER NOT NULL,

    CONSTRAINT "Recovery_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "RoundDetail" (
    "id" SERIAL NOT NULL,
    "roundId" INTEGER NOT NULL,
    "intensityId" INTEGER NOT NULL,
    "recoveryId" INTEGER NOT NULL,

    CONSTRAINT "RoundDetail_pkey" PRIMARY KEY ("id")
);

-- CreateIndex
CREATE UNIQUE INDEX "RoundDetail_roundId_intensityId_recoveryId_key" ON "RoundDetail"("roundId", "intensityId", "recoveryId");

-- AddForeignKey
ALTER TABLE "Intensity" ADD CONSTRAINT "Intensity_roundId_fkey" FOREIGN KEY ("roundId") REFERENCES "Round"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "Recovery" ADD CONSTRAINT "Recovery_roundId_fkey" FOREIGN KEY ("roundId") REFERENCES "Round"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "RoundDetail" ADD CONSTRAINT "RoundDetail_roundId_fkey" FOREIGN KEY ("roundId") REFERENCES "Round"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "RoundDetail" ADD CONSTRAINT "RoundDetail_intensityId_fkey" FOREIGN KEY ("intensityId") REFERENCES "Intensity"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "RoundDetail" ADD CONSTRAINT "RoundDetail_recoveryId_fkey" FOREIGN KEY ("recoveryId") REFERENCES "Recovery"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

schema.prisma

// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("SUPABASE_POSTGRES_URL") // uses connection pooling
  // directUrl = env("POSTGRES_URL_NON_POOLING") // uses a direct connection
}

model Athlete {
  id              Int              @id @default(autoincrement())
  email           String           @unique
  name            String
  tests           Test[]
  firstName       String?
  lastName        String?
  athleteWorkouts AthleteWorkout[]
}

model AthleteWorkout {
  id            Int      @id @default(autoincrement())
  workoutId     Int
  athleteId     Int
  dateScheduled DateTime
  Athlete       Athlete? @relation(fields: [athleteId], references: [id])
  Workout       Workout? @relation(fields: [workoutId], references: [id])
}

model Test {
  id           Int          @id @default(autoincrement())
  date         DateTime
  activity     Activity     @default(Swim)
  type         TestType     @default(Time_Trial)
  comment      String?
  distanceUnit String?
  results      TestResult[]
  Athlete      Athlete      @relation(fields: [athleteId], references: [id])
  athleteId    Int
}

model TestResult {
  id       Int     @id @default(autoincrement())
  distance Decimal
  duration Decimal
  Test     Test    @relation(fields: [testId], references: [id])
  testId   Int
}

model Workout {
  id              Int              @id @default(autoincrement())
  activity        String
  description     String
  athleteWorkouts AthleteWorkout[]
  title           String?
  sets            WorkoutSet[] // Relation to the junction table
}

model Set {
  id       Int          @id @default(autoincrement())
  slug     String?
  title    String?
  rounds   Round[]
  category String?
  comment  String?
  poolType String?
  workouts WorkoutSet[] // Relation to the junction table
}

model Round {
  id       Int     @id @default(autoincrement())
  setId    Int
  set      Set     @relation(fields: [setId], references: [id])
  quantity Int
  repeat   Int     @default(1)
  distance Int
  order    Int     @default(1)
  comment  String?
}

model WorkoutSet {
  workoutId Int
  setId     Int
  workout   Workout @relation(fields: [workoutId], references: [id])
  set       Set     @relation(fields: [setId], references: [id])

  @@id([workoutId, setId]) // Composite primary key
}

model Account {
  id                String  @id @default(cuid())
  userId            String
  type              String
  provider          String
  providerAccountId String
  refresh_token     String? @db.Text
  access_token      String? @db.Text
  expires_at        Int?
  token_type        String?
  scope             String?
  id_token          String? @db.Text
  session_state     String?

  user User @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@unique([provider, providerAccountId])
}

model Session {
  id           String   @id @default(cuid())
  sessionToken String   @unique
  userId       String
  expires      DateTime
  user         User     @relation(fields: [userId], references: [id], onDelete: Cascade)
}

model User {
  id            String    @id @default(cuid())
  name          String?
  email         String?   @unique
  emailVerified DateTime?
  image         String?
  accounts      Account[]
  sessions      Session[]
}

model VerificationToken {
  identifier String
  token      String   @unique
  expires    DateTime

  @@unique([identifier, token])
}

enum Activity {
  Swim
  Bike
  Run
  Strength
  Row
}

enum TestType {
  Time_Trial @map("Time Trial")
  Pace_Decay @map("Pace Decay")
  MAP        @map("Max Aerobic Power")
  Vo2_Max    @map("Vo2 Max")
}

enum IntensityType {
  Drill
  Recovery
  Endurance
  Tempo
  Threshold
  SupraThreshold
  Vo2Max
  Anaerobic
  Sprint
  Power
}

enum PoolType {
  SCY
  SCM
  LCM
}

schema.prisma (after)

// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("SUPABASE_POSTGRES_URL") // uses connection pooling
  // directUrl = env("POSTGRES_URL_NON_POOLING") // uses a direct connection
}

model Athlete {
  id              Int              @id @default(autoincrement())
  email           String           @unique
  name            String
  tests           Test[]
  firstName       String?
  lastName        String?
  athleteWorkouts AthleteWorkout[]
}

model AthleteWorkout {
  id            Int      @id @default(autoincrement())
  workoutId     Int
  athleteId     Int
  dateScheduled DateTime
  Athlete       Athlete? @relation(fields: [athleteId], references: [id])
  Workout       Workout? @relation(fields: [workoutId], references: [id])
}

model Test {
  id           Int          @id @default(autoincrement())
  date         DateTime
  activity     Activity     @default(Swim)
  type         TestType     @default(Time_Trial)
  comment      String?
  distanceUnit String?
  results      TestResult[]
  Athlete      Athlete      @relation(fields: [athleteId], references: [id])
  athleteId    Int
}

model TestResult {
  id       Int     @id @default(autoincrement())
  distance Decimal
  duration Decimal
  Test     Test    @relation(fields: [testId], references: [id])
  testId   Int
}

model Workout {
  id              Int              @id @default(autoincrement())
  activity        String
  description     String
  athleteWorkouts AthleteWorkout[]
  title           String?
  sets            WorkoutSet[] // Relation to the junction table
}

model Set {
  id       Int          @id @default(autoincrement())
  slug     String?
  title    String?
  rounds   Round[]
  category String?
  comment  String?
  poolType String?
  workouts WorkoutSet[] // Relation to the junction table
}

model Round {
  id          Int           @id @default(autoincrement())
  setId       Int
  set         Set           @relation(fields: [setId], references: [id])
  quantity    Int
  repeat      Int           @default(1)
  distance    Int
  order       Int           @default(1)
  comment     String?
  Intensity   Intensity[]
  Recovery    Recovery[]
  RoundDetail RoundDetail[]
}

model WorkoutSet {
  workoutId Int
  setId     Int
  workout   Workout @relation(fields: [workoutId], references: [id])
  set       Set     @relation(fields: [setId], references: [id])

  @@id([workoutId, setId]) // Composite primary key
}

model Account {
  id                String  @id @default(cuid())
  userId            String
  type              String
  provider          String
  providerAccountId String
  refresh_token     String? @db.Text
  access_token      String? @db.Text
  expires_at        Int?
  token_type        String?
  scope             String?
  id_token          String? @db.Text
  session_state     String?

  user User @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@unique([provider, providerAccountId])
}

model Session {
  id           String   @id @default(cuid())
  sessionToken String   @unique
  userId       String
  expires      DateTime
  user         User     @relation(fields: [userId], references: [id], onDelete: Cascade)
}

model User {
  id            String    @id @default(cuid())
  name          String?
  email         String?   @unique
  emailVerified DateTime?
  image         String?
  accounts      Account[]
  sessions      Session[]
}

model VerificationToken {
  identifier String
  token      String   @unique
  expires    DateTime

  @@unique([identifier, token])
}

enum Activity {
  Swim
  Bike
  Run
  Strength
  Row
}

enum TestType {
  Time_Trial @map("Time Trial")
  Pace_Decay @map("Pace Decay")
  MAP        @map("Max Aerobic Power")
  Vo2_Max    @map("Vo2 Max")
}

enum IntensityCategory {
  Drill
  Recovery
  Endurance
  Tempo
  Threshold
  SupraThreshold
  Vo2Max
  Anaerobic
  Sprint
  Power
}

enum PoolType {
  SCY
  SCM
  LCM
}

model Intensity {
  id           Int           @id @default(autoincrement())
  type         IntensityType
  description  String?       @map("description") // For Effort
  time         String?       @map("time") // For Pace, stored as a string to accommodate mm:ss format
  profileValue Int?          @map("profileValue") // For Profile
  round        Round         @relation(fields: [roundId], references: [id])
  roundId      Int
  rounds       RoundDetail[]
}

enum IntensityType {
  Effort
  Pace
  Profile
}

model Recovery {
  id          Int           @id @default(autoincrement())
  type        RecoveryType
  // Additional fields for recovery details
  description String?       @map("description") // For Effort
  time        String?       @map("time") // For time, stored as a string to accommodate mm:ss format
  sendOff     String?       @map("sendOff") // For sendoff
  round       Round         @relation(fields: [roundId], references: [id])
  roundId     Int
  rounds      RoundDetail[]
}

model RoundDetail {
  id          Int       @id @default(autoincrement())
  roundId     Int
  intensityId Int
  recoveryId  Int
  round       Round     @relation(fields: [roundId], references: [id])
  intensity   Intensity @relation(fields: [intensityId], references: [id])
  recovery    Recovery  @relation(fields: [recoveryId], references: [id])

  @@unique([roundId, intensityId, recoveryId])
}

enum RecoveryType {
  Effort
  Time
  Sendoff
}