api-isakafo/controllers/FinancesController.js

const { pool } = require('../config/databases'); 

// GET /finances
const getDashboard = async (req, res) => {
  try {
    const [payments] = await pool.query(`
      SELECT id, description, payment_date AS date_transaction, 'Paiement' AS type_transaction, 'Recette' AS categorie, amount
      FROM payments
      ORDER BY payment_date DESC
      LIMIT 10
    `);

    const [[{ totalRevenue }]] = await pool.query(`SELECT SUM(amount) AS totalRevenue FROM payments`);

    let expenses = [], totalExpenses = 0, depenses_change = 0;

    const [expensesTable] = await pool.query(`SHOW TABLES LIKE 'expenses'`);
    if (expensesTable.length > 0) {
      [expenses] = await db.query(`
        SELECT id, description, expense_date AS date_transaction, 'Dépense' AS type_transaction, 'Dépense' AS categorie, amount
        FROM expenses
        ORDER BY expense_date DESC
        LIMIT 10
      `);

      const [[{ total }]] = await pool.query(`SELECT SUM(amount) AS total FROM expenses`);
      totalExpenses = total || 0;

      const previousMonthExpenses = 800000;
      if (previousMonthExpenses > 0) {
        depenses_change = ((totalExpenses - previousMonthExpenses) / previousMonthExpenses) * 100;
      }
    }

    const previousMonthRevenue = 2000000;
    const chiffre_affaires_change = (previousMonthRevenue > 0)
      ? ((totalRevenue - previousMonthRevenue) / previousMonthRevenue) * 100
      : 0;

    const benefice_net = totalRevenue - totalExpenses;
    const benefice_net_marge = totalRevenue > 0 ? (benefice_net / totalRevenue) * 100 : 0;
    const tresorerie = benefice_net;

    const dashboard_stats = {
      chiffre_affaires: totalRevenue,
      chiffre_affaires_change,
      depenses: totalExpenses,
      depenses_change,
      benefices: benefice_net,
      benefice_net,
      benefice_net_marge,
      tresorerie,
      tresorerie_solde: tresorerie,
      total_liabilities: 0
    };

    const profitLoss = {
      revenu_total: totalRevenue,
      depense_total: totalExpenses,
      benefice_net,
      marge: benefice_net_marge
    };

    const balanceSheet = {
      total_assets: 0,
      total_liabilities: 0,
      total_equity: 0,
      net_worth: 0
    };

    const transactions = [...payments, ...expenses].sort((a, b) =>
      new Date(b.date_transaction) - new Date(a.date_transaction)
    );

    const [[invoiceTable]] = await pool.query(`SHOW TABLES LIKE 'invoices'`);
    let invoices = [];
    if (invoiceTable) {
      [invoices] = await pool.query(`
        SELECT id, invoice_number, client_name, invoice_date, status, total_amount
        FROM invoices
        ORDER BY invoice_date DESC
        LIMIT 10
      `);
    }

    res.json({
      transactions,
      dashboard_stats,
      reportDate: new Date().toISOString().slice(0, 10),
      profitLoss,
      balanceSheet,
      invoices
    });
  } catch (err) {
    console.error("Erreur dans getDashboard:", err.message);
    res.status(500).json({ error: "Erreur serveur" });
  }
};

// POST /finances/invoices
const generateInvoice = async (req, res) => {
  const {
    invoice_number,
    client_name,
    client_email,
    client_address,
    invoice_date,
    due_date,
    total_amount,
    status = 'Draft',
    notes
  } = req.body;

  try {
    await pool.query(`
      INSERT INTO invoices (invoice_number, client_name, client_email, client_address, invoice_date, due_date, total_amount, status, notes)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
    `, [
      invoice_number.trim(),
      client_name.trim(),
      client_email?.trim() || null,
      client_address.trim(),
      invoice_date || new Date(),
      due_date || null,
      parseFloat(total_amount),
      status.trim(),
      notes?.trim()
    ]);

    res.status(201).json({ message: 'Facture ajoutée avec succès.' });
  } catch (err) {
    console.error("Erreur dans generateInvoice:", err.message);
    res.status(500).json({ error: err.message });
  }
};

// POST /finances/payments
const processPayment = async (req, res) => {
  const { invoice_id, payment_date, amount, payment_method, description } = req.body;
  const conn = await pool.getConnection();

  try {
    await conn.beginTransaction();

    await conn.query(`
      INSERT INTO payments (invoice_id, payment_date, amount, payment_method, description)
      VALUES (?, ?, ?, ?, ?)
    `, [
      invoice_id,
      payment_date || new Date(),
      parseFloat(amount),
      payment_method.trim(),
      description.trim()
    ]);

    const [[{ total_amount }]] = await conn.query(`SELECT total_amount FROM invoices WHERE id = ?`, [invoice_id]);
    const [[{ total_paid }]] = await conn.query(`SELECT SUM(amount) AS total_paid FROM payments WHERE invoice_id = ?`, [invoice_id]);

    let status = 'Partially Paid';
    if (Math.abs(total_amount - total_paid) < 0.01) {
      status = 'Paid';
    }

    await conn.query(`UPDATE invoices SET status = ? WHERE id = ?`, [status, invoice_id]);

    await conn.query(`
      UPDATE balance_sheet_accounts
      SET amount = amount + ?
      WHERE name = 'Trésorerie' AND account_type = 'Asset'
    `, [amount]);

    await conn.commit();
    res.status(200).json({ message: 'Paiement enregistré avec succès.' });

  } catch (err) {
    await conn.rollback();
    console.error("Erreur dans processPayment:", err.message);
    res.status(500).json({ error: err.message });
  } finally {
    conn.release();
  }
};

// GET + POST /finances/taxes
const getTaxes = async (req, res) => {
  const [taxes] = await pool.query(`SELECT * FROM taxes ORDER BY name ASC`);
  res.json(taxes);
};

const createOrUpdateTax = async (req, res) => {
  const { id, name, rate, description, is_active } = req.body;

  if (!name || isNaN(rate)) {
    return res.status(400).json({ error: 'Nom et taux valides requis.' });
  }

  try {
    if (id) {
      await pool.query(`
        UPDATE taxes SET name = ?, rate = ?, description = ?, is_active = ?
        WHERE id = ?
      `, [name.trim(), rate, description?.trim(), is_active ? 1 : 0, id]);
    } else {
      await pool.query(`
        INSERT INTO taxes (name, rate, description, is_active)
        VALUES (?, ?, ?, ?)
      `, [name.trim(), rate, description?.trim(), is_active ? 1 : 0]);
    }

    res.json({ message: 'Taxe enregistrée.' });
  } catch (err) {
    console.error("Erreur dans createOrUpdateTax:", err.message);
    res.status(500).json({ error: err.message });
  }
};

// DELETE /finances/taxes/:id
const disableTax = async (req, res) => {
  const id = parseInt(req.params.id);
  if (!id) return res.status(400).json({ error: "ID invalide" });

  try {
    await pool.query(`UPDATE taxes SET is_active = 0 WHERE id = ?`, [id]);
    res.json({ message: 'Taxe désactivée.' });
  } catch (err) {
    console.error("Erreur dans disableTax:", err.message);
    res.status(500).json({ error: err.message });
  }
};

module.exports = {
  getDashboard,
  generateInvoice,
  processPayment,
  getTaxes,
  createOrUpdateTax,
  disableTax
};