import { sql, relations } from "drizzle-orm";
import { pgTable, text, varchar, integer, boolean, timestamp, pgEnum, jsonb, real } from "drizzle-orm/pg-core";
import { createInsertSchema } from "drizzle-zod";
import { z } from "zod";

export * from "./models/auth";
import { users } from "./models/auth";

export const roleEnum = pgEnum("user_role", ["student", "teacher", "parent", "admin", "supervisor"]);
export const educationalStageEnum = pgEnum("educational_stage", ["elementary", "middle", "high"]);
export const interactiveContentTypeEnum = pgEnum("interactive_content_type", ["lesson", "game"]);
export const curriculumCountryEnum = pgEnum("curriculum_country", ["saudi", "egyptian", "syrian", "custom"]);
export const subscriptionStatusEnum = pgEnum("subscription_status", ["active", "expired", "cancelled", "trial"]);
export const subscriptionPlanEnum = pgEnum("subscription_plan", ["monthly", "quarterly", "yearly"]);
export const lessonTypeEnum = pgEnum("lesson_type", ["video", "reading", "interactive", "assignment"]);
export const assessmentTypeEnum = pgEnum("assessment_type", ["quiz", "exercise", "exam"]);
export const notificationTypeEnum = pgEnum("notification_type", ["info", "success", "warning", "assignment", "grade", "announcement"]);

export const userProfiles = pgTable("user_profiles", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  userId: varchar("user_id").notNull().references(() => users.id),
  role: roleEnum("role").notNull().default("student"),
  phone: varchar("phone"),
  bio: text("bio"),
  preferredLanguage: varchar("preferred_language").default("ar"),
  parentId: varchar("parent_id"),
  gradeId: varchar("grade_id"),
  permissions: jsonb("permissions"),
  assignedCurricula: text("assigned_curricula").array(),
  supervisedSubjects: text("supervised_subjects").array(),
});

export const curricula = pgTable("curricula", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  nameAr: varchar("name_ar").notNull(),
  nameEn: varchar("name_en"),
  descriptionAr: text("description_ar"),
  descriptionEn: text("description_en"),
  country: curriculumCountryEnum("country").notNull(),
  isActive: boolean("is_active").default(true),
  imageUrl: varchar("image_url"),
});

export const grades = pgTable("grades", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  curriculumId: varchar("curriculum_id").notNull().references(() => curricula.id),
  nameAr: varchar("name_ar").notNull(),
  nameEn: varchar("name_en"),
  orderIndex: integer("order_index").notNull().default(0),
});

export const subjects = pgTable("subjects", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  gradeId: varchar("grade_id").notNull().references(() => grades.id),
  nameAr: varchar("name_ar").notNull(),
  nameEn: varchar("name_en"),
  descriptionAr: text("description_ar"),
  iconName: varchar("icon_name"),
  color: varchar("color"),
  orderIndex: integer("order_index").notNull().default(0),
});

export const courseSections = pgTable("course_sections", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  subjectId: varchar("subject_id").notNull().references(() => subjects.id),
  nameAr: varchar("name_ar").notNull(),
  nameEn: varchar("name_en"),
  descriptionAr: text("description_ar"),
  orderIndex: integer("order_index").notNull().default(0),
  isMandatory: boolean("is_mandatory").default(true),
});

export const lessons = pgTable("lessons", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  subjectId: varchar("subject_id").notNull().references(() => subjects.id),
  sectionId: varchar("section_id").references(() => courseSections.id),
  nameAr: varchar("name_ar").notNull(),
  nameEn: varchar("name_en"),
  descriptionAr: text("description_ar"),
  contentAr: text("content_ar"),
  contentEn: text("content_en"),
  videoUrl: varchar("video_url"),
  videoDurationSeconds: integer("video_duration_seconds"),
  thumbnailUrl: varchar("thumbnail_url"),
  lessonType: lessonTypeEnum("lesson_type").default("reading"),
  durationMinutes: integer("duration_minutes"),
  orderIndex: integer("order_index").notNull().default(0),
  isPublished: boolean("is_published").default(true),
  isMandatory: boolean("is_mandatory").default(true),
  isFree: boolean("is_free").default(false),
  prerequisiteLessonId: varchar("prerequisite_lesson_id"),
  learningOutcomes: text("learning_outcomes").array(),
});

