<?php
class ReportController extends Controller
{
    public function sales(): void
    {
        Auth::requirePermission('reports.sales');

        $from    = $this->get('from',  date('Y-m-01'));
        $to      = $this->get('to',    date('Y-m-d'));
        $groupBy = $this->get('group', 'day');
        $fmt     = match($groupBy) {
            'month' => '%Y-%m',
            'week'  => '%Y-%u',
            default => '%Y-%m-%d',
        };

        $p1 = [$from . ' 00:00:00', $to . ' 23:59:59'];

        $rows = Database::fetchAll(
            "SELECT DATE_FORMAT(sale_date,'$fmt') AS period,
                    COUNT(*)                      AS count,
                    COALESCE(SUM(subtotal),0)     AS subtotal,
                    COALESCE(SUM(discount),0)     AS discount,
                    COALESCE(SUM(tax),0)          AS tax,
                    COALESCE(SUM(total),0)        AS total,
                    COALESCE(SUM(paid),0)         AS paid,
                    COALESCE(SUM(balance),0)      AS balance
             FROM sales
             WHERE sale_date BETWEEN ? AND ? AND status = 'completed'
             GROUP BY period ORDER BY period ASC",
            $p1
        );

        $totals = Database::fetchOne(
            "SELECT COUNT(*)                       AS count,
                    COALESCE(SUM(total),0)         AS total,
                    COALESCE(SUM(paid),0)          AS paid,
                    COALESCE(SUM(balance),0)       AS balance,
                    COALESCE(SUM(discount),0)      AS discount,
                    COALESCE(SUM(tax),0)           AS tax
             FROM sales
             WHERE sale_date BETWEEN ? AND ? AND status = 'completed'",
            $p1
        );

        // Payment method breakdown
        $pmtBreakdown = Database::fetchAll(
            "SELECT sp.payment_method,
                    COUNT(DISTINCT sp.sale_id)   AS txn_count,
                    COALESCE(SUM(sp.amount),0)   AS total
             FROM sale_payments sp
             JOIN sales s ON s.id = sp.sale_id
             WHERE s.sale_date BETWEEN ? AND ? AND s.status = 'completed'
             GROUP BY sp.payment_method
             ORDER BY total DESC",
            $p1
        );

        // Top period
        $maxTotal = !empty($rows) ? max(array_column($rows, 'total')) : 0;

        // Days in period
        $days = max(1, (int)((strtotime($to) - strtotime($from)) / 86400) + 1);
        $avgDaily = $days > 0 ? (float)$totals['total'] / $days : 0;
        $avgInvoice = $totals['count'] > 0 ? (float)$totals['total'] / $totals['count'] : 0;

        $pageTitle  = 'Sales Report';
        $breadcrumb = [['label' => 'Reports'], ['label' => 'Sales']];
        ob_start();
        require APP_PATH . '/views/reports/sales.php';
        $content = ob_get_clean();
        require APP_PATH . '/views/layouts/main.php';
    }

    public function purchases(): void
    {
        Auth::requirePermission('reports.purchases');

        $from       = $this->get('from',     date('Y-m-01'));
        $to         = $this->get('to',       date('Y-m-d'));
        $supplierId = (int) $this->get('supplier', 0);
        $paidF      = $this->get('payment',  'all'); // all | paid | partial | due
        $groupBy    = $this->get('group',    'day');
        $fmt        = match($groupBy) { 'month' => '%Y-%m', 'week' => '%Y-%u', default => '%Y-%m-%d' };

        $where  = ['p.purchase_date BETWEEN ? AND ?', "p.status != 'cancelled'"];
        $params = [$from, $to];

        if ($supplierId) { $where[] = 'p.supplier_id = ?'; $params[] = $supplierId; }
        if ($paidF !== 'all') {
            $where[] = match($paidF) {
                'paid'    => "p.payment_status = 'paid'",
                'partial' => "p.payment_status = 'partial'",
                'due'     => "p.payment_status = 'due'",
                default   => '1=1',
            };
        }

        $rows = Database::fetchAll(
            "SELECT p.id, p.invoice_no, p.purchase_date, s.supplier_name, s.id AS supplier_id,
                    p.total, p.paid, p.balance, p.payment_status, p.status, p.discount
             FROM purchases p
             JOIN suppliers s ON s.id = p.supplier_id
             WHERE " . implode(' AND ', $where) . "
             ORDER BY p.purchase_date DESC",
            $params
        );

        $totals = Database::fetchOne(
            "SELECT COALESCE(SUM(p.total),0)    AS total,
                    COALESCE(SUM(p.paid),0)     AS paid,
                    COALESCE(SUM(p.balance),0)  AS balance,
                    COALESCE(SUM(p.discount),0) AS discount,
                    COUNT(*)                    AS count
             FROM purchases p
             WHERE " . implode(' AND ', $where),
            $params
        );

        // Period grouped breakdown
        $periodRows = Database::fetchAll(
            "SELECT DATE_FORMAT(p.purchase_date,'$fmt') AS period,
                    COUNT(*)                            AS count,
                    COALESCE(SUM(p.total),0)            AS total,
                    COALESCE(SUM(p.paid),0)             AS paid,
                    COALESCE(SUM(p.balance),0)          AS balance,
                    COALESCE(SUM(p.discount),0)         AS discount
             FROM purchases p
             WHERE " . implode(' AND ', $where) . "
             GROUP BY period ORDER BY period ASC",
            $params
        );

        // Supplier breakdown
        $bySupplier = Database::fetchAll(
            "SELECT s.supplier_name, s.id AS supplier_id,
                    COUNT(*)                   AS count,
                    COALESCE(SUM(p.total),0)   AS total,
                    COALESCE(SUM(p.paid),0)    AS paid,
                    COALESCE(SUM(p.balance),0) AS balance
             FROM purchases p
             JOIN suppliers s ON s.id = p.supplier_id
             WHERE " . implode(' AND ', $where) . "
             GROUP BY p.supplier_id ORDER BY total DESC",
            $params
        );

        // Payment status counts
        $paidCount    = count(array_filter($rows, fn($r) => $r['payment_status'] === 'paid'));
        $partialCount = count(array_filter($rows, fn($r) => $r['payment_status'] === 'partial'));
        $dueCount     = count(array_filter($rows, fn($r) => $r['payment_status'] === 'due'));

        $maxPeriod = !empty($periodRows) ? max(array_column($periodRows, 'total')) : 1;
        $days      = max(1, (int)((strtotime($to) - strtotime($from)) / 86400) + 1);

        $suppliers = Database::fetchAll(
            "SELECT DISTINCT s.id, s.supplier_name FROM suppliers s
             JOIN purchases p ON p.supplier_id = s.id
             WHERE p.purchase_date BETWEEN ? AND ? AND p.status != 'cancelled'
             ORDER BY s.supplier_name",
            [$from, $to]
        );

        $pageTitle  = 'Purchase Report';
        $breadcrumb = [['label' => 'Reports'], ['label' => 'Purchases']];
        ob_start();
        require APP_PATH . '/views/reports/purchases.php';
        $content = ob_get_clean();
        require APP_PATH . '/views/layouts/main.php';
    }

