const Database = require('better-sqlite3');
const { v4: uuidv4 } = require('uuid');
const path = require('path');

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

console.log('Populating payroll center and generating staff payslips...');

try {
  // 1. Clear existing payroll runs and payslips to ensure clean seeding
  db.prepare('DELETE FROM payslips').run();
  db.prepare('DELETE FROM payroll_runs').run();

  // 2. Fetch all active staff records with their salary grade details
  const staff = db.prepare(`
    SELECT sr.*, sg.basic_salary, sg.housing_allowance, sg.transport_allowance, sg.medical_allowance, sg.other_allowances
    FROM staff_records sr
    JOIN salary_grades sg ON sr.salary_grade_id = sg.id
    WHERE sr.is_active = 1 AND sr.is_deleted = 0
  `).all();

  if (staff.length === 0) {
    console.log('No active staff records found to generate payslips for. Please seed staff first.');
    process.exit(1);
  }
  console.log(`✓ Active staff members found: ${staff.length}`);

  // Fetch an admin user to act as creator / approver
  const adminUser = db.prepare("SELECT id FROM users WHERE role IN ('school_admin', 'systems_admin', 'principal') LIMIT 1").get();
  const creatorId = adminUser ? adminUser.id : staff[0].user_id;

  const insertRun = db.prepare(`
    INSERT INTO payroll_runs (uid, period_month, period_year, description, status, total_gross, total_deductions, total_net, created_by, paid_at, sync_status)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'pending')
  `);

  const insertPayslip = db.prepare(`
    INSERT INTO payslips (
      uid, staff_id, payroll_run_id, basic_salary, housing_allowance, 
      transport_allowance, medical_allowance, other_allowances, gross_salary,
      tax_deduction, pension_deduction, other_deductions, total_deductions, net_salary, status, paid_at, sync_status
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'pending')
  `);

  // Months to seed
  const runDefs = [
    { month: 1, year: 2026, desc: 'January 2026 Standard Run', status: 'paid', paidAt: '2026-01-28 10:00:00' },
    { month: 2, year: 2026, desc: 'February 2026 Standard Run', status: 'paid', paidAt: '2026-02-28 10:00:00' },
    { month: 3, year: 2026, desc: 'March 2026 Standard Run', status: 'paid', paidAt: '2026-03-28 10:00:00' },
    { month: 4, year: 2026, desc: 'April 2026 Standard Run', status: 'paid', paidAt: '2026-04-28 10:00:00' },
    { month: 5, year: 2026, desc: 'May 2026 Mid-Year Adjustment Run', status: 'calculated', paidAt: null },
    { month: 6, year: 2026, desc: 'June 2026 Standard Draft Run', status: 'draft', paidAt: null }
  ];

  runDefs.forEach(rd => {
    const runUid = uuidv4();
    let totalGross = 0;
    let totalDeductions = 0;
    let totalNet = 0;

    // For draft runs, we don't calculate payslips beforehand (they get calculated dynamically on the UI)
    if (rd.status !== 'draft') {
      // First insert the payroll run (we will update the totals later)
      const runResult = insertRun.run(runUid, rd.month, rd.year, rd.desc, rd.status, 0, 0, 0, creatorId, rd.paidAt);
      const runId = runResult.lastInsertRowid;

      const payslipStatus = rd.status === 'paid' ? 'paid' : 'pending';

      // Calculate and insert payslip for each staff member
      staff.forEach(s => {
        const basic = s.basic_salary;
        const house = s.housing_allowance;
        const transport = s.transport_allowance;
        const medical = s.medical_allowance;
        const other = s.other_allowances;
        
        const gross = basic + house + transport + medical + other;
        const tax = gross * 0.15; // 15% flat rate
        const pension = basic * 0.05; // 5% pension
        const deductions = tax + pension;
        const net = gross - deductions;

        totalGross += gross;
        totalDeductions += deductions;
        totalNet += net;

        insertPayslip.run(
          uuidv4(),
          s.user_id,
          runId,
          basic,
          house,
          transport,
          medical,
          other,
          gross,
          tax,
          pension,
          0,
          deductions,
          net,
          payslipStatus,
          rd.paidAt
        );
      });

      // Update the payroll run totals
      db.prepare(`
        UPDATE payroll_runs
        SET total_gross = ?, total_deductions = ?, total_net = ?
        WHERE id = ?
      `).run(totalGross, totalDeductions, totalNet, runId);
      
      console.log(`✓ Seeded ${rd.status} payroll run for month ${rd.month}/${rd.year} (Gross: $${totalGross.toFixed(2)}, Net: $${totalNet.toFixed(2)})`);
    } else {
      // For draft run, just insert with 0 totals
      insertRun.run(runUid, rd.month, rd.year, rd.desc, rd.status, 0, 0, 0, creatorId, null);
      console.log(`✓ Seeded draft payroll run for month ${rd.month}/${rd.year}`);
    }
  });

  console.log('✓ Seeding complete! Payroll center is now fully populated.');

} catch (err) {
  console.error('Error during seeding payroll data:', err);
}
