<?php
// ============================================================
//  MJPOS – Front Controller  (public/index.php)
// ============================================================

require_once __DIR__ . '/../config/config.php';
require_once APP_PATH . '/core/Database.php';
require_once APP_PATH . '/core/Security.php';
require_once APP_PATH . '/core/Router.php';
require_once APP_PATH . '/core/Auth.php';

// LogController — inline
if (!class_exists('LogController')) {
class LogController extends Controller
{
    private function requireSuperAdmin(): void
    {
        Auth::requireLogin();
        if (Auth::role() !== 'super_admin') {
            http_response_code(403);
            $pageTitle = 'Access Denied';
            require APP_PATH . '/views/errors/403.php';
            exit;
        }
    }

    // ── Activity / Audit Logs ────────────────────────────────
    public function auditLogs(): void
    {
        Auth::requirePermission('users.view');

        $from    = $this->get('from',   date('Y-m-d'));
        $to      = $this->get('to',     date('Y-m-d'));
        $module  = Security::strip($this->get('module', ''));
        $userId  = (int)$this->get('user', 0);
        $search  = Security::strip($this->get('search', ''));
        $page    = max(1, (int)$this->get('page', 1));
        $perPage = 50;

        $where  = ['al.created_at BETWEEN ? AND ?'];
        $params = [$from . ' 00:00:00', $to . ' 23:59:59'];

        if ($module) { $where[] = 'al.module = ?';           $params[] = $module; }
        if ($userId) { $where[] = 'al.user_id = ?';          $params[] = $userId; }
        if ($search) { $where[] = 'al.description LIKE ?';   $params[] = "%$search%"; }

        $total = (int) Database::fetchColumn(
            'SELECT COUNT(*) FROM audit_logs al WHERE ' . implode(' AND ', $where),
            $params
        );

        $offset = ($page - 1) * $perPage;
        $logs = Database::fetchAll(
            "SELECT al.*, u.name AS user_name, u.username
             FROM audit_logs al
             LEFT JOIN users u ON u.id = al.user_id
             WHERE " . implode(' AND ', $where) . "
             ORDER BY al.created_at DESC
             LIMIT {$perPage} OFFSET {$offset}",
            $params
        );

        // Stats
        $stats = Database::fetchAll(
            "SELECT module, COUNT(*) AS cnt
             FROM audit_logs
             WHERE created_at BETWEEN ? AND ?
             GROUP BY module ORDER BY cnt DESC",
            [$from . ' 00:00:00', $to . ' 23:59:59']
        );

        $modules = Database::fetchAll(
            'SELECT DISTINCT module FROM audit_logs ORDER BY module'
        );
        $users = Database::fetchAll(
            "SELECT DISTINCT u.id, u.name FROM audit_logs al
             JOIN users u ON u.id = al.user_id
             ORDER BY u.name"
        );

        $pages      = (int)ceil($total / $perPage);
        $pageTitle  = 'Activity Logs';
        $breadcrumb = [['label' => 'Admin'], ['label' => 'Activity Logs']];
        ob_start();
        require APP_PATH . '/views/logs/audit.php';
        $content = ob_get_clean();
        require APP_PATH . '/views/layouts/main.php';
    }

    // ── Online / Active Users ────────────────────────────────
    public function onlineUsers(): void
    {
        Auth::requirePermission('users.view');

        // 3-tier status: online (<2min), away (<15min)
        $onlineUsers = Database::fetchAll(
            "SELECT u.*, r.role_name,
                    COALESCE(b.name, 'Main Branch') AS branch_name,
                    TIMESTAMPDIFF(SECOND, u.last_active, NOW()) AS seconds_ago,
                    CASE
                      WHEN u.last_active >= DATE_SUB(NOW(), INTERVAL 2 MINUTE)  THEN 'online'
                      WHEN u.last_active >= DATE_SUB(NOW(), INTERVAL 15 MINUTE) THEN 'away'
                      ELSE 'offline'
                    END AS online_status
             FROM users u
             JOIN roles r ON r.id = u.role_id
             LEFT JOIN branches b ON b.id = u.branch_id
             WHERE u.last_active >= DATE_SUB(NOW(), INTERVAL 15 MINUTE)
               AND u.status = 'active'
             ORDER BY u.last_active DESC"
        );

        // Recent logins (last 24h)
        $recentLogins = Database::fetchAll(
            "SELECT al.*, u.name AS user_name, u.username, r.role_name
             FROM audit_logs al
             JOIN users u ON u.id = al.user_id
             JOIN roles r ON r.id = u.role_id
             WHERE al.action = 'login'
               AND al.created_at >= DATE_SUB(NOW(), INTERVAL 24 HOUR)
             ORDER BY al.created_at DESC
             LIMIT 50"
        );

        // Failed login attempts (last 24h)
        $failedLogins = Database::fetchAll(
            "SELECT al.*, al.ip_address, al.description
             FROM audit_logs al
             WHERE al.action = 'login_failed'
               AND al.created_at >= DATE_SUB(NOW(), INTERVAL 24 HOUR)
             ORDER BY al.created_at DESC
             LIMIT 50"
        );

        // Login stats
        $loginStats = Database::fetchOne(
            "SELECT
               COUNT(CASE WHEN action='login'        THEN 1 END) AS total_logins,
               COUNT(CASE WHEN action='login_failed' THEN 1 END) AS failed_logins,
               COUNT(CASE WHEN action='logout'       THEN 1 END) AS logouts
             FROM audit_logs
             WHERE created_at >= DATE_SUB(NOW(), INTERVAL 24 HOUR)"
        );

        $onlineCount = count($onlineUsers);

        $pageTitle  = 'Online Users';
        $breadcrumb = [['label' => 'Admin'], ['label' => 'Online Users']];
        ob_start();
        require APP_PATH . '/views/logs/online.php';
        $content = ob_get_clean();
        require APP_PATH . '/views/layouts/main.php';
    }

    // ── Real-time JSON status endpoint ──────────────────────
    public function onlineStatus(): void
    {
        Auth::requireLogin();
        if (Auth::role() !== 'super_admin') {
            header('Content-Type: application/json');
            echo json_encode(['error' => 'Forbidden']);
            exit;
        }

        header('Content-Type: application/json');
        header('Cache-Control: no-cache, no-store, must-revalidate');

        $online = Database::fetchAll(
            "SELECT u.id, u.name, u.username, r.role_name,
                    b.name AS branch_name,
                    u.last_active, u.last_login,
                    TIMESTAMPDIFF(SECOND, u.last_active, NOW()) AS seconds_ago,
                    CASE
                      WHEN u.last_active >= DATE_SUB(NOW(), INTERVAL 2 MINUTE)  THEN 'online'
                      WHEN u.last_active >= DATE_SUB(NOW(), INTERVAL 15 MINUTE) THEN 'away'
                      ELSE 'offline'
                    END AS online_status
             FROM users u
             JOIN roles r ON r.id = u.role_id
             LEFT JOIN branches b ON b.id = u.branch_id
             WHERE u.last_active >= DATE_SUB(NOW(), INTERVAL 15 MINUTE)
               AND u.status = 'active'
             ORDER BY u.last_active DESC"
        );

        $failed = (int) Database::fetchColumn(
            "SELECT COUNT(*) FROM audit_logs
             WHERE action = 'login_failed'
               AND created_at >= DATE_SUB(NOW(), INTERVAL 24 HOUR)"
        );

        $logins = (int) Database::fetchColumn(
            "SELECT COUNT(*) FROM audit_logs
             WHERE action = 'login'
               AND created_at >= DATE_SUB(NOW(), INTERVAL 24 HOUR)"
        );

        // Latest event (for live feed)
        $latestEvent = Database::fetchOne(
            "SELECT al.action, al.description, al.created_at,
                    al.ip_address, u.name AS user_name
             FROM audit_logs al
             LEFT JOIN users u ON u.id = al.user_id
             ORDER BY al.id DESC LIMIT 1"
        );

        echo json_encode([
            'online'       => $online,
            'online_count' => count($online),
            'failed_24h'   => $failed,
            'logins_24h'   => $logins,
            'latest_event' => $latestEvent,
            'server_time'  => date('H:i:s'),
            'timestamp'    => time(),
        ]);
        exit;
    }

}
}

