/**
 * Africa Alert - Primary School Data Seed (Grade 1 - Grade 7)
 *
 * Populates the database with a realistic working Primary School for UAT.
 * - 1 class for each cohort: Grade 1, Grade 2, Grade 3, Grade 4, Grade 5, Grade 6, Grade 7 (7 classes total)
 * - 10 Teachers
 * - 30 Students per class (210 students total)
 * - 4 Assignments per class (3 Done/Graded with full submissions, 1 Pending due in 5 days)
 * - All UAT Roles: superadmin, admin, principal, bursar, teacher, student, parent, hr, librarian, nurse, accountant
 * - Mid-term operational state: attendance, fees, payments, messages, events, library books.
 *
 * Idempotent & clean: replaces/seeds primary school UAT dataset safely.
 */

const Database = require('better-sqlite3');
const bcrypt = require('bcryptjs');
const path = require('path');

const dbPath = process.env.DB_PATH || path.join(__dirname, '../../data/school.db');

const ACADEMIC_YEAR = '2026';
const TERM = 'Term 2';

const MALE_NAMES = ['Tendai', 'Tinashe', 'Tafadzwa', 'Munyaradzi', 'Kudzai', 'Tapiwa', 'Tadiwa', 'Shingai', 'Thabani', 'Sipho', 'Nkosana', 'Bheki', 'Sibusiso', 'Melusi', 'Mthabisi', 'Simbarashe', 'Tatenda', 'Takudzwa', 'Panashe', 'Ngonidzashe', 'Tawanda', 'Munashe', 'Brian', 'Keith', 'Tanaka', 'Kudakwashe', 'Gamuchirai', 'Tawana', 'Rutendo', 'Anesu'];
const FEMALE_NAMES = ['Nyasha', 'Chipo', 'Farai', 'Rutendo', 'Rudo', 'Makanaka', 'Thandiwe', 'Zanele', 'Ayanda', 'Nomusa', 'Ruvimbo', 'Maita', 'Sukoluhle', 'Nokuthula', 'Ntombizodwa', 'Busisiwe', 'Nolwazi', 'Nomvula', 'Sinethemba', 'Nompilo', 'Mandisa', 'Philile', 'Lwandile', 'Snenhlanhla', 'Nontuthuko', 'Khanyisile', 'Nonhle', 'Qhawe', 'Lerato', 'Boitumelo'];
const SURNAMES = ['Moyo', 'Sibanda', 'Ndlovu', 'Ncube', 'Dube', 'Mutoko', 'Chirwa', 'Mhlanga', 'Zhou', 'Phiri', 'Nyoni', 'Mpofu', 'Gumbo', 'Tshuma', 'Nkomo', 'Chikanya', 'Mutasa', 'Mapfumo', 'Makoni', 'Chigumba', 'Khumalo', 'Maseko', 'Ndira', 'Muzenda', 'Chivasa', 'Mangena', 'Sithole', 'Ngwenya', 'Mlambo', 'Nkala'];

const randomFrom = (arr) => arr[Math.floor(Math.random() * arr.length)];
const isoDate = (d) => d.toISOString().split('T')[0];