export const lessonResources = pgTable("lesson_resources", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  lessonId: varchar("lesson_id").notNull().references(() => lessons.id),
  nameAr: varchar("name_ar").notNull(),
  nameEn: varchar("name_en"),
  fileUrl: varchar("file_url").notNull(),
  fileType: varchar("file_type"),
  fileSizeKb: integer("file_size_kb"),
  orderIndex: integer("order_index").notNull().default(0),
});

export const lessonNotes = pgTable("lesson_notes", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  userId: varchar("user_id").notNull().references(() => users.id),
  lessonId: varchar("lesson_id").notNull().references(() => lessons.id),
  content: text("content").notNull(),
  videoTimestamp: integer("video_timestamp"),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

export const lessonBookmarks = pgTable("lesson_bookmarks", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  userId: varchar("user_id").notNull().references(() => users.id),
  lessonId: varchar("lesson_id").notNull().references(() => lessons.id),
  createdAt: timestamp("created_at").defaultNow(),
});

export const lessonQna = pgTable("lesson_qna", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  lessonId: varchar("lesson_id").notNull().references(() => lessons.id),
  userId: varchar("user_id").notNull().references(() => users.id),
  parentId: varchar("parent_id"),
  content: text("content").notNull(),
  isInstructorAnswer: boolean("is_instructor_answer").default(false),
  upvotes: integer("upvotes").default(0),
  createdAt: timestamp("created_at").defaultNow(),
});

export const notifications = pgTable("notifications", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  userId: varchar("user_id").notNull().references(() => users.id),
  type: notificationTypeEnum("type").default("info"),
  titleAr: varchar("title_ar").notNull(),
  titleEn: varchar("title_en"),
  messageAr: text("message_ar"),
  messageEn: text("message_en"),
  link: varchar("link"),
  isRead: boolean("is_read").default(false),
  createdAt: timestamp("created_at").defaultNow(),
});

export const assessments = pgTable("assessments", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  lessonId: varchar("lesson_id").notNull().references(() => lessons.id),
  nameAr: varchar("name_ar").notNull(),
  nameEn: varchar("name_en"),
  assessmentType: assessmentTypeEnum("assessment_type").default("quiz"),
  questions: jsonb("questions").notNull(),
  passingScore: integer("passing_score").default(60),
  timeLimitMinutes: integer("time_limit_minutes"),
  orderIndex: integer("order_index").notNull().default(0),
});

export const studentProgress = pgTable("student_progress", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  userId: varchar("user_id").notNull().references(() => users.id),
  lessonId: varchar("lesson_id").notNull().references(() => lessons.id),
  isCompleted: boolean("is_completed").default(false),
  progressPercent: real("progress_percent").default(0),
  timeSpentMinutes: integer("time_spent_minutes").default(0),
  videoPosition: integer("video_position").default(0),
  lastAccessedAt: timestamp("last_accessed_at").defaultNow(),
});

export const assessmentResults = pgTable("assessment_results", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  userId: varchar("user_id").notNull().references(() => users.id),
  assessmentId: varchar("assessment_id").notNull().references(() => assessments.id),
  score: real("score").notNull(),
  answers: jsonb("answers"),
  attemptNumber: integer("attempt_number").default(1),
  completedAt: timestamp("completed_at").defaultNow(),
});

export const subscriptions = pgTable("subscriptions", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  userId: varchar("user_id").notNull().references(() => users.id),
  plan: subscriptionPlanEnum("plan").notNull(),
  status: subscriptionStatusEnum("status").default("trial"),
  curriculumId: varchar("curriculum_id").references(() => curricula.id),
  startDate: timestamp("start_date").defaultNow(),
  endDate: timestamp("end_date"),
  autoRenew: boolean("auto_renew").default(true),
});

