async function seedTeacherMedicalExams(db) {
  const teacherId = db.prepare(`SELECT id FROM users WHERE email = 'teacher@school.com'`).get()?.id;
  const adminId   = db.prepare(`SELECT id FROM users WHERE email = 'admin@school.com'`).get()?.id;
  const studentId = db.prepare(`SELECT id FROM users WHERE email = 'student@school.com'`).get()?.id;
  const mathSubjId = db.prepare(`SELECT id FROM subjects WHERE code = 'MATH4004'`).get()?.id;
  const classF4bId = db.prepare(`SELECT id FROM classes WHERE uid = 'uid-class-f4b'`).get()?.id;

  // Syllabuses
  if (mathSubjId) {
    db.prepare(
      `INSERT OR IGNORE INTO syllabuses (uid, subject_id, title, description, version, is_active, created_by)
       VALUES (@uid, @subject_id, @title, @description, @version, 1, @created_by)`
    ).run({
      uid: 'uid-syl-math',
      subject_id: mathSubjId,
      title: 'ZIMSEC O-Level Mathematics 4004',
      description: 'Official examination syllabus for Form 3 & 4 Mathematics',
      version: '2026.1',
      created_by: teacherId || adminId || 1
    });
  }

  // Schemes of Work
  if (mathSubjId && teacherId) {
    db.prepare(
      `INSERT OR IGNORE INTO schemes_of_work (uid, subject_id, teacher_id, term, academic_year, content, status)
       VALUES (@uid, @subject_id, @teacher_id, @term, @academic_year, @content, @status)`
    ).run({
      uid: 'uid-scheme-math',
      subject_id: mathSubjId,
      teacher_id: teacherId,
      term: 'Term 1',
      academic_year: '2026',
      content: 'Comprehensive term scheme covering Algebra, Trigonometry, and Statistics.',
      status: 'approved'
    });

    const schemeId = db.prepare(`SELECT id FROM schemes_of_work WHERE uid = 'uid-scheme-math'`).get()?.id;

    if (schemeId) {
      db.prepare(
        `INSERT OR IGNORE INTO focus_points (uid, scheme_id, topic, objectives, key_concepts)
         VALUES (@uid, @scheme_id, @topic, @objectives, @key_concepts)`
      ).run({
        uid: 'uid-fp-math',
        scheme_id: schemeId,
        topic: 'Quadratic Functions & Graphs',
        objectives: 'Students will learn to plot quadratic curves and determine turning points.',
        key_concepts: 'Parabola, Turning Point, Roots'
      });

      const fpId = db.prepare(`SELECT id FROM focus_points WHERE uid = 'uid-fp-math'`).get()?.id;

      db.prepare(
        `INSERT OR IGNORE INTO lesson_plans (uid, teacher_id, subject_id, scheme_id, focus_point_id, date, topic, objectives, introduction, development, conclusion, assessment_method, status)
         VALUES (@uid, @teacher_id, @subject_id, @scheme_id, @focus_point_id, @date, @topic, @objectives, @introduction, @development, @conclusion, @assessment_method, @status)`
      ).run({
        uid: 'uid-lp-math',
        teacher_id: teacherId,
        subject_id: mathSubjId,
        scheme_id: schemeId,
        focus_point_id: fpId,
        date: '2026-07-22',
        topic: 'Graphing Quadratic Equations',
        objectives: 'Plot f(x) = ax^2 + bx + c',
        introduction: 'Review table of values',
        development: 'Demonstrate plotting on board',
        conclusion: 'Students complete graph exercises',
        assessment_method: 'Peer marking of graphs',
        status: 'completed'
      });
    }
  }

  // Student Social Docs
  if (studentId) {
    db.prepare(
      `INSERT OR IGNORE INTO student_social_docs (uid, student_id, learner_type, past_behavior, family_size, home_environment, medical_notes, social_strengths, social_challenges, interests)
       VALUES (@uid, @student_id, @learner_type, @past_behavior, @family_size, @home_environment, @medical_notes, @social_strengths, @social_challenges, @interests)`
    ).run({
      uid: 'uid-socdoc-1',
      student_id: studentId,
      learner_type: 'Visual & Kinesthetic',
      past_behavior: 'Exemplary conduct and punctual',
      family_size: 4,
      home_environment: 'Supportive home environment with internet access',
      medical_notes: 'Mild asthma, carries inhaler',
      social_strengths: 'High leadership potential, active in debate club',
      social_challenges: 'Occasional public speaking anxiety',
      interests: 'Mathematics, Debate, Football'
    });
  }

  // Student Medical Profile & History
  if (studentId) {
    db.prepare(
      `INSERT OR IGNORE INTO student_medical_profiles (uid, student_id, blood_group, allergies, dietary_restrictions, chronic_conditions, emergency_instructions, updated_by)
       VALUES (@uid, @student_id, @blood_group, @allergies, @dietary_restrictions, @chronic_conditions, @emergency_instructions, @updated_by)`
    ).run({
      uid: 'uid-medprof-1',
      student_id: studentId,
      blood_group: 'O+',
      allergies: 'Peanuts, Shellfish',
      dietary_restrictions: 'No peanuts in school meals',
      chronic_conditions: 'Mild Asthma',
      emergency_instructions: 'In case of severe shortness of breath, administer Salbutamol inhaler (2 puffs) and notify parent immediately.',
      updated_by: teacherId || adminId || 1
    });

    db.prepare(
      `INSERT OR IGNORE INTO student_medication_logs (uid, student_id, medication_name, dosage, administered_at, administered_by, reason, notes)
       VALUES (@uid, @student_id, @medication_name, @dosage, @administered_at, @administered_by, @reason, @notes)`
    ).run({
      uid: 'uid-medlog-1',
      student_id: studentId,
      medication_name: 'Salbutamol Inhaler',
      dosage: '2 puffs',
      administered_at: '2026-07-14 10:30:00',
      administered_by: teacherId || adminId || 1,
      reason: 'Wheezing during physical sports',
      notes: 'Recovered well within 15 minutes'
    });

    db.prepare(
      `INSERT OR IGNORE INTO student_medical_history (uid, student_id, event_type, description, severity, onset_date, doctor_notes, is_visible_to_teachers)
       VALUES (@uid, @student_id, @event_type, @description, @severity, @onset_date, @doctor_notes, 1)`
    ).run({
      uid: 'uid-medhist-1',
      student_id: studentId,
      event_type: 'illness',
      description: 'Mild asthma onset during physical exercise',
      severity: 'moderate',
      onset_date: '2021-03-15',
      doctor_notes: 'EpiPen stored in school sick bay'
    });
  }

  // Online Exams & Attempt
  db.prepare(
    `INSERT OR IGNORE INTO exam_groups (uid, name, description, exam_type, duration_minutes, total_marks, passing_marks, academic_year, term, grading_scale)
     VALUES (@uid, @name, @description, @exam_type, @duration_minutes, @total_marks, @passing_marks, @academic_year, @term, @grading_scale)`
  ).run({
    uid: 'uid-egroup-math',
    name: 'Form 4 Term 1 Mathematics Mid-Term',
    description: 'Mid-Term online assessment for O-Level Mathematics',
    exam_type: 'midterm',
    duration_minutes: 60,
    total_marks: 100,
    passing_marks: 50,
    academic_year: '2026',
    term: 'Term 1',
    grading_scale: 'zimsec_olevel'
  });

  const egroupId = db.prepare(`SELECT id FROM exam_groups WHERE uid = 'uid-egroup-math'`).get()?.id;

  if (egroupId && mathSubjId) {
    db.prepare(
      `INSERT OR IGNORE INTO question_banks (uid, exam_group_id, subject_id, question_type, question, options, correct_answer, marks, difficulty)
       VALUES (@uid, @exam_group_id, @subject_id, @question_type, @question, @options, @correct_answer, @marks, @difficulty)`
    ).run({
      uid: 'uid-q1',
      exam_group_id: egroupId,
      subject_id: mathSubjId,
      question_type: 'multiple-choice',
      question: 'What are the roots of the equation x^2 - 5x + 6 = 0?',
      options: '["x = 2, 3", "x = -2, -3", "x = 1, 6", "x = -1, -6"]',
      correct_answer: 'x = 2, 3',
      marks: 10,
      difficulty: 'medium'
    });

    db.prepare(
      `INSERT OR IGNORE INTO question_banks (uid, exam_group_id, subject_id, question_type, question, options, correct_answer, marks, difficulty)
       VALUES (@uid, @exam_group_id, @subject_id, @question_type, @question, @options, @correct_answer, @marks, @difficulty)`
    ).run({
      uid: 'uid-q2',
      exam_group_id: egroupId,
      subject_id: mathSubjId,
      question_type: 'multiple-choice',
      question: 'Simplify: (3x^2) * (4x^3)',
      options: '["12x^5", "12x^6", "7x^5", "7x^6"]',
      correct_answer: '12x^5',
      marks: 10,
      difficulty: 'easy'
    });

    if (classF4bId) {
      db.prepare(
        `INSERT OR IGNORE INTO exam_schedules (uid, exam_group_id, class_id, subject_id, start_time, end_time, duration_minutes, is_active)
         VALUES (@uid, @exam_group_id, @class_id, @subject_id, @start_time, @end_time, @duration_minutes, 1)`
      ).run({
        uid: 'uid-esched-math',
        exam_group_id: egroupId,
        class_id: classF4bId,
        subject_id: mathSubjId,
        start_time: '2026-07-20 09:00:00',
        end_time: '2026-07-20 10:00:00',
        duration_minutes: 60
      });

      const schedId = db.prepare(`SELECT id FROM exam_schedules WHERE uid = 'uid-esched-math'`).get()?.id;

      if (studentId && schedId) {
        db.prepare(
          `INSERT OR IGNORE INTO exam_attempts (uid, student_id, exam_group_id, schedule_id, started_at, submitted_at, time_spent_seconds, total_marks, obtained_marks, percentage, status)
           VALUES (@uid, @student_id, @exam_group_id, @schedule_id, @started_at, @submitted_at, @time_spent_seconds, @total_marks, @obtained_marks, @percentage, @status)`
        ).run({
          uid: 'uid-eattempt-1',
          student_id: studentId,
          exam_group_id: egroupId,
          schedule_id: schedId,
          started_at: '2026-07-20 09:02:00',
          submitted_at: '2026-07-20 09:45:00',
          time_spent_seconds: 2580,
          total_marks: 100,
          obtained_marks: 85,
          percentage: 85.0,
          status: 'completed'
        });
      }
    }
  }

  console.log('[seed] teacher, medical & online exams seeded');
}

module.exports = { seedTeacherMedicalExams };
