import {
  curricula, grades, subjects, lessons, assessments,
  studentProgress, assessmentResults, subscriptions, userProfiles,
  teacherAssignments, courseSections, lessonResources, lessonNotes,
  lessonBookmarks, lessonQna, notifications,
  discussionForums, discussionPosts, discussionReplies,
  badgeDefinitions, userBadges, userPoints, libraryItems, readingProgress,
  attendanceSessions, attendanceRecords, parentChildren,
  subscriptionPlans,
  schoolClasses, classEnrollments, classTeachers, schedulePeriods,
  classSchedules, weeklyPlans, weeklyPlanItems, leaveRequests,
  interactiveLessons, interactiveLessonResults,
  type Curriculum, type Grade, type Subject, type Lesson,
  type Assessment, type StudentProgress, type AssessmentResult,
  type Subscription, type UserProfile, type TeacherAssignment,
  type CourseSection, type LessonResource, type LessonNote,
  type LessonBookmark, type LessonQna, type Notification,
  type DiscussionForum, type DiscussionPost, type DiscussionReply,
  type BadgeDefinition, type UserBadge, type LibraryItem,
  type AttendanceSession, type AttendanceRecord, type ParentChild,
  type SubscriptionPlan,
  type SchoolClass, type ClassEnrollment, type ClassTeacher,
  type SchedulePeriod, type ClassSchedule, type WeeklyPlan,
  type WeeklyPlanItem, type LeaveRequest,
  type InsertCurriculum, type InsertGrade, type InsertSubject,
  type InsertLesson, type InsertAssessment, type InsertProgress,
  type InsertResult, type InsertSubscription, type InsertProfile,
  type InsertTeacherAssignment, type InsertCourseSection,
  type InsertLessonResource, type InsertLessonNote,
  type InsertLessonBookmark, type InsertLessonQna, type InsertNotification,
  type InsertDiscussionPost, type InsertDiscussionReply,
  type InsertAttendanceSession, type InsertSubscriptionPlan,
  type InsertSchoolClass, type InsertClassEnrollment, type InsertClassTeacher,
  type InsertSchedulePeriod, type InsertClassSchedule, type InsertWeeklyPlan,
  type InsertWeeklyPlanItem, type InsertLeaveRequest,
} from "@shared/schema";
import { db } from "./db";
import { eq, and, or, desc, sql, count, asc, isNull, sum, inArray } from "drizzle-orm";

export interface IStorage {
  getUser(id: string): Promise<any>;
  getCurricula(): Promise<Curriculum[]>;
  getCurriculum(id: string): Promise<Curriculum | undefined>;
  createCurriculum(data: InsertCurriculum): Promise<Curriculum>;

  getGradesByCurriculum(curriculumId: string): Promise<(Grade & { subjects: Subject[] })[]>;
  createGrade(data: InsertGrade): Promise<Grade>;

  getSubject(id: string): Promise<Subject | undefined>;
  getAllSubjects(): Promise<Subject[]>;
  getSubjectsByGrade(gradeId: string): Promise<Subject[]>;
  createSubject(data: InsertSubject): Promise<Subject>;

  getSectionsBySubject(subjectId: string): Promise<CourseSection[]>;
  getSection(id: string): Promise<CourseSection | undefined>;
  createSection(data: InsertCourseSection): Promise<CourseSection>;

  getLesson(id: string): Promise<Lesson | undefined>;
  getLessonsBySubject(subjectId: string): Promise<Lesson[]>;
  getLessonsBySection(sectionId: string): Promise<Lesson[]>;
  getSubjectLessonsGrouped(subjectId: string): Promise<{ sections: (CourseSection & { lessons: Lesson[] })[]; ungrouped: Lesson[] }>;
  createLesson(data: InsertLesson): Promise<Lesson>;
  getAdjacentLessons(subjectId: string, orderIndex: number): Promise<{ prev?: Lesson; next?: Lesson }>;

  getResourcesByLesson(lessonId: string): Promise<LessonResource[]>;
  createResource(data: InsertLessonResource): Promise<LessonResource>;

  getNotesByLesson(userId: string, lessonId: string): Promise<LessonNote[]>;
  createNote(data: InsertLessonNote): Promise<LessonNote>;
  updateNote(id: string, userId: string, content: string): Promise<LessonNote | undefined>;
  deleteNote(id: string, userId: string): Promise<boolean>;

  getBookmark(userId: string, lessonId: string): Promise<LessonBookmark | undefined>;
  getUserBookmarks(userId: string): Promise<(LessonBookmark & { lesson?: Lesson })[]>;
  toggleBookmark(userId: string, lessonId: string): Promise<{ bookmarked: boolean }>;

  getQnaByLesson(lessonId: string): Promise<(LessonQna & { user?: { firstName: string | null; lastName: string | null; profileImageUrl: string | null }; replies?: LessonQna[] })[]>;
  createQna(data: InsertLessonQna): Promise<LessonQna>;

  getNotifications(userId: string, limit?: number): Promise<Notification[]>;
  getUnreadNotificationCount(userId: string): Promise<number>;
  markNotificationRead(id: string, userId: string): Promise<void>;
  markAllNotificationsRead(userId: string): Promise<void>;
  createNotification(data: InsertNotification): Promise<Notification>;

  getAssessmentsByLesson(lessonId: string): Promise<Assessment[]>;
  getAssessment(id: string): Promise<Assessment | undefined>;
  createAssessment(data: InsertAssessment): Promise<Assessment>;

  getProgress(userId: string, lessonId: string): Promise<StudentProgress | undefined>;
  getUserProgress(userId: string): Promise<StudentProgress[]>;
  upsertProgress(data: InsertProgress): Promise<StudentProgress>;

  createAssessmentResult(data: InsertResult): Promise<AssessmentResult>;
  getAssessmentResults(userId: string): Promise<AssessmentResult[]>;

  getSubscription(userId: string): Promise<Subscription | undefined>;
  createSubscription(data: InsertSubscription): Promise<Subscription>;

  getProfile(userId: string): Promise<UserProfile | undefined>;
  upsertProfile(data: InsertProfile): Promise<UserProfile>;

  getDashboardStats(userId: string): Promise<any>;
  getTodaySummary(userId: string): Promise<{ completedToday: number; dailyGoal: number; lastLesson: any | null }>;
  getRecentLessons(userId: string): Promise<any[]>;
  getUserLessonsWithProgress(userId: string): Promise<any[]>;
  getProgressBySubject(userId: string): Promise<any[]>;
  getWeeklyActivity(userId: string): Promise<{ day: string; lessons: number; minutes: number }[]>;
  getStudyStreak(userId: string): Promise<{ currentStreak: number; longestStreak: number; totalDays: number }>;
  getReports(userId: string): Promise<any[]>;

  getForums(): Promise<DiscussionForum[]>;
  getForumPosts(forumId: string): Promise<any[]>;
  createPost(data: InsertDiscussionPost): Promise<DiscussionPost>;
  getPostReplies(postId: string): Promise<any[]>;
  createReply(data: InsertDiscussionReply): Promise<DiscussionReply>;

  getBadgeDefinitions(): Promise<BadgeDefinition[]>;
  getUserBadges(userId: string): Promise<any[]>;
  awardBadge(userId: string, badgeId: string): Promise<void>;
  addPoints(userId: string, points: number, reason: string, referenceId?: string): Promise<void>;
  getUserPoints(userId: string): Promise<number>;
  getLeaderboard(limit?: number): Promise<{ userId: string; firstName: string | null; lastName: string | null; totalPoints: number }[]>;

  getLibraryItems(category?: string, subjectId?: string): Promise<LibraryItem[]>;
  getLibraryItem(id: string): Promise<LibraryItem | undefined>;

  getAllUsers(): Promise<any[]>;
  updateUserRole(userId: string, role: string): Promise<any>;
  getUsersByRole(role: string): Promise<any[]>;

  getTeacherStudents(teacherId: string): Promise<any[]>;
  getTeacherStats(teacherId: string): Promise<any>;

  getParentChildren(parentId: string): Promise<any[]>;
  addChild(parentId: string, childId: string): Promise<any>;
  removeChild(parentId: string, childId: string): Promise<boolean>;

  createAttendanceSession(data: InsertAttendanceSession): Promise<AttendanceSession>;
  getAttendanceSessions(subjectId?: string, teacherId?: string): Promise<any[]>;
  getAttendanceSession(id: string): Promise<any>;
  markAttendance(sessionId: string, records: { studentId: string; status: string; notes?: string }[]): Promise<void>;
  getStudentAttendance(studentId: string): Promise<any[]>;
  getAttendanceStats(studentId: string): Promise<{ present: number; absent: number; late: number; excused: number; total: number; rate: number }>;

