-- 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")
  )
);
