117 lines
3.9 KiB
SQL
117 lines
3.9 KiB
SQL
-- CreateTable
|
|
CREATE TABLE "User" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"email" TEXT NOT NULL,
|
|
"password" TEXT NOT NULL,
|
|
"role" TEXT NOT NULL DEFAULT 'USER',
|
|
"isFirstLogin" BOOLEAN NOT NULL DEFAULT true,
|
|
"isBlocked" BOOLEAN NOT NULL DEFAULT false,
|
|
"createdAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" DATETIME NOT NULL
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "BodyWeightRecord" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"userId" TEXT NOT NULL,
|
|
"weight" REAL NOT NULL,
|
|
"date" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"dateStr" TEXT NOT NULL,
|
|
CONSTRAINT "BodyWeightRecord_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User" ("id") ON DELETE CASCADE ON UPDATE CASCADE
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "UserProfile" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"userId" TEXT NOT NULL,
|
|
"weight" REAL,
|
|
"height" REAL,
|
|
"gender" TEXT,
|
|
"birthDate" DATETIME,
|
|
"language" TEXT DEFAULT 'en',
|
|
CONSTRAINT "UserProfile_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User" ("id") ON DELETE CASCADE ON UPDATE CASCADE
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Exercise" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"userId" TEXT,
|
|
"name" TEXT NOT NULL,
|
|
"type" TEXT NOT NULL,
|
|
"bodyWeightPercentage" REAL DEFAULT 0,
|
|
"isArchived" BOOLEAN NOT NULL DEFAULT false,
|
|
"isUnilateral" BOOLEAN NOT NULL DEFAULT false,
|
|
CONSTRAINT "Exercise_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User" ("id") ON DELETE CASCADE ON UPDATE CASCADE
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "WorkoutSession" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"userId" TEXT NOT NULL,
|
|
"startTime" DATETIME NOT NULL,
|
|
"endTime" DATETIME,
|
|
"userBodyWeight" REAL,
|
|
"note" TEXT,
|
|
"planId" TEXT,
|
|
"planName" TEXT,
|
|
CONSTRAINT "WorkoutSession_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User" ("id") ON DELETE CASCADE ON UPDATE CASCADE
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "WorkoutSet" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"sessionId" TEXT NOT NULL,
|
|
"exerciseId" TEXT NOT NULL,
|
|
"order" INTEGER NOT NULL,
|
|
"weight" REAL,
|
|
"reps" INTEGER,
|
|
"distanceMeters" REAL,
|
|
"durationSeconds" INTEGER,
|
|
"completed" BOOLEAN NOT NULL DEFAULT true,
|
|
"side" TEXT,
|
|
CONSTRAINT "WorkoutSet_sessionId_fkey" FOREIGN KEY ("sessionId") REFERENCES "WorkoutSession" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
CONSTRAINT "WorkoutSet_exerciseId_fkey" FOREIGN KEY ("exerciseId") REFERENCES "Exercise" ("id") ON DELETE RESTRICT ON UPDATE CASCADE
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "WorkoutPlan" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"userId" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"description" TEXT,
|
|
"exercises" TEXT NOT NULL,
|
|
"createdAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" DATETIME NOT NULL,
|
|
CONSTRAINT "WorkoutPlan_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User" ("id") ON DELETE CASCADE ON UPDATE CASCADE
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "SporadicSet" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"userId" TEXT NOT NULL,
|
|
"exerciseId" TEXT NOT NULL,
|
|
"weight" REAL,
|
|
"reps" INTEGER,
|
|
"distanceMeters" REAL,
|
|
"durationSeconds" INTEGER,
|
|
"height" REAL,
|
|
"bodyWeightPercentage" REAL,
|
|
"side" TEXT,
|
|
"timestamp" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"note" TEXT,
|
|
CONSTRAINT "SporadicSet_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
CONSTRAINT "SporadicSet_exerciseId_fkey" FOREIGN KEY ("exerciseId") REFERENCES "Exercise" ("id") ON DELETE RESTRICT ON UPDATE CASCADE
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "User_email_key" ON "User"("email");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "BodyWeightRecord_userId_dateStr_key" ON "BodyWeightRecord"("userId", "dateStr");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "UserProfile_userId_key" ON "UserProfile"("userId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "SporadicSet_userId_timestamp_idx" ON "SporadicSet"("userId", "timestamp");
|