<?php

namespace App\Controllers;

use App\Utils\Auth;
use App\Utils\Flash;
use App\Config\Database;

class InventoryController {

    public function index() {
        if (!Auth::hasPrivilege('inventory')) {
            Flash::set('error', 'You do not have permission to access Inventory.');
            header("Location: /dashboard");
            exit;
        }
        
        $user = Auth::user();
        $db = Database::getInstance()->getConnection();

        $branches = [];
        $selectedBranch = $_GET['branch_id'] ?? 'all';
        $selectedStockLevel = $_GET['stock_level'] ?? 'all';
        
        if ($user['role'] === 'admin') {
            $stmt = $db->prepare("SELECT id, name FROM branches WHERE tenant_id = ? ORDER BY name ASC");
            $stmt->execute([$user['tenant_id']]);
            $branches = $stmt->fetchAll();
        }

        $branchQuery = "";
        $params = [$user['tenant_id']];
        if ($user['role'] !== 'admin' && !empty($user['branch_id'])) {
            $branchQuery = " AND ib.branch_id = ?";
            $params[] = $user['branch_id'];
        } elseif ($user['role'] === 'admin' && $selectedBranch !== 'all') {
            $branchQuery = " AND ib.branch_id = ?";
            $params[] = $selectedBranch;
        }

        $stockLevelQuery = "";
        if ($selectedStockLevel !== 'all') {
            $stockLevelQuery = " AND ib.stock_level = ?";
            $params[] = $selectedStockLevel;
        }

        $searchQuery = $_GET['search'] ?? '';
        $searchSqlClause = "";
        if ($searchQuery !== '') {
            $searchSqlClause = " AND (m.name LIKE ? OR m.generic_name LIKE ? OR m.item_code LIKE ?)";
            $params[] = '%' . $searchQuery . '%';
            $params[] = '%' . $searchQuery . '%';
            $params[] = '%' . $searchQuery . '%';
        }

        $stmt = $db->prepare("
            SELECT ib.*, m.name as medicine_name, m.item_code, m.generic_name, m.status as medicine_status, m.reorder_level, m.expiry_alert_days, b.name as branch_name,
                   (SELECT SUM(quantity) FROM returned_items ri WHERE ri.batch_number = ib.batch_number AND ri.medicine_id = ib.medicine_id AND ri.branch_id = ib.branch_id AND ri.tenant_id = ib.tenant_id) as returned_quantity
            FROM inventory_batches ib
            JOIN medicines m ON ib.medicine_id = m.id
            LEFT JOIN branches b ON ib.branch_id = b.id
            WHERE ib.tenant_id = ? $branchQuery $stockLevelQuery $searchSqlClause
            ORDER BY ib.created_at DESC, ib.id DESC
        ");
        $stmt->execute($params);
        $batches = $stmt->fetchAll();

        // Group by medicine
        $groupedInventory = [];
        foreach($batches as $b) {
            $medId = $b['medicine_id'];
            if (!isset($groupedInventory[$medId])) {
                $groupedInventory[$medId] = [
                    'medicine_name' => $b['medicine_name'],
                    'item_code' => $b['item_code'],
                    'generic_name' => $b['generic_name'],
                    'reorder_level' => $b['reorder_level'],
                    'expiry_alert_days' => $b['expiry_alert_days'],
                    'total_quantity' => 0,
                    'active_quantity' => 0,
                    'batches' => []
                ];
            }
            $groupedInventory[$medId]['total_quantity'] += $b['quantity'];
            if (($b['status'] ?? 'active') === 'active') {
                $groupedInventory[$medId]['active_quantity'] += $b['quantity'];
            }
            $groupedInventory[$medId]['batches'][] = $b;
        }

        // Pagination
        $totalItems = count($groupedInventory);
        $perPage = 20;
        $totalPages = max(1, ceil($totalItems / $perPage));
        $page = isset($_GET['page']) ? max(1, (int)$_GET['page']) : 1;
        if ($page > $totalPages) $page = $totalPages;
        $offset = ($page - 1) * $perPage;
        
        $groupedInventory = array_slice($groupedInventory, $offset, $perPage, true);

        require_once __DIR__ . '/../../views/admin/inventory/index.php';
    }

    public function create() {
        if (!Auth::hasPrivilege('inventory')) {
            Flash::set('error', 'Permission denied.');
            header("Location: /dashboard");
            exit;
        }

        $user = Auth::user();
        $db = Database::getInstance()->getConnection();



        $stmt = $db->prepare("SELECT * FROM branches WHERE tenant_id = ?");
        $stmt->execute([$user['tenant_id']]);
        $branches = $stmt->fetchAll();

        $stmt = $db->prepare("SELECT * FROM medicines WHERE tenant_id = ? ORDER BY name ASC");
        $stmt->execute([$user['tenant_id']]);
        $medicines = $stmt->fetchAll();

        $stmt = $db->query("SELECT * FROM units_of_measure ORDER BY name ASC");
        $uoms = $stmt->fetchAll();

        // Generate a unique batch number like B-YYMMDD-XXXX
        $autoBatchNumber = 'B-' . date('ymd') . '-' . strtoupper(substr(uniqid(), -4));

        require_once __DIR__ . '/../../views/admin/inventory/create.php';
    }

    public function store() {
        if (!Auth::hasPrivilege('inventory')) {
            header("Location: /dashboard");
            exit;
        }

        $user = Auth::user();
        
        if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
            header("Location: /inventory");
            exit;
        }

        $db = Database::getInstance()->getConnection();
        
        $mode = $_POST['medicine_mode'] ?? 'new'; // 'new' or 'existing'
        $medicineId = $_POST['medicine_id'] ?? null;
        
        $branchId = $_POST['branch_id'] ?? null;
        if (empty($branchId) && $user['role'] !== 'admin') {
            $branchId = $user['branch_id'];
        }

        $batchNumber = $_POST['batch_number'] ?? '';
        $supplier = $_POST['supplier'] ?? '';
        $notes = $_POST['notes'] ?? null;
        $unitPrice = (isset($_POST['unit_price']) && $_POST['unit_price'] !== '') ? $_POST['unit_price'] : null;
        $sellingPrice = (isset($_POST['selling_price']) && $_POST['selling_price'] !== '') ? $_POST['selling_price'] : null;
        $expiryDate = $_POST['expiry_date'] ?? '';
        $quantity = $_POST['quantity'] ?? 0;

        try {
            $db->beginTransaction();

            if ($mode === 'new') {
                $itemCode = $_POST['item_code'] ?? null;
                $name = $_POST['name'] ?? '';
                $genericName = $_POST['generic_name'] ?? '';
                $uomId = $_POST['uom_id'] ?? null;
                $volume = $_POST['volume'] ?? null;
                $reorderLevel = $_POST['reorder_level'] ?? 10;
                $expiryAlertDays = $_POST['expiry_alert_days'] ?? 30;

                if (empty($name) || empty($uomId)) {
                    throw new \Exception("Medicine Name and Unit of Measure are required for new medicine.");
                }

                $stmt = $db->prepare("INSERT INTO medicines (tenant_id, uom_id, volume, name, item_code, generic_name, reorder_level, expiry_alert_days) VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
                $stmt->execute([$user['tenant_id'], $uomId, $volume, $name, $itemCode, $genericName, $reorderLevel, $expiryAlertDays]);
                $medicineId = $db->lastInsertId();
            } else {
                if (empty($medicineId)) {
                    throw new \Exception("Please select an existing medicine.");
                }
                // Fetch reorder level for existing medicine
                $stmt = $db->prepare("SELECT reorder_level FROM medicines WHERE id = ?");
                $stmt->execute([$medicineId]);
                $reorderLevel = $stmt->fetchColumn();
                if ($reorderLevel === false) {
                    $reorderLevel = 10;
                }
            }

            if (empty($batchNumber) || empty($expiryDate) || empty($branchId)) {
                throw new \Exception("Batch Number, Expiry Date, and Branch are required.");
            }

            $batchStatus = $_POST['batch_status'] ?? 'active';
            $stockLevel = $this->determineStockLevel($quantity, $reorderLevel);

            $stmt = $db->prepare("INSERT INTO inventory_batches (tenant_id, branch_id, medicine_id, batch_number, supplier, notes, unit_price, selling_price, expiry_date, quantity, status, stock_level) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
            $stmt->execute([$user['tenant_id'], $branchId, $medicineId, $batchNumber, $supplier, $notes, $unitPrice, $sellingPrice, $expiryDate, $quantity, $batchStatus, $stockLevel]);

            $db->commit();
            Flash::set('success', 'Stock added successfully.');
            header("Location: /inventory");
            exit;

        } catch (\Exception $e) {
            $db->rollBack();
            Flash::set('error', $e->getMessage());
            header("Location: /inventory/create");
            exit;
        }
    }

    public function edit($id) {
        if (!Auth::hasPrivilege('inventory')) {
            header("Location: /dashboard");
            exit;
        }

        $user = Auth::user();
        $db = Database::getInstance()->getConnection();

        $stmt = $db->prepare("
            SELECT ib.*, m.name as medicine_name, m.item_code, m.generic_name, m.status as medicine_status, m.reorder_level, m.expiry_alert_days 
            FROM inventory_batches ib
            JOIN medicines m ON ib.medicine_id = m.id
            WHERE ib.id = ? AND ib.tenant_id = ?
        ");
        $stmt->execute([$id, $user['tenant_id']]);
        $batch = $stmt->fetch();

        if (!$batch) {
            Flash::set('error', 'Batch not found.');
            header("Location: /inventory");
            exit;
        }

        // Restrict by branch if not admin
        if ($user['role'] !== 'admin' && $batch['branch_id'] != $user['branch_id']) {
            Flash::set('error', 'Permission denied for this branch stock.');
            header("Location: /inventory");
            exit;
        }



        require_once __DIR__ . '/../../views/admin/inventory/edit.php';
    }

    public function update($id) {
        if (!Auth::hasPrivilege('inventory')) {
            header("Location: /dashboard");
            exit;
        }

        $user = Auth::user();
        if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
            header("Location: /inventory");
            exit;
        }

        $db = Database::getInstance()->getConnection();
        
        $supplier = $_POST['supplier'] ?? '';
        $notes = $_POST['notes'] ?? null;
        $unitPrice = (isset($_POST['unit_price']) && $_POST['unit_price'] !== '') ? $_POST['unit_price'] : null;
        $sellingPrice = (isset($_POST['selling_price']) && $_POST['selling_price'] !== '') ? $_POST['selling_price'] : null;
        $expiryDate = $_POST['expiry_date'] ?? '';
        $quantity = $_POST['quantity'] ?? 0;
        $batchStatus = $_POST['batch_status'] ?? 'active';
        
        // Master updates
        $status = $_POST['status'] ?? 'active';
        $itemCode = $_POST['item_code'] ?? null;
        $reorderLevel = $_POST['reorder_level'] ?? 10;
        $expiryAlertDays = $_POST['expiry_alert_days'] ?? null;

        try {
            if (empty($expiryAlertDays) || intval($expiryAlertDays) < 1) {
                throw new \Exception("Notify Days Before Expiry is required and must be at least 1.");
            }
            $db->beginTransaction();
            
            // Verify ownership
            $stmt = $db->prepare("SELECT medicine_id, branch_id FROM inventory_batches WHERE id = ? AND tenant_id = ?");
            $stmt->execute([$id, $user['tenant_id']]);
            $batch = $stmt->fetch();
            
            if (!$batch || ($user['role'] !== 'admin' && $batch['branch_id'] != $user['branch_id'])) {
                throw new \Exception("Permission denied or batch not found.");
            }

            // Update Batch
            $stockLevel = $this->determineStockLevel($quantity, $reorderLevel);
            $stmt = $db->prepare("UPDATE inventory_batches SET supplier=?, notes=?, unit_price=?, selling_price=?, expiry_date=?, quantity=?, status=?, stock_level=? WHERE id=?");
            $stmt->execute([$supplier, $notes, $unitPrice, $sellingPrice, $expiryDate, $quantity, $batchStatus, $stockLevel, $id]);

            // Update Master Medicine
            $stmt = $db->prepare("UPDATE medicines SET status=?, item_code=?, reorder_level=?, expiry_alert_days=? WHERE id=?");
            $stmt->execute([$status, $itemCode, $reorderLevel, $expiryAlertDays, $batch['medicine_id']]);

            // Sync stock_level for all batches under this medicine in case reorder_level changed
            $stmt = $db->prepare("
                UPDATE inventory_batches 
                SET stock_level = CASE 
                    WHEN quantity = 0 THEN 'out-of stock'
                    WHEN quantity <= ? THEN 'low-stock'
                    ELSE 'In-stock'
                END
                WHERE medicine_id = ?
            ");
            $stmt->execute([$reorderLevel, $batch['medicine_id']]);

            $db->commit();
            Flash::set('success', 'Stock updated successfully.');
            header("Location: /inventory");
            exit;

        } catch (\Exception $e) {
            $db->rollBack();
            Flash::set('error', $e->getMessage());
            header("Location: /inventory/edit/$id");
            exit;
        }
    }

    public function withdraw($id) {
        if (!Auth::hasPrivilege('inventory')) {
            header("Location: /dashboard");
            exit;
        }

        $user = Auth::user();
        if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
            header("Location: /inventory");
            exit;
        }

        $db = Database::getInstance()->getConnection();
        $withdrawQty = intval($_POST['withdraw_quantity'] ?? 0);

        if ($withdrawQty <= 0) {
            Flash::set('error', 'Withdraw quantity must be greater than zero.');
            header("Location: /inventory");
            exit;
        }

        try {
            $stmt = $db->prepare("
                SELECT ib.quantity, ib.branch_id, ib.medicine_id, ib.withdrawal_comment, m.reorder_level 
                FROM inventory_batches ib
                JOIN medicines m ON ib.medicine_id = m.id
                WHERE ib.id = ? AND ib.tenant_id = ?
            ");
            $stmt->execute([$id, $user['tenant_id']]);
            $batch = $stmt->fetch();

            if (!$batch || ($user['role'] !== 'admin' && $batch['branch_id'] != $user['branch_id'])) {
                throw new \Exception("Permission denied or batch not found.");
            }

            if ($batch['quantity'] < $withdrawQty) {
                throw new \Exception("Insufficient stock to withdraw. Current stock: " . $batch['quantity']);
            }

            $commentText = $_POST['withdrawal_comment'] ?? '';
            $newComment = trim($commentText);
            if (!empty($newComment)) {
                $timestamp = date('Y-m-d H:i:s');
                $formattedComment = "[{$timestamp} - {$user['username']}]: {$newComment}";
                if (!empty($batch['withdrawal_comment'])) {
                    $newComment = $batch['withdrawal_comment'] . "\n" . $formattedComment;
                } else {
                    $newComment = $formattedComment;
                }
            } else {
                $newComment = $batch['withdrawal_comment'];
            }

            $newQuantity = $batch['quantity'] - $withdrawQty;
            $newStockLevel = $this->determineStockLevel($newQuantity, $batch['reorder_level']);

            $stmt = $db->prepare("UPDATE inventory_batches SET quantity = ?, stock_level = ?, withdrawal_comment = ? WHERE id = ?");
            $stmt->execute([$newQuantity, $newStockLevel, $newComment, $id]);

            Flash::set('success', "Successfully withdrew {$withdrawQty} items.");
            header("Location: /inventory");
            exit;
        } catch (\Exception $e) {
            Flash::set('error', $e->getMessage());
            header("Location: /inventory");
            exit;
        }
    }

    public function returnBatch($id) {
        if (!Auth::hasPrivilege('inventory')) {
            header("Location: /dashboard");
            exit;
        }

        $user = Auth::user();
        if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
            header("Location: /dashboard");
            exit;
        }

        $comment = trim($_POST['return_comment'] ?? '');
        if (empty($comment)) {
            Flash::set('error', 'Return comment is required.');
            header("Location: /dashboard");
            exit;
        }

        $db = Database::getInstance()->getConnection();

        try {
            $db->beginTransaction();

            // Verify ownership and get details
            $stmt = $db->prepare("SELECT id, tenant_id, branch_id, medicine_id, batch_number, quantity FROM inventory_batches WHERE id = ? AND tenant_id = ?");
            $stmt->execute([$id, $user['tenant_id']]);
            $batch = $stmt->fetch();

            if (!$batch) {
                throw new \Exception("Batch not found or access denied.");
            }

            // Insert into returned_items table for reporting
            $insertStmt = $db->prepare("INSERT INTO returned_items (tenant_id, branch_id, medicine_id, batch_number, quantity, reason, returned_by) VALUES (?, ?, ?, ?, ?, ?, ?)");
            $insertStmt->execute([
                $batch['tenant_id'],
                $batch['branch_id'],
                $batch['medicine_id'],
                $batch['batch_number'],
                $batch['quantity'],
                $comment,
                $user['id']
            ]);

            // Set quantity to 0, status to returned, stock_level to returned
            $updateStmt = $db->prepare("UPDATE inventory_batches SET quantity = 0, status = 'returned', stock_level = 'returned', withdrawal_comment = ? WHERE id = ?");
            $updateStmt->execute([$comment, $id]);

            $db->commit();

            Flash::set('success', "Batch marked as returned successfully.");
            header("Location: /dashboard");
            exit;
        } catch (\Exception $e) {
            Flash::set('error', $e->getMessage());
            header("Location: /dashboard");
            exit;
        }
    }

    public function delete($id) {
        if (!Auth::hasPrivilege('inventory')) {
            header("Location: /dashboard");
            exit;
        }

        $user = Auth::user();
        if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
            header("Location: /inventory");
            exit;
        }

        $db = Database::getInstance()->getConnection();

        try {
            $stmt = $db->prepare("SELECT branch_id, medicine_id FROM inventory_batches WHERE id = ? AND tenant_id = ?");
            $stmt->execute([$id, $user['tenant_id']]);
            $batch = $stmt->fetch();

            if (!$batch || ($user['role'] !== 'admin' && $batch['branch_id'] != $user['branch_id'])) {
                throw new \Exception("Permission denied or batch not found.");
            }

            $stmt = $db->prepare("DELETE FROM inventory_batches WHERE id = ?");
            $stmt->execute([$id]);

            // Check if there are any remaining batches for this medicine
            $stmt = $db->prepare("SELECT COUNT(*) FROM inventory_batches WHERE medicine_id = ? AND tenant_id = ?");
            $stmt->execute([$batch['medicine_id'], $user['tenant_id']]);
            $remaining = $stmt->fetchColumn();

            if ($remaining == 0) {
                // Delete the medicine record as well since it has no remaining batches
                $stmt = $db->prepare("DELETE FROM medicines WHERE id = ? AND tenant_id = ?");
                $stmt->execute([$batch['medicine_id'], $user['tenant_id']]);
            }

            Flash::set('success', 'Batch successfully deleted.');
            header("Location: /inventory");
            exit;
        } catch (\Exception $e) {
            Flash::set('error', 'Failed to delete batch: ' . $e->getMessage());
            header("Location: /inventory");
            exit;
        }
    }

    public function bulk() {
        if (!Auth::hasPrivilege('inventory')) {
            header("Location: /dashboard");
            exit;
        }
        
        $user = Auth::user();
        $db = Database::getInstance()->getConnection();
        $stmt = $db->prepare("SELECT * FROM branches WHERE tenant_id = ?");
        $stmt->execute([$user['tenant_id']]);
        $branches = $stmt->fetchAll();

        $stmt = $db->query("SELECT * FROM units_of_measure ORDER BY name ASC");
        $uoms = $stmt->fetchAll();

        require_once __DIR__ . '/../../views/admin/inventory/bulk.php';
    }

    public function template() {
        if (!Auth::hasPrivilege('inventory')) {
            header("Location: /dashboard");
            exit;
        }

        header('Content-Type: text/csv; charset=utf-8');
        header('Content-Disposition: attachment; filename=inventory_import_template.csv');
        $output = fopen('php://output', 'w');
        fputcsv($output, [
            'Item Code*', 'Medicine Name*', 'Generic Name', 'Unit of Measure*', 
            'Volume', 'Reorder Level', 'Expiry Alert Days*', 
            'Initial Quantity*', 'Expiry Date*', 
            'Unit Cost', 'Selling Price', 'Supplier', 'Notes'
        ]);
        fclose($output);
        exit;
    }

    public function bulkPreview() {
        if (!Auth::hasPrivilege('inventory')) {
            header("Location: /dashboard");
            exit;
        }

        $user = Auth::user();

        // If POST request with file uploaded, process and save to session, then redirect to GET
        if ($_SERVER['REQUEST_METHOD'] === 'POST' && !empty($_FILES['csv_file']['tmp_name'])) {
            $branchId = $_POST['branch_id'] ?? null;
            if (empty($branchId) && $user['role'] !== 'admin') {
                $branchId = $user['branch_id'];
            }

            if (empty($branchId)) {
                Flash::set('error', 'Please select a branch.');
                header("Location: /inventory/bulk");
                exit;
            }

            // Save file temporarily
            $tempDir = sys_get_temp_dir();
            $tempFilePath = $tempDir . '/nexcore_import_' . uniqid() . '.csv';
            if (!move_uploaded_file($_FILES['csv_file']['tmp_name'], $tempFilePath)) {
                Flash::set('error', 'Failed to securely upload file.');
                header("Location: /inventory/bulk");
                exit;
            }

            $_SESSION['bulk_temp_file'] = $tempFilePath;
            $_SESSION['bulk_branch_id'] = $branchId;
            
            // Clear old edits for new upload
            unset($_SESSION['bulk_edits'][$tempFilePath]);

            header("Location: /inventory/bulkPreview");
            exit;
        }

        $tempFilePath = $_SESSION['bulk_temp_file'] ?? '';
        $branchId = $_SESSION['bulk_branch_id'] ?? null;

        if (empty($tempFilePath) || !file_exists($tempFilePath)) {
            Flash::set('error', 'Please upload a valid CSV file.');
            header("Location: /inventory/bulk");
            exit;
        }

        // Parse file to generate preview using helper
        $sessionEdits = $_SESSION['bulk_edits'][$tempFilePath] ?? [];
        $validation = $this->validateCsv($tempFilePath, $sessionEdits);
        
        if (!$validation) {
            Flash::set('error', 'Failed to parse CSV file.');
            header("Location: /inventory/bulk");
            exit;
        }

        $totalRows = $validation['totalRows'];
        $validRowsCount = $validation['validRowsCount'];
        $allErrors = $validation['allErrors'];
        $allErrorsCount = count($allErrors);

        // Paginate errors
        $page = isset($_GET['page']) ? max(1, (int)$_GET['page']) : 1;
        $perPage = 20;
        $offset = ($page - 1) * $perPage;

        $errors = [];
        $chunk = array_slice($allErrors, $offset, $perPage, true);
        foreach ($chunk as $rowNum => $err) {
            $errors[] = [
                'row' => $rowNum,
                'message' => $err['message'],
                'data' => $err['data']
            ];
        }

        $totalPages = ceil($allErrorsCount / $perPage);

        $db = Database::getInstance()->getConnection();
        $stmt = $db->query("SELECT * FROM units_of_measure ORDER BY name ASC");
        $uoms = $stmt->fetchAll();

        require_once __DIR__ . '/../../views/admin/inventory/bulk_preview.php';
    }

    public function bulkPatchRow() {
        if (!Auth::hasPrivilege('inventory')) {
            http_response_code(403);
            echo json_encode(['error' => 'Permission denied']);
            exit;
        }

        if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
            http_response_code(405);
            echo json_encode(['error' => 'Method not allowed']);
            exit;
        }

        $input = json_decode(file_get_contents('php://input'), true);
        $tempFile = $input['temp_file'] ?? '';
        
        if (empty($tempFile)) {
            http_response_code(400);
            echo json_encode(['error' => 'Invalid temp file path']);
            exit;
        }

        if (!isset($_SESSION['bulk_edits'])) {
            $_SESSION['bulk_edits'] = [];
        }
        if (!isset($_SESSION['bulk_edits'][$tempFile])) {
            $_SESSION['bulk_edits'][$tempFile] = [];
        }

        $rowsToSave = [];
        if (isset($input['rows']) && is_array($input['rows'])) {
            $rowsToSave = $input['rows'];
        } elseif (isset($input['row'])) {
            $rowsToSave[] = [
                'row' => $input['row'],
                'itemCode' => $input['itemCode'] ?? '',
                'medName' => $input['medName'] ?? '',
                'uomName' => $input['uomName'] ?? '',
                'expiryAlertDays' => $input['expiryAlertDays'] ?? '',
                'quantity' => $input['quantity'] ?? '',
                'expiryDate' => $input['expiryDate'] ?? ''
            ];
        } else {
            http_response_code(400);
            echo json_encode(['error' => 'No row data to save']);
            exit;
        }

        foreach ($rowsToSave as $r) {
            $rowNum = $r['row'];
            $_SESSION['bulk_edits'][$tempFile][$rowNum] = [
                'itemCode' => $r['itemCode'] ?? '',
                'medName' => $r['medName'] ?? '',
                'uomName' => $r['uomName'] ?? '',
                'expiryAlertDays' => $r['expiryAlertDays'] ?? '',
                'quantity' => $r['quantity'] ?? '',
                'expiryDate' => $r['expiryDate'] ?? '',
                'notes' => $r['notes'] ?? ''
            ];
        }

        // Re-run validation on the entire file to get the new counts and row validation status
        $sessionEdits = $_SESSION['bulk_edits'][$tempFile];
        $validation = $this->validateCsv($tempFile, $sessionEdits);
        
        if (!$validation) {
            http_response_code(500);
            echo json_encode(['error' => 'Failed to parse CSV for validation']);
            exit;
        }

        // Determine validation result for each of the saved rows
        $rowResults = [];
        foreach ($rowsToSave as $r) {
            $rowNum = $r['row'];
            $isValid = !isset($validation['allErrors'][$rowNum]);
            $rowResults[$rowNum] = [
                'isValid' => $isValid,
                'errorMessage' => $isValid ? '' : $validation['allErrors'][$rowNum]['message']
            ];
        }

        echo json_encode([
            'success' => true,
            'totalRows' => $validation['totalRows'],
            'validRowsCount' => $validation['validRowsCount'],
            'invalidRowsCount' => count($validation['allErrors']),
            'rowResults' => $rowResults
        ]);
        exit;
    }