// ReportController — inline
if (!class_exists('ReportController')) {
class ReportController extends Controller
{
    public function sales(): void
    {
        Auth::requirePermission('reports.view');

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

        // Overall summary per cashier
        $rows = Database::fetchAll(
            "SELECT u.id AS cashier_id, u.name,
                    COUNT(DISTINCT s.id)                                                            AS bills,
                    COALESCE(SUM(s.total),0)                                                       AS total,
                    COALESCE(SUM(s.paid),0)                                                        AS paid,
                    COALESCE(SUM(s.balance),0)                                                     AS balance,
                    COALESCE(SUM(s.discount),0)                                                    AS discount,
                    COALESCE(SUM(si.quantity * (si.selling_price - si.cost_price)),0)              AS gross_profit,
                    COUNT(DISTINCT CASE WHEN s.payment_status != 'paid' THEN s.id END)             AS due_count,
                    MAX(s.sale_date)                                                                AS last_sale
             FROM sales s
             JOIN users u       ON u.id    = s.created_by
             JOIN sale_items si ON si.sale_id = s.id
             WHERE " . implode(' AND ', $where) . "
             GROUP BY s.created_by ORDER BY total DESC",
            $params
        );

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

// StiReportController — inline
if (!class_exists('StiReportController')) {
// ============================================================
//  MJPOS – Stimulsoft Report Controller
//  Requires: composer require stimulsoft/reports-php
//  Docs:     https://www.stimulsoft.com/en/documentation/online/programming-manual/
// ============================================================

class StiReportController extends Controller
{
    // ── Available reports ────────────────────────────────────
    private array $reportList = [
        'sales'            => ['title' => 'Sales Report',            'icon' => 'bi-receipt',           'color' => 'primary'],
        'profit_loss'      => ['title' => 'Profit & Loss',           'icon' => 'bi-graph-up-arrow',    'color' => 'success'],
        'stock'            => ['title' => 'Stock Value Report',      'icon' => 'bi-boxes',             'color' => 'warning'],
        'purchases'        => ['title' => 'Purchase Report',         'icon' => 'bi-truck',             'color' => 'info'],
        'expenses'         => ['title' => 'Expense Report',          'icon' => 'bi-wallet2',           'color' => 'danger'],
        'customers'        => ['title' => 'Customer Due Report',     'icon' => 'bi-people',            'color' => 'secondary'],
        'suppliers'        => ['title' => 'Supplier Due Report',     'icon' => 'bi-shop',              'color' => 'dark'],
        'credit_customers' => ['title' => 'Credit Customers',        'icon' => 'bi-journal-bookmark',  'color' => 'warning'],
        'tax'              => ['title' => 'Tax Report',              'icon' => 'bi-percent',           'color' => 'primary'],
        'cashier'          => ['title' => 'Cashier Performance',     'icon' => 'bi-person-badge',      'color' => 'info'],
    ];

    // ── Report Hub ───────────────────────────────────────────
    public function index(): void
    {
        Auth::requirePermission('reports.view');

        $reports = $this->reportList;

        $pageTitle  = 'Stimulsoft Reports';
        $breadcrumb = [['label' => 'Reports'], ['label' => 'Stimulsoft']];
        ob_start();
        require APP_PATH . '/views/sti/index.php';
        $content = ob_get_clean();
        require APP_PATH . '/views/layouts/main.php';
    }

    // ── Report Viewer ────────────────────────────────────────
    public function show(string $type): void
    {
        Auth::requirePermission('reports.view');

        if (!array_key_exists($type, $this->reportList)) {
            redirectWith(APP_URL . '/sti-reports', 'error', 'Report not found.');
            return;
        }

        $from   = $this->get('from',   date('Y-m-01'));
        $to     = $this->get('to',     date('Y-m-d'));
        $report = $this->reportList[$type];

        $pageTitle  = $report['title'];
        $breadcrumb = [
            ['label' => 'Reports'],
            ['label' => 'Stimulsoft', 'url' => APP_URL . '/sti-reports'],
            ['label' => $report['title']],
        ];
        ob_start();
        require APP_PATH . '/views/sti/viewer.php';
        $content = ob_get_clean();
        require APP_PATH . '/views/layouts/main.php';
    }

    // ── Data endpoint (JSON for Stimulsoft) ──────────────────
    public function data(string $type): void
    {
        Auth::requireLogin();
        if (!Auth::can('reports.view')) {
            http_response_code(403);
            echo json_encode(['error' => 'Forbidden']);
            exit;
        }

        header('Content-Type: application/json');
        header('Cache-Control: no-cache');

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

        $shopName    = setting('shop_name',    'MJPOS System');
        $shopAddress = setting('shop_address', '');
        $shopPhone   = setting('shop_phone',   '');
        $currency    = setting('currency_symbol', 'Rs.');

        $meta = [
            'shop_name'    => $shopName,
            'shop_address' => $shopAddress,
            'shop_phone'   => $shopPhone,
            'currency'     => $currency,
            'from'         => $from,
            'to'           => $to,
            'generated_by' => Auth::name(),
            'generated_at' => date('d M Y, H:i'),
        ];

        $data = match($type) {
            'sales'            => $this->dataSales($p1, $from, $to),
            'profit_loss'      => $this->dataProfitLoss($p1, $from, $to),
            'stock'            => $this->dataStock(),
            'purchases'        => $this->dataPurchases($from, $to),
            'expenses'         => $this->dataExpenses($from, $to),
            'customers'        => $this->dataCustomers(),
            'suppliers'        => $this->dataSuppliers(),
            'credit_customers' => $this->dataCreditCustomers(),
            'tax'              => $this->dataTax($p1, $from, $to),
            'cashier'          => $this->dataCashier($p1, $from, $to),
            default            => ['error' => 'Unknown report type'],
        };

        echo json_encode(['meta' => $meta, 'data' => $data]);
        exit;
    }

    // ── Data Providers ───────────────────────────────────────

    private function dataSales(array $p1, string $from, string $to): array
    {
        $rows = Database::fetchAll(
            "SELECT DATE_FORMAT(sale_date,'%Y-%m-%d') AS period,
                    COUNT(*) AS invoices,
                    SUM(subtotal) AS subtotal,
                    SUM(discount) AS discount,
                    SUM(tax) AS tax,
                    SUM(total) AS total,
                    SUM(paid) AS paid,
                    SUM(balance) AS balance
             FROM sales
             WHERE sale_date BETWEEN ? AND ? AND status = 'completed'
             GROUP BY period ORDER BY period ASC", $p1
        );

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

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

        return [
            'summary'  => $totals,
            'by_day'   => $rows,
            'payments' => $pmtBreakdown,
        ];
    }

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

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

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

        return [
            'summary' => [
                'revenue'       => $revenue,
                'cogs'          => $cogs,
                'discounts'     => $discounts,
                'tax_collected' => $tax,
                'expenses'      => $expenses,
                'gross_profit'  => $revenue - $cogs - $discounts,
                'net_profit'    => $revenue - $cogs - $discounts - $expenses,
                'gross_margin'  => $revenue > 0 ? round(($revenue-$cogs-$discounts)/$revenue*100,2) : 0,
                'net_margin'    => $revenue > 0 ? round(($revenue-$cogs-$discounts-$expenses)/$revenue*100,2) : 0,
            ],
            'by_category'      => $byCategory,
            'expenses_by_cat'  => $expByCategory,
        ];
    }

    private function dataStock(): array
    {
        $rows = Database::fetchAll(
            "SELECT p.product_code, p.product_name,
                    COALESCE(c.category_name,'Uncategorised') AS category,
                    COALESCE(u.short_name,'pcs') AS unit,
                    p.current_stock, p.reorder_level,
                    p.cost_price, p.selling_price,
                    (p.current_stock*p.cost_price) AS cost_value,
                    (p.current_stock*p.selling_price) AS retail_value,
                    CASE WHEN p.current_stock<=0 THEN 'Out'
                         WHEN p.current_stock<=p.reorder_level THEN 'Low'
                         ELSE 'OK' END AS stock_status
             FROM products p
             LEFT JOIN categories c ON c.id=p.category_id
             LEFT JOIN units u ON u.id=p.unit_id
             WHERE p.status='active'
             ORDER BY p.product_name"
        );

        return [
            'products'   => $rows,
            'total_cost' => array_sum(array_column($rows, 'cost_value')),
            'total_retail'=> array_sum(array_column($rows, 'retail_value')),
            'out_count'  => count(array_filter($rows, fn($r) => $r['stock_status'] === 'Out')),
            'low_count'  => count(array_filter($rows, fn($r) => $r['stock_status'] === 'Low')),
        ];
    }

    private function dataPurchases(string $from, string $to): array
    {
        $rows = Database::fetchAll(
            "SELECT p.invoice_no, s.supplier_name, p.purchase_date,
                    p.total, p.paid, p.balance, p.payment_status
             FROM purchases p JOIN suppliers s ON s.id=p.supplier_id
             WHERE p.purchase_date BETWEEN ? AND ? AND p.status!='cancelled'
             ORDER BY p.purchase_date DESC", [$from, $to]
        );

        return [
            'purchases'   => $rows,
            'total'       => array_sum(array_column($rows, 'total')),
            'paid'        => array_sum(array_column($rows, 'paid')),
            'balance'     => array_sum(array_column($rows, 'balance')),
            'count'       => count($rows),
        ];
    }

    private function dataExpenses(string $from, string $to): array
    {
        $rows = Database::fetchAll(
            "SELECT e.title, ec.category_name AS category,
                    e.expense_date AS date, e.amount, u.name AS recorded_by
             FROM expenses e
             JOIN expense_categories ec ON ec.id=e.expense_category_id
             LEFT JOIN users u ON u.id=e.created_by
             WHERE e.expense_date BETWEEN ? AND ?
             ORDER BY e.expense_date DESC", [$from, $to]
        );

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

        return [
            'expenses'    => $rows,
            'by_category' => $byCategory,
            'total'       => array_sum(array_column($rows, 'amount')),
            'count'       => count($rows),
        ];
    }

    private function dataCustomers(): array
    {
        $rows = Database::fetchAll(
            "SELECT c.customer_name AS name, c.phone,
                    c.current_balance AS outstanding, c.credit_limit,
                    COUNT(s.id) AS open_invoices,
                    MAX(s.sale_date) AS last_sale
             FROM customers c
             LEFT JOIN sales s ON s.customer_id=c.id AND s.status='completed' AND s.balance>0
             WHERE c.status='active' AND c.current_balance>0
             GROUP BY c.id ORDER BY c.current_balance DESC"
        );

        return [
            'customers'    => $rows,
            'total'        => array_sum(array_column($rows, 'outstanding')),
            'count'        => count($rows),
        ];
    }

    private function dataSuppliers(): array
    {
        $rows = Database::fetchAll(
            "SELECT s.supplier_name AS name, s.phone, s.contact_person,
                    s.current_balance AS outstanding,
                    COUNT(p.id) AS open_orders,
                    MAX(p.purchase_date) AS last_purchase
             FROM suppliers s
             LEFT JOIN purchases p ON p.supplier_id=s.id AND p.status='received' AND p.balance>0
             WHERE s.status='active' AND s.current_balance>0
             GROUP BY s.id ORDER BY s.current_balance DESC"
        );

        return [
            'suppliers' => $rows,
            'total'     => array_sum(array_column($rows, 'outstanding')),
            'count'     => count($rows),
        ];
    }

    private function dataCreditCustomers(): array
    {
        $rows = Database::fetchAll(
            "SELECT c.customer_name AS name, c.phone,
                    SUM(s.balance) AS outstanding,
                    SUM(s.total) AS total_credit,
                    COUNT(s.id) AS open_invoices
             FROM customers c
             INNER JOIN sales s ON s.customer_id=c.id
                                AND s.status='completed' AND s.balance>0.001
             WHERE c.status='active'
               AND EXISTS (SELECT 1 FROM sale_payments sp WHERE sp.sale_id=s.id AND sp.payment_method='credit')
             GROUP BY c.id HAVING outstanding>0.001
             ORDER BY outstanding DESC"
        );

        return [
            'customers' => $rows,
            'total'     => array_sum(array_column($rows, 'outstanding')),
            'count'     => count($rows),
        ];
    }

    private function dataTax(array $p1, string $from, string $to): array
    {
        $rows = Database::fetchAll(
            "SELECT DATE_FORMAT(sale_date,'%Y-%m-%d') AS period,
                    COUNT(*) AS invoices,
                    SUM(total) AS revenue,
                    SUM(discount) AS discount,
                    SUM(tax) AS tax_collected
             FROM sales WHERE sale_date BETWEEN ? AND ? AND status='completed'
             GROUP BY period ORDER BY period ASC", $p1
        );

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

        return [
            'by_day'      => $rows,
            'by_category' => $byCategory,
            'total_tax'   => array_sum(array_column($rows, 'tax_collected')),
            'total_revenue'=> array_sum(array_column($rows, 'revenue')),
            'tax_name'    => setting('tax_name', 'VAT'),
        ];
    }

    private function dataCashier(array $p1, string $from, string $to): array
    {
        $rows = Database::fetchAll(
            "SELECT u.id AS cashier_id, u.name,
                    COUNT(DISTINCT s.id) AS bills,
                    SUM(s.total) AS total,
                    SUM(s.paid) AS paid,
                    SUM(s.balance) AS balance,
                    SUM(s.discount) AS discount,
                    SUM(si.quantity*(si.selling_price-si.cost_price)) AS gross_profit,
                    MAX(s.sale_date) AS last_sale
             FROM users u
             JOIN sales s ON s.created_by=u.id AND s.status='completed'
             JOIN sale_items si ON si.sale_id=s.id
             WHERE s.sale_date BETWEEN ? AND ?
             GROUP BY u.id ORDER BY total DESC", $p1
        );

        $pmtRows = Database::fetchAll(
            "SELECT s.created_by AS cashier_id,
                    sp.payment_method, SUM(sp.amount) AS amount
             FROM sale_payments sp
             JOIN sales s ON s.id=sp.sale_id
             WHERE s.sale_date BETWEEN ? AND ? AND s.status='completed'
             GROUP BY s.created_by, sp.payment_method", $p1
        );

        $pmtMap = [];
        foreach ($pmtRows as $r) {
            $pmtMap[$r['cashier_id']][$r['payment_method']] = (float)$r['amount'];
        }

        foreach ($rows as &$row) {
            $row['payments'] = $pmtMap[$row['cashier_id']] ?? [];
        }

        return [
            'cashiers'     => $rows,
            'grand_total'  => array_sum(array_column($rows, 'total')),
            'grand_bills'  => array_sum(array_column($rows, 'bills')),
            'grand_profit' => array_sum(array_column($rows, 'gross_profit')),
        ];
    }

    // ── Export endpoint ──────────────────────────────────────
    public function export(string $type): void
    {
        Auth::requireLogin();
        if (!Auth::can('reports.view')) { http_response_code(403); exit; }

        $format = strtolower($this->get('format', 'pdf'));
        $from   = $this->get('from', date('Y-m-01'));
        $to     = $this->get('to',   date('Y-m-d'));

        // Fallback if Stimulsoft not installed
        if (!file_exists(BASE_PATH . '/vendor/autoload.php')) {
            $slug = str_replace('_', '-', $type);
            $this->redirect(APP_URL . "/reports/{$slug}?from={$from}&to={$to}");
            return;
        }

        require_once BASE_PATH . '/vendor/autoload.php';

        $formatMap = [
            'pdf'  => 'Pdf',
            'xlsx' => 'Excel2007',
            'docx' => 'Word2007',
            'html' => 'Html',
            'csv'  => 'Csv',
        ];
        $fmt = $formatMap[$format] ?? 'Pdf';

        $report = StiReportBuilder::build($type, $from, $to);
        $report->render();

        $filename = setting('shop_name','MJPOS') . '_'
                  . str_replace(' ','_',ucwords(str_replace('_',' ',$type)))
                  . '_' . $from . '_to_' . $to . '.' . $format;

        $exportFormat = constant('Stimulsoft\\Export\\StiExportFormat::' . $fmt);
        $report->exportDocumentToResponse($exportFormat, $filename);
        exit;
    }
} // end StiReportController
} // end if class_exists

// BranchController — inline
if (!class_exists('BranchController')) {
class BranchController extends Controller
{
    // ── List branches ────────────────────────────────────────
    public function index(): void
    {
        Auth::requirePermission('branches.manage');

        $branches = Database::fetchAll(
            "SELECT b.*,
                    COUNT(u.id) AS user_count
             FROM branches b
             LEFT JOIN users u ON u.branch_id = b.id AND u.status = 'active'
             GROUP BY b.id
             ORDER BY b.is_main DESC, b.name ASC"
        );

        $pageTitle  = 'Branch Management';
        $breadcrumb = [['label' => 'Admin'], ['label' => 'Branches']];
        ob_start();
        require APP_PATH . '/views/branches/index.php';
        $content = ob_get_clean();
        require APP_PATH . '/views/layouts/main.php';
    }

    // ── Create form ──────────────────────────────────────────
    public function create(): void
    {
        Auth::requirePermission('branches.manage');
        $branch     = null;
        $pageTitle  = 'Add Branch';
        $breadcrumb = [
            ['label' => 'Branches', 'url' => APP_URL . '/branches'],
            ['label' => 'Add Branch'],
        ];
        ob_start();
        require APP_PATH . '/views/branches/form.php';
        $content = ob_get_clean();
        require APP_PATH . '/views/layouts/main.php';
    }

    // ── Store new branch ─────────────────────────────────────
    public function store(): void
    {
        Auth::requirePermission('branches.manage');
        Auth::requireCsrf();

        $name    = trim(Security::strip($this->post('name', '')));
        $address = trim(Security::strip($this->post('address', '')));
        $phone   = trim(Security::strip($this->post('phone', '')));
        $email   = trim(Security::email($this->post('email', '')));
        $isMain  = (int)$this->post('is_main', 0);

        if (!$name) {
            redirectWith(APP_URL . '/branches/create', 'error', 'Branch name is required.');
            return;
        }

        // Only one main branch
        if ($isMain) {
            Database::execute('UPDATE branches SET is_main = 0');
        }

        Database::execute(
            'INSERT INTO branches (name, address, phone, email, is_main, status)
             VALUES (?, ?, ?, ?, ?, ?)',
            [$name, $address ?: null, $phone ?: null, $email ?: null, $isMain, 'active']
        );

        Auth::logAudit(Auth::id(), 'branch_create', 'branches', "Created branch: {$name}");
        redirectWith(APP_URL . '/branches', 'success', "Branch \"{$name}\" created.");
    }

    // ── Edit form ────────────────────────────────────────────
    public function edit(string $id): void
    {
        Auth::requirePermission('branches.manage');

        $branch = Database::fetchOne('SELECT * FROM branches WHERE id = ?', [(int)$id]);
        if (!$branch) {
            redirectWith(APP_URL . '/branches', 'error', 'Branch not found.');
            return;
        }

        // Users in this branch
        $branchUsers = Database::fetchAll(
            "SELECT u.id, u.name, u.username, r.role_name
             FROM users u JOIN roles r ON r.id = u.role_id
             WHERE u.branch_id = ? AND u.status = 'active'
             ORDER BY u.name", [(int)$id]
        );

        $pageTitle  = 'Edit Branch: ' . $branch['name'];
        $breadcrumb = [
            ['label' => 'Branches', 'url' => APP_URL . '/branches'],
            ['label' => 'Edit'],
        ];
        ob_start();
        require APP_PATH . '/views/branches/form.php';
        $content = ob_get_clean();
        require APP_PATH . '/views/layouts/main.php';
    }

    // ── Update branch ────────────────────────────────────────
    public function update(string $id): void
    {
        Auth::requirePermission('branches.manage');
        Auth::requireCsrf();

        $branchId = (int)$id;
        $branch   = Database::fetchOne('SELECT * FROM branches WHERE id = ?', [$branchId]);
        if (!$branch) {
            redirectWith(APP_URL . '/branches', 'error', 'Branch not found.');
            return;
        }

        $name    = trim(Security::strip($this->post('name', '')));
        $address = trim(Security::strip($this->post('address', '')));
        $phone   = trim(Security::strip($this->post('phone', '')));
        $email   = trim(Security::email($this->post('email', '')));
        $isMain  = (int)$this->post('is_main', 0);
        $status  = Security::enum($this->post('status', 'active'), ['active','inactive'], 'active');

        if (!$name) {
            redirectWith(APP_URL . '/branches/edit/' . $branchId, 'error', 'Branch name required.');
            return;
        }

        // Cannot deactivate main branch
        if ($branch['is_main'] && $status === 'inactive') {
            redirectWith(APP_URL . '/branches/edit/' . $branchId, 'error', 'Cannot deactivate the main branch.');
            return;
        }

        // Only one main branch
        if ($isMain && !$branch['is_main']) {
            Database::execute('UPDATE branches SET is_main = 0 WHERE id != ?', [$branchId]);
        }

        Database::execute(
            'UPDATE branches SET name=?, address=?, phone=?, email=?, is_main=?, status=? WHERE id=?',
            [$name, $address ?: null, $phone ?: null, $email ?: null, $isMain, $status, $branchId]
        );

        Auth::logAudit(Auth::id(), 'branch_update', 'branches', "Updated branch: {$name}");
        redirectWith(APP_URL . '/branches', 'success', "Branch \"{$name}\" updated.");
    }

    // ── Delete branch ────────────────────────────────────────
    public function delete(string $id): void
    {
        Auth::requirePermission('branches.manage');
        Auth::requireCsrf();

        $branchId = (int)$id;
        $branch   = Database::fetchOne('SELECT * FROM branches WHERE id = ?', [$branchId]);

        if (!$branch) {
            redirectWith(APP_URL . '/branches', 'error', 'Branch not found.');
            return;
        }
        if ($branch['is_main']) {
            redirectWith(APP_URL . '/branches', 'error', 'Cannot delete the main branch.');
            return;
        }

        $userCount = (int) Database::fetchColumn(
            'SELECT COUNT(*) FROM users WHERE branch_id = ?', [$branchId]
        );
        if ($userCount > 0) {
            redirectWith(APP_URL . '/branches', 'error',
                "Cannot delete — {$userCount} user(s) assigned. Reassign them first.");
            return;
        }

        Database::execute('DELETE FROM branches WHERE id = ?', [$branchId]);
        Auth::logAudit(Auth::id(), 'branch_delete', 'branches', "Deleted branch: {$branch['name']}");
        redirectWith(APP_URL . '/branches', 'success', "Branch \"{$branch['name']}\" deleted.");
    }

    // ── Assign user to branch ────────────────────────────────
    public function assignUser(): void
    {
        Auth::requirePermission('branches.manage');
        Auth::requireCsrf();

        $userId   = (int)$this->post('user_id', 0);
        $branchId = (int)$this->post('branch_id', 0);

        $user   = Database::fetchOne('SELECT id, name FROM users WHERE id = ?', [$userId]);
        $branch = Database::fetchOne('SELECT id, name FROM branches WHERE id = ?', [$branchId]);

        if (!$user || !$branch) {
            redirectWith(APP_URL . '/branches', 'error', 'Invalid user or branch.');
            return;
        }

        Database::execute('UPDATE users SET branch_id = ? WHERE id = ?', [$branchId, $userId]);
        Auth::logAudit(Auth::id(), 'user_branch_assign', 'branches',
            "Assigned {$user['name']} to branch {$branch['name']}");

        redirectWith(APP_URL . '/branches/edit/' . $branchId, 'success',
            "{$user['name']} assigned to {$branch['name']}.");
    }

    // ── Assign all unassigned users to a branch ──────────────
    public function assignAll(): void
    {
        Auth::requirePermission('branches.manage');
        Auth::requireCsrf();

        $branchId = (int)$this->post('branch_id', 1);
        $branch   = Database::fetchOne('SELECT * FROM branches WHERE id = ?', [$branchId]);
        if (!$branch) {
            redirectWith(APP_URL . '/branches', 'error', 'Branch not found.');
            return;
        }

        $count = Database::execute(
            'UPDATE users SET branch_id = ? WHERE branch_id IS NULL',
            [$branchId]
        );

        redirectWith(APP_URL . '/branches/edit/' . $branchId, 'success',
            "{$count} unassigned user(s) moved to \"{$branch['name']}\".");
    }
}
}

// DeveloperController — inline
if (!class_exists('DeveloperController')) {
class DeveloperController extends Controller
{
    private function guard(): void
    {
        Auth::requireLogin();
        if (Auth::role() !== 'developer') {
            http_response_code(403);
            require APP_PATH . '/views/errors/403.php';
            exit;
        }
    }

    // ── Dashboard ────────────────────────────────────────────
    public function index(): void
    {
        $this->guard();

        $tables = [
            'sales'            => 'Sales Invoices',
            'sale_items'       => 'Sale Items',
            'sale_payments'    => 'Sale Payments',
            'purchases'        => 'Purchases',
            'purchase_items'   => 'Purchase Items',
            'expenses'         => 'Expenses',
            'cheque_tracking'  => 'Cheque Records',
            'customer_payments'=> 'Customer Payments',
            'stock_movements'  => 'Stock Movements',
            'activity_logs'    => 'Activity Logs',
            'customers'        => 'Customers',
            'suppliers'        => 'Suppliers',
            'products'         => 'Products',
            'product_batches'  => 'Product Batches',
            'branches'         => 'Branches',
        ];

        $counts = [];
        foreach ($tables as $tbl => $label) {
            try {
                $counts[$tbl] = [
                    'label' => $label,
                    'count' => (int) Database::fetchColumn("SELECT COUNT(*) FROM `$tbl`"),
                ];
            } catch (Exception $e) {
                $counts[$tbl] = ['label' => $label, 'count' => '—'];
            }
        }

        try {
            $dbSize = Database::fetchOne(
                "SELECT ROUND(SUM(data_length+index_length)/1024/1024,2) AS mb
                 FROM information_schema.tables
                 WHERE table_schema = ?",
                [DB_NAME]
            );
        } catch (Exception $e) {
            $dbSize = ['mb' => '?'];
        }

        $pageTitle  = '🛠 Developer Tools';
        $breadcrumb = [['label' => 'Developer Tools']];
        ob_start();
        require APP_PATH . '/views/developer/index.php';
        $content = ob_get_clean();
        require APP_PATH . '/views/layouts/main.php';
    }

    // ── Clear confirm page ───────────────────────────────────
    public function clearConfirm(): void
    {
        $this->guard();

        $scope      = $_GET['scope'] ?? 'transactions';
        $pageTitle  = '⚠ Clear Database';
        $breadcrumb = [
            ['label' => 'Developer Tools', 'url' => APP_URL . '/developer'],
            ['label' => 'Clear Data'],
        ];
        ob_start();
        require APP_PATH . '/views/developer/clear_confirm.php';
        $content = ob_get_clean();
        require APP_PATH . '/views/layouts/main.php';
    }

    // ── Execute clear ────────────────────────────────────────
    public function clearExecute(): void
    {
        $this->guard();
        Auth::requireCsrf();

        $confirmText  = $this->post('confirm_text', '');
        $confirmCheck = $this->post('confirm_check', '');
        $scope        = $this->post('scope', 'transactions');

        if ($confirmText !== 'CLEAR ALL DATA' || $confirmCheck !== '1') {
            redirectWith(APP_URL . '/developer/clear?scope=' . $scope,
                'error', 'Confirmation failed. Type exactly: CLEAR ALL DATA');
            return;
        }

        try {
            Database::execute('SET FOREIGN_KEY_CHECKS = 0');

            if ($scope === 'transactions' || $scope === 'full') {
                foreach ([
                    'sale_payments','sales_return_items','sales_returns','sale_items','sales',
                    'purchase_items','purchase_return_items','purchase_returns','purchases',
                    'expenses','cheque_tracking',
                    'customer_payments','supplier_payments','held_bills','stock_movements',
                ] as $t) {
                    try { Database::execute("TRUNCATE TABLE `$t`"); } catch (Exception $e) {}
                }
                try { Database::execute("UPDATE customers SET current_balance=0, loyalty_points=0"); } catch(Exception $e) {}
                try { Database::execute("UPDATE suppliers SET current_balance=0"); } catch(Exception $e) {}
                try { Database::execute("UPDATE products  SET current_stock=0"); } catch(Exception $e) {}
            }

            if ($scope === 'full') {
                // Truncate any remaining tables that reference products/customers/suppliers
                // (must happen BEFORE truncating products/customers/suppliers themselves)
                foreach (['product_batches'] as $t) {
                    try { Database::execute("TRUNCATE TABLE `$t`"); } catch (Exception $e) {}
                }

                try { Database::execute("TRUNCATE TABLE customers"); } catch(Exception $e) {}
                try { Database::execute("TRUNCATE TABLE suppliers"); } catch(Exception $e) {}
                try { Database::execute("TRUNCATE TABLE products"); } catch(Exception $e) {}
                try { Database::execute("INSERT INTO customers (id,customer_name,status) VALUES (1,'Walk-in Customer','active')"); } catch(Exception $e) {}

                // ── Verify it actually worked — surface any FK error clearly ──
                $remaining = [];
                foreach (['products','customers','suppliers'] as $t) {
                    try {
                        $cnt = (int) Database::fetchColumn("SELECT COUNT(*) FROM `$t`");
                        // 1 customer row (Walk-in) is expected and fine
                        if ($t === 'customers' && $cnt <= 1) continue;
                        if ($cnt > 0) $remaining[] = "{$t} ({$cnt} rows)";
                    } catch (Exception $e) {}
                }
                if (!empty($remaining)) {
                    Database::execute('SET FOREIGN_KEY_CHECKS = 1');
                    redirectWith(APP_URL . '/developer', 'error',
                        '⚠ Full Clear partially failed — still has data in: ' .
                        implode(', ', $remaining) .
                        '. A foreign key reference may be blocking deletion. Check error log.');
                    return;
                }
            }

            // ── Full Reset: wipe transactions + products + branches,  ──
            // ── reset customer/supplier balances, reset ALL table IDs ──
            if ($scope === 'full_reset') {
                // Delete all transactional data
                foreach ([
                    'sale_payments','sale_items','sales',
                    'purchase_items','purchases',
                    'expenses','cheque_tracking',
                    'customer_payments','supplier_payments','held_bills','stock_movements',
                    'sales_return_items','sales_returns',
                    'purchase_return_items','purchase_returns',
                    'activity_logs',
                ] as $t) {
                    try { Database::execute("TRUNCATE TABLE `$t`"); } catch (Exception $e) {}
                }

                // Delete products entirely
                try { Database::execute("TRUNCATE TABLE product_batches"); } catch(Exception $e) {}
                try { Database::execute("TRUNCATE TABLE products"); } catch(Exception $e) {}

                // Delete branches, re-seed a default Main Branch
                try { Database::execute("TRUNCATE TABLE branches"); } catch(Exception $e) {}
                try {
                    Database::execute(
                        "INSERT INTO branches (id, name, status) VALUES (1, 'Main Branch', 'active')"
                    );
                } catch(Exception $e) {}

                // Reset customer & supplier balances to 0 — records kept
                try { Database::execute("UPDATE customers SET current_balance=0, loyalty_points=0"); } catch(Exception $e) {}
                try { Database::execute("UPDATE suppliers SET current_balance=0"); } catch(Exception $e) {}

                // Re-point any existing customers/suppliers to the re-created branch (if branch_id column exists)
                try { Database::execute("UPDATE users SET branch_id=1"); } catch(Exception $e) {}
            }

            if ($scope === 'logs') {
                try { Database::execute("TRUNCATE TABLE activity_logs"); } catch(Exception $e) {}
            }

            Database::execute('SET FOREIGN_KEY_CHECKS = 1');

            // ── Reset auto-increment IDs ──────────────────────────
            $idResetTables = ['sales','purchases','expenses'];

            if ($scope === 'full') {
                $idResetTables = array_merge($idResetTables, ['customers','suppliers','products']);
            }

            if ($scope === 'full_reset') {
                // Reset IDs on every table that was touched (transactions,
                // products, branches) — but NOT customers/suppliers since
                // those records are kept, just balance-reset.
                $idResetTables = [
                    'sales','sale_items','sale_payments',
                    'purchases','purchase_items',
                    'expenses','cheque_tracking',
                    'customer_payments','supplier_payments','stock_movements',
                    'sales_returns','sales_return_items',
                    'purchase_returns','purchase_return_items',
                    'activity_logs','products','product_batches','branches',
                ];
            }

            foreach ($idResetTables as $t) {
                try { Database::execute("ALTER TABLE `$t` AUTO_INCREMENT = 1"); } catch(Exception $e) {}
            }

            $label = match($scope) {
                'transactions' => 'Transaction Data',
                'full'         => 'Full User Data',
                'full_reset'   => 'Full Reset (Wipe & Reset IDs)',
                'logs'         => 'Activity Logs',
                default        => ucfirst($scope),
            };

            Auth::logAudit(Auth::id(), 'developer_clear', 'database',
                "DATABASE CLEARED: {$label} by " . Auth::name());

            redirectWith(APP_URL . '/developer', 'success',
                "✅ {$label} cleared. Database is clean.");

        } catch (Exception $e) {
            try { Database::execute('SET FOREIGN_KEY_CHECKS = 1'); } catch(Exception $ex) {}
            redirectWith(APP_URL . '/developer/clear?scope=' . $scope,
                'error', 'Error: ' . $e->getMessage());
        }
    }
}
}


// PayrollController — inline
if (!class_exists('PayrollController')) {
class PayrollController extends Controller
{
    // ── Dashboard ────────────────────────────────────────────
    public function index(): void
    {
        Auth::requirePermission('payroll.view');

        $periods = Database::fetchAll(
            "SELECT pp.*, COUNT(pr.id) AS emp_count,
                    COALESCE(SUM(pr.net_salary),0) AS total_net,
                    COUNT(CASE WHEN pr.payment_status='paid' THEN 1 END) AS paid_count
             FROM payroll_periods pp
             LEFT JOIN payroll_records pr ON pr.period_id = pp.id
             GROUP BY pp.id ORDER BY pp.year DESC, pp.month DESC LIMIT 12"
        );

        $stats = Database::fetchOne(
            "SELECT COUNT(*) AS total_emp,
                    COUNT(CASE WHEN status='active' THEN 1 END) AS active_emp,
                    COALESCE(SUM(CASE WHEN status='active' THEN basic_salary END),0) AS monthly_payroll
             FROM employees"
        );

        $currentPeriod = Database::fetchOne(
            "SELECT pp.*, COALESCE(SUM(pr.net_salary),0) AS total_net,
                    COUNT(pr.id) AS processed
             FROM payroll_periods pp
             LEFT JOIN payroll_records pr ON pr.period_id=pp.id
             WHERE pp.month=? AND pp.year=?
             GROUP BY pp.id",
            [(int)date('n'), (int)date('Y')]
        );

        $pageTitle  = 'Payroll Management';
        $breadcrumb = [['label' => 'Payroll']];
        ob_start(); require APP_PATH.'/views/payroll/index.php';
        $content = ob_get_clean();
        require APP_PATH.'/views/layouts/main.php';
    }

    // ── Employees list ────────────────────────────────────────
    public function employees(): void
    {
        Auth::requirePermission('payroll.employees');

        $search = trim($this->get('search',''));
        $dept   = Security::strip($this->get('dept',''));
        $status = $this->get('status','active');

        $where  = ['1=1']; $params = [];
        if ($search) { $where[]='(e.full_name LIKE ? OR e.emp_code LIKE ? OR e.designation LIKE ?)'; $params=array_merge($params,["%$search%","%$search%","%$search%"]); }
        if ($dept)   { $where[]='e.department=?'; $params[]=$dept; }
        if ($status!=='all') { $where[]='e.status=?'; $params[]=$status; }

        $employees = Database::fetchAll(
            "SELECT e.*, b.name AS branch_name
             FROM employees e
             LEFT JOIN branches b ON b.id=e.branch_id
             WHERE ".implode(' AND ',$where)."
             ORDER BY e.full_name ASC", $params
        );

        $departments = Database::fetchAll(
            "SELECT DISTINCT department FROM employees WHERE department IS NOT NULL AND department!='' ORDER BY department"
        );

        $pageTitle  = 'Employees';
        $breadcrumb = [['label'=>'Payroll','url'=>APP_URL.'/payroll'],['label'=>'Employees']];
        ob_start(); require APP_PATH.'/views/payroll/employees.php';
        $content = ob_get_clean();
        require APP_PATH.'/views/layouts/main.php';
    }

    // ── Add employee form ─────────────────────────────────────
    public function addEmployee(): void
    {
        Auth::requirePermission('payroll.employees');
        $employee   = null;
        $components = Database::fetchAll("SELECT * FROM salary_components WHERE status='active' ORDER BY type,name");
        $branches   = Database::fetchAll("SELECT id,name FROM branches WHERE status='active' ORDER BY is_main DESC,name");
        $users      = Database::fetchAll("SELECT id,name FROM users WHERE status='active' ORDER BY name");
        $pageTitle  = 'Add Employee';
        $breadcrumb = [['label'=>'Payroll','url'=>APP_URL.'/payroll'],['label'=>'Employees','url'=>APP_URL.'/payroll/employees'],['label'=>'Add']];
        ob_start(); require APP_PATH.'/views/payroll/employee_form.php';
        $content = ob_get_clean();
        require APP_PATH.'/views/layouts/main.php';
    }

    public function storeEmployee(): void
    {
        Auth::requirePermission('payroll.employees');
        Auth::requireCsrf();

        $code = strtoupper(Security::strip($this->post('emp_code','')));
        if (!$code) {
            $lastId = (int)Database::fetchColumn("SELECT MAX(id) FROM employees") + 1;
            $code   = 'EMP'.str_pad($lastId, 4, '0', STR_PAD_LEFT);
        }

        Database::execute(
            "INSERT INTO employees (emp_code,full_name,phone,email,nic,designation,department,
             branch_id,join_date,basic_salary,pay_type,bank_name,bank_account,bank_branch,
             status,user_id,epf_eligible)
             VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
            [
                $code,
                Security::strip($this->post('full_name','')),
                Security::strip($this->post('phone','')),
                Security::strip($this->post('email','')),
                Security::strip($this->post('nic','')),
                Security::strip($this->post('designation','')),
                Security::strip($this->post('department','')),
                (int)$this->post('branch_id',1),
                $this->post('join_date', date('Y-m-d')),
                (float)$this->post('basic_salary',0),
                Security::enum($this->post('pay_type','monthly'),['monthly','daily','hourly'],'monthly'),
                Security::strip($this->post('bank_name','')),
                Security::strip($this->post('bank_account','')),
                Security::strip($this->post('bank_branch','')),
                Security::enum($this->post('status','active'),['active','inactive','terminated'],'active'),
                $this->post('user_id') ?: null,
                ($this->post('epf_eligible') === '1') ? 1 : 0,
            ]
        );

        $empId = (int)Database::fetchColumn('SELECT LAST_INSERT_ID()');

        // Save components
        $compIds  = (array)$this->post('component_id',[]);
        $compAmts = (array)$this->post('component_amount',[]);
        foreach ($compIds as $i => $cid) {
            if ($cid && isset($compAmts[$i]) && (float)$compAmts[$i] > 0) {
                Database::execute(
                    'INSERT INTO employee_components (employee_id,component_id,amount) VALUES (?,?,?)',
                    [$empId, (int)$cid, (float)$compAmts[$i]]
                );
            }
        }

        Auth::logAudit(Auth::id(),'employee_create','employees',"Added employee: {$code}");
        redirectWith(APP_URL.'/payroll/employees','success',"Employee {$code} added.");
    }

    public function editEmployee(string $id): void
    {
        Auth::requirePermission('payroll.employees');
        $employee = Database::fetchOne('SELECT * FROM employees WHERE id=?',[(int)$id]);
        if (!$employee) { redirectWith(APP_URL.'/payroll/employees','error','Not found.'); return; }
        $empComponents = Database::fetchAll('SELECT * FROM employee_components WHERE employee_id=?',[(int)$id]);
        $empCompMap = [];
        foreach($empComponents as $ec) $empCompMap[$ec['component_id']]=$ec['amount'];
        $components = Database::fetchAll("SELECT * FROM salary_components WHERE status='active' ORDER BY type,name");
        $branches   = Database::fetchAll("SELECT id,name FROM branches WHERE status='active' ORDER BY is_main DESC,name");
        $users      = Database::fetchAll("SELECT id,name FROM users WHERE status='active' ORDER BY name");
        $pageTitle  = 'Edit Employee: '.$employee['full_name'];
        $breadcrumb = [['label'=>'Payroll','url'=>APP_URL.'/payroll'],['label'=>'Employees','url'=>APP_URL.'/payroll/employees'],['label'=>'Edit']];
        ob_start(); require APP_PATH.'/views/payroll/employee_form.php';
        $content = ob_get_clean();
        require APP_PATH.'/views/layouts/main.php';
    }

    public function updateEmployee(string $id): void
    {
        Auth::requirePermission('payroll.employees');
        Auth::requireCsrf();
        $empId = (int)$id;
        Database::execute(
            "UPDATE employees SET full_name=?,phone=?,email=?,nic=?,designation=?,department=?,
             branch_id=?,join_date=?,basic_salary=?,pay_type=?,bank_name=?,bank_account=?,
             bank_branch=?,status=?,user_id=?,epf_eligible=? WHERE id=?",
            [
                Security::strip($this->post('full_name','')),
                Security::strip($this->post('phone','')),
                Security::strip($this->post('email','')),
                Security::strip($this->post('nic','')),
                Security::strip($this->post('designation','')),
                Security::strip($this->post('department','')),
                (int)$this->post('branch_id',1),
                $this->post('join_date',date('Y-m-d')),
                (float)$this->post('basic_salary',0),
                Security::enum($this->post('pay_type','monthly'),['monthly','daily','hourly'],'monthly'),
                Security::strip($this->post('bank_name','')),
                Security::strip($this->post('bank_account','')),
                Security::strip($this->post('bank_branch','')),
                Security::enum($this->post('status','active'),['active','inactive','terminated'],'active'),
                $this->post('user_id') ?: null,
                ($this->post('epf_eligible') === '1') ? 1 : 0,
                $empId,
            ]
        );
        Database::execute('DELETE FROM employee_components WHERE employee_id=?',[$empId]);
        $compIds  = (array)$this->post('component_id',[]);
        $compAmts = (array)$this->post('component_amount',[]);
        foreach ($compIds as $i => $cid) {
            if ($cid && isset($compAmts[$i]) && (float)$compAmts[$i] > 0) {
                Database::execute(
                    'INSERT INTO employee_components (employee_id,component_id,amount) VALUES (?,?,?)',
                    [$empId,(int)$cid,(float)$compAmts[$i]]
                );
            }
        }
        redirectWith(APP_URL.'/payroll/employees','success','Employee updated.');
    }

    // ── Payroll Periods ───────────────────────────────────────
    public function createPeriod(): void
    {
        Auth::requirePermission('payroll.manage');
        Auth::requireCsrf();

        $month = (int)$this->post('month', date('n'));
        $year  = (int)$this->post('year',  date('Y'));

        $exists = Database::fetchColumn('SELECT id FROM payroll_periods WHERE month=? AND year=?',[$month,$year]);
        if ($exists) { redirectWith(APP_URL.'/payroll','error','Period already exists.'); return; }

        $start = date('Y-m-01', mktime(0,0,0,$month,1,$year));
        $end   = date('Y-m-t',  mktime(0,0,0,$month,1,$year));
        $name  = date('F Y',    mktime(0,0,0,$month,1,$year));

        Database::execute(
            'INSERT INTO payroll_periods (period_name,month,year,start_date,end_date,status,created_by) VALUES (?,?,?,?,?,?,?)',
            [$name,$month,$year,$start,$end,'draft',Auth::id()]
        );
        $periodId = (int)Database::fetchColumn('SELECT LAST_INSERT_ID()');
        redirectWith(APP_URL.'/payroll/process/'.$periodId,'success',"Period \"{$name}\" created. Now process payroll.");
    }

    // ── Process payroll for a period ─────────────────────────
    public function process(string $id): void
    {
        Auth::requirePermission('payroll.view');
        $period = Database::fetchOne('SELECT * FROM payroll_periods WHERE id=?',[(int)$id]);
        if (!$period) { redirectWith(APP_URL.'/payroll','error','Period not found.'); return; }

        $employees = Database::fetchAll(
            "SELECT e.*,
                    COALESCE(pr.id,0) AS record_id,
                    pr.net_salary,pr.payment_status,pr.gross_salary,
                    pr.total_allowances,pr.total_deductions,pr.present_days
             FROM employees e
             LEFT JOIN payroll_records pr ON pr.employee_id=e.id AND pr.period_id=?
             WHERE e.status='active' ORDER BY e.full_name", [(int)$id]
        );

        $pageTitle  = 'Process Payroll — '.$period['period_name'];
        $breadcrumb = [['label'=>'Payroll','url'=>APP_URL.'/payroll'],['label'=>$period['period_name']]];
        ob_start(); require APP_PATH.'/views/payroll/process.php';
        $content = ob_get_clean();
        require APP_PATH.'/views/layouts/main.php';
    }

    // ── Save payroll records ──────────────────────────────────
    public function savePayroll(string $id): void
    {
        Auth::requirePermission('payroll.manage');
        Auth::requireCsrf();

        $periodId = (int)$id;
        $period   = Database::fetchOne('SELECT * FROM payroll_periods WHERE id=?',[$periodId]);
        if (!$period || $period['status']==='paid') {
            redirectWith(APP_URL.'/payroll','error','Cannot modify paid period.');
            return;
        }

        $empIds      = (array)$this->post('employee_id',[]);
        $presentDays = (array)$this->post('present_days',[]);
        $otHours     = (array)$this->post('overtime_hours',[]);
        $otRates     = (array)$this->post('overtime_rate',[]);
        $pmtMethods  = (array)$this->post('payment_method',[]);
        $notes       = (array)$this->post('notes',[]);

        foreach ($empIds as $i => $empId) {
            $emp = Database::fetchOne('SELECT * FROM employees WHERE id=?',[(int)$empId]);
            if (!$emp) continue;

            $workingDays = 26;
            $present     = min((float)($presentDays[$i]??$workingDays), $workingDays);
            $otHrs       = max(0,(float)($otHours[$i]??0));
            $otRate      = max(0,(float)($otRates[$i]??0));
            $otAmt       = $otHrs * $otRate;
            $dailyRate   = $emp['pay_type']==='monthly' ? $emp['basic_salary']/$workingDays : $emp['basic_salary'];
            $earnedBasic = $dailyRate * $present;

            // Get components
            $comps = Database::fetchAll(
                'SELECT ec.amount, sc.name, sc.type, sc.calc_type, ec.component_id
                 FROM employee_components ec
                 JOIN salary_components sc ON sc.id=ec.component_id
                 WHERE ec.employee_id=?', [(int)$empId]
            );

            // Advances for this period
            $advances = (float)Database::fetchColumn(
                'SELECT COALESCE(SUM(amount),0) FROM salary_advances
                 WHERE employee_id=? AND deduct_month=? AND deduct_year=? AND status=\'pending\'',
                [(int)$empId, $period['month'], $period['year']]
            );

            $totalAllowances = $otAmt;
            $totalDeductions = 0;
            $epfEmployee     = round($earnedBasic * 0.08, 2);
            $epfEmployer     = round($earnedBasic * 0.12, 2);
            $etf             = round($earnedBasic * 0.03, 2);

            $compLines = [];
            foreach ($comps as $c) {
                $amt = $c['calc_type']==='percentage'
                    ? round($earnedBasic * $c['amount'] / 100, 2)
                    : (float)$c['amount'];
                if ($c['type']==='allowance') $totalAllowances += $amt;
                else                           $totalDeductions += $amt;
                $compLines[] = ['component_id'=>$c['component_id'],'name'=>$c['name'],'type'=>$c['type'],'amount'=>$amt];
            }
            if ($advances > 0) {
                $totalDeductions += $advances;
                $compLines[] = ['component_id'=>null,'name'=>'Salary Advance','type'=>'deduction','amount'=>$advances];
            }

            $grossSalary = $earnedBasic + $totalAllowances;
            $netSalary   = max(0, $grossSalary - $totalDeductions - $epfEmployee);

            // Upsert payroll record
            $existing = Database::fetchColumn(
                'SELECT id FROM payroll_records WHERE period_id=? AND employee_id=?',[$periodId,(int)$empId]
            );
            if ($existing) {
                Database::execute(
                    "UPDATE payroll_records SET basic_salary=?,working_days=?,present_days=?,
                     overtime_hours=?,overtime_rate=?,overtime_amount=?,gross_salary=?,
                     total_allowances=?,total_deductions=?,epf_employee=?,epf_employer=?,etf=?,
                     net_salary=?,payment_method=?,notes=? WHERE id=?",
                    [$emp['basic_salary'],$workingDays,$present,$otHrs,$otRate,$otAmt,$grossSalary,
                     $totalAllowances,$totalDeductions,$epfEmployee,$epfEmployer,$etf,$netSalary,
                     $pmtMethods[$i]??'bank_transfer',$notes[$i]??'',(int)$existing]
                );
                Database::execute('DELETE FROM payroll_record_components WHERE record_id=?',[(int)$existing]);
                $recId = (int)$existing;
            } else {
                Database::execute(
                    "INSERT INTO payroll_records (period_id,employee_id,basic_salary,working_days,
                     present_days,overtime_hours,overtime_rate,overtime_amount,gross_salary,
                     total_allowances,total_deductions,epf_employee,epf_employer,etf,net_salary,
                     payment_method,notes)
                     VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
                    [$periodId,(int)$empId,$emp['basic_salary'],$workingDays,$present,$otHrs,$otRate,
                     $otAmt,$grossSalary,$totalAllowances,$totalDeductions,$epfEmployee,$epfEmployer,
                     $etf,$netSalary,$pmtMethods[$i]??'bank_transfer',$notes[$i]??'']
                );
                $recId = (int)Database::fetchColumn('SELECT LAST_INSERT_ID()');
            }

            foreach ($compLines as $cl) {
                Database::execute(
                    'INSERT INTO payroll_record_components (record_id,component_id,name,type,amount) VALUES (?,?,?,?,?)',
                    [$recId,$cl['component_id']??null,$cl['name'],$cl['type'],$cl['amount']]
                );
            }

            // Mark advances as deducted
            if ($advances > 0) {
                Database::execute(
                    "UPDATE salary_advances SET status='deducted'
                     WHERE employee_id=? AND deduct_month=? AND deduct_year=? AND status='pending'",
                    [(int)$empId,$period['month'],$period['year']]
                );
            }
        }

        Database::execute("UPDATE payroll_periods SET status='processing' WHERE id=?",[$periodId]);
        Auth::logAudit(Auth::id(),'payroll_process','payroll_periods',"Processed: {$period['period_name']}");
        redirectWith(APP_URL.'/payroll/process/'.$periodId,'success','Payroll calculated. Review and approve.');
    }

    // ── Approve period ────────────────────────────────────────
    public function approve(string $id): void
    {
        Auth::requirePermission('payroll.approve');
        Auth::requireCsrf();
        $periodId = (int)$id;
        Database::execute(
            "UPDATE payroll_periods SET status='approved',approved_by=?,approved_at=NOW() WHERE id=?",
            [Auth::id(),$periodId]
        );
        redirectWith(APP_URL.'/payroll/process/'.$periodId,'success','Payroll approved. Ready for payment.');
    }

    // ── Mark as paid ──────────────────────────────────────────
    public function markPaid(string $id): void
    {
        Auth::requirePermission('payroll.approve');
        Auth::requireCsrf();
        $periodId = (int)$id;
        Database::execute(
            "UPDATE payroll_records SET payment_status='paid',paid_at=NOW() WHERE period_id=?",[$periodId]
        );
        Database::execute("UPDATE payroll_periods SET status='paid' WHERE id=?",[$periodId]);
        Auth::logAudit(Auth::id(),'payroll_paid','payroll_periods',"Marked paid: period #{$periodId}");
        redirectWith(APP_URL.'/payroll/process/'.$periodId,'success','All salaries marked as paid.');
    }

    // ── Pay slip (single employee) ────────────────────────────
    public function payslip(string $id): void
    {
        Auth::requirePermission('payroll.view');
        $record = Database::fetchOne(
            "SELECT pr.*, e.full_name, e.emp_code, e.designation, e.department,
                    e.bank_name, e.bank_account,
                    pp.period_name, pp.month, pp.year, pp.start_date, pp.end_date,
                    b.name AS branch_name
             FROM payroll_records pr
             JOIN employees e  ON e.id=pr.employee_id
             JOIN payroll_periods pp ON pp.id=pr.period_id
             LEFT JOIN branches b ON b.id=e.branch_id
             WHERE pr.id=?", [(int)$id]
        );
        if (!$record) { redirectWith(APP_URL.'/payroll','error','Record not found.'); return; }

        $components = Database::fetchAll(
            'SELECT * FROM payroll_record_components WHERE record_id=? ORDER BY type DESC',[(int)$id]
        );

        $pageTitle  = 'Pay Slip — '.$record['full_name'];
        ob_start(); require APP_PATH.'/views/payroll/payslip.php';
        $content = ob_get_clean();
        require APP_PATH.'/views/layouts/main.php';
    }

    // ── Salary advances ───────────────────────────────────────
    public function advances(): void
    {
        Auth::requirePermission('payroll.manage');
        $advances = Database::fetchAll(
            "SELECT sa.*, e.full_name, e.emp_code
             FROM salary_advances sa JOIN employees e ON e.id=sa.employee_id
             ORDER BY sa.advance_date DESC LIMIT 50"
        );
        $employees = Database::fetchAll("SELECT id,full_name,emp_code FROM employees WHERE status='active' ORDER BY full_name");
        $pageTitle  = 'Salary Advances';
        $breadcrumb = [['label'=>'Payroll','url'=>APP_URL.'/payroll'],['label'=>'Advances']];
        ob_start(); require APP_PATH.'/views/payroll/advances.php';
        $content = ob_get_clean();
        require APP_PATH.'/views/layouts/main.php';
    }

    public function storeAdvance(): void
    {
        Auth::requirePermission('payroll.manage');
        Auth::requireCsrf();
        Database::execute(
            'INSERT INTO salary_advances (employee_id,amount,advance_date,reason,deduct_month,deduct_year,created_by)
             VALUES (?,?,?,?,?,?,?)',
            [(int)$this->post('employee_id'), (float)$this->post('amount'),
             $this->post('advance_date',date('Y-m-d')), Security::strip($this->post('reason','')),
             (int)$this->post('deduct_month', date('n')),
             (int)$this->post('deduct_year',  date('Y')), Auth::id()]
        );
        redirectWith(APP_URL.'/payroll/advances','success','Advance recorded.');
    }

    // ── Reports ───────────────────────────────────────────────
    public function report(): void
    {
        Auth::requirePermission('payroll.reports');
        $year = (int)$this->get('year', date('Y'));

        $monthly = Database::fetchAll(
            "SELECT pp.month, pp.period_name, pp.status,
                    COUNT(pr.id) AS emp_count,
                    COALESCE(SUM(pr.gross_salary),0) AS total_gross,
                    COALESCE(SUM(pr.net_salary),0)   AS total_net,
                    COALESCE(SUM(pr.epf_employee),0) AS total_epf_emp,
                    COALESCE(SUM(pr.epf_employer),0) AS total_epf_er,
                    COALESCE(SUM(pr.etf),0)          AS total_etf
             FROM payroll_periods pp
             LEFT JOIN payroll_records pr ON pr.period_id=pp.id
             WHERE pp.year=? GROUP BY pp.id ORDER BY pp.month ASC", [$year]
        );

        $employees = Database::fetchAll(
            "SELECT e.full_name, e.emp_code, e.designation, e.department,
                    COUNT(pr.id) AS months_paid,
                    COALESCE(SUM(pr.net_salary),0) AS total_net
             FROM employees e
             LEFT JOIN payroll_records pr ON pr.employee_id=e.id
             LEFT JOIN payroll_periods pp ON pp.id=pr.period_id AND pp.year=?
             WHERE e.status='active'
             GROUP BY e.id ORDER BY e.full_name", [$year]
        );

        $pageTitle  = 'Payroll Report '.$year;
        $breadcrumb = [['label'=>'Payroll','url'=>APP_URL.'/payroll'],['label'=>'Reports']];
        ob_start(); require APP_PATH.'/views/payroll/report.php';
        $content = ob_get_clean();
        require APP_PATH.'/views/layouts/main.php';
    }

    // ── Toggle employee status ────────────────────────────────
    public function toggleEmployee(string $id): void
    {
        Auth::requirePermission('payroll.employees');
        Auth::requireCsrf();
        $emp = Database::fetchOne('SELECT id,status,full_name FROM employees WHERE id=?',[(int)$id]);
        if (!$emp) { redirectWith(APP_URL.'/payroll/employees','error','Employee not found.'); return; }
        $newStatus = $emp['status']==='active' ? 'inactive' : 'active';
        Database::execute('UPDATE employees SET status=? WHERE id=?',[$newStatus,(int)$id]);
        redirectWith(APP_URL.'/payroll/employees','success',
            e($emp['full_name']).' marked as '.ucfirst($newStatus).'.');
    }

    // ── Delete employee ───────────────────────────────────────
    public function deleteEmployee(string $id): void
    {
        Auth::requirePermission('payroll.employees');
        Auth::requireCsrf();
        $empId = (int)$id;
        $hasPayroll = (int)Database::fetchColumn(
            'SELECT COUNT(*) FROM payroll_records WHERE employee_id=?',[$empId]
        );
        if ($hasPayroll) {
            redirectWith(APP_URL.'/payroll/employees','error',
                'Cannot delete — employee has payroll history. Set status to Inactive instead.');
            return;
        }
        Database::execute('DELETE FROM employee_components WHERE employee_id=?',[$empId]);
        Database::execute('DELETE FROM salary_advances WHERE employee_id=?',[$empId]);
        Database::execute('DELETE FROM employees WHERE id=?',[$empId]);
        redirectWith(APP_URL.'/payroll/employees','success','Employee deleted.');
    }

    // ── View employee profile ─────────────────────────────────
    public function viewEmployee(string $id): void
    {
        Auth::requirePermission('payroll.view');
        $employee = Database::fetchOne(
            'SELECT e.*,b.name AS branch_name FROM employees e
             LEFT JOIN branches b ON b.id=e.branch_id WHERE e.id=?',[(int)$id]
        );
        if (!$employee) { redirectWith(APP_URL.'/payroll/employees','error','Not found.'); return; }

        $components = Database::fetchAll(
            'SELECT ec.amount, sc.name, sc.type
             FROM employee_components ec
             JOIN salary_components sc ON sc.id=ec.component_id
             WHERE ec.employee_id=? ORDER BY sc.type DESC,sc.name',[(int)$id]
        );
        $payrollHistory = Database::fetchAll(
            'SELECT pr.*,pp.period_name FROM payroll_records pr
             JOIN payroll_periods pp ON pp.id=pr.period_id
             WHERE pr.employee_id=? ORDER BY pp.year DESC,pp.month DESC LIMIT 12',[(int)$id]
        );
        $advances = Database::fetchAll(
            'SELECT * FROM salary_advances WHERE employee_id=? ORDER BY advance_date DESC LIMIT 6',[(int)$id]
        );

        $pageTitle  = 'Employee: '.$employee['full_name'];
        $breadcrumb = [
            ['label'=>'Payroll','url'=>APP_URL.'/payroll'],
            ['label'=>'Employees','url'=>APP_URL.'/payroll/employees'],
            ['label'=>$employee['full_name']],
        ];
        ob_start(); require APP_PATH.'/views/payroll/employee_view.php';
        $content = ob_get_clean();
        require APP_PATH.'/views/layouts/main.php';
    }
}
}

