<?php
class DashboardController {
    public function index(): void {
        Auth::requireLogin();
        Auth::requireActiveTenant();
        $tenantId = Auth::tenantId();

        // Today's sales - convert UTC timestamps to user's timezone (UTC+1)
        $userTimezone = '+01:00';
        $today = date('Y-m-d');

        $todaySales = db()->prepare(
            "SELECT COUNT(*) as count, COALESCE(SUM(total),0) as revenue
             FROM sales WHERE tenant_id=? AND DATE(CONVERT_TZ(created_at, '+00:00', ?)) = ? AND status='completed'"
        );
        $todaySales->execute([$tenantId, $userTimezone, $today]);
        $todaySales = $todaySales->fetch();

        // Today's purchases
        $todayPurchases = db()->prepare(
            "SELECT COUNT(*) as count, COALESCE(SUM(total),0) as total
             FROM purchases WHERE tenant_id=? AND DATE(CONVERT_TZ(created_at, '+00:00', ?)) = ? AND status='received'"
        );
        $todayPurchases->execute([$tenantId, $userTimezone, $today]);
        $todayPurchases = $todayPurchases->fetch();

        // This month's sales
        $monthStart = date('Y-m-01');
        $monthEnd = date('Y-m-t');
        
        $monthSales = db()->prepare(
            "SELECT COUNT(*) as count, COALESCE(SUM(total),0) as revenue
             FROM sales WHERE tenant_id=? AND DATE(CONVERT_TZ(created_at, '+00:00', ?)) BETWEEN ? AND ? AND status='completed'"
        );
        $monthSales->execute([$tenantId, $userTimezone, $monthStart, $monthEnd]);
        $monthSales = $monthSales->fetch();

        // Total products
        $totalProducts = db()->prepare("SELECT COUNT(*) FROM products WHERE tenant_id=? AND is_active=1");
        $totalProducts->execute([$tenantId]);
        $totalProducts = (int)$totalProducts->fetchColumn();

        // Low stock products
        $lowStockCount = db()->prepare(
            "SELECT COUNT(*) FROM (
               SELECT p.id, COALESCE(SUM(s.quantity),0) as total, p.alert_qty
               FROM products p LEFT JOIN stock s ON s.product_id=p.id AND s.tenant_id=p.tenant_id
               WHERE p.tenant_id=? AND p.is_active=1 GROUP BY p.id HAVING total <= p.alert_qty
             ) x"
        );
        $lowStockCount->execute([$tenantId]);
        $lowStockCount = (int)$lowStockCount->fetchColumn();

        // Expiring soon (include expired and expiring within 30 days)
        $expiringCount = db()->prepare(
            "SELECT COUNT(*) FROM batches WHERE tenant_id=? AND expiry_date IS NOT NULL AND (expiry_date < CURDATE() OR expiry_date <= DATE_ADD(CURDATE(),INTERVAL 30 DAY))"
        );
        $expiringCount->execute([$tenantId]);
        $expiringCount = (int)$expiringCount->fetchColumn();

        // Total customer due (sum of due amounts from sales with partial or due payment status)
        $totalCustomerDue = db()->prepare(
            "SELECT COALESCE(SUM(CASE WHEN payment_status = 'due' THEN total ELSE (total - amount_paid) END), 0) as due_amount
             FROM sales WHERE tenant_id=? AND status='completed' AND payment_status IN ('due', 'partial')"
        );
        $totalCustomerDue->execute([$tenantId]);
        $totalCustomerDue = (float)$totalCustomerDue->fetchColumn();

        // Total supplier due (sum of due amounts from purchases with partial or due payment status)
        $totalSupplierDue = db()->prepare(
            "SELECT COALESCE(SUM(CASE WHEN payment_status = 'due' THEN total ELSE (total - amount_paid) END), 0) as due_amount
             FROM purchases WHERE tenant_id=? AND status='received' AND payment_status IN ('due', 'partial')"
        );
        $totalSupplierDue->execute([$tenantId]);
        $totalSupplierDue = (float)$totalSupplierDue->fetchColumn();

        // Actual stock value (total value of all stock based on cost price)
        $stockValue = db()->prepare(
            "SELECT COALESCE(SUM(s.quantity * p.cost_price), 0) as total_value
             FROM stock s
             JOIN products p ON p.id = s.product_id
             WHERE s.tenant_id=? AND p.tenant_id=? AND s.quantity > 0"
        );
        $stockValue->execute([$tenantId, $tenantId]);
        $stockValue = (float)$stockValue->fetchColumn();

