Реалізація автоматичного зіставлення (маппінгу) товарів з каталогом постачальника

Реалізація автоматичного сопоставлення товарів з каталогом постачальника

Постачальник надсилає прайс з 50 000 позицій. У кожної — артикул, назва та категорія в системі постачальника. Потрібно зрозуміти: які з них уже є в каталозі сайту, які потрібно створити, а які — це дублі під іншим артикулом. Це завдання сопоставлення (маппінгу), яке розв'язується комбінацією детермінованих правил та нечіткого пошуку.

Рівні сопоставлення

Маппінг працює пошарово — від точного до наближеного:

1. Точне совпадіння артикулу — найнадійніший спосіб
2. Совпадіння EAN/штрихкоду — якщо постачальник його надає
3. Совпадіння за нормалізованою назвою — після очищення від зайвих символів
4. Нечітке совпадіння (fuzzy) — Jaro-Winkler або Levenshtein
5. Ручне сопоставлення — для всього, що не знайшлось автоматично

class ProductMatcher
{
    /** @return MatchResult */
    public function match(SupplierProduct $sp): MatchResult
    {
        // Рівень 1: точний артикул
        if ($p = Product::where('sku', $sp->article)->first()) {
            return MatchResult::exact($p->id, 'sku');
        }

        // Рівень 2: EAN
        if ($sp->ean && $p = Product::where('ean', $sp->ean)->first()) {
            return MatchResult::exact($p->id, 'ean');
        }

        // Рівень 3: нормалізована назва
        $normalized = $this->normalize($sp->name);
        if ($p = Product::where('name_normalized', $normalized)->first()) {
            return MatchResult::exact($p->id, 'name_normalized');
        }

        // Рівень 4: fuzzy
        $candidate = $this->fuzzySearch($normalized);
        if ($candidate && $candidate->score >= 0.88) {
            return MatchResult::fuzzy($candidate->id, $candidate->score);
        }

        return MatchResult::unmatched();
    }
}

Нормалізація назв

Перед порівнянням привести строки до єдиного виду:

private function normalize(string $name): string
{
    $name = mb_strtolower($name);
    $name = preg_replace('/[\s\-\_\/]+/', ' ', $name);          // пробіли
    $name = preg_replace('/[^\p{L}\p{N}\s]/u', '', $name);      // спецсимволи
    $name = preg_replace('/\b(арт|art|код|ref|no)\b\.?\s*/iu', '', $name); // службові слова
    return trim($name);
}

Нормалізоване значення зберігається в окремому індексованому полі name_normalized — не вичислювати при кожному сопоставленні.

Нечіткий пошук через PostgreSQL

Для fuzzy-пошуку у PostgreSQL підключаємо розширення pg_trgm:

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX products_name_trgm_idx ON products USING gin (name_normalized gin_trgm_ops);

Запит на пошук схожих:

SELECT id, name_normalized,
       similarity(name_normalized, :query) AS score
FROM products
WHERE similarity(name_normalized, :query) > 0.7
ORDER BY score DESC
LIMIT 5;

У PHP через Eloquent:

private function fuzzySearch(string $query): ?object
{
    return DB::selectOne(
        "SELECT id, similarity(name_normalized, ?) AS score
         FROM products
         WHERE similarity(name_normalized, ?) > 0.7
         ORDER BY score DESC
         LIMIT 1",
        [$query, $query]
    );
}

Хранение маппінгу

CREATE TABLE supplier_product_mapping (
    id              serial PRIMARY KEY,
    supplier_id     int NOT NULL,
    supplier_sku    varchar(100) NOT NULL,
    product_id      int REFERENCES products(id),
    match_type      varchar(20),    -- exact_sku | exact_ean | fuzzy | manual | new
    match_score     float,          -- для fuzzy
    confirmed       boolean DEFAULT false,
    confirmed_by    int,            -- user_id
    confirmed_at    timestamptz,
    created_at      timestamptz DEFAULT now(),
    UNIQUE (supplier_id, supplier_sku)
);

Підтверджені маппінги (confirmed = true) використовуються напрямки. Непідтверджені fuzzy-маппінги вимагають ревю оператора.

Workflow обробки несопоставлених позицій

MatchResult::unmatched()
  └─> перевірити: схожий товар є, але score < 0.88?
        ├─> YES → створити запис mapping (confirmed=false) + сповістити оператора
        └─> NO  → створити чорновик товару або пропустити

Оператор в admin-інтерфейсі бачить список confirmed=false з запропонованими кандидатами та кнопками «Прийняти» / «Відклонити» / «Знайти інший».

Маппінг категорій постачальника

Постачальник використовує свої категорії — потрібно сопоставити з деревом категорій сайту:

CREATE TABLE supplier_category_mapping (
    supplier_id          int,
    supplier_category    varchar(200),
    site_category_id     int REFERENCES categories(id),
    created_at           timestamptz DEFAULT now(),
    PRIMARY KEY (supplier_id, supplier_category)
);

Після первинного ручного маппінгу категорій нові поступаючі товари автоматично попадають у потрібну категорію сайту.

Обнаруження дублів

Окремо завдання — знайти позиції, які у постачальника проходять під різними артикулами, але в реальності це один і той же товар:

class DuplicateDetector
{
    public function findDuplicatePairs(int $supplierId): array
    {
        // Ищем позиции с одинаковым EAN от одного поставщика
        return DB::select(
            "SELECT a.supplier_sku AS sku_a, b.supplier_sku AS sku_b, a.ean
             FROM supplier_products a
             JOIN supplier_products b ON a.ean = b.ean AND a.supplier_sku < b.supplier_sku
             WHERE a.supplier_id = ? AND b.supplier_id = ?",
            [$supplierId, $supplierId]
        );
    }
}

Продуктивність при великому каталозі

При 100 000+ позицій повний перебір з fuzzy — занадто повільно. Оптимізації:

• Спочатку batch-запит точних совпадінь (один SQL з IN (...))
• Fuzzy-пошук тільки для решти несопоставлених
• Кешувати sku → product_id маппінг у Redis перед початком обробки
• Партиціонувати обробку по чанках через Queue

// Попередня завантаження відомих маппінгів у пам'ять
$knownMappings = SupplierProductMapping::where([
    'supplier_id' => $supplierId,
    'confirmed'   => true,
])->pluck('product_id', 'supplier_sku')->all();
// Далі — O(1) пошук за артикулом

Тривалість реалізації

• Точне совпадіння за SKU/EAN, хранение маппінгу, нові чорновики — 3 дні
• Нормалізація + fuzzy через pg_trgm + чергу підтверджень — +2 дні
• Маппінг категорій, детектор дублів, admin UI для ревю — +2–3 дні