kindergarten_java/reading-platform-backend/prisma/migrations/20260210055321_init/migration.sql

-- CreateTable
CREATE TABLE "tenants" (
    "id" BIGINT NOT NULL PRIMARY KEY,
    "name" TEXT NOT NULL,
    "address" TEXT,
    "contact_person" TEXT,
    "contact_phone" TEXT,
    "logo_url" TEXT,
    "package_type" TEXT NOT NULL DEFAULT 'STANDARD',
    "teacher_quota" INTEGER NOT NULL DEFAULT 20,
    "student_quota" INTEGER NOT NULL DEFAULT 200,
    "storage_quota" BIGINT NOT NULL DEFAULT 5368709120,
    "start_date" TEXT NOT NULL,
    "expire_date" TEXT NOT NULL,
    "teacher_count" INTEGER NOT NULL DEFAULT 0,
    "student_count" INTEGER NOT NULL DEFAULT 0,
    "storage_used" BIGINT NOT NULL DEFAULT 0,
    "status" TEXT NOT NULL DEFAULT 'ACTIVE',
    "created_at" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" DATETIME NOT NULL
);

-- CreateTable
CREATE TABLE "teachers" (
    "id" BIGINT NOT NULL PRIMARY KEY,
    "tenant_id" BIGINT NOT NULL,
    "name" TEXT NOT NULL,
    "phone" TEXT NOT NULL,
    "email" TEXT,
    "login_account" TEXT NOT NULL,
    "password_hash" TEXT NOT NULL,
    "class_ids" TEXT DEFAULT '[]',
    "status" TEXT NOT NULL DEFAULT 'ACTIVE',
    "lesson_count" INTEGER NOT NULL DEFAULT 0,
    "feedback_count" INTEGER NOT NULL DEFAULT 0,
    "created_at" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" DATETIME NOT NULL,
    "last_login_at" DATETIME,
    CONSTRAINT "teachers_tenant_id_fkey" FOREIGN KEY ("tenant_id") REFERENCES "tenants" ("id") ON DELETE CASCADE ON UPDATE CASCADE
);

-- CreateTable
CREATE TABLE "classes" (
    "id" BIGINT NOT NULL PRIMARY KEY,
    "tenant_id" BIGINT NOT NULL,
    "name" TEXT NOT NULL,
    "grade" TEXT NOT NULL,
    "teacher_id" BIGINT,
    "student_count" INTEGER NOT NULL DEFAULT 0,
    "lesson_count" INTEGER NOT NULL DEFAULT 0,
    "created_at" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" DATETIME NOT NULL,
    CONSTRAINT "classes_tenant_id_fkey" FOREIGN KEY ("tenant_id") REFERENCES "tenants" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT "classes_teacher_id_fkey" FOREIGN KEY ("teacher_id") REFERENCES "teachers" ("id") ON DELETE SET NULL ON UPDATE CASCADE
);

-- CreateTable
CREATE TABLE "students" (
    "id" BIGINT NOT NULL PRIMARY KEY,
    "tenant_id" BIGINT NOT NULL,
    "class_id" BIGINT NOT NULL,
    "name" TEXT NOT NULL,
    "gender" TEXT,
    "birth_date" DATETIME,
    "parent_phone" TEXT,
    "parent_name" TEXT,
    "reading_count" INTEGER NOT NULL DEFAULT 0,
    "lesson_count" INTEGER NOT NULL DEFAULT 0,
    "created_at" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" DATETIME NOT NULL,
    CONSTRAINT "students_tenant_id_fkey" FOREIGN KEY ("tenant_id") REFERENCES "tenants" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT "students_class_id_fkey" FOREIGN KEY ("class_id") REFERENCES "classes" ("id") ON DELETE CASCADE ON UPDATE CASCADE
);

