-- Phase 8: Lesson plan templates — reusable curriculum definitions

CREATE TYPE "skill_level" AS ENUM ('beginner', 'intermediate', 'advanced', 'all_levels');

CREATE TABLE "lesson_plan_template" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  "name" varchar(255) NOT NULL,
  "description" text,
  "instrument" varchar(100),
  "skill_level" skill_level NOT NULL DEFAULT 'all_levels',
  "created_by" uuid REFERENCES "user"("id"),
  "is_active" boolean NOT NULL DEFAULT true,
  "created_at" timestamptz NOT NULL DEFAULT now(),
  "updated_at" timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE "lesson_plan_template_section" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  "template_id" uuid NOT NULL REFERENCES "lesson_plan_template"("id"),
  "title" varchar(255) NOT NULL,
  "description" text,
  "sort_order" integer NOT NULL,
  "created_at" timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE "lesson_plan_template_item" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  "section_id" uuid NOT NULL REFERENCES "lesson_plan_template_section"("id"),
  "title" varchar(255) NOT NULL,
  "description" text,
  "grading_scale_id" uuid REFERENCES "grading_scale"("id"),
  "target_grade_value" varchar(50),
  "sort_order" integer NOT NULL,
  "created_at" timestamptz NOT NULL DEFAULT now()
);
