const express = require('express');
const mysql = require('mysql2');
const fs = require('fs');

const PORT = 3001;

// Function to start the Express server (only used if you pass --server)
const startServer = () => {
  const app = express();
  app.use(express.json());
  app.listen(PORT, () => {
    console.log(`Server running on http://localhost:${PORT}`);
  });
};

// MySQL connection configuration
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'sooggoo',
  password: '4Compassion*',
  database: 'evergreenint',
});

// Function that fetches labour cost data and writes it to a JSON file
const fetchAndSaveLabourCostReport = async (fromDate, toDate) => {
  // Use the provided parameters, or set defaults if they are falsy.
  const actualFromDate = fromDate || '1';      
  const actualToDate   = toDate   || '9999';   

  const query = `
    SELECT t.labourcode, 
           SUM(t.cost) AS cost, 
           SUM(t.fnpfcost) AS fnpfcost,
           SUM(t.tpafcost) AS tpafcost, 
           SUM(t.totalcost) AS totalcost
    FROM (
      SELECT labourcode, 
             SUM(cost) AS cost, 
             SUM(fnpf) AS fnpfcost, 
             SUM(tpaf) AS tpafcost, 
             SUM(cost_including_fnpf) AS totalcost
      FROM manual_timesheet
      WHERE id BETWEEN ? AND ?
      GROUP BY labourcode
      UNION ALL
      SELECT labourcode, 
             SUM(cost) AS cost, 
             SUM(fnpf) AS fnpfcost, 
             SUM(tpaf) AS tpafcost, 
             SUM(cost_including_fnpf) AS totalcost
      FROM absent
      WHERE id BETWEEN ? AND ?
      GROUP BY labourcode
      UNION ALL
      SELECT labourcode, 
             SUM(cost) AS cost, 
             SUM(fnpf) AS fnpfcost, 
             SUM(tpaf) AS tpafcost, 
             SUM(cost_including_fnpf) AS totalcost
      FROM payrol_adjustments_details
      WHERE id BETWEEN ? AND ?
      GROUP BY labourcode
    ) t
    GROUP BY t.labourcode
  `;
  
  const params = [actualFromDate, actualToDate, actualFromDate, actualToDate, actualFromDate, actualToDate];

  try {
    const [rows] = await connection.promise().query(query, params);

    if (rows.length > 0) {
      const jsonData = JSON.stringify(rows);
      const reportPath = '/home/erp/www/wormalderp/nodejsapp/labour_cost_report.json';
      fs.writeFileSync(reportPath, jsonData);
      console.log(`Labour cost report saved to ${reportPath}`);
    } else {
      console.log('No labour cost data available.');
    }
  } catch (error) {
    console.error('Error fetching labour cost data:', error);
  } finally {
    connection.end();
  }
};

// Function to run the report and then exit the process
const runLabourCostReportAndExit = async () => {
  // You can get the command line args here, for example:
  const fromDate = process.argv[2];
  const toDate = process.argv[3];

  await fetchAndSaveLabourCostReport(fromDate, toDate);
  process.exit(0);
};


// If the '--server' flag is provided, start the server; otherwise, run the report and exit.
if (process.argv.includes('--server')) {
  startServer();
} else {
  runLabourCostReportAndExit();
}
