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

if (process.argv.length < 4) {
  console.error('Usage: node alllabourcost1.js <fromID> <toID>');
  process.exit(1);
}
let rawFrom = process.argv[2];
let rawTo   = process.argv[3];
const fromID = parseInt(rawFrom.replace(/'/g, ''), 10);
const toID   = parseInt(rawTo.replace(/'/g, ''), 10);
if (isNaN(fromID) || isNaN(toID)) {
  console.error('Invalid from/to IDs:', rawFrom, rawTo);
  process.exit(1);
}

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

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

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

    const payrunRows = await query(`SELECT id, employeeid, totalgross_amount, fnpf_employer FROM payrun_details WHERE id BETWEEN ? AND ?`, [fromID, toID]);
    const payrunMap = {};
    let grandCost = 0;
    let grandFnpf = 0;
    payrunRows.forEach(r => {
      const key = `${r.id}_${r.employeeid}`;
      const gross = parseFloat(r.totalgross_amount) || 0;
      const fnpf = parseFloat(r.fnpf_employer) || 0;
      payrunMap[key] = {
        gross: gross,
        fnpf: fnpf,
        costAccum: 0
      };
      grandCost += gross;
      grandFnpf += fnpf;
    });

    const aggregated = {};
    function addCost(code, desc, cost, fnpf, tpaf, total, hours = 0) {
      if (!aggregated[code]) {
        aggregated[code] = {
          labourcode: code,
          description: desc || '',
          cost: 0,
          fnpfcost: 0,
          tpafcost: 0,
          totalcost: 0,
          hours: 0
        };
      }
      aggregated[code].cost     += cost;
      aggregated[code].fnpfcost += fnpf;
      aggregated[code].tpafcost += tpaf;
      aggregated[code].totalcost += total;
      aggregated[code].hours     += hours;
    }

    async function getEmployeeRate(employeeid) {
      const row = await query(`SELECT base_wage_rate FROM employeemaster WHERE employeeid = ? LIMIT 1`, [employeeid]);
      return row.length > 0 ? parseFloat(row[0].base_wage_rate) || 0 : 0;
    }

    async function processTable(table, fields, costFormula) {
      const rows = await query(`SELECT * FROM ${table} WHERE id BETWEEN ? AND ?`, [fromID, toID]);
      for (const r of rows) {
        const key = `${r.id}_${r.employeeid}`;
        if (!payrunMap[key]) continue;

        const baseRate = await getEmployeeRate(r.employeeid);
        const cost = costFormula(r, baseRate);

        const fnpfAllowed = payrunMap[key].fnpf > 0;
        let fnpf = fnpfAllowed ? cost * 0.10 : 0;
        let tpaf = fnpfAllowed ? cost * 0.01 : 0;
        let total = cost + fnpf;

        payrunMap[key].costAccum += cost;
        addCost(r.labourcode, labourDescs[r.labourcode], cost, fnpf, tpaf, total);
      }
    }

    await processTable('manual_timesheet', ["nt", "timehalf", "dt", "public_holiday_hours", "premium", "leave_hours", "meal", "stay_away"], (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 processTable('manual_tech_timesheet', ["nt", "timehalf", "dt", "public_holiday_hours", "leave_hours", "meal", "call_out_allowances", "stay_away"], (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 processTable('absent', ["gross"], (r) => parseFloat(r.gross) || 0);

    async function processPADetails() {
      const rows = await query(`SELECT * FROM payrol_adjustments_details WHERE id BETWEEN ? AND ?`, [fromID, toID]);
      for (const r of rows) {
        const key = `${r.id}_${r.employeeid}`;
        if (!payrunMap[key]) continue;

        const amt  = parseFloat(r.amount) || 0;
        const prem = parseFloat(r.premium) || 0;
        const meal = parseFloat(r.meal) || 0;
        const base = amt + prem + meal;

        const fnpfAllowed = (['6','16'].includes(r.type_details)) && payrunMap[key].fnpf > 0;
        let fnpf = fnpfAllowed ? base * 0.10 : 0;
        let tpaf = fnpfAllowed ? base * 0.01 : 0;
        let total = base + fnpf;

        payrunMap[key].costAccum += base;
        addCost(r.labourcode, labourDescs[r.labourcode], base, fnpf, tpaf, total);
      }
    }

    await processPADetails();

    const mismatches = [];
    for (const key in payrunMap) {
      const expected = payrunMap[key].gross;
      const actual = payrunMap[key].costAccum;
      if (Math.abs(expected - actual) > 0.01) {
        mismatches.push({ key, expected, actual });
      }
    }

    if (mismatches.length > 0) {
      console.warn('WARNING: Some employee cost totals do not match payrun_details:');
      mismatches.forEach(m => console.warn(`${m.key}: expected ${m.expected}, got ${m.actual}`));
    }

    const outArray = Object.values(aggregated);

    let sumTpaf = 0;
    outArray.forEach(item => {
      sumTpaf += item.tpafcost;
    });

    outArray.push({
      labourcode: 'TOTAL',
      description: '',
      cost:     grandCost,
      fnpfcost: grandFnpf,
      tpafcost: sumTpaf,
      totalcost: grandCost + grandFnpf + sumTpaf,
      hours:    0
    });

    const outputPath = '/home/erp/www/wormalderp/nodejsapp/allcost1.json';
    fs.writeFileSync(outputPath, JSON.stringify(outArray, null, 2), 'utf8');
    console.log('Wrote ' + outArray.length + ' records (including TOTAL) to ' + outputPath);

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