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

exports.getDashboardStats = async () => {
  const [rows] = await db.query(`
    SELECT 
      (SELECT COUNT(*) FROM items) AS total_items,
      (SELECT SUM(quantity * price) FROM items) AS total_inventory_value,
      (SELECT COUNT(*) FROM customers) AS total_customers,
      (SELECT COUNT(*) FROM suppliers) AS total_suppliers,
      (SELECT COUNT(*) FROM sales) AS total_sales,
      (SELECT COUNT(*) FROM purchases) AS total_purchases,
      (SELECT IFNULL(SUM(total), 0) FROM sales) AS total_sales_amount,
      (SELECT IFNULL(SUM(total), 0) FROM purchases) AS total_purchase_amount,
      (SELECT IFNULL(SUM(amount), 0) FROM expenses) AS total_expenses
  `);
  return rows[0];
};
