173 lines
8.0 KiB
SQL
173 lines
8.0 KiB
SQL
-- Academic years and School Timetable (slot layout) with slot ordering
|
|
-- Adds AcademicYear, SchoolTimetable; links Term/Holiday/Slots to them; renames Template/Entry and adds schoolTimetableId + audit fields.
|
|
|
|
-- 1. AcademicYear
|
|
CREATE TABLE "AcademicYear" (
|
|
"id" SERIAL NOT NULL,
|
|
"schoolId" TEXT NOT NULL,
|
|
"startYear" INTEGER NOT NULL,
|
|
"endYear" INTEGER NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"createdById" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "AcademicYear_pkey" PRIMARY KEY ("id"),
|
|
CONSTRAINT "AcademicYear_schoolId_fkey" FOREIGN KEY ("schoolId") REFERENCES "School"("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
CONSTRAINT "AcademicYear_endYear_check" CHECK ("endYear" = "startYear" + 1)
|
|
);
|
|
|
|
CREATE INDEX "AcademicYear_schoolId_idx" ON "AcademicYear"("schoolId");
|
|
|
|
-- 2. SchoolTimetable (slot layout for an academic year)
|
|
CREATE TABLE "SchoolTimetable" (
|
|
"id" SERIAL NOT NULL,
|
|
"academicYearId" INTEGER NOT NULL,
|
|
"schoolId" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"createdById" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "SchoolTimetable_pkey" PRIMARY KEY ("id"),
|
|
CONSTRAINT "SchoolTimetable_academicYearId_fkey" FOREIGN KEY ("academicYearId") REFERENCES "AcademicYear"("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
CONSTRAINT "SchoolTimetable_schoolId_fkey" FOREIGN KEY ("schoolId") REFERENCES "School"("id") ON DELETE CASCADE ON UPDATE CASCADE
|
|
);
|
|
|
|
CREATE INDEX "SchoolTimetable_academicYearId_idx" ON "SchoolTimetable"("academicYearId");
|
|
CREATE INDEX "SchoolTimetable_schoolId_idx" ON "SchoolTimetable"("schoolId");
|
|
|
|
-- 3. Term: add academicYearId, createdById, createdAt
|
|
ALTER TABLE "Term"
|
|
ADD COLUMN "academicYearId" INTEGER,
|
|
ADD COLUMN "createdById" TEXT,
|
|
ADD COLUMN "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP;
|
|
|
|
-- 4. Holiday: add academicYearId, createdById, createdAt
|
|
ALTER TABLE "Holiday"
|
|
ADD COLUMN "academicYearId" INTEGER,
|
|
ADD COLUMN "createdById" TEXT,
|
|
ADD COLUMN "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP;
|
|
|
|
-- 5. SchoolTimetableSlot: add schoolTimetableId, position, createdById (keep schoolId for denormalization)
|
|
ALTER TABLE "SchoolTimetableSlot"
|
|
ADD COLUMN "schoolTimetableId" INTEGER,
|
|
ADD COLUMN "position" INTEGER,
|
|
ADD COLUMN "createdById" TEXT,
|
|
ADD COLUMN "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP;
|
|
|
|
-- 6. Backfill: one AcademicYear (2026-2027) and one SchoolTimetable per school
|
|
INSERT INTO "AcademicYear" ("schoolId", "startYear", "endYear", "name")
|
|
SELECT "id", 2026, 2027, '2026-2027' FROM "School";
|
|
|
|
INSERT INTO "SchoolTimetable" ("academicYearId", "schoolId", "name")
|
|
SELECT ay."id", ay."schoolId", 'Standard Week'
|
|
FROM "AcademicYear" ay;
|
|
|
|
UPDATE "Term" t
|
|
SET "academicYearId" = ay."id"
|
|
FROM "AcademicYear" ay
|
|
WHERE ay."schoolId" = t."schoolId";
|
|
|
|
UPDATE "Holiday" h
|
|
SET "academicYearId" = ay."id"
|
|
FROM "AcademicYear" ay
|
|
WHERE ay."schoolId" = h."schoolId";
|
|
|
|
UPDATE "SchoolTimetableSlot" s
|
|
SET "schoolTimetableId" = st."id", "position" = sub.rn
|
|
FROM "SchoolTimetable" st,
|
|
(SELECT "id", ROW_NUMBER() OVER (PARTITION BY "schoolId" ORDER BY "id") AS rn FROM "SchoolTimetableSlot") sub
|
|
WHERE st."schoolId" = s."schoolId" AND sub."id" = s."id";
|
|
|
|
-- 7. Enforce NOT NULL and FKs for Term and SchoolTimetableSlot; optional FK for Holiday
|
|
ALTER TABLE "Holiday"
|
|
ADD CONSTRAINT "Holiday_academicYearId_fkey" FOREIGN KEY ("academicYearId") REFERENCES "AcademicYear"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- Term and SchoolTimetableSlot
|
|
ALTER TABLE "Term"
|
|
ADD CONSTRAINT "Term_academicYearId_fkey" FOREIGN KEY ("academicYearId") REFERENCES "AcademicYear"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
ALTER TABLE "Term" ALTER COLUMN "academicYearId" SET NOT NULL;
|
|
|
|
ALTER TABLE "SchoolTimetableSlot"
|
|
ADD CONSTRAINT "SchoolTimetableSlot_schoolTimetableId_fkey" FOREIGN KEY ("schoolTimetableId") REFERENCES "SchoolTimetable"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
ALTER TABLE "SchoolTimetableSlot" ALTER COLUMN "schoolTimetableId" SET NOT NULL;
|
|
ALTER TABLE "SchoolTimetableSlot" ALTER COLUMN "position" SET NOT NULL;
|
|
|
|
CREATE INDEX "Term_academicYearId_idx" ON "Term"("academicYearId");
|
|
CREATE INDEX "SchoolTimetableSlot_schoolTimetableId_idx" ON "SchoolTimetableSlot"("schoolTimetableId");
|
|
|
|
-- 8. Rename TimetableTemplate -> TeacherTimetableTemplate; add schoolTimetableId, createdById, createdAt
|
|
ALTER TABLE "TimetableTemplate"
|
|
ADD COLUMN "schoolTimetableId" INTEGER,
|
|
ADD COLUMN "createdById" TEXT,
|
|
ADD COLUMN "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP;
|
|
|
|
UPDATE "TimetableTemplate" tt
|
|
SET "schoolTimetableId" = (
|
|
SELECT st."id" FROM "SchoolTimetable" st WHERE st."schoolId" = tt."schoolId" ORDER BY st."id" LIMIT 1
|
|
);
|
|
|
|
ALTER TABLE "TimetableTemplate"
|
|
ADD CONSTRAINT "TimetableTemplate_schoolTimetableId_fkey" FOREIGN KEY ("schoolTimetableId") REFERENCES "SchoolTimetable"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
ALTER TABLE "TimetableTemplate" ALTER COLUMN "schoolTimetableId" SET NOT NULL;
|
|
|
|
ALTER TABLE "TimetableTemplate" RENAME TO "TeacherTimetableTemplate";
|
|
|
|
CREATE INDEX "TeacherTimetableTemplate_schoolTimetableId_idx" ON "TeacherTimetableTemplate"("schoolTimetableId");
|
|
|
|
-- 9. Rename TimetableEntry -> TeacherTimetableEntry; column timetableTemplateId -> teacherTimetableTemplateId
|
|
ALTER TABLE "TimetableEntry" RENAME TO "TeacherTimetableEntry";
|
|
ALTER TABLE "TeacherTimetableEntry" RENAME COLUMN "timetableTemplateId" TO "teacherTimetableTemplateId";
|
|
|
|
-- FK constraint name still points to old table name; drop and re-add
|
|
ALTER TABLE "TeacherTimetableEntry" DROP CONSTRAINT "TimetableEntry_timetableTemplateId_fkey";
|
|
ALTER TABLE "TeacherTimetableEntry"
|
|
ADD CONSTRAINT "TeacherTimetableEntry_teacherTimetableTemplateId_fkey"
|
|
FOREIGN KEY ("teacherTimetableTemplateId") REFERENCES "TeacherTimetableTemplate"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
CREATE INDEX "TeacherTimetableEntry_teacherTimetableTemplateId_idx" ON "TeacherTimetableEntry"("teacherTimetableTemplateId");
|
|
|
|
-- 10. Enable RLS on new tables
|
|
ALTER TABLE "AcademicYear" ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE "SchoolTimetable" ENABLE ROW LEVEL SECURITY;
|
|
|
|
-- 11. RLS policies for AcademicYear and SchoolTimetable (teacher_can_manage_school from 20260305020000)
|
|
CREATE POLICY "Admins have full access on AcademicYear"
|
|
ON "AcademicYear" FOR ALL USING (is_admin()) WITH CHECK (is_admin());
|
|
|
|
CREATE POLICY "Teachers manage academic years for their schools"
|
|
ON "AcademicYear" FOR ALL TO authenticated
|
|
USING (requesting_user_role() = 'teacher' AND teacher_can_manage_school("schoolId"))
|
|
WITH CHECK (requesting_user_role() = 'teacher' AND teacher_can_manage_school("schoolId"));
|
|
|
|
CREATE POLICY "Admins have full access on SchoolTimetable"
|
|
ON "SchoolTimetable" FOR ALL USING (is_admin()) WITH CHECK (is_admin());
|
|
|
|
CREATE POLICY "Teachers manage school timetables for their schools"
|
|
ON "SchoolTimetable" FOR ALL TO authenticated
|
|
USING (requesting_user_role() = 'teacher' AND teacher_can_manage_school("schoolId"))
|
|
WITH CHECK (requesting_user_role() = 'teacher' AND teacher_can_manage_school("schoolId"));
|
|
|
|
-- 12. Update TeacherTimetableEntry policies to use renamed table/column (drop old, create new)
|
|
DROP POLICY IF EXISTS "Admins have full access on TimetableEntry" ON "TeacherTimetableEntry";
|
|
DROP POLICY IF EXISTS "Teachers manage entries for their schools" ON "TeacherTimetableEntry";
|
|
|
|
CREATE POLICY "Admins have full access on TeacherTimetableEntry"
|
|
ON "TeacherTimetableEntry" FOR ALL USING (is_admin()) WITH CHECK (is_admin());
|
|
|
|
CREATE POLICY "Teachers manage entries for their schools"
|
|
ON "TeacherTimetableEntry" FOR ALL TO authenticated
|
|
USING (
|
|
requesting_user_role() = 'teacher'
|
|
AND EXISTS (
|
|
SELECT 1 FROM "TeacherTimetableTemplate" tt
|
|
WHERE tt."id" = "teacherTimetableTemplateId" AND teacher_can_manage_school(tt."schoolId")
|
|
)
|
|
)
|
|
WITH CHECK (
|
|
requesting_user_role() = 'teacher'
|
|
AND EXISTS (
|
|
SELECT 1 FROM "TeacherTimetableTemplate" tt
|
|
WHERE tt."id" = "teacherTimetableTemplateId" AND teacher_can_manage_school(tt."schoolId")
|
|
)
|
|
);
|