export const subscriptionPlans = pgTable("subscription_plans", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  nameAr: varchar("name_ar").notNull(),
  nameEn: varchar("name_en"),
  price: real("price").notNull(),
  period: varchar("period").notNull(),
  features: text("features").array(),
  isActive: boolean("is_active").default(true),
  isPopular: boolean("is_popular").default(false),
  orderIndex: integer("order_index").notNull().default(0),
});

export const teacherAssignments = pgTable("teacher_assignments", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  teacherId: varchar("teacher_id").notNull().references(() => users.id),
  subjectId: varchar("subject_id").notNull().references(() => subjects.id),
  hourlyRate: real("hourly_rate"),
});

export const discussionForums = pgTable("discussion_forums", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  subjectId: varchar("subject_id").references(() => subjects.id),
  titleAr: varchar("title_ar").notNull(),
  titleEn: varchar("title_en"),
  descriptionAr: text("description_ar"),
  descriptionEn: text("description_en"),
  isGeneral: boolean("is_general").default(false),
  createdAt: timestamp("created_at").defaultNow(),
});

export const discussionPosts = pgTable("discussion_posts", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  forumId: varchar("forum_id").notNull().references(() => discussionForums.id),
  userId: varchar("user_id").notNull().references(() => users.id),
  titleAr: varchar("title_ar").notNull(),
  titleEn: varchar("title_en"),
  contentAr: text("content_ar").notNull(),
  contentEn: text("content_en"),
  isPinned: boolean("is_pinned").default(false),
  likesCount: integer("likes_count").default(0),
  repliesCount: integer("replies_count").default(0),
  createdAt: timestamp("created_at").defaultNow(),
});

export const discussionReplies = pgTable("discussion_replies", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  postId: varchar("post_id").notNull().references(() => discussionPosts.id),
  userId: varchar("user_id").notNull().references(() => users.id),
  content: text("content").notNull(),
  isInstructorReply: boolean("is_instructor_reply").default(false),
  likesCount: integer("likes_count").default(0),
  createdAt: timestamp("created_at").defaultNow(),
});

export const badgeDefinitions = pgTable("badge_definitions", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  nameAr: varchar("name_ar").notNull(),
  nameEn: varchar("name_en"),
  descriptionAr: text("description_ar"),
  descriptionEn: text("description_en"),
  iconName: varchar("icon_name").notNull(),
  color: varchar("color").default("#10b981"),
  criteria: varchar("criteria").notNull(),
  threshold: integer("threshold").default(1),
});

export const userBadges = pgTable("user_badges", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  userId: varchar("user_id").notNull().references(() => users.id),
  badgeId: varchar("badge_id").notNull().references(() => badgeDefinitions.id),
  earnedAt: timestamp("earned_at").defaultNow(),
});

export const userPoints = pgTable("user_points", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  userId: varchar("user_id").notNull().references(() => users.id),
  points: integer("points").notNull(),
  reason: varchar("reason").notNull(),
  referenceId: varchar("reference_id"),
  createdAt: timestamp("created_at").defaultNow(),
});

export const libraryItems = pgTable("library_items", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  titleAr: varchar("title_ar").notNull(),
  titleEn: varchar("title_en"),
  descriptionAr: text("description_ar"),
  descriptionEn: text("description_en"),
  category: varchar("category").notNull(),
  fileUrl: varchar("file_url"),
  thumbnailUrl: varchar("thumbnail_url"),
  curriculumId: varchar("curriculum_id").references(() => curricula.id),
  subjectId: varchar("subject_id").references(() => subjects.id),
  gradeId: varchar("grade_id").references(() => grades.id),
  authorAr: varchar("author_ar"),
  authorEn: varchar("author_en"),
  downloadCount: integer("download_count").default(0),
  isPublic: boolean("is_public").default(true),
  createdAt: timestamp("created_at").defaultNow(),
});

export const readingProgress = pgTable("reading_progress", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  userId: varchar("user_id").notNull().references(() => users.id),
  libraryItemId: varchar("library_item_id").notNull().references(() => libraryItems.id),
  currentPage: integer("current_page").notNull().default(1),
  totalPages: integer("total_pages").default(0),
  lastReadAt: timestamp("last_read_at").defaultNow(),
  createdAt: timestamp("created_at").defaultNow(),
});