// ── Security headers (every response) ───────────────────────
Security::sendHeaders();

// ── Session ──────────────────────────────────────────────────
Auth::startSession();

// ── Settings cache ───────────────────────────────────────────
if (empty($_SESSION['settings'])) {
    try {
        $rows = Database::fetchAll('SELECT setting_key, setting_value FROM settings');
        $_SESSION['settings'] = array_column($rows, 'setting_value', 'setting_key');
    } catch (Exception $e) {
        $_SESSION['settings'] = [];
    }
}

// ── Routes ──────────────────────────────────────────────────
$router = new Router();

// Auth
$router->get( '/auth/login',                  'AuthController', 'loginPage');
$router->post('/auth/login',                  'AuthController', 'login');
$router->get( '/auth/logout',                 'AuthController', 'logout');
$router->get( '/auth/forgot-password',        'AuthController', 'forgotPasswordForm');
$router->post('/auth/forgot-password',        'AuthController', 'forgotPassword');
$router->get( '/auth/verify-otp',             'AuthController', 'verifyOtpForm');
$router->post('/auth/verify-otp',             'AuthController', 'verifyOtp');
$router->get( '/auth/reset-password/:token',  'AuthController', 'resetPasswordForm');
$router->post('/auth/reset-password/:token',  'AuthController', 'resetPassword');

