<?php
date_default_timezone_set('Asia/Manila');

session_start();
// ==========================================
// SESSION TIMEOUT LOGIC (Idle Timer)
// ==========================================
$timeout_duration = 43200; // 43200 seconds = 12 hours. (Use 3600 for 1 hour, 86400 for 24 hours)

if (isset($_SESSION['user_id'])) {
    if (isset($_SESSION['last_activity']) && (time() - $_SESSION['last_activity']) > $timeout_duration) {
        // The user was idle for too long. Destroy the session!
        session_unset();
        session_destroy();
        
        // If the frontend is quietly checking auth, tell it the session died
        if (isset($_GET['action']) && $_GET['action'] === 'check_auth') {
            echo json_encode(['status' => 'timeout', 'message' => 'Session expired.']);
            exit;
        }
    } else {
        // The user is active! Reset the timer back to zero.
        $_SESSION['last_activity'] = time();
    }
}

header('Content-Type: application/json');
require_once dirname(__DIR__) . '/db/db.php'; // Ensure your db.php from the previous step is still present

$action = isset($_GET['action']) ? $_GET['action'] : '';

// Protect actions - require login for everything except login and checking auth
// $public_actions = ['login', 'check_auth'];
// if (!in_array($action, $public_actions) && !isset($_SESSION['user_id'])) {
//     echo json_encode(['status' => 'error', 'message' => 'Unauthorized access. Please log in.']);
//     exit;
// }