    public function profitLoss(): void
    {
        Auth::requirePermission('reports.profit_loss');

        $from = $this->get('from', date('Y-m-01'));
        $to   = $this->get('to',   date('Y-m-d'));
        $p1   = [$from . ' 00:00:00', $to . ' 23:59:59'];

        $revenue   = (float) Database::fetchColumn("SELECT COALESCE(SUM(total),0)    FROM sales WHERE sale_date BETWEEN ? AND ? AND status='completed'", $p1);
        $cogs      = (float) Database::fetchColumn("SELECT COALESCE(SUM(si.quantity*si.cost_price),0) FROM sale_items si JOIN sales s ON s.id=si.sale_id WHERE s.sale_date BETWEEN ? AND ? AND s.status='completed'", $p1);
        $discounts = (float) Database::fetchColumn("SELECT COALESCE(SUM(discount),0) FROM sales WHERE sale_date BETWEEN ? AND ? AND status='completed'", $p1);
        $tax       = (float) Database::fetchColumn("SELECT COALESCE(SUM(tax),0) FROM sales WHERE sale_date BETWEEN ? AND ? AND status='completed'", $p1);
        $expenses  = (float) Database::fetchColumn("SELECT COALESCE(SUM(amount),0) FROM expenses WHERE expense_date BETWEEN ? AND ?", [$from, $to]);

        $grossProfit     = $revenue - $cogs - $discounts;
        $netProfit       = $grossProfit - $expenses;
        $grossMarginPct  = $revenue > 0 ? round($grossProfit / $revenue * 100, 1) : 0;
        $netMarginPct    = $revenue > 0 ? round($netProfit   / $revenue * 100, 1) : 0;

        // Category breakdown
        $byCategory = Database::fetchAll(
            "SELECT COALESCE(c.category_name,'Uncategorised') AS category_name,
                    COALESCE(SUM(si.quantity * si.selling_price),0) AS revenue,
                    COALESCE(SUM(si.quantity * si.cost_price),0)    AS cost,
                    COALESCE(SUM(si.quantity * (si.selling_price - si.cost_price)),0) AS profit,
                    SUM(si.quantity) AS qty_sold
             FROM sale_items si
             JOIN sales s     ON s.id  = si.sale_id
             JOIN products p  ON p.id  = si.product_id
             LEFT JOIN categories c ON c.id = p.category_id
             WHERE s.sale_date BETWEEN ? AND ? AND s.status = 'completed'
             GROUP BY p.category_id ORDER BY profit DESC",
            $p1
        );

        // Expense breakdown by category
        $expByCategory = Database::fetchAll(
            "SELECT ec.category_name,
                    COALESCE(SUM(e.amount),0) AS total,
                    COUNT(*) AS count
             FROM expenses e
             JOIN expense_categories ec ON ec.id = e.expense_category_id
             WHERE e.expense_date BETWEEN ? AND ?
             GROUP BY ec.id ORDER BY total DESC",
            [$from, $to]
        );

        // Daily/monthly trend for mini sparkline data
        $trend = Database::fetchAll(
            "SELECT DATE_FORMAT(sale_date,'%Y-%m-%d') AS day,
                    COALESCE(SUM(total),0) AS revenue,
                    COALESCE(SUM(si_cost.cogs),0) AS cogs
             FROM sales s
             LEFT JOIN (
               SELECT si.sale_id, SUM(si.quantity*si.cost_price) AS cogs
               FROM sale_items si GROUP BY si.sale_id
             ) si_cost ON si_cost.sale_id = s.id
             WHERE s.sale_date BETWEEN ? AND ? AND s.status = 'completed'
             GROUP BY day ORDER BY day ASC",
            $p1
        );

        $days = max(1, (int)((strtotime($to) - strtotime($from)) / 86400) + 1);

        $pageTitle  = 'Profit & Loss';
        $breadcrumb = [['label' => 'Reports'], ['label' => 'P&L']];
        ob_start();
        require APP_PATH . '/views/reports/profit_loss.php';
        $content = ob_get_clean();
        require APP_PATH . '/views/layouts/main.php';
    }

    public function stock(): void
    {
        Auth::requirePermission('reports.stock');

        $catId  = (int) $this->get('category', 0);
        $stockF = $this->get('stock', 'all'); // all | low | out | ok

        $where  = ["p.status = 'active'"];
        $params = [];

        if ($catId) { $where[] = 'p.category_id = ?'; $params[] = $catId; }
        if ($stockF === 'low') {
            $where[] = 'p.current_stock > 0 AND p.current_stock <= p.reorder_level';
        } elseif ($stockF === 'out') {
            $where[] = 'p.current_stock <= 0';
        } elseif ($stockF === 'ok') {
            $where[] = 'p.current_stock > p.reorder_level';
        }

        $rows = Database::fetchAll(
            "SELECT p.product_code, p.product_name, p.current_stock,
                    p.reorder_level, p.cost_price, p.selling_price,
                    COALESCE(c.category_name,'Uncategorised') AS category_name,
                    u.short_name AS unit,
                    (p.current_stock * p.cost_price)    AS cost_value,
                    (p.current_stock * p.selling_price) AS retail_value
             FROM products p
             LEFT JOIN categories c ON c.id = p.category_id
             LEFT JOIN units      u ON u.id = p.unit_id
             WHERE " . implode(' AND ', $where) . "
             ORDER BY p.product_name",
            $params
        );

        // Totals
        $total = [
            'cv' => array_sum(array_column($rows, 'cost_value')),
            'rv' => array_sum(array_column($rows, 'retail_value')),
        ];

        // Category breakdown
        $byCategory = Database::fetchAll(
            "SELECT COALESCE(c.category_name,'Uncategorised') AS category_name,
                    COUNT(*) AS product_count,
                    COALESCE(SUM(p.current_stock * p.cost_price),0)    AS cost_value,
                    COALESCE(SUM(p.current_stock * p.selling_price),0) AS retail_value,
                    SUM(CASE WHEN p.current_stock <= 0 THEN 1 ELSE 0 END) AS out_count,
                    SUM(CASE WHEN p.current_stock > 0 AND p.current_stock <= p.reorder_level THEN 1 ELSE 0 END) AS low_count
             FROM products p
             LEFT JOIN categories c ON c.id = p.category_id
             WHERE p.status = 'active'
             GROUP BY p.category_id ORDER BY cost_value DESC"
        );

        // Alert counts (all active products)
        $lowCount = (int) Database::fetchColumn("SELECT COUNT(*) FROM products WHERE status='active' AND current_stock > 0 AND current_stock <= reorder_level");
        $outCount = (int) Database::fetchColumn("SELECT COUNT(*) FROM products WHERE status='active' AND current_stock <= 0");

        $categories = Database::fetchAll("SELECT id, category_name FROM categories WHERE status='active' ORDER BY category_name");

        $pageTitle  = 'Stock Value Report';
        $breadcrumb = [['label' => 'Reports'], ['label' => 'Stock']];
        ob_start();
        require APP_PATH . '/views/reports/stock.php';
        $content = ob_get_clean();
        require APP_PATH . '/views/layouts/main.php';
    }

