<?php
// app/Controllers/InventoryController.php

class InventoryController {

    private function requireInventory() {
        if (!isset($_SESSION['user_id'])) {
            header('Location: /restpo/login'); exit;
        }
        $allowed = ['admin', 'inventory', 'cashier'];
        if (!in_array($_SESSION['role'], $allowed)) {
            header('Location: /restpo/login'); exit;
        }
        FeatureGate::enforceInventory($_SESSION['restaurant_id']);
    }

    private function getContext() {
        $role    = $_SESSION['role'];
        $rest_id = $_SESSION['restaurant_id'];

        if ($role === 'inventory') {
            // Inventory manager: their cashier_id is stored in session
            $db     = getDB();
            $stmt   = $db->prepare('SELECT cashier_id FROM users WHERE id = ?');
            $stmt->execute([$_SESSION['user_id']]);
            $u      = $stmt->fetch();
            return [
                'rest_id'    => $rest_id,
                'cashier_id' => $u['cashier_id'] ?? null,
                'is_admin'   => false,
            ];
        }

        if ($role === 'cashier') {
            return [
                'rest_id'    => $rest_id,
                'cashier_id' => $_SESSION['user_id'],
                'is_admin'   => false,
            ];
        }

        // Admin: can switch cashier context via GET/session
        $cashier_id = null;
        if (!empty($_GET['cashier_id'])) {
            $cashier_id = (int)$_GET['cashier_id'];
            $_SESSION['inv_cashier_id'] = $cashier_id;
        } elseif (isset($_SESSION['inv_cashier_id'])) {
            $cashier_id = (int)$_SESSION['inv_cashier_id'];
        }
        return [
            'rest_id'    => $rest_id,
            'cashier_id' => $cashier_id ?: null,
            'is_admin'   => true,
        ];
    }