  deleteCurriculum(id: string): Promise<boolean>;
  deleteGrade(id: string): Promise<boolean>;
  deleteSubject(id: string): Promise<boolean>;
  deleteSection(id: string): Promise<boolean>;
  deleteLesson(id: string): Promise<boolean>;
  deleteAssessment(id: string): Promise<boolean>;

  updateCurriculum(id: string, data: Partial<InsertCurriculum>): Promise<Curriculum | undefined>;
  updateGrade(id: string, data: Partial<InsertGrade>): Promise<Grade | undefined>;
  updateSubject(id: string, data: Partial<InsertSubject>): Promise<Subject | undefined>;
  updateSection(id: string, data: Partial<InsertCourseSection>): Promise<CourseSection | undefined>;
  updateLesson(id: string, data: Partial<InsertLesson>): Promise<Lesson | undefined>;
  updateAssessment(id: string, data: Partial<InsertAssessment>): Promise<Assessment | undefined>;

  deleteResource(id: string): Promise<boolean>;

  updateUser(userId: string, data: { firstName?: string; lastName?: string; email?: string }): Promise<any>;
  updateUserProfile(userId: string, data: { phone?: string; bio?: string; preferredLanguage?: string; assignedCurricula?: string[] }): Promise<any>;
  resetUserPassword(userId: string, newPassword: string): Promise<boolean>;
  deleteUser(userId: string): Promise<boolean>;

  getSubscriptionPlans(): Promise<SubscriptionPlan[]>;
  getSubscriptionPlan(id: string): Promise<SubscriptionPlan | undefined>;
  createSubscriptionPlan(data: InsertSubscriptionPlan): Promise<SubscriptionPlan>;
  updateSubscriptionPlan(id: string, data: Partial<InsertSubscriptionPlan>): Promise<SubscriptionPlan | undefined>;
  deleteSubscriptionPlan(id: string): Promise<boolean>;

  getAllSubscriptions(): Promise<any[]>;
  updateSubscription(id: string, data: Partial<InsertSubscription>): Promise<Subscription | undefined>;
  deleteSubscription(id: string): Promise<boolean>;
  createAdminSubscription(data: InsertSubscription): Promise<Subscription>;

  // Virtual School
  getSchoolClasses(): Promise<SchoolClass[]>;
  getSchoolClass(id: string): Promise<SchoolClass | undefined>;
  createSchoolClass(data: InsertSchoolClass): Promise<SchoolClass>;
  updateSchoolClass(id: string, data: Partial<InsertSchoolClass>): Promise<SchoolClass | undefined>;
  deleteSchoolClass(id: string): Promise<boolean>;

  getClassEnrollments(classId: string): Promise<ClassEnrollment[]>;
  createClassEnrollment(data: InsertClassEnrollment): Promise<ClassEnrollment>;
  deleteClassEnrollment(id: string): Promise<boolean>;

  getClassTeachers(classId: string): Promise<ClassTeacher[]>;
  createClassTeacher(data: InsertClassTeacher): Promise<ClassTeacher>;
  deleteClassTeacher(id: string): Promise<boolean>;

  getSchedulePeriods(): Promise<SchedulePeriod[]>;
  createSchedulePeriod(data: InsertSchedulePeriod): Promise<SchedulePeriod>;
  updateSchedulePeriod(id: string, data: Partial<InsertSchedulePeriod>): Promise<SchedulePeriod | undefined>;
  deleteSchedulePeriod(id: string): Promise<boolean>;

  getClassSchedules(classId: string): Promise<ClassSchedule[]>;
  createClassSchedule(data: InsertClassSchedule): Promise<ClassSchedule>;
  updateClassSchedule(id: string, data: Partial<InsertClassSchedule>): Promise<ClassSchedule | undefined>;
  deleteClassSchedule(id: string): Promise<boolean>;

  getWeeklyPlans(teacherId?: string, classId?: string): Promise<WeeklyPlan[]>;
  getWeeklyPlan(id: string): Promise<WeeklyPlan | undefined>;
  createWeeklyPlan(data: InsertWeeklyPlan): Promise<WeeklyPlan>;
  updateWeeklyPlan(id: string, data: Partial<InsertWeeklyPlan>): Promise<WeeklyPlan | undefined>;
  deleteWeeklyPlan(id: string): Promise<boolean>;

  getWeeklyPlanItems(planId: string): Promise<WeeklyPlanItem[]>;
  createWeeklyPlanItem(data: InsertWeeklyPlanItem): Promise<WeeklyPlanItem>;
  updateWeeklyPlanItem(id: string, data: Partial<InsertWeeklyPlanItem>): Promise<WeeklyPlanItem | undefined>;
  deleteWeeklyPlanItem(id: string): Promise<boolean>;

  getLeaveRequests(filters?: { studentId?: string; parentId?: string; status?: string }): Promise<LeaveRequest[]>;
  getLeaveRequest(id: string): Promise<LeaveRequest | undefined>;
  createLeaveRequest(data: InsertLeaveRequest): Promise<LeaveRequest>;
  updateLeaveRequest(id: string, data: Partial<InsertLeaveRequest>): Promise<LeaveRequest | undefined>;

  getStudentClassId(studentId: string): Promise<string | undefined>;

  getInteractiveLessons(filters?: { creatorId?: string; educationalStage?: string; subjectId?: string }): Promise<any[]>;
  getInteractiveLesson(id: string): Promise<any>;
  createInteractiveLesson(data: any): Promise<any>;
  updateInteractiveLesson(id: string, data: any): Promise<any>;
  deleteInteractiveLesson(id: string): Promise<boolean>;
  getInteractiveLessonResults(userId: string, lessonId: string): Promise<any>;
  createInteractiveLessonResult(data: any): Promise<any>;
}

export class DatabaseStorage implements IStorage {
  async getUser(id: string): Promise<any> {
    const { users } = await import("@shared/schema");
    const [u] = await db.select().from(users).where(eq(users.id, id));
    return u;
  }

  async getCurricula(): Promise<Curriculum[]> {
    return db.select().from(curricula).where(eq(curricula.isActive, true));
  }

  async getCurriculum(id: string): Promise<Curriculum | undefined> {
    const [c] = await db.select().from(curricula).where(eq(curricula.id, id));
    return c;
  }

  async createCurriculum(data: InsertCurriculum): Promise<Curriculum> {
    const [c] = await db.insert(curricula).values(data).returning();
    return c;
  }

  async getGradesByCurriculum(curriculumId: string): Promise<(Grade & { subjects: Subject[] })[]> {
    const gradeRows = await db.select().from(grades)
      .where(eq(grades.curriculumId, curriculumId))
      .orderBy(grades.orderIndex);

    const result: (Grade & { subjects: Subject[] })[] = [];
    for (const grade of gradeRows) {
      const subjectRows = await db.select().from(subjects)
        .where(eq(subjects.gradeId, grade.id))
        .orderBy(subjects.orderIndex);
      result.push({ ...grade, subjects: subjectRows });
    }
    return result;
  }

  async createGrade(data: InsertGrade): Promise<Grade> {
    const [g] = await db.insert(grades).values(data).returning();
    return g;
  }

  async getSubject(id: string): Promise<Subject | undefined> {
    const [s] = await db.select().from(subjects).where(eq(subjects.id, id));
    return s;
  }

  async getAllSubjects(): Promise<Subject[]> {
    return db.select().from(subjects).orderBy(subjects.orderIndex);
  }

  async getSubjectsByGrade(gradeId: string): Promise<Subject[]> {
    return db.select().from(subjects).where(eq(subjects.gradeId, gradeId)).orderBy(subjects.orderIndex);
  }

  async createSubject(data: InsertSubject): Promise<Subject> {
    const [s] = await db.insert(subjects).values(data).returning();
    return s;
  }

  async getSectionsBySubject(subjectId: string): Promise<CourseSection[]> {
    return db.select().from(courseSections)
      .where(eq(courseSections.subjectId, subjectId))
      .orderBy(asc(courseSections.orderIndex));
  }

  async getSection(id: string): Promise<CourseSection | undefined> {
    const [s] = await db.select().from(courseSections).where(eq(courseSections.id, id));
    return s;
  }

  async createSection(data: InsertCourseSection): Promise<CourseSection> {
    const [s] = await db.insert(courseSections).values(data).returning();
    return s;
  }

  async getLesson(id: string): Promise<Lesson | undefined> {
    const [l] = await db.select().from(lessons).where(eq(lessons.id, id));
    return l;
  }

  async getLessonsBySubject(subjectId: string): Promise<Lesson[]> {
    return db.select().from(lessons)
      .where(and(eq(lessons.subjectId, subjectId), eq(lessons.isPublished, true)))
      .orderBy(lessons.orderIndex);
  }

  async getLessonsBySection(sectionId: string): Promise<Lesson[]> {
    return db.select().from(lessons)
      .where(and(eq(lessons.sectionId, sectionId), eq(lessons.isPublished, true)))
      .orderBy(lessons.orderIndex);
  }