    public function customers(): void
    {
        Auth::requirePermission('reports.customers');

        $search = trim($this->get('search', ''));

        $where  = ["c.status = 'active'", 'c.current_balance > 0'];
        $params = [];
        if ($search) {
            $where[]  = '(c.customer_name LIKE ? OR c.phone LIKE ?)';
            $params[] = "%$search%";
            $params[] = "%$search%";
        }

        $rows = Database::fetchAll(
            "SELECT c.*,
                    COUNT(s.id)              AS open_invoices,
                    MAX(s.sale_date)         AS last_sale,
                    COALESCE(SUM(s.balance),0) AS invoice_balance
             FROM customers c
             LEFT JOIN sales s ON s.customer_id = c.id
                               AND s.status = 'completed'
                               AND s.balance > 0
             WHERE " . implode(' AND ', $where) . "
             GROUP BY c.id
             ORDER BY c.current_balance DESC",
            $params
        );

        $total          = (float) Database::fetchColumn("SELECT COALESCE(SUM(current_balance),0) FROM customers WHERE status='active' AND current_balance > 0");
        $totalCreditLim = (float) Database::fetchColumn("SELECT COALESCE(SUM(credit_limit),0) FROM customers WHERE status='active' AND credit_limit > 0");
        $overLimitCount = (int)   Database::fetchColumn("SELECT COUNT(*) FROM customers WHERE status='active' AND credit_limit > 0 AND current_balance >= credit_limit");

        $pageTitle  = 'Customer Due Report';
        $breadcrumb = [['label' => 'Reports'], ['label' => 'Customer Due']];
        ob_start();
        require APP_PATH . '/views/reports/customers.php';
        $content = ob_get_clean();
        require APP_PATH . '/views/layouts/main.php';
    }

    public function suppliers(): void
    {
        Auth::requirePermission('reports.suppliers');

        $search = trim($this->get('search', ''));

        $where  = ["s.status = 'active'", 's.current_balance > 0'];
        $params = [];
        if ($search) {
            $where[]  = '(s.supplier_name LIKE ? OR s.phone LIKE ?)';
            $params[] = "%$search%";
            $params[] = "%$search%";
        }

        $rows = Database::fetchAll(
            "SELECT s.*,
                    COUNT(p.id)               AS open_invoices,
                    MAX(p.purchase_date)      AS last_purchase,
                    COALESCE(SUM(p.balance),0) AS invoice_balance
             FROM suppliers s
             LEFT JOIN purchases p ON p.supplier_id = s.id
                                  AND p.status = 'received'
                                  AND p.balance > 0
             WHERE " . implode(' AND ', $where) . "
             GROUP BY s.id
             ORDER BY s.current_balance DESC",
            $params
        );

        $total           = (float) Database::fetchColumn("SELECT COALESCE(SUM(current_balance),0) FROM suppliers WHERE status='active' AND current_balance > 0");
        $pendingCheques  = (float) Database::fetchColumn("SELECT COALESCE(SUM(amount),0) FROM cheque_tracking WHERE status='pending' AND transaction_type='purchase'");
        $chequesCount    = (int)   Database::fetchColumn("SELECT COUNT(*) FROM cheque_tracking WHERE status='pending' AND transaction_type='purchase'");

        $pageTitle  = 'Supplier Due Report';
        $breadcrumb = [['label' => 'Reports'], ['label' => 'Supplier Due']];
        ob_start();
        require APP_PATH . '/views/reports/suppliers.php';
        $content = ob_get_clean();
        require APP_PATH . '/views/layouts/main.php';
    }

