-- CreateTable
CREATE TABLE "student_teacher_assignments" (
    "id" TEXT NOT NULL,
    "student_id" TEXT NOT NULL,
    "teacher_id" TEXT NOT NULL,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "student_teacher_assignments_pkey" PRIMARY KEY ("id")
);

-- CreateIndex
CREATE UNIQUE INDEX "student_teacher_assignments_student_id_teacher_id_key" ON "student_teacher_assignments"("student_id", "teacher_id");

-- Migrate existing assigned_teacher_id data to the new table
INSERT INTO "student_teacher_assignments" ("id", "student_id", "teacher_id", "created_at")
SELECT gen_random_uuid()::text, "id", "assigned_teacher_id", NOW()
FROM "users"
WHERE "assigned_teacher_id" IS NOT NULL;

-- DropForeignKey
ALTER TABLE "users" DROP CONSTRAINT IF EXISTS "users_assigned_teacher_id_fkey";

-- DropColumn
ALTER TABLE "users" DROP COLUMN "assigned_teacher_id";

-- AddForeignKey
ALTER TABLE "student_teacher_assignments" ADD CONSTRAINT "student_teacher_assignments_student_id_fkey" FOREIGN KEY ("student_id") REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "student_teacher_assignments" ADD CONSTRAINT "student_teacher_assignments_teacher_id_fkey" FOREIGN KEY ("teacher_id") REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE CASCADE;
