const mysql = require('mysql');
const fs = require('fs');
const util = require('util');

const connection = mysql.createConnection({
  host     : 'localhost',
  user     : 'sooggoo',
  password : '4Compassion*',
  database : 'evergreenint'
});
const query = util.promisify(connection.query).bind(connection);

(async () => {
  try {
    connection.connect();

    // Load Labour Descriptions
    const labourDescRows = await query(`SELECT code, name FROM labourcodes`);
    const labourDescs = {};
    labourDescRows.forEach(r => labourDescs[r.code] = r.name);

    // Load Employee Names
    const empRows = await query(`SELECT employeeid, first_name, last_name, base_wage_rate FROM employeemaster`);
    const empMap = {};
    empRows.forEach(e => {
      empMap[e.employeeid] = {
        name: `${e.first_name} ${e.last_name}`,
        rate: parseFloat(e.base_wage_rate) || 0
      };
    });

    const payrunRows = await query(`SELECT * FROM payrun_details`);
    const grouped = {};

    function accumulateBreakdown(group, labourcode, base, fnpf, tpaf, total) {
      if (!group.breakdown[labourcode]) {
        group.breakdown[labourcode] = {
          labourcode,
          description: labourDescs[labourcode] || '',
          cost: 0,
          fnpf: 0,
          tpaf: 0,
          total: 0
        };
      }
      group.breakdown[labourcode].cost += base;
      group.breakdown[labourcode].fnpf += fnpf;
      group.breakdown[labourcode].tpaf += tpaf;
      group.breakdown[labourcode].total += total;
    }

    function applyCost(id, empid, labourcode, base) {
      const key = `${id}_${empid}`;
      if (!grouped[key]) {
        const emp = empMap[empid] || { name: 'Unknown', rate: 0 };
        const match = payrunRows.find(p => p.id === id && p.employeeid === empid);
        grouped[key] = {
          id,
          employeeid: empid,
          name: emp.name,
          gross: match?.totalgross_amount || 0,
          fnpf_employer: match?.fnpf_employer || 0,
          breakdown: {}
        };
      }
      const fnpfApply = grouped[key].fnpf_employer > 0;
      const fnpf = fnpfApply ? base * 0.10 : 0;
      const tpaf = fnpfApply ? base * 0.01 : 0;
      const total = base + fnpf;
      accumulateBreakdown(grouped[key], labourcode, base, fnpf, tpaf, total);
    }

    async function addFrom(table, baseFormula) {
      const rows = await query(`SELECT * FROM ${table}`);
      for (const r of rows) {
        if (!empMap[r.employeeid]) continue;
        const base = baseFormula(r, empMap[r.employeeid].rate);
        applyCost(r.id, r.employeeid, r.labourcode, base);
      }
    }

    await addFrom('manual_timesheet', (r, rate) => {
      return (parseFloat(r.nt) || 0) * rate +
             (parseFloat(r.timehalf) || 0) * 1.5 * rate +
             (parseFloat(r.dt) || 0) * 2 * rate +
             (parseFloat(r.public_holiday_hours) || 0) * rate +
             (parseFloat(r.premium) || 0) +
             (parseFloat(r.leave_hours) || 0) * rate +
             (parseFloat(r.meal) || 0) +
             (parseFloat(r.stay_away) || 0);
    });

    await addFrom('manual_tech_timesheet', (r, rate) => {
      return (parseFloat(r.nt) || 0) * rate +
             (parseFloat(r.timehalf) || 0) * 1.5 * rate +
             (parseFloat(r.dt) || 0) * 2 * rate +
             (parseFloat(r.public_holiday_hours) || 0) * rate +
             (parseFloat(r.leave_hours) || 0) * rate +
             (parseFloat(r.meal) || 0) +
             (parseFloat(r.call_out_allowances) || 0) +
             (parseFloat(r.stay_away) || 0);
    });

    await addFrom('absent', (r) => parseFloat(r.gross) || 0);

    await addFrom('payrol_adjustments_details', (r) => {
      const base = (parseFloat(r.amount) || 0) + (parseFloat(r.premium) || 0) + (parseFloat(r.meal) || 0);
      return ['6', '16'].includes(r.type_details) ? base : 0;
    });

    const output = Object.values(grouped).map(g => ({
      ...g,
      breakdown: Object.values(g.breakdown)
    }));


    const outPath = '/home/erp/www/wormalderp/nodejsapp/payrun_detail_breakdown.json';
    fs.writeFileSync(outPath, JSON.stringify(output, null, 2));
    console.log(`Wrote ${output.length} employee records to ${outPath}`);

    connection.end();
  } catch (err) {
    console.error('Error:', err);
    connection.end();
    process.exit(1);
  }
})();