    public function daily(): void
    {
        @ini_set('memory_limit', '256M');
        @ini_set('max_execution_time', '60');
        @set_time_limit(60);

        Auth::requirePermission('reports.view');

        $date   = $this->get('date', date('Y-m-d'));
        $d1     = $date . ' 00:00:00';
        $d2     = $date . ' 23:59:59';
        $prev   = date('Y-m-d', strtotime($date . ' -1 day'));
        $p1     = [$d1, $d2];

        // ── Sales ────────────────────────────────────────────
        $salesSummary = Database::fetchOne(
            "SELECT COUNT(*)                              AS count,
                    COALESCE(SUM(total),0)                AS total,
                    COALESCE(SUM(paid),0)                 AS paid,
                    COALESCE(SUM(balance),0)              AS balance,
                    COALESCE(SUM(discount),0)             AS discount,
                    COALESCE(SUM(tax),0)                  AS tax,
                    COUNT(CASE WHEN payment_status='paid'    THEN 1 END) AS paid_count,
                    COUNT(CASE WHEN payment_status='partial' THEN 1 END) AS partial_count,
                    COUNT(CASE WHEN payment_status='due'     THEN 1 END) AS due_count
             FROM sales
             WHERE sale_date BETWEEN ? AND ? AND status='completed'", $p1
        );

        $sales = Database::fetchAll(
            "SELECT s.id, s.invoice_no, s.sale_date, s.total, s.paid,
                    s.balance, s.payment_status,
                    COALESCE(c.customer_name,'Walk-in') AS customer_name,
                    u.name AS cashier_name,
                    GROUP_CONCAT(DISTINCT sp.payment_method SEPARATOR ', ') AS methods
             FROM sales s
             LEFT JOIN customers c ON c.id = s.customer_id
             LEFT JOIN users u     ON u.id = s.created_by
             LEFT JOIN sale_payments sp ON sp.sale_id = s.id
             WHERE s.sale_date BETWEEN ? AND ? AND s.status='completed'
             GROUP BY s.id
             ORDER BY s.sale_date DESC", $p1
        );

        // ── Payment breakdown ────────────────────────────────
        $payments = Database::fetchAll(
            "SELECT sp.payment_method AS method,
                    COUNT(DISTINCT sp.sale_id) AS txn_count,
                    SUM(sp.amount) AS amount
             FROM sale_payments sp
             JOIN sales s ON s.id = sp.sale_id
             WHERE s.sale_date BETWEEN ? AND ? AND s.status='completed'
             GROUP BY sp.payment_method ORDER BY amount DESC", $p1
        );

        // ── COGS & Gross Profit ──────────────────────────────
        $cogs = (float) Database::fetchColumn(
            "SELECT COALESCE(SUM(si.quantity*si.cost_price),0)
             FROM sale_items si JOIN sales s ON s.id=si.sale_id
             WHERE s.sale_date BETWEEN ? AND ? AND s.status='completed'", $p1
        );
        $grossProfit = (float)$salesSummary['total'] - (float)$salesSummary['discount'] - $cogs;

        // ── Purchases ────────────────────────────────────────
        $purchaseSummary = Database::fetchOne(
            "SELECT COUNT(*) AS count, COALESCE(SUM(total),0) AS total,
                    COALESCE(SUM(paid),0) AS paid, COALESCE(SUM(balance),0) AS balance
             FROM purchases WHERE purchase_date = ? AND status != 'cancelled'", [$date]
        );

        $purchases = Database::fetchAll(
            "SELECT p.invoice_no, p.purchase_date, p.total, p.paid,
                    p.balance, p.payment_status, s.supplier_name
             FROM purchases p JOIN suppliers s ON s.id=p.supplier_id
             WHERE p.purchase_date = ? AND p.status != 'cancelled'
             ORDER BY p.id DESC", [$date]
        );

        // ── Expenses ─────────────────────────────────────────
        $expenseSummary = Database::fetchOne(
            "SELECT COUNT(*) AS count, COALESCE(SUM(amount),0) AS total
             FROM expenses WHERE expense_date = ?", [$date]
        );

        $expenses = Database::fetchAll(
            "SELECT e.title, ec.category_name, e.amount, u.name AS recorded_by
             FROM expenses e
             JOIN expense_categories ec ON ec.id=e.expense_category_id
             LEFT JOIN users u ON u.id=e.created_by
             WHERE e.expense_date = ? ORDER BY e.amount DESC", [$date]
        );

        // ── Top 10 products ──────────────────────────────────
        $topProducts = Database::fetchAll(
            "SELECT p.product_name, p.product_code,
                    SUM(si.quantity) AS qty_sold,
                    SUM(si.total)    AS revenue,
                    SUM(si.quantity*(si.selling_price-si.cost_price)) AS profit
             FROM sale_items si
             JOIN sales s ON s.id=si.sale_id
             JOIN products p ON p.id=si.product_id
             WHERE s.sale_date BETWEEN ? AND ? AND s.status='completed'
             GROUP BY p.id ORDER BY revenue DESC LIMIT 10", $p1
        );

        // ── Cashier performance ──────────────────────────────
        $cashiers = Database::fetchAll(
            "SELECT u.name AS cashier_name,
                    COUNT(DISTINCT s.id) AS bills,
                    SUM(s.total) AS total,
                    SUM(s.paid)  AS paid
             FROM users u JOIN sales s ON s.created_by=u.id
             WHERE s.sale_date BETWEEN ? AND ? AND s.status='completed'
             GROUP BY u.id ORDER BY total DESC", $p1
        );

        // ── Net profit ───────────────────────────────────────
        $netProfit = $grossProfit - (float)$expenseSummary['total'];

        // ── Previous day comparison ──────────────────────────
        $prevTotal = (float) Database::fetchColumn(
            "SELECT COALESCE(SUM(total),0) FROM sales
             WHERE sale_date BETWEEN ? AND ? AND status='completed'",
            [$prev.' 00:00:00', $prev.' 23:59:59']
        );
        $dayChange = $prevTotal > 0
            ? round(((float)$salesSummary['total'] - $prevTotal) / $prevTotal * 100, 1)
            : null;

        // ── Payroll — Attendance for this date ───────────────
        $payrollShowable = !in_array(Auth::role(),['cashier','stock_keeper'])
                        && Auth::can('payroll.view');
        $attSummary = [];
        $attDetail  = [];
        $payrollPeriodInfo = null;

        if ($payrollShowable) {
            try {
                $attSummary = Database::fetchOne(
                    "SELECT
                       COUNT(*)                                              AS total_marked,
                       COUNT(CASE WHEN status='present'  THEN 1 END)        AS present,
                       COUNT(CASE WHEN status='absent'   THEN 1 END)        AS absent,
                       COUNT(CASE WHEN status='late'     THEN 1 END)        AS late,
                       COUNT(CASE WHEN status='half_day' THEN 1 END)        AS half_day,
                       COUNT(CASE WHEN status='leave'    THEN 1 END)        AS leave_days,
                       COUNT(CASE WHEN method='fingerprint' THEN 1 END)     AS fp_count,
                       COALESCE(SUM(ot_hours),0)                            AS total_ot,
                       COALESCE(SUM(worked_hours),0)                        AS total_worked
                     FROM employee_attendance WHERE att_date = ?", [$date]
                );

                $totalEmp = (int) Database::fetchColumn(
                    "SELECT COUNT(*) FROM employees WHERE status='active'"
                );
                $attSummary['total_employees'] = $totalEmp;

                $attDetail = Database::fetchAll(
                    "SELECT e.emp_code, e.full_name, e.designation, e.department,
                            e.fingerprint_id, ea.check_in, ea.check_out,
                            ea.worked_hours, ea.ot_hours, ea.status, ea.method
                     FROM employee_attendance ea
                     JOIN employees e ON e.id = ea.employee_id
                     WHERE ea.att_date = ? AND ea.status != 'off'
                     ORDER BY ea.status, e.full_name", [$date]
                );

                // Any payroll period for this month
                $payrollPeriodInfo = Database::fetchOne(
                    "SELECT pp.period_name, pp.status,
                            COUNT(pr.id) AS emp_processed,
                            COALESCE(SUM(pr.net_salary),0) AS total_net,
                            COALESCE(SUM(pr.gross_salary),0) AS total_gross,
                            COALESCE(SUM(pr.epf_employee+pr.epf_employer+pr.etf),0) AS total_statutory
                     FROM payroll_periods pp
                     LEFT JOIN payroll_records pr ON pr.period_id=pp.id
                     WHERE pp.month=MONTH(?) AND pp.year=YEAR(?)
                     GROUP BY pp.id", [$date, $date]
                );
            } catch (\Exception $e) {
                $attSummary = [];
                $attDetail  = [];
            }
        }

        $pageTitle  = 'Daily Report — ' . date('d M Y', strtotime($date));
        $breadcrumb = [['label' => 'Reports'], ['label' => 'Daily Report']];
        ob_start();
        require APP_PATH . '/views/reports/daily.php';
        $content = ob_get_clean();
        require APP_PATH . '/views/layouts/main.php';
    }

