Autoparts-DB/scripts/backfill_vehicle_compat.py

#!/usr/bin/env python3
"""Backfill vehicle compatibility for ALL tenant inventory items.

Matches tenant inventory part numbers (and SKU aliases) against the master
supplier_catalog / supplier_catalog_interchange / supplier_catalog_compat
tables, then inserts model_year_engine_id compat rows with
source='supplier_catalog'.

Loads the whole mapping in memory (~1M small rows) so it runs in minutes,
not days. Safe to re-run: ON CONFLICT DO NOTHING skips existing rows.

Usage (as postgres):
  MASTER_DB_URL=... python3 scripts/backfill_vehicle_compat.py [db_name|all]
"""

import os
import re
import sys

sys.path.insert(0, os.path.join(os.path.dirname(os.path.abspath(__file__)), '..', 'pos'))

import psycopg2  # noqa: E402
from psycopg2.extras import execute_values  # noqa: E402

MASTER_DB = os.environ.get('MASTER_DB_NAME', 'nexus_autoparts')
BATCH_COMMIT = 2000

PREFIX_RE = re.compile(r'^[A-Z]{2,5}[-_ ]')  # tenant prefixes like OEM-, INJ-, WAL-


def norm(pn):
    if not pn:
        return ''
    return re.sub(r'[\s\-/._]', '', str(pn)).upper()


def pn_variants(part_number, aliases):
    """Candidate normalized part numbers for an inventory item."""
    out = set()
    for raw in [part_number] + list(aliases):
        n = norm(raw)
        if n:
            out.add(n)
        # strip tenant prefixes: OEM-96355 -> 96355
        stripped = PREFIX_RE.sub('', str(raw or '').upper())
        n2 = norm(stripped)
        if n2:
            out.add(n2)
    return out


def load_master_mapping(cur):
    cur.execute("SELECT id, sku FROM supplier_catalog")
    sku_map = {}
    for cid, sku in cur.fetchall():
        n = norm(sku)
        if n:
            sku_map.setdefault(n, set()).add(cid)

    ix_map = {}
    cur.execute("SELECT catalog_id, part_number FROM supplier_catalog_interchange")
    for cid, pn in cur.fetchall():
        n = norm(pn)
        if n:
            ix_map.setdefault(n, set()).add(cid)

    mye_map = {}
    cur.execute("""
        SELECT catalog_id, model_year_engine_id FROM supplier_catalog_compat
        WHERE model_year_engine_id IS NOT NULL
    """)
    for cid, mye in cur.fetchall():
        mye_map.setdefault(cid, set()).add(mye)

    total = len(sku_map) + len(ix_map) + len(mye_map)
    print(f'[master] skus={len(sku_map)} interchange={len(ix_map)} mye_entries={len(mye_map)} '
          f'(total {total})', flush=True)
    return sku_map, ix_map, mye_map


def backfill_tenant(db_name, sku_map, ix_map, mye_map):
    conn = psycopg2.connect(dbname=db_name)
    cur = conn.cursor()

    # Idempotency index (also fixes batch_add_compatibilities' ON CONFLICT)
    cur.execute("""
        CREATE UNIQUE INDEX IF NOT EXISTS ux_compat_inv_mye
        ON inventory_vehicle_compat (inventory_id, model_year_engine_id)
    """)
    conn.commit()

    # Items without any compat row yet
    cur.execute("""
        SELECT id, part_number FROM inventory
        WHERE is_active = true
          AND NOT EXISTS (
              SELECT 1 FROM inventory_vehicle_compat c WHERE c.inventory_id = inventory.id)
        ORDER BY id
    """)
    items = cur.fetchall()

    # SKU aliases by inventory_id
    cur.execute("SELECT inventory_id, sku FROM inventory_sku_aliases")
    aliases = {}
    for inv_id, sku in cur.fetchall():
        aliases.setdefault(inv_id, []).append(sku)

    print(f'[{db_name}] items sin compat: {len(items)}', flush=True)

    matched = inserted = 0
    pending = []
    for i, (inv_id, pn) in enumerate(items, start=1):
        mye_ids = set()
        for v in pn_variants(pn, aliases.get(inv_id, [])):
            for cid in (sku_map.get(v) or set()) | (ix_map.get(v) or set()):
                mye_ids |= mye_map.get(cid) or set()
            if len(mye_ids) > 500:
                mye_ids = set(list(mye_ids)[:500])
                break
        if mye_ids:
            matched += 1
            for mye in mye_ids:
                pending.append((inv_id, mye, 'supplier_catalog', 0.95))

        if len(pending) >= BATCH_COMMIT:
            execute_values(cur, """
                INSERT INTO inventory_vehicle_compat
                    (inventory_id, model_year_engine_id, source, confidence)
                VALUES %s ON CONFLICT (inventory_id, model_year_engine_id) DO NOTHING
            """, pending)
            conn.commit()
            inserted += len(pending)
            pending = []

        if i % 5000 == 0:
            print(f'  {i}/{len(items)} (matcheados={matched})', flush=True)

    if pending:
        execute_values(cur, """
            INSERT INTO inventory_vehicle_compat
                (inventory_id, model_year_engine_id, source, confidence)
            VALUES %s ON CONFLICT (inventory_id, model_year_engine_id) DO NOTHING
        """, pending)
        conn.commit()
        inserted += len(pending)

    cur.execute("SELECT count(*) FROM inventory_vehicle_compat")
    total = cur.fetchone()[0]
    cur.execute("""
        SELECT count(*) FROM inventory i
        WHERE i.is_active AND EXISTS (
            SELECT 1 FROM inventory_vehicle_compat c WHERE c.inventory_id = i.id)
    """)
    covered = cur.fetchone()[0]
    cur.execute("SELECT count(*) FROM inventory WHERE is_active")
    active = cur.fetchone()[0]

    print(f'[{db_name}] matcheados={matched} filas_insertadas={inserted} | '
          f'cobertura total: {covered}/{active} ({100 * covered // max(active, 1)}%)',
          flush=True)
    conn.close()


def main():
    target = sys.argv[1] if len(sys.argv) > 1 else 'all'
    mconn = psycopg2.connect(dbname=MASTER_DB)
    mcur = mconn.cursor()
    sku_map, ix_map, mye_map = load_master_mapping(mcur)

    if target == 'all':
        mcur.execute("SELECT db_name FROM tenants WHERE is_active = true ORDER BY id")
        dbs = [r[0] for r in mcur.fetchall()]
    else:
        dbs = [target]

    for db in dbs:
        try:
            backfill_tenant(db, sku_map, ix_map, mye_map)
        except Exception as e:
            print(f'[{db}] ERROR: {e}', flush=True)
    mcur.close()
    mconn.close()
    print('Backfill completado.', flush=True)


if __name__ == '__main__':
    main()