<?php
/**
 * Smart Reorder (AI-assisted) API
 * Provides reorder suggestions using stock, consumption history, supplier lead time, and supplier pricing.
 */

session_start();
header('Content-Type: application/json');
require_once 'config.php';

if (!isset($_SESSION['purchase_manager_id'])) {
    http_response_code(401);
    echo json_encode(['success' => false, 'message' => 'Unauthorized']);
    exit;
}

$pm_id = (int)$_SESSION['purchase_manager_id'];
$action = isset($_GET['action']) ? trim((string)$_GET['action']) : '';

if ($action !== 'get_suggestions') {
    echo json_encode(['success' => false, 'message' => 'Invalid action']);
    exit;
}

getSmartReorderSuggestions($conn, $pm_id);

function tableExists(mysqli $conn, string $tableName): bool
{
    $safe = $conn->real_escape_string($tableName);
    $res = $conn->query("SHOW TABLES LIKE '{$safe}'");
    return $res && $res->num_rows > 0;
}

function roundQty(float $value): float
{
    return round(max(0, $value), 3);
}

function getDirectUsageForWindow(mysqli $conn, int $ingredientId, int $days, bool $hasInventoryUpdates, bool $hasWastage): float
{
    $usage = 0.0;

    if ($hasInventoryUpdates) {
        $usageStmt = $conn->prepare("
            SELECT COALESCE(SUM(ABS(quantity_changed)), 0) AS total_used
            FROM inventory_updates
            WHERE ingredient_id = ?
              AND created_at >= DATE_SUB(NOW(), INTERVAL ? DAY)
              AND (
                  LOWER(action) IN ('subtract', 'consume', 'consumed', 'issued', 'issue', 'wastage', 'waste', 'spoilage', 'expiry')
                  OR (LOWER(action) = 'adjust' AND quantity_changed < 0)
              )
        ");
        if ($usageStmt) {
            $usageStmt->bind_param('ii', $ingredientId, $days);
            $usageStmt->execute();
            $row = $usageStmt->get_result()->fetch_assoc();
            $usage += (float)($row['total_used'] ?? 0);
            $usageStmt->close();
        }
    }

    if ($hasWastage) {
        $wastageStmt = $conn->prepare("
            SELECT COALESCE(SUM(quantity), 0) AS total_wastage
            FROM expiry_wastage_tracking
            WHERE ingredient_id = ?
              AND wastage_date >= DATE_SUB(CURDATE(), INTERVAL ? DAY)
        ");
        if ($wastageStmt) {
            $wastageStmt->bind_param('ii', $ingredientId, $days);
            $wastageStmt->execute();
            $row = $wastageStmt->get_result()->fetch_assoc();
            $usage += (float)($row['total_wastage'] ?? 0);
            $wastageStmt->close();
        }
    }

    return max(0.0, $usage);
}

function getRecipeUsageForWindow(mysqli $conn, int $ingredientId, int $days, bool $hasRecipePipeline): float
{
    if (!$hasRecipePipeline) {
        return 0.0;
    }

    // Estimated ingredient usage from dine-in order items x recipe ingredient quantities.
    $sql = "
        SELECT COALESCE(SUM(doi.quantity * ri.quantity_required), 0) AS recipe_used
        FROM dine_in_order_items doi
        INNER JOIN dine_in_orders dio ON dio.id = doi.dine_in_order_id
        INNER JOIN recipes r ON r.menu_item_id = doi.item_id
        INNER JOIN recipe_ingredients ri ON ri.recipe_id = r.id
        WHERE ri.ingredient_id = ?
          AND dio.created_at >= DATE_SUB(NOW(), INTERVAL ? DAY)
          AND COALESCE(dio.status, 'pending') <> 'cancelled'
    ";
    $stmt = $conn->prepare($sql);
    if (!$stmt) {
        return 0.0;
    }
    $stmt->bind_param('ii', $ingredientId, $days);
    $stmt->execute();
    $row = $stmt->get_result()->fetch_assoc();
    $stmt->close();
    return max(0.0, (float)($row['recipe_used'] ?? 0));
}

function getPeakDayFactor(mysqli $conn, int $ingredientId, bool $hasInventoryUpdates): float
{
    if (!$hasInventoryUpdates) {
        return 1.0;
    }

    $sql = "
        SELECT
            DAYOFWEEK(created_at) AS dow,
            COALESCE(SUM(ABS(quantity_changed)), 0) AS total_used
        FROM inventory_updates
        WHERE ingredient_id = ?
          AND created_at >= DATE_SUB(NOW(), INTERVAL 56 DAY)
          AND (
              LOWER(action) IN ('subtract', 'consume', 'consumed', 'issued', 'issue', 'wastage', 'waste', 'spoilage', 'expiry')
              OR (LOWER(action) = 'adjust' AND quantity_changed < 0)
          )
        GROUP BY DAYOFWEEK(created_at)
    ";
    $stmt = $conn->prepare($sql);
    if (!$stmt) {
        return 1.0;
    }
    $stmt->bind_param('i', $ingredientId);
    $stmt->execute();
    $result = $stmt->get_result();

    $sum = 0.0;
    $count = 0;
    $maxVal = 0.0;
    while ($row = $result->fetch_assoc()) {
        $v = (float)($row['total_used'] ?? 0);
        if ($v <= 0) {
            continue;
        }
        $sum += $v;
        $count++;
        if ($v > $maxVal) {
            $maxVal = $v;
        }
    }
    $stmt->close();

    if ($count <= 1 || $sum <= 0) {
        return 1.0;
    }

    $avg = $sum / $count;
    if ($avg <= 0) {
        return 1.0;
    }

    // Keep the peak influence bounded (no extreme swings).
    $rawRatio = $maxVal / $avg;
    $boundedRatio = min(max($rawRatio, 1.0), 1.35);
    return $boundedRatio;
}

function normalizeSupplierKey(string $value): string
{
    $value = strtolower(trim($value));
    // Keep only letters and numbers for robust matching.
    $value = preg_replace('/[^a-z0-9]+/', '', $value);
    return $value ?? '';
}

function extractSupplierCandidates(string $rawSupplier): array
{
    if (trim($rawSupplier) === '') {
        return [];
    }

    $normalized = str_replace([';', '|', '/', '\\'], ',', $rawSupplier);
    $parts = array_map('trim', explode(',', $normalized));
    $parts = array_values(array_filter($parts, static function ($p) {
        return $p !== '';
    }));

    if (empty($parts)) {
        $parts = [trim($rawSupplier)];
    }

    $keys = [];
    foreach ($parts as $name) {
        $k = normalizeSupplierKey($name);
        if ($k !== '') {
            $keys[$k] = $name;
        }
    }
    return $keys;
}

function getSmartReorderSuggestions(mysqli $conn, int $pm_id): void
{
    $horizonDays = isset($_GET['horizon_days']) ? (int)$_GET['horizon_days'] : 14;
    if ($horizonDays < 7) $horizonDays = 7;
    if ($horizonDays > 60) $horizonDays = 60;

    $hasInventoryUpdates = tableExists($conn, 'inventory_updates');
    $hasWastage = tableExists($conn, 'expiry_wastage_tracking');
    $hasSupplierProducts = tableExists($conn, 'supplier_products');
    $hasRecipePipeline = tableExists($conn, 'dine_in_orders')
        && tableExists($conn, 'dine_in_order_items')
        && tableExists($conn, 'recipes')
        && tableExists($conn, 'recipe_ingredients');

    $ingredientsSql = "
        SELECT id, name, unit, current_stock, minimum_stock, unit_cost, supplier
        FROM ingredients
        WHERE status = 'active'
        ORDER BY name ASC
    ";
    $ingredientsRes = $conn->query($ingredientsSql);
    if (!$ingredientsRes) {
        echo json_encode(['success' => false, 'message' => 'Failed to load ingredients']);
        exit;
    }

    $suggestions = [];
    $totalSuggestedCost = 0.0;
    $criticalCount = 0;

    // Preload active suppliers for fallback text matching (ingredients.supplier).
    $supplierById = [];
    $supplierByNormName = [];
    $supStmt = $conn->prepare("
        SELECT id, name, COALESCE(lead_time_days, 0) AS lead_time_days
        FROM suppliers
        WHERE purchase_manager_id = ?
          AND COALESCE(status, 'active') = 'active'
    ");
    if ($supStmt) {
        $supStmt->bind_param('i', $pm_id);
        $supStmt->execute();
        $supRes = $supStmt->get_result();
        while ($row = $supRes->fetch_assoc()) {
            $sid = (int)$row['id'];
            $sname = (string)($row['name'] ?? '');
            $supplierById[$sid] = [
                'id' => $sid,
                'name' => $sname,
                'lead_time_days' => (int)$row['lead_time_days']
            ];
            $norm = normalizeSupplierKey($sname);
            if ($norm !== '' && !isset($supplierByNormName[$norm])) {
                $supplierByNormName[$norm] = $supplierById[$sid];
            }
        }
        $supStmt->close();
    }

    while ($ing = $ingredientsRes->fetch_assoc()) {
        $ingredientId = (int)$ing['id'];
        $currentStock = (float)$ing['current_stock'];
        $minimumStock = (float)$ing['minimum_stock'];
        $unitCost = (float)$ing['unit_cost'];

        $windows = [7, 14, 30];
        $usageDaily = [];
        $directDaily = [];
        $recipeDaily = [];

        foreach ($windows as $days) {
            $directUsed = getDirectUsageForWindow($conn, $ingredientId, $days, $hasInventoryUpdates, $hasWastage);
            $recipeUsed = getRecipeUsageForWindow($conn, $ingredientId, $days, $hasRecipePipeline);
            $directDaily[$days] = $directUsed / $days;
            $recipeDaily[$days] = $recipeUsed / $days;
            $usageDaily[$days] = $directDaily[$days] + $recipeDaily[$days];
        }

        // Weighted average that prioritizes recent behavior.
        $avgDailyUsage = ($usageDaily[7] * 0.5) + ($usageDaily[14] * 0.3) + ($usageDaily[30] * 0.2);

        // Peak day adjustment for restaurants with strong weekly spikes.
        $peakFactor = getPeakDayFactor($conn, $ingredientId, $hasInventoryUpdates);
        $avgDailyUsage *= $peakFactor;

        // Fallback when history is sparse.
        if ($avgDailyUsage <= 0) {
            $avgDailyUsage = max($minimumStock / 14, 0.05);
            $peakFactor = 1.0;
        }

        $bestSupplierId = null;
        $bestSupplierName = null;
        $bestUnitPrice = $unitCost;
        $leadTimeDays = 3;
        $minimumOrderQty = 1.0;

        if ($hasSupplierProducts) {
            $supplierStmt = $conn->prepare("
                SELECT
                    sp.supplier_id,
                    s.name AS supplier_name,
                    COALESCE(NULLIF(sp.unit_price, 0), ?) AS effective_unit_price,
                    GREATEST(COALESCE(sp.minimum_order_qty, 1), 1) AS minimum_order_qty,
                    GREATEST(COALESCE(NULLIF(sp.lead_time_days, 0), s.lead_time_days, 3), 1) AS effective_lead_time
                FROM supplier_products sp
                INNER JOIN suppliers s ON s.id = sp.supplier_id
                WHERE sp.ingredient_id = ?
                  AND s.purchase_manager_id = ?
                  AND COALESCE(s.status, 'active') = 'active'
                  AND COALESCE(sp.availability, 'available') <> 'discontinued'
                ORDER BY sp.is_preferred DESC, effective_unit_price ASC
                LIMIT 1
            ");
            if ($supplierStmt) {
                $supplierStmt->bind_param('dii', $unitCost, $ingredientId, $pm_id);
                $supplierStmt->execute();
                $supplierRow = $supplierStmt->get_result()->fetch_assoc();
                if ($supplierRow) {
                    $bestSupplierId = (int)$supplierRow['supplier_id'];
                    $bestSupplierName = (string)$supplierRow['supplier_name'];
                    $bestUnitPrice = (float)$supplierRow['effective_unit_price'];
                    $leadTimeDays = (int)$supplierRow['effective_lead_time'];
                    $minimumOrderQty = (float)$supplierRow['minimum_order_qty'];
                }
                $supplierStmt->close();
            }
        }

        // Fallback: match plain text ingredients.supplier to active suppliers.
        if ($bestSupplierId === null && !empty($ing['supplier']) && !empty($supplierByNormName)) {
            $candidateKeys = extractSupplierCandidates((string)$ing['supplier']);
            foreach ($candidateKeys as $norm => $originalLabel) {
                if (!isset($supplierByNormName[$norm])) {
                    continue;
                }
                $matchedSupplier = $supplierByNormName[$norm];
                $bestSupplierId = (int)$matchedSupplier['id'];
                $bestSupplierName = (string)$matchedSupplier['name'];
                $leadTimeDays = max(1, (int)$matchedSupplier['lead_time_days']);
                // Keep ingredient unit cost when no supplier-product price exists.
                $bestUnitPrice = $unitCost > 0 ? $unitCost : $bestUnitPrice;
                break;
            }
        }

        $reviewWindowDays = 3;
        $safetyStock = max($minimumStock * 0.35, $avgDailyUsage * max(1.5, $leadTimeDays * 0.5));
        $reorderPoint = ($avgDailyUsage * ($leadTimeDays + $reviewWindowDays)) + $safetyStock;
        $targetStock = ($avgDailyUsage * ($horizonDays + $leadTimeDays)) + $safetyStock;
        $rawSuggestion = $targetStock - $currentStock;

        $suggestedQty = 0.0;
        if ($rawSuggestion > 0.01 || $currentStock <= $minimumStock) {
            $suggestedQty = max($rawSuggestion, $minimumOrderQty, max($minimumStock - $currentStock, 0));
            if ($minimumOrderQty > 0) {
                $suggestedQty = ceil($suggestedQty / $minimumOrderQty) * $minimumOrderQty;
            }
            $suggestedQty = roundQty($suggestedQty);
        }

        $daysToStockout = $avgDailyUsage > 0 ? ($currentStock / $avgDailyUsage) : 999;
        $urgency = 'low';
        if ($currentStock <= $minimumStock || $daysToStockout <= $leadTimeDays) {
            $urgency = 'critical';
        } elseif ($daysToStockout <= ($leadTimeDays + 2)) {
            $urgency = 'high';
        } elseif ($daysToStockout <= ($leadTimeDays + 5)) {
            $urgency = 'medium';
        }

        if ($suggestedQty > 0) {
            $estimatedCost = round($suggestedQty * $bestUnitPrice, 2);
            $totalSuggestedCost += $estimatedCost;
            if ($urgency === 'critical') {
                $criticalCount++;
            }

            $suggestions[] = [
                'ingredient_id' => $ingredientId,
                'ingredient_name' => $ing['name'],
                'unit' => $ing['unit'],
                'current_stock' => roundQty($currentStock),
                'minimum_stock' => roundQty($minimumStock),
                'avg_daily_usage' => roundQty($avgDailyUsage),
                'usage_daily_7' => roundQty($usageDaily[7] ?? 0),
                'usage_daily_14' => roundQty($usageDaily[14] ?? 0),
                'usage_daily_30' => roundQty($usageDaily[30] ?? 0),
                'usage_direct_daily_30' => roundQty($directDaily[30] ?? 0),
                'usage_recipe_daily_30' => roundQty($recipeDaily[30] ?? 0),
                'peak_factor' => round($peakFactor, 2),
                'lead_time_days' => $leadTimeDays,
                'reorder_point' => roundQty($reorderPoint),
                'target_stock' => roundQty($targetStock),
                'suggested_order_qty' => $suggestedQty,
                'supplier_id' => $bestSupplierId,
                'supplier_name' => $bestSupplierName,
                'recommended_unit_price' => round($bestUnitPrice, 2),
                'estimated_cost' => $estimatedCost,
                'days_to_stockout' => round($daysToStockout, 1),
                'urgency' => $urgency,
                'reason' => sprintf(
                    'Usage %.2f/day (7d %.2f, 14d %.2f, 30d %.2f, peak x%.2f), lead time %d day(s), stockout in %.1f day(s).',
                    $avgDailyUsage,
                    ($usageDaily[7] ?? 0),
                    ($usageDaily[14] ?? 0),
                    ($usageDaily[30] ?? 0),
                    $peakFactor,
                    $leadTimeDays,
                    $daysToStockout
                )
            ];
        }
    }

    $urgencyOrder = ['critical' => 1, 'high' => 2, 'medium' => 3, 'low' => 4];
    usort($suggestions, function ($a, $b) use ($urgencyOrder) {
        $aRank = $urgencyOrder[$a['urgency']] ?? 99;
        $bRank = $urgencyOrder[$b['urgency']] ?? 99;
        if ($aRank === $bRank) {
            return ($b['estimated_cost'] <=> $a['estimated_cost']);
        }
        return $aRank <=> $bRank;
    });

    echo json_encode([
        'success' => true,
        'horizon_days' => $horizonDays,
        'generated_at' => date('Y-m-d H:i:s'),
        'summary' => [
            'total_items' => count($suggestions),
            'critical_items' => $criticalCount,
            'estimated_total_cost' => round($totalSuggestedCost, 2)
        ],
        'suggestions' => $suggestions
    ]);
}