  async getSubjectLessonsGrouped(subjectId: string): Promise<{ sections: (CourseSection & { lessons: Lesson[] })[]; ungrouped: Lesson[] }> {
    const sectionRows = await this.getSectionsBySubject(subjectId);
    const allLessons = await this.getLessonsBySubject(subjectId);

    const sections: (CourseSection & { lessons: Lesson[] })[] = sectionRows.map(sec => ({
      ...sec,
      lessons: allLessons.filter(l => l.sectionId === sec.id),
    }));

    const ungrouped = allLessons.filter(l => !l.sectionId);

    return { sections, ungrouped };
  }

  async createLesson(data: InsertLesson): Promise<Lesson> {
    const [l] = await db.insert(lessons).values(data).returning();
    return l;
  }

  async getAdjacentLessons(subjectId: string, orderIndex: number): Promise<{ prev?: Lesson; next?: Lesson }> {
    const allLessons = await this.getLessonsBySubject(subjectId);
    const currentIdx = allLessons.findIndex(l => l.orderIndex === orderIndex);
    return {
      prev: currentIdx > 0 ? allLessons[currentIdx - 1] : undefined,
      next: currentIdx < allLessons.length - 1 ? allLessons[currentIdx + 1] : undefined,
    };
  }

  async getResourcesByLesson(lessonId: string): Promise<LessonResource[]> {
    return db.select().from(lessonResources)
      .where(eq(lessonResources.lessonId, lessonId))
      .orderBy(lessonResources.orderIndex);
  }

  async createResource(data: InsertLessonResource): Promise<LessonResource> {
    const [r] = await db.insert(lessonResources).values(data).returning();
    return r;
  }

  async getNotesByLesson(userId: string, lessonId: string): Promise<LessonNote[]> {
    return db.select().from(lessonNotes)
      .where(and(eq(lessonNotes.userId, userId), eq(lessonNotes.lessonId, lessonId)))
      .orderBy(desc(lessonNotes.createdAt));
  }

  async createNote(data: InsertLessonNote): Promise<LessonNote> {
    const [n] = await db.insert(lessonNotes).values(data).returning();
    return n;
  }

  async updateNote(id: string, userId: string, content: string): Promise<LessonNote | undefined> {
    const [n] = await db.update(lessonNotes)
      .set({ content, updatedAt: new Date() })
      .where(and(eq(lessonNotes.id, id), eq(lessonNotes.userId, userId)))
      .returning();
    return n;
  }

  async deleteNote(id: string, userId: string): Promise<boolean> {
    const result = await db.delete(lessonNotes)
      .where(and(eq(lessonNotes.id, id), eq(lessonNotes.userId, userId)));
    return true;
  }

  async getBookmark(userId: string, lessonId: string): Promise<LessonBookmark | undefined> {
    const [b] = await db.select().from(lessonBookmarks)
      .where(and(eq(lessonBookmarks.userId, userId), eq(lessonBookmarks.lessonId, lessonId)));
    return b;
  }

  async getUserBookmarks(userId: string): Promise<(LessonBookmark & { lesson?: Lesson })[]> {
    const bookmarkRows = await db.select().from(lessonBookmarks)
      .where(eq(lessonBookmarks.userId, userId))
      .orderBy(desc(lessonBookmarks.createdAt));

    const result: (LessonBookmark & { lesson?: Lesson })[] = [];
    for (const b of bookmarkRows) {
      const lesson = await this.getLesson(b.lessonId);
      result.push({ ...b, lesson: lesson || undefined });
    }
    return result;
  }

  async toggleBookmark(userId: string, lessonId: string): Promise<{ bookmarked: boolean }> {
    const existing = await this.getBookmark(userId, lessonId);
    if (existing) {
      await db.delete(lessonBookmarks).where(eq(lessonBookmarks.id, existing.id));
      return { bookmarked: false };
    }
    await db.insert(lessonBookmarks).values({ userId, lessonId });
    return { bookmarked: true };
  }

  async getQnaByLesson(lessonId: string): Promise<(LessonQna & { user?: { firstName: string | null; lastName: string | null; profileImageUrl: string | null }; replies?: LessonQna[] })[]> {
    const { users } = await import("@shared/schema");
    const allQna = await db.select({
      qna: lessonQna,
      user: {
        firstName: users.firstName,
        lastName: users.lastName,
        profileImageUrl: users.profileImageUrl,
      },
    })
      .from(lessonQna)
      .leftJoin(users, eq(lessonQna.userId, users.id))
      .where(eq(lessonQna.lessonId, lessonId))
      .orderBy(desc(lessonQna.createdAt));

    const topLevel = allQna.filter(q => !q.qna.parentId);
    return topLevel.map(q => ({
      ...q.qna,
      user: q.user || undefined,
      replies: allQna
        .filter(r => r.qna.parentId === q.qna.id)
        .map(r => ({ ...r.qna, user: r.user || undefined })) as any[],
    }));
  }

  async createQna(data: InsertLessonQna): Promise<LessonQna> {
    const [q] = await db.insert(lessonQna).values(data).returning();
    return q;
  }

  async getNotifications(userId: string, limit = 20): Promise<Notification[]> {
    return db.select().from(notifications)
      .where(eq(notifications.userId, userId))
      .orderBy(desc(notifications.createdAt))
      .limit(limit);
  }

  async getUnreadNotificationCount(userId: string): Promise<number> {
    const [result] = await db.select({ count: count() }).from(notifications)
      .where(and(eq(notifications.userId, userId), eq(notifications.isRead, false)));
    return result?.count || 0;
  }

  async markNotificationRead(id: string, userId: string): Promise<void> {
    await db.update(notifications)
      .set({ isRead: true })
      .where(and(eq(notifications.id, id), eq(notifications.userId, userId)));
  }

  async markAllNotificationsRead(userId: string): Promise<void> {
    await db.update(notifications)
      .set({ isRead: true })
      .where(and(eq(notifications.userId, userId), eq(notifications.isRead, false)));
  }

  async createNotification(data: InsertNotification): Promise<Notification> {
    const [n] = await db.insert(notifications).values(data).returning();
    return n;
  }

  async getAssessmentsByLesson(lessonId: string): Promise<Assessment[]> {
    return db.select().from(assessments)
      .where(eq(assessments.lessonId, lessonId))
      .orderBy(assessments.orderIndex);
  }

  async getAssessment(id: string): Promise<Assessment | undefined> {
    const [a] = await db.select().from(assessments).where(eq(assessments.id, id));
    return a;
  }

  async createAssessment(data: InsertAssessment): Promise<Assessment> {
    const [a] = await db.insert(assessments).values(data).returning();
    return a;
  }

  async getProgress(userId: string, lessonId: string): Promise<StudentProgress | undefined> {
    const [p] = await db.select().from(studentProgress)
      .where(and(eq(studentProgress.userId, userId), eq(studentProgress.lessonId, lessonId)));
    return p;
  }

  async getUserProgress(userId: string): Promise<StudentProgress[]> {
    return db.select().from(studentProgress).where(eq(studentProgress.userId, userId));
  }

  async upsertProgress(data: InsertProgress): Promise<StudentProgress> {
    const existing = await this.getProgress(data.userId, data.lessonId);
    if (existing) {
      const [p] = await db.update(studentProgress)
        .set({ ...data, lastAccessedAt: new Date() })
        .where(eq(studentProgress.id, existing.id))
        .returning();
      return p;
    }
    const [p] = await db.insert(studentProgress).values(data).returning();
    return p;
  }

  async createAssessmentResult(data: InsertResult): Promise<AssessmentResult> {
    const [r] = await db.insert(assessmentResults).values(data).returning();
    return r;
  }

  async getAssessmentResults(userId: string): Promise<AssessmentResult[]> {
    return db.select().from(assessmentResults)
      .where(eq(assessmentResults.userId, userId))
      .orderBy(desc(assessmentResults.completedAt));
  }

  async getSubscription(userId: string): Promise<Subscription | undefined> {
    const [s] = await db.select().from(subscriptions)
      .where(eq(subscriptions.userId, userId))
      .orderBy(desc(subscriptions.startDate));
    return s;
  }

  async createSubscription(data: InsertSubscription): Promise<Subscription> {
    const [s] = await db.insert(subscriptions).values(data).returning();
    return s;
  }

  async getProfile(userId: string): Promise<UserProfile | undefined> {
    const [p] = await db.select().from(userProfiles).where(eq(userProfiles.userId, userId));
    return p;
  }

  async upsertProfile(data: InsertProfile): Promise<UserProfile> {
    const existing = await this.getProfile(data.userId);
    if (existing) {
      const [p] = await db.update(userProfiles).set(data).where(eq(userProfiles.id, existing.id)).returning();
      return p;
    }
    const [p] = await db.insert(userProfiles).values(data).returning();
    return p;
  }