switch ($action) {
    case 'check_auth':
        if (isset($_SESSION['user_id'])) {
            echo json_encode([
                'status' => 'success', 
                'username' => $_SESSION['username'],
                'role' => $_SESSION['role'],
                'full_name' => $_SESSION['full_name']
            ]);
        } else {
            echo json_encode(['status' => 'error', 'message' => 'Not logged in']);
        }
        break;

    case 'login':
        if ($_SERVER['REQUEST_METHOD'] === 'POST') {
            
            // --- 1. COOLDOWN CHECK ---
            // If the user is currently locked out, reject the request immediately
            if (isset($_SESSION['lockout_time']) && time() < $_SESSION['lockout_time']) {
                $remaining = $_SESSION['lockout_time'] - time();
                echo json_encode([
                    'status' => 'locked', 
                    'message' => "Too many failed attempts. Locked out.", 
                    'remaining' => $remaining
                ]);
                exit;
            }

            $username = $_POST['username'] ?? '';
            $password = $_POST['password'] ?? '';

            $stmt = $pdo->prepare("
                SELECT u.id, u.username, u.password, u.role, e.first_name, e.last_name 
                FROM users u
                LEFT JOIN employees e ON u.employee_id = e.employee_id
                WHERE u.username = :username
            ");
            $stmt->execute([':username' => $username]);
            $user = $stmt->fetch(PDO::FETCH_ASSOC);

            // --- 2. VERIFY PASSWORD ---
            if ($user && password_verify($password, $user['password'])) {
                // SUCCESS: Reset the penalty counters
                unset($_SESSION['login_attempts']);
                unset($_SESSION['lockout_time']);

                $_SESSION['user_id'] = $user['id'];
                $_SESSION['username'] = $user['username'];
                $_SESSION['role'] = $user['role'];
                $_SESSION['full_name'] = $user['first_name'] ? $user['first_name'] . ' ' . $user['last_name'] : 'Administrator';

                // START THE TIMEOUT CLOCK!
                $_SESSION['last_activity'] = time();

                echo json_encode(['status' => 'success', 'message' => 'Login successful']);
            } else {
                // FAILURE: Increase the strike counter
                $_SESSION['login_attempts'] = isset($_SESSION['login_attempts']) ? $_SESSION['login_attempts'] + 1 : 1;
                $max_free_attempts = 3;

                if ($_SESSION['login_attempts'] >= $max_free_attempts) {
                    // Progressive Math: 30s, then 60s, then 120s, then 240s...
                    $multiplier = $_SESSION['login_attempts'] - $max_free_attempts;
                    $penalty_seconds = 30 * pow(2, $multiplier); 
                    
                    $_SESSION['lockout_time'] = time() + $penalty_seconds;

                    echo json_encode([
                        'status' => 'locked', 
                        'message' => "Too many failed attempts.", 
                        'remaining' => $penalty_seconds
                    ]);
                } else {
                    $attempts_left = $max_free_attempts - $_SESSION['login_attempts'];
                    echo json_encode([
                        'status' => 'error', 
                        'message' => "Invalid credentials. $attempts_left attempt(s) remaining."
                    ]);
                }
            }
        }
        break;

    case 'logout':
        session_unset();
        session_destroy();
        echo json_encode(['status' => 'success', 'message' => 'Logged out successfully']);
        break;

    case 'get_inventory':
        $stmt = $pdo->query("
            SELECT i.*, s.name as supplier_name 
            FROM inventory_items i 
            LEFT JOIN suppliers s ON i.supplier_id = s.supplier_id
            ORDER BY i.name ASC
        ");
        $items = $stmt->fetchAll();
        echo json_encode(['status' => 'success', 'data' => $items]);
        break;

    case 'get_available_items':
        try {
            // Only fetch items where quantity is greater than 0
            $stmt = $pdo->query("
                SELECT inventory_id, name, description, quantity, unit_price 
                FROM inventory_items 
                WHERE quantity > 0 
                ORDER BY name ASC
            ");
            $items = $stmt->fetchAll(PDO::FETCH_ASSOC);
            echo json_encode(['status' => 'success', 'data' => $items]);
        } catch (Exception $e) {
            echo json_encode(['status' => 'error', 'message' => 'Failed to fetch items: ' . $e->getMessage()]);
        }
        break; 

    case 'get_suppliers':
        // UPDATED: Now fetches ALL columns so the edit modal gets the email, phone, etc.
        $stmt = $pdo->query("SELECT * FROM suppliers ORDER BY name ASC");
        $suppliers = $stmt->fetchAll();
        echo json_encode(['status' => 'success', 'data' => $suppliers]);
        break;

    case 'get_supplier_items':
        $supplier_id = $_GET['supplier_id'] ?? 0;
        
        // Fetch only items that belong to the selected supplier
        $stmt = $pdo->prepare("SELECT inventory_id, name, quantity, unit_price FROM inventory_items WHERE supplier_id = ? ORDER BY name ASC");
        $stmt->execute([$supplier_id]);
        $items = $stmt->fetchAll();
        
        echo json_encode(['status' => 'success', 'data' => $items]);
        break;

    case 'get_issued_items':
        try {
            // Join transactions with inventory to get the item names
            $stmt = $pdo->query("
                SELECT 
                    t.transaction_id, 
                    t.transaction_date, 
                    t.qty_issued, 
                    i.inventory_id, 
                    i.name, 
                    i.description 
                FROM inventory_transactions t
                JOIN inventory_items i ON t.inventory_id = i.inventory_id
                ORDER BY t.transaction_date DESC
            ");
            $data = $stmt->fetchAll(PDO::FETCH_ASSOC);
            echo json_encode(['status' => 'success', 'data' => $data]);
        } catch (Exception $e) {
            echo json_encode(['status' => 'error', 'message' => 'Database error: ' . $e->getMessage()]);
        }
        break;

    // ---------------------------------------------------------
    // NEW VENDOR MANAGEMENT ENDPOINTS
    // ---------------------------------------------------------

    case 'get_vendor_history':
        $supplier_id = $_GET['supplier_id'] ?? 0;
        try {
            $stmt = $pdo->prepare("SELECT po_id, order_date, total_amount, status FROM purchase_orders WHERE supplier_id = ? ORDER BY order_date DESC");
            $stmt->execute([$supplier_id]);
            $data = $stmt->fetchAll(PDO::FETCH_ASSOC);
            echo json_encode(['status' => 'success', 'data' => $data]);
        } catch (Exception $e) {
            echo json_encode(['status' => 'error', 'message' => $e->getMessage()]);
        }
        break;

    case 'add_supplier':
        if ($_SERVER['REQUEST_METHOD'] === 'POST') {
            $name = $_POST['name'] ?? '';
            $person = $_POST['contact_person'] ?? '';
            $phone = $_POST['phone'] ?? '';
            $email = $_POST['email'] ?? '';
            $address = $_POST['address'] ?? '';

            try {
                $stmt = $pdo->prepare("INSERT INTO suppliers (name, contact_person, phone, email, address) VALUES (?, ?, ?, ?, ?)");
                $stmt->execute([$name, $person, $phone, $email, $address]);
                echo json_encode(['status' => 'success', 'message' => 'Vendor added successfully.']);
            } catch (Exception $e) {
                echo json_encode(['status' => 'error', 'message' => 'Failed to add vendor: ' . $e->getMessage()]);
            }
        }
        break;

    case 'update_supplier':
        if ($_SERVER['REQUEST_METHOD'] === 'POST') {
            $id = $_POST['supplier_id'] ?? '';
            $name = $_POST['name'] ?? '';
            $person = $_POST['contact_person'] ?? '';
            $phone = $_POST['phone'] ?? '';
            $email = $_POST['email'] ?? '';
            $address = $_POST['address'] ?? '';

            try {
                $stmt = $pdo->prepare("UPDATE suppliers SET name=?, contact_person=?, phone=?, email=?, address=? WHERE supplier_id=?");
                $stmt->execute([$name, $person, $phone, $email, $address, $id]);
                echo json_encode(['status' => 'success', 'message' => 'Vendor updated successfully.']);
            } catch (Exception $e) {
                echo json_encode(['status' => 'error', 'message' => 'Failed to update vendor: ' . $e->getMessage()]);
            }
        }
        break;

    case 'delete_supplier':
        if ($_SESSION['role'] !== 'admin') {
            echo json_encode(['status' => 'error', 'message' => 'Access Denied: Admins only.']);
            break;
        }

        if ($_SERVER['REQUEST_METHOD'] === 'POST') {
            $id = $_POST['supplier_id'] ?? '';
            try {
                $stmt = $pdo->prepare("DELETE FROM suppliers WHERE supplier_id = ?");
                $stmt->execute([$id]);
                echo json_encode(['status' => 'success', 'message' => 'Vendor deleted successfully.']);
            } catch (PDOException $e) {
                // Catches Foreign Key constraint if supplier has active inventory/POs
                if ($e->getCode() == '23000') {
                    echo json_encode(['status' => 'error', 'message' => 'Cannot delete: Vendor is tied to existing items or Purchase Orders.']);
                } else {
                    echo json_encode(['status' => 'error', 'message' => 'Failed to delete: ' . $e->getMessage()]);
                }
            }
        }
        break;

    // ---------------------------------------------------------
    // END VENDOR MANAGEMENT ENDPOINTS
    // ---------------------------------------------------------

    case 'add_item':
        if ($_SERVER['REQUEST_METHOD'] === 'POST') {
            $name = $_POST['name'] ?? '';
            $description = $_POST['description'] ?? '';
            $quantity = $_POST['quantity'] ?? 0;
            $unit_price = $_POST['unit_price'] ?? 0.00;
            $reorder_level = $_POST['reorder_level'] ?? 0;
            $supplier_id = $_POST['supplier_id'] ?? null;

            try {
                $sql = "INSERT INTO inventory_items (name, description, quantity, unit_price, reorder_level, supplier_id) 
                        VALUES (:name, :description, :quantity, :unit_price, :reorder_level, :supplier_id)";
                $stmt = $pdo->prepare($sql);
                $stmt->execute([
                    ':name' => $name,
                    ':description' => $description,
                    ':quantity' => $quantity,
                    ':unit_price' => $unit_price,
                    ':reorder_level' => $reorder_level,
                    ':supplier_id' => $supplier_id
                ]);
                echo json_encode(['status' => 'success', 'message' => 'Item added successfully']);
            } catch (Exception $e) {
                echo json_encode(['status' => 'error', 'message' => $e->getMessage()]);
            }
        }
        break;

    case 'update_item':
        if ($_SERVER['REQUEST_METHOD'] === 'POST') {
            $id = $_POST['inventory_id'];
            $name = $_POST['name'];
            $description = $_POST['description'];
            $quantity = $_POST['quantity'];
            $unit_price = $_POST['unit_price'];
            $reorder_level = $_POST['reorder_level'];
            $supplier_id = $_POST['supplier_id'];

            try {
                $stmt = $pdo->prepare("UPDATE inventory_items SET name=?, description=?, quantity=?, unit_price=?, reorder_level=?, supplier_id=? WHERE inventory_id=?");
                $stmt->execute([$name, $description, $quantity, $unit_price, $reorder_level, $supplier_id, $id]);
                echo json_encode(['status' => 'success', 'message' => 'Item updated successfully']);
            } catch (Exception $e) {
                echo json_encode(['status' => 'error', 'message' => $e->getMessage()]);
            }
        }
        break;

    case 'delete_item':
        if ($_SESSION['role'] !== 'admin') {
            echo json_encode(['status' => 'error', 'message' => 'Access Denied: Admins only.']);
            break;
        }

        if ($_SERVER['REQUEST_METHOD'] === 'POST') {
            $id = $_POST['inventory_id'];
            try {
                $stmt = $pdo->prepare("DELETE FROM inventory_items WHERE inventory_id=?");
                $stmt->execute([$id]);
                echo json_encode(['status' => 'success', 'message' => 'Item deleted successfully']);
            } catch (PDOException $e) {
                // Error 23000 is a Foreign Key Constraint violation
                if ($e->getCode() == '23000') {
                    echo json_encode(['status' => 'error', 'message' => 'Cannot delete: This item is part of an existing Purchase Order.']);
                } else {
                    echo json_encode(['status' => 'error', 'message' => 'Failed to delete: ' . $e->getMessage()]);
                }
            }
        }
        break;

    case 'issue_item':
        if ($_SERVER['REQUEST_METHOD'] === 'POST') {
            $id = $_POST['inventory_id'] ?? null;
            $issue_qty = (int)($_POST['issue_qty'] ?? 0);

            if (!$id || $issue_qty <= 0) {
                echo json_encode(['status' => 'error', 'message' => 'Invalid ID or Quantity.']);
                break;
            }

            try {
                $pdo->beginTransaction();
                
                // 1. Check current stock before deducting
                $stmtCheck = $pdo->prepare("SELECT name, quantity FROM inventory_items WHERE inventory_id = ?");
                $stmtCheck->execute([$id]);
                $item = $stmtCheck->fetch(PDO::FETCH_ASSOC);

                if (!$item) {
                    throw new Exception("Item not found in the database.");
                }
                
                // 2. Prevent negative inventory
                if ($item['quantity'] < $issue_qty) {
                    throw new Exception("Insufficient stock! You requested $issue_qty, but only {$item['quantity']} are available.");
                }

                // 3. Subtract the quantity safely
                $stmt = $pdo->prepare("UPDATE inventory_items SET quantity = quantity - ? WHERE inventory_id = ?");
                $stmt->execute([$issue_qty, $id]);
                
                // 4. Log the transaction for the predictive engine
                $logStmt = $pdo->prepare("INSERT INTO inventory_transactions (inventory_id, qty_issued) VALUES (?, ?)");
                $logStmt->execute([$id, $issue_qty]);

                $pdo->commit();
                echo json_encode([
                    'status' => 'success', 
                    'message' => "Successfully issued $issue_qty unit(s) of {$item['name']}."
                ]);
                
            } catch (Exception $e) {
                $pdo->rollBack();
                echo json_encode(['status' => 'error', 'message' => $e->getMessage()]);
            }
        }
        break;

        case 'get_po_details':
        $po_id = $_GET['po_id'] ?? 0;
        try {
            // Join purchase_items with inventory_items to get the actual item name
            $stmt = $pdo->prepare("
                SELECT pi.quantity, pi.unit_price, i.name 
                FROM purchase_items pi
                JOIN inventory_items i ON pi.inventory_id = i.inventory_id
                WHERE pi.po_id = ?
            ");
            $stmt->execute([$po_id]);
            $data = $stmt->fetchAll(PDO::FETCH_ASSOC);
            
            echo json_encode(['status' => 'success', 'data' => $data]);
        } catch (Exception $e) {
            echo json_encode(['status' => 'error', 'message' => $e->getMessage()]);
        }
        break;
    
    case 'get_pos':
        try {
            $stmt = $pdo->query("
                SELECT 
                    po.po_id, po.order_date, s.name as supplier_name, 
                    po.total_amount, po.status, i.name as item_name,
                    pi.quantity as item_qty, pi.unit_price, s.supplier_id
                FROM purchase_orders po
                JOIN suppliers s ON po.supplier_id = s.supplier_id
                LEFT JOIN purchase_items pi ON po.po_id = pi.po_id
                LEFT JOIN inventory_items i ON pi.inventory_id = i.inventory_id
                ORDER BY po.po_id DESC
            ");
            $pos = $stmt->fetchAll();
            echo json_encode(['status' => 'success', 'data' => $pos]);
        } catch (Exception $e) {
            echo json_encode(['status' => 'error', 'message' => $e->getMessage()]);
        }
        break;

    case 'auto_restock':
        try {
            $stmt = $pdo->query("SELECT * FROM inventory_items WHERE quantity <= reorder_level");
            $low_stock_items = $stmt->fetchAll(PDO::FETCH_ASSOC);

            if (count($low_stock_items) === 0) {
                echo json_encode(['status' => 'error', 'message' => 'All stock levels are currently healthy. Nothing to restock.']);
                exit;
            }

            $pdo->beginTransaction();
            $po_created_count = 0;

            foreach ($low_stock_items as $item) {
                $order_qty = ($item['reorder_level'] > 0) ? $item['reorder_level'] : 10;
                $total_amount = $order_qty * $item['unit_price'];

                // 1. Create the Main Order Wrapper
                $insertPo = $pdo->prepare("
                    INSERT INTO purchase_orders (supplier_id, order_date, total_amount, status) 
                    VALUES (?, NOW(), ?, 'Pending')
                ");
                $insertPo->execute([$item['supplier_id'], $total_amount]);
                $new_po_id = $pdo->lastInsertId();

                // 2. Insert the actual Item details into purchase_items
                $insertItem = $pdo->prepare("
                    INSERT INTO purchase_items (po_id, inventory_id, quantity, unit_price) 
                    VALUES (?, ?, ?, ?)
                ");
                $insertItem->execute([$new_po_id, $item['inventory_id'], $order_qty, $item['unit_price']]);
                
                $po_created_count++;
            }

            $pdo->commit();
            echo json_encode(['status' => 'success', 'message' => "Successfully generated $po_created_count individual Purchase Order(s)."]);

        } catch (Exception $e) {
            $pdo->rollBack();
            echo json_encode(['status' => 'error', 'message' => 'System Error: ' . $e->getMessage()]);
        }
        break;

    case 'create_po':
        if ($_SERVER['REQUEST_METHOD'] === 'POST') {
            $supplier_id = $_POST['supplier_id'];
            $inventory_id = $_POST['inventory_id'];
            $quantity = $_POST['quantity'];
            $unit_price = $_POST['unit_price'];
            $total_amount = $quantity * $unit_price;
            $order_date = date('Y-m-d');

            try {
                $pdo->beginTransaction();
                // Changed 'Ordered' to 'Pending'
                $stmt = $pdo->prepare("INSERT INTO purchase_orders (supplier_id, order_date, total_amount, status) VALUES (?, ?, ?, 'Pending')");
                $stmt->execute([$supplier_id, $order_date, $total_amount]);
                $po_id = $pdo->lastInsertId();

                $stmt2 = $pdo->prepare("INSERT INTO purchase_items (po_id, inventory_id, quantity, unit_price) VALUES (?, ?, ?, ?)");
                $stmt2->execute([$po_id, $inventory_id, $quantity, $unit_price]);

                $pdo->commit();
                echo json_encode(['status' => 'success', 'message' => 'Purchase Order created and sent to Finance for approval.']);
            } catch (Exception $e) {
                $pdo->rollBack();
                echo json_encode(['status' => 'error', 'message' => 'Failed to create PO: ' . $e->getMessage()]);
            }
        }
        break;

    case 'update_po_qty':
        if ($_SERVER['REQUEST_METHOD'] === 'POST') {
            $po_id = $_POST['po_id'];
            $new_quantity = (int)$_POST['new_quantity'];

            try {
                $pdo->beginTransaction();

                // 1. Get the unit price for this PO item
                $stmt1 = $pdo->prepare("SELECT unit_price FROM purchase_items WHERE po_id = ?");
                $stmt1->execute([$po_id]);
                $item = $stmt1->fetch();

                if (!$item) throw new Exception("PO Item not found.");

                $unit_price = $item['unit_price'];
                $new_total = $new_quantity * $unit_price;

                // 2. Update Quantity in purchase_items
                $stmt2 = $pdo->prepare("UPDATE purchase_items SET quantity = ? WHERE po_id = ?");
                $stmt2->execute([$new_quantity, $po_id]);

                // 3. Update Total Amount in purchase_orders (Ensure it's still Pending)
                $stmt3 = $pdo->prepare("UPDATE purchase_orders SET total_amount = ? WHERE po_id = ? AND status = 'Pending'");
                $stmt3->execute([$new_total, $po_id]);

                if ($stmt3->rowCount() == 0) throw new Exception("Cannot update. PO might already be approved by Finance.");

                $pdo->commit();
                echo json_encode(['status' => 'success', 'message' => 'PO Quantity and Total Price updated successfully.']);
            } catch (Exception $e) {
                $pdo->rollBack();
                echo json_encode(['status' => 'error', 'message' => $e->getMessage()]);
            }
        }
        break;

    case 'update_po_status':
        if ($_SERVER['REQUEST_METHOD'] === 'POST') {
            $po_id = $_POST['po_id'] ?? null;
            $new_status = $_POST['status'] ?? '';

            // 1. Strict Validation: Match your database ENUM exactly
            $allowed_statuses = ['Pending', 'Approved', 'Received', 'Missing', 'Issues', 'Cancelled'];

            if (!$po_id || !in_array($new_status, $allowed_statuses)) {
                echo json_encode(['status' => 'error', 'message' => 'Invalid Purchase Order ID or Status.']);
                break;
            }

            try {
                // 2. Prevent status changes if the PO is already permanently finalized
                $stmt = $pdo->prepare("
                    UPDATE purchase_orders 
                    SET status = ? 
                    WHERE po_id = ? AND status NOT IN ('Received', 'Cancelled')
                ");
                $stmt->execute([$new_status, $po_id]);

                // 3. Verify that the database actually made a change
                if ($stmt->rowCount() > 0) {
                    echo json_encode([
                        'status' => 'success', 
                        'message' => "Purchase Order #$po_id successfully updated to '$new_status'."
                    ]);
                } else {
                    echo json_encode([
                        'status' => 'error', 
                        'message' => "Could not update. PO #$po_id may not exist or is already finalized."
                    ]);
                }

            } catch (PDOException $e) {
                echo json_encode(['status' => 'error', 'message' => 'Database Error: ' . $e->getMessage()]);
            } catch (Exception $e) {
                echo json_encode(['status' => 'error', 'message' => 'System Error: ' . $e->getMessage()]);
            }
        }
        break;

    case 'receive_po':
        if ($_SERVER['REQUEST_METHOD'] === 'POST') {
            $po_id = $_POST['po_id'];

            try {
                $pdo->beginTransaction();

                // 1. Explicitly check the current status in the database first
                $checkStmt = $pdo->prepare("SELECT status FROM purchase_orders WHERE po_id = ?");
                $checkStmt->execute([$po_id]);
                $current_po = $checkStmt->fetch(PDO::FETCH_ASSOC);

                if (!$current_po) {
                    throw new Exception("Purchase Order #$po_id not found in the database.");
                }

                if ($current_po['status'] === 'Received') {
                    throw new Exception("This PO has already been received and added to inventory.");
                }

                // Make sure it matches exactly (Case sensitive)
                if (strtolower($current_po['status']) !== 'approved') {
                    throw new Exception("Cannot receive. The database says this PO is currently '" . $current_po['status'] . "', but it must be 'Approved'.");
                }

                // 2. Update PO status to Received
                $stmt = $pdo->prepare("UPDATE purchase_orders SET status = 'Received' WHERE po_id = ?");
                $stmt->execute([$po_id]);

                // 3. Get the items from this PO
                $stmtItems = $pdo->prepare("SELECT inventory_id, quantity FROM purchase_items WHERE po_id = ?");
                $stmtItems->execute([$po_id]);
                $items = $stmtItems->fetchAll();

                // 4. Loop through items and update inventory stock
                $updateStock = $pdo->prepare("UPDATE inventory_items SET quantity = quantity + ? WHERE inventory_id = ?");
                foreach ($items as $item) {
                    $updateStock->execute([$item['quantity'], $item['inventory_id']]);
                }

                // 5. 🔥 FINANCE INTEGRATION: Generate Vendor Payment (Invoice) 🔥
                $stmtPo = $pdo->prepare("SELECT supplier_id, total_amount FROM purchase_orders WHERE po_id = ?");
                $stmtPo->execute([$po_id]);
                $poData = $stmtPo->fetch();

                $stmtFinance = $pdo->prepare("INSERT INTO vendor_payments (supplier_id, po_id, pay_date, amount) VALUES (?, ?, ?, ?)");
                $stmtFinance->execute([
                    $poData['supplier_id'], 
                    $po_id, 
                    date('Y-m-d'), 
                    $poData['total_amount']
                ]);

                $pdo->commit();
                echo json_encode(['status' => 'success', 'message' => 'PO Received! Stock updated & Finance invoiced.']);
                
            } catch (Exception $e) {
                $pdo->rollBack();
                echo json_encode(['status' => 'error', 'message' => $e->getMessage()]);
            }
        }
        break;

    case 'cancel_po':
        if ($_SERVER['REQUEST_METHOD'] === 'POST') {
            $po_id = $_POST['po_id'];
            try {
                // Only allow cancellation if it hasn't been received yet!
                $stmt = $pdo->prepare("UPDATE purchase_orders SET status = 'Cancelled' WHERE po_id = ? AND status != 'Received'");
                $stmt->execute([$po_id]);
                
                if($stmt->rowCount() > 0) {
                    echo json_encode(['status' => 'success', 'message' => 'Purchase Order Cancelled.']);
                } else {
                    echo json_encode(['status' => 'error', 'message' => 'Cannot cancel this PO.']);
                }
            } catch (Exception $e) {
                echo json_encode(['status' => 'error', 'message' => 'Database error: ' . $e->getMessage()]);
            }
        }
        break;

    case 'issue_item':
        if ($_SERVER['REQUEST_METHOD'] === 'POST') {
            $id = $_POST['inventory_id'];
            $issue_qty = (int)$_POST['issue_qty'];

            try {
                $pdo->beginTransaction();

                // 1. Subtract the quantity
                $stmt = $pdo->prepare("UPDATE inventory_items SET quantity = GREATEST(0, quantity - ?) WHERE inventory_id = ?");
                $stmt->execute([$issue_qty, $id]);
                
                // 2. NEW: Log the transaction so we can predict future burn rates
                $logStmt = $pdo->prepare("INSERT INTO inventory_transactions (inventory_id, qty_issued) VALUES (?, ?)");
                $logStmt->execute([$id, $issue_qty]);

                $pdo->commit();
                echo json_encode(['status' => 'success', 'message' => "Successfully issued $issue_qty item(s)."]);
            } catch (Exception $e) {
                $pdo->rollBack();
                echo json_encode(['status' => 'error', 'message' => 'Failed to issue item: ' . $e->getMessage()]);
            }
        }
        break;

    case 'get_inventory':
        // NEW: We join the last 30 days of transactions to calculate the Burn Rate
        $stmt = $pdo->query("
            SELECT 
                i.*, 
                s.name as supplier_name,
                COALESCE(SUM(t.qty_issued), 0) as issued_last_30_days
            FROM inventory_items i 
            LEFT JOIN suppliers s ON i.supplier_id = s.supplier_id
            LEFT JOIN inventory_transactions t ON i.inventory_id = t.inventory_id 
                AND t.transaction_date >= DATE_SUB(NOW(), INTERVAL 30 DAY)
            GROUP BY i.inventory_id
            ORDER BY i.name ASC
        ");
        
        $items = $stmt->fetchAll(PDO::FETCH_ASSOC);

        // Calculate Predictive Metrics in PHP
        foreach($items as &$item) {
            // Average per day over the last 30 days
            $daily_burn = $item['issued_last_30_days'] / 30;
            $item['daily_burn_rate'] = round($daily_burn, 2);
            
            // Forecast: How many days until we hit 0?
            if ($daily_burn > 0) {
                $item['days_to_stockout'] = floor($item['quantity'] / $daily_burn);
            } else {
                // If 0 burn rate, they have a "Safe" 999 days
                $item['days_to_stockout'] = 999; 
            }
        }

        echo json_encode(['status' => 'success', 'data' => $items]);
        break;

    case 'get_analytics':
        try {
            // 1. DESCRIPTIVE: Valuations & Item Count
            $stmt1 = $pdo->query("SELECT SUM(quantity * unit_price) as total_value, COUNT(inventory_id) as total_items FROM inventory_items");
            $inv_stats = $stmt1->fetch(PDO::FETCH_ASSOC);

            // 2. DIAGNOSTIC: Total Spend & Turnover
            $stmt4 = $pdo->query("SELECT SUM(total_amount) as total_spend FROM purchase_orders WHERE status = 'Received'");
            $po_stats = $stmt4->fetch(PDO::FETCH_ASSOC);

            $total_value = $inv_stats['total_value'] ?? 0;
            $total_spend = $po_stats['total_spend'] ?? 0;
            $turnover_ratio = ($total_value > 0) ? ($total_spend / $total_value) : 0;

            // Strict-Mode Safe: Added s.name to GROUP BY
            $stmt5 = $pdo->query("
                SELECT s.name as category_name, SUM(po.total_amount) as category_spend 
                FROM purchase_orders po 
                JOIN suppliers s ON po.supplier_id = s.supplier_id 
                WHERE po.status = 'Received'
                GROUP BY s.supplier_id, s.name
            ");
            $spend_by_category = $stmt5->fetchAll(PDO::FETCH_ASSOC);

            // Strict-Mode Safe: Added s.name to GROUP BY
            $stmt6 = $pdo->query("
                SELECT s.name as supplier_name, SUM(i.quantity * i.unit_price) as supplier_value 
                FROM inventory_items i 
                JOIN suppliers s ON i.supplier_id = s.supplier_id 
                GROUP BY s.supplier_id, s.name
            ");
            $valuation_data = $stmt6->fetchAll(PDO::FETCH_ASSOC);

            // 3. PREDICTIVE: Calculate Burn Rates (100% Strict-Mode Safe)
            $stmtPredict = $pdo->query("
                SELECT 
                    i.inventory_id,
                    i.name, 
                    i.quantity, 
                    (COALESCE(SUM(t.qty_issued), 0) / 30) as daily_burn
                FROM inventory_items i
                LEFT JOIN inventory_transactions t ON i.inventory_id = t.inventory_id 
                    AND t.transaction_date >= DATE_SUB(NOW(), INTERVAL 30 DAY)
                GROUP BY i.inventory_id, i.name, i.quantity
                HAVING (COALESCE(SUM(t.qty_issued), 0) / 30) > 0 
                   AND (i.quantity / (COALESCE(SUM(t.qty_issued), 0) / 30)) <= 7
                ORDER BY (i.quantity / (COALESCE(SUM(t.qty_issued), 0) / 30)) ASC
                LIMIT 5
            ");
            $predictive_data = $stmtPredict->fetchAll(PDO::FETCH_ASSOC);

            $issuedStmt = $pdo->query("
                SELECT COALESCE(SUM(qty_issued), 0) as total_issued 
                FROM inventory_transactions 
                WHERE transaction_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
            ");
            $issued_30_days = $issuedStmt->fetch(PDO::FETCH_ASSOC)['total_issued'];

            // Calculate explicit days for the JSON payload
            foreach($predictive_data as &$pred) {
                $pred['days_left'] = floor($pred['quantity'] / $pred['daily_burn']);
            }

            // 4. PRESCRIPTIVE: Find exact number of low stock items
            $stmt2 = $pdo->query("SELECT COUNT(inventory_id) as low_stock FROM inventory_items WHERE quantity <= reorder_level");
            $shortages = $stmt2->fetch(PDO::FETCH_ASSOC);

            // Send perfect JSON back to the browser
            echo json_encode([
                'status' => 'success', 
                'data' => [
                    'total_value' => $total_value,
                    'total_items' => $inv_stats['total_items'] ?? 0,
                    'low_stock' => $shortages['low_stock'] ?? 0,
                    'total_spend' => $total_spend,
                    'turnover_ratio' => round($turnover_ratio, 2),
                    'issued_30_days' => $issued_30_days,
                    'spend_by_category' => $spend_by_category,
                    'valuation_data' => $valuation_data,
                    'predictions' => $predictive_data
                ]
            ]);

        } catch (PDOException $e) {
            // 🔥 THE SAFETY NET: If the database fails, it tells us EXACTLY why instead of a 500 error!
            echo json_encode(['status' => 'error', 'message' => 'Database Error: ' . $e->getMessage()]);
        } catch (Exception $e) {
            echo json_encode(['status' => 'error', 'message' => 'System Error: ' . $e->getMessage()]);
        }
        break;

    default:
        echo json_encode(['status' => 'error', 'message' => 'Invalid action']);
        break;
}
?>