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