<?php
/**
 * Fast ingredient-to-supplier linker.
 * Strategy:
 * 1) Load supplier names for current purchase manager into a normalized lookup map.
 * 2) Scan ingredients once and derive supplier matches from ingredients.supplier tokens.
 * 3) Bulk INSERT IGNORE into supplier_products in chunks (no row-by-row exists checks).
 */

session_start();
include 'config.php';

header('Content-Type: application/json');

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'];
$started_at = microtime(true);

function normalize_name($value) {
    $value = trim((string)$value);
    if ($value === '') {
        return '';
    }
    $value = strtolower($value);
    $value = str_replace('&', 'and', $value);
    $value = preg_replace('/[.\-,]/', ' ', $value);
    $value = preg_replace('/\s+/', ' ', $value);
    return trim($value);
}

function split_supplier_tokens($supplierText) {
    $supplierText = trim((string)$supplierText);
    if ($supplierText === '') {
        return [];
    }
    $parts = preg_split('/[,\;|\/]+/', $supplierText);
    $tokens = [];
    foreach ($parts as $part) {
        $n = normalize_name($part);
        if ($n !== '') {
            $tokens[$n] = true;
        }
    }
    return array_keys($tokens);
}

try {
    $table_check = $conn->query("SHOW TABLES LIKE 'supplier_products'");
    if (!$table_check || $table_check->num_rows === 0) {
        echo json_encode([
            'success' => false,
            'message' => 'supplier_products table not found. Please run setup_purchase_manager_db.php first.'
        ]);
        exit;
    }

    $columns_result = $conn->query("SHOW COLUMNS FROM supplier_products");
    if (!$columns_result) {
        throw new Exception('Failed to read supplier_products columns: ' . $conn->error);
    }
    $available_columns = [];
    while ($col = $columns_result->fetch_assoc()) {
        $available_columns[$col['Field']] = true;
    }

    if (!isset($available_columns['supplier_id']) || !isset($available_columns['ingredient_id'])) {
        throw new Exception('supplier_products schema is missing required columns (supplier_id, ingredient_id)');
    }

    $supplier_stmt = $conn->prepare("
        SELECT id, name, lead_time_days
        FROM suppliers
        WHERE purchase_manager_id = ?
    ");
    if (!$supplier_stmt) {
        throw new Exception('Prepare failed (suppliers): ' . $conn->error);
    }
    $supplier_stmt->bind_param('i', $pm_id);
    if (!$supplier_stmt->execute()) {
        throw new Exception('Execute failed (suppliers): ' . $supplier_stmt->error);
    }
    $supplier_rows = $supplier_stmt->get_result();

    $supplier_map = [];
    while ($s = $supplier_rows->fetch_assoc()) {
        $key = normalize_name($s['name'] ?? '');
        if ($key === '') {
            continue;
        }
        if (!isset($supplier_map[$key])) {
            $supplier_map[$key] = [];
        }
        $supplier_map[$key][] = [
            'id' => (int)$s['id'],
            'name' => (string)$s['name'],
            'lead_time_days' => (int)($s['lead_time_days'] ?? 0)
        ];
    }

    if (empty($supplier_map)) {
        echo json_encode([
            'success' => true,
            'linked_count' => 0,
            'skipped_count' => 0,
            'results' => [],
            'skipped' => ['No suppliers found for this purchase manager'],
            'elapsed_ms' => (int)round((microtime(true) - $started_at) * 1000)
        ]);
        exit;
    }

    $ingredients_stmt = $conn->prepare("
        SELECT id, name, unit, unit_cost, supplier
        FROM ingredients
        WHERE status = 'active'
          AND TRIM(COALESCE(supplier, '')) <> ''
    ");
    if (!$ingredients_stmt) {
        throw new Exception('Prepare failed (ingredients): ' . $conn->error);
    }
    if (!$ingredients_stmt->execute()) {
        throw new Exception('Execute failed (ingredients): ' . $ingredients_stmt->error);
    }
    $ingredient_rows = $ingredients_stmt->get_result();

    $rows_to_insert = [];
    $pair_seen = [];
    $results = [];
    $skipped_count = 0;

    while ($ing = $ingredient_rows->fetch_assoc()) {
        $ingredient_id = (int)$ing['id'];
        $ingredient_name = (string)($ing['name'] ?? '');
        $ingredient_unit = trim((string)($ing['unit'] ?? ''));
        if ($ingredient_unit === '') {
            $ingredient_unit = 'unit';
        }
        $ingredient_cost = (float)($ing['unit_cost'] ?? 0);
        $tokens = split_supplier_tokens($ing['supplier'] ?? '');

        $matched = false;
        foreach ($tokens as $token) {
            if (!isset($supplier_map[$token])) {
                continue;
            }
            foreach ($supplier_map[$token] as $supplier) {
                $pair_key = $supplier['id'] . ':' . $ingredient_id;
                if (isset($pair_seen[$pair_key])) {
                    continue;
                }
                $pair_seen[$pair_key] = true;
                $matched = true;

                $rows_to_insert[] = [
                    'supplier_id' => $supplier['id'],
                    'ingredient_id' => $ingredient_id,
                    'product_name' => $ingredient_name,
                    'unit' => $ingredient_unit,
                    'unit_price' => $ingredient_cost,
                    'lead_time_days' => $supplier['lead_time_days']
                ];

                if (count($results) < 100) {
                    $results[] = $ingredient_name . ' linked to ' . $supplier['name'];
                }
            }
        }

        if (!$matched) {
            $skipped_count++;
        }
    }

    if (empty($rows_to_insert)) {
        echo json_encode([
            'success' => true,
            'linked_count' => 0,
            'skipped_count' => $skipped_count,
            'results' => [],
            'skipped' => $skipped_count > 0 ? [$skipped_count . ' ingredient(s) had no matching supplier name.'] : [],
            'elapsed_ms' => (int)round((microtime(true) - $started_at) * 1000)
        ]);
        exit;
    }

    $note = 'Auto-linked from ingredients.supplier [' . date('Y-m-d H:i:s') . ']';
    $linked_count = 0;

    $insert_columns = ['supplier_id', 'ingredient_id'];
    $optional_columns = [
        'supplier_sku',
        'supplier_product_name',
        'unit',
        'unit_price',
        'minimum_order_qty',
        'lead_time_days',
        'availability',
        'is_preferred',
        'notes'
    ];
    foreach ($optional_columns as $col_name) {
        if (isset($available_columns[$col_name])) {
            $insert_columns[] = $col_name;
        }
    }

    $conn->begin_transaction();

    $chunks = array_chunk($rows_to_insert, 400);
    foreach ($chunks as $chunk) {
        $placeholders = [];
        $types = '';
        $params = [];

        foreach ($chunk as $row) {
            $row_placeholders = [];
            foreach ($insert_columns as $col_name) {
                $row_placeholders[] = '?';
                switch ($col_name) {
                    case 'supplier_id':
                        $types .= 'i';
                        $params[] = (int)$row['supplier_id'];
                        break;
                    case 'ingredient_id':
                        $types .= 'i';
                        $params[] = (int)$row['ingredient_id'];
                        break;
                    case 'supplier_sku':
                        $types .= 's';
                        $params[] = '';
                        break;
                    case 'supplier_product_name':
                        $types .= 's';
                        $params[] = (string)$row['product_name'];
                        break;
                    case 'unit':
                        $types .= 's';
                        $params[] = (string)$row['unit'];
                        break;
                    case 'unit_price':
                        $types .= 'd';
                        $params[] = (float)$row['unit_price'];
                        break;
                    case 'minimum_order_qty':
                        $types .= 'd';
                        $params[] = 1.0;
                        break;
                    case 'lead_time_days':
                        $types .= 'i';
                        $params[] = (int)$row['lead_time_days'];
                        break;
                    case 'availability':
                        $types .= 's';
                        $params[] = 'available';
                        break;
                    case 'is_preferred':
                        $types .= 'i';
                        $params[] = 0;
                        break;
                    case 'notes':
                        $types .= 's';
                        $params[] = $note;
                        break;
                }
            }
            $placeholders[] = '(' . implode(', ', $row_placeholders) . ')';
        }

        $sql = "
            INSERT IGNORE INTO supplier_products (
                " . implode(', ', $insert_columns) . "
            ) VALUES " . implode(', ', $placeholders);

        $stmt = $conn->prepare($sql);
        if (!$stmt) {
            throw new Exception('Prepare failed (bulk insert): ' . $conn->error);
        }

        $bindValues = [];
        $bindValues[] = &$types;
        foreach ($params as $idx => $value) {
            $bindValues[] = &$params[$idx];
        }

        if (!call_user_func_array([$stmt, 'bind_param'], $bindValues)) {
            throw new Exception('Bind failed (bulk insert): ' . $stmt->error);
        }
        if (!$stmt->execute()) {
            throw new Exception('Execute failed (bulk insert): ' . $stmt->error);
        }
        $linked_count += (int)$stmt->affected_rows;
    }

    $conn->commit();

    echo json_encode([
        'success' => true,
        'linked_count' => $linked_count,
        'skipped_count' => $skipped_count,
        'results' => $results,
        'skipped' => $skipped_count > 0 ? [$skipped_count . ' ingredient(s) had no matching supplier name.'] : [],
        'elapsed_ms' => (int)round((microtime(true) - $started_at) * 1000)
    ]);
    exit;
} catch (Exception $e) {
    if (isset($conn) && $conn instanceof mysqli) {
        @ $conn->rollback();
    }
    echo json_encode([
        'success' => false,
        'message' => 'Error: ' . $e->getMessage(),
        'elapsed_ms' => (int)round((microtime(true) - $started_at) * 1000)
    ]);
    exit;
}
