Autoparts-DB/scripts/meli_attach_images.py

#!/usr/bin/env python3
"""Attach images to inventory items that are (or will be) published on MeLi.

Fixes publishing block #1: ML requires at least one picture, and Rached (and
most tenants) have zero rows in inventory_images, so publish_items rejects
every item with "El producto no tiene imagen".

Image sources, in priority order:
  1. local  — files already on disk for this tenant:
       /home/Autopartes/pos/static/images/parts/<tenant>_<inventory_id>.jpg
       /home/Autopartes/data/images/parts/<tenant>/<inventory_id>_full.webp
  2. catalog — master supplier_catalog.image_url, joined by part number / SKU
  3. ml      — pictures of the MercadoLibre listing already linked to the item
       (downloaded and re-hosted locally with services.image_service, or, with
        --remote, registered as the ML secure_url directly)

NOTE on the data/ Excel catalogs (VAZLO, YOKOMITSU, KNADIAN, LUK, RAYBESTOS,
KG, PRODUCTOS_RACHED_2026): they were checked and contain NO image columns
(marca / sku / INTERCAMBIO / nombre / carro only), so they cannot supply
images. Also, master supplier_catalog.image_url is empty for all 17k rows
today, so the catalog source is currently a no-op.

Registered images go to inventory_images (sort_order + is_primary; the first
becomes primary) and inventory.image_url is set to the primary one.

Usage (from /home/Autopartes):
    python3 scripts/meli_attach_images.py --tenant 31                 # dry-run
    python3 scripts/meli_attach_images.py --tenant 31 --apply
    python3 scripts/meli_attach_images.py --tenant 31 --apply --remote
    python3 scripts/meli_attach_images.py --tenant 31 --apply --limit 5
    python3 scripts/meli_attach_images.py --tenant 31 --apply --inventory-id 4752
    python3 scripts/meli_attach_images.py --tenant 31 --json
"""

import argparse
import json
import os
import sys

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

STATIC_PARTS_DIR = '/home/Autopartes/pos/static/images/parts'
DATA_PARTS_DIR = '/home/Autopartes/data/images/parts'
MELI_MAX_PICTURES = 10


def _load_env():
    env_file = '/etc/nexus/pos.env'
    if os.environ.get('MASTER_DB_URL') or not os.path.exists(env_file):
        return
    for line in open(env_file):
        line = line.strip()
        if not line or line.startswith('#') or '=' not in line:
            continue
        key, value = line.split('=', 1)
        os.environ.setdefault(key.strip(), value.strip().strip('"').strip("'"))


def _local_urls(tenant_id, inventory_id):
    """Images already on disk for this tenant/item (relative, servable URLs)."""
    urls = []
    scraper = os.path.join(STATIC_PARTS_DIR, f'{tenant_id}_{inventory_id}.jpg')
    if os.path.exists(scraper):
        urls.append(f'/pos/static/images/parts/{tenant_id}_{inventory_id}.jpg')
    processed = os.path.join(STATIC_PARTS_DIR, str(tenant_id), f'{inventory_id}_full.webp')
    if os.path.exists(processed):
        urls.append(f'/pos/static/images/parts/{tenant_id}/{inventory_id}_full.webp')
    return urls


def _catalog_urls(master_cur, skus):
    """supplier_catalog.image_url for any of the given SKUs (non-empty only)."""
    keys = [s for s in (skus or []) if s]
    if not keys:
        return []
    master_cur.execute(
        """
        SELECT DISTINCT image_url FROM supplier_catalog
        WHERE image_url IS NOT NULL AND image_url <> ''
          AND UPPER(sku) = ANY(%s)
        LIMIT %s
        """,
        ([str(k).strip().upper() for k in keys], MELI_MAX_PICTURES),
    )
    return [r[0] for r in master_cur.fetchall()]


def _ml_urls(svc, tenant_id, inventory_id, external_item_id, remote, apply=True):
    """Pictures of the ML listing. Returns candidate URLs for inventory_images.

    The first picture is downloaded and re-hosted locally with
    services.image_service (that service stores one file per item, so the
    primary is self-contained); the remaining pictures keep their ML-hosted
    secure_url, which is public and valid for ML itself.

    In dry-run (apply=False) nothing is downloaded: secure_urls are returned
    as candidates so the caller can report without side effects.
    """
    from services import image_service
    pictures = (svc.get_item(str(external_item_id)).get('pictures') or [])
    urls = []
    for index, pic in enumerate(pictures[:MELI_MAX_PICTURES]):
        remote_url = pic.get('secure_url') or pic.get('url')
        if not remote_url:
            continue
        if remote or not apply or index > 0:
            urls.append(remote_url)
            continue
        try:
            saved = image_service.save_image(tenant_id, inventory_id, image_url=remote_url)
            urls.append(saved['image_url'])
        except Exception as e:
            print(f"    ! could not download {remote_url}: {e}", flush=True)
            urls.append(remote_url)  # fall back to the ML-hosted URL
    return urls


def _register_images(cur, inventory_id, urls, apply):
    """Insert new inventory_images rows (first becomes primary) + set image_url."""
    urls = [u for u in dict.fromkeys(urls) if u]  # dedupe, keep order
    if not urls:
        return 0

    cur.execute(
        "SELECT url, is_primary FROM inventory_images WHERE inventory_id = %s "
        "ORDER BY is_primary DESC, sort_order, id",
        (inventory_id,),
    )
    existing = cur.fetchall()
    have = {r[0] for r in existing}
    has_primary = any(r[1] for r in existing)
    order = len(existing)

    added = 0
    for url in urls:
        if url in have:
            continue
        is_primary = (not has_primary and added == 0)
        if apply:
            cur.execute(
                "INSERT INTO inventory_images (inventory_id, url, sort_order, is_primary) "
                "VALUES (%s, %s, %s, %s)",
                (inventory_id, url, order, is_primary),
            )
        have.add(url)
        added += 1
        order += 1
        if is_primary:
            has_primary = True

    if apply and added:
        cur.execute(
            "SELECT url FROM inventory_images WHERE inventory_id = %s "
            "ORDER BY is_primary DESC, sort_order, id LIMIT 1",
            (inventory_id,),
        )
        row = cur.fetchone()
        if row:
            cur.execute("UPDATE inventory SET image_url = %s WHERE id = %s", (row[0], inventory_id))
    return added