// Dashboard
$router->get('/dashboard', 'DashboardController', 'index');

// POS
$router->get( '/pos',                   'PosController', 'index');
$router->post('/pos/sale',              'PosController', 'sale');
$router->post('/pos/hold',              'PosController', 'hold');
$router->get( '/pos/held',              'PosController', 'heldList');
$router->get( '/pos/recall/:id',        'PosController', 'recall');
$router->post('/pos/held/delete/:id',   'PosController', 'deleteHeld');
$router->get( '/pos/receipt/:id',       'PosController', 'receipt');

// Sales
$router->get( '/sales',                 'SaleController', 'index');
$router->get( '/sales/view/:id',        'SaleController', 'show');
$router->get( '/sales/show/:id',        'SaleController', 'show');
$router->get( '/sales/payment/:id',     'SaleController', 'paymentForm');
$router->post('/sales/payment/:id',     'SaleController', 'recordPayment');
$router->get( '/sales/return/:id',      'SaleReturnController', 'create');
$router->post('/sales/return/:id',      'SaleReturnController', 'store');

// Customers
$router->get( '/customers',             'CustomerController', 'index');
$router->get( '/customers/create',      'CustomerController', 'create');
$router->post('/customers/create',      'CustomerController', 'store');
$router->get( '/customers/view/:id',    'CustomerController', 'show');
$router->get( '/customers/edit/:id',    'CustomerController', 'edit');
$router->post('/customers/edit/:id',    'CustomerController', 'update');
$router->post('/customers/delete/:id',  'CustomerController', 'delete');

