const db = require('../config/db');

async function getDashboardStats() {
  const todayExpr = db.isSQLite ? "DATE('now')" : 'CURDATE()';
  const monthExpr = db.isSQLite ? "strftime('%Y-%m', 'now')" : "DATE_FORMAT(CURDATE(), '%Y-%m')";
  const invoiceMonthExpr = db.isSQLite ? "strftime('%Y-%m', invoice_date)" : "DATE_FORMAT(invoice_date, '%Y-%m')";
  const [[summary]] = await db.query(`
    SELECT
      COUNT(*) AS total_bills,
      COALESCE(SUM(grand_total), 0) AS total_amount,
      COALESCE(SUM(cgst_amount + sgst_amount + igst_amount), 0) AS total_tax,
      COALESCE(SUM(CASE WHEN invoice_date = ${todayExpr} THEN grand_total ELSE 0 END), 0) AS today_amount,
      COALESCE(SUM(CASE WHEN ${invoiceMonthExpr} = ${monthExpr} THEN grand_total ELSE 0 END), 0) AS month_amount
    FROM invoices
    WHERE status = 'active'
  `);
  const [recent] = await db.query('SELECT id, invoice_number, invoice_date, customer_name, grand_total, status FROM invoices ORDER BY invoice_date DESC, id DESC LIMIT 8');
  return { summary, recent };
}

module.exports = { getDashboardStats };