    // ── Daily Attendance Report ──────────────────────────────
    public function dailyAttendance(): void
    {
        Auth::requirePermission('payroll.view');

        $date = $this->get('date', date('Y-m-d'));

        // ── Attendance summary ────────────────────────────────
        try {
            $attSummary = Database::fetchOne(
                "SELECT
                   COUNT(CASE WHEN status='present'  THEN 1 END) AS present,
                   COUNT(CASE WHEN status='absent'   THEN 1 END) AS absent,
                   COUNT(CASE WHEN status='late'     THEN 1 END) AS late,
                   COUNT(CASE WHEN status='half_day' THEN 1 END) AS half_day,
                   COUNT(CASE WHEN status='leave'    THEN 1 END) AS leave_days,
                   COUNT(CASE WHEN method='fingerprint' THEN 1 END) AS fp_count,
                   COUNT(*) AS total_marked,
                   COALESCE(SUM(ot_hours),0)     AS total_ot,
                   COALESCE(SUM(worked_hours),0) AS total_worked
                 FROM employee_attendance WHERE att_date = ?", [$date]
            );
            $attSummary = $attSummary ?: [];
            $attSummary['total_employees'] = (int)Database::fetchColumn(
                "SELECT COUNT(*) FROM employees WHERE status='active'"
            );

            // ── Detail list ───────────────────────────────────
            $attDetail = Database::fetchAll(
                "SELECT e.emp_code, e.full_name, e.designation,
                        e.department, e.phone, e.fingerprint_id,
                        ea.check_in, ea.check_out, ea.worked_hours,
                        ea.ot_hours, ea.status, ea.method
                 FROM employee_attendance ea
                 JOIN employees e ON e.id = ea.employee_id
                 WHERE ea.att_date = ? AND ea.status != 'off'
                 ORDER BY FIELD(ea.status,'present','late','half_day','leave','absent'), e.full_name",
                [$date]
            );

            // ── Absent / Not Marked ───────────────────────────
            $absentList = Database::fetchAll(
                "SELECT e.emp_code, e.full_name, e.designation, e.department, e.phone
                 FROM employees e
                 WHERE e.status='active'
                   AND e.id NOT IN (
                     SELECT employee_id FROM employee_attendance WHERE att_date=?
                   )
                 ORDER BY e.department, e.full_name", [$date]
            );

            // ── Department summary ────────────────────────────
            $deptSummary = Database::fetchAll(
                "SELECT e.department,
                        COUNT(*) AS total,
                        COUNT(CASE WHEN ea.status IN ('present','late','half_day') THEN 1 END) AS present
                 FROM employees e
                 LEFT JOIN employee_attendance ea
                   ON ea.employee_id=e.id AND ea.att_date=?
                 WHERE e.status='active'
                 GROUP BY e.department ORDER BY e.department", [$date]
            );

            // ── Method breakdown ──────────────────────────────
            $methodBreakdown = Database::fetchAll(
                "SELECT COALESCE(method,'manual') AS method, COUNT(*) AS cnt
                 FROM employee_attendance
                 WHERE att_date=? AND status != 'off'
                 GROUP BY method ORDER BY cnt DESC", [$date]
            );
            $methodBreakdown = array_map(function($r){
                $labels=['fingerprint'=>'Fingerprint','manual'=>'Manual',
                          'qr'=>'QR Code','card'=>'Card','face'=>'Face ID'];
                $r['method'] = $labels[$r['method']] ?? ucfirst($r['method']);
                return $r;
            }, $methodBreakdown);

        } catch (\Exception $e) {
            $attSummary      = ['present'=>0,'absent'=>0,'late'=>0,'half_day'=>0,
                                'leave_days'=>0,'fp_count'=>0,'total_marked'=>0,
                                'total_ot'=>0,'total_worked'=>0,'total_employees'=>0];
            $attDetail       = [];
            $absentList      = [];
            $deptSummary     = [];
            $methodBreakdown = [];
        }

        $pageTitle  = 'Daily Attendance — '.date('d F Y', strtotime($date));
        $breadcrumb = [
            ['label'=>'Reports'],
            ['label'=>'Daily Attendance'],
        ];
        ob_start(); require APP_PATH.'/views/reports/daily_attendance.php';
        $content = ob_get_clean();
        require APP_PATH.'/views/layouts/main.php';
    }