// Purchases
$router->get( '/purchases',             'PurchaseController', 'index');
$router->get( '/purchases/create',      'PurchaseController', 'create');
$router->post('/purchases/create',      'PurchaseController', 'store');
$router->get( '/purchases/view/:id',    'PurchaseController', 'show');
$router->get( '/purchases/edit/:id',    'PurchaseController', 'edit');
$router->post('/purchases/edit/:id',    'PurchaseController', 'update');
$router->get( '/purchases/payment/:id', 'PurchaseController', 'paymentForm');
$router->post('/purchases/payment/:id', 'PurchaseController', 'recordPayment');
$router->post('/purchases/cancel/:id',  'PurchaseController', 'cancel');
$router->get( '/purchases/return/:id',  'PurchaseReturnController', 'create');
$router->post('/purchases/return/:id',  'PurchaseReturnController', 'store');

// Suppliers
$router->get( '/suppliers',             'SupplierController', 'index');
$router->get( '/suppliers/create',      'SupplierController', 'create');
$router->post('/suppliers/create',      'SupplierController', 'store');
$router->get( '/suppliers/view/:id',    'SupplierController', 'show');
$router->get( '/suppliers/edit/:id',    'SupplierController', 'edit');
$router->post('/suppliers/edit/:id',    'SupplierController', 'update');
$router->post('/suppliers/delete/:id',  'SupplierController', 'delete');