export const attendanceSessions = pgTable("attendance_sessions", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  subjectId: varchar("subject_id").notNull().references(() => subjects.id),
  teacherId: varchar("teacher_id").notNull().references(() => users.id),
  sessionDate: timestamp("session_date").notNull().defaultNow(),
  titleAr: varchar("title_ar"),
  titleEn: varchar("title_en"),
  notes: text("notes"),
  createdAt: timestamp("created_at").defaultNow(),
});

export const attendanceRecords = pgTable("attendance_records", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  sessionId: varchar("session_id").notNull().references(() => attendanceSessions.id),
  studentId: varchar("student_id").notNull().references(() => users.id),
  status: varchar("status").notNull().default("present"),
  notes: text("notes"),
});

export const parentChildren = pgTable("parent_children", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  parentId: varchar("parent_id").notNull().references(() => users.id),
  childId: varchar("child_id").notNull().references(() => users.id),
  createdAt: timestamp("created_at").defaultNow(),
});

// Virtual School Tables
export const leaveRequestStatusEnum = pgEnum("leave_request_status", ["pending", "approved", "rejected"]);
export const dayOfWeekEnum = pgEnum("day_of_week", ["sunday", "monday", "tuesday", "wednesday", "thursday"]);

export const schoolClasses = pgTable("school_classes", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  nameAr: varchar("name_ar").notNull(),
  nameEn: varchar("name_en"),
  gradeId: varchar("grade_id").references(() => grades.id),
  capacity: integer("capacity").default(30),
  roomNumber: varchar("room_number"),
  isActive: boolean("is_active").default(true),
  academicYear: varchar("academic_year"),
  createdAt: timestamp("created_at").defaultNow(),
});

export const classEnrollments = pgTable("class_enrollments", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  classId: varchar("class_id").notNull().references(() => schoolClasses.id),
  studentId: varchar("student_id").notNull().references(() => users.id),
  enrolledAt: timestamp("enrolled_at").defaultNow(),
});

export const classTeachers = pgTable("class_teachers", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  classId: varchar("class_id").notNull().references(() => schoolClasses.id),
  teacherId: varchar("teacher_id").notNull().references(() => users.id),
  subjectId: varchar("subject_id").references(() => subjects.id),
  isHomeroom: boolean("is_homeroom").default(false),
});

export const schedulePeriods = pgTable("schedule_periods", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  periodNumber: integer("period_number").notNull(),
  startTime: varchar("start_time").notNull(),
  endTime: varchar("end_time").notNull(),
  isBreak: boolean("is_break").default(false),
  labelAr: varchar("label_ar"),
  labelEn: varchar("label_en"),
});

export const classSchedules = pgTable("class_schedules", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  classId: varchar("class_id").notNull().references(() => schoolClasses.id),
  periodId: varchar("period_id").notNull().references(() => schedulePeriods.id),
  dayOfWeek: dayOfWeekEnum("day_of_week").notNull(),
  subjectId: varchar("subject_id").references(() => subjects.id),
  teacherId: varchar("teacher_id").references(() => users.id),
  roomNumber: varchar("room_number"),
});

export const weeklyPlans = pgTable("weekly_plans", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  teacherId: varchar("teacher_id").notNull().references(() => users.id),
  classId: varchar("class_id").notNull().references(() => schoolClasses.id),
  subjectId: varchar("subject_id").notNull().references(() => subjects.id),
  weekStart: timestamp("week_start").notNull(),
  titleAr: varchar("title_ar"),
  titleEn: varchar("title_en"),
  status: varchar("status").default("draft"),
  createdAt: timestamp("created_at").defaultNow(),
});

