const db = require("../db");

exports.statusReport = async (req, res) => {
  try {
    // Filter aus URL
    const year = parseInt(req.query.year) || new Date().getFullYear();

    const quarter = parseInt(req.query.quarter) || 0; // 0 = alle

    // WHERE-Teil dynamisch bauen
    let where = "WHERE 1=1";
    const params = [];

    if (year) {
      where += " AND YEAR(invoice_date) = ?";
      params.push(year);
    }

    if (quarter) {
      where += " AND QUARTER(invoice_date) = ?";
      params.push(quarter);
    }

    // Report-Daten
    const [stats] = await db.promise().query(`
      SELECT
        CONCAT(type, '_', status) AS status,
        SUM(total_amount) AS total

      FROM invoices

      GROUP BY type, status
    `);

    // Verfügbare Jahre
    const [years] = await db.promise().query(`
      SELECT DISTINCT YEAR(invoice_date) AS year
      FROM invoices
      ORDER BY year DESC
    `);

    res.render("reportview", {
      title: "Abrechnungsreport",

      user: req.session.user,
      stats,

      years: years.map((y) => y.year),
      selectedYear: year,
      selectedQuarter: quarter,

      sidebarPartial: "partials/sidebar-invoices",
      active: "reports",
    });
  } catch (err) {
    console.error("❌ Report:", err);
    res.status(500).send("Fehler beim Report");
  }
};