<?php
/**
 * Update ingredient price and automatically recalculate affected recipes
 * 
 * @param PDO $pdo Database connection
 * @param int $ingredient_id Ingredient ID
 * @param float $new_price New price
 * @param int $user_id User making the change
 * @return array Result with success status and affected recipes count
 */
function updateIngredientPriceWithRecalculation($pdo, $ingredient_id, $new_price, $user_id) {
    // Get old price and ingredient details
    $stmt = $pdo->prepare("SELECT price, name FROM ingredients WHERE id = ?");
    $stmt->execute([$ingredient_id]);
    $ingredient = $stmt->fetch(PDO::FETCH_ASSOC);
    
    if (!$ingredient) {
        return ['success' => false, 'error' => 'Ingredient not found'];
    }
    
    $old_price = floatval($ingredient['price']);
    $ingredient_name = $ingredient['name'];
    
    // Check if price actually changed
    if (abs($old_price - $new_price) < 0.001) {
        return ['success' => true, 'updated_recipes' => 0, 'message' => 'Price unchanged'];
    }
    
    $pdo->beginTransaction();
    
    try {
        // 1. Update ingredient price
        $stmt = $pdo->prepare("UPDATE ingredients SET price = ? WHERE id = ?");
        $stmt->execute([$new_price, $ingredient_id]);
        
        // 2. Log ingredient price change
        $stmt = $pdo->prepare("
            INSERT INTO ingredient_price_history 
            (ingredient_id, ingredient_name, old_price, new_price, changed_by, changed_at) 
            VALUES (?, ?, ?, ?, ?, NOW())
        ");
        $stmt->execute([$ingredient_id, $ingredient_name, $old_price, $new_price, $user_id]);
        
        // 3. Find all affected recipes
        $stmt = $pdo->prepare("SELECT DISTINCT recipe_id FROM recipe_ingredients WHERE ingredient_id = ?");
        $stmt->execute([$ingredient_id]);
        $affected_recipes = $stmt->fetchAll(PDO::FETCH_COLUMN);
        
        $updated_recipes = 0;
        
        // 4. Update each affected recipe
        foreach ($affected_recipes as $recipe_id) {
            // Get quantity for this recipe
            $stmt = $pdo->prepare("SELECT quantity FROM recipe_ingredients WHERE ingredient_id = ? AND recipe_id = ?");
            $stmt->execute([$ingredient_id, $recipe_id]);
            $quantity = $stmt->fetchColumn();
            
            if (!$quantity) continue;
            
            $new_line_cost = $quantity * $new_price;
            
            // Update recipe ingredient
            $stmt = $pdo->prepare("
                UPDATE recipe_ingredients 
                SET unit_cost = ?, line_cost = ? 
                WHERE ingredient_id = ? AND recipe_id = ?
            ");
            $stmt->execute([$new_price, $new_line_cost, $ingredient_id, $recipe_id]);
            
            // Log recipe price change
            $stmt = $pdo->prepare("
                INSERT INTO recipe_price_history 
                (recipe_id, ingredient_id, ingredient_name, old_price, new_price, changed_by, changed_at) 
                VALUES (?, ?, ?, ?, ?, ?, NOW())
            ");
            $stmt->execute([$recipe_id, $ingredient_id, $ingredient_name, $old_price, $new_price, $user_id]);
            
            // Recalculate total recipe cost
            $stmt = $pdo->prepare("SELECT SUM(line_cost) as total_cost FROM recipe_ingredients WHERE recipe_id = ?");
            $stmt->execute([$recipe_id]);
            $total_cost = $stmt->fetchColumn();
            
            // Get portions
            $stmt = $pdo->prepare("SELECT portions FROM recipe_costings WHERE id = ?");
            $stmt->execute([$recipe_id]);
            $portions = $stmt->fetchColumn();
            $cost_per_portion = $portions > 0 ? $total_cost / $portions : 0;
            
            // Update recipe
            $stmt = $pdo->prepare("UPDATE recipe_costings SET total_cost = ?, cost_per_portion = ? WHERE id = ?");
            $stmt->execute([$total_cost, $cost_per_portion, $recipe_id]);
            
            $updated_recipes++;
        }
        
        $pdo->commit();
        
        return [
            'success' => true,
            'updated_recipes' => $updated_recipes,
            'message' => "Ingredient price updated. $updated_recipes recipe(s) automatically recalculated."
        ];
        
    } catch (Exception $e) {
        $pdo->rollBack();
        return ['success' => false, 'error' => 'Database error: ' . $e->getMessage()];
    }
}
?>