    public function tax(): void
    {
        Auth::requirePermission('reports.tax');

        $from    = $this->get('from',  date('Y-m-01'));
        $to      = $this->get('to',    date('Y-m-d'));
        $groupBy = $this->get('group', 'day'); // day | week | month
        $p       = [$from . ' 00:00:00', $to . ' 23:59:59'];

        $fmt = match($groupBy) {
            'month' => '%Y-%m',
            'week'  => '%Y-%u',
            default => '%Y-%m-%d',
        };

        // Period breakdown
        $rows = Database::fetchAll(
            "SELECT DATE_FORMAT(sale_date,'$fmt') AS period,
                    COALESCE(SUM(tax),0)           AS tax_collected,
                    COALESCE(SUM(total),0)         AS revenue,
                    COALESCE(SUM(discount),0)      AS discount,
                    COUNT(*)                       AS invoices
             FROM sales
             WHERE sale_date BETWEEN ? AND ? AND status = 'completed'
             GROUP BY period ORDER BY period ASC",
            $p
        );

        // Totals
        $totalTax      = (float) array_sum(array_column($rows, 'tax_collected'));
        $totalRevenue  = (float) array_sum(array_column($rows, 'revenue'));
        $totalDiscount = (float) array_sum(array_column($rows, 'discount'));
        $totalInvoices = (int)   array_sum(array_column($rows, 'invoices'));

        // Tax by product category
        $byCategory = Database::fetchAll(
            "SELECT COALESCE(c.category_name,'Uncategorised') AS category_name,
                    COALESCE(SUM(si.tax),0) AS tax_collected,
                    COALESCE(SUM(si.total),0) AS revenue,
                    COUNT(DISTINCT s.id) AS invoice_count
             FROM sale_items si
             JOIN sales s ON s.id = si.sale_id
             JOIN products p ON p.id = si.product_id
             LEFT JOIN categories c ON c.id = p.category_id
             WHERE s.sale_date BETWEEN ? AND ? AND s.status = 'completed'
               AND si.tax > 0
             GROUP BY p.category_id ORDER BY tax_collected DESC",
            $p
        );

        // Tax by tax rate
        $byRate = Database::fetchAll(
            "SELECT p.tax_percent,
                    COALESCE(SUM(si.tax),0) AS tax_collected,
                    COALESCE(SUM(si.total),0) AS revenue,
                    COUNT(*) AS line_count
             FROM sale_items si
             JOIN sales s ON s.id = si.sale_id
             JOIN products p ON p.id = si.product_id
             WHERE s.sale_date BETWEEN ? AND ? AND s.status = 'completed'
               AND si.tax > 0
             GROUP BY p.tax_percent ORDER BY p.tax_percent ASC",
            $p
        );

        $days    = max(1, (int)((strtotime($to) - strtotime($from)) / 86400) + 1);
        $taxName = setting('tax_name', 'Tax');
        $maxTax  = !empty($rows) ? max(array_column($rows, 'tax_collected')) : 1;

        $pageTitle  = 'Tax Report';
        $breadcrumb = [['label' => 'Reports'], ['label' => 'Tax']];
        ob_start();
        require APP_PATH . '/views/reports/tax.php';
        $content = ob_get_clean();
        require APP_PATH . '/views/layouts/main.php';
    }

    public function cashier(): void
    {
        Auth::requirePermission('reports.cashier');

        $from      = $this->get('from',    date('Y-m-01'));
        $to        = $this->get('to',      date('Y-m-d'));
        $cashierId = (int) $this->get('cashier', 0);
        $p         = [$from . ' 00:00:00', $to . ' 23:59:59'];

        $where  = ['s.sale_date BETWEEN ? AND ?', "s.status = 'completed'"];
        $params = $p;
        if ($cashierId) {
            $where[]  = 's.created_by = ?';
            $params[] = $cashierId;
        }

        // Overall summary per cashier
        // IMPORTANT: Do NOT join sale_items directly here — it causes sale-level columns
        // (total, paid, balance, discount) to be multiplied by the item count per sale.
        // Instead, calculate gross_profit in a subquery first, then join.
        $rows = Database::fetchAll(
            "SELECT u.id AS cashier_id, u.name,
                    COUNT(DISTINCT s.id)                                                              AS bills,
                    COALESCE(SUM(s.total),0)                                                         AS total,
                    COALESCE(SUM(s.paid),0)                                                          AS paid,
                    COALESCE(SUM(s.balance),0)                                                       AS balance,
                    COALESCE(SUM(s.discount),0)                                                      AS discount,
                    COALESCE(SUM(gp.sale_profit),0)                                                  AS gross_profit,
                    COUNT(DISTINCT CASE WHEN s.payment_status != 'paid' THEN s.id END)               AS due_count,
                    MAX(s.sale_date)                                                                  AS last_sale
             FROM sales s
             JOIN users u ON u.id = s.created_by
             LEFT JOIN (
                 SELECT sale_id,
                        SUM(quantity * (selling_price - cost_price)) AS sale_profit
                 FROM sale_items
                 GROUP BY sale_id
             ) gp ON gp.sale_id = s.id
             WHERE " . implode(' AND ', $where) . "
             GROUP BY s.created_by ORDER BY total DESC",
            $params
        );

        // Payment method breakdown per cashier
        $pmtRows = Database::fetchAll(
            "SELECT s.created_by, sp.payment_method,
                    COALESCE(SUM(sp.amount),0) AS total
             FROM sale_payments sp
             JOIN sales s ON s.id = sp.sale_id
             WHERE s.sale_date BETWEEN ? AND ? AND s.status = 'completed'
             GROUP BY s.created_by, sp.payment_method",
            $p
        );

        // Index pmt by cashier
        $pmtByCashier = [];
        foreach ($pmtRows as $pm) {
            $pmtByCashier[$pm['created_by']][$pm['payment_method']] = (float)$pm['total'];
        }

        // Top cashier for badges
        $maxTotal = !empty($rows) ? max(array_column($rows, 'total')) : 0;

        // Cashier list for filter
        $cashiers = Database::fetchAll(
            "SELECT DISTINCT u.id, u.name FROM users u
             JOIN sales s ON s.created_by = u.id
             WHERE s.sale_date BETWEEN ? AND ? AND s.status = 'completed'
             ORDER BY u.name",
            $p
        );

        $days = max(1, (int)((strtotime($to) - strtotime($from)) / 86400) + 1);

        $grandTotal  = (float) array_sum(array_column($rows, 'total'));
        $grandPaid   = (float) array_sum(array_column($rows, 'paid'));
        $grandBills  = (int)   array_sum(array_column($rows, 'bills'));
        $grandProfit = (float) array_sum(array_column($rows, 'gross_profit'));
        $grandBal    = (float) array_sum(array_column($rows, 'balance'));

        $pageTitle  = 'Cashier Report';
        $breadcrumb = [['label' => 'Reports'], ['label' => 'Cashier']];
        ob_start();
        require APP_PATH . '/views/reports/cashier.php';
        $content = ob_get_clean();
        require APP_PATH . '/views/layouts/main.php';
    }

