-- TeacherSchoolSchedule table to model when teachers work at specific schools
CREATE TABLE "TeacherSchoolSchedule" (
  "id" SERIAL PRIMARY KEY,
  "teacherId" TEXT NOT NULL REFERENCES "Teacher"("id") ON DELETE CASCADE ON UPDATE CASCADE,
  "schoolId" TEXT NOT NULL REFERENCES "School"("id") ON DELETE CASCADE ON UPDATE CASCADE,
  "startDate" TIMESTAMP(3) NOT NULL,
  "endDate" TIMESTAMP(3) NOT NULL,
  -- 1=Monday ... 7=Sunday, aligned with TimetableEntry.dayOfWeek
  "daysOfWeek" INTEGER[] NOT NULL
);

ALTER TABLE "TeacherSchoolSchedule" ENABLE ROW LEVEL SECURITY;

-- RLS policies for School, TeacherSchool, and TeacherSchoolSchedule

-- Allow admins full control over School
CREATE POLICY "Admins have full access to School"
ON "School"
FOR ALL
USING (is_admin())
WITH CHECK (is_admin());

-- Teachers can read schools they are mapped to via TeacherSchool
CREATE POLICY "Teachers can view their schools"
ON "School"
FOR SELECT
TO authenticated
USING (
  is_admin()
  OR id IN (
    SELECT "schoolId" FROM "TeacherSchool" WHERE "teacherId" = requesting_user_id()
  )
);

-- Optionally allow any authenticated user to browse the school directory
CREATE POLICY "Anyone can view schools"
ON "School"
FOR SELECT
TO authenticated
USING (true);

-- Teachers can manage schools where they are the adminId (independent/agency owners)
CREATE POLICY "Teachers manage their own schools"
ON "School"
FOR ALL
TO authenticated
USING (
  requesting_user_role() = 'teacher'
  AND "adminId" = requesting_user_id()
)
WITH CHECK (
  requesting_user_role() = 'teacher'
  AND "adminId" = requesting_user_id()
);

-- TeacherSchool policies
CREATE POLICY "Admins have full access to TeacherSchool"
ON "TeacherSchool"
FOR ALL
USING (is_admin())
WITH CHECK (is_admin());

CREATE POLICY "Teachers can view their TeacherSchool mappings"
ON "TeacherSchool"
FOR SELECT
TO authenticated
USING (
  requesting_user_role() = 'teacher'
  AND "teacherId" = requesting_user_id()
);

-- TeacherSchoolSchedule policies
CREATE POLICY "Admins have full access to TeacherSchoolSchedule"
ON "TeacherSchoolSchedule"
FOR ALL
USING (is_admin())
WITH CHECK (is_admin());

CREATE POLICY "Teachers can view their schedules"
ON "TeacherSchoolSchedule"
FOR SELECT
TO authenticated
USING (
  requesting_user_role() = 'teacher'
  AND "teacherId" = requesting_user_id()
);