41 lines
1.1 KiB
SQL
41 lines
1.1 KiB
SQL
-- CreateTable
|
|
CREATE TABLE "User" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"name" TEXT NOT NULL,
|
|
"pin" TEXT NOT NULL,
|
|
"role" TEXT NOT NULL
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Period" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"name" TEXT NOT NULL,
|
|
"startDate" TEXT NOT NULL,
|
|
"endDate" TEXT NOT NULL
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Youth" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"name" TEXT NOT NULL,
|
|
"team" TEXT NOT NULL,
|
|
"periodId" TEXT NOT NULL,
|
|
CONSTRAINT "Youth_periodId_fkey" FOREIGN KEY ("periodId") REFERENCES "Period" ("id") ON DELETE CASCADE ON UPDATE CASCADE
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Attendance" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"date" TEXT NOT NULL,
|
|
"shiftId" TEXT NOT NULL,
|
|
"hoursWorked" REAL NOT NULL,
|
|
"weightedHours" REAL NOT NULL,
|
|
"status" TEXT NOT NULL,
|
|
"note" TEXT,
|
|
"youthId" TEXT NOT NULL,
|
|
CONSTRAINT "Attendance_youthId_fkey" FOREIGN KEY ("youthId") REFERENCES "Youth" ("id") ON DELETE CASCADE ON UPDATE CASCADE
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "Attendance_date_youthId_shiftId_key" ON "Attendance"("date", "youthId", "shiftId");
|