        // Stock value at retail price
        $stockValueRetail = db()->prepare(
            "SELECT COALESCE(SUM(s.quantity * p.selling_price), 0) as total_value
             FROM stock s
             JOIN products p ON p.id = s.product_id
             WHERE s.tenant_id=? AND p.tenant_id=? AND s.quantity > 0"
        );
        $stockValueRetail->execute([$tenantId, $tenantId]);
        $stockValueRetail = (float)$stockValueRetail->fetchColumn();

        // Total sales count (all-time)
        $totalSalesCount = db()->prepare(
            "SELECT COUNT(*) FROM sales WHERE tenant_id=? AND status='completed'"
        );
        $totalSalesCount->execute([$tenantId]);
        $totalSalesCount = (int)$totalSalesCount->fetchColumn();

        // Total revenue (all-time)
        $totalRevenue = db()->prepare(
            "SELECT COALESCE(SUM(total), 0) FROM sales WHERE tenant_id=? AND status='completed'"
        );
        $totalRevenue->execute([$tenantId]);
        $totalRevenue = (float)$totalRevenue->fetchColumn();

        // Weekly revenue (last 7 days)
        $weeklyRevenue = db()->prepare(
            "SELECT DATE(created_at) as date, COALESCE(SUM(total),0) as revenue
             FROM sales WHERE tenant_id=? AND DATE(created_at) >= DATE_SUB(CURDATE(),INTERVAL 6 DAY)
               AND status='completed'
             GROUP BY DATE(created_at) ORDER BY date ASC"
        );
        $weeklyRevenue->execute([$tenantId]);
        $weeklyRevenue = $weeklyRevenue->fetchAll();

        // Ensure weeklyRevenue is always an array
        if (!is_array($weeklyRevenue)) {
            $weeklyRevenue = [];
        }

        // Recent sales (last 5)
        $recentSales = db()->prepare(
            "SELECT s.*,c.name as customer_name FROM sales s
             LEFT JOIN customers c ON c.id=s.customer_id
             WHERE s.tenant_id=? AND s.status='completed'
             ORDER BY s.created_at DESC LIMIT 5"
        );
        $recentSales->execute([$tenantId]);
        $recentSales = $recentSales->fetchAll();

        // Low stock products list
        $lowStockItems = db()->prepare(
            "SELECT p.name,p.sku,p.alert_qty,COALESCE(SUM(s.quantity),0) as total
             FROM products p LEFT JOIN stock s ON s.product_id=p.id AND s.tenant_id=p.tenant_id
             WHERE p.tenant_id=? AND p.is_active=1 GROUP BY p.id HAVING total <= p.alert_qty
             ORDER BY total ASC LIMIT 6"
        );
        $lowStockItems->execute([$tenantId]);
        $lowStockItems = $lowStockItems->fetchAll();

        // Expiring products list (include expired and expiring within 90 days)
        $expiringItems = db()->prepare(
            "SELECT b.id as batch_id, b.batch_no, b.expiry_date, p.name as product, p.sku,
                    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,
                    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,
                    DATEDIFF(b.expiry_date, CURDATE()) as days_to_expiry
             FROM batches b
             JOIN products p ON p.id = b.product_id
             WHERE b.tenant_id=? AND b.expiry_date IS NOT NULL
             AND (b.expiry_date < CURDATE() OR b.expiry_date <= DATE_ADD(CURDATE(), INTERVAL 90 DAY))
             AND (
                 (SELECT SUM(s2.quantity) FROM stock s2 WHERE s2.batch_id = b.id AND s2.tenant_id = b.tenant_id) > 0
                 OR
                 (SELECT SUM(s3.quantity) FROM stock s3 WHERE s3.product_id = p.id AND s3.tenant_id = p.tenant_id) > 0
             )
             ORDER BY b.expiry_date ASC LIMIT 6"
        );
        $expiringItems->execute([$tenantId]);
        $expiringItems = $expiringItems->fetchAll();

        // Notifications
        $notifCount = NotificationModel::unreadCount();
        $subscription = PlanModel::getActiveSubscription($tenantId);
        $usage = PlanGuard::getUsage($tenantId);
        
        // Pass today's date to view for stat card links
        $todayDate = $today;

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