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 the sick leave report and writes it to a JSON file
const fetchAndSaveSickLeaveReport = async () => {
  const query = `
    SELECT 
      e.employeeid,
      e.first_name,
      e.last_name,
      e.start_date,
      CASE 
        WHEN TIMESTAMPDIFF(MONTH, e.start_date, CURDATE()) >= 3 THEN 10 
        ELSE 0 
      END AS sick_leave_available,
      (
        COALESCE(m.leave_used, 0) +
        COALESCE(a.leave_used, 0) +
        COALESCE(mt.leave_used, 0)
      ) AS used,
      (
        CASE 
          WHEN TIMESTAMPDIFF(MONTH, e.start_date, CURDATE()) >= 3 THEN 10 
          ELSE 0 
        END -
        (
          COALESCE(m.leave_used, 0) +
          COALESCE(a.leave_used, 0) +
          COALESCE(mt.leave_used, 0)
        )
      ) AS balance
    FROM employeemaster e
    LEFT JOIN (
      SELECT employeeid, SUM(leave_days) AS leave_used
      FROM manual_timesheet
      WHERE leave_type = 'Sick Leave'
        AND id >= 179
      GROUP BY employeeid
    ) m ON m.employeeid = e.employeeid
    LEFT JOIN (
      SELECT employeeid, SUM(days) AS leave_used
      FROM absent
      WHERE type = 'Sick Leave'
        AND id >= 179
      GROUP BY employeeid
    ) a ON a.employeeid = e.employeeid
    LEFT JOIN (
      SELECT employeeid, SUM(leave_days) AS leave_used
      FROM manual_tech_timesheet
      WHERE leave_type = 'Sick Leave'
        AND id >= 179
      GROUP BY employeeid
    ) mt ON mt.employeeid = e.employeeid
    WHERE e.status = 'Active';
  `;

  try {
    const [rows] = await connection.promise().query(query);
    if (rows.length > 0) {
      const jsonData = JSON.stringify(rows, null, 2);
      const reportPath = '/home/erp/www/wormalderp/nodejsapp/sick_leave_report.json';
      fs.writeFileSync(reportPath, jsonData);
      console.log(`Sick leave report saved to ${reportPath}`);
    } else {
      console.log('No sick leave data available.');
    }
  } catch (error) {
    console.error('Error fetching sick leave data:', error);
  } finally {
    connection.end();
  }
};

// Function to run the report and then exit the process
const runSickLeaveReportAndExit = async () => {
  await fetchAndSaveSickLeaveReport();
  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 {
  runSickLeaveReportAndExit();
}