    public function cashierDetail(string $id): void
    {
        Auth::requirePermission('reports.view');

        $cashierId = (int)$id;
        $from      = $this->get('from', date('Y-m-01'));
        $to        = $this->get('to',   date('Y-m-d'));
        $pmtFilter = $this->get('pmt',  'all');
        $p         = [$from . ' 00:00:00', $to . ' 23:59:59'];

        // Cashier info
        $cashier = Database::fetchOne(
            "SELECT id, name, username, email, phone, last_login FROM users WHERE id = ?",
            [$cashierId]
        );
        if (!$cashier) {
            redirectWith(APP_URL . '/reports/cashier', 'error', 'Cashier not found.');
            return;
        }

        // Overall summary for this cashier
        // Use LEFT JOIN subquery for profit to avoid multiplying sale totals by item count
        $summary = Database::fetchOne(
            "SELECT COUNT(DISTINCT s.id)                                              AS bills,
                    COALESCE(SUM(s.total),0)                                          AS total,
                    COALESCE(SUM(s.paid),0)                                           AS paid,
                    COALESCE(SUM(s.balance),0)                                        AS balance,
                    COALESCE(SUM(s.discount),0)                                       AS discount,
                    COALESCE(SUM(s.tax),0)                                            AS tax,
                    COALESCE(SUM(gp.sale_profit),0)                                   AS gross_profit,
                    COUNT(DISTINCT CASE WHEN s.payment_status='paid'    THEN s.id END) AS paid_count,
                    COUNT(DISTINCT CASE WHEN s.payment_status='partial' THEN s.id END) AS partial_count,
                    COUNT(DISTINCT CASE WHEN s.payment_status='due'     THEN s.id END) AS due_count,
                    COUNT(DISTINCT CASE WHEN s.status='returned'        THEN s.id END) AS return_count
             FROM sales s
             LEFT JOIN (
                 SELECT sale_id,
                        SUM(quantity * (selling_price - cost_price)) AS sale_profit
                 FROM sale_items
                 GROUP BY sale_id
             ) gp ON gp.sale_id = s.id
             WHERE s.created_by = ? AND s.sale_date BETWEEN ? AND ?
               AND s.status IN ('completed','returned')",
            [$cashierId, $p[0], $p[1]]
        );

        // Daily breakdown
        $daily = Database::fetchAll(
            "SELECT DATE(sale_date) AS day,
                    COUNT(*) AS bills,
                    SUM(total) AS total,
                    SUM(paid)  AS paid,
                    SUM(balance) AS balance
             FROM sales
             WHERE created_by = ? AND sale_date BETWEEN ? AND ?
               AND status = 'completed'
             GROUP BY day ORDER BY day ASC",
            [$cashierId, $p[0], $p[1]]
        );

        // Payment method totals
        $pmtTotals = Database::fetchAll(
            "SELECT sp.payment_method, SUM(sp.amount) AS total, COUNT(*) AS count
             FROM sale_payments sp
             JOIN sales s ON s.id = sp.sale_id
             WHERE s.created_by = ? AND s.sale_date BETWEEN ? AND ?
               AND s.status = 'completed'
             GROUP BY sp.payment_method ORDER BY total DESC",
            [$cashierId, $p[0], $p[1]]
        );

        // All invoices with optional payment filter
        $iWhere  = ['s.created_by = ?', 's.sale_date BETWEEN ? AND ?', "s.status = 'completed'"];
        $iParams = [$cashierId, $p[0], $p[1]];
        if ($pmtFilter !== 'all') {
            $iWhere[]  = 's.payment_status = ?';
            $iParams[] = $pmtFilter;
        }

        $invoices = Database::fetchAll(
            "SELECT s.id, s.invoice_no, s.sale_date, s.total, s.paid,
                    s.balance, s.discount, s.payment_status,
                    COALESCE(c.customer_name,'Walk-in') AS customer_name,
                    GROUP_CONCAT(DISTINCT sp.payment_method ORDER BY sp.id SEPARATOR ', ') AS pmt_methods
             FROM sales s
             LEFT JOIN customers c    ON c.id  = s.customer_id
             LEFT JOIN sale_payments sp ON sp.sale_id = s.id
             WHERE " . implode(' AND ', $iWhere) . "
             GROUP BY s.id
             ORDER BY s.sale_date DESC",
            $iParams
        );

        $days = max(1, (int)((strtotime($to) - strtotime($from)) / 86400) + 1);
        $margin = ($summary['total'] > 0)
            ? round($summary['gross_profit'] / $summary['total'] * 100, 1) : 0;
        $maxDay = !empty($daily) ? max(array_column($daily, 'total')) : 1;

        $pageTitle  = 'Cashier: ' . $cashier['name'];
        $breadcrumb = [
            ['label' => 'Reports'],
            ['label' => 'Cashier', 'url' => APP_URL . '/reports/cashier?from=' . $from . '&to=' . $to],
            ['label' => $cashier['name']],
        ];
        ob_start();
        require APP_PATH . '/views/reports/cashier_detail.php';
        $content = ob_get_clean();
        require APP_PATH . '/views/layouts/main.php';
    }