    public function bulkCommitChunk() {
        if (!Auth::hasPrivilege('inventory')) {
            http_response_code(403);
            echo json_encode(['error' => 'Permission denied']);
            exit;
        }

        if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
            http_response_code(405);
            echo json_encode(['error' => 'Method not allowed']);
            exit;
        }

        $input = json_decode(file_get_contents('php://input'), true);
        $tempFilePath = $input['temp_file_path'] ?? '';
        $branchId = $input['branch_id'] ?? null;
        $startRow = intval($input['start_row'] ?? 1);
        $chunkSize = intval($input['chunk_size'] ?? 500);

        if (empty($tempFilePath) || !file_exists($tempFilePath)) {
            http_response_code(400);
            echo json_encode(['error' => 'Invalid temp file path']);
            exit;
        }

        $user = Auth::user();
        $db = Database::getInstance()->getConnection();
        $tenantId = $user['tenant_id'];

        if (empty($branchId) && $user['role'] !== 'admin') {
            $branchId = $user['branch_id'];
        }

        if ($user['role'] !== 'admin' && $branchId != $user['branch_id']) {
            http_response_code(403);
            echo json_encode(['error' => 'Permission denied for this branch.']);
            exit;
        }

        if ($startRow === 1) {
            $_SESSION['bulk_report'] = [
                'total' => 0,
                'success' => 0,
                'errors' => 0,
                'error_details' => []
            ];
        }

