async function seedAcademicsAndGrading(db) {
  const teacherId = db.prepare(`SELECT id FROM users WHERE email = 'teacher@school.com'`).get()?.id;
  const studentId = db.prepare(`SELECT id FROM users WHERE email = 'student@school.com'`).get()?.id;

  // Departments
  const departments = [
    { uid: 'uid-dept-academics', name: 'Academics', code: 'ACAD', description: 'Academic affairs & curriculum' },
    { uid: 'uid-dept-admin',     name: 'Administration', code: 'ADMIN', description: 'School administration' },
    { uid: 'uid-dept-finance',   name: 'Finance', code: 'FIN', description: 'Finance and accounts' },
    { uid: 'uid-dept-sciences',  name: 'Sciences', code: 'SCI', description: 'Natural Sciences & Mathematics' },
    { uid: 'uid-dept-humanities',name: 'Humanities', code: 'HUM', description: 'Languages, Arts & Social Studies' },
  ];

  const stmtDept = db.prepare(
    `INSERT OR IGNORE INTO departments (uid, name, code, description) VALUES (@uid, @name, @code, @description)`
  );
  for (const d of departments) stmtDept.run(d);

  const deptSciId = db.prepare(`SELECT id FROM departments WHERE code = 'SCI'`).get()?.id;

  // Classes
  const classes = [
    { uid: 'uid-class-f1a', name: 'Form 1A', section: 'A', capacity: 35, class_teacher_id: teacherId },
    { uid: 'uid-class-f4b', name: 'Form 4B', section: 'B', capacity: 30, class_teacher_id: teacherId },
    { uid: 'uid-class-l6s', name: 'Lower 6 Science', section: 'S', capacity: 25, class_teacher_id: teacherId },
  ];

  const stmtClass = db.prepare(
    `INSERT OR IGNORE INTO classes (uid, name, section, capacity, class_teacher_id)
     VALUES (@uid, @name, @section, @capacity, @class_teacher_id)`
  );
  for (const c of classes) stmtClass.run(c);

  const classF4bId = db.prepare(`SELECT id FROM classes WHERE uid = 'uid-class-f4b'`).get()?.id;

  // Subjects (legacy `subjects` table and new `subjects_new`)
  const subjects = [
    { uid: 'uid-subj-math',    name: 'Mathematics',       code: 'MATH4004', class_id: classF4bId, teacher_id: teacherId },
    { uid: 'uid-subj-english', name: 'English Language',  code: 'ENG1122',  class_id: classF4bId, teacher_id: teacherId },
    { uid: 'uid-subj-physics', name: 'Physics',           code: 'PHYS5054', class_id: classF4bId, teacher_id: teacherId },
    { uid: 'uid-subj-chem',    name: 'Chemistry',         code: 'CHEM5070', class_id: classF4bId, teacher_id: teacherId },
    { uid: 'uid-subj-comm',    name: 'Commerce',          code: 'COMM7100', class_id: classF4bId, teacher_id: teacherId },
  ];

  const stmtSubj = db.prepare(
    `INSERT OR IGNORE INTO subjects (uid, name, code, class_id, teacher_id)
     VALUES (@uid, @name, @code, @class_id, @teacher_id)`
  );
  for (const s of subjects) stmtSubj.run(s);

  // Table `subjects_new`
  const stmtSubjNew = db.prepare(
    `INSERT OR IGNORE INTO subjects_new (uid, name, code, department_id, class_id, teacher_id, credits)
     VALUES (@uid, @name, @code, ${deptSciId || 'NULL'}, @class_id, @teacher_id, 4)`
  );
  for (const s of subjects) stmtSubjNew.run(s);

  const mathSubjId = db.prepare(`SELECT id FROM subjects WHERE code = 'MATH4004'`).get()?.id;
  const physSubjId = db.prepare(`SELECT id FROM subjects WHERE code = 'PHYS5054'`).get()?.id;

  // Courses
  const courses = [
    { uid: 'uid-course-math', name: 'Form 4 Mathematics 2026', subject_id: mathSubjId, teacher_id: teacherId, status: 'active' },
    { uid: 'uid-course-phys', name: 'Form 4 Physics 2026',     subject_id: physSubjId, teacher_id: teacherId, status: 'active' },
  ].filter(c => c.subject_id && c.teacher_id);

  const stmtCourse = db.prepare(
    `INSERT OR IGNORE INTO courses (uid, name, subject_id, teacher_id, status)
     VALUES (@uid, @name, @subject_id, @teacher_id, @status)`
  );

  const safeRun = (stmt, item) => {
    try {
      stmt.run(item);
    } catch (e) {
      // Ignore FK constraint issues on optional seed rows
    }
  };

  for (const cr of courses) safeRun(stmtCourse, cr);

  const courseMathId = db.prepare(`SELECT id FROM courses WHERE uid = 'uid-course-math'`).get()?.id;
  const coursePhysId = db.prepare(`SELECT id FROM courses WHERE uid = 'uid-course-phys'`).get()?.id;

  // Enrollments & Course Enrolments
  if (studentId && classF4bId) {
    db.prepare(
      `INSERT OR IGNORE INTO enrollments (uid, student_id, class_id, academic_year, status)
       VALUES (?, ?, ?, '2026', 'active')`
    ).run('uid-enr-1', studentId, classF4bId);
  }

  if (studentId && courseMathId) {
    db.prepare(
      `INSERT OR IGNORE INTO course_enrolments (uid, student_id, course_id, status)
       VALUES (?, ?, ?, 'active')`
    ).run('uid-cenr-math', studentId, courseMathId);
  }

  if (studentId && coursePhysId) {
    db.prepare(
      `INSERT OR IGNORE INTO course_enrolments (uid, student_id, course_id, status)
       VALUES (?, ?, ?, 'active')`
    ).run('uid-cenr-phys', studentId, coursePhysId);
  }

  // Attendance Records
  if (studentId && mathSubjId) {
    const dates = ['2026-07-15', '2026-07-16', '2026-07-17', '2026-07-18', '2026-07-21'];
    const stmtAtt = db.prepare(
      `INSERT OR IGNORE INTO attendance (uid, student_id, subject_id, date, status, marked_by)
       VALUES (?, ?, ?, ?, 'present', ?)`
    );
    for (let i = 0; i < dates.length; i++) {
      stmtAtt.run(`uid-att-${i}`, studentId, mathSubjId, dates[i], teacherId);
    }
  }

  // Assignments & Submissions
  if (courseMathId && studentId) {
    db.prepare(
      `INSERT OR IGNORE INTO assignments (uid, course_id, title, description, due_date, max_score, is_published)
       VALUES (?, ?, ?, ?, '2026-07-30', 100, 1)`
    ).run(
      'uid-assign-1',
      courseMathId,
      'Quadratic Equations & Graphs',
      'Complete exercises 4A to 4D in Pure Mathematics textbook.'
    );

    const assignId = db.prepare(`SELECT id FROM assignments WHERE uid = 'uid-assign-1'`).get()?.id;

    if (assignId) {
      db.prepare(
        `INSERT OR IGNORE INTO submissions (uid, assignment_id, student_id, content, status, grade, feedback, graded_by)
         VALUES (?, ?, ?, ?, 'graded', 88.5, 'Excellent step-by-step resolution of quadratic roots.', ?)`
      ).run(
        'uid-sub-1',
        assignId,
        studentId,
        'Submitted assignment document on quadratic roots.',
        teacherId
      );
    }
  }

  // Grades & Course Grades
  if (studentId && mathSubjId) {
    db.prepare(
      `INSERT OR IGNORE INTO grades (uid, student_id, subject_id, exam_type, marks, total_marks, grade, remarks, academic_year, term)
       VALUES (?, ?, ?, 'Mid-Term', 88, 100, 'A', 'Strong analytical problem solving', '2026', 'Term 1')`
    ).run('uid-grade-math', studentId, mathSubjId);
  }

  if (studentId && physSubjId) {
    db.prepare(
      `INSERT OR IGNORE INTO grades (uid, student_id, subject_id, exam_type, marks, total_marks, grade, remarks, academic_year, term)
       VALUES (?, ?, ?, 'Mid-Term', 82, 100, 'A', 'Good understanding of mechanics & waves', '2026', 'Term 1')`
    ).run('uid-grade-phys', studentId, physSubjId);
  }

  console.log('[seed] academics and grading seeded');
}

module.exports = { seedAcademicsAndGrading };