export const weeklyPlanItems = pgTable("weekly_plan_items", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  planId: varchar("plan_id").notNull().references(() => weeklyPlans.id),
  dayOfWeek: dayOfWeekEnum("day_of_week").notNull(),
  topicAr: varchar("topic_ar"),
  topicEn: varchar("topic_en"),
  objectivesAr: text("objectives_ar"),
  objectivesEn: text("objectives_en"),
  activitiesAr: text("activities_ar"),
  activitiesEn: text("activities_en"),
  homeworkAr: text("homework_ar"),
  homeworkEn: text("homework_en"),
  resources: text("resources"),
  notes: text("notes"),
});

export const leaveRequests = pgTable("leave_requests", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  studentId: varchar("student_id").notNull().references(() => users.id),
  parentId: varchar("parent_id").notNull().references(() => users.id),
  requestType: varchar("request_type").notNull().default("sick"),
  startDate: timestamp("start_date").notNull(),
  endDate: timestamp("end_date").notNull(),
  reasonAr: text("reason_ar"),
  reasonEn: text("reason_en"),
  attachmentUrl: varchar("attachment_url"),
  status: leaveRequestStatusEnum("status").default("pending"),
  reviewedBy: varchar("reviewed_by").references(() => users.id),
  reviewNotes: text("review_notes"),
  createdAt: timestamp("created_at").defaultNow(),
  reviewedAt: timestamp("reviewed_at"),
});

export const interactiveLessons = pgTable("interactive_lessons", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  creatorId: varchar("creator_id").notNull().references(() => users.id),
  titleAr: varchar("title_ar").notNull(),
  titleEn: varchar("title_en"),
  descriptionAr: text("description_ar"),
  descriptionEn: text("description_en"),
  subjectId: varchar("subject_id").references(() => subjects.id),
  gradeId: varchar("grade_id").references(() => grades.id),
  curriculumId: varchar("curriculum_id").references(() => curricula.id),
  educationalStage: educationalStageEnum("educational_stage").notNull(),
  contentType: interactiveContentTypeEnum("content_type").notNull().default("lesson"),
  contentData: jsonb("content_data").notNull(),
  gameTemplate: varchar("game_template"),
  isPublished: boolean("is_published").default(false),
  assignedGrades: text("assigned_grades").array(),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

export const interactiveLessonResults = pgTable("interactive_lesson_results", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  userId: varchar("user_id").notNull().references(() => users.id),
  interactiveLessonId: varchar("interactive_lesson_id").notNull().references(() => interactiveLessons.id),
  score: real("score"),
  maxScore: real("max_score"),
  completedSlides: integer("completed_slides").default(0),
  totalSlides: integer("total_slides").default(0),
  timeSpentSeconds: integer("time_spent_seconds").default(0),
  answers: jsonb("answers"),
  completedAt: timestamp("completed_at").defaultNow(),
});

// Relations
export const curriculaRelations = relations(curricula, ({ many }) => ({
  grades: many(grades),
}));

export const gradesRelations = relations(grades, ({ one, many }) => ({
  curriculum: one(curricula, { fields: [grades.curriculumId], references: [curricula.id] }),
  subjects: many(subjects),
}));

export const subjectsRelations = relations(subjects, ({ one, many }) => ({
  grade: one(grades, { fields: [subjects.gradeId], references: [grades.id] }),
  sections: many(courseSections),
  lessons: many(lessons),
}));

export const courseSectionsRelations = relations(courseSections, ({ one, many }) => ({
  subject: one(subjects, { fields: [courseSections.subjectId], references: [subjects.id] }),
  lessons: many(lessons),
}));

export const lessonsRelations = relations(lessons, ({ one, many }) => ({
  subject: one(subjects, { fields: [lessons.subjectId], references: [subjects.id] }),
  section: one(courseSections, { fields: [lessons.sectionId], references: [courseSections.id] }),
  assessments: many(assessments),
  progress: many(studentProgress),
  resources: many(lessonResources),
  notes: many(lessonNotes),
  bookmarks: many(lessonBookmarks),
  qna: many(lessonQna),
}));

export const lessonResourcesRelations = relations(lessonResources, ({ one }) => ({
  lesson: one(lessons, { fields: [lessonResources.lessonId], references: [lessons.id] }),
}));