        $uoms = [];
        $stmt = $db->query("SELECT id, LOWER(name) as name FROM units_of_measure");
        foreach ($stmt->fetchAll() as $r) $uoms[$r['name']] = $r['id'];

        $medicines = [];
        $stmt = $db->prepare("SELECT id, LOWER(name) as name FROM medicines WHERE tenant_id = ?");
        $stmt->execute([$tenantId]);
        foreach ($stmt->fetchAll() as $r) $medicines[$r['name']] = $r['id'];

        $insertMedicineStmt = $db->prepare("INSERT INTO medicines (tenant_id, uom_id, volume, name, item_code, generic_name, reorder_level, expiry_alert_days) VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
        $insertBatchStmt = $db->prepare("INSERT INTO inventory_batches (tenant_id, branch_id, medicine_id, batch_number, supplier, notes, unit_price, selling_price, expiry_date, quantity, status, stock_level) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'active', ?)");

        $sessionEdits = $_SESSION['bulk_edits'][$tempFilePath] ?? [];

        $handle = fopen($tempFilePath, 'r');
        $header = fgetcsv($handle);

        $currentRow = 0;
        $processedInChunk = 0;
        $successCount = 0;
        $errorCount = 0;
        $errorsList = [];
        $done = false;

        while (($row = fgetcsv($handle)) !== false) {
            $currentRow++;
            
            if ($currentRow < $startRow) {
                continue;
            }

            if (isset($sessionEdits[$currentRow])) {
                $edits = $sessionEdits[$currentRow];
                if (isset($edits['itemCode'])) $row[0] = $edits['itemCode'];
                if (isset($edits['medName'])) $row[1] = $edits['medName'];
                if (isset($edits['uomName'])) $row[3] = $edits['uomName'];
                if (isset($edits['expiryAlertDays'])) $row[6] = $edits['expiryAlertDays'];
                if (isset($edits['quantity'])) $row[7] = $edits['quantity'];
                if (isset($edits['expiryDate'])) $row[8] = $edits['expiryDate'];
                if (isset($edits['notes'])) $row[12] = $edits['notes'];
            }

            if (count($row) < 12) {
                $errorCount++;
                $errorsList[] = ['row' => $currentRow, 'message' => 'Missing columns. Expected 12.'];
                $processedInChunk++;
                if ($processedInChunk >= $chunkSize) break;
                continue;
            }

            $itemCode = trim($row[0]);
            $medName = trim($row[1]);
            $genericName = trim($row[2]);
            $uomName = strtolower(trim($row[3]));
            $volume = trim($row[4]) !== '' ? floatval(trim($row[4])) : null;
            $reorderLevel = trim($row[5]) !== '' ? intval(trim($row[5])) : 10;
            
            $rawExpiryAlertDays = trim($row[6]);
            $expiryAlertDays = $rawExpiryAlertDays !== '' ? intval($rawExpiryAlertDays) : null;
            
            $rawQty = trim($row[7]);
            $quantity = $rawQty !== '' ? intval($rawQty) : null;
            
            $rawExpiry = trim($row[8]);
            $expiryDate = null;
            if (!empty($rawExpiry)) {
                try {
                    $d = new \DateTime($rawExpiry);
                    $expiryDate = $d->format('Y-m-d');
                } catch (\Exception $e) {
                    $expiryDate = null;
                }
            }

            $unitCost = trim($row[9]) !== '' ? floatval(trim($row[9])) : null;
            $sellingPrice = trim($row[10]) !== '' ? floatval(trim($row[10])) : null;
            $supplier = trim($row[11]);
            $notes = isset($row[12]) ? trim($row[12]) : null;
            if ($notes === '') $notes = null;
            $batchNumber = 'B-' . date('ymd') . '-' . strtoupper(substr(uniqid(), -4));

            if (empty($itemCode) || empty($medName) || empty($uomName) || $expiryAlertDays === null || $expiryAlertDays < 1 || $quantity === null || $quantity < 1 || empty($expiryDate)) {
                $errorCount++;
                $dbg = "ic:".(empty($itemCode)?'Y':'N')." mn:".(empty($medName)?'Y':'N')." uom:".(empty($uomName)?'Y':'N')." ead:".($expiryAlertDays===null?'null':$expiryAlertDays)." qty:".($quantity===null?'null':$quantity)." exp:".(empty($expiryDate)?'Y':'N');
                $errorsList[] = ['row' => $currentRow, 'message' => 'Required fields missing or invalid. DEBUG: ' . $dbg];
                $processedInChunk++;
                if ($processedInChunk >= $chunkSize) break;
                continue;
            }

            try {
                $db->beginTransaction();

                $uomId = null;
                if (!empty($uomName) && isset($uoms[$uomName])) {
                    $uomId = $uoms[$uomName];
                }

                $medNameLower = strtolower($medName);
                if (!isset($medicines[$medNameLower])) {
                    $insertMedicineStmt->execute([$tenantId, $uomId, $volume, $medName, $itemCode, $genericName, $reorderLevel, $expiryAlertDays]);
                    $medId = $db->lastInsertId();
                    $medicines[$medNameLower] = $medId;
                }
                $medicineId = $medicines[$medNameLower];

                $stockLevel = $this->determineStockLevel($quantity, $reorderLevel);
                $insertBatchStmt->execute([
                    $tenantId, $branchId, $medicineId, $batchNumber, $supplier, $notes,
                    $unitCost, $sellingPrice, $expiryDate, $quantity, $stockLevel
                ]);

                $db->commit();
                $successCount++;
            } catch (\Exception $e) {
                if ($db->inTransaction()) {
                    $db->rollBack();
                }
                $errorCount++;
                $errorsList[] = ['row' => $currentRow, 'message' => $e->getMessage()];
            }

            $processedInChunk++;
            if ($processedInChunk >= $chunkSize) {
                break;
            }
        }

        $nextTest = fgetcsv($handle);
        if ($nextTest === false) {
            $done = true;
        }
        fclose($handle);

        $_SESSION['bulk_report']['total'] += $processedInChunk;
        $_SESSION['bulk_report']['success'] += $successCount;
        $_SESSION['bulk_report']['errors'] += $errorCount;
        $_SESSION['bulk_report']['error_details'] = array_merge($_SESSION['bulk_report']['error_details'], $errorsList);

        if ($done) {
            @unlink($tempFilePath);
            unset($_SESSION['bulk_edits'][$tempFilePath]);
            unset($_SESSION['bulk_temp_file']);
            unset($_SESSION['bulk_branch_id']);
        }

        echo json_encode([
            'success' => true,
            'processed' => $processedInChunk,
            'chunkSuccess' => $successCount,
            'chunkErrors' => $errorCount,
            'done' => $done,
            'next_row' => $startRow + $processedInChunk
        ]);
        exit;
    }