-- CreateTable
CREATE TABLE "courses" (
    "id" BIGINT NOT NULL PRIMARY KEY,
    "name" TEXT NOT NULL,
    "description" TEXT,
    "picture_book_id" INTEGER,
    "picture_book_name" TEXT,
    "grade_tags" TEXT NOT NULL DEFAULT '[]',
    "domain_tags" TEXT NOT NULL DEFAULT '[]',
    "duration" INTEGER NOT NULL DEFAULT 25,
    "status" TEXT NOT NULL DEFAULT 'DRAFT',
    "version" TEXT NOT NULL DEFAULT '1.0',
    "usage_count" INTEGER NOT NULL DEFAULT 0,
    "teacher_count" INTEGER NOT NULL DEFAULT 0,
    "avg_rating" REAL NOT NULL DEFAULT 0,
    "created_by" INTEGER,
    "created_at" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" DATETIME NOT NULL,
    "published_at" DATETIME
);

-- CreateTable
CREATE TABLE "course_resources" (
    "id" BIGINT NOT NULL PRIMARY KEY,
    "course_id" BIGINT NOT NULL,
    "resource_type" TEXT NOT NULL,
    "resource_name" TEXT NOT NULL,
    "file_url" TEXT NOT NULL,
    "file_size" BIGINT,
    "mime_type" TEXT,
    "metadata" TEXT,
    "sort_order" INTEGER NOT NULL DEFAULT 0,
    "created_at" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT "course_resources_course_id_fkey" FOREIGN KEY ("course_id") REFERENCES "courses" ("id") ON DELETE CASCADE ON UPDATE CASCADE
);

-- CreateTable
CREATE TABLE "course_scripts" (
    "id" BIGINT NOT NULL PRIMARY KEY,
    "course_id" BIGINT NOT NULL,
    "step_index" INTEGER NOT NULL,
    "step_name" TEXT NOT NULL,
    "step_type" TEXT NOT NULL,
    "duration" INTEGER NOT NULL,
    "objective" TEXT,
    "teacher_script" TEXT,
    "interaction_points" TEXT,
    "resource_ids" TEXT,
    "sort_order" INTEGER NOT NULL DEFAULT 0,
    "created_at" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" DATETIME NOT NULL,
    CONSTRAINT "course_scripts_course_id_fkey" FOREIGN KEY ("course_id") REFERENCES "courses" ("id") ON DELETE CASCADE ON UPDATE CASCADE
);

-- CreateTable
CREATE TABLE "course_script_pages" (
    "id" BIGINT NOT NULL PRIMARY KEY,
    "script_id" BIGINT NOT NULL,
    "page_number" INTEGER NOT NULL,
    "questions" TEXT,
    "interaction_component" TEXT,
    "teacher_notes" TEXT,
    "created_at" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" DATETIME NOT NULL,
    CONSTRAINT "course_script_pages_script_id_fkey" FOREIGN KEY ("script_id") REFERENCES "course_scripts" ("id") ON DELETE CASCADE ON UPDATE CASCADE
);

-- CreateTable
CREATE TABLE "course_activities" (
    "id" BIGINT NOT NULL PRIMARY KEY,
    "course_id" BIGINT NOT NULL,
    "name" TEXT NOT NULL,
    "domain" TEXT,
    "domain_tag_id" INTEGER,
    "activity_type" TEXT NOT NULL,
    "duration" INTEGER,
    "online_materials" TEXT,
    "offlineMaterials" TEXT,
    "activityGuide" TEXT,
    "objectives" TEXT,
    "sort_order" INTEGER NOT NULL DEFAULT 0,
    "created_at" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT "course_activities_course_id_fkey" FOREIGN KEY ("course_id") REFERENCES "courses" ("id") ON DELETE CASCADE ON UPDATE CASCADE
);

-- CreateTable
CREATE TABLE "lessons" (
    "id" BIGINT NOT NULL PRIMARY KEY,
    "tenant_id" BIGINT NOT NULL,
    "teacher_id" BIGINT NOT NULL,
    "class_id" BIGINT NOT NULL,
    "course_id" BIGINT NOT NULL,
    "planned_datetime" DATETIME,
    "start_datetime" DATETIME,
    "end_datetime" DATETIME,
    "actual_duration" INTEGER,
    "status" TEXT NOT NULL DEFAULT 'PLANNED',
    "overall_rating" TEXT,
    "participation_rating" TEXT,
    "completion_note" TEXT,
    "created_at" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" DATETIME NOT NULL,
    CONSTRAINT "lessons_tenant_id_fkey" FOREIGN KEY ("tenant_id") REFERENCES "tenants" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT "lessons_teacher_id_fkey" FOREIGN KEY ("teacher_id") REFERENCES "teachers" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT "lessons_class_id_fkey" FOREIGN KEY ("class_id") REFERENCES "classes" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT "lessons_course_id_fkey" FOREIGN KEY ("course_id") REFERENCES "courses" ("id") ON DELETE RESTRICT ON UPDATE CASCADE
);

