-- Phase 7: Grade history and session-plan item linking

CREATE TABLE "lesson_plan_item_grade_history" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  "lesson_plan_item_id" uuid NOT NULL REFERENCES "lesson_plan_item"("id"),
  "grading_scale_id" uuid REFERENCES "grading_scale"("id"),
  "grade_value" varchar(50) NOT NULL,
  "graded_by" uuid REFERENCES "user"("id"),
  "session_id" uuid REFERENCES "lesson_session"("id"),
  "notes" text,
  "created_at" timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE "lesson_session_plan_item" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  "session_id" uuid NOT NULL REFERENCES "lesson_session"("id"),
  "lesson_plan_item_id" uuid NOT NULL REFERENCES "lesson_plan_item"("id"),
  "created_at" timestamptz NOT NULL DEFAULT now()
);