export const lessonNotesRelations = relations(lessonNotes, ({ one }) => ({
  user: one(users, { fields: [lessonNotes.userId], references: [users.id] }),
  lesson: one(lessons, { fields: [lessonNotes.lessonId], references: [lessons.id] }),
}));

export const lessonBookmarksRelations = relations(lessonBookmarks, ({ one }) => ({
  user: one(users, { fields: [lessonBookmarks.userId], references: [users.id] }),
  lesson: one(lessons, { fields: [lessonBookmarks.lessonId], references: [lessons.id] }),
}));

export const lessonQnaRelations = relations(lessonQna, ({ one }) => ({
  lesson: one(lessons, { fields: [lessonQna.lessonId], references: [lessons.id] }),
  user: one(users, { fields: [lessonQna.userId], references: [users.id] }),
}));

export const notificationsRelations = relations(notifications, ({ one }) => ({
  user: one(users, { fields: [notifications.userId], references: [users.id] }),
}));

export const assessmentsRelations = relations(assessments, ({ one }) => ({
  lesson: one(lessons, { fields: [assessments.lessonId], references: [lessons.id] }),
}));

export const studentProgressRelations = relations(studentProgress, ({ one }) => ({
  user: one(users, { fields: [studentProgress.userId], references: [users.id] }),
  lesson: one(lessons, { fields: [studentProgress.lessonId], references: [lessons.id] }),
}));

export const discussionForumsRelations = relations(discussionForums, ({ one, many }) => ({
  subject: one(subjects, { fields: [discussionForums.subjectId], references: [subjects.id] }),
  posts: many(discussionPosts),
}));

export const discussionPostsRelations = relations(discussionPosts, ({ one, many }) => ({
  forum: one(discussionForums, { fields: [discussionPosts.forumId], references: [discussionForums.id] }),
  user: one(users, { fields: [discussionPosts.userId], references: [users.id] }),
  replies: many(discussionReplies),
}));

export const discussionRepliesRelations = relations(discussionReplies, ({ one }) => ({
  post: one(discussionPosts, { fields: [discussionReplies.postId], references: [discussionPosts.id] }),
  user: one(users, { fields: [discussionReplies.userId], references: [users.id] }),
}));

export const userBadgesRelations = relations(userBadges, ({ one }) => ({
  user: one(users, { fields: [userBadges.userId], references: [users.id] }),
  badge: one(badgeDefinitions, { fields: [userBadges.badgeId], references: [badgeDefinitions.id] }),
}));

export const userPointsRelations = relations(userPoints, ({ one }) => ({
  user: one(users, { fields: [userPoints.userId], references: [users.id] }),
}));

export const libraryItemsRelations = relations(libraryItems, ({ one }) => ({
  subject: one(subjects, { fields: [libraryItems.subjectId], references: [subjects.id] }),
  grade: one(grades, { fields: [libraryItems.gradeId], references: [grades.id] }),
}));