-- CreateTable
CREATE TABLE "lesson_feedbacks" (
    "id" BIGINT NOT NULL PRIMARY KEY,
    "lesson_id" BIGINT NOT NULL,
    "teacher_id" BIGINT NOT NULL,
    "design_quality" INTEGER,
    "participation" INTEGER,
    "goal_achievement" INTEGER,
    "step_feedbacks" TEXT,
    "pros" TEXT,
    "suggestions" TEXT,
    "activities_done" TEXT,
    "created_at" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" DATETIME NOT NULL,
    CONSTRAINT "lesson_feedbacks_lesson_id_fkey" FOREIGN KEY ("lesson_id") REFERENCES "lessons" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT "lesson_feedbacks_teacher_id_fkey" FOREIGN KEY ("teacher_id") REFERENCES "teachers" ("id") ON DELETE RESTRICT ON UPDATE CASCADE
);

-- CreateTable
CREATE TABLE "student_records" (
    "id" BIGINT NOT NULL PRIMARY KEY,
    "lesson_id" BIGINT NOT NULL,
    "student_id" BIGINT NOT NULL,
    "focus" INTEGER,
    "participation" INTEGER,
    "interest" INTEGER,
    "understanding" INTEGER,
    "domainAchievements" TEXT,
    "notes" TEXT,
    "created_at" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" DATETIME NOT NULL,
    CONSTRAINT "student_records_lesson_id_fkey" FOREIGN KEY ("lesson_id") REFERENCES "lessons" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT "student_records_student_id_fkey" FOREIGN KEY ("student_id") REFERENCES "students" ("id") ON DELETE RESTRICT ON UPDATE CASCADE
);

-- CreateTable
CREATE TABLE "tags" (
    "id" BIGINT NOT NULL PRIMARY KEY,
    "level" INTEGER NOT NULL,
    "code" TEXT NOT NULL,
    "name" TEXT NOT NULL,
    "parent_id" BIGINT,
    "description" TEXT,
    "metadata" TEXT,
    "sort_order" INTEGER NOT NULL DEFAULT 0,
    "created_at" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- CreateTable
CREATE TABLE "tenant_courses" (
    "id" BIGINT NOT NULL PRIMARY KEY,
    "tenant_id" BIGINT NOT NULL,
    "course_id" BIGINT NOT NULL,
    "authorized" BOOLEAN NOT NULL DEFAULT true,
    "authorized_at" DATETIME,
    "created_at" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT "tenant_courses_tenant_id_fkey" FOREIGN KEY ("tenant_id") REFERENCES "tenants" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT "tenant_courses_course_id_fkey" FOREIGN KEY ("course_id") REFERENCES "courses" ("id") ON DELETE CASCADE ON UPDATE CASCADE
);

-- CreateIndex
CREATE UNIQUE INDEX "teachers_login_account_key" ON "teachers"("login_account");

-- CreateIndex
CREATE UNIQUE INDEX "course_scripts_course_id_step_index_key" ON "course_scripts"("course_id", "step_index");

-- CreateIndex
CREATE UNIQUE INDEX "course_script_pages_script_id_page_number_key" ON "course_script_pages"("script_id", "page_number");

-- CreateIndex
CREATE UNIQUE INDEX "lesson_feedbacks_lesson_id_teacher_id_key" ON "lesson_feedbacks"("lesson_id", "teacher_id");

-- CreateIndex
CREATE UNIQUE INDEX "student_records_lesson_id_student_id_key" ON "student_records"("lesson_id", "student_id");

-- CreateIndex
CREATE UNIQUE INDEX "tags_code_key" ON "tags"("code");

-- CreateIndex
CREATE UNIQUE INDEX "tenant_courses_tenant_id_course_id_key" ON "tenant_courses"("tenant_id", "course_id");