    // ─── INDEX ───────────────────────────────────────────────────────────────
    public function index() {
        $this->requireInventory();
        $db  = getDB();
        $ctx = $this->getContext();
        $rest_id    = $ctx['rest_id'];
        $cashier_id = $ctx['cashier_id'];

        FeatureGate::enforceSubscription($rest_id);

        // Load cashiers for admin switcher
        $cashiers = [];
        if ($ctx['is_admin']) {
            $cs = $db->prepare('SELECT id, name FROM users WHERE restaurant_id = ? AND role = "cashier" AND status = "active" ORDER BY name ASC');
            $cs->execute([$rest_id]);
            $cashiers = $cs->fetchAll();
        }

        // Load current cashier name
        $cashierName = null;
        if ($cashier_id) {
            $cn = $db->prepare('SELECT name FROM users WHERE id = ? AND restaurant_id = ?');
            $cn->execute([$cashier_id, $rest_id]);
            $cashierName = $cn->fetchColumn();
        }

        // Load items with joined categories
        if ($cashier_id) {
            $stmt = $db->prepare('
                SELECT ii.*, ic.name as category_name 
                FROM inventory_items ii 
                LEFT JOIN inventory_categories ic ON ic.id = ii.category_id 
                WHERE ii.restaurant_id = ? AND ii.cashier_id = ? AND ii.status = "active" 
                ORDER BY ii.name ASC
            ');
            $stmt->execute([$rest_id, $cashier_id]);
        } else {
            $stmt = $db->prepare('
                SELECT ii.*, ic.name as category_name 
                FROM inventory_items ii 
                LEFT JOIN inventory_categories ic ON ic.id = ii.category_id 
                WHERE ii.restaurant_id = ? AND ii.status = "active" 
                ORDER BY ii.name ASC
            ');
            $stmt->execute([$rest_id]);
        }
        $items = $stmt->fetchAll();

        // Load categories for modals/filters
        if ($cashier_id) {
            $catStmt = $db->prepare('SELECT * FROM inventory_categories WHERE restaurant_id = ? AND (cashier_id = ? OR cashier_id IS NULL) AND status = "active" ORDER BY name ASC');
            $catStmt->execute([$rest_id, $cashier_id]);
        } else {
            $catStmt = $db->prepare('SELECT * FROM inventory_categories WHERE restaurant_id = ? AND status = "active" ORDER BY name ASC');
            $catStmt->execute([$rest_id]);
        }
        $categoriesList = $catStmt->fetchAll();

        // Load suppliers for modals
        if ($cashier_id) {
            $supStmt = $db->prepare('SELECT * FROM inventory_suppliers WHERE restaurant_id = ? AND (cashier_id = ? OR cashier_id IS NULL) AND status = "active" ORDER BY name ASC');
            $supStmt->execute([$rest_id, $cashier_id]);
        } else {
            $supStmt = $db->prepare('SELECT * FROM inventory_suppliers WHERE restaurant_id = ? AND status = "active" ORDER BY name ASC');
            $supStmt->execute([$rest_id]);
        }
        $suppliersList = $supStmt->fetchAll();

        // Summary stats
        $totalItems  = count($items);
        $lowStock    = array_filter($items, fn($i) => $i['quantity'] <= $i['low_stock_threshold'] && $i['quantity'] > 0);
        $outOfStock  = array_filter($items, fn($i) => $i['quantity'] <= 0);
        $totalValue  = array_sum(array_map(fn($i) => (float)$i['quantity'] * (float)($i['cost_price'] ?? 0), $items));

        // Today's movement totals
        $todayWhere = $ctx['cashier_id']
            ? 'WHERE l.restaurant_id = ? AND l.cashier_id = ? AND DATE(l.created_at) = CURDATE()'
            : 'WHERE l.restaurant_id = ? AND DATE(l.created_at) = CURDATE()';
        $todayParams = $ctx['cashier_id'] ? [$rest_id, $ctx['cashier_id']] : [$rest_id];
        $tStmt = $db->prepare("SELECT type, SUM(ABS(quantity_change)) as total FROM inventory_logs l $todayWhere GROUP BY type");
        $tStmt->execute($todayParams);
        $todayMovements = ['stock_in' => 0, 'stock_out' => 0, 'waste' => 0];
        foreach ($tStmt->fetchAll() as $row) {
            if (isset($todayMovements[$row['type']])) {
                $todayMovements[$row['type']] = (float)$row['total'];
            }
        }

        $pageTitle = 'Inventory Manager';

        ob_start();
        require 'app/Views/inventory/index.php';
        $content = ob_get_clean();

        if (in_array($_SESSION['role'], ['admin', 'cashier'])) {
            require 'app/Views/layouts/admin.php';
        } else {
            require 'app/Views/layouts/inventory.php';
        }
    }

    // ─── STORE (Add Item) ─────────────────────────────────────────────────────
    public function store() {
        $this->requireInventory();
        $db  = getDB();
        $ctx = $this->getContext();

        $name        = trim($_POST['name'] ?? '');
        $sku         = trim($_POST['sku'] ?? '') ?: null;
        $category_id = !empty($_POST['category_id']) ? (int)$_POST['category_id'] : null;
        $unit        = trim($_POST['unit'] ?? 'pcs');
        $qty         = (float)($_POST['quantity'] ?? 0);
        $low         = (float)($_POST['low_stock_threshold'] ?? 10);
        $cost        = !empty($_POST['cost_price']) ? (float)$_POST['cost_price'] : null;
        $notes       = trim($_POST['notes'] ?? '') ?: null;

        if (!$name) {
            $this->redirect('error', 'Item name is required.', $ctx);
        }

        // Fetch category name text for backward compatibility
        $categoryName = null;
        if ($category_id) {
            $catNameStmt = $db->prepare('SELECT name FROM inventory_categories WHERE id = ?');
            $catNameStmt->execute([$category_id]);
            $categoryName = $catNameStmt->fetchColumn() ?: null;
        }

        $stmt = $db->prepare('INSERT INTO inventory_items (restaurant_id, cashier_id, name, sku, category_id, category, unit, quantity, low_stock_threshold, cost_price, notes) VALUES (?,?,?,?,?,?,?,?,?,?,?)');
        $stmt->execute([$ctx['rest_id'], $ctx['cashier_id'], $name, $sku, $category_id, $categoryName, $unit, $qty, $low, $cost, $notes]);
        $itemId = $db->lastInsertId();

        // Log initial stock
        if ($qty > 0) {
            $db->prepare('INSERT INTO inventory_logs (inventory_item_id, restaurant_id, cashier_id, user_id, type, quantity_change, quantity_before, quantity_after, unit_price, supplier_id, notes) VALUES (?,?,?,?,?,?,?,?,?,?,?)')
               ->execute([$itemId, $ctx['rest_id'], $ctx['cashier_id'], $_SESSION['user_id'], 'initial', $qty, 0, $qty, $cost, null, 'Initial stock']);
        }

        $this->redirect('success', 'Item added successfully.', $ctx);
    }

    // ─── STOCK IN ─────────────────────────────────────────────────────────────
    public function stockIn() {
        $this->requireInventory();
        $db  = getDB();
        $ctx = $this->getContext();

        $item_id     = (int)($_POST['item_id'] ?? 0);
        $qty         = (float)($_POST['quantity'] ?? 0);
        $supplier_id = !empty($_POST['supplier_id']) ? (int)$_POST['supplier_id'] : null;
        $unit_price  = !empty($_POST['unit_price']) ? (float)$_POST['unit_price'] : null;
        $reference   = trim($_POST['reference'] ?? '') ?: null;
        $notes       = trim($_POST['notes'] ?? '') ?: null;

        if (!$item_id || $qty <= 0) {
            $this->redirect('error', 'Invalid stock-in data.', $ctx);
        }

        $item = $this->getItem($item_id, $ctx);
        if (!$item) { $this->redirect('error', 'Item not found.', $ctx); }

        $before = (float)$item['quantity'];
        $after  = $before + $qty;

        // Update item quantity and cost_price (using transaction unit price if provided)
        if ($unit_price !== null) {
            $db->prepare('UPDATE inventory_items SET quantity = ?, cost_price = ? WHERE id = ?')->execute([$after, $unit_price, $item_id]);
        } else {
            $db->prepare('UPDATE inventory_items SET quantity = ? WHERE id = ?')->execute([$after, $item_id]);
        }

        // Insert into inventory_logs including supplier_id and unit_price
        $db->prepare('INSERT INTO inventory_logs (inventory_item_id, restaurant_id, cashier_id, user_id, type, quantity_change, quantity_before, quantity_after, unit_price, supplier_id, reference, notes) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)')
           ->execute([$item_id, $ctx['rest_id'], $ctx['cashier_id'], $_SESSION['user_id'], 'stock_in', $qty, $before, $after, $unit_price, $supplier_id, $reference, $notes]);

        $this->redirect('success', "Stock in recorded: +$qty " . htmlspecialchars($item['unit']), $ctx);
    }

    // ─── STOCK OUT ────────────────────────────────────────────────────────────
    public function stockOut() {
        $this->requireInventory();
        $db  = getDB();
        $ctx = $this->getContext();

        $item_id   = (int)($_POST['item_id'] ?? 0);
        $qty       = (float)($_POST['quantity'] ?? 0);
        $reference = trim($_POST['reference'] ?? '') ?: null;
        $notes     = trim($_POST['notes'] ?? '') ?: null;

        if (!$item_id || $qty <= 0) {
            $this->redirect('error', 'Invalid stock-out data.', $ctx);
        }

        $item = $this->getItem($item_id, $ctx);
        if (!$item) { $this->redirect('error', 'Item not found.', $ctx); }

        $before = (float)$item['quantity'];
        $after  = max(0, $before - $qty);

        $db->prepare('UPDATE inventory_items SET quantity = ? WHERE id = ?')->execute([$after, $item_id]);
        $db->prepare('INSERT INTO inventory_logs (inventory_item_id, restaurant_id, cashier_id, user_id, type, quantity_change, quantity_before, quantity_after, reference, notes) VALUES (?,?,?,?,?,?,?,?,?,?)')
           ->execute([$item_id, $ctx['rest_id'], $ctx['cashier_id'], $_SESSION['user_id'], 'stock_out', -$qty, $before, $after, $reference, $notes]);

        $this->redirect('success', "Stock out recorded: -$qty " . htmlspecialchars($item['unit']), $ctx);
    }

    // ─── ADJUSTMENT ───────────────────────────────────────────────────────────
    public function adjust() {
        $this->requireInventory();
        $db  = getDB();
        $ctx = $this->getContext();

        $item_id = (int)($_POST['item_id'] ?? 0);
        $new_qty = (float)($_POST['new_quantity'] ?? 0);
        $notes   = trim($_POST['notes'] ?? '') ?: 'Manual adjustment';

        if (!$item_id) {
            $this->redirect('error', 'Invalid item.', $ctx);
        }

        $item   = $this->getItem($item_id, $ctx);
        if (!$item) { $this->redirect('error', 'Item not found.', $ctx); }

        $before = (float)$item['quantity'];
        $change = $new_qty - $before;

        $db->prepare('UPDATE inventory_items SET quantity = ? WHERE id = ?')->execute([$new_qty, $item_id]);
        $db->prepare('INSERT INTO inventory_logs (inventory_item_id, restaurant_id, cashier_id, user_id, type, quantity_change, quantity_before, quantity_after, notes) VALUES (?,?,?,?,?,?,?,?,?)')
           ->execute([$item_id, $ctx['rest_id'], $ctx['cashier_id'], $_SESSION['user_id'], 'adjustment', $change, $before, $new_qty, $notes]);

        $this->redirect('success', 'Stock adjustment saved.', $ctx);
    }

    // ─── WASTE ────────────────────────────────────────────────────────────────
    public function waste() {
        $this->requireInventory();
        $db  = getDB();
        $ctx = $this->getContext();

        $item_id = (int)($_POST['item_id'] ?? 0);
        $qty     = (float)($_POST['quantity'] ?? 0);
        $reason  = trim($_POST['reason'] ?? 'Other');
        $notes   = trim($_POST['notes'] ?? '') ?: null;

        if (!$item_id || $qty <= 0) {
            $this->redirect('error', 'Invalid waste data.', $ctx);
        }

        $item = $this->getItem($item_id, $ctx);
        if (!$item) { $this->redirect('error', 'Item not found.', $ctx); }

        $before = (float)$item['quantity'];
        $after  = max(0, $before - $qty);
        $logNotes = $reason . ($notes ? ' — ' . $notes : '');

        $db->prepare('UPDATE inventory_items SET quantity = ? WHERE id = ?')->execute([$after, $item_id]);
        $db->prepare('INSERT INTO inventory_logs (inventory_item_id, restaurant_id, cashier_id, user_id, type, quantity_change, quantity_before, quantity_after, notes) VALUES (?,?,?,?,?,?,?,?,?)')
           ->execute([$item_id, $ctx['rest_id'], $ctx['cashier_id'], $_SESSION['user_id'], 'waste', -$qty, $before, $after, $logNotes]);

        $this->redirect('success', "Waste recorded: -$qty " . htmlspecialchars($item['unit']) . " ($reason)", $ctx);
    }

    // ─── UPDATE (Edit Item) ───────────────────────────────────────────────────
    public function update() {
        $this->requireInventory();
        $db  = getDB();
        $ctx = $this->getContext();

        $item_id     = (int)($_POST['item_id'] ?? 0);
        $name        = trim($_POST['name'] ?? '');
        $sku         = trim($_POST['sku'] ?? '') ?: null;
        $category_id = !empty($_POST['category_id']) ? (int)$_POST['category_id'] : null;
        $unit        = trim($_POST['unit'] ?? 'pcs');
        $low         = (float)($_POST['low_stock_threshold'] ?? 10);
        $cost        = !empty($_POST['cost_price']) ? (float)$_POST['cost_price'] : null;
        $notes       = trim($_POST['notes'] ?? '') ?: null;

        if (!$item_id || !$name) {
            $this->redirect('error', 'Item name is required.', $ctx);
        }

        $item = $this->getItem($item_id, $ctx);
        if (!$item) { $this->redirect('error', 'Item not found.', $ctx); }

        // Fetch category name text for backward compatibility
        $categoryName = null;
        if ($category_id) {
            $catNameStmt = $db->prepare('SELECT name FROM inventory_categories WHERE id = ?');
            $catNameStmt->execute([$category_id]);
            $categoryName = $catNameStmt->fetchColumn() ?: null;
        }

        $db->prepare('UPDATE inventory_items SET name=?, sku=?, category_id=?, category=?, unit=?, low_stock_threshold=?, cost_price=?, notes=? WHERE id=?')
           ->execute([$name, $sku, $category_id, $categoryName, $unit, $low, $cost, $notes, $item_id]);

        $this->redirect('success', 'Item updated successfully.', $ctx);
    }

    // ─── DELETE ───────────────────────────────────────────────────────────────
    public function delete() {
        $this->requireInventory();
        $db  = getDB();
        $ctx = $this->getContext();

        $item_id = (int)($_POST['item_id'] ?? 0);
        if (!$item_id) { $this->redirect('error', 'Invalid item.', $ctx); }

        $item = $this->getItem($item_id, $ctx);
        if (!$item) { $this->redirect('error', 'Item not found.', $ctx); }

        $db->prepare('UPDATE inventory_items SET status = "inactive" WHERE id = ?')->execute([$item_id]);
        $this->redirect('success', 'Item removed from inventory.', $ctx);
    }

    // ─── LOGS (API) ───────────────────────────────────────────────────────────
    public function logs() {
        $this->requireInventory();
        $db  = getDB();
        $ctx = $this->getContext();

        $item_id = (int)($_GET['item_id'] ?? 0);
        if (!$item_id) { echo json_encode([]); exit; }

        $item = $this->getItem($item_id, $ctx);
        if (!$item) { echo json_encode([]); exit; }

        $conditions = ['l.inventory_item_id = ?'];
        $params     = [$item_id];

        $typeFilter = $_GET['type'] ?? '';
        if ($typeFilter && in_array($typeFilter, ['stock_in','stock_out','waste','adjustment','initial'])) {
            $conditions[] = 'l.type = ?';
            $params[]     = $typeFilter;
        }
        $dateFrom = $_GET['date_from'] ?? '';
        $dateTo   = $_GET['date_to']   ?? '';
        if ($dateFrom) { $conditions[] = 'DATE(l.created_at) >= ?'; $params[] = $dateFrom; }
        if ($dateTo)   { $conditions[] = 'DATE(l.created_at) <= ?'; $params[] = $dateTo; }

        $where = implode(' AND ', $conditions);
        $logs  = $db->prepare("
            SELECT l.*, u.name as user_name, ii.name as item_name, ii.unit as item_unit, s.name as supplier_name
            FROM inventory_logs l
            LEFT JOIN users u ON u.id = l.user_id
            LEFT JOIN inventory_items ii ON ii.id = l.inventory_item_id
            LEFT JOIN inventory_suppliers s ON s.id = l.supplier_id
            WHERE $where
            ORDER BY l.created_at DESC
            LIMIT 100
        ");
        $logs->execute($params);
        header('Content-Type: application/json');
        echo json_encode($logs->fetchAll(PDO::FETCH_ASSOC));
        exit;
    }

    // ─── CATEGORIES CRUD ──────────────────────────────────────────────────────
    public function categories() {
        $this->requireInventory();
        $db  = getDB();
        $ctx = $this->getContext();
        $rest_id    = $ctx['rest_id'];
        $cashier_id = $ctx['cashier_id'];

        FeatureGate::enforceSubscription($rest_id);

        // Load cashiers for admin switcher
        $cashiers = [];
        if ($ctx['is_admin']) {
            $cs = $db->prepare('SELECT id, name FROM users WHERE restaurant_id = ? AND role = "cashier" AND status = "active" ORDER BY name ASC');
            $cs->execute([$rest_id]);
            $cashiers = $cs->fetchAll();
        }

        // Load current cashier name
        $cashierName = null;
        if ($cashier_id) {
            $cn = $db->prepare('SELECT name FROM users WHERE id = ? AND restaurant_id = ?');
            $cn->execute([$cashier_id, $rest_id]);
            $cashierName = $cn->fetchColumn();
        }

        // Get categories with count of active items in each category
        if ($cashier_id) {
            $stmt = $db->prepare('
                SELECT c.*, COUNT(ii.id) as item_count 
                FROM inventory_categories c
                LEFT JOIN inventory_items ii ON ii.category_id = c.id AND ii.status = "active" AND ii.cashier_id = ?
                WHERE c.restaurant_id = ? AND (c.cashier_id = ? OR c.cashier_id IS NULL) AND c.status = "active"
                GROUP BY c.id
                ORDER BY c.name ASC
            ');
            $stmt->execute([$cashier_id, $rest_id, $cashier_id]);
        } else {
            $stmt = $db->prepare('
                SELECT c.*, COUNT(ii.id) as item_count 
                FROM inventory_categories c
                LEFT JOIN inventory_items ii ON ii.category_id = c.id AND ii.status = "active"
                WHERE c.restaurant_id = ? AND c.status = "active"
                GROUP BY c.id
                ORDER BY c.name ASC
            ');
            $stmt->execute([$rest_id]);
        }
        $categories = $stmt->fetchAll();

        $pageTitle = 'Inventory Categories';

        ob_start();
        require 'app/Views/inventory/categories.php';
        $content = ob_get_clean();

        if (in_array($_SESSION['role'], ['admin', 'cashier'])) {
            require 'app/Views/layouts/admin.php';
        } else {
            require 'app/Views/layouts/inventory.php';
        }
    }

    public function categoryStore() {
        $this->requireInventory();
        $db  = getDB();
        $ctx = $this->getContext();

        $name = trim($_POST['name'] ?? '');
        $description = trim($_POST['description'] ?? '') ?: null;

        if (!$name) {
            $this->redirectPath('error', 'Category name is required.', 'categories', $ctx);
        }

        $stmt = $db->prepare('INSERT INTO inventory_categories (restaurant_id, cashier_id, name, description) VALUES (?, ?, ?, ?)');
        $stmt->execute([$ctx['rest_id'], $ctx['cashier_id'], $name, $description]);

        $this->redirectPath('success', 'Category created successfully.', 'categories', $ctx);
    }

    public function categoryUpdate() {
        $this->requireInventory();
        $db  = getDB();
        $ctx = $this->getContext();

        $id = (int)($_POST['id'] ?? 0);
        $name = trim($_POST['name'] ?? '');
        $description = trim($_POST['description'] ?? '') ?: null;

        if (!$id || !$name) {
            $this->redirectPath('error', 'Invalid category data.', 'categories', $ctx);
        }

        // Verify ownership
        $chk = $db->prepare('SELECT id FROM inventory_categories WHERE id = ? AND restaurant_id = ?');
        $chk->execute([$id, $ctx['rest_id']]);
        if (!$chk->fetchColumn()) {
            $this->redirectPath('error', 'Category not found.', 'categories', $ctx);
        }

        $stmt = $db->prepare('UPDATE inventory_categories SET name = ?, description = ? WHERE id = ?');
        $stmt->execute([$name, $description, $id]);

        $this->redirectPath('success', 'Category updated successfully.', 'categories', $ctx);
    }

    public function categoryDelete() {
        $this->requireInventory();
        $db  = getDB();
        $ctx = $this->getContext();

        $id = (int)($_POST['id'] ?? 0);
        if (!$id) {
            $this->redirectPath('error', 'Invalid category.', 'categories', $ctx);
        }

        // Verify ownership
        $chk = $db->prepare('SELECT id FROM inventory_categories WHERE id = ? AND restaurant_id = ?');
        $chk->execute([$id, $ctx['rest_id']]);
        if (!$chk->fetchColumn()) {
            $this->redirectPath('error', 'Category not found.', 'categories', $ctx);
        }

        $stmt = $db->prepare('UPDATE inventory_categories SET status = "inactive" WHERE id = ?');
        $stmt->execute([$id]);

        $this->redirectPath('success', 'Category deactivated successfully.', 'categories', $ctx);
    }

    // ─── SUPPLIERS CRUD ───────────────────────────────────────────────────────
    public function suppliers() {
        $this->requireInventory();
        $db  = getDB();
        $ctx = $this->getContext();
        $rest_id    = $ctx['rest_id'];
        $cashier_id = $ctx['cashier_id'];

        FeatureGate::enforceSubscription($rest_id);

        // Load cashiers for admin switcher
        $cashiers = [];
        if ($ctx['is_admin']) {
            $cs = $db->prepare('SELECT id, name FROM users WHERE restaurant_id = ? AND role = "cashier" AND status = "active" ORDER BY name ASC');
            $cs->execute([$rest_id]);
            $cashiers = $cs->fetchAll();
        }

        // Load current cashier name
        $cashierName = null;
        if ($cashier_id) {
            $cn = $db->prepare('SELECT name FROM users WHERE id = ? AND restaurant_id = ?');
            $cn->execute([$cashier_id, $rest_id]);
            $cashierName = $cn->fetchColumn();
        }

        // Get suppliers with count of transactions
        if ($cashier_id) {
            $stmt = $db->prepare('
                SELECT s.*, COUNT(l.id) as transaction_count 
                FROM inventory_suppliers s
                LEFT JOIN inventory_logs l ON l.supplier_id = s.id AND l.cashier_id = ?
                WHERE s.restaurant_id = ? AND (s.cashier_id = ? OR s.cashier_id IS NULL) AND s.status = "active"
                GROUP BY s.id
                ORDER BY s.name ASC
            ');
            $stmt->execute([$cashier_id, $rest_id, $cashier_id]);
        } else {
            $stmt = $db->prepare('
                SELECT s.*, COUNT(l.id) as transaction_count 
                FROM inventory_suppliers s
                LEFT JOIN inventory_logs l ON l.supplier_id = s.id
                WHERE s.restaurant_id = ? AND s.status = "active"
                GROUP BY s.id
                ORDER BY s.name ASC
            ');
            $stmt->execute([$rest_id]);
        }
        $suppliers = $stmt->fetchAll();

        $pageTitle = 'Inventory Suppliers';

        ob_start();
        require 'app/Views/inventory/suppliers.php';
        $content = ob_get_clean();

        if (in_array($_SESSION['role'], ['admin', 'cashier'])) {
            require 'app/Views/layouts/admin.php';
        } else {
            require 'app/Views/layouts/inventory.php';
        }
    }

    public function supplierStore() {
        $this->requireInventory();
        $db  = getDB();
        $ctx = $this->getContext();

        $name = trim($_POST['name'] ?? '');
        $contact_person = trim($_POST['contact_person'] ?? '') ?: null;
        $phone = trim($_POST['phone'] ?? '') ?: null;
        $email = trim($_POST['email'] ?? '') ?: null;
        $address = trim($_POST['address'] ?? '') ?: null;
        $notes = trim($_POST['notes'] ?? '') ?: null;

        if (!$name) {
            $this->redirectPath('error', 'Supplier name is required.', 'suppliers', $ctx);
        }

        $stmt = $db->prepare('INSERT INTO inventory_suppliers (restaurant_id, cashier_id, name, contact_person, phone, email, address, notes) VALUES (?, ?, ?, ?, ?, ?, ?, ?)');
        $stmt->execute([$ctx['rest_id'], $ctx['cashier_id'], $name, $contact_person, $phone, $email, $address, $notes]);

        $this->redirectPath('success', 'Supplier created successfully.', 'suppliers', $ctx);
    }

    public function supplierUpdate() {
        $this->requireInventory();
        $db  = getDB();
        $ctx = $this->getContext();

        $id = (int)($_POST['id'] ?? 0);
        $name = trim($_POST['name'] ?? '');
        $contact_person = trim($_POST['contact_person'] ?? '') ?: null;
        $phone = trim($_POST['phone'] ?? '') ?: null;
        $email = trim($_POST['email'] ?? '') ?: null;
        $address = trim($_POST['address'] ?? '') ?: null;
        $notes = trim($_POST['notes'] ?? '') ?: null;

        if (!$id || !$name) {
            $this->redirectPath('error', 'Invalid supplier data.', 'suppliers', $ctx);
        }

        // Verify ownership
        $chk = $db->prepare('SELECT id FROM inventory_suppliers WHERE id = ? AND restaurant_id = ?');
        $chk->execute([$id, $ctx['rest_id']]);
        if (!$chk->fetchColumn()) {
            $this->redirectPath('error', 'Supplier not found.', 'suppliers', $ctx);
        }

        $stmt = $db->prepare('UPDATE inventory_suppliers SET name = ?, contact_person = ?, phone = ?, email = ?, address = ?, notes = ? WHERE id = ?');
        $stmt->execute([$name, $contact_person, $phone, $email, $address, $notes, $id]);

        $this->redirectPath('success', 'Supplier updated successfully.', 'suppliers', $ctx);
    }

    public function supplierDelete() {
        $this->requireInventory();
        $db  = getDB();
        $ctx = $this->getContext();

        $id = (int)($_POST['id'] ?? 0);
        if (!$id) {
            $this->redirectPath('error', 'Invalid supplier.', 'suppliers', $ctx);
        }

        // Verify ownership
        $chk = $db->prepare('SELECT id FROM inventory_suppliers WHERE id = ? AND restaurant_id = ?');
        $chk->execute([$id, $ctx['rest_id']]);
        if (!$chk->fetchColumn()) {
            $this->redirectPath('error', 'Supplier not found.', 'suppliers', $ctx);
        }

        $stmt = $db->prepare('UPDATE inventory_suppliers SET status = "inactive" WHERE id = ?');
        $stmt->execute([$id]);

        $this->redirectPath('success', 'Supplier deactivated successfully.', 'suppliers', $ctx);
    }

    // Helper redirect for different sub-pages
    private function redirectPath($type, $msg, $subpath, $ctx) {
        $qs = $ctx['cashier_id'] ? '?cashier_id=' . $ctx['cashier_id'] : '';
        $base = ($_SESSION['role'] === 'inventory') ? '/restpo/inventory/' : '/restpo/admin/inventory/';
        $connector = ($qs) ? '&' : '?';
        header("Location: $base$subpath$qs$connector$type=" . urlencode($msg));
        exit;
    }

    // ─── REPORTS & ANALYTICS ──────────────────────────────────────────────────
    public function reports() {
        $this->requireInventory();
        $db = getDB();
        $ctx = $this->getContext();
        $rest_id = $ctx['rest_id'];
        $cashier_id = $ctx['cashier_id'];

        FeatureGate::enforceSubscription($rest_id);

        $range_type = $_GET['range_type'] ?? 'monthly';
        $date_from = $_GET['from'] ?? '';
        $date_to = $_GET['to'] ?? '';

        $report = $this->getReportData($rest_id, $cashier_id, $range_type, $date_from, $date_to);

        // Load cashiers for admin switcher
        $cashiers = [];
        if ($ctx['is_admin']) {
            $cs = $db->prepare('SELECT id, name FROM users WHERE restaurant_id = ? AND role = "cashier" AND status = "active" ORDER BY name ASC');
            $cs->execute([$rest_id]);
            $cashiers = $cs->fetchAll();
        }

        // Load current cashier name
        $cashierName = null;
        if ($cashier_id) {
            $cn = $db->prepare('SELECT name FROM users WHERE id = ? AND restaurant_id = ?');
            $cn->execute([$cashier_id, $rest_id]);
            $cashierName = $cn->fetchColumn();
        }

        $pageTitle = 'Inventory Reports';

        ob_start();
        require 'app/Views/inventory/reports.php';
        $content = ob_get_clean();

        if (in_array($_SESSION['role'], ['admin', 'cashier'])) {
            require 'app/Views/layouts/admin.php';
        } else {
            require 'app/Views/layouts/inventory.php';
        }
    }

    public function reportsExport() {
        $this->requireInventory();
        $db = getDB();
        $ctx = $this->getContext();
        $rest_id = $ctx['rest_id'];
        $cashier_id = $ctx['cashier_id'];

        FeatureGate::enforceSubscription($rest_id);

        $range_type = $_GET['range_type'] ?? 'monthly';
        $date_from = $_GET['from'] ?? '';
        $date_to = $_GET['to'] ?? '';

        $report = $this->getReportData($rest_id, $cashier_id, $range_type, $date_from, $date_to);

        // Get restaurant details for print header
        $restStmt = $db->prepare('SELECT * FROM restaurants WHERE id = ?');
        $restStmt->execute([$rest_id]);
        $restaurant = $restStmt->fetch();

        $cashierName = null;
        if ($cashier_id) {
            $cn = $db->prepare('SELECT name FROM users WHERE id = ? AND restaurant_id = ?');
            $cn->execute([$cashier_id, $rest_id]);
            $cashierName = $cn->fetchColumn();
        }

        require 'app/Views/inventory/reports_print.php';
        exit;
    }

    private function getReportData($rest_id, $cashier_id, $range_type, $date_from, $date_to) {
        $db = getDB();
        $today = date('Y-m-d');
        
        $from = $date_from ?: date('Y-m-01');
        $to = $date_to ?: $today;

        if ($range_type === 'daily') {
            $from = $today;
            $to = $today;
        } elseif ($range_type === 'weekly') {
            $from = date('Y-m-d', strtotime('-6 days'));
            $to = $today;
        } elseif ($range_type === 'monthly') {
            $from = date('Y-m-01');
            $to = $today;
        }

        // Summary KPIs
        $kpiStmt = $db->prepare('
            SELECT 
                l.type, 
                COUNT(l.id) as log_count, 
                SUM(ABS(l.quantity_change)) as total_qty, 
                SUM(ABS(l.quantity_change) * COALESCE(l.unit_price, ii.cost_price, 0)) as total_value
            FROM inventory_logs l
            JOIN inventory_items ii ON ii.id = l.inventory_item_id
            WHERE l.restaurant_id = ? 
              AND (l.cashier_id = ? OR ? IS NULL)
              AND DATE(l.created_at) BETWEEN ? AND ?
            GROUP BY l.type
        ');
        $kpiStmt->execute([$rest_id, $cashier_id, $cashier_id, $from, $to]);
        $kpis = ['stock_in' => 0, 'stock_out' => 0, 'waste' => 0];
        $kpiCounts = ['stock_in' => 0, 'stock_out' => 0, 'waste' => 0];
        foreach ($kpiStmt->fetchAll() as $row) {
            if (isset($kpis[$row['type']])) {
                $kpis[$row['type']] = (float)$row['total_value'];
                $kpiCounts[$row['type']] = (int)$row['log_count'];
            }
        }

        // Category breakdown
        $catStmt = $db->prepare('
            SELECT 
                COALESCE(ic.name, "Uncategorized") as category_name, 
                l.type, 
                SUM(ABS(l.quantity_change)) as total_qty, 
                SUM(ABS(l.quantity_change) * COALESCE(l.unit_price, ii.cost_price, 0)) as total_value
            FROM inventory_logs l
            JOIN inventory_items ii ON ii.id = l.inventory_item_id
            LEFT JOIN inventory_categories ic ON ic.id = ii.category_id
            WHERE l.restaurant_id = ? 
              AND (l.cashier_id = ? OR ? IS NULL)
              AND DATE(l.created_at) BETWEEN ? AND ?
            GROUP BY ii.category_id, ic.name, l.type
            ORDER BY ic.name ASC
        ');
        $catStmt->execute([$rest_id, $cashier_id, $cashier_id, $from, $to]);
        $categoryBreakdown = [];
        foreach ($catStmt->fetchAll() as $row) {
            $cat = $row['category_name'];
            if (!isset($categoryBreakdown[$cat])) {
                $categoryBreakdown[$cat] = ['stock_in' => 0, 'stock_out' => 0, 'waste' => 0];
            }
            if (isset($categoryBreakdown[$cat][$row['type']])) {
                $categoryBreakdown[$cat][$row['type']] = (float)$row['total_value'];
            }
        }

        // Supplier purchases
        $supStmt = $db->prepare('
            SELECT 
                s.name as supplier_name, 
                COUNT(l.id) as purchases_count, 
                SUM(l.quantity_change) as total_qty, 
                SUM(l.quantity_change * COALESCE(l.unit_price, ii.cost_price, 0)) as total_value
            FROM inventory_logs l
            JOIN inventory_items ii ON ii.id = l.inventory_item_id
            JOIN inventory_suppliers s ON s.id = l.supplier_id
            WHERE l.restaurant_id = ? 
              AND (l.cashier_id = ? OR ? IS NULL)
              AND l.type = "stock_in"
              AND DATE(l.created_at) BETWEEN ? AND ?
            GROUP BY s.id, s.name
            ORDER BY total_value DESC
        ');
        $supStmt->execute([$rest_id, $cashier_id, $cashier_id, $from, $to]);
        $supplierPurchases = $supStmt->fetchAll();

        // Logs list
        $logsStmt = $db->prepare('
            SELECT 
                l.*, 
                u.name as user_name, 
                ii.name as item_name, 
                ii.unit as item_unit, 
                s.name as supplier_name
            FROM inventory_logs l
            JOIN inventory_items ii ON ii.id = l.inventory_item_id
            LEFT JOIN users u ON u.id = l.user_id
            LEFT JOIN inventory_suppliers s ON s.id = l.supplier_id
            WHERE l.restaurant_id = ? 
              AND (l.cashier_id = ? OR ? IS NULL)
              AND DATE(l.created_at) BETWEEN ? AND ?
            ORDER BY l.created_at DESC
            LIMIT 500
        ');
        $logsStmt->execute([$rest_id, $cashier_id, $cashier_id, $from, $to]);
        $logs = $logsStmt->fetchAll();

        return [
            'from' => $from,
            'to' => $to,
            'kpis' => $kpis,
            'kpiCounts' => $kpiCounts,
            'categoryBreakdown' => $categoryBreakdown,
            'supplierPurchases' => $supplierPurchases,
            'logs' => $logs
        ];
    }

    // ─── HELPERS ─────────────────────────────────────────────────────────────
    private function getItem($item_id, $ctx) {
        $db = getDB();
        if ($ctx['cashier_id']) {
            $stmt = $db->prepare('SELECT * FROM inventory_items WHERE id = ? AND restaurant_id = ? AND cashier_id = ? AND status = "active"');
            $stmt->execute([$item_id, $ctx['rest_id'], $ctx['cashier_id']]);
        } else {
            $stmt = $db->prepare('SELECT * FROM inventory_items WHERE id = ? AND restaurant_id = ? AND status = "active"');
            $stmt->execute([$item_id, $ctx['rest_id']]);
        }
        return $stmt->fetch();
    }

    private function redirect($type, $msg, $ctx) {
        $qs = $ctx['cashier_id'] ? '?cashier_id=' . $ctx['cashier_id'] : '';
        $base = ($_SESSION['role'] === 'inventory') ? '/restpo/inventory' : '/restpo/admin/inventory';
        header("Location: $base$qs&$type=" . urlencode($msg));
        exit;
    }
}