  async getDashboardStats(userId: string): Promise<any> {
    const progress = await this.getUserProgress(userId);
    const results = await this.getAssessmentResults(userId);

    const completedLessons = progress.filter((p) => p.isCompleted).length;
    const totalLessons = progress.length || 1;
    const completionRate = totalLessons > 0 ? Math.round((completedLessons / totalLessons) * 100) : 0;
    const totalMinutes = progress.reduce((sum, p) => sum + (p.timeSpentMinutes || 0), 0);
    const totalHours = Math.round(totalMinutes / 60 * 10) / 10;
    const averageScore = results.length > 0
      ? Math.round(results.reduce((sum, r) => sum + r.score, 0) / results.length)
      : 0;

    return { completedLessons, completionRate, totalHours, averageScore, totalLessons };
  }

  async getTodaySummary(userId: string): Promise<{ completedToday: number; dailyGoal: number; lastLesson: any | null }> {
    const todayStart = new Date();
    todayStart.setHours(0, 0, 0, 0);

    const allProgress = await db.select().from(studentProgress)
      .where(eq(studentProgress.userId, userId))
      .orderBy(desc(studentProgress.lastAccessedAt));

    const completedToday = allProgress.filter(p => {
      if (!p.isCompleted || !p.lastAccessedAt) return false;
      return new Date(p.lastAccessedAt) >= todayStart;
    }).length;

    let lastLesson: any = null;
    const inProgress = allProgress.find(p => !p.isCompleted && (p.progressPercent || 0) > 0);
    if (inProgress) {
      const lesson = await this.getLesson(inProgress.lessonId);
      if (lesson) {
        const subject = await this.getSubject(lesson.subjectId);
        lastLesson = {
          id: lesson.id,
          nameAr: lesson.nameAr,
          nameEn: lesson.nameEn,
          subjectNameAr: subject?.nameAr || "",
          subjectNameEn: subject?.nameEn || "",
          progressPercent: inProgress.progressPercent || 0,
          videoPosition: inProgress.videoPosition || 0,
        };
      }
    }

    return { completedToday, dailyGoal: 5, lastLesson };
  }

  async getRecentLessons(userId: string): Promise<any[]> {
    const progressList = await db.select().from(studentProgress)
      .where(eq(studentProgress.userId, userId))
      .orderBy(desc(studentProgress.lastAccessedAt))
      .limit(5);

    const result = [];
    for (const p of progressList) {
      const lesson = await this.getLesson(p.lessonId);
      if (lesson) {
        const subject = await this.getSubject(lesson.subjectId);
        result.push({
          id: lesson.id,
          nameAr: lesson.nameAr,
          nameEn: lesson.nameEn,
          subjectName: subject?.nameAr || "",
          lessonType: lesson.lessonType,
          progressPercent: p.progressPercent,
          isCompleted: p.isCompleted,
        });
      }
    }
    return result;
  }

  async getUserLessonsWithProgress(userId: string): Promise<any[]> {
    const progressList = await this.getUserProgress(userId);
    const result = [];
    for (const p of progressList) {
      const lesson = await this.getLesson(p.lessonId);
      if (lesson) {
        const subject = await this.getSubject(lesson.subjectId);
        result.push({
          id: lesson.id,
          nameAr: lesson.nameAr,
          nameEn: lesson.nameEn,
          subjectName: subject?.nameAr || "",
          lessonType: lesson.lessonType,
          progressPercent: p.progressPercent,
          isCompleted: p.isCompleted,
          timeSpentMinutes: p.timeSpentMinutes,
        });
      }
    }
    return result;
  }

  async getProgressBySubject(userId: string): Promise<any[]> {
    const progressList = await this.getUserProgress(userId);
    const subjectMap = new Map<string, { subjectName: string; completed: number; total: number }>();

    for (const p of progressList) {
      const lesson = await this.getLesson(p.lessonId);
      if (!lesson) continue;
      const subject = await this.getSubject(lesson.subjectId);
      if (!subject) continue;

      if (!subjectMap.has(subject.id)) {
        const allLessons = await this.getLessonsBySubject(subject.id);
        subjectMap.set(subject.id, {
          subjectName: subject.nameAr,
          completed: 0,
          total: allLessons.length,
        });
      }

      const entry = subjectMap.get(subject.id)!;
      if (p.isCompleted) entry.completed++;
    }

    return Array.from(subjectMap.entries()).map(([subjectId, data]) => ({
      subjectId,
      subjectName: data.subjectName,
      completedLessons: data.completed,
      totalLessons: data.total,
      progressPercent: data.total > 0 ? Math.round((data.completed / data.total) * 100) : 0,
    }));
  }

  async getWeeklyActivity(userId: string): Promise<{ day: string; lessons: number; minutes: number }[]> {
    const sevenDaysAgo = new Date();
    sevenDaysAgo.setDate(sevenDaysAgo.getDate() - 6);
    sevenDaysAgo.setHours(0, 0, 0, 0);

    const progress = await db.select()
      .from(studentProgress)
      .where(and(
        eq(studentProgress.userId, userId),
        sql`${studentProgress.lastAccessedAt} >= ${sevenDaysAgo}`
      ));

    const days: { day: string; lessons: number; minutes: number }[] = [];
    for (let i = 6; i >= 0; i--) {
      const date = new Date();
      date.setDate(date.getDate() - i);
      const dayStr = date.toISOString().split("T")[0];
      const dayProgress = progress.filter(p => {
        if (!p.lastAccessedAt) return false;
        return new Date(p.lastAccessedAt).toISOString().split("T")[0] === dayStr;
      });
      days.push({
        day: dayStr,
        lessons: dayProgress.length,
        minutes: dayProgress.reduce((sum, p) => sum + (p.timeSpentMinutes || 0), 0),
      });
    }
    return days;
  }

  async getStudyStreak(userId: string): Promise<{ currentStreak: number; longestStreak: number; totalDays: number }> {
    const progress = await db.select()
      .from(studentProgress)
      .where(eq(studentProgress.userId, userId))
      .orderBy(desc(studentProgress.lastAccessedAt));

    const uniqueDays = new Set<string>();
    for (const p of progress) {
      if (p.lastAccessedAt) {
        uniqueDays.add(new Date(p.lastAccessedAt).toISOString().split("T")[0]);
      }
    }

    const sortedDays = Array.from(uniqueDays).sort().reverse();
    let currentStreak = 0;
    let longestStreak = 0;
    let tempStreak = 0;
    const today = new Date().toISOString().split("T")[0];

    for (let i = 0; i < sortedDays.length; i++) {
      const expectedDate = new Date();
      expectedDate.setDate(expectedDate.getDate() - i);
      const expected = expectedDate.toISOString().split("T")[0];
      if (sortedDays[i] === expected || (i === 0 && sortedDays[i] === today)) {
        tempStreak++;
      } else {
        break;
      }
    }
    currentStreak = tempStreak;

    tempStreak = 1;
    for (let i = 1; i < sortedDays.length; i++) {
      const prev = new Date(sortedDays[i - 1]);
      const curr = new Date(sortedDays[i]);
      const diff = (prev.getTime() - curr.getTime()) / (1000 * 60 * 60 * 24);
      if (diff === 1) {
        tempStreak++;
      } else {
        longestStreak = Math.max(longestStreak, tempStreak);
        tempStreak = 1;
      }
    }
    longestStreak = Math.max(longestStreak, tempStreak, currentStreak);

    return { currentStreak, longestStreak, totalDays: uniqueDays.size };
  }

  async getReports(userId: string): Promise<any[]> {
    const results = await this.getAssessmentResults(userId);
    const reports = [];
    for (const r of results) {
      const assessment = await this.getAssessment(r.assessmentId);
      if (assessment) {
        reports.push({
          id: r.id,
          title: assessment.nameAr,
          description: `${assessment.assessmentType === "quiz" ? "اختبار قصير" : assessment.assessmentType === "exercise" ? "تمرين" : "امتحان"} - المحاولة ${r.attemptNumber}`,
          score: r.score,
          date: r.completedAt,
        });
      }
    }
    return reports;
  }

  async getForums(): Promise<DiscussionForum[]> {
    return db.select().from(discussionForums).orderBy(desc(discussionForums.createdAt));
  }

  async getForumPosts(forumId: string): Promise<any[]> {
    const { users } = await import("@shared/schema");
    const rows = await db.select({
      post: discussionPosts,
      user: {
        firstName: users.firstName,
        lastName: users.lastName,
        profileImageUrl: users.profileImageUrl,
      },
    })
      .from(discussionPosts)
      .leftJoin(users, eq(discussionPosts.userId, users.id))
      .where(eq(discussionPosts.forumId, forumId))
      .orderBy(desc(discussionPosts.isPinned), desc(discussionPosts.createdAt));

    return rows.map(r => ({ ...r.post, user: r.user || undefined }));
  }

