<?php
header('Content-Type: application/json');
require_once __DIR__ . '/../../includes/config.php';
require_once __DIR__ . '/../../includes/database.php';

$response = ['success' => false, 'data' => [], 'message' => ''];

// Function to update recipe costings when ingredient price changes
function updateRecipeCostingsForIngredient($pdo, $ingredientId, $oldPrice, $newPrice, $userId) {
    // Start transaction
    $pdo->beginTransaction();
    
    try {
        // Update recipe_ingredients for this ingredient
        $stmt = $pdo->prepare("
            UPDATE recipe_ingredients 
            SET unit_cost = ?, 
                line_cost = quantity * ? 
            WHERE ingredient_id = ?
        ");
        $stmt->execute([$newPrice, $newPrice, $ingredientId]);
        
        // Get all affected recipe IDs
        $stmt = $pdo->prepare("
            SELECT DISTINCT ri.recipe_id 
            FROM recipe_ingredients ri
            JOIN recipe_costings rc ON ri.recipe_id = rc.id
            WHERE ri.ingredient_id = ? AND rc.created_by = ?
        ");
        $stmt->execute([$ingredientId, $userId]);
        $affectedRecipes = $stmt->fetchAll(PDO::FETCH_COLUMN, 0);
        
        // Recalculate totals for each affected recipe
        foreach ($affectedRecipes as $recipeId) {
            // Get total cost for this recipe
            $stmt = $pdo->prepare("
                SELECT SUM(line_cost) as total_cost 
                FROM recipe_ingredients 
                WHERE recipe_id = ?
            ");
            $stmt->execute([$recipeId]);
            $result = $stmt->fetch(PDO::FETCH_ASSOC);
            $totalCost = floatval($result['total_cost'] ?? 0);
            
            // Get recipe portions
            $stmt = $pdo->prepare("SELECT portions FROM recipe_costings WHERE id = ?");
            $stmt->execute([$recipeId]);
            $recipe = $stmt->fetch(PDO::FETCH_ASSOC);
            $portions = intval($recipe['portions'] ?? 1);
            
            // Calculate new cost per portion
            $costPerPortion = $portions > 0 ? $totalCost / $portions : 0;
            
            // Update recipe costing
            $stmt = $pdo->prepare("UPDATE recipe_costings SET total_cost = ?, cost_per_portion = ? WHERE id = ?");
            $stmt->execute([$totalCost, $costPerPortion, $recipeId]);
            
            // Check if price_update_logs table exists, create if not
            try {
                $pdo->exec("CREATE TABLE IF NOT EXISTS price_update_logs (
                    id INT PRIMARY KEY AUTO_INCREMENT,
                    recipe_id INT NOT NULL,
                    ingredient_id INT NOT NULL,
                    old_price DECIMAL(10,2) NOT NULL,
                    new_price DECIMAL(10,2) NOT NULL,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    INDEX idx_recipe_id (recipe_id),
                    INDEX idx_ingredient_id (ingredient_id)
                )");
                
                // Add a price update log
                $stmt = $pdo->prepare("
                    INSERT INTO price_update_logs (recipe_id, ingredient_id, old_price, new_price, updated_at) 
                    VALUES (?, ?, ?, ?, NOW())
                ");
                $stmt->execute([$recipeId, $ingredientId, $oldPrice, $newPrice]);
            } catch (Exception $e) {
                error_log("Price update log error: " . $e->getMessage());
                // Continue without logging if table creation fails
            }
        }
        
        $pdo->commit();
        return count($affectedRecipes);
        
    } catch (Exception $e) {
        $pdo->rollBack();
        throw $e;
    }
}

try {
    if (!isset($_FILES['csv_file']) || $_FILES['csv_file']['error'] !== UPLOAD_ERR_OK) {
        throw new Exception('No file uploaded or upload error occurred');
    }
    
    $uploadedFile = $_FILES['csv_file'];
    $file = $uploadedFile['tmp_name'];
    
    if (!file_exists($file)) {
        throw new Exception('Uploaded file not found on server');
    }
    
    if ($uploadedFile['size'] > 5 * 1024 * 1024) {
        throw new Exception('File size too large. Maximum 5MB allowed.');
    }
    
    if (!is_readable($file)) {
        throw new Exception('Cannot read uploaded file');
    }
    
    // Get user ID from database session (same as users.php)
    $user_id = null;
    $auth_token = $_COOKIE['rcp_auth'] ?? null;
    
    if ($auth_token) {
        $pdo = Database::getInstance()->getConnection();
        
        $stmt = $pdo->prepare("SELECT session_data FROM user_sessions WHERE session_id = ? AND expires_at > NOW()");
        $stmt->execute([$auth_token]);
        $session_data = $stmt->fetchColumn();
        
        if ($session_data) {
            $user = json_decode($session_data, true);
            $user_id = $user['user_id'] ?? null;
        }
    }
    
    if (!$user_id) {
        throw new Exception('User not authenticated');
    }
    
    // Read CSV file
    $handle = fopen($file, 'r');
    if (!$handle) {
        throw new Exception('Cannot open CSV file for reading');
    }
    
    $headers = fgetcsv($handle);
    if ($headers === FALSE || count($headers) === 0) {
        fclose($handle);
        throw new Exception('Empty CSV file or invalid format');
    }
    
    $headers = array_map('strtolower', array_map('trim', $headers));
    $expectedHeaders = ['name', 'unit', 'price', 'category', 'supplier_info'];
    
    $missingHeaders = array_diff($expectedHeaders, $headers);
    if (!empty($missingHeaders)) {
        fclose($handle);
        throw new Exception('Missing CSV headers: ' . implode(', ', $missingHeaders));
    }
    
    $addedCount = 0;
    $updatedCount = 0;
    $recipeUpdates = 0;
    $errors = [];
    $rowNumber = 1;
    
    while (($data = fgetcsv($handle)) !== FALSE) {
        $rowNumber++;
        
        if ($data === null || count(array_filter($data)) === 0) {
            continue;
        }
        
        if (count($data) < count($expectedHeaders)) {
            $errors[] = ['row' => $rowNumber, 'message' => 'Insufficient columns in row'];
            continue;
        }
        
        $ingredientData = [];
        foreach ($expectedHeaders as $index => $header) {
            if (isset($headers[$index]) && isset($data[$index])) {
                $ingredientData[$header] = trim($data[$index]);
            } else {
                $ingredientData[$header] = '';
            }
        }
        
        $required = ['name', 'unit', 'price'];
        foreach ($required as $field) {
            if (empty($ingredientData[$field])) {
                $errors[] = ['row' => $rowNumber, 'message' => "Missing required field: $field"];
                continue 2;
            }
        }
        
        if (!is_numeric($ingredientData['price']) || floatval($ingredientData['price']) < 0) {
            $errors[] = ['row' => $rowNumber, 'message' => "Invalid price: " . $ingredientData['price']];
            continue;
        }
        
        $price = floatval($ingredientData['price']);
        
        // Check if ingredient exists for this user
        $checkStmt = $pdo->prepare("SELECT id, price FROM ingredients WHERE name = ? AND user_id = ?");
        $checkStmt->execute([$ingredientData['name'], $user_id]);
        $existing = $checkStmt->fetch();
        
        if ($existing) {
            // Check if price changed
            $oldPrice = floatval($existing['price']);
            $newPrice = $price;
            
            // Update existing ingredient including supplier_info
            $updateStmt = $pdo->prepare("UPDATE ingredients SET price = ?, unit = ?, category = ?, supplier_info = ? WHERE id = ?");
            $success = $updateStmt->execute([
                $newPrice,
                $ingredientData['unit'],
                $ingredientData['category'],
                $ingredientData['supplier_info'],
                $existing['id']
            ]);
            
            if ($success) {
                $updatedCount++;
                
                // If price changed, update all recipe costings
                if ($oldPrice != $newPrice) {
                    try {
                        $affectedRecipes = updateRecipeCostingsForIngredient($pdo, $existing['id'], $oldPrice, $newPrice, $user_id);
                        $recipeUpdates += $affectedRecipes;
                        error_log("Price updated for ingredient {$ingredientData['name']}: {$oldPrice} -> {$newPrice}, affected {$affectedRecipes} recipes");
                    } catch (Exception $e) {
                        error_log("Error updating recipe costings for ingredient {$existing['id']}: " . $e->getMessage());
                        // Don't fail the whole upload if recipe update fails
                    }
                }
            } else {
                $errors[] = ['row' => $rowNumber, 'message' => 'Failed to update existing ingredient'];
            }
        } else {
            // Insert new ingredient with supplier_info
            $insertStmt = $pdo->prepare("INSERT INTO ingredients (user_id, name, unit, price, category, supplier_info) 
                                       VALUES (?, ?, ?, ?, ?, ?)");
            
            try {
                $success = $insertStmt->execute([
                    $user_id,
                    $ingredientData['name'],
                    $ingredientData['unit'],
                    $price,
                    $ingredientData['category'],
                    $ingredientData['supplier_info']
                ]);
                
                if ($success) {
                    $addedCount++;
                } else {
                    $errors[] = ['row' => $rowNumber, 'message' => 'Failed to insert new ingredient'];
                }
            } catch (PDOException $e) {
                $errors[] = ['row' => $rowNumber, 'message' => 'Database error: ' . $e->getMessage()];
            }
        }
    }
    
    fclose($handle);
    
    $response['success'] = true;
    $response['data'] = [
        'added_count' => $addedCount,
        'updated_count' => $updatedCount,
        'recipe_updates' => $recipeUpdates,
        'errors' => $errors
    ];
    $response['message'] = "Bulk upload completed. Added: {$addedCount}, Updated: {$updatedCount}, Recipe Updates: {$recipeUpdates}, Errors: " . count($errors);
    
} catch (Exception $e) {
    $response['message'] = 'Error: ' . $e->getMessage();
}

echo json_encode($response);
exit;
?>