// Products
$router->get( '/products',              'ProductController', 'index');
$router->get( '/products/create',       'ProductController', 'create');
$router->post('/products/create',       'ProductController', 'store');
$router->get( '/products/view/:id',     'ProductController', 'show');
$router->get( '/products/edit/:id',     'ProductController', 'edit');
$router->post('/products/edit/:id',     'ProductController', 'update');
$router->post('/products/delete/:id',   'ProductController', 'delete');
$router->get( '/products/generate-barcode', 'ProductController', 'generateBarcodeAjax');
$router->get( '/products/barcode/:id',      'ProductController', 'barcodeLabel');
$router->get( '/products/barcode-labels',   'ProductController', 'barcodeSheet');
$router->get( '/products/barcode-labels/print', 'ProductController', 'barcodeSheetPrint');
$router->get( '/products/search',       'ProductController', 'search');

// Stock
$router->get( '/stock',                   'StockController', 'index');
$router->get( '/stock/low',               'StockController', 'lowStock');
$router->get( '/stock/expiry',            'StockController', 'expiry');
$router->get( '/stock/movements',         'StockController', 'movements');
$router->get( '/stock/movements/:id',     'StockController', 'movements');
$router->get( '/stock/adjust/:id',        'StockController', 'adjustForm');
$router->post('/stock/adjust/:id',        'StockController', 'adjust');
$router->get( '/stock/adjust',            'StockController', 'adjustForm');
$router->post('/stock/adjust',            'StockController', 'adjust');

