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

  // Fee Groups
  const feeGroups = [
    { uid: 'uid-fee-tuition', name: 'Term 1 Tuition Fee 2026', description: 'Standard term tuition fee', amount: 450.00, type: 'tuition', frequency: 'termly', academic_year: '2026', due_date: '2026-08-31' },
    { uid: 'uid-fee-dev',     name: 'Development Levy 2026',   description: 'School infrastructure development levy', amount: 50.00, type: 'levy', frequency: 'annual', academic_year: '2026', due_date: '2026-08-31' },
    { uid: 'uid-fee-bus',     name: 'Bus Transport Fee Term 1',description: 'Suburban daily transport route fee', amount: 80.00, type: 'transport', frequency: 'termly', academic_year: '2026', due_date: '2026-08-31' },
  ];

  const stmtFeeGroup = db.prepare(
    `INSERT OR IGNORE INTO fee_groups (uid, name, description, amount, type, frequency, academic_year, due_date)
     VALUES (@uid, @name, @description, @amount, @type, @frequency, @academic_year, @due_date)`
  );
  for (const fg of feeGroups) stmtFeeGroup.run(fg);

  const feeTuitionId = db.prepare(`SELECT id FROM fee_groups WHERE uid = 'uid-fee-tuition'`).get()?.id;
  const feeDevId     = db.prepare(`SELECT id FROM fee_groups WHERE uid = 'uid-fee-dev'`).get()?.id;

  // Student Fees & Payments
  if (studentId && feeTuitionId) {
    db.prepare(
      `INSERT OR IGNORE INTO student_fees (uid, student_id, fee_group_id, amount, paid_amount, status, due_date, paid_at)
       VALUES (@uid, @student_id, @fee_group_id, @amount, @paid_amount, @status, @due_date, @paid_at)`
    ).run({
      uid: 'uid-sfee-tuition',
      student_id: studentId,
      fee_group_id: feeTuitionId,
      amount: 450.00,
      paid_amount: 300.00,
      status: 'partial',
      due_date: '2026-08-31',
      paid_at: '2026-07-10'
    });

    const sfeeTuitionId = db.prepare(`SELECT id FROM student_fees WHERE uid = 'uid-sfee-tuition'`).get()?.id;

    if (sfeeTuitionId) {
      db.prepare(
        `INSERT OR IGNORE INTO payments (uid, student_fee_id, amount, payment_date, payment_method, reference_number, transaction_id, collected_by, notes)
         VALUES (@uid, @student_fee_id, @amount, @payment_date, @payment_method, @reference_number, @transaction_id, @collected_by, @notes)`
      ).run({
        uid: 'uid-pay-1',
        student_fee_id: sfeeTuitionId,
        amount: 300.00,
        payment_date: '2026-07-10',
        payment_method: 'paynow',
        reference_number: 'PAY-2026-001',
        transaction_id: 'TX-PN-99812',
        collected_by: bursarId || 1,
        notes: 'Paynow EcoCash payment for Term 1 tuition balance'
      });
    }
  }

  if (studentId && feeDevId) {
    db.prepare(
      `INSERT OR IGNORE INTO student_fees (uid, student_id, fee_group_id, amount, paid_amount, status, due_date, paid_at)
       VALUES (@uid, @student_id, @fee_group_id, @amount, @paid_amount, @status, @due_date, @paid_at)`
    ).run({
      uid: 'uid-sfee-dev',
      student_id: studentId,
      fee_group_id: feeDevId,
      amount: 50.00,
      paid_amount: 50.00,
      status: 'paid',
      due_date: '2026-08-31',
      paid_at: '2026-07-05'
    });

    const sfeeDevId = db.prepare(`SELECT id FROM student_fees WHERE uid = 'uid-sfee-dev'`).get()?.id;

    if (sfeeDevId) {
      db.prepare(
        `INSERT OR IGNORE INTO payments (uid, student_fee_id, amount, payment_date, payment_method, reference_number, transaction_id, collected_by, notes)
         VALUES (@uid, @student_fee_id, @amount, @payment_date, @payment_method, @reference_number, @transaction_id, @collected_by, @notes)`
      ).run({
        uid: 'uid-pay-2',
        student_fee_id: sfeeDevId,
        amount: 50.00,
        payment_date: '2026-07-05',
        payment_method: 'cash',
        reference_number: 'PAY-2026-002',
        transaction_id: 'CASH-REC-014',
        collected_by: bursarId || 1,
        notes: 'Direct cash payment at Bursar desk'
      });
    }
  }

  // Expenses
  const expenses = [
    { uid: 'uid-exp-1', category: 'Laboratory Supplies', subcategory: 'Chemicals', description: 'Purchase of Titration reagents & indicators', amount: 250.00, expense_date: '2026-07-12', vendor: 'Harare Scientific Ltd', payment_method: 'bank_transfer', status: 'approved', created_by: bursarId },
    { uid: 'uid-exp-2', category: 'Sports Equipment', subcategory: 'Balls & Nets', description: 'Soccer and Netball replacement equipment', amount: 180.00, expense_date: '2026-07-15', vendor: 'City Sports Mart', payment_method: 'cash', status: 'approved', created_by: bursarId },
  ];

  const stmtExp = db.prepare(
    `INSERT OR IGNORE INTO expenses (uid, category, subcategory, description, amount, expense_date, vendor, payment_method, status, created_by)
     VALUES (@uid, @category, @subcategory, @description, @amount, @expense_date, @vendor, @payment_method, @status, @created_by)`
  );
  for (const ex of expenses) stmtExp.run(ex);

  // Payroll Runs & Payslips
  db.prepare(
    `INSERT OR IGNORE INTO payroll_runs (uid, period_month, period_year, description, status, total_gross, total_deductions, total_net, created_by)
     VALUES (@uid, @period_month, @period_year, @description, @status, @total_gross, @total_deductions, @total_net, @created_by)`
  ).run({
    uid: 'uid-payrun-jul26',
    period_month: 7,
    period_year: 2026,
    description: 'July 2026 Monthly Staff Payroll',
    status: 'paid',
    total_gross: 2900.00,
    total_deductions: 320.00,
    total_net: 2580.00,
    created_by: hrId || 1
  });

  const payrunId = db.prepare(`SELECT id FROM payroll_runs WHERE uid = 'uid-payrun-jul26'`).get()?.id;

  if (payrunId && teacherId) {
    db.prepare(
      `INSERT OR IGNORE INTO payslips (uid, staff_id, payroll_run_id, basic_salary, housing_allowance, transport_allowance, gross_salary, net_salary, status)
       VALUES (@uid, @staff_id, @payroll_run_id, @basic_salary, @housing_allowance, @transport_allowance, @gross_salary, @net_salary, @status)`
    ).run({
      uid: 'uid-pslip-teacher',
      staff_id: teacherId,
      payroll_run_id: payrunId,
      basic_salary: 850.00,
      housing_allowance: 150.00,
      transport_allowance: 80.00,
      gross_salary: 1080.00,
      net_salary: 960.00,
      status: 'paid'
    });
  }

  if (payrunId && bursarId) {
    db.prepare(
      `INSERT OR IGNORE INTO payslips (uid, staff_id, payroll_run_id, basic_salary, housing_allowance, transport_allowance, gross_salary, net_salary, status)
       VALUES (@uid, @staff_id, @payroll_run_id, @basic_salary, @housing_allowance, @transport_allowance, @gross_salary, @net_salary, @status)`
    ).run({
      uid: 'uid-pslip-bursar',
      staff_id: bursarId,
      payroll_run_id: payrunId,
      basic_salary: 950.00,
      housing_allowance: 180.00,
      transport_allowance: 100.00,
      gross_salary: 1230.00,
      net_salary: 1090.00,
      status: 'paid'
    });
  }

  console.log('[seed] finance and payroll seeded');
}

module.exports = { seedFinanceAndPayroll };
