import { getDb } from "../config/db.js";

export const financeReport = async (_req, res) => {
  const db = getDb();
  const [rows] = await db.execute(
    `SELECT 
      t.id AS transaction_id,
      t.type AS service_name,
      t.asset_type,
      n.name AS notary,
      n.region,
      n.district,
      t.client_name AS customer,
      p.paid_at AS payment_date,
      p.reference_number,
      p.phone,
      t.amount AS commodity_value,
      t.tax_amount AS mof_tax,
      (p.total - t.tax_amount) AS notary_fee,
      p.total AS total_payment
    FROM transactions t
    JOIN payments p ON p.transaction_id = t.id
    JOIN notaries n ON n.id = t.notary_id
    WHERE t.status = 'completed'
    ORDER BY p.paid_at DESC`
  );
  res.json(rows);
};

export const dashboardSummary = async (_req, res) => {
  const db = getDb();
  const [[revenueRow]] = await db.execute(
    `SELECT COALESCE(SUM(p.total), 0) AS totalRevenue 
     FROM payments p
     JOIN transactions t ON t.id = p.transaction_id
     WHERE t.status = 'completed'`
  );
  const [[taxRow]] = await db.execute(
    `SELECT COALESCE(SUM(tax_amount), 0) AS totalTax 
     FROM transactions 
     WHERE status = 'completed'`
  );
  const [[todayRow]] = await db.execute(
    `SELECT COUNT(*) AS transactionsToday 
     FROM transactions 
     WHERE DATE(created_at) = CURDATE() AND status = 'completed'`
  );
  const [serviceRows] = await db.execute(
    `SELECT type, COUNT(*) AS total
     FROM transactions
     WHERE status = 'completed'
     GROUP BY type
     ORDER BY total DESC`
  );

  res.json({
    totalRevenue: Number(revenueRow.totalRevenue),
    totalTax: Number(taxRow.totalTax),
    transactionsToday: Number(todayRow.transactionsToday),
    byService: serviceRows.reduce((acc, item) => {
      acc[item.type] = Number(item.total);
      return acc;
    }, {})
  });
};