  async createPost(data: InsertDiscussionPost): Promise<DiscussionPost> {
    const [p] = await db.insert(discussionPosts).values(data).returning();
    return p;
  }

  async getPostReplies(postId: string): Promise<any[]> {
    const { users } = await import("@shared/schema");
    const rows = await db.select({
      reply: discussionReplies,
      user: {
        firstName: users.firstName,
        lastName: users.lastName,
        profileImageUrl: users.profileImageUrl,
      },
    })
      .from(discussionReplies)
      .leftJoin(users, eq(discussionReplies.userId, users.id))
      .where(eq(discussionReplies.postId, postId))
      .orderBy(asc(discussionReplies.createdAt));

    return rows.map(r => ({ ...r.reply, user: r.user || undefined }));
  }

  async createReply(data: InsertDiscussionReply): Promise<DiscussionReply> {
    const [r] = await db.insert(discussionReplies).values(data).returning();
    await db.update(discussionPosts)
      .set({ repliesCount: sql`${discussionPosts.repliesCount} + 1` })
      .where(eq(discussionPosts.id, data.postId));
    return r;
  }

  async getBadgeDefinitions(): Promise<BadgeDefinition[]> {
    return db.select().from(badgeDefinitions);
  }

  async getUserBadges(userId: string): Promise<any[]> {
    const rows = await db.select({
      userBadge: userBadges,
      badge: badgeDefinitions,
    })
      .from(userBadges)
      .innerJoin(badgeDefinitions, eq(userBadges.badgeId, badgeDefinitions.id))
      .where(eq(userBadges.userId, userId))
      .orderBy(desc(userBadges.earnedAt));

    return rows.map(r => ({ ...r.userBadge, badge: r.badge }));
  }

  async awardBadge(userId: string, badgeId: string): Promise<void> {
    await db.insert(userBadges).values({ userId, badgeId });
  }

  async addPoints(userId: string, points: number, reason: string, referenceId?: string): Promise<void> {
    await db.insert(userPoints).values({ userId, points, reason, referenceId: referenceId || null });
  }

  async getUserPoints(userId: string): Promise<number> {
    const [result] = await db.select({ total: sql<number>`COALESCE(SUM(${userPoints.points}), 0)` })
      .from(userPoints)
      .where(eq(userPoints.userId, userId));
    return Number(result?.total) || 0;
  }

  async getLeaderboard(limit = 20): Promise<{ userId: string; firstName: string | null; lastName: string | null; totalPoints: number }[]> {
    const { users } = await import("@shared/schema");
    const rows = await db.select({
      userId: userPoints.userId,
      firstName: users.firstName,
      lastName: users.lastName,
      totalPoints: sql<number>`COALESCE(SUM(${userPoints.points}), 0)`,
    })
      .from(userPoints)
      .innerJoin(users, eq(userPoints.userId, users.id))
      .groupBy(userPoints.userId, users.firstName, users.lastName)
      .orderBy(sql`SUM(${userPoints.points}) DESC`)
      .limit(limit);

    return rows.map(r => ({ ...r, totalPoints: Number(r.totalPoints) }));
  }

  async getLibraryItems(category?: string, subjectId?: string, curriculumId?: string): Promise<LibraryItem[]> {
    const conditions: any[] = [eq(libraryItems.isPublic, true)];
    if (category) conditions.push(eq(libraryItems.category, category));
    if (subjectId) conditions.push(eq(libraryItems.subjectId, subjectId));
    if (curriculumId) conditions.push(eq(libraryItems.curriculumId, curriculumId));
    return db.select().from(libraryItems).where(and(...conditions)).orderBy(desc(libraryItems.createdAt));
  }

  async getLibraryItem(id: string): Promise<LibraryItem | undefined> {
    const [item] = await db.select().from(libraryItems).where(eq(libraryItems.id, id));
    return item;
  }

  async createLibraryItem(data: any): Promise<LibraryItem> {
    const [item] = await db.insert(libraryItems).values(data).returning();
    return item;
  }

  async updateLibraryItem(id: string, data: any): Promise<LibraryItem> {
    const [item] = await db.update(libraryItems).set(data).where(eq(libraryItems.id, id)).returning();
    return item;
  }

  async deleteLibraryItem(id: string): Promise<void> {
    await db.delete(readingProgress).where(eq(readingProgress.libraryItemId, id));
    await db.delete(libraryItems).where(eq(libraryItems.id, id));
  }

  async getReadingProgress(userId: string, libraryItemId: string): Promise<any | undefined> {
    const [progress] = await db.select().from(readingProgress)
      .where(and(eq(readingProgress.userId, userId), eq(readingProgress.libraryItemId, libraryItemId)));
    return progress;
  }

  async upsertReadingProgress(userId: string, libraryItemId: string, currentPage: number, totalPages: number): Promise<any> {
    const existing = await this.getReadingProgress(userId, libraryItemId);
    if (existing) {
      const [updated] = await db.update(readingProgress)
        .set({ currentPage, totalPages, lastReadAt: new Date() })
        .where(eq(readingProgress.id, existing.id))
        .returning();
      return updated;
    }
    const [created] = await db.insert(readingProgress)
      .values({ userId, libraryItemId, currentPage, totalPages })
      .returning();
    return created;
  }

  async getUserReadingProgressBatch(userId: string, itemIds: string[]): Promise<any[]> {
    if (!itemIds.length) return [];
    return db.select().from(readingProgress)
      .where(and(eq(readingProgress.userId, userId), inArray(readingProgress.libraryItemId, itemIds)));
  }

  async getAllUsers(): Promise<any[]> {
    const { users } = await import("@shared/schema");
    const rows = await db.select({
      id: users.id,
      firstName: users.firstName,
      lastName: users.lastName,
      email: users.email,
      profileImageUrl: users.profileImageUrl,
      createdAt: users.createdAt,
      role: userProfiles.role,
      assignedCurricula: userProfiles.assignedCurricula,
    })
      .from(users)
      .leftJoin(userProfiles, eq(users.id, userProfiles.userId));
    return rows;
  }

  async updateUserRole(userId: string, role: string): Promise<any> {
    const existing = await this.getProfile(userId);
    if (existing) {
      const [p] = await db.update(userProfiles)
        .set({ role: role as any })
        .where(eq(userProfiles.userId, userId))
        .returning();
      return p;
    }
    const [p] = await db.insert(userProfiles).values({ userId, role: role as any }).returning();
    return p;
  }

  async getUsersByRole(role: string): Promise<any[]> {
    const { users } = await import("@shared/schema");
    const rows = await db.select({
      id: users.id,
      firstName: users.firstName,
      lastName: users.lastName,
      email: users.email,
      profileImageUrl: users.profileImageUrl,
      createdAt: users.createdAt,
      role: userProfiles.role,
    })
      .from(users)
      .innerJoin(userProfiles, eq(users.id, userProfiles.userId))
      .where(eq(userProfiles.role, role as any));
    return rows;
  }

  async getTeacherStudents(teacherId: string): Promise<any[]> {
    const { users } = await import("@shared/schema");
    const assignments = await db.select().from(teacherAssignments)
      .where(eq(teacherAssignments.teacherId, teacherId));

    const subjectIds = assignments.map(a => a.subjectId);
    if (subjectIds.length === 0) return [];

    const studentMap = new Map<string, any>();
    for (const subjectId of subjectIds) {
      const subjectLessons = await db.select().from(lessons).where(eq(lessons.subjectId, subjectId));
      for (const lesson of subjectLessons) {
        const progressRows = await db.select({
          progress: studentProgress,
          user: {
            id: users.id,
            firstName: users.firstName,
            lastName: users.lastName,
            email: users.email,
            profileImageUrl: users.profileImageUrl,
          },
        })
          .from(studentProgress)
          .innerJoin(users, eq(studentProgress.userId, users.id))
          .where(eq(studentProgress.lessonId, lesson.id));

        for (const row of progressRows) {
          if (!studentMap.has(row.user.id)) {
            studentMap.set(row.user.id, {
              ...row.user,
              completedLessons: 0,
              totalLessons: 0,
              avgProgress: 0,
              progressEntries: [],
            });
          }
          const student = studentMap.get(row.user.id);
          student.totalLessons++;
          if (row.progress.isCompleted) student.completedLessons++;
          student.progressEntries.push(row.progress.progressPercent || 0);
        }
      }
    }

    return Array.from(studentMap.values()).map(s => {
      const avg = s.progressEntries.length > 0
        ? Math.round(s.progressEntries.reduce((a: number, b: number) => a + b, 0) / s.progressEntries.length)
        : 0;
      return {
        id: s.id,
        firstName: s.firstName,
        lastName: s.lastName,
        email: s.email,
        profileImageUrl: s.profileImageUrl,
        completedLessons: s.completedLessons,
        totalLessons: s.totalLessons,
        avgProgress: avg,
      };
    });
  }