function runPrimarySchoolSeed(dbInstance) {
  const db = dbInstance || new Database(dbPath);
  db.pragma('foreign_keys = ON');

  console.log('=====================================================');
  console.log('Seeding Primary School UAT Data (Grades 1-7)...');
  console.log('=====================================================');

  const defaultPwdHash = bcrypt.hashSync('admin123', 4);
  const teacherPwdHash = bcrypt.hashSync('teacher123', 4);
  const studentPwdHash = bcrypt.hashSync('student123', 4);
  const parentPwdHash  = bcrypt.hashSync('parent123', 4);
  const bursarPwdHash  = bcrypt.hashSync('bursar123', 4);
  const hrPwdHash      = bcrypt.hashSync('hr123', 4);
  const libPwdHash     = bcrypt.hashSync('librarian123', 4);
  const nursePwdHash   = bcrypt.hashSync('nurse123', 4);
  const princPwdHash   = bcrypt.hashSync('principal123', 4);

  db.transaction(() => {
    // ---------------------------------------------------------------
    // 0. System Settings & Education Level Config
    // ---------------------------------------------------------------
    const setSetting = db.prepare(`INSERT OR REPLACE INTO system_settings (key, value, description, category) VALUES (?, ?, ?, ?)`);
    setSetting.run('school_name', 'Africa Alert Primary School', 'Display name of the school', 'general');
    setSetting.run('term', TERM, 'Current academic term', 'academic');
    setSetting.run('academic_year', ACADEMIC_YEAR, 'Current academic year', 'academic');
    setSetting.run('education_level', 'primary', 'Current education level', 'general');
    setSetting.run('default_grading_scale', 'zimsec_primary', 'Default primary grading scale', 'academic');

    // ---------------------------------------------------------------
    // 1. All Roles Demo Users (for UAT)
    // ---------------------------------------------------------------
    const stmtUser = db.prepare(`
      INSERT INTO users 
        (uid, email, password, role, first_name, last_name, phone, gender, is_active, sync_status)
      VALUES 
        (?, ?, ?, ?, ?, ?, ?, ?, 1, 'synced')
      ON CONFLICT(email) DO UPDATE SET
        password = excluded.password,
        role = excluded.role,
        first_name = excluded.first_name,
        last_name = excluded.last_name,
        phone = excluded.phone,
        is_active = 1
    `);

    const coreUatUsers = [
      { uid: 'uid-superadmin', email: 'superadmin@school.com', role: 'school_admin', first_name: 'Super',    last_name: 'Admin',    pwd: defaultPwdHash, phone: '+263771000001' },
      { uid: 'uid-admin',      email: 'admin@school.com',      role: 'school_admin', first_name: 'System',   last_name: 'Admin',    pwd: defaultPwdHash, phone: '+263771000002' },
      { uid: 'uid-principal',  email: 'principal@school.com',  role: 'principal', first_name: 'Tendai',   last_name: 'Moyo',     pwd: princPwdHash,   phone: '+263771000003' },
      { uid: 'uid-bursar',     email: 'bursar@school.com',     role: 'bursar',    first_name: 'Kudzai',   last_name: 'Ncube',    pwd: bursarPwdHash,  phone: '+263771000004' },
      { uid: 'uid-accountant', email: 'accountant@school.com', role: 'accountant',first_name: 'Sipho',    last_name: 'Dube',     pwd: bursarPwdHash,  phone: '+263771000005' },
      { uid: 'uid-hr',         email: 'hr@school.com',         role: 'hr',        first_name: 'Nyasha',   last_name: 'Sithole',  pwd: hrPwdHash,      phone: '+263771000006' },
      { uid: 'uid-librarian',  email: 'librarian@school.com',  role: 'librarian', first_name: 'Rumbidzai', last_name: 'Gumbo',    pwd: libPwdHash,     phone: '+263771000007' },
      { uid: 'uid-nurse',      email: 'nurse@school.com',      role: 'nurse',     first_name: 'Chipo',    last_name: 'Phiri',    pwd: nursePwdHash,   phone: '+263771000008' },
    ];

    for (const u of coreUatUsers) {
      stmtUser.run(u.uid, u.email, u.pwd, u.role, u.first_name, u.last_name, u.phone, 'female');
    }
    console.log('✓ Core UAT administrative roles seeded.');

    // ---------------------------------------------------------------
    // 2. 10 Teachers
    // ---------------------------------------------------------------
    const teacherIds = [];
    stmtUser.run('uid-teacher-0', 'teacher@school.com', teacherPwdHash, 'teacher', 'Blessing', 'Chiwenga', '+263772000001', 'female');
    const t0Row = db.prepare('SELECT id FROM users WHERE email = ?').get('teacher@school.com');
    teacherIds.push(t0Row.id);

    for (let i = 1; i <= 9; i++) {
      const isMale = i % 2 === 0;
      const fn = isMale ? MALE_NAMES[i] : FEMALE_NAMES[i];
      const ln = SURNAMES[i];
      const email = `teacher${i}@school.com`;
      stmtUser.run(`uid-teacher-${i}`, email, teacherPwdHash, 'teacher', fn, ln, `+26377200000${i+1}`, isMale ? 'male' : 'female');
      const row = db.prepare('SELECT id FROM users WHERE email = ?').get(email);
      teacherIds.push(row.id);
    }
    console.log(`✓ 10 Teachers seeded.`);

    // ---------------------------------------------------------------
    // 3. 7 Classes (Grade 1 to Grade 7, 1 per cohort)
    // ---------------------------------------------------------------
    const gradesDef = [
      { name: 'Grade 1', level: 1 },
      { name: 'Grade 2', level: 2 },
      { name: 'Grade 3', level: 3 },
      { name: 'Grade 4', level: 4 },
      { name: 'Grade 5', level: 5 },
      { name: 'Grade 6', level: 6 },
      { name: 'Grade 7', level: 7 },
    ];

    const classIds = [];
    const stmtClass = db.prepare(`
      INSERT INTO classes (uid, name, section, capacity, description, class_teacher_id, sync_status)
      VALUES (?, ?, 'A', 35, ?, ?, 'synced')
      ON CONFLICT(uid) DO UPDATE SET
        name = excluded.name,
        description = excluded.description,
        class_teacher_id = excluded.class_teacher_id
    `);

    for (let i = 0; i < gradesDef.length; i++) {
      const g = gradesDef[i];
      const uid = `uid-class-g${g.level}`;
      const teacherId = teacherIds[i % teacherIds.length];
      stmtClass.run(uid, g.name, `${g.name} A - Academic Year ${ACADEMIC_YEAR}`, teacherId);
      const row = db.prepare('SELECT id FROM classes WHERE uid = ?').get(uid);
      classIds.push({ id: row.id, name: g.name, level: g.level, teacher_id: teacherId });
    }
    console.log(`✓ 7 Primary School Classes (Grade 1 - Grade 7) seeded.`);

    // ---------------------------------------------------------------
    // 4. Primary Subjects & Courses
    // ---------------------------------------------------------------
    const primarySubjectsDef = [
      { name: 'Mathematics', code: 'MATH-PRI' },
      { name: 'English Language', code: 'ENG-PRI' },
      { name: 'Shona & Indigenous Languages', code: 'IND-PRI' },
      { name: 'Science & Technology', code: 'SCI-PRI' },
      { name: 'Agriculture', code: 'AGRI-PRI' },
      { name: 'Social Sciences & Heritage', code: 'SOC-PRI' },
      { name: 'Visual & Performing Arts', code: 'VPA-PRI' },
      { name: 'ICT & Computer Studies', code: 'ICT-PRI' },
    ];

    const subjectIds = [];
    const stmtSubj = db.prepare(`
      INSERT INTO subjects (uid, name, code, class_id, teacher_id, credit_hours, sync_status)
      VALUES (?, ?, ?, ?, ?, 4, 'synced')
      ON CONFLICT(uid) DO UPDATE SET
        name = excluded.name,
        code = excluded.code,
        teacher_id = excluded.teacher_id
    `);

    for (let i = 0; i < primarySubjectsDef.length; i++) {
      const s = primarySubjectsDef[i];
      const uid = `uid-subj-pri-${i}`;
      const teacherId = teacherIds[i % teacherIds.length];
      stmtSubj.run(uid, s.name, s.code, classIds[0].id, teacherId);
      const row = db.prepare('SELECT id FROM subjects WHERE uid = ?').get(uid);
      subjectIds.push({ id: row.id, name: s.name, code: s.code, teacher_id: teacherId });
    }

    const courseIdsByClass = {};
    const stmtCourse = db.prepare(`
      INSERT INTO courses (uid, name, subject_id, teacher_id, status)
      VALUES (?, ?, ?, ?, 'active')
      ON CONFLICT(uid) DO UPDATE SET
        name = excluded.name,
        subject_id = excluded.subject_id,
        teacher_id = excluded.teacher_id
    `);

    for (const cls of classIds) {
      courseIdsByClass[cls.id] = [];
      for (const subj of subjectIds) {
        const courseUid = `uid-course-g${cls.level}-${subj.code}`;
        stmtCourse.run(courseUid, `${cls.name} ${subj.name}`, subj.id, subj.teacher_id);
        const crow = db.prepare('SELECT id FROM courses WHERE uid = ?').get(courseUid);
        if (crow) courseIdsByClass[cls.id].push(crow.id);
      }
    }
    console.log(`✓ Primary Subjects & Courses created for all 7 Grade classes.`);

    // ---------------------------------------------------------------
    // 5. 210 Students (30 per class) + Enrollments
    // ---------------------------------------------------------------
    const studentIdsByClass = {};
    const allStudents = [];
    const stmtEnrollment = db.prepare(`
      INSERT INTO enrollments (uid, student_id, class_id, academic_year, status, roll_number, sync_status)
      VALUES (?, ?, ?, ?, 'active', ?, 'synced')
      ON CONFLICT(uid) DO UPDATE SET
        class_id = excluded.class_id,
        roll_number = excluded.roll_number
    `);
    const stmtCourseEnrollment = db.prepare(`
      INSERT INTO course_enrolments (uid, student_id, course_id, status)
      VALUES (?, ?, ?, 'active')
      ON CONFLICT(uid) DO NOTHING
    `);

    let studentCounter = 0;

    for (const cls of classIds) {
      studentIdsByClass[cls.id] = [];
      for (let sIdx = 1; sIdx <= 30; sIdx++) {
        studentCounter++;
        let email, fn, ln, uid;

        if (cls.level === 7 && sIdx === 1) {
          email = 'student@school.com';
          fn = 'Tariro';
          ln = 'Mutasa';
          uid = 'uid-demo-student';
        } else {
          const isMale = Math.random() < 0.5;
          fn = isMale ? randomFrom(MALE_NAMES) : randomFrom(FEMALE_NAMES);
          ln = randomFrom(SURNAMES);
          email = `student.g${cls.level}.${sIdx}@school.com`;
          uid = `uid-student-g${cls.level}-${sIdx}`;
        }

        stmtUser.run(uid, email, studentPwdHash, 'student', fn, ln, null, 'female');
        const sRow = db.prepare('SELECT id FROM users WHERE email = ?').get(email);
        const studentId = sRow.id;

        const rollNum = `2026-G${cls.level}-${sIdx.toString().padStart(2, '0')}`;
        stmtEnrollment.run(`uid-enr-g${cls.level}-${sIdx}`, studentId, cls.id, ACADEMIC_YEAR, rollNum);

        studentIdsByClass[cls.id].push(studentId);
        allStudents.push({ id: studentId, email, name: `${fn} ${ln}`, classId: cls.id, gradeLevel: cls.level });

        for (const courseId of courseIdsByClass[cls.id]) {
          stmtCourseEnrollment.run(`uid-cenr-s${studentId}-c${courseId}`, studentId, courseId);
        }
      }
    }
    console.log(`✓ 210 Students (30 per class from Grade 1 to 7) enrolled.`);

    // ---------------------------------------------------------------
    // 6. Parents & Parent-Student Links
    // ---------------------------------------------------------------
    const stmtParentLink = db.prepare(`
      INSERT INTO parent_students (uid, parent_id, student_id, relationship, sync_status)
      VALUES (?, ?, ?, ?, 'synced')
      ON CONFLICT(parent_id, student_id) DO NOTHING
    `);

    stmtUser.run('uid-demo-parent', 'parent@school.com', parentPwdHash, 'parent', 'Farai', 'Mutasa', '+263773000001', 'male');
    const demoParentRow = db.prepare('SELECT id FROM users WHERE email = ?').get('parent@school.com');

    const demoStudentId = db.prepare('SELECT id FROM users WHERE email = ?').get('student@school.com').id;
    stmtParentLink.run('uid-plink-demo-1', demoParentRow.id, demoStudentId, 'father');

    if (allStudents[0]) stmtParentLink.run('uid-plink-demo-2', demoParentRow.id, allStudents[0].id, 'father');
    if (allStudents[1]) stmtParentLink.run('uid-plink-demo-3', demoParentRow.id, allStudents[1].id, 'father');

    for (let pIdx = 1; pIdx <= 60; pIdx++) {
      const isMale = pIdx % 2 === 0;
      const fn = isMale ? MALE_NAMES[pIdx % MALE_NAMES.length] : FEMALE_NAMES[pIdx % FEMALE_NAMES.length];
      const ln = SURNAMES[pIdx % SURNAMES.length];
      const email = `parent${pIdx}@school.com`;
      stmtUser.run(`uid-parent-${pIdx}`, email, parentPwdHash, 'parent', fn, ln, `+263773000${pIdx.toString().padStart(3, '0')}`, isMale ? 'male' : 'female');
      const pRow = db.prepare('SELECT id FROM users WHERE email = ?').get(email);

      const startS = (pIdx * 3) % allStudents.length;
      for (let offset = 0; offset < 2; offset++) {
        const st = allStudents[(startS + offset) % allStudents.length];
        stmtParentLink.run(`uid-plink-${pIdx}-${offset}`, pRow.id, st.id, isMale ? 'father' : 'mother');
      }
    }
    console.log(`✓ Parents & Parent-Student relationships linked.`);

    // ---------------------------------------------------------------
    // 7. ASSIGNMENTS: 4 per Class (3 DONE/Graded + 1 PENDING)
    // ---------------------------------------------------------------
    const stmtAssignment = db.prepare(`
      INSERT INTO assignments (uid, course_id, title, description, due_date, max_score, is_published, sync_status)
      VALUES (?, ?, ?, ?, ?, ?, 1, 'synced')
      ON CONFLICT(uid) DO UPDATE SET
        title = excluded.title,
        description = excluded.description,
        due_date = excluded.due_date,
        max_score = excluded.max_score
    `);

    const stmtSubmission = db.prepare(`
      INSERT INTO submissions (uid, assignment_id, student_id, content, status, grade, feedback, graded_by, submitted_at)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
      ON CONFLICT(uid) DO UPDATE SET
        status = excluded.status,
        grade = excluded.grade,
        feedback = excluded.feedback
    `);

    const stmtGrade = db.prepare(`
      INSERT INTO grades (uid, student_id, subject_id, exam_type, marks, total_marks, grade, remarks, academic_year, term, sync_status)
      VALUES (?, ?, ?, 'assignment', ?, 100, ?, ?, ?, ?, 'synced')
      ON CONFLICT(uid) DO UPDATE SET
        marks = excluded.marks,
        grade = excluded.grade
    `);

    let totalAssignmentsCount = 0;
    let totalSubmissionsCount = 0;

    const assignmentTemplates = [
      // Grade 1
      [
        { title: 'Grade 1 Math: Counting & Numbers 1-50', desc: 'Count objects and complete number patterns.', pastDays: 18, pending: false },
        { title: 'Grade 1 Phonics & Alphabet Trace', desc: 'Trace letters A-Z and match with pictures.', pastDays: 10, pending: false },
        { title: 'Grade 1 Shona: Madzita NeMhuri', desc: 'Identify family members in Shona.', pastDays: 3, pending: false },
        { title: 'Grade 1 Art: Primary Colors Drawing', desc: 'Draw a rainbow using primary colors.', pastDays: -5, pending: true },
      ],
      // Grade 2
      [
        { title: 'Grade 2 Math: Addition & Subtraction', desc: 'Solve 2-digit addition problems.', pastDays: 16, pending: false },
        { title: 'Grade 2 English: Reading Comprehension', desc: 'Read the short story and answer 5 questions.', pastDays: 9, pending: false },
        { title: 'Grade 2 Science: Parts of a Plant', desc: 'Label roots, stem, leaves, and flower.', pastDays: 4, pending: false },
        { title: 'Grade 2 Agriculture: Domestic Animals', desc: 'List 5 farm animals and their uses.', pastDays: -5, pending: true },
      ],
      // Grade 3
      [
        { title: 'Grade 3 Math: Multiplication Tables (2, 5, 10)', desc: 'Complete multiplication table worksheets.', pastDays: 20, pending: false },
        { title: 'Grade 3 English: Nouns and Verbs', desc: 'Identify nouns and verbs in prescribed sentences.', pastDays: 11, pending: false },
        { title: 'Grade 3 Social Science: Our Local Community', desc: 'Write 5 sentences about community helpers.', pastDays: 3, pending: false },
        { title: 'Grade 3 ICT: Parts of a Computer System', desc: 'Identify monitor, keyboard, and mouse.', pastDays: -5, pending: true },
      ],
      // Grade 4
      [
        { title: 'Grade 4 Math: Long Division & Fractions', desc: 'Divide 3-digit numbers and simplify fractions.', pastDays: 21, pending: false },
        { title: 'Grade 4 English Essay: My School Day', desc: 'Write a 150-word essay about your school.', pastDays: 12, pending: false },
        { title: 'Grade 4 Science: Water Cycle & Weather', desc: 'Explain evaporation, condensation, and precipitation.', pastDays: 5, pending: false },
        { title: 'Grade 4 Agriculture: Soil Types & Crops', desc: 'Compare clay, sand, and loam soils.', pastDays: -5, pending: true },
      ],
      // Grade 5
      [
        { title: 'Grade 5 Math: Decimals & Percentages', desc: 'Convert fractions to decimals and percentages.', pastDays: 22, pending: false },
        { title: 'Grade 5 English: Formal Letter Writing', desc: 'Write a letter to the Headmaster requesting library books.', pastDays: 14, pending: false },
        { title: 'Grade 5 Science: Human Digestive System', desc: 'Trace the path of food through organs.', pastDays: 4, pending: false },
        { title: 'Grade 5 Heritage: Historical Monuments in Zim', desc: 'Research Great Zimbabwe and write 1 page.', pastDays: -5, pending: true },
      ],
      // Grade 6
      [
        { title: 'Grade 6 Math: Geometry & Perimeter/Area', desc: 'Calculate area of rectangles and triangles.', pastDays: 25, pending: false },
        { title: 'Grade 6 English Composition: An Unforgettable Day', desc: 'Write a 250-word narrative essay.', pastDays: 15, pending: false },
        { title: 'Grade 6 Science: Renewable Energy Sources', desc: 'Compare solar, hydro, and wind energy.', pastDays: 5, pending: false },
        { title: 'Grade 6 ICT: Word Processing & Formatting', desc: 'Create a formatted document with headings and bullets.', pastDays: -5, pending: true },
      ],
      // Grade 7
      [
        { title: 'Grade 7 ZIMSEC Math Revision Paper 1', desc: 'Complete 40 multiple choice revision questions.', pastDays: 28, pending: false },
        { title: 'Grade 7 English Paper 2 Guided Composition', desc: 'Write a formal speech for Prize Giving Day.', pastDays: 16, pending: false },
        { title: 'Grade 7 General Science Practice Test', desc: 'Full mid-term science practice exam.', pastDays: 6, pending: false },
        { title: 'Grade 7 Heritage & Social Studies Project', desc: 'Prepare a group presentation on national symbols.', pastDays: -5, pending: true },
      ],
    ];

    for (let gIdx = 0; gIdx < classIds.length; gIdx++) {
      const cls = classIds[gIdx];
      const templates = assignmentTemplates[gIdx];
      const courses = courseIdsByClass[cls.id];
      const students = studentIdsByClass[cls.id];

      for (let aIdx = 0; aIdx < templates.length; aIdx++) {
        const tpl = templates[aIdx];
        const subjectId = subjectIds[aIdx % subjectIds.length].id;
        const assignUid = `uid-assign-g${cls.level}-${aIdx+1}`;

        const dueDate = new Date();
        dueDate.setDate(dueDate.getDate() - tpl.pastDays);

        stmtAssignment.run(assignUid, subjectId, tpl.title, tpl.desc, isoDate(dueDate), 100);
        const aRow = db.prepare('SELECT id FROM assignments WHERE uid = ?').get(assignUid);
        const assignId = aRow.id;
        totalAssignmentsCount++;

        const teacherId = cls.teacher_id;

        if (!tpl.pending) {
          // 3 DONE / GRADED ASSIGNMENTS -> ALL 30 students submitted & graded!
          for (let s = 0; s < students.length; s++) {
            const studentId = students[s];
            const marks = Math.floor(65 + Math.random() * 32); // 65 - 96
            const letterGrade = marks >= 80 ? '1' : marks >= 70 ? '2' : marks >= 60 ? '3' : '4';
            const subUid = `uid-sub-a${assignId}-s${studentId}`;

            const subDate = new Date(dueDate);
            subDate.setHours(subDate.getHours() - Math.floor(Math.random() * 24));

            stmtSubmission.run(
              subUid,
              assignId,
              studentId,
              `Submitted assignment response for ${tpl.title}`,
              'graded',
              marks,
              marks >= 80 ? 'Excellent work, clear understanding!' : 'Good effort, check feedback notes.',
              teacherId,
              isoDate(subDate)
            );
            totalSubmissionsCount++;

            stmtGrade.run(
              `uid-grade-a${assignId}-s${studentId}`,
              studentId,
              subjectIds[aIdx % subjectIds.length].id,
              marks,
              letterGrade,
              'Mid-term assessment score',
              ACADEMIC_YEAR,
              TERM
            );
          }
        } else {
          // 1 PENDING ASSIGNMENT -> 10 students submitted (pending grading), 20 pending submission
          for (let s = 0; s < students.length; s++) {
            const studentId = students[s];
            if (s < 10) {
              const subUid = `uid-sub-a${assignId}-s${studentId}`;
              stmtSubmission.run(
                subUid,
                assignId,
                studentId,
                `Draft project submission for ${tpl.title}`,
                'submitted',
                null,
                null,
                null,
                isoDate(new Date())
              );
              totalSubmissionsCount++;
            }
          }
        }
      }
    }
    console.log(`✓ ${totalAssignmentsCount} Assignments (3 DONE + 1 PENDING per class across all 7 grades).`);
    console.log(`✓ ${totalSubmissionsCount} Submissions & Graded records populated.`);

    // ---------------------------------------------------------------
    // 8. Attendance (last 40 school days for 210 students)
    // ---------------------------------------------------------------
    const stmtAtt = db.prepare(`
      INSERT INTO attendance (uid, student_id, class_id, date, status, sync_status)
      VALUES (?, ?, ?, ?, ?, 'synced')
      ON CONFLICT(uid) DO NOTHING
    `);
    const attStatuses = ['present', 'present', 'present', 'present', 'present', 'present', 'present', 'present', 'late', 'absent'];
    let attCount = 0;

    for (let dayOffset = 0; dayOffset < 40; dayOffset++) {
      const d = new Date();
      d.setDate(d.getDate() - dayOffset);
      if (d.getDay() === 0 || d.getDay() === 6) continue;

      const dateStr = isoDate(d);
      for (const cls of classIds) {
        for (const studentId of studentIdsByClass[cls.id]) {
          const status = attStatuses[Math.floor(Math.random() * attStatuses.length)];
          stmtAtt.run(`uid-att-c${cls.id}-s${studentId}-d${dateStr}`, studentId, cls.id, dateStr, status);
          attCount++;
        }
      }
    }
    console.log(`✓ ${attCount} Attendance records seeded across 40 past school days.`);

    // ---------------------------------------------------------------
    // 9. Finance: Fee Groups, Student Fees & Payments
    // ---------------------------------------------------------------
    const stmtFeeGroup = db.prepare(`
      INSERT INTO fee_groups (uid, name, description, amount, type, frequency, academic_year, due_date, sync_status)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, 'synced')
      ON CONFLICT(uid) DO UPDATE SET amount = excluded.amount
    `);

    stmtFeeGroup.run('uid-fg-tuition', 'Term 2 Primary Tuition Fee', 'Term 2 tuition for Primary School', 180, 'tuition', 'termly', ACADEMIC_YEAR, '2026-06-30');
    stmtFeeGroup.run('uid-fg-levy', 'Sports & Activity Levy', 'Sports, arts, and amenities levy', 20, 'other', 'termly', ACADEMIC_YEAR, '2026-06-30');
    stmtFeeGroup.run('uid-fg-ict', 'Computer & Library Access Fee', 'ICT lab and library maintenance fee', 15, 'activity', 'termly', ACADEMIC_YEAR, '2026-06-30');

    const fgTuition = db.prepare('SELECT id FROM fee_groups WHERE uid = ?').get('uid-fg-tuition').id;

    const stmtStudentFee = db.prepare(`
      INSERT INTO student_fees (uid, student_id, fee_group_id, amount, paid_amount, status, due_date, sync_status)
      VALUES (?, ?, ?, ?, ?, ?, ?, 'synced')
      ON CONFLICT(uid) DO UPDATE SET paid_amount = excluded.paid_amount, status = excluded.status
    `);
    const stmtPayment = db.prepare(`
      INSERT INTO payments (uid, student_fee_id, amount, payment_method, reference_number, collected_by, notes, sync_status)
      VALUES (?, ?, ?, ?, ?, ?, ?, 'synced')
      ON CONFLICT(uid) DO NOTHING
    `);

    let sfCount = 0;
    let payCount = 0;
    const methods = ['paynow', 'ecocash', 'cash', 'bank'];

    for (const st of allStudents) {
      const rand = Math.random();
      let status = 'pending';
      let paid = 0;
      if (rand < 0.65) { status = 'paid'; paid = 180; }
      else if (rand < 0.85) { status = 'partial'; paid = 90; }

      const sfUid = `uid-sf-tui-${st.id}`;
      stmtStudentFee.run(sfUid, st.id, fgTuition, 180, paid, status, '2026-06-30');
      const sfRow = db.prepare('SELECT id FROM student_fees WHERE uid = ?').get(sfUid);
      sfCount++;

      if (paid > 0) {
        const pMethod = randomFrom(methods);
        const ref = 'TXN-PRI-' + Math.floor(100000 + Math.random() * 900000);
        stmtPayment.run(`uid-pay-${sfRow.id}`, sfRow.id, paid, pMethod, ref, teacherIds[0], `${pMethod} payment for Term 2 tuition`);
        payCount++;
      }
    }
    console.log(`✓ ${sfCount} Student Fee accounts & ${payCount} Payments seeded.`);

    // ---------------------------------------------------------------
    // 10. Announcements & Direct Messages
    // ---------------------------------------------------------------
    const stmtMsg = db.prepare(`
      INSERT INTO messages (uid, sender_id, recipient_id, subject, body, is_announcement, priority, sync_status)
      VALUES (?, ?, ?, ?, ?, ?, ?, 'synced')
      ON CONFLICT(uid) DO NOTHING
    `);
    const adminId = db.prepare('SELECT id FROM users WHERE email = ?').get('admin@school.com').id;

    stmtMsg.run('uid-msg-1', adminId, null, 'Mid-Term Consultation & Report Day', 'Dear Parents, Mid-term consultation day is scheduled for next Friday. Please arrange to meet your child’s class teacher.', 1, 'high');
    stmtMsg.run('uid-msg-2', adminId, null, 'Inter-House Athletics Competition', 'All primary school grades will participate in the Annual Athletics Day on the main sports grounds.', 1, 'normal');
    stmtMsg.run('uid-msg-3', teacherIds[0], demoParentRow.id, 'Tariro’s Outstanding Performance', 'Dear Mr. Mutasa, Tariro has excelled in the recent Grade 7 Math revision test. Keep encouraging her!', 0, 'normal');

    console.log('✓ School Notices & Messages seeded.');

    // ---------------------------------------------------------------
    // 11. Events Calendar
    // ---------------------------------------------------------------
    const stmtEvent = db.prepare(`
      INSERT INTO events (uid, title, description, event_date, event_type, venue, is_public, academic_year, sync_status)
      VALUES (?, ?, ?, ?, ?, ?, 1, ?, 'synced')
      ON CONFLICT(uid) DO NOTHING
    `);
    const today = new Date();
    stmtEvent.run('uid-evt-1', 'Grade 7 ZIMSEC Registration Deadline', 'Final day for Grade 7 examination registration', isoDate(new Date(today.getTime() + 7*86400000)), 'deadline', 'Administration Office', ACADEMIC_YEAR);
    stmtEvent.run('uid-evt-2', 'Primary School Sports Day', 'Inter-house sports competitions', isoDate(new Date(today.getTime() + 14*86400000)), 'activity', 'Main Sports Field', ACADEMIC_YEAR);
    stmtEvent.run('uid-evt-3', 'Parent-Teacher Mid-Term Conference', 'Individual parent meetings with class teachers', isoDate(new Date(today.getTime() + 21*86400000)), 'meeting', 'School Assembly Hall', ACADEMIC_YEAR);

    console.log('✓ Calendar Events seeded.');
  })();

  console.log('=====================================================');
  console.log('  PRIMARY SCHOOL UAT SEED COMPLETE!');
  console.log('=====================================================');
  console.log('UAT Credentials Overview:');
  console.log('  Super Admin : superadmin@school.com / superadmin123');
  console.log('  Admin       : admin@school.com      / admin123');
  console.log('  Principal   : principal@school.com  / principal123');
  console.log('  Bursar      : bursar@school.com     / bursar123');
  console.log('  Teacher (G1): teacher@school.com    / teacher123');
  console.log('  Student (G7): student@school.com    / student123');
  console.log('  Parent      : parent@school.com     / parent123');
  console.log('  HR Manager  : hr@school.com         / hr123');
  console.log('  Librarian   : librarian@school.com  / librarian123');
  console.log('  Nurse       : nurse@school.com      / nurse123');
  console.log('=====================================================');
}

if (require.main === module) {
  runPrimarySchoolSeed();
}

module.exports = runPrimarySchoolSeed;
