<?php
// ============================================================
//  MJPOS – DashboardController
// ============================================================

class DashboardController extends Controller
{
    public function index(): void
    {
        Auth::requireLogin();
        Auth::requirePermission('dashboard.view');

        if (Auth::role() === 'cashier') {
            header('Location: ' . APP_URL . '/pos'); exit;
        }

        $today    = date('Y-m-d');
        $monthStart = date('Y-m-01');
        $lastMonthStart = date('Y-m-01', strtotime('first day of last month'));
        $lastMonthEnd   = date('Y-m-t',  strtotime('last day of last month'));

        // ── Today's KPIs ─────────────────────────────────────
        $todaySales = Database::fetchOne(
            "SELECT COUNT(*) AS invoice_count,
                    COALESCE(SUM(total),0)   AS gross,
                    COALESCE(SUM(paid),0)    AS paid,
                    COALESCE(SUM(balance),0) AS balance
             FROM sales WHERE DATE(sale_date) = ? AND status = 'completed'",
            [$today]
        );
        $todayProfit = (float) Database::fetchColumn(
            "SELECT COALESCE(SUM(si.quantity*(si.selling_price-si.cost_price)-si.discount),0)
             FROM sale_items si JOIN sales s ON s.id=si.sale_id
             WHERE DATE(s.sale_date)=? AND s.status='completed'", [$today]
        );
        $todayExpenses  = (float) Database::fetchColumn("SELECT COALESCE(SUM(amount),0) FROM expenses WHERE expense_date=?", [$today]);
        $todayPurchases = (float) Database::fetchColumn("SELECT COALESCE(SUM(total),0) FROM purchases WHERE purchase_date=? AND status='received'", [$today]);

        // ── Month-to-date ─────────────────────────────────────
        $mtdSales = (float) Database::fetchColumn(
            "SELECT COALESCE(SUM(total),0) FROM sales WHERE sale_date BETWEEN ? AND ? AND status='completed'",
            [$monthStart, $today]
        );
        $lastMonthSales = (float) Database::fetchColumn(
            "SELECT COALESCE(SUM(total),0) FROM sales WHERE sale_date BETWEEN ? AND ? AND status='completed'",
            [$lastMonthStart, $lastMonthEnd]
        );
        $mtdChange = $lastMonthSales > 0 ? round(($mtdSales - $lastMonthSales) / $lastMonthSales * 100, 1) : 0;

        // ── Alerts ────────────────────────────────────────────
        $lowStockCount  = (int) Database::fetchColumn(
            "SELECT COUNT(*) FROM products WHERE status='active' AND current_stock > 0 AND current_stock <= reorder_level AND reorder_level > 0"
        );
        $outStockCount  = (int) Database::fetchColumn("SELECT COUNT(*) FROM products WHERE status='active' AND current_stock<=0");
        $pendingCheques = (int) Database::fetchColumn("SELECT COUNT(*) FROM cheque_tracking WHERE status='pending'");
        $overdueCheques = (int) Database::fetchColumn("SELECT COUNT(*) FROM cheque_tracking WHERE status='pending' AND cheque_date < CURDATE()");
        $chequePending  = (float) Database::fetchColumn("SELECT COALESCE(SUM(amount),0) FROM cheque_tracking WHERE status='pending'");
        $customerDues   = (float) Database::fetchColumn("SELECT COALESCE(SUM(current_balance),0) FROM customers WHERE status='active' AND current_balance>0");
        $supplierDues   = (float) Database::fetchColumn("SELECT COALESCE(SUM(current_balance),0) FROM suppliers WHERE status='active' AND current_balance>0");

        // Expiry alerts
        $expiredCount = 0; $expiringSoon = 0;
        try {
            $expiredCount = (int) Database::fetchColumn("SELECT COUNT(*) FROM products WHERE status='active' AND has_expiry=1 AND expiry_date < CURDATE()");
            $expiringSoon = (int) Database::fetchColumn("SELECT COUNT(*) FROM products WHERE status='active' AND has_expiry=1 AND expiry_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(),INTERVAL 30 DAY)");
        } catch (\Exception $e) {}

        // ── Accounting Summary ────────────────────────────────
        $cashBankAccounts = [];
        $totalCashBank    = 0.0;
        try {
            $cashBankAccounts = Database::fetchAll(
                "SELECT account_name, account_type, current_balance
                 FROM finance_accounts WHERE status='active'
                 ORDER BY account_type, account_name"
            );
            $totalCashBank = array_sum(array_column($cashBankAccounts, 'current_balance'));
        } catch (\Exception $e) {}

        // MTD purchases & expenses (for net profit snapshot)
        $mtdPurchases = (float) Database::fetchColumn(
            "SELECT COALESCE(SUM(total),0) FROM purchases WHERE purchase_date BETWEEN ? AND ? AND status='received'",
            [$monthStart, $today]
        );
        $mtdExpenses = (float) Database::fetchColumn(
            "SELECT COALESCE(SUM(amount),0) FROM expenses WHERE expense_date BETWEEN ? AND ?",
            [$monthStart, $today]
        );
        $mtdNetProfit = $mtdSales - $mtdPurchases - $mtdExpenses;

        // Receivables / Payables (mirrors customerDues / supplierDues but named for accounting widget)
        $totalReceivables = $customerDues;
        $totalPayables    = $supplierDues;

        // ── Summary counts ────────────────────────────────────
        $totalProducts  = (int) Database::fetchColumn("SELECT COUNT(*) FROM products  WHERE status='active'");
        $totalCustomers = (int) Database::fetchColumn("SELECT COUNT(*) FROM customers WHERE status='active'");
        $totalSuppliers = (int) Database::fetchColumn("SELECT COUNT(*) FROM suppliers WHERE status='active'");

        // ── Last 7 days trend ─────────────────────────────────
        $weekTrend = Database::fetchAll(
            "SELECT DATE(sale_date) AS day,
                    COALESCE(SUM(total),0) AS total,
                    COALESCE(SUM(paid),0)  AS paid,
                    COUNT(*)               AS count
             FROM sales
             WHERE sale_date >= DATE_SUB(CURDATE(), INTERVAL 6 DAY)
               AND status = 'completed'
             GROUP BY day ORDER BY day ASC"
        );

        // ── Top 5 products (30 days) ──────────────────────────
        $topProducts = Database::fetchAll(
            "SELECT p.product_name, SUM(si.quantity) AS qty_sold, SUM(si.total) AS revenue
             FROM sale_items si
             JOIN sales s   ON s.id  = si.sale_id
             JOIN products p ON p.id = si.product_id
             WHERE s.sale_date >= DATE_SUB(NOW(), INTERVAL 30 DAY) AND s.status='completed'
             GROUP BY si.product_id ORDER BY qty_sold DESC LIMIT 5"
        );

        // ── Monthly sales (last 6 months) ─────────────────────
        $monthlySales = Database::fetchAll(
            "SELECT DATE_FORMAT(sale_date,'%b %Y') AS month_label,
                    DATE_FORMAT(sale_date,'%Y-%m')  AS month_key,
                    SUM(total) AS total, SUM(paid) AS paid
             FROM sales
             WHERE sale_date >= DATE_SUB(NOW(), INTERVAL 6 MONTH) AND status='completed'
             GROUP BY month_key ORDER BY month_key ASC"
        );

        // ── Recent sales ──────────────────────────────────────
        $recentSales = Database::fetchAll(
            "SELECT s.id, s.invoice_no, s.sale_date, s.total, s.payment_status,
                    COALESCE(c.customer_name,'Walk-in') AS customer_name,
                    u.name AS cashier_name
             FROM sales s
             LEFT JOIN customers c ON c.id = s.customer_id
             LEFT JOIN users     u ON u.id = s.created_by
             WHERE s.status = 'completed'
             ORDER BY s.created_at DESC LIMIT 8"
        );

        // ── Cashier summary today ─────────────────────────────
        $cashierSales = Database::fetchAll(
            "SELECT u.name, COUNT(s.id) AS bills, SUM(s.total) AS total
             FROM sales s JOIN users u ON u.id = s.created_by
             WHERE DATE(s.sale_date)=? AND s.status='completed'
             GROUP BY s.created_by ORDER BY total DESC",
            [$today]
        );


        // ── Today Attendance ─────────────────────────────────
        $todayAtt = ['present'=>0,'absent'=>0,'late'=>0,'half_day'=>0,'leave'=>0,'total'=>0];
        $attList  = [];
        try {
            $attSummary = Database::fetchOne(
                "SELECT
                   COUNT(CASE WHEN ea.status='present'  THEN 1 END) AS present,
                   COUNT(CASE WHEN ea.status='absent'   THEN 1 END) AS absent,
                   COUNT(CASE WHEN ea.status='late'     THEN 1 END) AS late,
                   COUNT(CASE WHEN ea.status='half_day' THEN 1 END) AS half_day,
                   COUNT(CASE WHEN ea.status='leave'    THEN 1 END) AS leave_days
                 FROM employee_attendance ea
                 WHERE ea.att_date = ?", [$today]
            );
            if ($attSummary) {
                $todayAtt['present']  = (int)$attSummary['present'];
                $todayAtt['absent']   = (int)$attSummary['absent'];
                $todayAtt['late']     = (int)$attSummary['late'];
                $todayAtt['half_day'] = (int)$attSummary['half_day'];
                $todayAtt['leave']    = (int)$attSummary['leave_days'];
            }
            $todayAtt['total'] = (int)Database::fetchColumn(
                "SELECT COUNT(*) FROM employees WHERE status='active'"
            );
            $attList = Database::fetchAll(
                "SELECT e.full_name, e.designation, e.fingerprint_id,
                        ea.check_in, ea.check_out, ea.status, ea.method, ea.ot_hours
                 FROM employee_attendance ea
                 JOIN employees e ON e.id = ea.employee_id
                 WHERE ea.att_date = ? AND ea.status != 'off'
                 ORDER BY ea.status ASC, ea.check_in ASC
                 LIMIT 15", [$today]
            );
        } catch (\Exception $e) {
            $todayAtt = ['present'=>0,'absent'=>0,'late'=>0,'half_day'=>0,'leave'=>0,'total'=>0];
        }

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