  async getTeacherStats(teacherId: string): Promise<any> {
    const students = await this.getTeacherStudents(teacherId);
    const assignments = await db.select().from(teacherAssignments)
      .where(eq(teacherAssignments.teacherId, teacherId));

    const subjectIds = assignments.map(a => a.subjectId);
    let totalLessons = 0;
    for (const subjectId of subjectIds) {
      const lessonRows = await db.select().from(lessons).where(eq(lessons.subjectId, subjectId));
      totalLessons += lessonRows.length;
    }

    const avgPerformance = students.length > 0
      ? Math.round(students.reduce((sum: number, s: any) => sum + s.avgProgress, 0) / students.length)
      : 0;

    return {
      studentCount: students.length,
      lessonsCount: totalLessons,
      subjectsCount: subjectIds.length,
      avgPerformance,
    };
  }

  async getParentChildren(parentId: string): Promise<any[]> {
    const { users } = await import("@shared/schema");
    const links = await db.select({
      link: parentChildren,
      child: {
        id: users.id,
        firstName: users.firstName,
        lastName: users.lastName,
        email: users.email,
        profileImageUrl: users.profileImageUrl,
      },
    })
      .from(parentChildren)
      .innerJoin(users, eq(parentChildren.childId, users.id))
      .where(eq(parentChildren.parentId, parentId));

    const result = [];
    for (const row of links) {
      const progress = await this.getUserProgress(row.child.id);
      const completed = progress.filter(p => p.isCompleted).length;
      const total = progress.length;
      result.push({
        linkId: row.link.id,
        ...row.child,
        completedLessons: completed,
        totalLessons: total,
        progressPercent: total > 0 ? Math.round((completed / total) * 100) : 0,
      });
    }
    return result;
  }

  async addChild(parentId: string, childId: string): Promise<any> {
    const [link] = await db.insert(parentChildren).values({ parentId, childId }).returning();
    return link;
  }

  async removeChild(parentId: string, childId: string): Promise<boolean> {
    await db.delete(parentChildren)
      .where(and(eq(parentChildren.parentId, parentId), eq(parentChildren.childId, childId)));
    return true;
  }

  async createAttendanceSession(data: InsertAttendanceSession): Promise<AttendanceSession> {
    const [session] = await db.insert(attendanceSessions).values(data).returning();
    return session;
  }

  async getAttendanceSessions(subjectId?: string, teacherId?: string): Promise<any[]> {
    const { users } = await import("@shared/schema");
    let conditions: any[] = [];
    if (subjectId) conditions.push(eq(attendanceSessions.subjectId, subjectId));
    if (teacherId) conditions.push(eq(attendanceSessions.teacherId, teacherId));

    const query = db.select({
      session: attendanceSessions,
      teacher: {
        firstName: users.firstName,
        lastName: users.lastName,
      },
      subject: {
        nameAr: subjects.nameAr,
        nameEn: subjects.nameEn,
      },
    })
      .from(attendanceSessions)
      .leftJoin(users, eq(attendanceSessions.teacherId, users.id))
      .leftJoin(subjects, eq(attendanceSessions.subjectId, subjects.id))
      .orderBy(desc(attendanceSessions.sessionDate));

    const rows = conditions.length > 0
      ? await query.where(conditions.length === 1 ? conditions[0] : and(...conditions))
      : await query;

    return rows.map(r => ({
      ...r.session,
      teacher: r.teacher || undefined,
      subject: r.subject || undefined,
    }));
  }

  async getAttendanceSession(id: string): Promise<any> {
    const { users } = await import("@shared/schema");
    const [session] = await db.select({
      session: attendanceSessions,
      teacher: {
        firstName: users.firstName,
        lastName: users.lastName,
      },
      subject: {
        nameAr: subjects.nameAr,
        nameEn: subjects.nameEn,
      },
    })
      .from(attendanceSessions)
      .leftJoin(users, eq(attendanceSessions.teacherId, users.id))
      .leftJoin(subjects, eq(attendanceSessions.subjectId, subjects.id))
      .where(eq(attendanceSessions.id, id));

    if (!session) return undefined;

    const records = await db.select({
      record: attendanceRecords,
      student: {
        id: users.id,
        firstName: users.firstName,
        lastName: users.lastName,
        profileImageUrl: users.profileImageUrl,
      },
    })
      .from(attendanceRecords)
      .innerJoin(users, eq(attendanceRecords.studentId, users.id))
      .where(eq(attendanceRecords.sessionId, id));

    return {
      ...session.session,
      teacher: session.teacher || undefined,
      subject: session.subject || undefined,
      records: records.map(r => ({ ...r.record, student: r.student })),
    };
  }

  async markAttendance(sessionId: string, records: { studentId: string; status: string; notes?: string }[]): Promise<void> {
    await db.delete(attendanceRecords).where(eq(attendanceRecords.sessionId, sessionId));
    if (records.length > 0) {
      await db.insert(attendanceRecords).values(
        records.map(r => ({
          sessionId,
          studentId: r.studentId,
          status: r.status,
          notes: r.notes || null,
        }))
      );
    }
  }

  async getStudentAttendance(studentId: string): Promise<any[]> {
    const rows = await db.select({
      record: attendanceRecords,
      session: attendanceSessions,
    })
      .from(attendanceRecords)
      .innerJoin(attendanceSessions, eq(attendanceRecords.sessionId, attendanceSessions.id))
      .where(eq(attendanceRecords.studentId, studentId))
      .orderBy(desc(attendanceSessions.sessionDate));

    const result = [];
    for (const row of rows) {
      const subject = await this.getSubject(row.session.subjectId);
      result.push({
        ...row.record,
        session: {
          ...row.session,
          subjectName: subject?.nameAr || "",
        },
      });
    }
    return result;
  }

  async getAttendanceStats(studentId: string): Promise<{ present: number; absent: number; late: number; excused: number; total: number; rate: number }> {
    const records = await db.select().from(attendanceRecords)
      .where(eq(attendanceRecords.studentId, studentId));

    const stats = { present: 0, absent: 0, late: 0, excused: 0, total: records.length, rate: 0 };
    for (const r of records) {
      if (r.status === "present") stats.present++;
      else if (r.status === "absent") stats.absent++;
      else if (r.status === "late") stats.late++;
      else if (r.status === "excused") stats.excused++;
    }
    stats.rate = stats.total > 0 ? Math.round(((stats.present + stats.late) / stats.total) * 100) : 0;
    return stats;
  }
  async deleteCurriculum(id: string): Promise<boolean> {
    const gradesList = await db.select().from(grades).where(eq(grades.curriculumId, id));
    for (const grade of gradesList) {
      await this.deleteGrade(grade.id);
    }
    await db.delete(curricula).where(eq(curricula.id, id));
    return true;
  }

  async deleteGrade(id: string): Promise<boolean> {
    const subjectsList = await db.select().from(subjects).where(eq(subjects.gradeId, id));
    for (const subject of subjectsList) {
      await this.deleteSubject(subject.id);
    }
    await db.delete(grades).where(eq(grades.id, id));
    return true;
  }

  async deleteSubject(id: string): Promise<boolean> {
    const sectionsList = await db.select().from(courseSections).where(eq(courseSections.subjectId, id));
    for (const section of sectionsList) {
      await this.deleteSection(section.id);
    }
    const lessonsList = await db.select().from(lessons).where(eq(lessons.subjectId, id));
    for (const lesson of lessonsList) {
      await this.deleteLesson(lesson.id);
    }
    await db.delete(teacherAssignments).where(eq(teacherAssignments.subjectId, id));
    await db.delete(subjects).where(eq(subjects.id, id));
    return true;
  }

  async deleteSection(id: string): Promise<boolean> {
    const lessonsList = await db.select().from(lessons).where(eq(lessons.sectionId, id));
    for (const lesson of lessonsList) {
      await this.deleteLesson(lesson.id);
    }
    await db.delete(courseSections).where(eq(courseSections.id, id));
    return true;
  }

  async deleteLesson(id: string): Promise<boolean> {
    await db.delete(lessonResources).where(eq(lessonResources.lessonId, id));
    await db.delete(lessonNotes).where(eq(lessonNotes.lessonId, id));
    await db.delete(lessonBookmarks).where(eq(lessonBookmarks.lessonId, id));
    await db.delete(lessonQna).where(eq(lessonQna.lessonId, id));
    await db.delete(studentProgress).where(eq(studentProgress.lessonId, id));
    const assessmentsList = await db.select().from(assessments).where(eq(assessments.lessonId, id));
    for (const assessment of assessmentsList) {
      await db.delete(assessmentResults).where(eq(assessmentResults.assessmentId, assessment.id));
    }
    await db.delete(assessments).where(eq(assessments.lessonId, id));
    await db.delete(lessons).where(eq(lessons.id, id));
    return true;
  }

