120 lines
5.7 KiB
SQL
120 lines
5.7 KiB
SQL
-- Migration: Schools, Agencies, and Timetables
|
|
|
|
-- 1. Create SchoolType Enum
|
|
CREATE TYPE "SchoolType" AS ENUM ('MANAGED', 'INDEPENDENT', 'AGENCY');
|
|
|
|
-- 2. Create School Table
|
|
CREATE TABLE "School" (
|
|
"id" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"type" "SchoolType" NOT NULL DEFAULT 'MANAGED',
|
|
"adminId" TEXT NOT NULL, -- The user who created this school
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "School_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- 3. Create TeacherSchool (Mapping Table)
|
|
CREATE TABLE "TeacherSchool" (
|
|
"id" SERIAL NOT NULL,
|
|
"teacherId" TEXT NOT NULL,
|
|
"schoolId" TEXT NOT NULL,
|
|
"isManaged" BOOLEAN NOT NULL DEFAULT true,
|
|
|
|
CONSTRAINT "TeacherSchool_pkey" PRIMARY KEY ("id"),
|
|
CONSTRAINT "TeacherSchool_teacherId_fkey" FOREIGN KEY ("teacherId") REFERENCES "Teacher"("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
CONSTRAINT "TeacherSchool_schoolId_fkey" FOREIGN KEY ("schoolId") REFERENCES "School"("id") ON DELETE CASCADE ON UPDATE CASCADE
|
|
);
|
|
CREATE UNIQUE INDEX "TeacherSchool_teacherId_schoolId_key" ON "TeacherSchool"("teacherId", "schoolId");
|
|
|
|
-- 4. Create Term Table
|
|
CREATE TABLE "Term" (
|
|
"id" SERIAL NOT NULL,
|
|
"schoolId" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"startDate" TIMESTAMP(3) NOT NULL,
|
|
"endDate" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "Term_pkey" PRIMARY KEY ("id"),
|
|
CONSTRAINT "Term_schoolId_fkey" FOREIGN KEY ("schoolId") REFERENCES "School"("id") ON DELETE CASCADE ON UPDATE CASCADE
|
|
);
|
|
|
|
-- 5. Create Holiday Table
|
|
CREATE TABLE "Holiday" (
|
|
"id" SERIAL NOT NULL,
|
|
"schoolId" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"startDate" TIMESTAMP(3) NOT NULL,
|
|
"endDate" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "Holiday_pkey" PRIMARY KEY ("id"),
|
|
CONSTRAINT "Holiday_schoolId_fkey" FOREIGN KEY ("schoolId") REFERENCES "School"("id") ON DELETE CASCADE ON UPDATE CASCADE
|
|
);
|
|
|
|
-- 6. Create SchoolTimetableSlot Table
|
|
CREATE TABLE "SchoolTimetableSlot" (
|
|
"id" SERIAL NOT NULL,
|
|
"schoolId" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"startTime" TEXT NOT NULL, -- e.g. "09:00"
|
|
"endTime" TEXT NOT NULL, -- e.g. "10:00"
|
|
"isTeachingSlot" BOOLEAN NOT NULL DEFAULT true,
|
|
|
|
CONSTRAINT "SchoolTimetableSlot_pkey" PRIMARY KEY ("id"),
|
|
CONSTRAINT "SchoolTimetableSlot_schoolId_fkey" FOREIGN KEY ("schoolId") REFERENCES "School"("id") ON DELETE CASCADE ON UPDATE CASCADE
|
|
);
|
|
|
|
-- 7. Create TimetableTemplate and TimetableEntry
|
|
CREATE TABLE "TimetableTemplate" (
|
|
"id" SERIAL NOT NULL,
|
|
"schoolId" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"teacherId" TEXT NOT NULL,
|
|
|
|
CONSTRAINT "TimetableTemplate_pkey" PRIMARY KEY ("id"),
|
|
CONSTRAINT "TimetableTemplate_schoolId_fkey" FOREIGN KEY ("schoolId") REFERENCES "School"("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
CONSTRAINT "TimetableTemplate_teacherId_fkey" FOREIGN KEY ("teacherId") REFERENCES "Teacher"("id") ON DELETE CASCADE ON UPDATE CASCADE
|
|
);
|
|
|
|
CREATE TABLE "TimetableEntry" (
|
|
"id" SERIAL NOT NULL,
|
|
"timetableTemplateId" INTEGER NOT NULL,
|
|
"schoolTimetableSlotId" INTEGER NOT NULL,
|
|
"classId" INTEGER NOT NULL,
|
|
"subjectId" INTEGER NOT NULL,
|
|
"dayOfWeek" INTEGER NOT NULL, -- 1=Monday, 7=Sunday
|
|
|
|
CONSTRAINT "TimetableEntry_pkey" PRIMARY KEY ("id"),
|
|
CONSTRAINT "TimetableEntry_timetableTemplateId_fkey" FOREIGN KEY ("timetableTemplateId") REFERENCES "TimetableTemplate"("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
CONSTRAINT "TimetableEntry_schoolTimetableSlotId_fkey" FOREIGN KEY ("schoolTimetableSlotId") REFERENCES "SchoolTimetableSlot"("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
CONSTRAINT "TimetableEntry_classId_fkey" FOREIGN KEY ("classId") REFERENCES "Class"("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
CONSTRAINT "TimetableEntry_subjectId_fkey" FOREIGN KEY ("subjectId") REFERENCES "Subject"("id") ON DELETE CASCADE ON UPDATE CASCADE
|
|
);
|
|
|
|
-- 8. Alter Existing Tables to add schoolId (Since DB is reset, we assume tables are empty and can add NOT NULL safely)
|
|
-- For schemas heavily dependent on multi-tenancy:
|
|
ALTER TABLE "Admin" ADD COLUMN "schoolId" TEXT NOT NULL REFERENCES "School"("id") ON DELETE CASCADE;
|
|
ALTER TABLE "Student" ADD COLUMN "schoolId" TEXT NOT NULL REFERENCES "School"("id") ON DELETE CASCADE;
|
|
ALTER TABLE "Parent" ADD COLUMN "schoolId" TEXT NOT NULL REFERENCES "School"("id") ON DELETE CASCADE;
|
|
ALTER TABLE "Class" ADD COLUMN "schoolId" TEXT NOT NULL REFERENCES "School"("id") ON DELETE CASCADE;
|
|
ALTER TABLE "Subject" ADD COLUMN "schoolId" TEXT NOT NULL REFERENCES "School"("id") ON DELETE CASCADE;
|
|
ALTER TABLE "Lesson" ADD COLUMN "schoolId" TEXT NOT NULL REFERENCES "School"("id") ON DELETE CASCADE;
|
|
ALTER TABLE "Exam" ADD COLUMN "schoolId" TEXT NOT NULL REFERENCES "School"("id") ON DELETE CASCADE;
|
|
ALTER TABLE "Assignment" ADD COLUMN "schoolId" TEXT NOT NULL REFERENCES "School"("id") ON DELETE CASCADE;
|
|
ALTER TABLE "Result" ADD COLUMN "schoolId" TEXT NOT NULL REFERENCES "School"("id") ON DELETE CASCADE;
|
|
ALTER TABLE "Attendance" ADD COLUMN "schoolId" TEXT NOT NULL REFERENCES "School"("id") ON DELETE CASCADE;
|
|
ALTER TABLE "Event" ADD COLUMN "schoolId" TEXT NOT NULL REFERENCES "School"("id") ON DELETE CASCADE;
|
|
ALTER TABLE "Announcement" ADD COLUMN "schoolId" TEXT NOT NULL REFERENCES "School"("id") ON DELETE CASCADE;
|
|
|
|
-- 9. Cleanup Lesson table constraints and deprecated fields
|
|
ALTER TABLE "Lesson" DROP COLUMN "day";
|
|
|
|
-- Enable RLS on new tables
|
|
ALTER TABLE "School" ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE "TeacherSchool" ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE "Term" ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE "Holiday" ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE "SchoolTimetableSlot" ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE "TimetableTemplate" ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE "TimetableEntry" ENABLE ROW LEVEL SECURITY;
|