"""
Build food_barcodes.db  –  local offline barcode database for Pantrio
==========================================================================
Source : Open Food Facts (ODbL 1.0) – https://world.openfoodfacts.org/data
Filter : countries_en contains Germany / Austria / Switzerland
Output : pantrio_flutter/assets/food_barcodes.db  (~15-25 MB)

Run once; takes ~5-10 min depending on connection speed.
"""

import csv
import gzip
csv.field_size_limit(10_000_000)  # allow very large fields in OFF CSV
import io
import os
import sqlite3
import sys
import urllib.request
import ssl

URL     = "https://static.openfoodfacts.org/data/en.openfoodfacts.org.products.csv.gz"
OUT_DB  = os.path.join(os.path.dirname(__file__),
                       "ZutatenChef", "Resources", "food_barcodes.db")

COUNTRIES = {"germany", "austria", "switzerland",
             "deutschland", "österreich", "schweiz"}

# ── helpers ──────────────────────────────────────────────────────────────────

def is_dach(countries_en: str) -> bool:
    low = countries_en.lower()
    return any(c in low for c in COUNTRIES)

def clean_name(raw: str) -> str:
    name = raw.strip()
    if len(name) < 2:
        return ""
    # Skip rows that are clearly numeric-only or very short garbage
    if name.isdigit():
        return ""
    return name

def fmt_mb(n_bytes: int) -> str:
    return f"{n_bytes / 1024 / 1024:.1f} MB"

# ── main ─────────────────────────────────────────────────────────────────────

def main() -> None:
    os.makedirs(os.path.dirname(OUT_DB), exist_ok=True)

    # Remove old DB
    if os.path.exists(OUT_DB):
        os.remove(OUT_DB)

    conn = sqlite3.connect(OUT_DB)
    conn.execute("PRAGMA journal_mode=WAL")
    conn.execute("PRAGMA synchronous=NORMAL")
    # brand und quantity kommen direkt aus dem Export. Bis 2026-08 wurden sie
    # verworfen, und die App hat beides GERATEN: stripBrand() schnitt den
    # Markennamen heuristisch vom Produktnamen ab, UnitInferenceService leitete
    # die Einheit aus dem Namen ab. Gemessen im Export: brands bei 79,0 % der
    # DACH-Produkte gesetzt, quantity bei 63,6 %. Gelesene Daten schlagen
    # geratene.
    conn.execute(
        "CREATE TABLE products "
        "(barcode TEXT PRIMARY KEY, name TEXT NOT NULL, "
        " brand TEXT, quantity TEXT)"
    )
    conn.execute("CREATE TABLE meta (schluessel TEXT PRIMARY KEY, wert TEXT NOT NULL)")

    ctx = ssl.create_default_context()
    req = urllib.request.Request(URL, headers={"User-Agent": "Pantrio/1.0 db-builder"})

    print(f"Downloading & filtering {URL}")
    print("(streaming – no full download needed)\n")

    total = kept = skipped = 0
    batch: list[tuple[str, str, str | None, str | None]] = []

    with urllib.request.urlopen(req, timeout=60, context=ctx) as resp:
        with gzip.GzipFile(fileobj=resp) as gz:
            reader = csv.DictReader(
                io.TextIOWrapper(gz, encoding="utf-8", errors="replace"),
                delimiter="\t",
            )
            for row in reader:
                total += 1

                # Progress every 100k rows
                if total % 100_000 == 0:
                    db_size = os.path.getsize(OUT_DB) if os.path.exists(OUT_DB) else 0
                    print(f"  Processed {total:,}  |  kept {kept:,}  |  DB {fmt_mb(db_size)}")
                    sys.stdout.flush()

                # Country filter
                if not is_dach(row.get("countries_en", "")):
                    skipped += 1
                    continue

                barcode = row.get("code", "").strip()
                if not barcode or not barcode.isdigit():
                    skipped += 1
                    continue

                name = clean_name(
                    row.get("product_name") or row.get("generic_name") or ""
                )
                if not name:
                    skipped += 1
                    continue

                marke = (row.get("brands") or "").split(",")[0].strip()
                menge = (row.get("quantity") or "").strip()
                # Offensichtlicher Muell raus: sehr lange Freitextfelder sind in
                # OFF fast immer Fehleingaben und blaehen die Datei nur auf.
                if len(marke) > 60: marke = ""
                if len(menge) > 30: menge = ""
                batch.append((barcode, name, marke or None, menge or None))
                kept += 1

                if len(batch) >= 2000:
                    conn.executemany(
                        "INSERT OR REPLACE INTO products VALUES (?, ?, ?, ?)", batch
                    )
                    conn.commit()
                    batch.clear()

    # Lizenzangaben IN die Datenbank schreiben. ODbL 4.2 b verlangt den
    # Lizenzhinweis ausdruecklich auch in der abgeleiteten Datenbank selbst,
    # nicht nur in der Doku daneben.
    import datetime as _dt
    conn.executemany("INSERT OR REPLACE INTO meta VALUES (?,?)", [
        ("quelle",            "Open Food Facts — https://world.openfoodfacts.org/data"),
        ("lizenz",            "Open Database License (ODbL) v1.0"),
        ("lizenz_uri",        "https://opendatacommons.org/licenses/odbl/1-0/"),
        ("inhalte_lizenz",    "Database Contents License (DbCL) v1.0"),
        ("inhalte_lizenz_uri","https://opendatacommons.org/licenses/dbcl/1-0/"),
        ("namensnennung",     "Contains information from Open Food Facts, which is made "
                              "available here under the Open Database License (ODbL)."),
        ("abgeleitet_von",    "en.openfoodfacts.org.products.csv.gz"),
        ("bearbeitung",       "Gefiltert auf countries_en = Deutschland/Oesterreich/Schweiz, "
                              "Spalten code, product_name, brands, quantity uebernommen, "
                              "nach SQLite umgewandelt. Erzeugendes Skript: build_food_db.py"),
        ("abgeleitete_db",    "https://vorinoapp.de/opendata/"),
        ("erzeugt_von",       "Vorino (Marcel Soendenaa-Defourny)"),
        ("stand",             _dt.date.today().isoformat()),
    ])
    conn.commit()

    # Flush remainder
    if batch:
        conn.executemany("INSERT OR REPLACE INTO products VALUES (?, ?, ?, ?)", batch)
        conn.commit()

    # Index + vacuum for smaller file
    print("\nCreating index and compacting…")
    # KEIN eigener Index auf barcode: die Spalte ist bereits PRIMARY KEY, SQLite
    # legt dafuer automatisch sqlite_autoindex_products_1 an. Ein zusaetzlicher
    # Index waere eine exakte Dublette und kostete 9,8 MiB im App-Paket — gemessen
    # 2026-08-02 per dbstat an der ausgelieferten Datei (40,0 -> 29,8 MB nach
    # DROP INDEX + VACUUM, Abfrageplan unveraendert).
    conn.execute("VACUUM")
    conn.close()

    final_mb = os.path.getsize(OUT_DB) / 1024 / 1024
    print(f"\nDone!")
    print(f"  Products kept : {kept:,}")
    print(f"  DB size       : {final_mb:.1f} MB")
    print(f"  Saved to      : {OUT_DB}")

if __name__ == "__main__":
    main()