    public function creditCustomers(): void
    {
        Auth::requirePermission('reports.view');

        $from   = $this->get('from',     date('Y-m-01'));
        $to     = $this->get('to',       date('Y-m-d'));
        $status = $this->get('status',   'all');   // all | overdue | active | cleared
        $custId = (int) $this->get('customer', 0);

        // Only sales that have a credit payment — these are the Credit Book entries
        $where  = ["s.sale_date BETWEEN ? AND ?", "s.status = 'completed'",
                   "EXISTS (SELECT 1 FROM sale_payments sp WHERE sp.sale_id = s.id AND sp.payment_method = 'credit')"];
        $params = [$from . ' 00:00:00', $to . ' 23:59:59'];

        if ($custId) { $where[] = 's.customer_id = ?'; $params[] = $custId; }

        $salesRows = Database::fetchAll(
            "SELECT s.id AS sale_id, s.invoice_no, s.sale_date,
                    s.total AS sale_total, s.paid AS sale_paid,
                    s.balance AS sale_balance, s.payment_status, s.note AS sale_note,
                    COALESCE(c.customer_name, 'Walk-in') AS customer_name,
                    c.id AS customer_id, c.phone AS customer_phone,
                    c.credit_limit, c.current_balance AS customer_balance,
                    u.name AS cashier_name
             FROM sales s
             LEFT JOIN customers c ON c.id = s.customer_id
             LEFT JOIN users     u ON u.id = s.created_by
             WHERE " . implode(' AND ', $where) . "
             ORDER BY s.sale_date DESC",
            $params
        );

        // Parse credit payment reference for each sale
        $today = date('Y-m-d');
        foreach ($salesRows as &$row) {
            $row['due_date']      = null;
            $row['credit_amount'] = 0.0;
            $row['is_overdue']    = false;
            $row['days_overdue']  = 0;
            $row['days_remaining']= 0;

            $creditPmt = Database::fetchOne(
                "SELECT amount, reference FROM sale_payments
                 WHERE sale_id = ? AND payment_method = 'credit'
                 ORDER BY id ASC LIMIT 1",
                [$row['sale_id']]
            );

            if ($creditPmt) {
                $row['credit_amount'] = (float)$creditPmt['amount'];
                $ref   = $creditPmt['reference'] ?? '';
                // Parse both old format (Name:/Tel:/Due:) and new format (Customer:/Due:)
                foreach (array_map('trim', explode('|', $ref)) as $part) {
                    if (str_starts_with($part, 'Due:'))      $row['due_date']  = trim(substr($part, 4));
                    if (str_starts_with($part, 'Customer:')) $row['due_date']  = $row['due_date']; // already set
                }
                // Re-parse due date specifically
                foreach (array_map('trim', explode('|', $ref)) as $part) {
                    if (str_starts_with($part, 'Due:')) {
                        $row['due_date'] = trim(substr($part, 4));
                        break;
                    }
                }
            }

            if ($row['due_date'] && $row['sale_balance'] > 0) {
                $diff = (int)round((strtotime($today) - strtotime($row['due_date'])) / 86400);
                if ($diff > 0) {
                    $row['is_overdue']   = true;
                    $row['days_overdue'] = $diff;
                } else {
                    $row['days_remaining'] = abs($diff);
                }
            }
        }
        unset($row);

        // Status filter
        if ($status === 'overdue') {
            $salesRows = array_values(array_filter($salesRows, fn($r) => $r['is_overdue']));
        } elseif ($status === 'active') {
            $salesRows = array_values(array_filter($salesRows, fn($r) => !$r['is_overdue'] && $r['sale_balance'] > 0));
        } elseif ($status === 'cleared') {
            $salesRows = array_values(array_filter($salesRows, fn($r) => $r['sale_balance'] <= 0));
        }

        $totalBalance = (float) array_sum(array_column($salesRows, 'sale_balance'));
        $totalCredit  = (float) array_sum(array_column($salesRows, 'credit_amount'));
        $totalOverdue = (float) array_sum(array_map(fn($r) => $r['is_overdue'] ? $r['sale_balance'] : 0, $salesRows));
        $overdueCount = count(array_filter($salesRows, fn($r) => $r['is_overdue']));
        $activeCount  = count(array_filter($salesRows, fn($r) => !$r['is_overdue'] && $r['sale_balance'] > 0));

        // Group by customer
        $byCustomer = [];
        foreach ($salesRows as $row) {
            $key = $row['customer_id'] ?? 'wi_' . md5($row['customer_name']);
            if (!isset($byCustomer[$key])) {
                $byCustomer[$key] = [
                    'customer_id'    => $row['customer_id'],
                    'customer_name'  => $row['customer_name'],
                    'customer_phone' => $row['customer_phone'] ?? '',
                    'credit_limit'   => (float)($row['credit_limit'] ?? 0),
                    'total_balance'  => 0.0,
                    'total_credit'   => 0.0,
                    'invoice_count'  => 0,
                    'overdue_count'  => 0,
                    'overdue_amount' => 0.0,
                    'next_due'       => null,
                    'is_overdue'     => false,
                ];
            }
            $byCustomer[$key]['total_balance']  += (float)$row['sale_balance'];
            $byCustomer[$key]['total_credit']   += (float)$row['credit_amount'];
            $byCustomer[$key]['invoice_count']  += 1;
            if ($row['is_overdue']) {
                $byCustomer[$key]['overdue_count']  += 1;
                $byCustomer[$key]['overdue_amount'] += (float)$row['sale_balance'];
                $byCustomer[$key]['is_overdue']      = true;
            }
            if ($row['due_date'] && !$row['is_overdue']) {
                if (!$byCustomer[$key]['next_due'] || $row['due_date'] < $byCustomer[$key]['next_due'])
                    $byCustomer[$key]['next_due'] = $row['due_date'];
            }
        }
        usort($byCustomer, fn($a,$b) => $b['total_balance'] <=> $a['total_balance']);

        $customers  = Database::fetchAll(
            "SELECT id, customer_name FROM customers WHERE status='active' ORDER BY customer_name"
        );

        $pageTitle  = 'Credit Customers Report';
        $breadcrumb = [['label' => 'Reports'], ['label' => 'Credit Customers']];
        ob_start();
        require APP_PATH . '/views/reports/credit_customers.php';
        $content = ob_get_clean();
        require APP_PATH . '/views/layouts/main.php';
    }

    public function expenses(): void
    {
        Auth::requirePermission('reports.view');

        $from   = $this->get('from',  date('Y-m-01'));
        $to     = $this->get('to',    date('Y-m-d'));
        $catId  = (int) $this->get('category', 0);

        $where  = ['e.expense_date BETWEEN ? AND ?'];
        $params = [$from, $to];
        if ($catId) { $where[] = 'e.expense_category_id = ?'; $params[] = $catId; }

        // Category breakdown
        $byCategory = Database::fetchAll(
            "SELECT ec.id AS cat_id, ec.category_name,
                    COALESCE(SUM(e.amount),0) AS total,
                    COUNT(*) AS count,
                    MAX(e.amount) AS max_single
             FROM expenses e
             JOIN expense_categories ec ON ec.id = e.expense_category_id
             WHERE " . implode(' AND ', $where) . "
             GROUP BY e.expense_category_id ORDER BY total DESC",
            $params
        );

        $totalAmt   = (float) array_sum(array_column($byCategory, 'total'));
        $totalCount = (int)   array_sum(array_column($byCategory, 'count'));

        // Daily trend (for the period)
        $dailyTrend = Database::fetchAll(
            "SELECT e.expense_date AS day,
                    COALESCE(SUM(e.amount),0) AS total,
                    COUNT(*) AS count
             FROM expenses e
             WHERE " . implode(' AND ', $where) . "
             GROUP BY e.expense_date ORDER BY e.expense_date ASC",
            $params
        );

        // Top individual expenses in period
        $topExpenses = Database::fetchAll(
            "SELECT e.*, ec.category_name, u.name AS recorded_by
             FROM expenses e
             JOIN expense_categories ec ON ec.id = e.expense_category_id
             LEFT JOIN users u ON u.id = e.created_by
             WHERE " . implode(' AND ', $where) . "
             ORDER BY e.amount DESC LIMIT 10",
            $params
        );

        $days       = max(1, (int)((strtotime($to) - strtotime($from)) / 86400) + 1);
        $avgDaily   = $totalAmt / $days;
        $avgPerTxn  = $totalCount > 0 ? $totalAmt / $totalCount : 0;

        $categories = Database::fetchAll("SELECT id, category_name FROM expense_categories WHERE status='active' ORDER BY category_name");

        $pageTitle  = 'Expense Report';
        $breadcrumb = [['label' => 'Reports'], ['label' => 'Expenses']];
        ob_start();
        require APP_PATH . '/views/reports/expenses.php';
        $content = ob_get_clean();
        require APP_PATH . '/views/layouts/main.php';
    }
}