#!/usr/bin/env python3 """ Tandoor FDC→BLS Mapping Sync Liest alle Tandoor-Zutaten mit fdc_id, prüft die Mapping-DB, holt fehlende FDC-Metadaten, validiert BLS-Codes. """ import sqlite3 import json import os import sys import urllib.request import urllib.error import time # ── Pfade ─────────────────────────────────────────────────────────────────── SKILL_DIR = "/home/jarvis/.openclaw/workspace/skills/tandoor-manager" WORKSPACE = "/home/jarvis/.openclaw/workspace" TANDOOR_DB = os.path.join(SKILL_DIR, "tandoor_foods.db") MAPPING_DB = os.path.join(SKILL_DIR, "fdc_bls_mapping.db") BLS_DB = os.path.join(WORKSPACE, "skills/bls-manager/data/BLS_4_0.db") SECRETS = os.path.join(WORKSPACE, ".secrets") # ── FDC API Key ───────────────────────────────────────────────────────────── FDC_API_KEY = None with open(SECRETS) as f: for line in f: line = line.strip() if line.startswith("FDC_API_KEY="): FDC_API_KEY = line.split("=", 1)[1].strip().strip('"') break if not FDC_API_KEY: print("❌ FDC_API_KEY nicht in .secrets gefunden") sys.exit(1) # ── FDC API Helper ────────────────────────────────────────────────────────── FDC_BASE = "https://api.nal.usda.gov/fdc/v1" def fdc_food(fdc_id, retries=2): """Holt Food-Details von FDC API.""" url = f"{FDC_BASE}/food/{fdc_id}?api_key={FDC_API_KEY}" for attempt in range(retries + 1): try: req = urllib.request.Request(url, headers={"Accept": "application/json"}) with urllib.request.urlopen(req, timeout=15) as resp: return json.loads(resp.read()) except urllib.error.HTTPError as e: if e.code == 429: print(f" ⚠ Rate limit für fdc_id={fdc_id}, warte 60s...") time.sleep(60) continue elif e.code == 404: print(f" ⚠ FDC ID {fdc_id} nicht gefunden (404)") return None else: print(f" ⚠ HTTP {e.code} für fdc_id={fdc_id}") return None except Exception as e: if attempt < retries: time.sleep(2) continue print(f" ⚠ Fehler für fdc_id={fdc_id}: {e}") return None return None # ── Datenbank-Setup ───────────────────────────────────────────────────────── conn_map = sqlite3.connect(MAPPING_DB) conn_map.row_factory = sqlite3.Row cur_map = conn_map.cursor() conn_tan = sqlite3.connect(TANDOOR_DB) cur_tan = conn_tan.cursor() conn_bls = sqlite3.connect(BLS_DB) cur_bls = conn_bls.cursor() # ── BLS-Codes laden (für Validierung) ─────────────────────────────────────── cur_bls.execute("SELECT \"BLS Code\" FROM bls_daten") valid_bls_codes = set(row[0].strip() for row in cur_bls.fetchall() if row[0]) print(f"[1] BLS 4.0 geladen: {len(valid_bls_codes)} gültige Codes") # ── Tandoor-Zutaten mit fdc_id ────────────────────────────────────────────── cur_tan.execute("SELECT id, name, fdc_id FROM foods WHERE fdc_id IS NOT NULL ORDER BY name") tandoor_foods = cur_tan.fetchall() print(f"[2] Tandoor-Zutaten mit fdc_id: {len(tandoor_foods)}") # ── Bestehende Mappings laden ─────────────────────────────────────────────── cur_map.execute("SELECT fdc_id, bls_code, match_type, fdc_desc FROM fdc_bls_map") existing = {row["fdc_id"]: dict(row) for row in cur_map.fetchall()} print(f"[3] Bestehende Mappings: {len(existing)}") # ── Stats ─────────────────────────────────────────────────────────────────── stats = { "new_entries": 0, "updated_to_unmapped": 0, "bls_validated": 0, "fdc_api_calls": 0, "fdc_errors": 0, "unchanged": 0, } print("\n[4] Verarbeite Zutaten...\n") def process_food(tid, tname, fdc_id): global stats entry = existing.get(fdc_id) # FDC-Beschreibung holen (wenn fehlend oder unbekannt) fdc_desc = None if entry and entry.get("fdc_desc"): fdc_desc = entry["fdc_desc"] else: print(f" ● {tname} (fdc_id={fdc_id}) → FDC API...") fdc_data = fdc_food(fdc_id) stats["fdc_api_calls"] += 1 if fdc_data: fdc_desc = fdc_data.get("description", fdc_data.get("briefDescription", "")) else: stats["fdc_errors"] += 1 fdc_desc = f"Tandoor: {tname}" if stats["fdc_api_calls"] % 10 == 0: time.sleep(1) # Rate limit freundlich if not entry: # ── NEU: Noch nicht in Mapping-DB ────────────────────────────────── cur_map.execute( "INSERT INTO fdc_bls_map (fdc_id, bls_code, match_type, fdc_desc, bls_desc, confidence, notes) " "VALUES (?, NULL, 'unmapped', ?, NULL, 0.0, 'Auto: keine Zuordnung möglich') ON CONFLICT(fdc_id) DO NOTHING", (fdc_id, fdc_desc or f"Tandoor: {tname}") ) print(f" ➕ NEU eingetragen als 'unmapped' (FDC: {fdc_desc or 'n/a'})") stats["new_entries"] += 1 else: bls_code = entry.get("bls_code") match_type = entry.get("match_type") # ── VALIDIERE BLS-Code ───────────────────────────────────────────── if bls_code is None or bls_code.strip() == "": # Kein BLS-Code zugewiesen → unmapped if match_type != "unmapped": cur_map.execute( "UPDATE fdc_bls_map SET match_type='unmapped', confidence=0.0, " "notes='Auto: kein BLS-Code zugewiesen', updated_at=datetime('now') " "WHERE fdc_id=?", (fdc_id,) ) print(f" 🔄 {tname}: Kein BLS-Code → auf 'unmapped' gesetzt") stats["updated_to_unmapped"] += 1 else: stats["unchanged"] += 1 elif bls_code not in valid_bls_codes: # Ungültiger BLS-Code → unmapped print(f" 🔄 {tname}: BLS-Code '{bls_code}' ungültig → auf 'unmapped' gesetzt") cur_map.execute( "UPDATE fdc_bls_map SET bls_code=NULL, match_type='unmapped', confidence=0.0, " "notes='Auto: BLS-Code nicht in BLS 4.0 gefunden', updated_at=datetime('now') " "WHERE fdc_id=?", (fdc_id,) ) stats["updated_to_unmapped"] += 1 else: # BLS-Code gültig → OK # Optional: fdc_desc aktualisieren falls fehlend if not entry.get("fdc_desc") and fdc_desc: cur_map.execute( "UPDATE fdc_bls_map SET fdc_desc=?, updated_at=datetime('now') WHERE fdc_id=?", (fdc_desc, fdc_id) ) stats["bls_validated"] += 1 # ── Haupt-Loop ────────────────────────────────────────────────────────────── for tid, tname, fdc_id in tandoor_foods: process_food(tid, tname, fdc_id) if stats["fdc_api_calls"] > 0 and stats["fdc_api_calls"] % 50 == 0: print(f"\n ... {stats['fdc_api_calls']} FDC-API-Calls gemacht, kurze Pause...\n") time.sleep(2) conn_map.commit() # ── Finaler Report ────────────────────────────────────────────────────────── print("\n" + "="*60) print("SYNC-REPORT") print("="*60) print(f" Neue Einträge: {stats['new_entries']}") print(f" Auf 'unmapped' gesetzt: {stats['updated_to_unmapped']}") print(f" BLS gültig bestätigt: {stats['bls_validated']}") print(f" Unverändert: {stats['unchanged']}") print(f" FDC API Calls: {stats['fdc_api_calls']}") print(f" FDC API Fehler: {stats['fdc_errors']}") # Zusammenfassung print("\n[5] Aktueller Status in Mapping-DB:") cur_map.execute("SELECT match_type, COUNT(*) FROM fdc_bls_map GROUP BY match_type") for row in cur_map.fetchall(): print(f" {row[0] or 'None':12s}: {row[1]:4d}") cur_map.execute("SELECT COUNT(*) FROM fdc_bls_map WHERE bls_code IS NULL") no_bls = cur_map.fetchone()[0] print(f"\n Ohne BLS-Zuordnung: {no_bls}") conn_map.close() conn_tan.close() conn_bls.close() print("\n✅ Sync abgeschlossen.")