// Categories
$router->get( '/categories',            'CategoryController', 'index');
$router->post('/categories/store',      'CategoryController', 'store');
$router->post('/categories/update/:id', 'CategoryController', 'update');
$router->post('/categories/delete/:id', 'CategoryController', 'delete');

// Brands
$router->get( '/brands',                'BrandController', 'index');
$router->post('/brands/store',          'BrandController', 'store');
$router->post('/brands/update/:id',     'BrandController', 'update');
$router->post('/brands/delete/:id',     'BrandController', 'delete');

// Units
$router->get( '/units',                 'UnitController', 'index');
$router->post('/units/store',           'UnitController', 'store');
$router->post('/units/update/:id',      'UnitController', 'update');
$router->post('/units/delete/:id',      'UnitController', 'delete');

// Expenses
$router->get( '/expenses',              'ExpenseController', 'index');
$router->get( '/expenses/create',       'ExpenseController', 'create');
$router->post('/expenses/create',       'ExpenseController', 'store');
$router->post('/expenses/store',        'ExpenseController', 'store');
$router->get( '/expenses/edit/:id',     'ExpenseController', 'edit');
$router->post('/expenses/edit/:id',     'ExpenseController', 'update');
$router->post('/expenses/delete/:id',   'ExpenseController', 'delete');

// Cheques
$router->get( '/cheques',               'ChequesController', 'index');
$router->get( '/cheques/pending',       'ChequesController', 'pending');
$router->post('/cheques/status/:id',    'ChequesController', 'updateStatus');

// Finance / Credit Book
$router->get( '/finance/credit-book',             'FinanceController', 'creditBook');
$router->get( '/finance/overview',                'FinanceController', 'overview');
$router->get( '/finance/accounts',                'FinanceController', 'accounts');
$router->post('/finance/accounts/store',          'FinanceController', 'storeAccount');
$router->get( '/finance/accounts/edit/:id',       'FinanceController', 'editAccount');
$router->post('/finance/accounts/edit/:id',       'FinanceController', 'updateAccount');
$router->get( '/finance/accounts/:id',            'FinanceController', 'accountLedger');
$router->post('/finance/accounts/:id/transaction','FinanceController', 'transaction');
$router->post('/finance/accounts/:id/transfer',   'FinanceController', 'transfer');
$router->post('/finance/transactions/delete/:id', 'FinanceController', 'deleteTxn');
$router->get( '/finance/credit-book/:id',         'FinanceController', 'creditBookCustomer');
$router->post('/finance/credit-book/payment/:id', 'FinanceController', 'recordPayment');

// Reports
$router->get('/reports/daily',            'ReportController', 'daily');
$router->get('/reports/daily-attendance',  'ReportController', 'dailyAttendance');
$router->get('/admin/send-daily-report',   'ReportController', 'sendDailyReport');
$router->get('/reports/sales',            'ReportController', 'sales');
$router->get('/reports/profit-loss',      'ReportController', 'profitLoss');
$router->get('/reports/stock',            'ReportController', 'stock');
$router->get('/reports/customers',        'ReportController', 'customers');
$router->get('/reports/credit-customers', 'ReportController', 'creditCustomers');
$router->get('/reports/suppliers',        'ReportController', 'suppliers');
$router->get('/reports/expenses',         'ReportController', 'expenses');
$router->get('/reports/tax',              'ReportController', 'tax');
$router->get('/reports/cashier',          'ReportController', 'cashier');
$router->get('/reports/cashier/:id',      'ReportController', 'cashierDetail');

// Branches
$router->get( '/branches',                  'BranchController', 'index');
$router->get( '/branches/create',           'BranchController', 'create');
$router->post('/branches/create',           'BranchController', 'store');
$router->get( '/branches/edit/:id',         'BranchController', 'edit');
$router->post('/branches/edit/:id',         'BranchController', 'update');
$router->post('/branches/delete/:id',       'BranchController', 'delete');
$router->post('/branches/assign-user',      'BranchController', 'assignUser');
$router->post('/branches/assign-all',       'BranchController', 'assignAll');

// Users
$router->get( '/users',                 'UserController', 'index');
$router->get( '/users/create',          'UserController', 'create');
$router->post('/users/create',          'UserController', 'store');
$router->get( '/users/edit/:id',        'UserController', 'edit');
$router->post('/users/edit/:id',        'UserController', 'update');
$router->post('/users/delete/:id',      'UserController', 'delete');
$router->post('/users/toggle/:id',      'UserController', 'toggle');
$router->post('/users/role/:id',        'UserController', 'changeRole');

// Roles
$router->get( '/roles',                 'RoleController', 'index');
$router->get( '/roles/create',          'RoleController', 'create');
$router->post('/roles/create',          'RoleController', 'store');
$router->get( '/roles/edit/:id',        'RoleController', 'edit');
$router->post('/roles/edit/:id',        'RoleController', 'update');
$router->post('/roles/delete/:id',      'RoleController', 'delete');

// Settings
$router->get( '/settings',              'SettingController', 'index');
$router->post('/settings',              'SettingController', 'update');
$router->get( '/settings/test-print',   'SettingController', 'testPrint');
$router->get( '/settings/kiosk-bat',    'SettingController', 'downloadKioskBat');
$router->get( '/settings/sms-status',   'SettingController', 'smsStatus');
$router->get( '/settings/sms-test',     'SettingController', 'smsTest');

// Backup
$router->get( '/backup',                     'BackupController', 'index');
$router->post('/backup/create',              'BackupController', 'create');
$router->get( '/backup/download/:file',      'BackupController', 'download');
$router->post('/backup/delete/:file',        'BackupController', 'delete');
$router->post('/backup/restore/:file',       'BackupController', 'restore');
$router->post('/backup/upload',              'BackupController', 'upload');

// Logs (Super Admin only)
$router->get('/logs/online',        'LogController', 'onlineUsers');
$router->get('/logs/online-status', 'LogController', 'onlineStatus');
$router->get('/logs/activity',      'LogController', 'auditLogs');

// Stimulsoft Reports
$router->get('/sti-reports',                  'StiReportController', 'index');
$router->get('/sti-reports/view/:type',       'StiReportController', 'show');
$router->get('/sti-reports/data/:type',       'StiReportController', 'data');
$router->get('/sti-reports/export/:type',     'StiReportController', 'export');

// Developer Tools
$router->get( '/developer',               'DeveloperController', 'index');
$router->get( '/developer/clear',         'DeveloperController', 'clearConfirm');
$router->post('/developer/clear-execute', 'DeveloperController', 'clearExecute');

// Payroll
$router->get( '/payroll',                          'PayrollController', 'index');
$router->get( '/payroll/employees',                'PayrollController', 'employees');
$router->get( '/payroll/employees/view/:id',       'PayrollController', 'viewEmployee');
$router->get( '/payroll/employees/add',            'PayrollController', 'addEmployee');
$router->post('/payroll/employees/toggle/:id',     'PayrollController', 'toggleEmployee');
$router->post('/payroll/employees/delete/:id',     'PayrollController', 'deleteEmployee');
$router->post('/payroll/employees/add',            'PayrollController', 'storeEmployee');
$router->get( '/payroll/employees/edit/:id',       'PayrollController', 'editEmployee');
$router->post('/payroll/employees/edit/:id',       'PayrollController', 'updateEmployee');
$router->post('/payroll/create-period',            'PayrollController', 'createPeriod');
$router->get( '/payroll/process/:id',              'PayrollController', 'process');
$router->post('/payroll/save/:id',                 'PayrollController', 'savePayroll');
$router->post('/payroll/approve/:id',              'PayrollController', 'approve');
$router->post('/payroll/mark-paid/:id',            'PayrollController', 'markPaid');
$router->get( '/payroll/payslip/:id',              'PayrollController', 'payslip');
$router->get( '/payroll/advances',                 'PayrollController', 'advances');
$router->post('/payroll/advances',                 'PayrollController', 'storeAdvance');
$router->get( '/payroll/report',                   'PayrollController', 'report');