  async deleteAssessment(id: string): Promise<boolean> {
    await db.delete(assessmentResults).where(eq(assessmentResults.assessmentId, id));
    await db.delete(assessments).where(eq(assessments.id, id));
    return true;
  }

  async updateCurriculum(id: string, data: Partial<InsertCurriculum>): Promise<Curriculum | undefined> {
    const [c] = await db.update(curricula).set(data).where(eq(curricula.id, id)).returning();
    return c;
  }

  async updateGrade(id: string, data: Partial<InsertGrade>): Promise<Grade | undefined> {
    const [g] = await db.update(grades).set(data).where(eq(grades.id, id)).returning();
    return g;
  }

  async updateSubject(id: string, data: Partial<InsertSubject>): Promise<Subject | undefined> {
    const [s] = await db.update(subjects).set(data).where(eq(subjects.id, id)).returning();
    return s;
  }

  async updateSection(id: string, data: Partial<InsertCourseSection>): Promise<CourseSection | undefined> {
    const [s] = await db.update(courseSections).set(data).where(eq(courseSections.id, id)).returning();
    return s;
  }

  async updateLesson(id: string, data: Partial<InsertLesson>): Promise<Lesson | undefined> {
    const [l] = await db.update(lessons).set(data).where(eq(lessons.id, id)).returning();
    return l;
  }

  async updateAssessment(id: string, data: Partial<InsertAssessment>): Promise<Assessment | undefined> {
    const [a] = await db.update(assessments).set(data).where(eq(assessments.id, id)).returning();
    return a;
  }

  async deleteResource(id: string): Promise<boolean> {
    await db.delete(lessonResources).where(eq(lessonResources.id, id));
    return true;
  }

  async updateUser(userId: string, data: { firstName?: string; lastName?: string; email?: string; profileImageUrl?: string }): Promise<any> {
    const { users } = await import("@shared/schema");
    const [u] = await db.update(users).set({ ...data, updatedAt: new Date() }).where(eq(users.id, userId)).returning();
    return u;
  }

  async updateUserProfile(userId: string, data: { phone?: string; bio?: string; preferredLanguage?: string; assignedCurricula?: string[]; supervisedSubjects?: string[] }): Promise<any> {
    const existing = await this.getProfile(userId);
    if (existing) {
      const [p] = await db.update(userProfiles).set(data).where(eq(userProfiles.userId, userId)).returning();
      return p;
    } else {
      const [p] = await db.insert(userProfiles).values({ userId, role: "student", ...data }).returning();
      return p;
    }
  }

  async resetUserPassword(userId: string, newPassword: string): Promise<boolean> {
    const bcrypt = await import("bcryptjs");
    const { users } = await import("@shared/schema");
    const hashedPassword = await bcrypt.default.hash(newPassword, 10);
    await db.update(users).set({ password: hashedPassword, updatedAt: new Date() }).where(eq(users.id, userId));
    return true;
  }

  async deleteUser(userId: string): Promise<boolean> {
    const { users, sessions } = await import("@shared/schema");
    await db.delete(leaveRequests).where(or(eq(leaveRequests.studentId, userId), eq(leaveRequests.parentId, userId), eq(leaveRequests.reviewedBy, userId)));
    const userPlans = await db.select({ id: weeklyPlans.id }).from(weeklyPlans).where(eq(weeklyPlans.teacherId, userId));
    for (const p of userPlans) {
      await db.delete(weeklyPlanItems).where(eq(weeklyPlanItems.planId, p.id));
    }
    await db.delete(weeklyPlans).where(eq(weeklyPlans.teacherId, userId));
    await db.delete(classSchedules).where(eq(classSchedules.teacherId, userId));
    await db.delete(classTeachers).where(eq(classTeachers.teacherId, userId));
    await db.delete(classEnrollments).where(eq(classEnrollments.studentId, userId));
    await db.delete(parentChildren).where(or(eq(parentChildren.parentId, userId), eq(parentChildren.childId, userId)));
    const userSessions = await db.select({ id: attendanceSessions.id }).from(attendanceSessions).where(eq(attendanceSessions.teacherId, userId));
    for (const s of userSessions) {
      await db.delete(attendanceRecords).where(eq(attendanceRecords.sessionId, s.id));
    }
    await db.delete(attendanceSessions).where(eq(attendanceSessions.teacherId, userId));
    await db.delete(attendanceRecords).where(eq(attendanceRecords.studentId, userId));
    await db.delete(userPoints).where(eq(userPoints.userId, userId));
    await db.delete(userBadges).where(eq(userBadges.userId, userId));
    await db.delete(discussionReplies).where(eq(discussionReplies.userId, userId));
    await db.delete(discussionPosts).where(eq(discussionPosts.userId, userId));
    await db.delete(lessonQna).where(eq(lessonQna.userId, userId));
    await db.delete(userProfiles).where(eq(userProfiles.userId, userId));
    await db.delete(subscriptions).where(eq(subscriptions.userId, userId));
    await db.delete(notifications).where(eq(notifications.userId, userId));
    await db.delete(studentProgress).where(eq(studentProgress.userId, userId));
    await db.delete(lessonNotes).where(eq(lessonNotes.userId, userId));
    await db.delete(lessonBookmarks).where(eq(lessonBookmarks.userId, userId));
    await db.delete(teacherAssignments).where(eq(teacherAssignments.teacherId, userId));
    await db.delete(assessmentResults).where(eq(assessmentResults.userId, userId));
    await db.delete(users).where(eq(users.id, userId));
    return true;
  }

  async getSubscriptionPlans(): Promise<SubscriptionPlan[]> {
    return db.select().from(subscriptionPlans).orderBy(asc(subscriptionPlans.orderIndex));
  }

  async getSubscriptionPlan(id: string): Promise<SubscriptionPlan | undefined> {
    const [p] = await db.select().from(subscriptionPlans).where(eq(subscriptionPlans.id, id));
    return p;
  }

  async createSubscriptionPlan(data: InsertSubscriptionPlan): Promise<SubscriptionPlan> {
    const [p] = await db.insert(subscriptionPlans).values(data).returning();
    return p;
  }

  async updateSubscriptionPlan(id: string, data: Partial<InsertSubscriptionPlan>): Promise<SubscriptionPlan | undefined> {
    const [p] = await db.update(subscriptionPlans).set(data).where(eq(subscriptionPlans.id, id)).returning();
    return p;
  }

  async deleteSubscriptionPlan(id: string): Promise<boolean> {
    await db.delete(subscriptionPlans).where(eq(subscriptionPlans.id, id));
    return true;
  }

  async getAllSubscriptions(): Promise<any[]> {
    const { users } = await import("@shared/schema");
    const rows = await db
      .select({
        id: subscriptions.id,
        userId: subscriptions.userId,
        plan: subscriptions.plan,
        status: subscriptions.status,
        startDate: subscriptions.startDate,
        endDate: subscriptions.endDate,
        autoRenew: subscriptions.autoRenew,
        firstName: users.firstName,
        lastName: users.lastName,
        email: users.email,
      })
      .from(subscriptions)
      .leftJoin(users, eq(subscriptions.userId, users.id))
      .orderBy(desc(subscriptions.startDate));
    return rows;
  }

  async updateSubscription(id: string, data: Partial<InsertSubscription>): Promise<Subscription | undefined> {
    const [s] = await db.update(subscriptions).set(data).where(eq(subscriptions.id, id)).returning();
    return s;
  }

  async deleteSubscription(id: string): Promise<boolean> {
    await db.delete(subscriptions).where(eq(subscriptions.id, id));
    return true;
  }

  async createAdminSubscription(data: InsertSubscription): Promise<Subscription> {
    const [s] = await db.insert(subscriptions).values(data).returning();
    return s;
  }

  // Virtual School implementations

  async getSchoolClasses(): Promise<SchoolClass[]> {
    return db.select().from(schoolClasses).orderBy(desc(schoolClasses.createdAt));
  }

  async getSchoolClass(id: string): Promise<SchoolClass | undefined> {
    const [c] = await db.select().from(schoolClasses).where(eq(schoolClasses.id, id));
    return c;
  }

  async createSchoolClass(data: InsertSchoolClass): Promise<SchoolClass> {
    const [c] = await db.insert(schoolClasses).values(data).returning();
    return c;
  }

  async updateSchoolClass(id: string, data: Partial<InsertSchoolClass>): Promise<SchoolClass | undefined> {
    const [c] = await db.update(schoolClasses).set(data).where(eq(schoolClasses.id, id)).returning();
    return c;
  }

