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
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'sooggoo',
  password: '4Compassion*',
  database: 'evergreenint',
});

const fetchAndSaveJSON = async () => {
  const query = `
    WITH AgingData AS (
      SELECT 
        debtorsmaster.debtorno,
        debtorsmaster.name,
        debtortrans.account_number,
        department.departmentname,
        IFNULL(debtortrans.ovamount, 0) + IFNULL(debtortrans.ovgst, 0) + IFNULL(debtortrans.ovfreight, 0) + IFNULL(debtortrans.ovdiscount, 0)
          - IFNULL(debtortrans.alloc, 0) - IFNULL(debtortrans.ptax, 0) - IFNULL(debtortrans.retention, 0) AS balance,
        CASE 
          WHEN debtortrans.trandate IS NOT NULL THEN DATEDIFF(CURDATE(), LAST_DAY(debtortrans.trandate))
          ELSE 0 
        END AS aging_days
      FROM debtorsmaster
      LEFT JOIN debtortrans 
        ON debtorsmaster.debtorno = debtortrans.debtorno 
        AND debtortrans.settled = 0 
        AND debtortrans.cancelled = 0
      LEFT JOIN debtoraccount
        ON debtortrans.account_number = debtoraccount.account_number
      LEFT JOIN department
        ON debtoraccount.departmentid = department.departmentid
    WHERE  debtortrans.trandate > '2020-12-31 00:00:00'
    AND debtortrans.account_number IS NOT NULL 
      AND debtortrans.account_number != ''      
    )
    SELECT 
      JSON_ARRAYAGG(
        JSON_OBJECT(
          'debtorno', debtorno,
          'name', name,
          'account_number', account_number,
          'departmentname', departmentname,
          'total_balance', total_balance,
          'current', current,
          '30days', days_30,
          '60days', days_60,
          '90days', days_90,
          '120days', days_120,
          '150days', days_150,
          '180daysplus', days_180
        )
      ) AS report
    FROM (
      SELECT 
        debtorno,
        account_number,
        departmentname,
        MIN(name) AS name,
        SUM(balance) AS total_balance,
        SUM(CASE WHEN aging_days < 30 THEN balance ELSE 0 END) AS current,
        SUM(CASE WHEN aging_days BETWEEN 30 AND 59 THEN balance ELSE 0 END) AS days_30,
        SUM(CASE WHEN aging_days BETWEEN 60 AND 89 THEN balance ELSE 0 END) AS days_60,
        SUM(CASE WHEN aging_days BETWEEN 90 AND 119 THEN balance ELSE 0 END) AS days_90,
        SUM(CASE WHEN aging_days BETWEEN 120 AND 149 THEN balance ELSE 0 END) AS days_120,
        SUM(CASE WHEN aging_days BETWEEN 150 AND 179 THEN balance ELSE 0 END) AS days_150,
        SUM(CASE WHEN aging_days >= 180 THEN balance ELSE 0 END) AS days_180
      FROM AgingData

      GROUP BY account_number, debtorno, departmentname
    ) AS aggregated_data;
  `;

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

    if (rows.length > 0 && rows[0].report) {
      const jsonData = rows[0].report; // JSON array from MySQL

      // Save JSON to a file
      const reportPath = '/home/erp/www/wormalderp/nodejsapp/aging_report.json';
      fs.writeFileSync(reportPath, jsonData);
      console.log(`Aging report saved to ${reportPath}`);
    } else {
      console.log('No data available.');
    }
  } catch (error) {
    console.error('Error fetching data:', error);
  } finally {
    connection.end();
  }
};

// Function to run the report and then exit the process
const runReportAndExit = async () => {
  await fetchAndSaveJSON();
  process.exit(0);
};

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