#!/usr/bin/env python3
"""
Backfill PostgreSQL brand tables with `barcode` and `barcode_type` from seed catalogs.
This ensures existing products with barcode details in seed files or DB rows are populated and served to the API and UI.
"""
from __future__ import annotations

import json
import logging
import sys
from pathlib import Path

sys.path.insert(0, str(Path(__file__).resolve().parents[1]))

from app.services.vector_store import _connect, list_available_brands, _sanitize_name, resolve_parent_brand, ensure_brand_schema

logging.basicConfig(level=logging.INFO, format="%(asctime)s - %(levelname)s - %(message)s")
logger = logging.getLogger(__name__)

SEED_DIR = Path(__file__).resolve().parents[1] / "data" / "seed_catalogs"


def backfill_barcodes() -> None:
    conn = _connect()
    if not conn:
        logger.error("Could not connect to PostgreSQL database.")
        return

    brands = list_available_brands()
    logger.info("Ensuring schema for %d brand(s)...", len(brands))
    for brand in brands:
        ensure_brand_schema(brand)

    seed_files = sorted(SEED_DIR.glob("*.json")) if SEED_DIR.exists() else []
    logger.info("Found %d seed file(s) in %s", len(seed_files), SEED_DIR)

    updated_count = 0
    with conn.cursor() as cur:
        for seed_file in seed_files:
            try:
                data = json.loads(seed_file.read_text(encoding="utf-8-sig"))
            except Exception as e:
                logger.warning("Could not read seed file %s: %s", seed_file.name, e)
                continue

            products = data.get("products", [])
            brand = data.get("brand") or (products[0].get("brand_name") if products else None)
            if not brand or not products:
                continue

            table_name = f"brand_{_sanitize_name(resolve_parent_brand(brand))}"

            for p in products:
                image_id = p.get("image_id") or p.get("sku") or ""
                product_name = p.get("product_name") or p.get("title") or ""
                barcode = str(p.get("barcode") or p.get("Barcode") or "").strip() or None
                barcode_type = str(p.get("barcode_type") or p.get("Barcode_Type") or "").strip() or None

                if not barcode and not barcode_type:
                    continue

                if image_id:
                    cur.execute(
                        f"""
                        UPDATE {table_name}
                        SET barcode = COALESCE(%s, barcode),
                            barcode_type = COALESCE(%s, barcode_type)
                        WHERE image_id = %s
                        """,
                        (barcode, barcode_type, image_id)
                    )
                    if cur.rowcount > 0:
                        updated_count += cur.rowcount
                elif product_name:
                    cur.execute(
                        f"""
                        UPDATE {table_name}
                        SET barcode = COALESCE(%s, barcode),
                            barcode_type = COALESCE(%s, barcode_type)
                        WHERE product_name = %s
                        """,
                        (barcode, barcode_type, product_name)
                    )
                    if cur.rowcount > 0:
                        updated_count += cur.rowcount

        conn.commit()
    conn.close()
    logger.info("🎉 Barcode backfill complete! Updated %d product row(s).", updated_count)


if __name__ == "__main__":
    backfill_barcodes()