// Insert schemas
export const insertCurriculumSchema = createInsertSchema(curricula).omit({ id: true });
export const insertGradeSchema = createInsertSchema(grades).omit({ id: true });
export const insertSubjectSchema = createInsertSchema(subjects).omit({ id: true });
export const insertCourseSectionSchema = createInsertSchema(courseSections).omit({ id: true });
export const insertLessonSchema = createInsertSchema(lessons).omit({ id: true });
export const insertLessonResourceSchema = createInsertSchema(lessonResources).omit({ id: true });
export const insertLessonNoteSchema = createInsertSchema(lessonNotes).omit({ id: true });
export const insertLessonBookmarkSchema = createInsertSchema(lessonBookmarks).omit({ id: true });
export const insertLessonQnaSchema = createInsertSchema(lessonQna).omit({ id: true });
export const insertNotificationSchema = createInsertSchema(notifications).omit({ id: true });
export const insertAssessmentSchema = createInsertSchema(assessments).omit({ id: true });
export const insertProgressSchema = createInsertSchema(studentProgress).omit({ id: true });
export const insertResultSchema = createInsertSchema(assessmentResults).omit({ id: true });
export const insertSubscriptionSchema = createInsertSchema(subscriptions).omit({ id: true });
export const insertProfileSchema = createInsertSchema(userProfiles).omit({ id: true });
export const insertTeacherAssignmentSchema = createInsertSchema(teacherAssignments).omit({ id: true });
export const insertDiscussionForumSchema = createInsertSchema(discussionForums).omit({ id: true });
export const insertDiscussionPostSchema = createInsertSchema(discussionPosts).omit({ id: true });
export const insertDiscussionReplySchema = createInsertSchema(discussionReplies).omit({ id: true });
export const insertBadgeDefinitionSchema = createInsertSchema(badgeDefinitions).omit({ id: true });
export const insertUserBadgeSchema = createInsertSchema(userBadges).omit({ id: true });
export const insertUserPointsSchema = createInsertSchema(userPoints).omit({ id: true });
export const insertLibraryItemSchema = createInsertSchema(libraryItems).omit({ id: true });
export const insertAttendanceSessionSchema = createInsertSchema(attendanceSessions).omit({ id: true });
export const insertAttendanceRecordSchema = createInsertSchema(attendanceRecords).omit({ id: true });
export const insertParentChildSchema = createInsertSchema(parentChildren).omit({ id: true });
export const insertSubscriptionPlanSchema = createInsertSchema(subscriptionPlans).omit({ id: true });
export const insertSchoolClassSchema = createInsertSchema(schoolClasses).omit({ id: true });
export const insertClassEnrollmentSchema = createInsertSchema(classEnrollments).omit({ id: true });
export const insertClassTeacherSchema = createInsertSchema(classTeachers).omit({ id: true });
export const insertSchedulePeriodSchema = createInsertSchema(schedulePeriods).omit({ id: true });
export const insertClassScheduleSchema = createInsertSchema(classSchedules).omit({ id: true });
export const insertWeeklyPlanSchema = createInsertSchema(weeklyPlans).omit({ id: true });
export const insertWeeklyPlanItemSchema = createInsertSchema(weeklyPlanItems).omit({ id: true });
export const insertLeaveRequestSchema = createInsertSchema(leaveRequests).omit({ id: true });
export const insertInteractiveLessonSchema = createInsertSchema(interactiveLessons).omit({ id: true, createdAt: true, updatedAt: true });
export const insertInteractiveLessonResultSchema = createInsertSchema(interactiveLessonResults).omit({ id: true, completedAt: true });

// Insert types
export type InsertCurriculum = z.infer<typeof insertCurriculumSchema>;
export type InsertGrade = z.infer<typeof insertGradeSchema>;
export type InsertSubject = z.infer<typeof insertSubjectSchema>;
export type InsertCourseSection = z.infer<typeof insertCourseSectionSchema>;
export type InsertLesson = z.infer<typeof insertLessonSchema>;
export type InsertLessonResource = z.infer<typeof insertLessonResourceSchema>;
export type InsertLessonNote = z.infer<typeof insertLessonNoteSchema>;
export type InsertLessonBookmark = z.infer<typeof insertLessonBookmarkSchema>;
export type InsertLessonQna = z.infer<typeof insertLessonQnaSchema>;
export type InsertNotification = z.infer<typeof insertNotificationSchema>;
export type InsertAssessment = z.infer<typeof insertAssessmentSchema>;
export type InsertProgress = z.infer<typeof insertProgressSchema>;
export type InsertResult = z.infer<typeof insertResultSchema>;
export type InsertSubscription = z.infer<typeof insertSubscriptionSchema>;
export type InsertProfile = z.infer<typeof insertProfileSchema>;
export type InsertTeacherAssignment = z.infer<typeof insertTeacherAssignmentSchema>;
export type InsertDiscussionForum = z.infer<typeof insertDiscussionForumSchema>;
export type InsertDiscussionPost = z.infer<typeof insertDiscussionPostSchema>;
export type InsertDiscussionReply = z.infer<typeof insertDiscussionReplySchema>;
export type InsertBadgeDefinition = z.infer<typeof insertBadgeDefinitionSchema>;
export type InsertUserBadge = z.infer<typeof insertUserBadgeSchema>;
export type InsertUserPoints = z.infer<typeof insertUserPointsSchema>;
export type InsertLibraryItem = z.infer<typeof insertLibraryItemSchema>;
export type InsertAttendanceSession = z.infer<typeof insertAttendanceSessionSchema>;
export type InsertAttendanceRecord = z.infer<typeof insertAttendanceRecordSchema>;
export type InsertParentChild = z.infer<typeof insertParentChildSchema>;
export type InsertSubscriptionPlan = z.infer<typeof insertSubscriptionPlanSchema>;
export type InsertSchoolClass = z.infer<typeof insertSchoolClassSchema>;
export type InsertClassEnrollment = z.infer<typeof insertClassEnrollmentSchema>;
export type InsertClassTeacher = z.infer<typeof insertClassTeacherSchema>;
export type InsertSchedulePeriod = z.infer<typeof insertSchedulePeriodSchema>;
export type InsertClassSchedule = z.infer<typeof insertClassScheduleSchema>;
export type InsertWeeklyPlan = z.infer<typeof insertWeeklyPlanSchema>;
export type InsertWeeklyPlanItem = z.infer<typeof insertWeeklyPlanItemSchema>;
export type InsertLeaveRequest = z.infer<typeof insertLeaveRequestSchema>;
export type InsertInteractiveLesson = z.infer<typeof insertInteractiveLessonSchema>;
export type InsertInteractiveLessonResult = z.infer<typeof insertInteractiveLessonResultSchema>;