def main():
    ap = argparse.ArgumentParser(description=__doc__,
                                 formatter_class=argparse.RawDescriptionHelpFormatter)
    ap.add_argument('--tenant', type=int, required=True, help='tenant id (e.g. 31 = Rached)')
    ap.add_argument('--apply', action='store_true', help='write to inventory_images (default: dry-run)')
    ap.add_argument('--source', choices=['auto', 'local', 'catalog', 'ml'], default='auto',
                    help='restrict to one source (default: auto = first available)')
    ap.add_argument('--remote', action='store_true',
                    help='register the MeLi secure_url directly instead of downloading it')
    ap.add_argument('--limit', type=int, default=None, help='max items to process')
    ap.add_argument('--inventory-id', type=int, action='append', dest='inventory_ids',
                    help='only this inventory id (repeatable)')
    ap.add_argument('--json', action='store_true', help='print the summary as JSON')
    args = ap.parse_args()

    _load_env()

    from tenant_db import get_tenant_conn, get_master_conn
    from services import marketplace_external_service as meli

    conn = get_tenant_conn(args.tenant)
    master = get_master_conn()

    try:
        cur = conn.cursor()
        cfg = meli.get_meli_config(conn)
        svc = meli._get_meli_service(cfg, conn)

        # Targets: explicit inventory ids, or every mapped ML listing.
        if args.inventory_ids:
            cur.execute(
                """
                SELECT inventory_id, MAX(external_item_id)
                FROM marketplace_listings
                WHERE channel = 'mercadolibre' AND inventory_id = ANY(%s)
                GROUP BY inventory_id
                ORDER BY inventory_id
                """,
                (args.inventory_ids,),
            )
            targets = cur.fetchall()
            # Inventory ids with no listing yet still deserve an image.
            seen = {t[0] for t in targets}
            targets += [(iid, None) for iid in args.inventory_ids if iid not in seen]
        else:
            cur.execute(
                """
                SELECT inventory_id, MAX(external_item_id)
                FROM marketplace_listings
                WHERE channel = 'mercadolibre' AND inventory_id IS NOT NULL
                GROUP BY inventory_id
                ORDER BY inventory_id
                """
            )
            targets = cur.fetchall()

        if args.limit:
            targets = targets[:args.limit]

        master_cur = master.cursor()
        summary = {"tenant": args.tenant, "dry_run": not args.apply,
                   "targets": len(targets), "with_images": 0, "no_image": 0,
                   "images_added": 0, "sources": {"local": 0, "catalog": 0, "ml": 0},
                   "no_image_items": [], "details": []}

        if not targets:
            print("No targets: no marketplace listing is linked to inventory yet.")
            return 0

        for inventory_id, external_item_id in targets:
            cur.execute(
                "SELECT part_number FROM inventory WHERE id = %s", (inventory_id,)
            )
            row = cur.fetchone()
            part_number = row[0] if row else None

            urls, used = [], None
            want = lambda s: args.source in ('auto', s)  # noqa: E731

            if want('local'):
                local = _local_urls(args.tenant, inventory_id)
                if local:
                    urls, used = local, 'local'
            if not urls and want('catalog'):
                catalog = _catalog_urls(master_cur, [part_number])
                if catalog:
                    urls, used = catalog, 'catalog'
            if not urls and want('ml') and external_item_id and svc:
                try:
                    ml = _ml_urls(svc, args.tenant, inventory_id, external_item_id,
                                  args.remote, apply=args.apply)
                    if ml:
                        urls, used = ml, 'ml'
                except Exception as e:
                    print(f"  [{inventory_id}] ML image fetch failed: {e}", flush=True)

            if not urls:
                summary['no_image'] += 1
                summary['no_image_items'].append(inventory_id)
                summary['details'].append({
                    "inventory_id": inventory_id, "part_number": part_number,
                    "status": "no_image",
                })
                continue

            added = _register_images(cur, inventory_id, urls, args.apply)
            if args.apply:
                conn.commit()
            summary['with_images'] += 1
            summary['images_added'] += added
            summary['sources'][used] = summary['sources'].get(used, 0) + 1
            summary['details'].append({
                "inventory_id": inventory_id, "part_number": part_number,
                "status": "ok", "source": used, "found": len(urls), "added": added,
            })

        master_cur.close()
        cur.close()
    finally:
        master.close()
        conn.close()

    if args.json:
        print(json.dumps(summary, indent=2, ensure_ascii=False, default=str))
        return 0

    mode = 'DRY-RUN (no writes)' if summary['dry_run'] else 'APPLIED'
    print(f"[tenant {args.tenant}] attach images — {mode}")
    print(f"  targets:        {summary['targets']}")
    print(f"  with images:    {summary['with_images']}  (by source: {summary['sources']})")
    print(f"  images added:   {summary['images_added']}")
    print(f"  no image found: {summary['no_image']}")
    if summary['no_image_items']:
        print(f"    inventory ids without a usable image: {summary['no_image_items']}")
    if summary['dry_run'] and summary['images_added'] >= 0:
        print("\n  Re-run with --apply to persist the images in inventory_images.")
    return 0


if __name__ == '__main__':
    sys.exit(main())