<?php
class ReportsController {
    
    public function index(): void {
        Auth::requireLogin();
        Auth::requireActiveTenant();
        Auth::requireRole(['admin','owner','manager']);
        PlanGuard::enforce('reports_advanced');

        $tid = Auth::tenantId();
        $report = $_GET['report'] ?? 'dashboard';
        $format = $_GET['format'] ?? 'html';
        $filters = $this->getFilters();
        
        // Route to appropriate report method
        switch ($report) {
            case 'inventory':
                $this->inventoryReport($tid, $filters, $format);
                break;
            case 'low-stock':
                $this->lowStockReport($tid, $filters, $format);
                break;
            case 'stock-valuation':
                $this->stockValuationReport($tid, $filters, $format);
                break;
            case 'sales':
                $this->salesReport($tid, $filters, $format);
                break;
            case 'product-sales':
                $this->productSalesReport($tid, $filters, $format);
                break;
            case 'daily-stock-monitoring':
                $this->dailyStockMonitoringReport($tid, $filters, $format);
                break;
            case 'stock-movement':
                $this->stockMovementReport($tid, $filters, $format);
                break;
            case 'stock-trending':
                $this->stockTrendingReport($tid, $filters, $format);
                break;
            case 'profit-loss':
                $this->profitLossReport($tid, $filters, $format);
                break;
            case 'ai-analytics':
                $this->aiAnalyticsReport($tid, $filters, $format);
                break;
            case 'expiry':
                $this->expiryReport($tid, $filters, $format);
                break;
            case 'tax':
                $this->taxReport($tid, $filters, $format);
                break;
            case 'stock-summary':
                $this->stockSummaryReport($tid, $filters, $format);
                break;
            case 'purchase-items':
                $this->purchaseItemsReport($tid, $filters, $format);
                break;
            case 'top-suppliers':
                $this->topSuppliersReport($tid, $filters, $format);
                break;
            case 'purchase-pay-due':
                $this->purchasePayDueReport($tid, $filters, $format);
                break;
            default:
                $this->dashboard($tid);
                break;
        }
    }

    private function getFilters(): array {
        // For expiry reports, show all expiry data by default (no date filter)
        $isExpiryReport = ($_GET['report'] ?? '') === 'expiry';
        $isSalesReport = ($_GET['report'] ?? '') === 'sales';
        $isDailyStockMonitoring = ($_GET['report'] ?? '') === 'daily-stock-monitoring';

        // Sales report shows all-time by default (no date filters)
        // Expiry report shows all batches with expiry dates by default (no date filter)
        // Daily stock monitoring uses a single date filter
        if ($isSalesReport || $isExpiryReport) {
            $defaultDateFrom = null;
            $defaultDateTo = null;
        } else {
            $defaultDateFrom = date('Y-m-01');
            $defaultDateTo = date('Y-m-d');
        }

        $filters = [
            'date_from' => isset($_GET['date_from']) && $_GET['date_from'] !== '' ? $_GET['date_from'] : $defaultDateFrom,
            'date_to' => isset($_GET['date_to']) && $_GET['date_to'] !== '' ? $_GET['date_to'] : $defaultDateTo,
            'category_id' => $_GET['category_id'] ?? '',
            'location_id' => $_GET['location_id'] ?? '',
            'supplier_id' => $_GET['supplier_id'] ?? '',
            'customer_id' => $_GET['customer_id'] ?? '',
            'product_id' => $_GET['product_id'] ?? '',
            'search' => $_GET['search'] ?? '',
            'status' => $_GET['status'] ?? '',
            'user_id' => $_GET['user_id'] ?? '',
            'page' => (int)($_GET['page'] ?? 1)
        ];

        // Add date filter for daily stock monitoring
        if ($isDailyStockMonitoring) {
            $filters['date'] = isset($_GET['date']) && $_GET['date'] !== '' ? $_GET['date'] : date('Y-m-d');
        }

        return $filters;
    }

    private function dashboard(int $tid): void {
        // Sales Overview (Last 30 Days)
        $sales30 = db()->prepare(
            "SELECT DATE(created_at) as date, SUM(total) as daily_total, COUNT(*) as daily_count
             FROM sales WHERE tenant_id=? AND status='completed' 
             AND created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
             GROUP BY DATE(created_at) ORDER BY date ASC"
        );
        $sales30->execute([$tid]);
        $chartData = $sales30->fetchAll();

        // Inventory Valuation (Using Movement History)
        $valuation = db()->prepare(
            "SELECT 
                SUM(acc.qty * acc.cost) as total_cost,
                SUM(acc.qty * acc.price) as total_retail
             FROM (
                SELECT p.id, p.cost_price as cost, p.selling_price as price,
                COALESCE((
                    SELECT SUM(CASE 
                        WHEN sm.type IN ('purchase', 'transfer_in', 'adjustment_in') OR (sm.type IN ('adjustment', 'return') AND sm.quantity > 0) THEN ABS(sm.quantity)
                        WHEN sm.type IN ('sale', 'transfer_out', 'adjustment_out') OR (sm.type IN ('adjustment', 'return') AND sm.quantity < 0) THEN -ABS(sm.quantity)
                        ELSE 0 
                    END)
                    FROM stock_movements sm
                    WHERE sm.product_id = p.id AND sm.tenant_id = p.tenant_id
                ), 0) as qty
                FROM products p
                WHERE p.tenant_id = ? AND p.is_active = 1
             ) as acc"
        );
        $valuation->execute([$tid]);
        $valStats = $valuation->fetch();

        // Top Selling Products
        $topProds = db()->prepare(
            "SELECT p.name, p.sku, SUM(si.quantity) as total_sold, SUM(si.quantity * si.unit_price) as total_revenue
             FROM sale_items si JOIN products p ON p.id = si.product_id
             WHERE si.tenant_id=? GROUP BY si.product_id ORDER BY total_sold DESC LIMIT 10"
        );
        $topProds->execute([$tid]);
        $topSelling = $topProds->fetchAll();

        // Monthly Profit (Simplified)
        $profit = db()->prepare(
            "SELECT SUM(si.quantity * (si.unit_price - p.cost_price)) as gross_profit
             FROM sale_items si 
             JOIN products p ON p.id = si.product_id
             JOIN sales s ON s.id = si.sale_id
             WHERE si.tenant_id=? AND s.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)"
        );
        $profit->execute([$tid]);
        $monthProfit = $profit->fetchColumn();

