<?php
/**
 * Initialize Sample Recipes and Ingredients Data
 * This script populates the database with sample recipes linked to existing menu items
 */

// Database connection
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "granny_restaurant";

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
    die(json_encode([
        'success' => false,
        'message' => 'Connection failed: ' . $conn->connect_error
    ]));
}

try {
    // Get all menu items
    $sql = "SELECT id, item_name, price FROM menu_items LIMIT 20";
    $stmt = $conn->prepare($sql);
    
    if (!$stmt) {
        sendResponse(false, "Prepare failed: " . $conn->error);
        exit;
    }
    
    $stmt->execute();
    $result = $stmt->get_result();
    $menuItems = $result->fetch_all(MYSQLI_ASSOC);
    $stmt->close();

    if (empty($menuItems)) {
        sendResponse(false, "No menu items found");
        exit;
    }

    // Sample ingredients to create
    $sampleIngredients = [
        ['name' => 'Tomato', 'description' => 'Fresh red tomatoes', 'unit' => 'kg', 'unit_cost' => 40, 'current_stock' => 50, 'minimum_stock' => 10, 'supplier' => 'Local Market', 'status' => 'active'],
        ['name' => 'Olive Oil', 'description' => 'Premium olive oil', 'unit' => 'liter', 'unit_cost' => 300, 'current_stock' => 20, 'minimum_stock' => 5, 'supplier' => 'Import Store', 'status' => 'active'],
        ['name' => 'Garlic', 'description' => 'Fresh garlic cloves', 'unit' => 'kg', 'unit_cost' => 80, 'current_stock' => 15, 'minimum_stock' => 5, 'supplier' => 'Local Market', 'status' => 'active'],
        ['name' => 'Onion', 'description' => 'Yellow onions', 'unit' => 'kg', 'unit_cost' => 30, 'current_stock' => 40, 'minimum_stock' => 10, 'supplier' => 'Local Market', 'status' => 'active'],
        ['name' => 'Paneer (Indian Cottage Cheese)', 'description' => 'Fresh paneer', 'unit' => 'kg', 'unit_cost' => 250, 'current_stock' => 10, 'minimum_stock' => 3, 'supplier' => 'Daily Dairy', 'status' => 'active'],
        ['name' => 'Chicken Breast', 'description' => 'Boneless chicken breast', 'unit' => 'kg', 'unit_cost' => 180, 'current_stock' => 15, 'minimum_stock' => 5, 'supplier' => 'Meat Market', 'status' => 'active'],
        ['name' => 'Mutton', 'description' => 'Fresh mutton pieces', 'unit' => 'kg', 'unit_cost' => 350, 'current_stock' => 8, 'minimum_stock' => 3, 'supplier' => 'Meat Market', 'status' => 'active'],
        ['name' => 'Fish', 'description' => 'Fresh fish fillets', 'unit' => 'kg', 'unit_cost' => 300, 'current_stock' => 12, 'minimum_stock' => 4, 'supplier' => 'Fish Market', 'status' => 'active'],
        ['name' => 'Rice', 'description' => 'Basmati rice', 'unit' => 'kg', 'unit_cost' => 60, 'current_stock' => 100, 'minimum_stock' => 20, 'supplier' => 'Grain Supplier', 'status' => 'active'],
        ['name' => 'Flour', 'description' => 'Wheat flour', 'unit' => 'kg', 'unit_cost' => 25, 'current_stock' => 50, 'minimum_stock' => 15, 'supplier' => 'Grain Supplier', 'status' => 'active'],
        ['name' => 'Milk', 'description' => 'Fresh milk', 'unit' => 'liter', 'unit_cost' => 40, 'current_stock' => 30, 'minimum_stock' => 10, 'supplier' => 'Dairy Farm', 'status' => 'active'],
        ['name' => 'Cream', 'description' => 'Heavy cream', 'unit' => 'liter', 'unit_cost' => 120, 'current_stock' => 5, 'minimum_stock' => 2, 'supplier' => 'Dairy Farm', 'status' => 'active'],
        ['name' => 'Yogurt', 'description' => 'Plain yogurt', 'unit' => 'kg', 'unit_cost' => 80, 'current_stock' => 12, 'minimum_stock' => 4, 'supplier' => 'Dairy Farm', 'status' => 'active'],
        ['name' => 'Butter', 'description' => 'Unsalted butter', 'unit' => 'kg', 'unit_cost' => 350, 'current_stock' => 8, 'minimum_stock' => 3, 'supplier' => 'Dairy Farm', 'status' => 'active'],
        ['name' => 'Ghee (Clarified Butter)', 'description' => 'Pure ghee', 'unit' => 'liter', 'unit_cost' => 450, 'current_stock' => 5, 'minimum_stock' => 2, 'supplier' => 'Dairy Farm', 'status' => 'active'],
        ['name' => 'Cumin Seeds', 'description' => 'Dried cumin seeds', 'unit' => 'kg', 'unit_cost' => 200, 'current_stock' => 2, 'minimum_stock' => 1, 'supplier' => 'Spice Market', 'status' => 'active'],
        ['name' => 'Coriander Powder', 'description' => 'Ground coriander', 'unit' => 'kg', 'unit_cost' => 150, 'current_stock' => 3, 'minimum_stock' => 1, 'supplier' => 'Spice Market', 'status' => 'active'],
        ['name' => 'Turmeric Powder', 'description' => 'Ground turmeric', 'unit' => 'kg', 'unit_cost' => 100, 'current_stock' => 3, 'minimum_stock' => 1, 'supplier' => 'Spice Market', 'status' => 'active'],
        ['name' => 'Red Chili Powder', 'description' => 'Chili powder', 'unit' => 'kg', 'unit_cost' => 250, 'current_stock' => 2, 'minimum_stock' => 1, 'supplier' => 'Spice Market', 'status' => 'active'],
        ['name' => 'Salt', 'description' => 'Table salt', 'unit' => 'kg', 'unit_cost' => 15, 'current_stock' => 20, 'minimum_stock' => 5, 'supplier' => 'General Store', 'status' => 'active'],
        ['name' => 'Sugar', 'description' => 'Granulated sugar', 'unit' => 'kg', 'unit_cost' => 40, 'current_stock' => 25, 'minimum_stock' => 10, 'supplier' => 'General Store', 'status' => 'active'],
        ['name' => 'Ginger', 'description' => 'Fresh ginger', 'unit' => 'kg', 'unit_cost' => 60, 'current_stock' => 8, 'minimum_stock' => 2, 'supplier' => 'Local Market', 'status' => 'active'],
        ['name' => 'Green Chili', 'description' => 'Fresh green chili', 'unit' => 'kg', 'unit_cost' => 80, 'current_stock' => 5, 'minimum_stock' => 2, 'supplier' => 'Local Market', 'status' => 'active'],
        ['name' => 'Cilantro (Coriander Leaves)', 'description' => 'Fresh cilantro', 'unit' => 'kg', 'unit_cost' => 50, 'current_stock' => 3, 'minimum_stock' => 1, 'supplier' => 'Local Market', 'status' => 'active'],
        ['name' => 'Mint Leaves', 'description' => 'Fresh mint', 'unit' => 'kg', 'unit_cost' => 40, 'current_stock' => 2, 'minimum_stock' => 1, 'supplier' => 'Local Market', 'status' => 'active'],
        ['name' => 'Lemon', 'description' => 'Fresh lemons', 'unit' => 'kg', 'unit_cost' => 60, 'current_stock' => 10, 'minimum_stock' => 3, 'supplier' => 'Local Market', 'status' => 'active'],
        ['name' => 'Bell Pepper', 'description' => 'Colored bell peppers', 'unit' => 'kg', 'unit_cost' => 100, 'current_stock' => 8, 'minimum_stock' => 2, 'supplier' => 'Local Market', 'status' => 'active'],
        ['name' => 'Cucumber', 'description' => 'Fresh cucumbers', 'unit' => 'kg', 'unit_cost' => 40, 'current_stock' => 12, 'minimum_stock' => 3, 'supplier' => 'Local Market', 'status' => 'active'],
    ];

    // Insert ingredients
    $createdIngredients = [];
    foreach ($sampleIngredients as $ing) {
        $sql = "INSERT IGNORE INTO ingredients (name, description, unit, unit_cost, current_stock, minimum_stock, supplier, status) 
                VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
        $stmt = $conn->prepare($sql);
        $stmt->bind_param('sssddiss', 
            $ing['name'], 
            $ing['description'], 
            $ing['unit'], 
            $ing['unit_cost'],
            $ing['current_stock'],
            $ing['minimum_stock'],
            $ing['supplier'],
            $ing['status']
        );
        $stmt->execute();
        $stmt->close();

        // Get the ingredient ID
        $sql = "SELECT id FROM ingredients WHERE name = ?";
        $stmt = $conn->prepare($sql);
        $stmt->bind_param('s', $ing['name']);
        $stmt->execute();
        $result = $stmt->get_result();
        if ($row = $result->fetch_assoc()) {
            $createdIngredients[$ing['name']] = $row['id'];
        }
        $stmt->close();
    }

    // Sample recipes mapped to menu item types
    $sampleRecipes = [
        // Paneer dishes
        'paneer' => [
            'name' => 'Paneer Tikka Masala',
            'preparation_time' => 30,
            'difficulty_level' => 'medium',
            'yield_quantity' => 4,
            'chef_notes' => 'Marinate paneer for at least 1 hour. Cook masala sauce separately. Combine before serving.',
            'ingredients' => [
                ['name' => 'Paneer (Indian Cottage Cheese)', 'quantity' => 500, 'unit' => 'g'],
                ['name' => 'Yogurt', 'quantity' => 0.5, 'unit' => 'kg'],
                ['name' => 'Tomato', 'quantity' => 0.5, 'unit' => 'kg'],
                ['name' => 'Onion', 'quantity' => 0.3, 'unit' => 'kg'],
                ['name' => 'Cream', 'quantity' => 0.2, 'unit' => 'liter'],
                ['name' => 'Garlic', 'quantity' => 0.05, 'unit' => 'kg'],
                ['name' => 'Ginger', 'quantity' => 0.05, 'unit' => 'kg'],
                ['name' => 'Red Chili Powder', 'quantity' => 0.02, 'unit' => 'kg'],
                ['name' => 'Turmeric Powder', 'quantity' => 0.01, 'unit' => 'kg'],
                ['name' => 'Coriander Powder', 'quantity' => 0.02, 'unit' => 'kg'],
                ['name' => 'Salt', 'quantity' => 0.01, 'unit' => 'kg'],
                ['name' => 'Ghee (Clarified Butter)', 'quantity' => 0.1, 'unit' => 'liter'],
                ['name' => 'Cilantro (Coriander Leaves)', 'quantity' => 0.02, 'unit' => 'kg'],
            ]
        ],
        // Chicken dishes
        'chicken' => [
            'name' => 'Chicken Butter Masala',
            'preparation_time' => 35,
            'difficulty_level' => 'medium',
            'yield_quantity' => 4,
            'chef_notes' => 'Marinate chicken for 30 minutes. Cook in tomato-cream sauce. Garnish with cilantro.',
            'ingredients' => [
                ['name' => 'Chicken Breast', 'quantity' => 600, 'unit' => 'g'],
                ['name' => 'Tomato', 'quantity' => 0.6, 'unit' => 'kg'],
                ['name' => 'Onion', 'quantity' => 0.4, 'unit' => 'kg'],
                ['name' => 'Cream', 'quantity' => 0.3, 'unit' => 'liter'],
                ['name' => 'Butter', 'quantity' => 0.1, 'unit' => 'kg'],
                ['name' => 'Garlic', 'quantity' => 0.06, 'unit' => 'kg'],
                ['name' => 'Ginger', 'quantity' => 0.06, 'unit' => 'kg'],
                ['name' => 'Red Chili Powder', 'quantity' => 0.015, 'unit' => 'kg'],
                ['name' => 'Turmeric Powder', 'quantity' => 0.01, 'unit' => 'kg'],
                ['name' => 'Coriander Powder', 'quantity' => 0.03, 'unit' => 'kg'],
                ['name' => 'Salt', 'quantity' => 0.015, 'unit' => 'kg'],
                ['name' => 'Cilantro (Coriander Leaves)', 'quantity' => 0.03, 'unit' => 'kg'],
            ]
        ],
        // Biryani
        'biryani' => [
            'name' => 'Chicken Biryani',
            'preparation_time' => 45,
            'difficulty_level' => 'hard',
            'yield_quantity' => 4,
            'chef_notes' => 'Layer rice and marinated chicken. Cook on high heat for 3 min, then low heat for 25 min. Dum pukht style.',
            'ingredients' => [
                ['name' => 'Rice', 'quantity' => 1, 'unit' => 'kg'],
                ['name' => 'Chicken Breast', 'quantity' => 800, 'unit' => 'g'],
                ['name' => 'Yogurt', 'quantity' => 0.5, 'unit' => 'kg'],
                ['name' => 'Onion', 'quantity' => 0.6, 'unit' => 'kg'],
                ['name' => 'Garlic', 'quantity' => 0.08, 'unit' => 'kg'],
                ['name' => 'Ginger', 'quantity' => 0.08, 'unit' => 'kg'],
                ['name' => 'Green Chili', 'quantity' => 0.04, 'unit' => 'kg'],
                ['name' => 'Mint Leaves', 'quantity' => 0.05, 'unit' => 'kg'],
                ['name' => 'Cilantro (Coriander Leaves)', 'quantity' => 0.05, 'unit' => 'kg'],
                ['name' => 'Ghee (Clarified Butter)', 'quantity' => 0.15, 'unit' => 'liter'],
                ['name' => 'Red Chili Powder', 'quantity' => 0.02, 'unit' => 'kg'],
                ['name' => 'Turmeric Powder', 'quantity' => 0.01, 'unit' => 'kg'],
                ['name' => 'Coriander Powder', 'quantity' => 0.03, 'unit' => 'kg'],
                ['name' => 'Salt', 'quantity' => 0.02, 'unit' => 'kg'],
            ]
        ],
        // Tandoori
        'tandoori' => [
            'name' => 'Tandoori Chicken',
            'preparation_time' => 40,
            'difficulty_level' => 'easy',
            'yield_quantity' => 4,
            'chef_notes' => 'Marinate chicken in yogurt and spices for minimum 2 hours. Grill in tandoor at high temperature.',
            'ingredients' => [
                ['name' => 'Chicken Breast', 'quantity' => 800, 'unit' => 'g'],
                ['name' => 'Yogurt', 'quantity' => 0.5, 'unit' => 'kg'],
                ['name' => 'Garlic', 'quantity' => 0.08, 'unit' => 'kg'],
                ['name' => 'Ginger', 'quantity' => 0.08, 'unit' => 'kg'],
                ['name' => 'Green Chili', 'quantity' => 0.03, 'unit' => 'kg'],
                ['name' => 'Lemon', 'quantity' => 0.2, 'unit' => 'kg'],
                ['name' => 'Red Chili Powder', 'quantity' => 0.02, 'unit' => 'kg'],
                ['name' => 'Turmeric Powder', 'quantity' => 0.015, 'unit' => 'kg'],
                ['name' => 'Coriander Powder', 'quantity' => 0.03, 'unit' => 'kg'],
                ['name' => 'Salt', 'quantity' => 0.015, 'unit' => 'kg'],
                ['name' => 'Olive Oil', 'quantity' => 0.05, 'unit' => 'liter'],
            ]
        ],
    ];

    $createdCount = 0;

    // Create recipes for menu items
    foreach ($menuItems as $menu) {
        $menuName = strtolower($menu['item_name']);
        
        // Determine recipe type based on menu item name with flexible matching
        $recipeType = null;
        
        // Check for specific recipe types with proper matching (order matters - check most specific first)
        if (stripos($menuName, 'biryani') !== false || stripos($menuName, 'briyani') !== false) {
            $recipeType = 'biryani';
        } elseif (stripos($menuName, 'tandoori') !== false) {
            $recipeType = 'tandoori';
        } elseif (stripos($menuName, 'paneer') !== false || stripos($menuName, 'cottage') !== false) {
            $recipeType = 'paneer';
        } elseif (stripos($menuName, 'chicken') !== false) {
            $recipeType = 'chicken';
        }

        // Skip if no matching recipe type
        if (!$recipeType || !isset($sampleRecipes[$recipeType])) {
            continue;
        }

        $recipe = $sampleRecipes[$recipeType];

        // Check if recipe already exists for this menu item
        $sql = "SELECT id FROM recipes WHERE menu_item_id = ?";
        $stmt = $conn->prepare($sql);
        $stmt->bind_param('i', $menu['id']);
        $stmt->execute();
        if ($stmt->get_result()->num_rows > 0) {
            $stmt->close();
            continue; // Skip if recipe already exists
        }
        $stmt->close();

        // Insert recipe
        $sql = "INSERT INTO recipes (menu_item_id, name, preparation_time, difficulty_level, yield_quantity, chef_notes) 
                VALUES (?, ?, ?, ?, ?, ?)";
        $stmt = $conn->prepare($sql);
        $stmt->bind_param('isisss', 
            $menu['id'],
            $recipe['name'],
            $recipe['preparation_time'],
            $recipe['difficulty_level'],
            $recipe['yield_quantity'],
            $recipe['chef_notes']
        );

        if ($stmt->execute()) {
            $recipeId = $stmt->insert_id;
            $stmt->close();

            // Insert recipe ingredients
            foreach ($recipe['ingredients'] as $ing) {
                if (!isset($createdIngredients[$ing['name']])) {
                    continue; // Skip if ingredient not found
                }

                $ingId = $createdIngredients[$ing['name']];
                $sql = "INSERT IGNORE INTO recipe_ingredients (recipe_id, ingredient_id, quantity_required, unit) 
                        VALUES (?, ?, ?, ?)";
                $stmt = $conn->prepare($sql);
                $stmt->bind_param('iids', $recipeId, $ingId, $ing['quantity'], $ing['unit']);
                $stmt->execute();
                $stmt->close();
            }

            $createdCount++;
        } else {
            $stmt->close();
        }
    }

    sendResponse(true, "Sample data created successfully", [
        'ingredients_created' => count($createdIngredients),
        'recipes_created' => $createdCount,
        'menu_items_total' => count($menuItems)
    ]);

} catch (Exception $e) {
    sendResponse(false, "Error: " . $e->getMessage());
}

function sendResponse($success, $message, $data = null) {
    $response = [
        'success' => $success,
        'message' => $message
    ];
    if ($data !== null) {
        $response['data'] = $data;
    }
    echo json_encode($response) . "\n";
}