// FP Upload
$router->get( '/payroll/fp-upload',          'FPUploadController', 'index');
$router->get( '/payroll/fp-upload/template', 'FPUploadController', 'template');
$router->post('/payroll/fp-import',          'FPUploadController', 'import');
$router->get( '/payroll/attendance-chart',   'FPUploadController', 'chart');

// ── Dispatch ────────────────────────────────────────────────
// FPUploadController — inline
if (!class_exists('FPUploadController')) {
class FPUploadController extends Controller
{
    public function index(): void
    {
        Auth::requirePermission('payroll.view');
        $pageTitle  = 'Fingerprint Data Uploader';
        $breadcrumb = [
            ['label'=>'Payroll','url'=>APP_URL.'/payroll'],
            ['label'=>'FP Data Upload'],
        ];
        ob_start(); require APP_PATH.'/views/payroll/fp_upload.php';
        $content = ob_get_clean();
        require APP_PATH.'/views/layouts/main.php';
    }

    public function template(): void
    {
        Auth::requirePermission('payroll.view');
        header('Content-Type: text/csv; charset=utf-8');
        header('Content-Disposition: attachment; filename="fingerprint_template.csv"');
        $out = fopen('php://output','w');
        fputcsv($out,['UserID','Date','Time','Status','DeviceID']);
        try {
            $emps = Database::fetchAll(
                "SELECT fingerprint_id,emp_code,full_name FROM employees WHERE status='active' ORDER BY emp_code"
            );
            foreach ($emps as $e) {
                fputcsv($out,[$e['fingerprint_id']?:$e['emp_code'],date('Y-m-d'),'08:00:00','0','ZK-DEVICE-01']);
                fputcsv($out,[$e['fingerprint_id']?:$e['emp_code'],date('Y-m-d'),'17:00:00','1','ZK-DEVICE-01']);
            }
        } catch(Exception $e) {
            fputcsv($out,['FP001',date('Y-m-d'),'08:00:00','0','ZK-DEVICE-01']);
            fputcsv($out,['FP001',date('Y-m-d'),'17:00:00','1','ZK-DEVICE-01']);
        }
        fclose($out); exit;
    }

    public function import(): void
    {
        Auth::requirePermission('payroll.manage');
        Auth::requireCsrf();
        header('Content-Type: application/json');

        $rows = json_decode($this->post('rows','[]'), true);
        $imported = 0; $errors = 0;

        foreach ((array)$rows as $row) {
            $fpId = trim($row['empId'] ?? '');
            $date = $this->parseDate(trim($row['date'] ?? ''));
            $time = trim($row['time'] ?? '') ?: null;
            $type = $row['type'] ?? 'in';

            if (!$fpId || !$date) { $errors++; continue; }

            try {
                $emp = Database::fetchOne(
                    "SELECT id FROM employees WHERE (fingerprint_id=? OR emp_code=?) AND status='active'",
                    [$fpId,$fpId]
                );
                if (!$emp) { $errors++; continue; }

                $existing = Database::fetchOne(
                    'SELECT * FROM employee_attendance WHERE employee_id=? AND att_date=?',
                    [$emp['id'],$date]
                );

                if ($type === 'in') {
                    if (!$existing) {
                        Database::execute(
                            "INSERT INTO employee_attendance (employee_id,att_date,check_in,status,method,fingerprint_id,recorded_by)
                             VALUES (?,?,?,'present','fingerprint',?,?)",
                            [$emp['id'],$date,$time,$fpId,Auth::id()]
                        );
                    } elseif (!$existing['check_in']) {
                        Database::execute(
                            "UPDATE employee_attendance SET check_in=?,method='fingerprint',fingerprint_id=? WHERE id=?",
                            [$time,$fpId,$existing['id']]
                        );
                    }
                } elseif ($type === 'out' && $existing) {
                    $inTs  = $existing['check_in'] ? strtotime($date.' '.$existing['check_in']) : 0;
                    $outTs = $time ? strtotime($date.' '.$time) : 0;
                    $wh    = ($inTs&&$outTs) ? round(min(max(0,($outTs-$inTs)/3600),12),2) : 0;
                    $oth   = round(max(0,$wh-9),2);
                    Database::execute(
                        'UPDATE employee_attendance SET check_out=?,worked_hours=?,ot_hours=? WHERE id=?',
                        [$time,$wh,$oth,$existing['id']]
                    );
                }

                // Log to fingerprint_logs if table exists
                try {
                    if ($time) {
                        Database::execute(
                            "INSERT INTO fingerprint_logs (device_id,device_user_id,employee_id,punch_time,punch_type,status)
                             VALUES ('IMPORT',?,?,?,'$type','processed')",
                            [$fpId,$emp['id'],$date.' '.$time]
                        );
                    }
                } catch(Exception $e) {}

                $imported++;
            } catch(Exception $e) { $errors++; }
        }

        try { Auth::logAudit(Auth::id(),'fp_import','attendance',"FP import: {$imported} records, {$errors} errors"); } catch(Exception $e){}
        echo json_encode(['imported'=>$imported,'errors'=>$errors]);
        exit;
    }

    private function parseDate(string $raw): string
    {
        if (!$raw) return '';
        foreach (['Y-m-d','d/m/Y','m/d/Y','d-m-Y','Ymd','d M Y','Y/m/d'] as $fmt) {
            $dt = \DateTime::createFromFormat($fmt, $raw);
            if ($dt) return $dt->format('Y-m-d');
        }
        $ts = strtotime($raw);
        return $ts ? date('Y-m-d',$ts) : '';
    }
    // ── Attendance Chart ──────────────────────────────────────
    public function chart(): void
    {
        Auth::requirePermission('payroll.view');

        $month = (int)$this->get('month', date('n'));
        $year  = (int)$this->get('year',  date('Y'));
        $monthLabel   = date('F Y', mktime(0,0,0,$month,1,$year));
        $daysInMonth  = cal_days_in_month(CAL_GREGORIAN,$month,$year);
        $monthStart   = sprintf('%04d-%02d-01',$year,$month);
        $monthEnd     = sprintf('%04d-%02d-%02d',$year,$month,$daysInMonth);

        // Count actual working days (exclude Sundays)
        $workingDays = 0;
        for($d=1;$d<=$daysInMonth;$d++) {
            if(date('w',mktime(0,0,0,$month,$d,$year)) != 0) $workingDays++;
        }

        // All active employees
        $employees = Database::fetchAll(
            "SELECT id,emp_code,full_name,fingerprint_id FROM employees WHERE status='active' ORDER BY full_name"
        );
        $totalEmployees = count($employees);

        // Per-employee stats
        $empStats = Database::fetchAll(
            "SELECT e.id,e.emp_code,e.full_name,e.fingerprint_id,
                    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='leave'   THEN 1 END) AS leave_days,
                    COUNT(CASE WHEN ea.status='half_day'THEN 1 END) AS half_day,
                    COALESCE(SUM(ea.ot_hours),0)     AS ot_hours,
                    COALESCE(SUM(ea.worked_hours),0) AS worked_hours
             FROM employees e
             LEFT JOIN employee_attendance ea
               ON ea.employee_id=e.id AND ea.att_date BETWEEN ? AND ?
             WHERE e.status='active'
             GROUP BY e.id ORDER BY e.full_name",
            [$monthStart,$monthEnd]
        );

        // Totals
        $totalPresent   = array_sum(array_column($empStats,'present'));
        $totalAbsent    = array_sum(array_column($empStats,'absent'));
        $totalLate      = array_sum(array_column($empStats,'late'));
        $totalLeave     = array_sum(array_column($empStats,'leave_days'));
        $totalHalfDay   = array_sum(array_column($empStats,'half_day'));
        $totalOT        = array_sum(array_column($empStats,'ot_hours'));
        $totalWorkedHrs = array_sum(array_column($empStats,'worked_hours'));

        // FP count
        try {
            $fpCount = (int)Database::fetchColumn(
                "SELECT COUNT(*) FROM employee_attendance
                 WHERE method='fingerprint' AND att_date BETWEEN ? AND ?",
                [$monthStart,$monthEnd]
            );
        } catch(Exception $e) { $fpCount = 0; }

        // Daily data for trend chart
        $dailyRaw = Database::fetchAll(
            "SELECT att_date,
                    COUNT(CASE WHEN status='present' THEN 1 END) AS present,
                    COUNT(CASE WHEN status='absent'  THEN 1 END) AS absent,
                    COUNT(CASE WHEN status='late'    THEN 1 END) AS late,
                    COUNT(CASE WHEN status='leave'   THEN 1 END) AS leave_days
             FROM employee_attendance
             WHERE att_date BETWEEN ? AND ? AND status != 'off'
             GROUP BY att_date ORDER BY att_date",
            [$monthStart,$monthEnd]
        );
        $dailyMap = [];
        foreach($dailyRaw as $r) {
            $dailyMap[$r['att_date']] = $r;
        }
        $dailyData = [];
        for($d=1;$d<=$daysInMonth;$d++) {
            $dow = date('w',mktime(0,0,0,$month,$d,$year));
            if($dow == 0) continue; // skip Sunday
            $dt  = sprintf('%04d-%02d-%02d',$year,$month,$d);
            $rec = $dailyMap[$dt] ?? ['present'=>0,'absent'=>0,'late'=>0,'leave_days'=>0];
            $dailyData[] = [
                'day'     => $d.'\n'.date('D',mktime(0,0,0,$month,$d,$year)),
                'present' => (int)$rec['present'],
                'absent'  => (int)$rec['absent'],
                'late'    => (int)$rec['late'],
                'leave'   => (int)($rec['leave_days'] ?? 0),
            ];
        }

        // Status breakdown for doughnut
        $statusData = [
            ['label'=>'Present', 'value'=>$totalPresent],
            ['label'=>'Absent',  'value'=>$totalAbsent],
            ['label'=>'Late',    'value'=>$totalLate],
            ['label'=>'Leave',   'value'=>$totalLeave],
            ['label'=>'Half Day','value'=>$totalHalfDay],
        ];

        // Method breakdown
        $methodRaw = Database::fetchAll(
            "SELECT method, COUNT(*) AS cnt FROM employee_attendance
             WHERE att_date BETWEEN ? AND ? AND status NOT IN ('off','absent')
             GROUP BY method ORDER BY cnt DESC",
            [$monthStart,$monthEnd]
        );
        $methodLabels = ['fingerprint'=>'Fingerprint','manual'=>'Manual','qr'=>'QR Code','card'=>'Card','face'=>'Face'];
        $methodData = array_map(fn($r)=>[
            'label' => $methodLabels[$r['method']]??ucfirst($r['method']),
            'value' => (int)$r['cnt'],
        ], $methodRaw);

        // OT data (top 10 by OT hours)
        $otData = array_map(fn($e)=>[
            'name'  => explode(' ',$e['full_name'])[0],
            'hours' => (float)$e['ot_hours'],
        ], array_filter($empStats,fn($e)=>$e['ot_hours']>0));
        usort($otData,fn($a,$b)=>$b['hours']<=>$a['hours']);
        $otData = array_slice($otData,0,10);

        $pageTitle  = 'Attendance Analytics — '.$monthLabel;
        $breadcrumb = [
            ['label'=>'Payroll','url'=>APP_URL.'/payroll'],
            ['label'=>'Attendance Analytics'],
        ];
        ob_start(); require APP_PATH.'/views/payroll/attendance_chart.php';
        $content = ob_get_clean();
        require APP_PATH.'/views/layouts/main.php';
    }

}
}

// ── Dispatch the request — must run AFTER all inline controller ──
// ── classes above have been defined, otherwise "Class not found" ──
$router->dispatch();