  async deleteSchoolClass(id: string): Promise<boolean> {
    await db.delete(classEnrollments).where(eq(classEnrollments.classId, id));
    await db.delete(classTeachers).where(eq(classTeachers.classId, id));
    await db.delete(classSchedules).where(eq(classSchedules.classId, id));
    await db.delete(schoolClasses).where(eq(schoolClasses.id, id));
    return true;
  }

  async getClassEnrollments(classId: string): Promise<ClassEnrollment[]> {
    return db.select().from(classEnrollments).where(eq(classEnrollments.classId, classId));
  }

  async createClassEnrollment(data: InsertClassEnrollment): Promise<ClassEnrollment> {
    const [e] = await db.insert(classEnrollments).values(data).returning();
    return e;
  }

  async deleteClassEnrollment(id: string): Promise<boolean> {
    await db.delete(classEnrollments).where(eq(classEnrollments.id, id));
    return true;
  }

  async getClassTeachers(classId: string): Promise<ClassTeacher[]> {
    return db.select().from(classTeachers).where(eq(classTeachers.classId, classId));
  }

  async createClassTeacher(data: InsertClassTeacher): Promise<ClassTeacher> {
    const [t] = await db.insert(classTeachers).values(data).returning();
    return t;
  }

  async deleteClassTeacher(id: string): Promise<boolean> {
    await db.delete(classTeachers).where(eq(classTeachers.id, id));
    return true;
  }

  async getSchedulePeriods(): Promise<SchedulePeriod[]> {
    return db.select().from(schedulePeriods).orderBy(asc(schedulePeriods.periodNumber));
  }

  async createSchedulePeriod(data: InsertSchedulePeriod): Promise<SchedulePeriod> {
    const [p] = await db.insert(schedulePeriods).values(data).returning();
    return p;
  }

  async updateSchedulePeriod(id: string, data: Partial<InsertSchedulePeriod>): Promise<SchedulePeriod | undefined> {
    const [p] = await db.update(schedulePeriods).set(data).where(eq(schedulePeriods.id, id)).returning();
    return p;
  }

  async deleteSchedulePeriod(id: string): Promise<boolean> {
    await db.delete(schedulePeriods).where(eq(schedulePeriods.id, id));
    return true;
  }

  async getClassSchedules(classId: string): Promise<ClassSchedule[]> {
    return db.select().from(classSchedules).where(eq(classSchedules.classId, classId));
  }

  async createClassSchedule(data: InsertClassSchedule): Promise<ClassSchedule> {
    const [s] = await db.insert(classSchedules).values(data).returning();
    return s;
  }

  async updateClassSchedule(id: string, data: Partial<InsertClassSchedule>): Promise<ClassSchedule | undefined> {
    const [s] = await db.update(classSchedules).set(data).where(eq(classSchedules.id, id)).returning();
    return s;
  }

  async deleteClassSchedule(id: string): Promise<boolean> {
    await db.delete(classSchedules).where(eq(classSchedules.id, id));
    return true;
  }

  async getWeeklyPlans(teacherId?: string, classId?: string): Promise<WeeklyPlan[]> {
    const conditions = [];
    if (teacherId) conditions.push(eq(weeklyPlans.teacherId, teacherId));
    if (classId) conditions.push(eq(weeklyPlans.classId, classId));

    if (conditions.length > 0) {
      return db.select().from(weeklyPlans).where(and(...conditions)).orderBy(desc(weeklyPlans.weekStart));
    }
    return db.select().from(weeklyPlans).orderBy(desc(weeklyPlans.weekStart));
  }

  async getWeeklyPlan(id: string): Promise<WeeklyPlan | undefined> {
    const [p] = await db.select().from(weeklyPlans).where(eq(weeklyPlans.id, id));
    return p;
  }

  async createWeeklyPlan(data: InsertWeeklyPlan): Promise<WeeklyPlan> {
    const [p] = await db.insert(weeklyPlans).values(data).returning();
    return p;
  }

  async updateWeeklyPlan(id: string, data: Partial<InsertWeeklyPlan>): Promise<WeeklyPlan | undefined> {
    const [p] = await db.update(weeklyPlans).set(data).where(eq(weeklyPlans.id, id)).returning();
    return p;
  }

  async deleteWeeklyPlan(id: string): Promise<boolean> {
    await db.delete(weeklyPlanItems).where(eq(weeklyPlanItems.planId, id));
    await db.delete(weeklyPlans).where(eq(weeklyPlans.id, id));
    return true;
  }

  async getWeeklyPlanItems(planId: string): Promise<WeeklyPlanItem[]> {
    return db.select().from(weeklyPlanItems).where(eq(weeklyPlanItems.planId, planId));
  }

  async createWeeklyPlanItem(data: InsertWeeklyPlanItem): Promise<WeeklyPlanItem> {
    const [i] = await db.insert(weeklyPlanItems).values(data).returning();
    return i;
  }

  async updateWeeklyPlanItem(id: string, data: Partial<InsertWeeklyPlanItem>): Promise<WeeklyPlanItem | undefined> {
    const [i] = await db.update(weeklyPlanItems).set(data).where(eq(weeklyPlanItems.id, id)).returning();
    return i;
  }

  async deleteWeeklyPlanItem(id: string): Promise<boolean> {
    await db.delete(weeklyPlanItems).where(eq(weeklyPlanItems.id, id));
    return true;
  }

  async getLeaveRequests(filters?: { studentId?: string; parentId?: string; status?: string }): Promise<LeaveRequest[]> {
    const conditions = [];
    if (filters?.studentId) conditions.push(eq(leaveRequests.studentId, filters.studentId));
    if (filters?.parentId) conditions.push(eq(leaveRequests.parentId, filters.parentId));
    if (filters?.status) conditions.push(eq(leaveRequests.status, filters.status as any));

    if (conditions.length > 0) {
      return db.select().from(leaveRequests).where(and(...conditions)).orderBy(desc(leaveRequests.createdAt));
    }
    return db.select().from(leaveRequests).orderBy(desc(leaveRequests.createdAt));
  }

  async getLeaveRequest(id: string): Promise<LeaveRequest | undefined> {
    const [r] = await db.select().from(leaveRequests).where(eq(leaveRequests.id, id));
    return r;
  }

  async createLeaveRequest(data: InsertLeaveRequest): Promise<LeaveRequest> {
    const [r] = await db.insert(leaveRequests).values(data).returning();
    return r;
  }

  async updateLeaveRequest(id: string, data: Partial<InsertLeaveRequest>): Promise<LeaveRequest | undefined> {
    const [r] = await db.update(leaveRequests).set(data).where(eq(leaveRequests.id, id)).returning();
    return r;
  }

  async getStudentClassId(studentId: string): Promise<string | undefined> {
    const [enrollment] = await db.select().from(classEnrollments).where(eq(classEnrollments.studentId, studentId));
    return enrollment?.classId;
  }

  async getInteractiveLessons(filters?: { creatorId?: string; educationalStage?: string; subjectId?: string }): Promise<any[]> {
    const conditions = [];
    if (filters?.creatorId) conditions.push(eq(interactiveLessons.creatorId, filters.creatorId));
    if (filters?.educationalStage) conditions.push(eq(interactiveLessons.educationalStage, filters.educationalStage as any));
    if (filters?.subjectId) conditions.push(eq(interactiveLessons.subjectId, filters.subjectId));

    const query = conditions.length > 0
      ? db.select().from(interactiveLessons).where(and(...conditions)).orderBy(desc(interactiveLessons.createdAt))
      : db.select().from(interactiveLessons).orderBy(desc(interactiveLessons.createdAt));
    return query;
  }

  async getInteractiveLesson(id: string): Promise<any> {
    const [r] = await db.select().from(interactiveLessons).where(eq(interactiveLessons.id, id));
    return r;
  }

  async createInteractiveLesson(data: any): Promise<any> {
    const [r] = await db.insert(interactiveLessons).values(data).returning();
    return r;
  }

  async updateInteractiveLesson(id: string, data: any): Promise<any> {
    const [r] = await db.update(interactiveLessons).set({ ...data, updatedAt: new Date() }).where(eq(interactiveLessons.id, id)).returning();
    return r;
  }

  async deleteInteractiveLesson(id: string): Promise<boolean> {
    await db.delete(interactiveLessonResults).where(eq(interactiveLessonResults.interactiveLessonId, id));
    const result = await db.delete(interactiveLessons).where(eq(interactiveLessons.id, id));
    return true;
  }

  async getInteractiveLessonResults(userId: string, lessonId: string): Promise<any> {
    const [r] = await db.select().from(interactiveLessonResults)
      .where(and(eq(interactiveLessonResults.userId, userId), eq(interactiveLessonResults.interactiveLessonId, lessonId)));
    return r;
  }

  async createInteractiveLessonResult(data: any): Promise<any> {
    const [r] = await db.insert(interactiveLessonResults).values(data).returning();
    return r;
  }
}

export const storage = new DatabaseStorage();