    public function bulkCommit() {
        if (!Auth::hasPrivilege('inventory')) {
            header("Location: /dashboard");
            exit;
        }

        $tempFilePath = $_POST['temp_file_path'] ?? $_SESSION['bulk_temp_file'] ?? '';
        $branchId = $_POST['branch_id'] ?? $_SESSION['bulk_branch_id'] ?? null;

        if ($_SERVER['REQUEST_METHOD'] !== 'POST' || empty($tempFilePath)) {
            Flash::set('error', 'Invalid import session.');
            header("Location: /inventory/bulk");
            exit;
        }
        
        if (!file_exists($tempFilePath)) {
            Flash::set('error', 'Upload session expired or file missing.');
            header("Location: /inventory/bulk");
            exit;
        }

        $user = Auth::user();
        $db = Database::getInstance()->getConnection();
        $tenantId = $user['tenant_id'];

        if (empty($branchId) && $user['role'] !== 'admin') {
            $branchId = $user['branch_id'];
        }

        if ($user['role'] !== 'admin' && $branchId != $user['branch_id']) {
            @unlink($tempFilePath);
            Flash::set('error', 'Permission denied for this branch.');
            header("Location: /inventory/bulk");
            exit;
        }

        // 1. Pre-load Master Data into memory
        $uoms = [];
        $stmt = $db->query("SELECT id, LOWER(name) as name FROM units_of_measure");
        foreach ($stmt->fetchAll() as $r) $uoms[$r['name']] = $r['id'];

        $medicines = [];
        $stmt = $db->prepare("SELECT id, LOWER(name) as name FROM medicines WHERE tenant_id = ?");
        $stmt->execute([$tenantId]);
        foreach ($stmt->fetchAll() as $r) $medicines[$r['name']] = $r['id'];

        $insertMedicineStmt = $db->prepare("INSERT INTO medicines (tenant_id, uom_id, volume, name, item_code, generic_name, reorder_level, expiry_alert_days) VALUES (?, ?, ?, ?, ?, ?, ?, ?)");

        // Apply session edits
        $sessionEdits = $_SESSION['bulk_edits'][$tempFilePath] ?? [];

        // 2. Process CSV File
        $handle = fopen($tempFilePath, 'r');
        $header = fgetcsv($handle);

        $totalRows = 0;
        $successCount = 0;
        $batchInsertData = [];
        $batchSize = 1000;

        try {
            $db->beginTransaction();

            while (($row = fgetcsv($handle)) !== false) {
                $totalRows++;
                
                // Apply edits from session
                if (isset($sessionEdits[$totalRows])) {
                    $edits = $sessionEdits[$totalRows];
                    if (isset($edits['itemCode'])) $row[0] = $edits['itemCode'];
                    if (isset($edits['medName'])) $row[1] = $edits['medName'];
                    if (isset($edits['uomName'])) $row[3] = $edits['uomName'];
                    if (isset($edits['expiryAlertDays'])) $row[6] = $edits['expiryAlertDays'];
                    if (isset($edits['quantity'])) $row[7] = $edits['quantity'];
                    if (isset($edits['expiryDate'])) $row[8] = $edits['expiryDate'];
                    if (isset($edits['notes'])) $row[12] = $edits['notes'];
                }

                if (count($row) < 12) continue;

                $itemCode = trim($row[0]);
                $medName = trim($row[1]);
                $genericName = trim($row[2]);
                $uomName = strtolower(trim($row[3]));
                $volume = trim($row[4]) !== '' ? floatval(trim($row[4])) : null;
                $reorderLevel = trim($row[5]) !== '' ? intval(trim($row[5])) : 10;
                
                $rawExpiryAlertDays = trim($row[6]);
                $expiryAlertDays = $rawExpiryAlertDays !== '' ? intval($rawExpiryAlertDays) : null;
                
                $rawQty = trim($row[7]);
                $quantity = $rawQty !== '' ? intval($rawQty) : null;
                
                $rawExpiry = trim($row[8]);
                $expiryDate = null;
                if (!empty($rawExpiry)) {
                    try {
                        $d = new \DateTime($rawExpiry);
                        $expiryDate = $d->format('Y-m-d');
                    } catch (\Exception $e) {
                        $expiryDate = null;
                    }
                }

                $unitCost = trim($row[9]) !== '' ? floatval(trim($row[9])) : null;
                $sellingPrice = trim($row[10]) !== '' ? floatval(trim($row[10])) : null;
                $supplier = trim($row[11]);
                $notes = isset($row[12]) ? trim($row[12]) : null;
                if ($notes === '') $notes = null;
                $batchNumber = 'B-' . date('ymd') . '-' . strtoupper(substr(uniqid(), -4));

                if (empty($itemCode) || empty($medName) || empty($uomName) || $expiryAlertDays === null || $expiryAlertDays < 1 || $quantity === null || $quantity < 1 || empty($expiryDate)) {
                    continue; // Skip invalid, already reported in preview
                }

                $uomId = null;
                if (!empty($uomName) && isset($uoms[$uomName])) {
                    $uomId = $uoms[$uomName];
                }

                // Get or Create Medicine
                $medNameLower = strtolower($medName);
                if (!isset($medicines[$medNameLower])) {
                    $insertMedicineStmt->execute([$tenantId, $uomId, $volume, $medName, $itemCode, $genericName, $reorderLevel, $expiryAlertDays]);
                    $medId = $db->lastInsertId();
                    $medicines[$medNameLower] = $medId;
                }
                $medicineId = $medicines[$medNameLower];

                $stockLevel = $this->determineStockLevel($quantity, $reorderLevel);
                // Add to batch insert
                $batchInsertData[] = [
                    $tenantId, $branchId, $medicineId, $batchNumber, $supplier, $notes,
                    $unitCost, $sellingPrice, $expiryDate, $quantity, $stockLevel
                ];
                $successCount++;

                if (count($batchInsertData) >= $batchSize) {
                    $this->executeBulkInsert($db, $batchInsertData);
                    $batchInsertData = [];
                }
            }
            
            if (count($batchInsertData) > 0) {
                $this->executeBulkInsert($db, $batchInsertData);
            }

            $db->commit();
            fclose($handle);
            @unlink($tempFilePath); // Cleanup temp file
            unset($_SESSION['bulk_edits'][$tempFilePath]); // Clear edits
            unset($_SESSION['bulk_temp_file']);
            unset($_SESSION['bulk_branch_id']);

            $_SESSION['bulk_report'] = [
                'total' => $totalRows,
                'success' => $successCount,
                'errors' => $totalRows - $successCount,
                'error_details' => []
            ];

            header("Location: /inventory/bulk");
            exit;

        } catch (\Exception $e) {
            if ($db->inTransaction()) {
                $db->rollBack();
            }
            fclose($handle);
            @unlink($tempFilePath); // Cleanup temp file
            unset($_SESSION['bulk_temp_file']);
            unset($_SESSION['bulk_branch_id']);
            Flash::set('error', 'Critical Error: ' . $e->getMessage());
            header("Location: /inventory/bulk");
            exit;
        }
    }