        // Expiry Summary
        $expiryStats = db()->prepare(
            "SELECT 
                COUNT(*) as total_batches,
                SUM(CASE WHEN b.expiry_date < CURDATE() THEN 1 ELSE 0 END) as expired_count,
                SUM(CASE WHEN b.expiry_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 30 DAY) THEN 1 ELSE 0 END) as expiring_soon_count,
                SUM(CASE WHEN b.expiry_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 90 DAY) THEN 1 ELSE 0 END) as expiring_count,
                SUM(s.quantity * p.cost_price) as total_cost_value
             FROM batches b
             JOIN products p ON p.id = b.product_id
             JOIN stock s ON s.batch_id = b.id AND s.tenant_id = b.tenant_id
             WHERE b.tenant_id = ? AND b.expiry_date IS NOT NULL AND s.quantity > 0"
        );
        $expiryStats->execute([$tid]);
        $expirySummary = $expiryStats->fetch();

        $pageTitle = 'Reports Dashboard';
        ob_start();
        include APP_ROOT . '/views/tenant/reports/index.php';
        $content = ob_get_clean();
        include APP_ROOT . '/views/tenant/layouts/main.php';
    }

    private function inventoryReport(int $tid, array $filters, string $format): void {
        $page = (int)($filters['page'] ?? 1);
        $perPage = $_GET['per_page'] ?? ITEMS_PER_PAGE;
        $pp = $perPage === 'all' ? PHP_INT_MAX : (int)$perPage;
        $offset = ($page - 1) * $pp;

        $locFilter = "";
        $subParams = [$tid];
        if (!empty($filters['location_id'])) {
            $locFilter = " AND sm.location_id = ?";
            $subParams[] = $filters['location_id'];
        }

        $sql = "SELECT p.id, p.sku, p.name, p.cost_price, p.selling_price, c.name as category,
                COALESCE(sm_sum.stock_quantity, 0) as stock_quantity,
                COALESCE(sm_sum.stock_quantity * p.cost_price, 0) as total_cost,
                COALESCE(sm_sum.stock_quantity * p.selling_price, 0) as total_value,
                p.alert_qty as low_stock_threshold, p.is_active
                FROM products p
                LEFT JOIN categories c ON c.id = p.category_id
                LEFT JOIN (
                    SELECT sm.product_id,
                           SUM(CASE 
                               WHEN sm.type IN ('purchase', 'transfer_in', 'adjustment_in') OR (sm.type IN ('adjustment', 'return') AND sm.quantity > 0) THEN ABS(sm.quantity)
                               WHEN sm.type IN ('sale', 'transfer_out', 'adjustment_out') OR (sm.type IN ('adjustment', 'return') AND sm.quantity < 0) THEN -ABS(sm.quantity)
                               ELSE 0 
                           END) as stock_quantity
                    FROM stock_movements sm
                    WHERE sm.tenant_id = ?" . $locFilter . "
                    GROUP BY sm.product_id
                ) as sm_sum ON sm_sum.product_id = p.id
                WHERE p.tenant_id = ?";

        $params = array_merge($subParams, [$tid]);

        if (!empty($filters['search'])) {
            $sql .= " AND (p.name LIKE ? OR p.sku LIKE ?)";
            $params[] = "%{$filters['search']}%";
            $params[] = "%{$filters['search']}%";
        }

        if (!empty($filters['category_id'])) {
            $sql .= " AND p.category_id = ?";
            $params[] = $filters['category_id'];
        }

        if (!empty($filters['location_id'])) {
            // Only show products that have movements at this location if filtered
            $sql .= " AND sm_sum.stock_quantity IS NOT NULL";
        }

        $sql .= " ORDER BY p.name";

        // Get total count
        $countSql = "SELECT COUNT(*) FROM ({$sql}) as count_table";
        $cntStmt = db()->prepare($countSql);
        $cntStmt->execute($params);
        $total = (int)$cntStmt->fetchColumn();

        // Add pagination
        $sql .= " LIMIT {$pp} OFFSET {$offset}";

        $stmt = db()->prepare($sql);
        $stmt->execute($params);
        $inventory = $stmt->fetchAll();

        // Calculate summary from full dataset (without pagination) using movements
        $summarySql = "SELECT COUNT(*) as total_products,
                SUM(COALESCE(sm_sum.stock_quantity, 0) * p.selling_price) as total_stock_value,
                SUM(CASE WHEN COALESCE(sm_sum.stock_quantity, 0) <= p.alert_qty THEN 1 ELSE 0 END) as low_stock_items,
                SUM(CASE WHEN COALESCE(sm_sum.stock_quantity, 0) = 0 THEN 1 ELSE 0 END) as out_of_stock
                FROM products p
                LEFT JOIN (
                    SELECT sm.product_id,
                           SUM(CASE 
                               WHEN sm.type IN ('purchase', 'transfer_in', 'adjustment_in') OR (sm.type IN ('adjustment', 'return') AND sm.quantity > 0) THEN ABS(sm.quantity)
                               WHEN sm.type IN ('sale', 'transfer_out', 'adjustment_out') OR (sm.type IN ('adjustment', 'return') AND sm.quantity < 0) THEN -ABS(sm.quantity)
                               ELSE 0 
                           END) as stock_quantity
                    FROM stock_movements sm
                    WHERE sm.tenant_id = ?" . $locFilter . "
                    GROUP BY sm.product_id
                ) as sm_sum ON sm_sum.product_id = p.id
                WHERE p.tenant_id = ?"
                . (!empty($filters['search']) ? " AND (p.name LIKE ? OR p.sku LIKE ?)" : "")
                . (!empty($filters['category_id']) ? " AND p.category_id = ?" : "")
                . (!empty($filters['location_id']) ? " AND sm_sum.stock_quantity IS NOT NULL" : "");

        $summaryStmt = db()->prepare($summarySql);
        $summaryStmt->execute($params);
        $summary = $summaryStmt->fetch();

        $data = [
            'title' => 'Inventory Report',
            'filters' => $filters,
            'inventory' => $inventory,
            'pagination' => [
                'total' => $total,
                'page' => $page,
                'last_page' => $perPage === 'all' ? 1 : (int)ceil($total / $pp)
            ],
            'summary' => [
                'total_products' => (int)($summary['total_products'] ?? 0),
                'total_stock_value' => (float)($summary['total_stock_value'] ?? 0),
                'low_stock_items' => (int)($summary['low_stock_items'] ?? 0),
                'out_of_stock' => (int)($summary['out_of_stock'] ?? 0)
            ]
        ];

        $this->outputReport($data, $format, 'inventory');
    }

    private function lowStockReport(int $tid, array $filters, string $format): void {
        $page = (int)($filters['page'] ?? 1);
        $perPage = $_GET['per_page'] ?? ITEMS_PER_PAGE;
        $pp = $perPage === 'all' ? PHP_INT_MAX : (int)$perPage;
        $offset = ($page - 1) * $pp;

        $locFilter = "";
        $subParams = [$tid];
        if (!empty($filters['location_id'])) {
            $locFilter = " AND sm.location_id = ?";
            $subParams[] = $filters['location_id'];
        }

        $sql = "SELECT p.id, p.sku, p.name, p.cost_price, p.selling_price, c.name as category,
                COALESCE(sm_sum.stock_quantity, 0) as stock_quantity,
                p.alert_qty as low_stock_threshold,
                (p.alert_qty - COALESCE(sm_sum.stock_quantity, 0)) as needed_to_reorder
                FROM products p
                LEFT JOIN categories c ON c.id = p.category_id
                LEFT JOIN (
                    SELECT sm.product_id,
                           SUM(CASE 
                               WHEN sm.type IN ('purchase', 'transfer_in', 'adjustment_in') OR (sm.type IN ('adjustment', 'return') AND sm.quantity > 0) THEN ABS(sm.quantity)
                               WHEN sm.type IN ('sale', 'transfer_out', 'adjustment_out') OR (sm.type IN ('adjustment', 'return') AND sm.quantity < 0) THEN -ABS(sm.quantity)
                               ELSE 0 
                           END) as stock_quantity
                    FROM stock_movements sm
                    WHERE sm.tenant_id = ?" . $locFilter . "
                    GROUP BY sm.product_id
                ) as sm_sum ON sm_sum.product_id = p.id
                WHERE p.tenant_id = ? AND p.is_active = 1";

        $params = array_merge($subParams, [$tid]);

        if (!empty($filters['search'])) {
            $sql .= " AND (p.name LIKE ? OR p.sku LIKE ?)";
            $params[] = "%{$filters['search']}%";
            $params[] = "%{$filters['search']}%";
        }

        if (!empty($filters['category_id'])) {
            $sql .= " AND p.category_id = ?";
            $params[] = $filters['category_id'];
        }

        // Filter for low stock items only (using the subquery results)
        $sql .= " AND (COALESCE(sm_sum.stock_quantity, 0) <= p.alert_qty OR COALESCE(sm_sum.stock_quantity, 0) = 0)";

        if (!empty($filters['location_id'])) {
            // If location is filtered, only show products with movements there OR those with 0 stock that might need attention
            // Usually, we just show everything that matches the low stock condition for that location
        }

        $sql .= " ORDER BY (COALESCE(sm_sum.stock_quantity, 0) / NULLIF(p.alert_qty, 0)) ASC";

        // Get total count
        $countSql = "SELECT COUNT(*) FROM ({$sql}) as count_table";
        $cntStmt = db()->prepare($countSql);
        $cntStmt->execute($params);
        $total = (int)$cntStmt->fetchColumn();

        // Add pagination
        $sql .= " LIMIT {$pp} OFFSET {$offset}";

        $stmt = db()->prepare($sql);
        $stmt->execute($params);
        $lowStockItems = $stmt->fetchAll();

        // Calculate summary stats using the same movement-based logic but for the entire set
        $summary = [
            'total_items' => $total,
            'out_of_stock' => 0,
            'total_reorder_value' => 0
        ];
        
        // We need another query for accurate totals because $lowStockItems is just the current page
        $summaryStmt = db()->prepare("SELECT 
            SUM(CASE WHEN stock_quantity = 0 THEN 1 ELSE 0 END) as out_of_stock,
            SUM(CASE WHEN stock_quantity < low_stock_threshold THEN (low_stock_threshold - stock_quantity) * cost_price ELSE 0 END) as total_reorder_value
            FROM ({$sql}) as full_dataset");
        $summaryStmt->execute($params);
        $summaryData = $summaryStmt->fetch();
        
        $summary['out_of_stock'] = (int)($summaryData['out_of_stock'] ?? 0);
        $summary['total_reorder_value'] = (float)($summaryData['total_reorder_value'] ?? 0);

        $data = [
            'title' => 'Low Stock Items Report',
            'filters' => $filters,
            'low_stock' => $lowStockItems,
            'pagination' => [
                'total' => $total,
                'page' => $page,
                'last_page' => $perPage === 'all' ? 1 : (int)ceil($total / $pp)
            ],
            'summary' => $summary
        ];

        $this->outputReport($data, $format, 'low-stock');
    }

    private function stockValuationReport(int $tid, array $filters, string $format): void {
        $page = (int)($filters['page'] ?? 1);
        $perPage = $_GET['per_page'] ?? ITEMS_PER_PAGE;
        $pp = $perPage === 'all' ? PHP_INT_MAX : (int)$perPage;
        $offset = ($page - 1) * $pp;

        $sql = "SELECT p.id, p.sku, p.name as product, c.name as category,
                l.name as location, s.quantity, p.cost_price, p.selling_price,
                (s.quantity * p.cost_price) as cost_value,
                (s.quantity * p.selling_price) as retail_value,
                ((s.quantity * p.selling_price) - (s.quantity * p.cost_price)) as potential_profit
                FROM stock s
                JOIN products p ON p.id = s.product_id
                JOIN categories c ON c.id = p.category_id
                JOIN locations l ON l.id = s.location_id
                WHERE s.tenant_id = ? AND s.quantity > 0";

        $params = [$tid];

        if (!empty($filters['category_id'])) {
            $sql .= " AND p.category_id = ?";
            $params[] = $filters['category_id'];
        }

        if (!empty($filters['location_id'])) {
            $sql .= " AND s.location_id = ?";
            $params[] = $filters['location_id'];
        }

        $sql .= " ORDER BY retail_value DESC";

        // Get total count
        $countSql = "SELECT COUNT(*) FROM ({$sql}) as count_table";
        $cntStmt = db()->prepare($countSql);
        $cntStmt->execute($params);
        $total = (int)$cntStmt->fetchColumn();

        // Add pagination
        $sql .= " LIMIT {$pp} OFFSET {$offset}";

        $stmt = db()->prepare($sql);
        $stmt->execute($params);
        $stockData = $stmt->fetchAll();

        // Calculate summary from full dataset (without pagination)
        $summarySql = "SELECT COUNT(*) as total_products,
                SUM(s.quantity * p.selling_price) as total_stock_value,
                SUM(s.quantity * p.cost_price) as total_stock_cost,
                SUM((s.quantity * p.selling_price) - (s.quantity * p.cost_price)) as potential_profit,
                SUM(s.quantity * p.cost_price) as total_cost_value,
                SUM(s.quantity * p.selling_price) as total_retail_value,
                SUM((s.quantity * p.selling_price) - (s.quantity * p.cost_price)) as total_potential_profit
                FROM stock s
                JOIN products p ON p.id = s.product_id
                JOIN categories c ON c.id = p.category_id
                JOIN locations l ON l.id = s.location_id
                WHERE s.tenant_id = ? AND s.quantity > 0"
                . (!empty($filters['category_id']) ? " AND p.category_id = ?" : "")
                . (!empty($filters['location_id']) ? " AND s.location_id = ?" : "");

        $summaryStmt = db()->prepare($summarySql);
        $summaryStmt->execute($params);
        $summary = $summaryStmt->fetch();

        $data = [
            'title' => 'Stock Valuation Report',
            'filters' => $filters,
            'stock_data' => $stockData,
            'pagination' => [
                'total' => $total,
                'page' => $page,
                'last_page' => $perPage === 'all' ? 1 : (int)ceil($total / $pp)
            ],
            'summary' => [
                'total_products' => (int)($summary['total_products'] ?? 0),
                'total_stock_value' => (float)($summary['total_stock_value'] ?? 0),
                'total_stock_cost' => (float)($summary['total_stock_cost'] ?? 0),
                'potential_profit' => (float)($summary['potential_profit'] ?? 0),
                'total_cost_value' => (float)($summary['total_cost_value'] ?? 0),
                'total_retail_value' => (float)($summary['total_retail_value'] ?? 0),
                'total_potential_profit' => (float)($summary['total_potential_profit'] ?? 0)
            ]
        ];

        $this->outputReport($data, $format, 'stock-valuation');
    }

    private function salesReport(int $tid, array $filters, string $format): void {
        $page = (int)($filters['page'] ?? 1);
        $perPage = $_GET['per_page'] ?? ITEMS_PER_PAGE;
        $pp = $perPage === 'all' ? PHP_INT_MAX : (int)$perPage;
        $offset = ($page - 1) * $pp;

        $sql = "SELECT s.id, s.sale_no, s.type, s.customer_id, s.location_id, s.user_id,
                       s.subtotal, s.discount_amount, s.tax_amount, s.total, s.amount_paid,
                       s.payment_method, s.payment_status, s.status, s.created_at,
                       c.name as customer, l.name as location, u.name as created_by,
                       COUNT(si.id) as item_count
                FROM sales s
                LEFT JOIN customers c ON c.id = s.customer_id
                LEFT JOIN locations l ON l.id = s.location_id
                LEFT JOIN users u ON u.id = s.user_id
                LEFT JOIN sale_items si ON si.sale_id = s.id
                WHERE s.tenant_id = ? AND s.status = 'completed'";

        $params = [$tid];

        if (!empty($filters['date_from'])) {
            $sql .= " AND DATE(s.created_at) >= ?";
            $params[] = $filters['date_from'];
        }

        if (!empty($filters['date_to'])) {
            $sql .= " AND DATE(s.created_at) <= ?";
            $params[] = $filters['date_to'];
        }

        if (!empty($filters['customer_id'])) {
            $sql .= " AND s.customer_id = ?";
            $params[] = $filters['customer_id'];
        }

        if (!empty($filters['location_id'])) {
            $sql .= " AND s.location_id = ?";
            $params[] = $filters['location_id'];
        }

        if (!empty($filters['status'])) {
            $sql .= " AND s.status = ?";
            $params[] = $filters['status'];
        }

        if (!empty($filters['user_id'])) {
            $sql .= " AND s.user_id = ?";
            $params[] = $filters['user_id'];
        }

        $sql .= " GROUP BY s.id ORDER BY s.created_at DESC";

        // Get total count
        $countSql = "SELECT COUNT(DISTINCT s.id) FROM sales s
                     LEFT JOIN customers c ON c.id = s.customer_id
                     LEFT JOIN locations l ON l.id = s.location_id
                     LEFT JOIN users u ON u.id = s.user_id
                     LEFT JOIN sale_items si ON si.sale_id = s.id
                     WHERE s.tenant_id = ? AND s.status = 'completed'"
                     . (!empty($filters['date_from']) ? " AND DATE(s.created_at) >= ?" : "")
                     . (!empty($filters['date_to']) ? " AND DATE(s.created_at) <= ?" : "")
                     . (!empty($filters['customer_id']) ? " AND s.customer_id = ?" : "")
                     . (!empty($filters['location_id']) ? " AND s.location_id = ?" : "")
                     . (!empty($filters['status']) ? " AND s.status = ?" : "")
                     . (!empty($filters['user_id']) ? " AND s.user_id = ?" : "");
        
        $cntStmt = db()->prepare($countSql);
        $cntStmt->execute($params);
        $total = (int)$cntStmt->fetchColumn();

        // Add pagination
        $sql .= " LIMIT {$pp} OFFSET {$offset}";

        $stmt = db()->prepare($sql);
        $stmt->execute($params);
        $sales = $stmt->fetchAll();

        // Calculate summary - separate queries to avoid JOIN duplication
        $summaryStmt = db()->prepare("SELECT
                                        COUNT(*) as total_sales,
                                        COALESCE(SUM(total), 0) as total_revenue,
                                        ROUND(AVG(total), 2) as average_sale_value
                                    FROM sales
                                    WHERE tenant_id = ? AND status = 'completed'"
                                    . (!empty($filters['date_from']) ? " AND DATE(created_at) >= ?" : "")
                                    . (!empty($filters['date_to']) ? " AND DATE(created_at) <= ?" : "")
                                    . (!empty($filters['customer_id']) ? " AND customer_id = ?" : "")
                                    . (!empty($filters['location_id']) ? " AND location_id = ?" : "")
                                    . (!empty($filters['status']) ? " AND status = ?" : "")
                                    . (!empty($filters['user_id']) ? " AND user_id = ?" : ""));
        $summaryStmt->execute($params);
        $summary = $summaryStmt->fetch();

        // Calculate total items sold separately with JOIN
        $itemsSoldStmt = db()->prepare("SELECT COALESCE(SUM(si.quantity), 0) as total_items_sold
                                        FROM sale_items si
                                        JOIN sales s ON s.id = si.sale_id
                                        WHERE si.tenant_id = ? AND s.status = 'completed'"
                                        . (!empty($filters['date_from']) ? " AND DATE(s.created_at) >= ?" : "")
                                        . (!empty($filters['date_to']) ? " AND DATE(s.created_at) <= ?" : "")
                                        . (!empty($filters['customer_id']) ? " AND s.customer_id = ?" : "")
                                        . (!empty($filters['location_id']) ? " AND s.location_id = ?" : "")
                                        . (!empty($filters['status']) ? " AND s.status = ?" : "")
                                        . (!empty($filters['user_id']) ? " AND s.user_id = ?" : ""));
        $itemsSoldStmt->execute($params);
        $itemsSold = $itemsSoldStmt->fetch();
        $summary['total_items_sold'] = $itemsSold['total_items_sold'];

        $data = [
            'title' => 'Sales Report',
            'filters' => $filters,
            'sales' => $sales,
            'summary' => [
                'total_sales' => (int)($summary['total_sales'] ?? 0),
                'total_revenue' => (float)($summary['total_revenue'] ?? 0),
                'total_items_sold' => (int)($summary['total_items_sold'] ?? 0),
                'average_sale_value' => (float)($summary['average_sale_value'] ?? 0)
            ],
            'pagination' => [
                'total' => $total,
                'page' => $page,
                'last_page' => $perPage === 'all' ? 1 : (int)ceil($total / $pp)
            ]
        ];

        $this->outputReport($data, $format, 'sales');
    }

    private function productSalesReport(int $tid, array $filters, string $format): void {
        $page = (int)($filters['page'] ?? 1);
        $perPage = $_GET['per_page'] ?? ITEMS_PER_PAGE;
        $pp = $perPage === 'all' ? PHP_INT_MAX : (int)$perPage;
        $offset = ($page - 1) * $pp;

        $sql = "SELECT p.id, p.name, p.sku, c.name as category,
                SUM(si.quantity) as units_sold,
                SUM(si.quantity * si.unit_price) as revenue,
                SUM(si.quantity * si.unit_price) - COALESCE(SUM((si.quantity * si.unit_price / s.subtotal) * s.tax_amount), 0) as net_revenue,
                COALESCE(SUM((si.quantity * si.unit_price / s.subtotal) * s.tax_amount), 0) as tax_amount,
                SUM(si.quantity * p.cost_price) as cost,
                SUM(si.quantity * (si.unit_price - p.cost_price)) as profit,
                CASE
                    WHEN SUM(si.quantity * si.unit_price) > 0
                    THEN (SUM(si.quantity * (si.unit_price - p.cost_price)) / SUM(si.quantity * si.unit_price)) * 100
                    ELSE 0
                END as margin
                FROM sale_items si
                JOIN products p ON p.id = si.product_id
                JOIN categories c ON c.id = p.category_id
                JOIN sales s ON s.id = si.sale_id
                WHERE si.tenant_id = ? AND s.status = 'completed' AND s.subtotal > 0";
        
        $params = [$tid];
        
        if (!empty($filters['date_from'])) {
            $sql .= " AND DATE(s.created_at) >= ?";
            $params[] = $filters['date_from'];
        }
        
        if (!empty($filters['date_to'])) {
            $sql .= " AND DATE(s.created_at) <= ?";
            $params[] = $filters['date_to'];
        }
        
        if (!empty($filters['category_id'])) {
            $sql .= " AND p.category_id = ?";
            $params[] = $filters['category_id'];
        }
        
        if (!empty($filters['location_id'])) {
            $sql .= " AND s.location_id = ?";
            $params[] = $filters['location_id'];
        }
        
        $sql .= " GROUP BY p.id ORDER BY revenue DESC";
        
        // Get total count
        $countSql = "SELECT COUNT(*) FROM ({$sql}) as count_table";
        $cntStmt = db()->prepare($countSql);
        $cntStmt->execute($params);
        $total = (int)$cntStmt->fetchColumn();
        
        // Add pagination
        $sql .= " LIMIT {$pp} OFFSET {$offset}";
        
        $stmt = db()->prepare($sql);
        $stmt->execute($params);
        $productSales = $stmt->fetchAll();

        $totalRevenue = array_sum(array_column($productSales, 'revenue'));
        $totalNetRevenue = array_sum(array_column($productSales, 'net_revenue'));
        $totalTaxAmount = array_sum(array_column($productSales, 'tax_amount'));

        $data = [
            'title' => 'Product Sales Report',
            'filters' => $filters,
            'product_sales' => $productSales,
            'pagination' => [
                'total' => $total,
                'page' => $page,
                'last_page' => $perPage === 'all' ? 1 : (int)ceil($total / $pp)
            ],
            'summary' => [
                'total_products' => $total,
                'total_revenue' => $totalRevenue + $totalTaxAmount,
                'total_net_revenue' => $totalNetRevenue,
                'total_tax_amount' => $totalTaxAmount,
                'total_units_sold' => array_sum(array_column($productSales, 'units_sold')),
                'average_price' => array_sum(array_column($productSales, 'units_sold')) > 0 ? ($totalRevenue + $totalTaxAmount) / array_sum(array_column($productSales, 'units_sold')) : 0
            ]
        ];
        
        $this->outputReport($data, $format, 'product-sales');
    }

    private function dailyStockMonitoringReport(int $tid, array $filters, string $format): void {
        $page = (int)($filters['page'] ?? 1);
        $perPage = $_GET['per_page'] ?? ITEMS_PER_PAGE;
        $pp = $perPage === 'all' ? PHP_INT_MAX : (int)$perPage;
        $offset = ($page - 1) * $pp;

        $date = $filters['date'] ?? date('Y-m-d');
        $locationId = $filters['location_id'] ?? null;
        $categoryId = $filters['category_id'] ?? null;

        // Base subquery for movement filters
        $movementWhere = "sm.tenant_id = ? AND DATE(sm.created_at) = ?";
        $movementParams = [$tid, $date];
        if ($locationId) {
            $movementWhere .= " AND sm.location_id = ?";
            $movementParams[] = $locationId;
        }

        // Main SQL construction
        $sql = "SELECT p.id, p.name, p.sku, c.name as category,
                COALESCE(SUM(s.quantity), 0) as current_stock,
                COALESCE(
                    (SELECT SUM(ABS(sm.quantity))
                     FROM stock_movements sm
                     WHERE sm.product_id = p.id
                     AND {$movementWhere}
                     AND sm.quantity < 0), 0
                ) as total_sold,
                COALESCE(
                    (SELECT SUM(sm.quantity)
                     FROM stock_movements sm
                     WHERE sm.product_id = p.id
                     AND {$movementWhere}
                     AND sm.quantity > 0), 0
                ) as total_added,
                COALESCE(SUM(s.quantity), 0) as closing_stock,
                (COALESCE(SUM(s.quantity), 0) - COALESCE(
                    (SELECT SUM(sm.quantity)
                     FROM stock_movements sm
                     WHERE sm.product_id = p.id
                     AND {$movementWhere}), 0
                )) as opening_stock
                FROM products p
                LEFT JOIN categories c ON c.id = p.category_id
                LEFT JOIN stock s ON s.product_id = p.id AND s.tenant_id = p.tenant_id" . ($locationId ? " AND s.location_id = ?" : "") . "
                WHERE p.tenant_id = ? AND p.is_active = 1";

        $params = array_merge($movementParams, $movementParams, $movementParams);
        if ($locationId) {
            $params[] = $locationId;
        }
        $params[] = $tid;

        if ($categoryId) {
            $sql .= " AND p.category_id = ?";
            $params[] = $categoryId;
        }

        $sql .= " GROUP BY p.id, p.name, p.sku, c.name";
        $orderBy = " ORDER BY p.name";

        // Get total count
        $countSql = "SELECT COUNT(*) FROM ({$sql}) as t";
        $cntStmt = db()->prepare($countSql);
        $cntStmt->execute($params);
        $total = (int)$cntStmt->fetchColumn();

        // Get summary from full dataset
        $summarySql = "SELECT COUNT(*) as total_products,
                       SUM(opening_stock) as total_opening_stock,
                       SUM(total_sold) as total_sold,
                       SUM(closing_stock) as total_closing_stock
                       FROM ({$sql}) as summary_table";
        $sumStmt = db()->prepare($summarySql);
        $sumStmt->execute($params);
        $summary = $sumStmt->fetch();

        // Add pagination and execution
        $sql .= $orderBy . " LIMIT {$pp} OFFSET {$offset}";
        $stmt = db()->prepare($sql);
        $stmt->execute($params);
        $stockData = $stmt->fetchAll();

        // Calculate stock_movement for each item
        foreach ($stockData as &$item) {
            $item['stock_movement'] = $item['total_added'] - $item['total_sold'];
        }
        unset($item);

        $data = [
            'title' => 'Daily Stock Monitoring Report',
            'filters' => $filters,
            'stock_data' => $stockData,
            'pagination' => [
                'total' => $total,
                'page' => $page,
                'last_page' => $perPage === 'all' ? 1 : (int)ceil($total / $pp)
            ],
            'summary' => [
                'total_products' => (int)($summary['total_products'] ?? 0),
                'total_opening_stock' => (float)($summary['total_opening_stock'] ?? 0),
                'total_sold' => (float)($summary['total_sold'] ?? 0),
                'total_closing_stock' => (float)($summary['total_closing_stock'] ?? 0)
            ]
        ];

        $this->outputReport($data, $format, 'daily-stock-monitoring');
    }

    private function stockMovementReport(int $tid, array $filters, string $format): void {
        $page = (int)($filters['page'] ?? 1);
        $perPage = $_GET['per_page'] ?? ITEMS_PER_PAGE;
        $pp = $perPage === 'all' ? PHP_INT_MAX : (int)$perPage;
        $offset = ($page - 1) * $pp;

        $where = "sm.tenant_id = ?";
        $params = [$tid];
        
        if (!empty($filters['date_from'])) {
            $where .= " AND sm.created_at >= ?";
            $params[] = $filters['date_from'];
        }
        
        if (!empty($filters['date_to'])) {
            $where .= " AND sm.created_at <= ?";
            $params[] = $filters['date_to'];
        }

        if (!empty($filters['location_id'])) {
            $where .= " AND sm.location_id = ?";
            $params[] = $filters['location_id'];
        }

        if (!empty($filters['product_id'])) {
            $where .= " AND sm.product_id = ?";
            $params[] = $filters['product_id'];
        }
        
        $sql = "SELECT 
                CASE 
                    WHEN sm.type = 'sale' THEN 'Sale'
                    WHEN sm.type = 'purchase' THEN 'Purchase'
                    WHEN sm.type = 'adjustment' THEN 'Adjustment'
                    WHEN sm.type = 'transfer_in' OR sm.type = 'transfer_out' THEN 'Transfer'
                    ELSE sm.type
                END as movement_type,
                sm.created_at, sm.reference,
                p.name as product, p.sku, sm.quantity as quantity_change,
                l.name as location, 
                '' as customer_or_supplier, 
                u.name as created_by
                FROM stock_movements sm
                JOIN products p ON p.id = sm.product_id
                JOIN locations l ON l.id = sm.location_id
                LEFT JOIN users u ON u.id = sm.user_id
                WHERE {$where}";
        
        $sql .= " ORDER BY created_at DESC";
        
        // Get total count
        $countSql = "SELECT COUNT(*) FROM ({$sql}) as count_table";
        $cntStmt = db()->prepare($countSql);
        $cntStmt->execute($params);
        $total = (int)$cntStmt->fetchColumn();
        
        // Add pagination
        $sql .= " LIMIT {$pp} OFFSET {$offset}";
        
        $stmt = db()->prepare($sql);
        $stmt->execute($params);
        $movements = $stmt->fetchAll();
        
        $data = [
            'title' => 'Stock Movement Report',
            'filters' => $filters,
            'movements' => $movements,
            'pagination' => [
                'total' => $total,
                'page' => $page,
                'last_page' => $perPage === 'all' ? 1 : (int)ceil($total / $pp)
            ],
            'summary' => [
                'total_movements' => $total,
                'total_sales' => count(array_filter($movements, fn($m) => $m['movement_type'] == 'Sale')),
                'total_purchases' => count(array_filter($movements, fn($m) => $m['movement_type'] == 'Purchase'))
            ]
        ];
        
        $this->outputReport($data, $format, 'stock-movement');
    }

    private function stockTrendingReport(int $tid, array $filters, string $format): void {
        $days = 30; // Default to 30 days
        $sql = "SELECT DATE(s.created_at) as date,
                SUM(CASE WHEN s.type = 'Sale' THEN si.quantity ELSE 0 END) as sold_quantity,
                SUM(CASE WHEN s.type = 'Purchase' THEN pi.quantity ELSE 0 END) as purchased_quantity,
                COUNT(DISTINCT s.id) as transaction_count
                FROM (
                    SELECT created_at, 'Sale' as type, id FROM sales WHERE tenant_id = ? AND status = 'completed'
                    UNION ALL
                    SELECT created_at, 'Purchase' as type, id FROM purchases WHERE tenant_id = ? AND status = 'received'
                ) s
                LEFT JOIN sale_items si ON s.type = 'Sale' AND si.sale_id = s.id AND si.tenant_id = ?
                LEFT JOIN purchase_items pi ON s.type = 'Purchase' AND pi.purchase_id = s.id AND pi.tenant_id = ?
                WHERE s.created_at >= DATE_SUB(NOW(), INTERVAL ? DAY)
                GROUP BY DATE(s.created_at)
                ORDER BY date DESC";
        
        $stmt = db()->prepare($sql);
        $stmt->execute([$tid, $tid, $tid, $tid, $days]);
        $trends = $stmt->fetchAll();
        
        $data = [
            'title' => 'Stock Trending Report',
            'filters' => $filters,
            'trends' => $trends,
            'summary' => [
                'period_days' => $days,
                'total_sold' => array_sum(array_column($trends, 'sold_quantity')),
                'total_purchased' => array_sum(array_column($trends, 'purchased_quantity')),
                'total_transactions' => array_sum(array_column($trends, 'transaction_count'))
            ]
        ];
        
        $this->outputReport($data, $format, 'stock-trending');
    }

    private function profitLossReport(int $tid, array $filters, string $format): void {
        // Use subqueries to avoid duplicate rows from multi-item sales
        $sql = "SELECT 
                s_daily.date,
                s_daily.revenue,
                s_daily.total_discounts,
                s_daily.total_tax,
                s_daily.transaction_count,
                COALESCE(si_daily.cost_of_goods_sold, 0) as cost_of_goods_sold,
                (s_daily.revenue - COALESCE(si_daily.cost_of_goods_sold, 0)) as gross_profit
                FROM (
                    SELECT DATE(created_at) as date,
                           SUM(total) as revenue,
                           SUM(discount_amount) as total_discounts,
                           SUM(tax_amount) as total_tax,
                           COUNT(id) as transaction_count
                    FROM sales
                    WHERE tenant_id = ? AND status = 'completed'";
        
        $params = [$tid];
        
        if (!empty($filters['date_from'])) {
            $sql .= " AND DATE(created_at) >= ?";
            $params[] = $filters['date_from'];
        }
        
        if (!empty($filters['date_to'])) {
            $sql .= " AND DATE(created_at) <= ?";
            $params[] = $filters['date_to'];
        }
        
        $sql .= " GROUP BY DATE(created_at)
                ) s_daily
                LEFT JOIN (
                    SELECT DATE(s.created_at) as date,
                           SUM(si.quantity * p.cost_price) as cost_of_goods_sold
                    FROM sale_items si
                    JOIN sales s ON s.id = si.sale_id
                    JOIN products p ON p.id = si.product_id
                    WHERE s.tenant_id = ? AND s.status = 'completed'";
        
        $params[] = $tid;
        
        if (!empty($filters['date_from'])) {
            $sql .= " AND DATE(s.created_at) >= ?";
            $params[] = $filters['date_from'];
        }
        
        if (!empty($filters['date_to'])) {
            $sql .= " AND DATE(s.created_at) <= ?";
            $params[] = $filters['date_to'];
        }
        
        $sql .= " GROUP BY DATE(s.created_at)
                ) si_daily ON s_daily.date = si_daily.date
                ORDER BY s_daily.date DESC";
        
        $stmt = db()->prepare($sql);
        $stmt->execute($params);
        $profitLoss = $stmt->fetchAll();
        
        $data = [
            'title' => 'Profit & Loss Report',
            'filters' => $filters,
            'profit_loss' => $profitLoss,
            'summary' => [
                'total_revenue' => array_sum(array_column($profitLoss, 'revenue')),
                'total_cogs' => array_sum(array_column($profitLoss, 'cost_of_goods_sold')),
                'total_gross_profit' => array_sum(array_column($profitLoss, 'gross_profit')),
                'total_discounts' => array_sum(array_column($profitLoss, 'total_discounts')),
                'total_tax' => array_sum(array_column($profitLoss, 'total_tax')),
                'profit_margin' => 0
            ]
        ];
        
        if ($data['summary']['total_revenue'] > 0) {
            $data['summary']['profit_margin'] = ($data['summary']['total_gross_profit'] / $data['summary']['total_revenue']) * 100;
        }
        
        $this->outputReport($data, $format, 'profit-loss');
    }

    private function aiAnalyticsReport(int $tid, array $filters, string $format): void {
        // Top performing products
        $topProducts = db()->prepare(
            "SELECT p.name, p.sku, SUM(si.quantity) as total_sold, 
             SUM(si.quantity * si.unit_price) as total_revenue,
             AVG(si.unit_price) as avg_price
             FROM sale_items si 
             JOIN products p ON p.id = si.product_id
             JOIN sales s ON s.id = si.sale_id
             WHERE si.tenant_id = ? AND s.status = 'completed'
             AND s.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
             GROUP BY si.product_id 
             ORDER BY total_revenue DESC LIMIT 10"
        );
        $topProducts->execute([$tid]);
        
        // Slow moving products
        $slowMoving = db()->prepare(
            "SELECT p.name, p.sku, COALESCE(SUM(s.quantity), 0) as stock_quantity,
             COALESCE(SUM(si.quantity), 0) as sold_quantity,
             p.cost_price, p.selling_price
             FROM products p
             LEFT JOIN stock s ON s.product_id = p.id AND s.tenant_id = p.tenant_id
             LEFT JOIN sale_items si ON si.product_id = p.id
             LEFT JOIN sales sa ON sa.id = si.sale_id AND sa.status = 'completed' 
                 AND sa.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
             WHERE p.tenant_id = ? AND p.is_active = 1
             GROUP BY p.id
             HAVING stock_quantity > 0 AND (sold_quantity IS NULL OR sold_quantity = 0)
             ORDER BY stock_quantity DESC LIMIT 10"
        );
        $slowMoving->execute([$tid]);
        
        // Sales by category
        $categorySales = db()->prepare(
            "SELECT c.name, SUM(si.quantity * si.unit_price) as revenue,
             COUNT(DISTINCT s.id) as transaction_count
             FROM sale_items si
             JOIN products p ON p.id = si.product_id
             JOIN categories c ON c.id = p.category_id
             JOIN sales s ON s.id = si.sale_id
             WHERE si.tenant_id = ? AND s.status = 'completed'
             AND s.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
             GROUP BY c.id
             ORDER BY revenue DESC"
        );
        $categorySales->execute([$tid]);
        
        // Customer insights
        $customerInsights = db()->prepare(
            "SELECT c.name, COUNT(s.id) as purchase_count,
             SUM(s.total) as total_spent, AVG(s.total) as avg_purchase,
             MAX(s.created_at) as last_purchase
             FROM customers c
             JOIN sales s ON s.customer_id = c.id
             WHERE s.tenant_id = ? AND s.status = 'completed'
             AND s.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
             GROUP BY c.id
             ORDER BY total_spent DESC LIMIT 10"
        );
        $customerInsights->execute([$tid]);
        
        $topProductsData = $topProducts->fetchAll();
        $slowMovingData = $slowMoving->fetchAll();
        $categorySalesData = $categorySales->fetchAll();
        $customerInsightsData = $customerInsights->fetchAll();

        $data = [
            'title' => 'AI Analytics Report',
            'filters' => $filters,
            'top_products' => $topProductsData,
            'slow_moving' => $slowMovingData,
            'category_sales' => $categorySalesData,
            'customer_insights' => $customerInsightsData,
            'summary' => [
                'analysis_period' => 'Last 30 Days',
                'top_performing_category' => $categorySalesData[0]['name'] ?? 'N/A',
                'total_customers_analyzed' => count($customerInsightsData),
                'slow_moving_items' => count($slowMovingData)
            ]
        ];
        
        $this->outputReport($data, $format, 'ai-analytics');
    }

    private function expiryReport(int $tid, array $filters, string $format): void {
        Auth::requirePermission('reports.expiry');
        
        $page = (int)($filters['page'] ?? 1);
        $perPage = $_GET['per_page'] ?? ITEMS_PER_PAGE;
        $pp = $perPage === 'all' ? PHP_INT_MAX : (int)$perPage;
        $offset = ($page - 1) * $pp;

        $sql = "SELECT b.id as batch_id, b.batch_no, b.expiry_date, b.mfg_date,
                p.id as product_id, p.name as product, p.sku, COALESCE(c.name, 'Uncategorized') as category,
                COALESCE(
                    (SELECT SUM(s2.quantity) FROM stock s2 WHERE s2.batch_id = b.id AND s2.tenant_id = b.tenant_id),
                    (SELECT SUM(s3.quantity) FROM stock s3 WHERE s3.product_id = p.id AND s3.tenant_id = p.tenant_id),
                    0
                ) as quantity,
                p.cost_price, p.selling_price,
                COALESCE(
                    (SELECT SUM(s2.quantity) FROM stock s2 WHERE s2.batch_id = b.id AND s2.tenant_id = b.tenant_id),
                    (SELECT SUM(s3.quantity) FROM stock s3 WHERE s3.product_id = p.id AND s3.tenant_id = p.tenant_id),
                    0
                ) * p.cost_price as cost_value,
                COALESCE(
                    (SELECT SUM(s2.quantity) FROM stock s2 WHERE s2.batch_id = b.id AND s2.tenant_id = b.tenant_id),
                    (SELECT SUM(s3.quantity) FROM stock s3 WHERE s3.product_id = p.id AND s3.tenant_id = p.tenant_id),
                    0
                ) * p.selling_price as retail_value,
                COALESCE(
                    (SELECT l2.name FROM stock s2 JOIN locations l2 ON l2.id = s2.location_id WHERE s2.batch_id = b.id AND s2.tenant_id = b.tenant_id LIMIT 1),
                    (SELECT l3.name FROM stock s3 JOIN locations l3 ON l3.id = s3.location_id WHERE s3.product_id = p.id AND s3.tenant_id = p.tenant_id LIMIT 1),
                    'N/A'
                ) as location,
                DATEDIFF(b.expiry_date, CURDATE()) as days_to_expiry,
                CASE
                    WHEN b.expiry_date < CURDATE() THEN 'expired'
                    WHEN b.expiry_date <= DATE_ADD(CURDATE(), INTERVAL 30 DAY) THEN 'expiring_soon'
                    WHEN b.expiry_date <= DATE_ADD(CURDATE(), INTERVAL 90 DAY) THEN 'expiring'
                    ELSE 'safe'
                END as expiry_status
                FROM batches b
                JOIN products p ON p.id = b.product_id
                LEFT JOIN categories c ON c.id = p.category_id
                WHERE b.tenant_id = ? AND b.expiry_date IS NOT NULL";
        
        $params = [$tid];
        
        if (!empty($filters['category_id'])) {
            $sql .= " AND p.category_id = ?";
            $params[] = $filters['category_id'];
        }
        
        if (!empty($filters['location_id'])) {
            $sql .= " AND s.location_id = ?";
            $params[] = $filters['location_id'];
        }
        
        if (!empty($filters['date_from'])) {
            $sql .= " AND b.expiry_date >= ?";
            $params[] = $filters['date_from'];
        }
        
        if (!empty($filters['date_to'])) {
            $sql .= " AND b.expiry_date <= ?";
            $params[] = $filters['date_to'];
        }
        
        // Filter by expiry status if specified
        if (!empty($filters['status'])) {
            switch ($filters['status']) {
                case 'expired':
                    $sql .= " AND b.expiry_date < CURDATE()";
                    break;
                case 'expiring_soon':
                    $sql .= " AND b.expiry_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 30 DAY)";
                    break;
                case 'expiring':
                    $sql .= " AND b.expiry_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 90 DAY)";
                    break;
                case 'safe':
                    $sql .= " AND b.expiry_date > DATE_ADD(CURDATE(), INTERVAL 90 DAY)";
                    break;
            }
        }
        
        $sql .= " ORDER BY b.expiry_date ASC";
        
        // Get total count
        $countSql = "SELECT COUNT(*) FROM ({$sql}) as count_table";
        $cntStmt = db()->prepare($countSql);
        $cntStmt->execute($params);
        $total = (int)$cntStmt->fetchColumn();
        
        // Add pagination
        $sql .= " LIMIT {$pp} OFFSET {$offset}";
        
        $stmt = db()->prepare($sql);
        $stmt->execute($params);
        $expiryData = $stmt->fetchAll();
        
        $data = [
            'title' => 'Product Expiry Report',
            'filters' => $filters,
            'expiry_data' => $expiryData,
            'pagination' => [
                'total' => $total,
                'page' => $page,
                'last_page' => $perPage === 'all' ? 1 : (int)ceil($total / $pp)
            ],
            'summary' => [
                'total_batches' => $total,
                'expired_count' => count(array_filter($expiryData, fn($item) => $item['expiry_status'] == 'expired')),
                'expiring_soon_count' => count(array_filter($expiryData, fn($item) => $item['expiry_status'] == 'expiring_soon')),
                'expiring_count' => count(array_filter($expiryData, fn($item) => $item['expiry_status'] == 'expiring')),
                'safe_count' => count(array_filter($expiryData, fn($item) => $item['expiry_status'] == 'safe')),
                'total_cost_value' => array_sum(array_column($expiryData, 'cost_value')),
                'total_retail_value' => array_sum(array_column($expiryData, 'retail_value'))
            ]
        ];
        
        $this->outputReport($data, $format, 'expiry');
    }

    private function outputReport(array $data, string $format, string $reportType): void {
        switch ($format) {
            case 'csv':
                $this->exportCSV($data, $reportType);
                break;
            case 'pdf':
                $this->exportPDF($data, $reportType);
                break;
            case 'print':
                $this->printReport($data, $reportType);
                break;
            default:
                $this->displayHTML($data, $reportType);
                break;
        }
    }

    private function displayHTML(array $data, string $reportType): void {
        $pageTitle = $data['title'];
        ob_start();
        include APP_ROOT . "/views/tenant/reports/{$reportType}.php";
        $content = ob_get_clean();
        include APP_ROOT . '/views/tenant/layouts/main.php';
    }

    private function exportCSV(array $data, string $reportType): void {
        header('Content-Type: text/csv');
        header('Content-Disposition: attachment; filename="' . $data['title'] . '.csv"');
        
        $output = fopen('php://output', 'w');
        
        // Add title and filters
        fputcsv($output, [$data['title']]);
        fputcsv($output, ['Generated: ' . date('Y-m-d H:i:s')]);
        fputcsv($output, []);
        
        // Add summary if available
        if (isset($data['summary'])) {
            fputcsv($output, ['Summary']);
            foreach ($data['summary'] as $key => $value) {
                fputcsv($output, [ucfirst(str_replace('_', ' ', $key)), $value]);
            }
            fputcsv($output, []);
        }
        
        // Add data based on report type
        $this->addCSVData($output, $data, $reportType);
        
        fclose($output);
        exit;
    }

    private function addCSVData($output, array $data, string $reportType): void {
        switch ($reportType) {
            case 'inventory':
                if (!empty($data['inventory'])) {
                    fputcsv($output, ['SKU', 'Product Name', 'Category', 'Stock Quantity', 'Cost Price', 'Selling Price', 'Total Value']);
                    foreach ($data['inventory'] as $item) {
                        fputcsv($output, [
                            $item['sku'], $item['name'], $item['category'],
                            $item['stock_quantity'], $item['cost_price'], $item['selling_price'], $item['total_value']
                        ]);
                    }
                }
                break;

            case 'low-stock':
                if (!empty($data['low_stock'])) {
                    fputcsv($output, ['SKU', 'Product Name', 'Category', 'Current Stock', 'Alert Qty', 'Needed to Reorder', 'Cost Price', 'Selling Price', 'Status']);
                    foreach ($data['low_stock'] as $item) {
                        fputcsv($output, [
                            $item['sku'], $item['name'], $item['category'],
                            $item['stock_quantity'], $item['low_stock_threshold'],
                            max(0, $item['needed_to_reorder']), $item['cost_price'], $item['selling_price'],
                            $item['stock_quantity'] == 0 ? 'Out of Stock' : 'Low Stock'
                        ]);
                    }
                }
                break;
                
            case 'sales':
                if (!empty($data['sales'])) {
                    fputcsv($output, ['Sale No', 'Date', 'Customer', 'Location', 'Total', 'Payment Method']);
                    foreach ($data['sales'] as $sale) {
                        fputcsv($output, [
                            $sale['sale_no'], $sale['created_at'], $sale['customer'],
                            $sale['location'], $sale['total'], $sale['payment_method']
                        ]);
                    }
                    // Add totals row
                    fputcsv($output, []);
                    fputcsv($output, ['TOTALS', '', '', '', array_sum(array_column($data['sales'], 'total')), '']);
                }
                break;
                
            case 'purchases':
                if (!empty($data['purchases'])) {
                    fputcsv($output, ['Purchase No', 'Date', 'Supplier', 'Location', 'Total', 'Status']);
                    foreach ($data['purchases'] as $purchase) {
                        fputcsv($output, [
                            $purchase['purchase_no'], $purchase['created_at'], $purchase['supplier'],
                            $purchase['location'], $purchase['total'], $purchase['status']
                        ]);
                    }
                }
                break;

            case 'purchase-items':
                if (!empty($data['purchase_items'])) {
                    fputcsv($output, ['Purchase No', 'Purchase Date', 'Product', 'SKU', 'Category', 'Quantity', 'Unit Cost', 'Total Cost', 'Supplier', 'Location', 'Potential Revenue', 'Potential Profit']);
                    foreach ($data['purchase_items'] as $item) {
                        fputcsv($output, [
                            $item['purchase_no'], $item['purchase_date'], $item['product_name'],
                            $item['sku'], $item['category'], $item['quantity'], $item['unit_cost'],
                            $item['total'], $item['supplier'], $item['location'],
                            $item['potential_revenue'], $item['potential_profit']
                        ]);
                    }
                    // Add totals row
                    fputcsv($output, []);
                    fputcsv($output, ['TOTALS', '', '', '', '', $data['summary']['total_quantity'], '', $data['summary']['total_cost'], '', '', $data['summary']['total_potential_revenue'], $data['summary']['total_potential_profit']]);
                }
                break;

            case 'top-suppliers':
                if (!empty($data['suppliers'])) {
                    fputcsv($output, ['Supplier', 'Purchase Count', 'Total Quantity', 'Unique Products', 'Avg Unit Cost', 'Total Cost']);
                    foreach ($data['suppliers'] as $supplier) {
                        fputcsv($output, [
                            $supplier['supplier'] ?? 'Direct', $supplier['purchase_count'],
                            $supplier['total_quantity'], $supplier['unique_products'],
                            $supplier['avg_unit_cost'], $supplier['total_cost']
                        ]);
                    }
                    // Add totals row
                    fputcsv($output, []);
                    fputcsv($output, ['TOTAL', $data['summary']['total_purchases'], $data['summary']['total_quantity'], '', $data['summary']['avg_unit_cost'], $data['summary']['total_cost']]);
                }
                break;

            case 'purchase-pay-due':
                if (!empty($data['purchases'])) {
                    fputcsv($output, ['Purchase No', 'Date', 'Supplier', 'Location', 'Total', 'Amount Paid', 'Due Amount', 'Payment Status', 'Payment Method']);
                    foreach ($data['purchases'] as $purchase) {
                        fputcsv($output, [
                            $purchase['purchase_no'], $purchase['created_at'], $purchase['supplier_name'] ?? 'Direct',
                            $purchase['location_name'], $purchase['total'], $purchase['amount_paid'],
                            $purchase['due_amount'], $purchase['payment_status'], $purchase['payment_method']
                        ]);
                    }
                    // Add totals row
                    fputcsv($output, []);
                    fputcsv($output, ['TOTALS', '', '', '', $data['summary']['total_purchase_value'], $data['summary']['total_amount_paid'], $data['summary']['total_amount_due'], '', '']);
                }
                break;

            case 'product-sales':
                if (!empty($data['product_sales'])) {
                    fputcsv($output, ['Product', 'SKU', 'Category', 'Units Sold', 'Revenue', 'Cost', 'Profit', 'Margin (%)']);
                    foreach ($data['product_sales'] as $product) {
                        fputcsv($output, [
                            $product['name'], $product['sku'], $product['category'],
                            $product['units_sold'], $product['revenue'], $product['cost'],
                            $product['profit'], number_format($product['margin'], 1)
                        ]);
                    }
                    // Add totals row
                    fputcsv($output, []);
                    $total_units = array_sum(array_column($data['product_sales'], 'units_sold'));
                    $total_revenue = array_sum(array_column($data['product_sales'], 'revenue'));
                    $total_cost = array_sum(array_column($data['product_sales'], 'cost'));
                    $total_profit = array_sum(array_column($data['product_sales'], 'profit'));
                    $avg_margin = $total_revenue > 0 ? ($total_profit / $total_revenue) * 100 : 0;
                    fputcsv($output, ['TOTALS', '', '', $total_units, $total_revenue, $total_cost, $total_profit, number_format($avg_margin, 1) . '%']);
                }
                break;

            case 'daily-stock-monitoring':
                if (!empty($data['stock_data'])) {
                    fputcsv($output, ['Product', 'SKU', 'Category', 'Opening Stock', 'Total Sold', 'Closing Stock', 'Current Stock', 'Stock Movement']);
                    foreach ($data['stock_data'] as $item) {
                        fputcsv($output, [
                            $item['name'], $item['sku'], $item['category'],
                            $item['opening_stock'], $item['total_sold'], $item['closing_stock'],
                            $item['current_stock'], $item['stock_movement']
                        ]);
                    }
                    // Add totals row
                    fputcsv($output, []);
                    $total_opening = array_sum(array_column($data['stock_data'], 'opening_stock'));
                    $total_sold = array_sum(array_column($data['stock_data'], 'total_sold'));
                    $total_closing = array_sum(array_column($data['stock_data'], 'closing_stock'));
                    $total_current = array_sum(array_column($data['stock_data'], 'current_stock'));
                    $total_movement = array_sum(array_column($data['stock_data'], 'stock_movement'));
                    fputcsv($output, ['TOTALS', '', '', $total_opening, $total_sold, $total_closing, $total_current, $total_movement]);
                }
                break;
        }
    }

    private function exportPDF(array $data, string $reportType): void {
        // For now, redirect to print view which can be saved as PDF
        $this->printReport($data, $reportType);
    }

    private function printReport(array $data, string $reportType): void {
        $pageTitle = $data['title'] . ' (Printable)';
        $template = APP_ROOT . "/views/tenant/reports/{$reportType}-print.php";
        if (!file_exists($template)) {
            $template = APP_ROOT . "/views/tenant/reports/generic-print.php";
        }
        ob_start();
        include $template;
        $content = ob_get_clean();
        include APP_ROOT . '/views/tenant/layouts/print.php';
    }

    private function taxReport(int $tid, array $filters, string $format): void {
        $page = (int)($filters['page'] ?? 1);
        $perPage = $_GET['per_page'] ?? ITEMS_PER_PAGE;
        $pp = $perPage === 'all' ? PHP_INT_MAX : (int)$perPage;
        $offset = ($page - 1) * $pp;

        $dateFrom = $filters['date_from'] ?? date('Y-m-01');
        $dateTo = $filters['date_to'] ?? date('Y-m-d');
        $locationId = $filters['location_id'] ?? '';

        // Get tax collected by tax group
        // Use subqueries to avoid duplicate rows from multi-item sales
        $sql = "
            SELECT
                COALESCE(tg.name, 'Standard Tax') as tax_group_name,
                COALESCE(tg.rate, " . (float)setting('tax_rate', 0) . ") as tax_rate,
                COALESCE(tg.type, 'percentage') as tax_type,
                COUNT(DISTINCT s.id) as sale_count,
                SUM(s.tax_amount) as total_tax_collected,
                SUM(s.subtotal) as total_sales_amount
            FROM sales s
            LEFT JOIN (
                SELECT DISTINCT si.sale_id, p.tax_group_id
                FROM sale_items si
                JOIN products p ON p.id = si.product_id
            ) si ON si.sale_id = s.id
            LEFT JOIN tax_groups tg ON tg.id = si.tax_group_id
            WHERE s.tenant_id = ?
            AND DATE(s.created_at) BETWEEN ? AND ?
            AND s.status = 'completed'
            " . ($locationId ? "AND s.location_id = ?" : "") . "
            GROUP BY tg.id, tg.name, tg.rate, tg.type
            ORDER BY total_tax_collected DESC
        ";

        $params = [$tid, $dateFrom, $dateTo];
        if ($locationId) {
            $params[] = $locationId;
        }

        $stmt = db()->prepare($sql);
        $stmt->execute($params);
        $taxData = $stmt->fetchAll();

        // Get tax breakdown by date
        $dateSql = "
            SELECT
                DATE(s.created_at) as tax_date,
                COUNT(DISTINCT s.id) as sale_count,
                SUM(s.tax_amount) as total_tax_collected,
                SUM(s.subtotal) as total_sales_amount
            FROM sales s
            WHERE s.tenant_id = ?
            AND DATE(s.created_at) BETWEEN ? AND ?
            AND s.status = 'completed'
            AND s.tax_amount > 0
            " . ($locationId ? "AND s.location_id = ?" : "") . "
            GROUP BY DATE(s.created_at)
            ORDER BY tax_date DESC
        ";

        $dateStmt = db()->prepare($dateSql);
        $dateStmt->execute($params);
        $taxByDate = $dateStmt->fetchAll();

        // Get tax breakdown by location
        $locationSql = "
            SELECT
                l.name as location_name,
                COUNT(DISTINCT s.id) as sale_count,
                SUM(s.tax_amount) as total_tax_collected,
                SUM(s.subtotal) as total_sales_amount
            FROM sales s
            LEFT JOIN locations l ON l.id = s.location_id
            WHERE s.tenant_id = ?
            AND DATE(s.created_at) BETWEEN ? AND ?
            AND s.status = 'completed'
            AND s.tax_amount > 0
            " . ($locationId ? "AND s.location_id = ?" : "") . "
            GROUP BY l.id, l.name
            ORDER BY total_tax_collected DESC
        ";

        $locationStmt = db()->prepare($locationSql);
        $locationStmt->execute($params);
        $taxByLocation = $locationStmt->fetchAll();

        // Get tax breakdown by product category
        $categorySql = "
            SELECT
                c.name as category_name,
                COUNT(DISTINCT s.id) as sale_count,
                SUM(s.tax_amount) as total_tax_collected,
                SUM(s.subtotal) as total_sales_amount
            FROM sales s
            LEFT JOIN sale_items si ON si.sale_id = s.id
            LEFT JOIN products p ON p.id = si.product_id
            LEFT JOIN categories c ON c.id = p.category_id
            WHERE s.tenant_id = ?
            AND DATE(s.created_at) BETWEEN ? AND ?
            AND s.status = 'completed'
            AND s.tax_amount > 0
            " . ($locationId ? "AND s.location_id = ?" : "") . "
            GROUP BY c.id, c.name
            ORDER BY total_tax_collected DESC
        ";

        $categoryStmt = db()->prepare($categorySql);
        $categoryStmt->execute($params);
        $taxByCategory = $categoryStmt->fetchAll();

        // Get detailed tax breakdown by sale
        $detailSql = "
            SELECT
                s.sale_no,
                s.created_at,
                c.name as customer_name,
                l.name as location_name,
                s.tax_amount,
                s.subtotal,
                s.total,
                s.payment_status,
                COALESCE(tg.name, 'Standard Tax') as tax_group_name,
                COUNT(si.id) as item_count
            FROM sales s
            LEFT JOIN customers c ON c.id = s.customer_id
            LEFT JOIN locations l ON l.id = s.location_id
            LEFT JOIN sale_items si ON si.sale_id = s.id
            LEFT JOIN products p ON p.id = si.product_id
            LEFT JOIN tax_groups tg ON tg.id = p.tax_group_id
            WHERE s.tenant_id = ?
            AND DATE(s.created_at) BETWEEN ? AND ?
            AND s.status = 'completed'
            AND s.tax_amount > 0
            " . ($locationId ? "AND s.location_id = ?" : "") . "
            GROUP BY s.id
            ORDER BY s.created_at DESC
            LIMIT {$pp} OFFSET {$offset}
        ";

        $detailStmt = db()->prepare($detailSql);
        $detailStmt->execute($params);
        $taxDetails = $detailStmt->fetchAll();

        // Get total count for pagination
        $countSql = "
            SELECT COUNT(DISTINCT s.id)
            FROM sales s
            WHERE s.tenant_id = ?
            AND DATE(s.created_at) BETWEEN ? AND ?
            AND s.status = 'completed'
            AND s.tax_amount > 0
            " . ($locationId ? "AND s.location_id = ?" : "") . "
        ";

        $countStmt = db()->prepare($countSql);
        $countStmt->execute($params);
        $total = (int)$countStmt->fetchColumn();

        $data = [
            'title' => 'Tax Report',
            'filters' => $filters,
            'summary' => $taxData,
            'tax_by_date' => $taxByDate,
            'tax_by_location' => $taxByLocation,
            'tax_by_category' => $taxByCategory,
            'details' => $taxDetails,
            'pagination' => [
                'total' => $total,
                'page' => $page,
                'last_page' => $perPage === 'all' ? 1 : (int)ceil($total / $pp)
            ],
            'total_tax_collected' => array_sum(array_column($taxData, 'total_tax_collected')),
            'total_sales' => array_sum(array_column($taxData, 'total_sales_amount'))
        ];

        $this->outputReport($data, $format, 'tax');
    }

    private function stockSummaryReport(int $tid, array $filters, string $format): void {
        $dateFrom = $filters['date_from'] ?? date('Y-m-01');
        $dateTo = $filters['date_to'] ?? date('Y-m-d');
        $locationId = $filters['location_id'] ?? '';

        // Single optimized query to get all stock data
        $stockSql = "
            SELECT
                p.id,
                p.name,
                p.sku,
                p.cost_price,
                p.selling_price,
                COALESCE((
                    SELECT SUM(CASE 
                        WHEN sm.type IN ('purchase', 'transfer_in', 'adjustment_in') OR (sm.type IN ('adjustment', 'return') AND sm.quantity > 0) THEN ABS(sm.quantity)
                        WHEN sm.type IN ('sale', 'transfer_out', 'adjustment_out') OR (sm.type IN ('adjustment', 'return') AND sm.quantity < 0) THEN -ABS(sm.quantity)
                        ELSE 0 
                    END)
                    FROM stock_movements sm
                    WHERE sm.product_id = p.id AND sm.tenant_id = p.tenant_id
                ), 0) as quantity
            FROM products p
            WHERE p.tenant_id = ?
            GROUP BY p.id, p.name, p.sku, p.cost_price, p.selling_price
            HAVING quantity > 0
            ORDER BY p.name ASC
        ";

        $stmt = db()->prepare($stockSql);
        if ($locationId) {
            $stmt->execute([$tid, $locationId]);
        } else {
            $stmt->execute([$tid]);
        }
        $stockData = $stmt->fetchAll();

        // Use the same data for opening, current, and closing (since we query current stock)
        $openingStock = array_map(function($item) {
            $val = $item['quantity'] * $item['cost_price'];
            return array_merge($item, ['opening_quantity' => $item['quantity'], 'opening_value' => $val, 'value' => $val]);
        }, $stockData);
        
        $currentStock = array_map(function($item) {
            $val = $item['quantity'] * $item['cost_price'];
            return array_merge($item, ['current_quantity' => $item['quantity'], 'current_value' => $val, 'value' => $val]);
        }, $stockData);
        
        $closingStock = array_map(function($item) {
            $val = $item['quantity'] * $item['cost_price'];
            return array_merge($item, ['closing_quantity' => $item['quantity'], 'closing_value' => $val, 'value' => $val]);
        }, $stockData);

        // Calculate totals
        $totalOpeningQty = array_sum(array_column($openingStock, 'opening_quantity'));
        $totalOpeningValue = array_sum(array_column($openingStock, 'opening_value'));
        $totalCurrentQty = array_sum(array_column($currentStock, 'current_quantity'));
        $totalCurrentValue = array_sum(array_column($currentStock, 'current_value'));
        $totalClosingQty = array_sum(array_column($closingStock, 'closing_quantity'));
        $totalClosingValue = array_sum(array_column($closingStock, 'closing_value'));

        $data = [
            'title' => 'Stock Summary Report',
            'filters' => $filters,
            'opening_stock' => $openingStock,
            'current_stock' => $currentStock,
            'closing_stock' => $closingStock,
            'totals' => [
                'opening_quantity' => $totalOpeningQty,
                'opening_value' => $totalOpeningValue,
                'current_quantity' => $totalCurrentQty,
                'current_value' => $totalCurrentValue,
                'closing_quantity' => $totalClosingQty,
                'closing_value' => $totalClosingValue
            ]
        ];

        $this->outputReport($data, $format, 'stock-summary');
    }

    private function purchaseItemsReport(int $tid, array $filters, string $format): void {
        $page = (int)($filters['page'] ?? 1);
        $perPage = $_GET['per_page'] ?? ITEMS_PER_PAGE;
        $pp = $perPage === 'all' ? PHP_INT_MAX : (int)$perPage;
        $offset = ($page - 1) * $pp;

        // Bypass cache temporarily to debug 500 error
        // $cacheKey = "purchase_items_{$tid}_{$page}_{$pp}_" . md5(serialize($filters));

        $data = (function() use ($tid, $filters, $page, $perPage, $pp, $offset) {
            $sql = "SELECT pi.id, pi.quantity, pi.unit_cost, pi.total,
                    p.id as product_id, p.name as product_name, p.sku, p.cost_price as current_cost, p.selling_price,
                    c.name as category,
                    pur.id as purchase_id, pur.purchase_no, pur.created_at as purchase_date, pur.status as purchase_status,
                    s.name as supplier, l.name as location,
                    (pi.quantity * p.selling_price) as potential_revenue,
                    (pi.quantity * (p.selling_price - pi.unit_cost)) as potential_profit
                    FROM purchase_items pi
                    JOIN purchases pur ON pur.id = pi.purchase_id
                    JOIN products p ON p.id = pi.product_id
                    LEFT JOIN categories c ON c.id = p.category_id
                    LEFT JOIN suppliers s ON s.id = pur.supplier_id
                    LEFT JOIN locations l ON l.id = pur.location_id
                    WHERE pi.tenant_id = ? AND pur.status = 'received'";

            $params = [$tid];

            if (!empty($filters['date_from'])) {
                $sql .= " AND DATE(pur.created_at) >= ?";
                $params[] = $filters['date_from'];
            }

            if (!empty($filters['date_to'])) {
                $sql .= " AND DATE(pur.created_at) <= ?";
                $params[] = $filters['date_to'];
            }

            if (!empty($filters['supplier_id'])) {
                $sql .= " AND pur.supplier_id = ?";
                $params[] = $filters['supplier_id'];
            }

            if (!empty($filters['category_id'])) {
                $sql .= " AND p.category_id = ?";
                $params[] = $filters['category_id'];
            }

            if (!empty($filters['product_id'])) {
                $sql .= " AND p.id = ?";
                $params[] = $filters['product_id'];
            }

            $sql .= " ORDER BY pur.created_at DESC";

            // Get total count
            $countSql = "SELECT COUNT(*) FROM ({$sql}) as count_table";
            $cntStmt = db()->prepare($countSql);
            $cntStmt->execute($params);
            $total = (int)$cntStmt->fetchColumn();

            // Add pagination
            $sql .= " LIMIT {$pp} OFFSET {$offset}";

            // Get paginated purchase items first
            $stmt = db()->prepare($sql);
            $stmt->execute($params);
            $purchaseItems = $stmt->fetchAll();

            // Calculate summary from paginated data (simplified approach)
            $summary = [
                'total_items' => count($purchaseItems),
                'total_quantity' => array_sum(array_column($purchaseItems, 'quantity')),
                'total_cost' => array_sum(array_column($purchaseItems, 'total')),
                'avg_unit_cost' => count($purchaseItems) > 0 ? array_sum(array_column($purchaseItems, 'unit_cost')) / count($purchaseItems) : 0,
                'total_potential_revenue' => array_sum(array_column($purchaseItems, 'potential_revenue')),
                'total_potential_profit' => array_sum(array_column($purchaseItems, 'potential_profit')),
                'unique_suppliers' => count(array_unique(array_column($purchaseItems, 'supplier'))),
                'unique_products' => count(array_unique(array_column($purchaseItems, 'product_id')))
            ];

            // Get supplier breakdown
            $supplierSql = "SELECT COALESCE(s.name, 'Direct') as supplier,
                            COUNT(DISTINCT pur.id) as purchase_count,
                            SUM(pi.quantity) as total_quantity,
                            SUM(pi.total) as total_cost,
                            AVG(pi.unit_cost) as avg_unit_cost
                            FROM purchase_items pi
                            JOIN purchases pur ON pur.id = pi.purchase_id
                            LEFT JOIN suppliers s ON s.id = pur.supplier_id
                            WHERE pi.tenant_id = ?";
            $supplierParams = [$tid];

            if (!empty($filters['date_from'])) {
                $supplierSql .= " AND DATE(pur.created_at) >= ?";
                $supplierParams[] = $filters['date_from'];
            }

            if (!empty($filters['date_to'])) {
                $supplierSql .= " AND DATE(pur.created_at) <= ?";
                $supplierParams[] = $filters['date_to'];
            }

            $supplierSql .= " GROUP BY COALESCE(s.id, 0), s.name ORDER BY total_cost DESC LIMIT 10";
            $supplierStmt = db()->prepare($supplierSql);
            $supplierStmt->execute($supplierParams);
            $topSuppliers = $supplierStmt->fetchAll();

            // Get category breakdown
            $categorySql = "SELECT COALESCE(c.name, 'Uncategorized') as category,
                            COUNT(DISTINCT pi.product_id) as product_count,
                            SUM(pi.quantity) as total_quantity,
                            SUM(pi.total) as total_cost
                            FROM purchase_items pi
                            JOIN purchases pur ON pur.id = pi.purchase_id
                            JOIN products p ON p.id = pi.product_id
                            LEFT JOIN categories c ON c.id = p.category_id
                            WHERE pi.tenant_id = ?";
            $categoryParams = [$tid];

            if (!empty($filters['date_from'])) {
                $categorySql .= " AND DATE(pur.created_at) >= ?";
                $categoryParams[] = $filters['date_from'];
            }

            if (!empty($filters['date_to'])) {
                $categorySql .= " AND DATE(pur.created_at) <= ?";
                $categoryParams[] = $filters['date_to'];
            }

            $categorySql .= " GROUP BY COALESCE(c.id, 0), c.name ORDER BY total_cost DESC";
            $categoryStmt = db()->prepare($categorySql);
            $categoryStmt->execute($categoryParams);
            $categoryBreakdown = $categoryStmt->fetchAll();

            // Get top purchased products
            $topProductsSql = "SELECT p.name as product, p.sku,
                               SUM(pi.quantity) as total_quantity,
                               SUM(pi.total) as total_cost,
                               AVG(pi.unit_cost) as avg_unit_cost,
                               COUNT(DISTINCT pur.id) as purchase_count
                               FROM purchase_items pi
                               JOIN purchases pur ON pur.id = pi.purchase_id
                               JOIN products p ON p.id = pi.product_id
                               WHERE pi.tenant_id = ?";
            $topProductsParams = [$tid];

            if (!empty($filters['date_from'])) {
                $topProductsSql .= " AND DATE(pur.created_at) >= ?";
                $topProductsParams[] = $filters['date_from'];
            }

            if (!empty($filters['date_to'])) {
                $topProductsSql .= " AND DATE(pur.created_at) <= ?";
                $topProductsParams[] = $filters['date_to'];
            }

            $topProductsSql .= " GROUP BY p.id ORDER BY total_cost DESC LIMIT 10";
            $topProductsStmt = db()->prepare($topProductsSql);
            $topProductsStmt->execute($topProductsParams);
            $topProducts = $topProductsStmt->fetchAll();

            return [
                'title' => 'Purchase Items Report',
                'filters' => $filters,
                'purchase_items' => $purchaseItems,
                'pagination' => [
                    'total' => $total,
                    'page' => $page,
                    'last_page' => $perPage === 'all' ? 1 : (int)ceil($total / $pp)
                ],
                'summary' => $summary,
                'top_suppliers' => $topSuppliers,
                'category_breakdown' => $categoryBreakdown,
                'top_products' => $topProducts
            ];
        })();

        $this->outputReport($data, $format, 'purchase-items');
    }

    private function topSuppliersReport(int $tid, array $filters, string $format): void {
        // Get supplier analysis
        $sql = "SELECT COALESCE(s.name, 'Direct') as supplier,
                COUNT(DISTINCT pur.id) as purchase_count,
                SUM(pi.quantity) as total_quantity,
                SUM(pi.total) as total_cost,
                AVG(pi.unit_cost) as avg_unit_cost,
                COUNT(DISTINCT pi.product_id) as unique_products
                FROM purchase_items pi
                JOIN purchases pur ON pur.id = pi.purchase_id
                LEFT JOIN suppliers s ON s.id = pur.supplier_id
                WHERE pi.tenant_id = ?";
        
        $params = [$tid];
        
        // Only apply date filters if they are explicitly set (not using defaults)
        if (isset($_GET['date_from']) && !empty($filters['date_from'])) {
            $sql .= " AND DATE(pur.created_at) >= ?";
            $params[] = $filters['date_from'];
        }
        
        if (isset($_GET['date_to']) && !empty($filters['date_to'])) {
            $sql .= " AND DATE(pur.created_at) <= ?";
            $params[] = $filters['date_to'];
        }
        
        if (!empty($filters['supplier_id'])) {
            $sql .= " AND pur.supplier_id = ?";
            $params[] = $filters['supplier_id'];
        }
        
        $sql .= " GROUP BY COALESCE(s.id, 0), s.name ORDER BY total_cost DESC";
        
        $stmt = db()->prepare($sql);
        $stmt->execute($params);
        $suppliers = $stmt->fetchAll();

        // Debug: Check if we have data
        if (empty($suppliers)) {
            // Try a simpler query to see if there's any data at all
            $simpleSql = "SELECT COUNT(*) as count FROM purchase_items pi WHERE pi.tenant_id = ?";
            $simpleStmt = db()->prepare($simpleSql);
            $simpleStmt->execute([$tid]);
            $count = $simpleStmt->fetchColumn();
            
            if ($count == 0) {
                // No purchase items at all
                $suppliers = [];
            } else {
                // There are purchase items but the complex query failed, try without JOIN
                $simpleSql = "SELECT 'Direct' as supplier,
                        COUNT(DISTINCT pur.id) as purchase_count,
                        SUM(pi.quantity) as total_quantity,
                        SUM(pi.total) as total_cost,
                        AVG(pi.unit_cost) as avg_unit_cost,
                        COUNT(DISTINCT pi.product_id) as unique_products
                        FROM purchase_items pi
                        JOIN purchases pur ON pur.id = pi.purchase_id
                        WHERE pi.tenant_id = ?
                        GROUP BY 'Direct'";
                $simpleStmt = db()->prepare($simpleSql);
                $simpleStmt->execute([$tid]);
                $suppliers = $simpleStmt->fetchAll();
            }
        }

        // Calculate summary
        $summary = [
            'total_suppliers' => count($suppliers),
            'total_purchases' => array_sum(array_column($suppliers, 'purchase_count')),
            'total_items' => array_sum(array_column($suppliers, 'total_quantity')),
            'total_cost' => array_sum(array_column($suppliers, 'total_cost')),
            'average_purchase' => count($suppliers) > 0 ? array_sum(array_column($suppliers, 'total_cost')) / count($suppliers) : 0,
            'avg_unit_cost' => count($suppliers) > 0 ? array_sum(array_column($suppliers, 'avg_unit_cost')) / count($suppliers) : 0
        ];

        $data = [
            'title' => 'Top Suppliers by Cost',
            'filters' => $filters,
            'suppliers' => $suppliers,
            'summary' => $summary
        ];
        
        $this->outputReport($data, $format, 'top-suppliers');
    }

    private function purchasePayDueReport(int $tid, array $filters, string $format): void {
        $page = (int)($_GET['page'] ?? 1);
        $perPage = $_GET['per_page'] ?? ITEMS_PER_PAGE;
        $pp = $perPage === 'all' ? PHP_INT_MAX : (int)$perPage;
        $off = ($page - 1) * $pp;

        // Build query for purchases with due payments
        $sql = "SELECT p.id, p.purchase_no, p.created_at, p.supplier_id, p.location_id,
                p.subtotal, p.tax_amount, p.discount_amount, p.total, p.amount_paid,
                p.payment_method, p.payment_status, p.status, p.note,
                s.name as supplier_name, l.name as location_name
                FROM purchases p
                INNER JOIN suppliers s ON s.id = p.supplier_id
                LEFT JOIN locations l ON l.id = p.location_id
                WHERE p.tenant_id = ? AND p.payment_status IN ('due', 'partial', 'credit')";

        $params = [$tid];

        if (!empty($filters['date_from'])) {
            $sql .= " AND DATE(p.created_at) >= ?";
            $params[] = $filters['date_from'];
        }

        if (!empty($filters['date_to'])) {
            $sql .= " AND DATE(p.created_at) <= ?";
            $params[] = $filters['date_to'];
        }

        if (!empty($filters['supplier_id'])) {
            $sql .= " AND p.supplier_id = ?";
            $params[] = $filters['supplier_id'];
        }

        if (!empty($filters['location_id'])) {
            $sql .= " AND p.location_id = ?";
            $params[] = $filters['location_id'];
        }

        // Get total count
        $countSql = str_replace("SELECT p.id, p.purchase_no, p.created_at, p.supplier_id, p.location_id,
                p.subtotal, p.tax_amount, p.discount_amount, p.total, p.amount_paid,
                p.payment_method, p.payment_status, p.status, p.note,
                s.name as supplier_name, l.name as location_name", "SELECT COUNT(*)", $sql);
        $countStmt = db()->prepare($countSql);
        $countStmt->execute($params);
        $total = (int)$countStmt->fetchColumn();

        // Get paginated data
        $sql .= " ORDER BY p.created_at DESC LIMIT {$pp} OFFSET {$off}";
        $stmt = db()->prepare($sql);
        $stmt->execute($params);
        $purchases = $stmt->fetchAll();

        // Calculate due amount for each purchase
        foreach ($purchases as &$purchase) {
            $purchase['due_amount'] = $purchase['total'] - $purchase['amount_paid'];
        }

        // Calculate summary
        $summary = [
            'total_purchases' => count($purchases),
            'total_amount_due' => array_sum(array_column($purchases, 'due_amount')),
            'total_amount_paid' => array_sum(array_column($purchases, 'amount_paid')),
            'total_purchase_value' => array_sum(array_column($purchases, 'total')),
            'due_count' => count(array_filter($purchases, fn($p) => $p['payment_status'] === 'due')),
            'partial_count' => count(array_filter($purchases, fn($p) => $p['payment_status'] === 'partial')),
            'credit_count' => count(array_filter($purchases, fn($p) => $p['payment_status'] === 'credit'))
        ];

        $data = [
            'title' => 'Pay Due to Stock Purchases',
            'filters' => $filters,
            'purchases' => $purchases,
            'pagination' => [
                'total' => $total,
                'page' => $page,
                'last_page' => $perPage === 'all' ? 1 : (int)ceil($total / $pp)
            ],
            'summary' => $summary
        ];

        $this->outputReport($data, $format, 'purchase-pay-due');
    }
}
