consultoria-as
a386ca5b4a
- F0 blindaje: Redis/Celery, pg_hba sin trust LAN, secretos a env 600, backups, webhook auth, jobs muertos - F1 caja: bugs POS, reembolsos al corte, apartados, CFDI, topes de descuento en cotizacion/apartado - F2/F7 IA de catalogo: autocategorizacion, dedup de SKUs con embeddings, OCR de facturas, descripciones, claves SAT, limpieza de fitment - F3 IA en el flujo: vision Yonke, preguntas MeLi, venta cruzada, anomalias, sinonimos, busqueda semantica (rerank/embeddings) - F4/F8 escala y cartera: /metrics+Prometheus+Grafana, pool, score de credito, churn, metricas multi-tenant - Nexus Yonke (M1-M6): modelo de datos 1-de-1, alta por donador, vitrina publica, MeLi usado, consignacion, garantia por dias, reportes, etiquetas - Marketplace B2B entre yonkes (catalogo + pedidos cross-tenant) - Seguridad: auth en /api/admin/* (35 endpoints), alertas por tenant - Migraciones v4.27/v4.28; timers expire-demos y rollup-metrics
781 lines
29 KiB
Python
781 lines
29 KiB
Python
# /home/Autopartes/pos/services/product_categorizer.py
|
|
"""Product auto-categorization for tenant inventory.
|
|
|
|
Given one or more items (part_number / name / brand) assign a tenant
|
|
``categories.id`` using, in order:
|
|
|
|
1. A rule engine over the Nexpart taxonomy (``services/nexpart_taxonomy``):
|
|
the item (Spanish) name is matched against the Spanish translations of
|
|
the 558 Nexpart part-types / 108 subgroups / 14 groups, plus a small
|
|
supplemental Spanish keyword table for terms the catalog lacks.
|
|
2. A fallback to the LLM classifier ``ai_chat.classify_part`` when the rule
|
|
engine finds nothing (opt-in via ``use_llm``).
|
|
|
|
The rule engine maps a Nexpart node to a *canonical category* (a short Spanish
|
|
name like "Frenos", "Motor", "Encendido"). The canonical name is then resolved
|
|
against the tenant's own ``categories`` table: an existing row is reused when a
|
|
close match exists, otherwise a new row is created.
|
|
|
|
Public API:
|
|
categorize_item(conn, part_number, name, brand, ...) -> dict
|
|
categorize_items(conn, items, ...) -> dict
|
|
categorize_inventory(conn, limit, ...) -> dict
|
|
|
|
Every result dict has the shape:
|
|
{part_number, category_id, category_name, canonical_category,
|
|
confidence, source}
|
|
|
|
``source`` is one of: 'taxonomy', 'keyword', 'brand', 'llm', 'none'.
|
|
"""
|
|
|
|
import re
|
|
import unicodedata
|
|
from difflib import SequenceMatcher
|
|
|
|
from services import nexpart_taxonomy as nt
|
|
from services.nexpart_taxonomy import NEXPART_TAXONOMY, TAXONOMY_OVERRIDES_ES
|
|
|
|
try: # translations.py is optional; the taxonomy overrides are the main source
|
|
from services.translations import PART_TRANSLATIONS
|
|
except Exception: # pragma: no cover
|
|
PART_TRANSLATIONS = {}
|
|
|
|
|
|
# ── Canonical category names ───────────────────────────────────────────────
|
|
# Short Spanish names that both align with the Nexpart groups and, in
|
|
# practice, with the names tenants already use in their `categories` table.
|
|
|
|
CANONICAL_BY_GROUP = {
|
|
"Ignition & Filters": "Filtros y Encendido",
|
|
"Belts, Hoses, Water Pumps & Cooling System Parts": "Enfriamiento",
|
|
"Starting & Charging System Parts (Alternators, Batteries & Cables)": "Eléctrico",
|
|
"Brake System, Wheel Bearings, Studs, Nuts & Hardware": "Frenos",
|
|
"Fuel & Emissions Parts": "Combustible y Emisiones",
|
|
"Heating & Air Conditioning": "Aire Acondicionado",
|
|
"Engine Parts": "Motor",
|
|
"Drivetrain Parts": "Tren Motriz",
|
|
"Steering & Suspension Parts": "Suspensión",
|
|
"Exhaust, Clutch & Flywheel Parts": "Escape",
|
|
"Wipers, Lamps & Fuses": "Luces y Limpiaparabrisas",
|
|
"Body Parts, Cables, Caps, Electrical Motors, Switches & Other Miscellaneous Parts": "Carrocería",
|
|
"Chemicals, Waxes & Lubricants": "Lubricantes y Químicos",
|
|
"Tires, Wheels, Tools & Accessory Parts": "Llantas y Accesorios",
|
|
}
|
|
|
|
# Subgroup overrides: more specific than the group default.
|
|
CANONICAL_BY_SUBGROUP = {
|
|
# Ignition & Filters
|
|
"Filters & PCV": "Filtros",
|
|
"Spark Plugs & Glow Plugs": "Encendido",
|
|
"Tune-Up & Ignition Parts": "Encendido",
|
|
"Ignition Wires": "Encendido",
|
|
"Computers & Relays": "Eléctrico",
|
|
# Starting & Charging
|
|
"Batteries": "Eléctrico",
|
|
"Starters": "Eléctrico",
|
|
"Alternators & Voltage Regulators": "Eléctrico",
|
|
"Starter Solenoids, Switches & Relays": "Eléctrico",
|
|
# Cooling
|
|
"Radiators & Electric Fan Motors": "Enfriamiento",
|
|
"Water Pumps, Fan Blades & Clutches": "Enfriamiento",
|
|
"Thermostats, Housings & Radiator Caps": "Enfriamiento",
|
|
"Engine Coolant & Bypass Hoses": "Enfriamiento",
|
|
# Brakes
|
|
"Front Friction, Drums & Rotors": "Frenos",
|
|
"Rear Friction, Drums & Rotors": "Frenos",
|
|
"Front Calipers, Wheel Cylinders, Hoses": "Frenos",
|
|
"Rear Calipers, Wheel Cylinders, Hoses": "Frenos",
|
|
"Master Cylinders, Boosters & Switches": "Frenos",
|
|
"Brake Cables, Studs, Nuts & Spindle Nuts": "Frenos",
|
|
"Front Brake Hardware & ABS Sensors": "Frenos",
|
|
"Rear Brake Hardware & ABS Sensors": "Frenos",
|
|
"Miscellaneous Disc Hardware": "Frenos",
|
|
"Miscellaneous Drum Hardware": "Frenos",
|
|
"Front Wheel Bearings & Seals": "Baleros y Mazas",
|
|
"Rear Wheel Bearings & Seals": "Baleros y Mazas",
|
|
"Spindles & Hubs": "Baleros y Mazas",
|
|
# Fuel & emissions
|
|
"Fuel Pumps & Tanks": "Combustible y Emisiones",
|
|
"Fuel Injection Parts, Mass Air Flow Sensors": "Combustible y Emisiones",
|
|
"Carburetors, Carburetor Kits & Components": "Combustible y Emisiones",
|
|
"EGR & Emissions Valves": "Combustible y Emisiones",
|
|
"Emission Sensors, Relays, Solenoids & Switches": "Combustible y Emisiones",
|
|
"Fuel Injection Sensors, Relays & Switches": "Combustible y Emisiones",
|
|
"Turbochargers & Superchargers": "Motor",
|
|
# AC
|
|
"AC Compressors, Kits & Parts": "Aire Acondicionado",
|
|
"AC Condensers & Evaporators": "Aire Acondicionado",
|
|
"AC Accumulators, Receiver Driers & Valves": "Aire Acondicionado",
|
|
"AC Hose Assemblies & Fittings": "Aire Acondicionado",
|
|
"Blower Motors & Parts": "Aire Acondicionado",
|
|
"Heater Cores & Heater Control Valves": "Aire Acondicionado",
|
|
# Engine
|
|
"Cams, Lifters & Timing Parts": "Motor",
|
|
"Crankshafts & Bearings": "Motor",
|
|
"Pistons, Rings & Rods": "Motor",
|
|
"Heads & Manifolds": "Motor",
|
|
"Gasket Sets": "Motor",
|
|
"Individual Gaskets & Seals": "Motor",
|
|
"Intake & Exhaust Valves": "Motor",
|
|
"Rockers & Push Rods": "Motor",
|
|
"Vacuum & Oil Pumps": "Motor",
|
|
"Engine Block Parts": "Motor",
|
|
"Engines & Kits": "Motor",
|
|
"Engine Mounts & Other Miscellaneous Engine Parts": "Motor",
|
|
# Drivetrain
|
|
"Driveshafts, U-Joints & CV (Constant Velocity) Parts": "Tren Motriz",
|
|
"Axle & Differential Parts": "Tren Motriz",
|
|
"Transmission & Parts": "Transmisión",
|
|
"Transmission Kits & Gaskets": "Transmisión",
|
|
"Automatic Transmission Seals": "Transmisión",
|
|
"Manual Transmission Seals": "Transmisión",
|
|
"Manual Transmission Bearings": "Transmisión",
|
|
"Clutches & Clutch Kits": "Clutch",
|
|
"Clutch Hydraulics": "Clutch",
|
|
"Miscellaneous Clutch Parts": "Clutch",
|
|
# Steering & suspension
|
|
"Ball Joints & Control Arms": "Suspensión",
|
|
"Shock Absorbers & Struts": "Suspensión",
|
|
"Sway Bars, Stabilizer Bars, Strut Rods & Parts": "Suspensión",
|
|
"Alignment Kits & Tools": "Suspensión",
|
|
"Steering Linkages, Rods & Arms": "Dirección",
|
|
"Rack & Pinion, Gear Box, Power Cylinder": "Dirección",
|
|
"Power Steering Pumps, Hoses & Kits": "Dirección",
|
|
"King Pins, Trailing Arms, Alignment & Other Chassis": "Dirección",
|
|
# Exhaust
|
|
"All Exhaust & Diagrams": "Escape",
|
|
"Catalytic Converter": "Escape",
|
|
"Manifolds & Headers": "Escape",
|
|
"Individual Exhaust Parts": "Escape",
|
|
# Wipers / lamps / fuses
|
|
"Arms, Blades & Refills": "Limpiaparabrisas",
|
|
"Wiper Motors & Washer Pumps": "Limpiaparabrisas",
|
|
"Headlamps & Flashers": "Luces",
|
|
"Exterior Lamps": "Luces",
|
|
"Interior Lamps": "Luces",
|
|
"Lighting Modules & Switches": "Luces",
|
|
"Lighting Relays & Sensors": "Luces",
|
|
# Body / electrical / misc
|
|
"Bumpers & License Plates": "Carrocería",
|
|
"Door, Window & Tailgate Parts": "Carrocería",
|
|
"Glass": "Carrocería",
|
|
"Hood & Tailgate Parts": "Carrocería",
|
|
"Hoods Fenders & Body Parts": "Carrocería",
|
|
"Hood, Fender & Body Parts": "Carrocería",
|
|
"Interior & Steering Wheel": "Carrocería",
|
|
"Electrical Motors": "Eléctrico",
|
|
"Electronics, Sensors, Relays & Miscellaneous Parts": "Eléctrico",
|
|
"Sensors, Switches & Relays": "Eléctrico",
|
|
"Switches, Relays & Miscellaneous Parts": "Eléctrico",
|
|
"Electronics Audio/Visual & Mirrors": "Carrocería",
|
|
# Chemicals / tools
|
|
"Engine & Transmission Lubricants & Additives": "Lubricantes y Químicos",
|
|
"Tires & Wheels": "Llantas y Accesorios",
|
|
"Tools, Jacks, Hardware & Manuals": "Llantas y Accesorios",
|
|
"Wheel & Hardware": "Llantas y Accesorios",
|
|
}
|
|
|
|
# Extra names a canonical category should be matched against in the tenant's
|
|
# `categories` table (in addition to the canonical name itself).
|
|
CATEGORY_ALIASES = {
|
|
"Filtros y Encendido": ["Filtros", "Encendido", "Filtros y Encendido", "Afinacion y Encendido"],
|
|
"Filtros": ["Filtros", "Filtro de aceite", "Filtros de aire", "Filtros de gasolina"],
|
|
"Encendido": ["Encendido", "Ignición y Eléctrico", "Bujías"],
|
|
"Enfriamiento": ["Enfriamiento", "Refrigeración", "Sistema de Enfriamiento", "Radiadores"],
|
|
"Eléctrico": ["Eléctrico", "Eléctrico e Iluminación", "Ignición y Eléctrico", "Baterías y Eléctrico"],
|
|
"Frenos": ["Frenos", "Frenos (Discos)", "Balatas", "Sistema de Frenos"],
|
|
"Baleros y Mazas": ["Baleros", "Balero", "Mazas", "Baleros y Sellos", "Baleros de Rueda"],
|
|
"Combustible y Emisiones": ["Combustible", "Combustible y Aire", "Inyección", "Emisiones"],
|
|
"Aire Acondicionado": ["Aire Acondicionado", "Calefacción y Aire Acondicionado", "A/C", "Heat & AC"],
|
|
"Motor": ["Motor", "Partes de Motor", "Engine", "Motor y Componentes"],
|
|
"Tren Motriz": ["Tren Motriz", "Drivetrain", "Transmisión", "Flechas"],
|
|
"Transmisión": ["Transmisión", "Transmission", "Clutch y Transmisión", "Caja"],
|
|
"Clutch": ["Clutch", "Clutch y Transmisión", "Kits de Clutch"],
|
|
"Suspensión": ["Suspensión", "Suspension", "Amortiguadores", "Dirección y Suspensión"],
|
|
"Dirección": ["Dirección", "Steering", "Cremalleras", "Dirección y Rótulas"],
|
|
"Escape": ["Escape", "Exhaust", "Mofles", "Sistema de Escape"],
|
|
"Luces y Limpiaparabrisas": ["Luces", "Iluminación", "Limpiaparabrisas", "Faros y Calaveras"],
|
|
"Luces": ["Luces", "Iluminación", "Faros", "Calaveras"],
|
|
"Limpiaparabrisas": ["Limpiaparabrisas", "Plumas", "Wipers"],
|
|
"Carrocería": ["Carrocería", "Body & Lamp Assembly", "Body", "Accesorios"],
|
|
"Lubricantes y Químicos": ["Aceites y Lubricantes", "Lubricantes", "Químicos", "Químicos, Ceras y Lubricantes"],
|
|
"Llantas y Accesorios": ["Llantas", "Llantas y Rines", "Herramienta", "Herramientas", "Accesorios", "Tires, Wheels, Tools & Accessory Parts"],
|
|
}
|
|
|
|
# Supplemental Spanish rules for terms the Nexpart translations don't cover.
|
|
# Applied only when the taxonomy matcher found nothing. Longest match wins.
|
|
ES_RULES = [
|
|
("disco de freno", "Frenos"),
|
|
("discos de freno", "Frenos"),
|
|
("tambor de freno", "Frenos"),
|
|
("cinta de freno", "Frenos"),
|
|
("tambor", "Frenos"),
|
|
("balero de rueda", "Baleros y Mazas"),
|
|
("balero", "Baleros y Mazas"),
|
|
("maza de rueda", "Baleros y Mazas"),
|
|
("maza", "Baleros y Mazas"),
|
|
("husillo", "Baleros y Mazas"),
|
|
("cremallera", "Dirección"),
|
|
("terminal de direccion", "Dirección"),
|
|
("bieleta", "Dirección"),
|
|
("rotula", "Suspensión"),
|
|
("horquilla", "Suspensión"),
|
|
("birlo", "Frenos"),
|
|
("motoventilador", "Enfriamiento"),
|
|
("ventilador", "Enfriamiento"),
|
|
("calavera", "Luces"),
|
|
("faro", "Luces"),
|
|
("direccional", "Luces"),
|
|
("foco", "Luces"),
|
|
("espejo", "Carrocería"),
|
|
("salpicadera", "Carrocería"),
|
|
("defensa", "Carrocería"),
|
|
("cristal", "Carrocería"),
|
|
("empaque", "Motor"),
|
|
("junta", "Motor"),
|
|
("sello", "Motor"),
|
|
("sensor de oxigeno", "Combustible y Emisiones"),
|
|
("inyector", "Combustible y Emisiones"),
|
|
("bomba de gasolina", "Combustible y Emisiones"),
|
|
("catalizador", "Escape"),
|
|
("convertidor", "Escape"),
|
|
("mofle", "Escape"),
|
|
("silenciador", "Escape"),
|
|
("clutch", "Clutch"),
|
|
("embrague", "Clutch"),
|
|
("banda", "Enfriamiento"),
|
|
("manguera", "Enfriamiento"),
|
|
("radiador", "Enfriamiento"),
|
|
("termostato", "Enfriamiento"),
|
|
("bomba de agua", "Enfriamiento"),
|
|
("bomba de aceite", "Motor"),
|
|
("alternador", "Eléctrico"),
|
|
("arrancador", "Eléctrico"),
|
|
("marcha", "Eléctrico"),
|
|
("bateria", "Eléctrico"),
|
|
("sensor", "Eléctrico"),
|
|
("switch", "Eléctrico"),
|
|
("relevador", "Eléctrico"),
|
|
("fusible", "Eléctrico"),
|
|
("filtro", "Filtros"),
|
|
("bujia", "Encendido"),
|
|
("balata", "Frenos"),
|
|
("pastilla", "Frenos"),
|
|
("amortiguador", "Suspensión"),
|
|
("toe", "Suspensión"),
|
|
# Drivetrain / CV
|
|
("junta homocinetica", "Tren Motriz"),
|
|
("homocinetica", "Tren Motriz"),
|
|
("cubre polvo", "Tren Motriz"),
|
|
("cubrepolvo", "Tren Motriz"),
|
|
("guardapolvo", "Tren Motriz"),
|
|
("fuelle", "Tren Motriz"),
|
|
("tripode", "Tren Motriz"),
|
|
("flecha", "Tren Motriz"),
|
|
("reten", "Motor"),
|
|
("o ring", "Motor"),
|
|
# Consumables
|
|
("aditivo", "Lubricantes y Químicos"),
|
|
("aceite", "Lubricantes y Químicos"),
|
|
("grasa", "Lubricantes y Químicos"),
|
|
("liquido", "Lubricantes y Químicos"),
|
|
("spray", "Lubricantes y Químicos"),
|
|
("aerosol", "Lubricantes y Químicos"),
|
|
("limpiador", "Lubricantes y Químicos"),
|
|
("anticongelante", "Lubricantes y Químicos"),
|
|
("refrigerante", "Enfriamiento"),
|
|
("solvente", "Lubricantes y Químicos"),
|
|
("silicon", "Lubricantes y Químicos"),
|
|
("sellador", "Lubricantes y Químicos"),
|
|
("pegamento", "Lubricantes y Químicos"),
|
|
("acondicionador", "Lubricantes y Químicos"),
|
|
("afloja todo", "Lubricantes y Químicos"),
|
|
("pintura", "Carrocería"),
|
|
# Electrical modules
|
|
("modulo", "Eléctrico"),
|
|
("regulador", "Eléctrico"),
|
|
("solenoide", "Eléctrico"),
|
|
# Tools / accessories
|
|
("herramienta", "Llantas y Accesorios"),
|
|
("inflallanta", "Llantas y Accesorios"),
|
|
("desarmador", "Llantas y Accesorios"),
|
|
("pinza", "Llantas y Accesorios"),
|
|
("dado", "Llantas y Accesorios"),
|
|
# Misc frequent terms seen in real tenant catalogs
|
|
("barra de direccion", "Dirección"),
|
|
("terminal de direccion", "Dirección"),
|
|
("terminal", "Dirección"),
|
|
("espiga", "Suspensión"),
|
|
("resorte", "Suspensión"),
|
|
("barra", "Suspensión"),
|
|
("aspas", "Enfriamiento"),
|
|
("aspa", "Enfriamiento"),
|
|
("tulipa", "Luces"),
|
|
("conector", "Eléctrico"),
|
|
("bulbo", "Eléctrico"),
|
|
("resistencia", "Eléctrico"),
|
|
("tripoide", "Tren Motriz"),
|
|
("cople", "Tren Motriz"),
|
|
("motor", "Motor"),
|
|
("dir", "Dirección"),
|
|
]
|
|
|
|
# Weak brand-only hints, used when neither name rules nor the taxonomy hit.
|
|
BRAND_HINTS = {
|
|
"NGK": "Encendido", "DENSO": "Encendido", "ACDELCO": "Encendido",
|
|
"BREMBO": "Frenos", "RAYBESTOS": "Frenos", "WAGNER": "Frenos",
|
|
"MONROE": "Suspensión", "GABRIEL": "Suspensión", "MOOG": "Suspensión",
|
|
"FRAM": "Filtros", "PUROLATOR": "Filtros", "WIX": "Filtros",
|
|
"GATES": "Enfriamiento", "DAYCO": "Enfriamiento", "GMB": "Enfriamiento",
|
|
"TRW": "Dirección", "EXEDY": "Clutch", "LUK": "Clutch",
|
|
"FEL-PRO": "Motor", "MAHLE": "Motor", "VICTOR REINZ": "Motor",
|
|
"BOSCH": "Eléctrico", "VALEO": "Eléctrico",
|
|
}
|
|
|
|
|
|
# ── Normalization / indexes ────────────────────────────────────────────────
|
|
|
|
def _norm(s):
|
|
"""Uppercase, strip accents, keep alphanumerics, collapse whitespace."""
|
|
s = unicodedata.normalize("NFD", str(s or ""))
|
|
s = s.encode("ascii", "ignore").decode("ascii")
|
|
s = re.sub(r"[^A-Za-z0-9]+", " ", s).upper().strip()
|
|
return " ".join(s.split())
|
|
|
|
|
|
def _build_indexes():
|
|
pt_index = {} # normalized Spanish part-type -> English Nexpart part type
|
|
node_index = {} # normalized Spanish group/subgroup -> (group, subgroup|None)
|
|
|
|
for en, es in TAXONOMY_OVERRIDES_ES.items():
|
|
n = _norm(es)
|
|
if not n:
|
|
continue
|
|
if en in NEXPART_TAXONOMY:
|
|
node_index.setdefault(n, (en, None))
|
|
continue
|
|
found = None
|
|
for g, subs in NEXPART_TAXONOMY.items():
|
|
for sg in subs:
|
|
if en == sg:
|
|
found = (g, sg)
|
|
break
|
|
if found:
|
|
break
|
|
if found:
|
|
node_index.setdefault(n, found)
|
|
else:
|
|
pt_index.setdefault(n, en)
|
|
|
|
# Invert the TecDoc→Spanish dictionary for extra coverage.
|
|
for en, es in PART_TRANSLATIONS.items():
|
|
for part in str(es).split("/"):
|
|
n = _norm(part)
|
|
if n and n not in pt_index and n not in node_index:
|
|
pt_index[n] = en
|
|
|
|
return pt_index, node_index
|
|
|
|
|
|
_PT_INDEX, _NODE_INDEX = _build_indexes()
|
|
_ES_RULES_SORTED = sorted(
|
|
((_norm(k), v) for k, v in ES_RULES if _norm(k)), key=lambda kv: -len(kv[0])
|
|
)
|
|
|
|
|
|
def _key_usable(key):
|
|
"""Generic single tokens ("sensor", "kit") create false positives."""
|
|
tokens = key.split()
|
|
if len(tokens) >= 2:
|
|
return True
|
|
return len(key) >= 9
|
|
|
|
|
|
# ── Matchers ───────────────────────────────────────────────────────────────
|
|
|
|
def match_taxonomy(name):
|
|
"""Match an item name against the Nexpart taxonomy.
|
|
|
|
Returns (group, subgroup, part_type, kind, confidence) or None.
|
|
kind ∈ {'part_type', 'subgroup', 'group'}.
|
|
"""
|
|
n = _norm(name)
|
|
if not n:
|
|
return None
|
|
padded = " " + n + " "
|
|
|
|
best = None # (key_len, kind_rank, group, subgroup, part_type)
|
|
|
|
for key, en in _PT_INDEX.items():
|
|
if not _key_usable(key) or (" " + key + " ") not in padded:
|
|
continue
|
|
node = nt.tecdoc_to_nexpart(en)
|
|
if not node:
|
|
continue
|
|
cand = (len(key), 3, node[0], node[1], node[2])
|
|
if best is None or cand[:2] > best[:2]:
|
|
best = cand
|
|
|
|
for key, (g, sg) in _NODE_INDEX.items():
|
|
if not _key_usable(key) or (" " + key + " ") not in padded:
|
|
continue
|
|
rank = 2 if sg else 1
|
|
cand = (len(key), rank, g, sg, None)
|
|
if best is None or cand[:2] > best[:2]:
|
|
best = cand
|
|
|
|
if not best:
|
|
return None
|
|
if best[1] == 3:
|
|
return (best[2], best[3], best[4], "part_type", 0.85)
|
|
if best[1] == 2:
|
|
return (best[2], best[3], None, "subgroup", 0.65)
|
|
return (best[2], None, None, "group", 0.5)
|
|
|
|
|
|
def canonical_category(group, subgroup):
|
|
"""Map a Nexpart (group, subgroup) to a canonical Spanish category."""
|
|
if subgroup and subgroup in CANONICAL_BY_SUBGROUP:
|
|
return CANONICAL_BY_SUBGROUP[subgroup]
|
|
if group and group in CANONICAL_BY_GROUP:
|
|
return CANONICAL_BY_GROUP[group]
|
|
return "Otros"
|
|
|
|
|
|
def match_rules(name):
|
|
"""Supplemental Spanish keyword match. Returns (canonical, confidence)."""
|
|
n = _norm(name)
|
|
if not n:
|
|
return None
|
|
padded = " " + n + " "
|
|
for key, canonical in _ES_RULES_SORTED:
|
|
if " " + key + " " in padded:
|
|
return canonical, 0.6
|
|
return None
|
|
|
|
|
|
def classify_name(name, brand=None):
|
|
"""Rule-only classification. Returns (canonical, confidence, source)."""
|
|
hit = match_taxonomy(name)
|
|
if hit:
|
|
group, subgroup, _pt, _kind, conf = hit
|
|
return canonical_category(group, subgroup), conf, "taxonomy"
|
|
|
|
rule = match_rules(name)
|
|
if rule:
|
|
return rule[0], rule[1], "keyword"
|
|
|
|
brand_key = _norm(brand)
|
|
if brand_key and brand_key in BRAND_HINTS:
|
|
return BRAND_HINTS[brand_key], 0.4, "brand"
|
|
|
|
return None, 0.0, "none"
|
|
|
|
|
|
# ── Tenant category resolution ─────────────────────────────────────────────
|
|
|
|
def _similarity(a, b):
|
|
if not a or not b:
|
|
return 0.0
|
|
if a == b:
|
|
return 1.0
|
|
ta, tb = a.split(), b.split()
|
|
if len(ta) >= 1 and len(tb) >= 1:
|
|
seta, setb = set(ta), set(tb)
|
|
# whole-string containment (word aligned) is a strong signal
|
|
if setb <= seta or seta <= setb:
|
|
return 0.85
|
|
return SequenceMatcher(None, a, b).ratio()
|
|
|
|
|
|
class CategoryResolver:
|
|
"""Resolve canonical category names to rows in the tenant `categories`."""
|
|
|
|
def __init__(self, conn, create=True):
|
|
self.conn = conn
|
|
self.create = create
|
|
self._cache = {}
|
|
self._existing = None # list of (id, name, norm)
|
|
self.available = self._load()
|
|
|
|
def _load(self):
|
|
cur = self.conn.cursor()
|
|
try:
|
|
cur.execute("SELECT to_regclass('public.categories')")
|
|
if cur.fetchone()[0] is None:
|
|
return False
|
|
cur.execute(
|
|
"SELECT id, name FROM categories "
|
|
"WHERE is_active IS NULL OR is_active = true"
|
|
)
|
|
self._existing = [(r[0], r[1], _norm(r[1])) for r in cur.fetchall()]
|
|
return True
|
|
except Exception:
|
|
return False
|
|
finally:
|
|
cur.close()
|
|
|
|
def _best_existing(self, canonical):
|
|
target = _norm(canonical)
|
|
candidates = [target] + [_norm(a) for a in CATEGORY_ALIASES.get(canonical, [])]
|
|
best_id, best_name, best_score = None, None, 0.0
|
|
for cid, name, norm_name in self._existing:
|
|
score = max(_similarity(norm_name, c) for c in candidates if c)
|
|
if score > best_score:
|
|
best_id, best_name, best_score = cid, name, score
|
|
if best_score >= 0.72:
|
|
return best_id, best_name
|
|
return None, None
|
|
|
|
def resolve(self, canonical):
|
|
"""Return (category_id, category_name) for a canonical category."""
|
|
if canonical in self._cache:
|
|
return self._cache[canonical]
|
|
if not self.available:
|
|
self._cache[canonical] = (None, canonical)
|
|
return self._cache[canonical]
|
|
|
|
cid, name = self._best_existing(canonical)
|
|
if cid is not None:
|
|
self._cache[canonical] = (cid, name)
|
|
return self._cache[canonical]
|
|
|
|
if not self.create:
|
|
self._cache[canonical] = (None, canonical)
|
|
return self._cache[canonical]
|
|
|
|
cur = self.conn.cursor()
|
|
try:
|
|
cur.execute(
|
|
"INSERT INTO categories (name) VALUES (%s) RETURNING id",
|
|
(canonical,),
|
|
)
|
|
cid = cur.fetchone()[0]
|
|
self.conn.commit()
|
|
finally:
|
|
cur.close()
|
|
self._existing.append((cid, canonical, _norm(canonical)))
|
|
self._cache[canonical] = (cid, canonical)
|
|
return self._cache[canonical]
|
|
|
|
|
|
# ── Public API ─────────────────────────────────────────────────────────────
|
|
|
|
def categorize_item(conn, part_number, name=None, brand=None,
|
|
use_llm=False, persist=False, resolver=None, create_categories=True):
|
|
"""Classify a single item and optionally persist it.
|
|
|
|
Returns {part_number, category_id, category_name, canonical_category,
|
|
confidence, source, persisted}.
|
|
"""
|
|
if resolver is None:
|
|
resolver = CategoryResolver(conn, create=create_categories)
|
|
|
|
canonical, confidence, source = classify_name(name or part_number, brand)
|
|
|
|
if canonical is None and use_llm:
|
|
canonical, confidence, source = _llm_category(part_number, name)
|
|
|
|
if canonical is None:
|
|
result = {
|
|
"part_number": part_number, "category_id": None,
|
|
"category_name": None, "canonical_category": None,
|
|
"confidence": 0.0, "source": "none", "persisted": 0,
|
|
}
|
|
else:
|
|
cid, cname = resolver.resolve(canonical)
|
|
result = {
|
|
"part_number": part_number, "category_id": cid,
|
|
"category_name": cname, "canonical_category": canonical,
|
|
"confidence": round(confidence, 2), "source": source, "persisted": 0,
|
|
}
|
|
|
|
if persist and result["category_id"] is not None and part_number:
|
|
result["persisted"] = _persist_item_category(conn, part_number, result["category_id"])
|
|
|
|
return result
|
|
|
|
|
|
def _llm_category(part_number, name=None):
|
|
"""Ask ai_chat.classify_part for a category. Returns (canonical, conf, src)."""
|
|
if not part_number:
|
|
return None, 0.0, "none"
|
|
try:
|
|
from services.ai_chat import classify_part
|
|
res = classify_part(str(part_number))
|
|
except Exception:
|
|
return None, 0.0, "none"
|
|
if not res:
|
|
return None, 0.0, "none"
|
|
raw = (res.get("category") or "").strip()
|
|
if not raw:
|
|
return None, 0.0, "none"
|
|
canon = _map_llm_category(raw)
|
|
return canon, 0.5, "llm"
|
|
|
|
|
|
def map_llm_category(raw):
|
|
"""Public alias: map a free-text Spanish category (LLM output)."""
|
|
return _map_llm_category(raw)
|
|
|
|
|
|
def _map_llm_category(raw):
|
|
"""Map a free-text Spanish category (LLM output) to a canonical name."""
|
|
n = _norm(raw)
|
|
if not n:
|
|
return None
|
|
# direct canonical / alias hit
|
|
for canonical, aliases in CATEGORY_ALIASES.items():
|
|
for a in [canonical] + list(aliases):
|
|
if _norm(a) == n:
|
|
return canonical
|
|
# fall back to the keyword rules over the LLM's own words
|
|
rule = match_rules(raw)
|
|
if rule:
|
|
return rule[0]
|
|
return raw.strip()
|
|
|
|
|
|
def _persist_item_category(conn, part_number, category_id):
|
|
cur = conn.cursor()
|
|
try:
|
|
cur.execute(
|
|
"UPDATE inventory SET category_id = %s "
|
|
"WHERE part_number = %s AND is_active = true "
|
|
"AND (category_id IS NULL OR category_id <> %s)",
|
|
(category_id, part_number, category_id),
|
|
)
|
|
n = cur.rowcount
|
|
conn.commit()
|
|
return n
|
|
finally:
|
|
cur.close()
|
|
|
|
|
|
def categorize_items(conn, items, use_llm=False, persist=False,
|
|
create_categories=True, sample_limit=20):
|
|
"""Categorize a list of {part_number, name, brand} dicts.
|
|
|
|
Returns {count, persisted, by_source, categories, results, sample}.
|
|
"""
|
|
# A dry run must not mutate the tenant's categories table.
|
|
create_categories = create_categories and persist
|
|
resolver = CategoryResolver(conn, create=create_categories)
|
|
results = []
|
|
by_source = {}
|
|
persisted = 0
|
|
for it in items or []:
|
|
r = categorize_item(
|
|
conn, it.get("part_number"), it.get("name"), it.get("brand"),
|
|
use_llm=use_llm, persist=persist, resolver=resolver,
|
|
create_categories=create_categories,
|
|
)
|
|
persisted += r.get("persisted", 0)
|
|
by_source[r["source"]] = by_source.get(r["source"], 0) + 1
|
|
results.append(r)
|
|
|
|
categories = {}
|
|
for r in results:
|
|
if r["category_name"]:
|
|
categories[r["category_name"]] = categories.get(r["category_name"], 0) + 1
|
|
|
|
return {
|
|
"count": len(results),
|
|
"persisted": persisted,
|
|
"by_source": by_source,
|
|
"categories": categories,
|
|
"results": results[:sample_limit] if sample_limit else results,
|
|
"sample": results[:sample_limit],
|
|
}
|
|
|
|
|
|
def categorize_inventory(conn, limit=200, only_uncategorized=True, use_llm=False,
|
|
persist=True, create_categories=True, offset=0,
|
|
after_id=None, sample_limit=20):
|
|
"""Categorize inventory rows in bulk.
|
|
|
|
``after_id`` enables checkpointed backfills (process rows with id > after_id).
|
|
Returns {scanned, persisted, by_source, categories, sample, last_id}.
|
|
"""
|
|
cur = conn.cursor()
|
|
where = ["is_active = true"]
|
|
params = []
|
|
if only_uncategorized:
|
|
where.append("(category_id IS NULL)")
|
|
if after_id is not None:
|
|
where.append("id > %s")
|
|
params.append(after_id)
|
|
sql = (
|
|
"SELECT id, part_number, name, brand FROM inventory "
|
|
"WHERE " + " AND ".join(where) +
|
|
" ORDER BY id LIMIT %s OFFSET %s"
|
|
)
|
|
params.extend([int(limit), int(offset)])
|
|
cur.execute(sql, params)
|
|
rows = cur.fetchall()
|
|
cur.close()
|
|
|
|
# A dry run must not mutate the tenant's categories table.
|
|
create_categories = create_categories and persist
|
|
resolver = CategoryResolver(conn, create=create_categories)
|
|
by_source = {}
|
|
categories = {}
|
|
persisted = 0
|
|
sample = []
|
|
last_id = after_id
|
|
|
|
for inv_id, part_number, name, brand in rows:
|
|
canonical, confidence, source = classify_name(name or part_number, brand)
|
|
if canonical is None and use_llm:
|
|
canonical, confidence, source = _llm_category(part_number, name)
|
|
|
|
cid, cname = (None, None)
|
|
if canonical:
|
|
cid, cname = resolver.resolve(canonical)
|
|
|
|
n = 0
|
|
if persist and cid is not None:
|
|
c2 = conn.cursor()
|
|
try:
|
|
c2.execute(
|
|
"UPDATE inventory SET category_id = %s "
|
|
"WHERE id = %s AND (category_id IS NULL OR category_id <> %s)",
|
|
(cid, inv_id, cid),
|
|
)
|
|
n = c2.rowcount
|
|
conn.commit()
|
|
finally:
|
|
c2.close()
|
|
persisted += n
|
|
by_source[source] = by_source.get(source, 0) + 1
|
|
if cname:
|
|
categories[cname] = categories.get(cname, 0) + 1
|
|
if len(sample) < sample_limit:
|
|
sample.append({
|
|
"inventory_id": inv_id, "part_number": part_number, "name": name,
|
|
"category_id": cid, "category_name": cname,
|
|
"canonical_category": canonical,
|
|
"confidence": round(confidence, 2), "source": source,
|
|
})
|
|
last_id = inv_id
|
|
|
|
return {
|
|
"scanned": len(rows),
|
|
"persisted": persisted,
|
|
"by_source": by_source,
|
|
"categories": categories,
|
|
"sample": sample,
|
|
"last_id": last_id,
|
|
}
|
|
|
|
|
|
def stats():
|
|
"""Diagnostic helper for the rule engine."""
|
|
return {
|
|
"part_type_keys": len(_PT_INDEX),
|
|
"node_keys": len(_NODE_INDEX),
|
|
"es_rules": len(_ES_RULES_SORTED),
|
|
"brand_hints": len(BRAND_HINTS),
|
|
"canonical_categories": sorted(set(CANONICAL_BY_GROUP.values())),
|
|
}
|