    private function executeBulkInsert($db, $data) {
        if (empty($data)) return;
        
        $placeholders = [];
        $values = [];
        
        foreach ($data as $row) {
            $placeholders[] = '(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, \'active\', ?)';
            foreach ($row as $val) {
                $values[] = $val;
            }
        }
        
        $sql = "INSERT INTO inventory_batches (tenant_id, branch_id, medicine_id, batch_number, supplier, notes, unit_price, selling_price, expiry_date, quantity, status, stock_level) VALUES " . implode(', ', $placeholders);
        $stmt = $db->prepare($sql);
        $stmt->execute($values);
    }

    private function validateCsv($tempFilePath, $sessionEdits) {
        $handle = fopen($tempFilePath, 'r');
        if (!$handle) return null;
        
        $header = fgetcsv($handle);
        $totalRows = 0;
        $validRowsCount = 0;
        $allErrors = [];

        $db = Database::getInstance()->getConnection();
        $uoms = [];
        $stmt = $db->query("SELECT id, LOWER(name) as name FROM units_of_measure");
        foreach ($stmt->fetchAll() as $r) $uoms[$r['name']] = $r['id'];

        while (($row = fgetcsv($handle)) !== false) {
            $totalRows++;
            
            // Apply edits from session
            if (isset($sessionEdits[$totalRows])) {
                $edits = $sessionEdits[$totalRows];
                if (isset($edits['itemCode'])) $row[0] = $edits['itemCode'];
                if (isset($edits['medName'])) $row[1] = $edits['medName'];
                if (isset($edits['uomName'])) $row[3] = $edits['uomName'];
                if (isset($edits['expiryAlertDays'])) $row[6] = $edits['expiryAlertDays'];
                if (isset($edits['quantity'])) $row[7] = $edits['quantity'];
                if (isset($edits['expiryDate'])) $row[8] = $edits['expiryDate'];
                if (isset($edits['notes'])) $row[12] = $edits['notes'];
            }
            
            if (count($row) < 12) {
                $allErrors[$totalRows] = [
                    'message' => 'Missing columns. Expected at least 12.',
                    'data' => $row
                ];
                continue;
            }

            $itemCode = trim($row[0]);
            $medName = trim($row[1]);
            $uomName = strtolower(trim($row[3]));
            
            $rawExpiry = trim($row[8]);
            $expiryDate = null;
            $isExpiryDateValidFormat = false;
            
            if (!empty($rawExpiry)) {
                try {
                    $d = new \DateTime($rawExpiry);
                    $expiryDate = $d->format('Y-m-d');
                    $isExpiryDateValidFormat = true;
                } catch (\Exception $e) {
                    $isExpiryDateValidFormat = false;
                }
            }

            $rawQty = trim($row[7]);
            $quantity = $rawQty !== '' ? intval($rawQty) : null;
            
            $rawExpiryAlertDays = trim($row[6]);
            $expiryAlertDays = $rawExpiryAlertDays !== '' ? intval($rawExpiryAlertDays) : null;

            $missingFields = [];
            $invalidFields = [];

            if (empty($itemCode)) {
                $missingFields[] = 'Item Code';
            }
            if (empty($medName)) {
                $missingFields[] = 'Medicine Name';
            }
            
            if (empty($row[3])) {
                $missingFields[] = 'Unit of Measure';
            } elseif (!isset($uoms[$uomName])) {
                $invalidFields[] = "Unit of Measure ('{$row[3]}' is not a registered UOM)";
            }

            if ($rawExpiryAlertDays === '') {
                $missingFields[] = 'Expiry Alert Days';
            } elseif ($expiryAlertDays === null || $expiryAlertDays < 1) {
                $invalidFields[] = 'Expiry Alert Days must be at least 1';
            }
            
            if ($rawQty === '') {
                $missingFields[] = 'Initial Quantity';
            } elseif ($quantity === null || $quantity < 1) {
                $invalidFields[] = 'Initial Quantity must be at least 1';
            }

            if (empty($rawExpiry)) {
                $missingFields[] = 'Expiry Date';
            } elseif (!$isExpiryDateValidFormat) {
                $invalidFields[] = "Expiry Date ('{$rawExpiry}' is not a valid date)";
            }

            if (!empty($missingFields) || !empty($invalidFields)) {
                $msgs = [];
                if (!empty($missingFields)) {
                    $msgs[] = 'Missing required field: ' . implode(', ', $missingFields);
                }
                if (!empty($invalidFields)) {
                    $msgs[] = implode(', ', $invalidFields);
                }
                
                $allErrors[$totalRows] = [
                    'message' => implode('. ', $msgs) . '.',
                    'data' => $row
                ];
                continue;
            }

            $validRowsCount++;
        }
        fclose($handle);
        
        return [
            'totalRows' => $totalRows,
            'validRowsCount' => $validRowsCount,
            'allErrors' => $allErrors
        ];
    }

    private function determineStockLevel($quantity, $reorderLevel) {
        $quantity = intval($quantity);
        $reorderLevel = intval($reorderLevel);
        if ($quantity === 0) {
            return 'out-of stock';
        }
        if ($quantity <= $reorderLevel) {
            return 'low-stock';
        }
        return 'In-stock';
    }
}
