#!/usr/bin/env python3
"""One-time catalog enrichment: re-extract model and category from existing offer titles.

Does NOT re-fetch from eBay. Only updates metadata in catalog.db using improved extractors.
"""

import sqlite3
import sys
from pathlib import Path

sys.path.insert(0, str(Path(__file__).resolve().parent.parent / "src"))

from agent_samochodowy.matcher.catalog import (
    _extract_brand_from_title,
    _extract_category_from_title,
    _extract_model_from_title,
)


def main() -> None:
    db_path = Path(__file__).resolve().parent.parent / "data" / "catalog.db"
    conn = sqlite3.connect(str(db_path))
    conn.row_factory = sqlite3.Row

    rows = conn.execute("SELECT offer_id, title, brand, model, category FROM offers").fetchall()
    print(f"Total offers: {len(rows)}")

    model_gained = 0
    model_changed = 0
    category_gained = 0
    category_changed = 0

    updates = []
    for row in rows:
        offer_id = row["offer_id"]
        title = row["title"]
        old_brand = row["brand"]
        old_model = row["model"]
        old_category = row["category"]

        # Re-extract brand if missing
        brand = old_brand or _extract_brand_from_title(title)

        # Re-extract model using (possibly new) brand
        new_model = _extract_model_from_title(title, brand)
        if new_model and not old_model:
            model_gained += 1
        elif new_model and old_model and new_model != old_model:
            model_changed += 1
            # Keep existing model if it's more specific (longer)
            if len(old_model) > len(new_model):
                new_model = old_model

        # Re-extract category if missing
        new_category = _extract_category_from_title(title)
        if new_category and not old_category:
            category_gained += 1
        elif new_category and old_category and new_category != old_category:
            category_changed += 1
            new_category = old_category  # keep existing if already set

        # Only update if something changed
        final_model = new_model or old_model
        final_category = new_category or old_category

        if final_model != old_model or final_category != old_category:
            updates.append((final_model, final_category, offer_id))

    print(f"\nModel:    gained={model_gained}, changed={model_changed}")
    print(f"Category: gained={category_gained}, changed={category_changed}")
    print(f"Total rows to update: {len(updates)}")

    if updates:
        conn.executemany(
            "UPDATE offers SET model = ?, category = ? WHERE offer_id = ?",
            updates,
        )
        conn.commit()
        print("Database updated.")

    # Rebuild FTS index
    print("Rebuilding FTS5 index...")
    conn.execute("DELETE FROM offers_fts")
    conn.execute("""
        INSERT INTO offers_fts(offer_id, title, category, brand, model, engine_code)
        SELECT offer_id, title, category, brand, model, engine_code
        FROM offers WHERE is_part = 1
    """)
    conn.commit()
    print("FTS5 index rebuilt.")

    # Report final stats
    total = conn.execute("SELECT COUNT(*) FROM offers").fetchone()[0]
    with_model = conn.execute("SELECT COUNT(*) FROM offers WHERE model IS NOT NULL AND model != ''").fetchone()[0]
    with_category = conn.execute("SELECT COUNT(*) FROM offers WHERE category IS NOT NULL AND category != ''").fetchone()[0]
    print(f"\nFinal: {with_model}/{total} ({100*with_model/total:.0f}%) with model, "
          f"{with_category}/{total} ({100*with_category/total:.0f}%) with category")

    # Per-brand report
    print("\nPer brand:")
    for brand in ["DAF", "VOLVO", "MAN", "SCANIA", "MERCEDES", "IVECO", "RENAULT"]:
        tot = conn.execute("SELECT COUNT(*) FROM offers WHERE UPPER(brand)=?", (brand,)).fetchone()[0]
        mod = conn.execute(
            "SELECT COUNT(*) FROM offers WHERE UPPER(brand)=? AND model IS NOT NULL AND model != ''",
            (brand,),
        ).fetchone()[0]
        cat = conn.execute(
            "SELECT COUNT(*) FROM offers WHERE UPPER(brand)=? AND category IS NOT NULL AND category != ''",
            (brand,),
        ).fetchone()[0]
        print(f"  {brand:12s} total={tot:5d}  model={mod:4d} ({100*mod/tot:.0f}%)  category={cat:4d} ({100*cat/tot:.0f}%)")

    conn.close()


if __name__ == "__main__":
    main()