// Select types
export type Curriculum = typeof curricula.$inferSelect;
export type Grade = typeof grades.$inferSelect;
export type Subject = typeof subjects.$inferSelect;
export type CourseSection = typeof courseSections.$inferSelect;
export type Lesson = typeof lessons.$inferSelect;
export type LessonResource = typeof lessonResources.$inferSelect;
export type LessonNote = typeof lessonNotes.$inferSelect;
export type LessonBookmark = typeof lessonBookmarks.$inferSelect;
export type LessonQna = typeof lessonQna.$inferSelect;
export type Notification = typeof notifications.$inferSelect;
export type Assessment = typeof assessments.$inferSelect;
export type StudentProgress = typeof studentProgress.$inferSelect;
export type AssessmentResult = typeof assessmentResults.$inferSelect;
export type Subscription = typeof subscriptions.$inferSelect;
export type UserProfile = typeof userProfiles.$inferSelect;
export type TeacherAssignment = typeof teacherAssignments.$inferSelect;
export type DiscussionForum = typeof discussionForums.$inferSelect;
export type DiscussionPost = typeof discussionPosts.$inferSelect;
export type DiscussionReply = typeof discussionReplies.$inferSelect;
export type BadgeDefinition = typeof badgeDefinitions.$inferSelect;
export type UserBadge = typeof userBadges.$inferSelect;
export type UserPointEntry = typeof userPoints.$inferSelect;
export type LibraryItem = typeof libraryItems.$inferSelect;
export type AttendanceSession = typeof attendanceSessions.$inferSelect;
export type AttendanceRecord = typeof attendanceRecords.$inferSelect;
export type ParentChild = typeof parentChildren.$inferSelect;
export type SubscriptionPlan = typeof subscriptionPlans.$inferSelect;
export type SchoolClass = typeof schoolClasses.$inferSelect;
export type ClassEnrollment = typeof classEnrollments.$inferSelect;
export type ClassTeacher = typeof classTeachers.$inferSelect;
export type SchedulePeriod = typeof schedulePeriods.$inferSelect;
export type ClassSchedule = typeof classSchedules.$inferSelect;
export type WeeklyPlan = typeof weeklyPlans.$inferSelect;
export type WeeklyPlanItem = typeof weeklyPlanItems.$inferSelect;
export type LeaveRequest = typeof leaveRequests.$inferSelect;
export type InteractiveLesson = typeof interactiveLessons.$inferSelect;
export type InteractiveLessonResult = typeof interactiveLessonResults.$inferSelect;