#!/usr/bin/env python3
"""
Tandoor Morning Check — Full Pipeline
=====================================
1. Diff Tandoor foods (previous vs current)
2. Validate FDC IDs (exist they? do names match?)
3. Check Mapping DB consistency (Tandoor ↔ FDC ↔ BLS)
4. Compare nutrition properties against BLS (via mapping) and FDC API

Outputs a beautiful morning report.
"""
import sqlite3
import json
import os
import sys
import urllib.request
import urllib.error
import time
from datetime import datetime, timezone

# ── paths ──────────────────────────────────────────────────────────────────
SCRIPT_DIR = os.path.dirname(os.path.abspath(__file__))
SKILL_DIR  = os.path.dirname(SCRIPT_DIR)
WORKSPACE  = os.path.normpath(os.path.join(SKILL_DIR, "../.."))

DB_PATH        = os.path.join(SKILL_DIR, "tandoor_foods.db")
PREV_PATH      = os.path.join(SKILL_DIR, "tandoor_foods_previous.db")
CURR_PATH      = os.path.join(SKILL_DIR, "tandoor_foods_current.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 ────────────────────────────────────────────────────────────────
FDC_API_KEY = None
if os.path.exists(SECRETS):
    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

FDC_BASE = "https://api.nal.usda.gov/fdc/v1"
_fdc_cache = {}


def fdc_food(fdc_id, retries=2):
    """Fetch food details from FDC API with caching."""
    if fdc_id in _fdc_cache:
        return _fdc_cache[fdc_id]
    if not FDC_API_KEY:
        return None
    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:
                data = json.loads(resp.read())
                _fdc_cache[fdc_id] = data
                return data
        except urllib.error.HTTPError as e:
            if e.code == 429:
                print(f"      ⚠ Rate limit für fdc_id={fdc_id}, warte 30s...", file=sys.stderr)
                time.sleep(30)
                continue
            elif e.code == 404:
                _fdc_cache[fdc_id] = {"_error": "404", "_not_found": True}
                return _fdc_cache[fdc_id]
            else:
                _fdc_cache[fdc_id] = {"_error": str(e.code)}
                return _fdc_cache[fdc_id]
        except Exception as e:
            if attempt < retries:
                time.sleep(2)
                continue
            _fdc_cache[fdc_id] = {"_error": str(e)}
            return _fdc_cache[fdc_id]
    return None


def load_foods(db_path):
    """Load foods with properties from SQLite."""
    if not os.path.exists(db_path):
        return {}
    conn = sqlite3.connect(db_path)
    conn.row_factory = sqlite3.Row
    cur = conn.cursor()
    cur.execute("SELECT * FROM foods")
    foods = {}
    for row in cur.fetchall():
        d = dict(row)
        d["properties"] = {}
        foods[d["id"]] = d
    cur.execute("""
        SELECT p.food_id, pt.name, p.property_amount
        FROM properties p
        JOIN property_types pt ON pt.id = p.property_type_id
    """)
    for row in cur.fetchall():
        fid = row[0]
        if fid in foods:
            foods[fid]["properties"][row[1]] = row[2]
    conn.close()
    return foods


# ── SECTION 1: Diff ────────────────────────────────────────────────────────
def section_diff():
    prev = load_foods(PREV_PATH)
    curr = load_foods(CURR_PATH)

    if not prev:
        return {"status": "no_previous", "added": list(curr.values()), "removed": [], "modified": []}

    prev_ids = set(prev.keys())
    curr_ids = set(curr.keys())

    added   = [curr[fid] for fid in sorted(curr_ids - prev_ids)]
    removed = [prev[fid] for fid in sorted(prev_ids - curr_ids)]
    modified = []

    for fid in sorted(prev_ids & curr_ids):
        p, c = prev[fid], curr[fid]
        changes = {}
        for field in ("name", "description", "fdc_id", "food_type", "parent_id"):
            if p.get(field) != c.get(field):
                changes[field] = {"old": p.get(field), "new": c.get(field)}
        # properties
        all_props = set(p.get("properties", {}).keys()) | set(c.get("properties", {}).keys())
        prop_changes = []
        for k in sorted(all_props):
            ov = p.get("properties", {}).get(k)
            nv = c.get("properties", {}).get(k)
            if ov != nv and (ov is None or nv is None or abs(ov - nv) > 0.01):
                prop_changes.append({"prop": k, "old": ov, "new": nv})
        if prop_changes:
            changes["properties"] = prop_changes
        if changes:
            modified.append({"id": fid, "name": c["name"], "changes": changes})

    return {
        "status": "ok",
        "added": added,
        "removed": removed,
        "modified": modified,
    }


# ── SECTION 2: FDC Validation ─────────────────────────────────────────────-
def section_fdc_validation(curr_foods):
    """Check if Tandoor fdc_ids are valid and names match."""
    results = []
    suspicious = []

    for fid, food in sorted(curr_foods.items(), key=lambda x: x[1]["name"]):
        fdc_id = food.get("fdc_id")
        if not fdc_id:
            continue

        fdc_data = fdc_food(fdc_id)
        if fdc_data is None:
            results.append({"tandoor_id": fid, "name": food["name"], "fdc_id": fdc_id,
                            "status": "api_error", "detail": "FDC API nicht erreichbar oder kein Key"})
            continue

        if fdc_data.get("_not_found"):
            results.append({"tandoor_id": fid, "name": food["name"], "fdc_id": fdc_id,
                            "status": "not_found", "detail": "FDC ID existiert nicht"})
            continue

        fdc_name = fdc_data.get("description") or fdc_data.get("briefDescription") or ""
        t_name = food["name"].lower()
        f_name = fdc_name.lower()

        # Simple name similarity check
        name_match = t_name in f_name or f_name in t_name or any(w in f_name for w in t_name.split())

        status = "ok" if name_match else "suspicious"
        item = {
            "tandoor_id": fid,
            "name": food["name"],
            "fdc_id": fdc_id,
            "fdc_name": fdc_name[:60],
            "status": status,
        }
        results.append(item)
        if status == "suspicious":
            suspicious.append(item)

        # rate limit friendly
        if len(_fdc_cache) % 10 == 0:
            time.sleep(0.5)

    return {"results": results, "suspicious": suspicious}


# ── SECTION 3: Mapping Consistency ─────────────────────────────────────────
def section_mapping_consistency(curr_foods):
    """Check Tandoor ↔ FDC ↔ BLS mapping consistency."""
    conn_m = sqlite3.connect(MAPPING_DB)
    cur_m = conn_m.cursor()
    cur_m.execute("SELECT fdc_id, bls_code, match_type, fdc_desc FROM fdc_bls_map")
    mappings = {}
    for row in cur_m.fetchall():
        mappings[row[0]] = {"bls_code": row[1], "match_type": row[2], "fdc_desc": row[3]}
    conn_m.close()

    conn_b = sqlite3.connect(BLS_DB)
    cur_b = conn_b.cursor()
    cur_b.execute('SELECT "BLS Code" FROM bls_daten')
    valid_bls = set(r[0] for r in cur_b.fetchall() if r[0])
    conn_b.close()

    unmapped = []
    mapped_ok = []
    mapped_invalid = []
    not_in_mapping = []

    for fid, food in sorted(curr_foods.items(), key=lambda x: x[1]["name"]):
        fdc_id = food.get("fdc_id")
        if not fdc_id:
            continue

        m = mappings.get(fdc_id)
        if m is None:
            not_in_mapping.append({"tandoor_id": fid, "name": food["name"], "fdc_id": fdc_id})
        elif not m.get("bls_code"):
            unmapped.append({"tandoor_id": fid, "name": food["name"], "fdc_id": fdc_id,
                             "fdc_desc": m.get("fdc_desc", "")})
        elif m["bls_code"] not in valid_bls:
            mapped_invalid.append({"tandoor_id": fid, "name": food["name"], "fdc_id": fdc_id,
                                   "bls_code": m["bls_code"]})
        else:
            mapped_ok.append({"tandoor_id": fid, "name": food["name"], "fdc_id": fdc_id,
                              "bls_code": m["bls_code"], "match_type": m["match_type"]})

    return {
        "not_in_mapping": not_in_mapping,
        "unmapped": unmapped,
        "mapped_invalid": mapped_invalid,
        "mapped_ok": mapped_ok,
        "stats": {
            "total_foods_with_fdc": len([f for f in curr_foods.values() if f.get("fdc_id")]),
            "not_in_mapping": len(not_in_mapping),
            "unmapped": len(unmapped),
            "mapped_invalid": len(mapped_invalid),
            "mapped_ok": len(mapped_ok),
        }
    }


# ── SECTION 4: Nutrition Comparison ────────────────────────────────────────

def get_unit_conversion_factor(from_unit, to_unit):
    """Return factor to convert from BLS unit to Tandoor/FDC unit."""
    if from_unit == to_unit:
        return 1.0
    # Normalize units
    unit_to_mg = {
        'g': 1000.0,
        'mg': 1.0,
        'µg': 0.001,
        'ug': 0.001,
        'kcal': 1.0,
        'kJ': 1.0,
    }
    from_base = unit_to_mg.get(from_unit)
    to_base = unit_to_mg.get(to_unit)
    if from_base is None or to_base is None:
        return None
    return from_base / to_base


def load_property_mappings():
    """Load property type mappings with units from DB."""
    conn = sqlite3.connect(MAPPING_DB)
    conn.row_factory = sqlite3.Row
    cur = conn.cursor()
    cur.execute("""
        SELECT 
            tandoor_property_type_id,
            tandoor_name,
            fdc_id,
            fdc_unit,
            bls_code,
            bls_unit,
            match_type
        FROM property_type_mappings
        WHERE bls_code IS NOT NULL AND match_type != 'not_in_bls'
    """)
    mappings = {}
    for row in cur.fetchall():
        mappings[row['tandoor_name']] = {
            'bls_code': row['bls_code'],
            'fdc_unit': row['fdc_unit'],
            'bls_unit': row['bls_unit'],
        }
    conn.close()
    return mappings


def resolve_bls_col(all_cols, bls_code):
    """Find BLS column by exact code prefix."""
    # bls_code like 'ENERCC' should match 'ENERCC Energie (Kilokalorien) [...]'
    for col in all_cols:
        if col.startswith(bls_code + ' ') or col == bls_code:
            return col
    return None



def section_nutrition_check(curr_foods, mapping_data):
    """Compare Tandoor nutrition values against BLS via verified mapping,
    with automatic unit conversion from property_type_mappings."""
    prop_mappings = load_property_mappings()

    # ── Load BLS data ──────────────────────────────────────────────────────
    conn_b = sqlite3.connect(BLS_DB)
    cur_b = conn_b.cursor()
    cur_b.execute("SELECT * FROM bls_daten")
    all_rows = cur_b.fetchall()
    cur_b.execute("SELECT * FROM bls_daten LIMIT 1")
    all_cols = [d[0] for d in cur_b.description]
    conn_b.close()

    bls_by_code = {}
    for row in all_rows:
        rec = dict(zip(all_cols, row))
        code = rec.get("BLS Code", "")
        if code:
            bls_by_code[code] = rec

    # Resolve BLS columns once
    col_map = {}
    for t_name, info in prop_mappings.items():
        bls_code = info['bls_code']
        col = resolve_bls_col(all_cols, bls_code) if bls_code else None
        if col:
            col_map[t_name] = {'col': col, 'info': info}

    # ── Build verified food→BLS mapping ────────────────────────────────────
    conn_m = sqlite3.connect(MAPPING_DB)
    cur_m = conn_m.cursor()
    cur_m.execute("SELECT fdc_id, bls_code FROM fdc_bls_map WHERE bls_code IS NOT NULL")
    verified = {r[0]: r[1] for r in cur_m.fetchall()}
    conn_m.close()

    # ── tolerance config ───────────────────────────────────────────────────
    TOLERANCE = {
        'Energie (Kilokalorien)': 5.0,
        'Energie (Kilojoule)': 20.0,
        'Protein': 1.0,
        'Fett': 1.0,
        'Kohlenhydrate': 2.0,
        'Ballaststoffe': 1.0,
        'Wasser': 5.0,
        'Zucker': 1.0,
        'default': 0.5,
    }

    diffs = []
    missing_bls = []
    tandoor_zero = []
    unit_conversions = []

    for fid, food in sorted(curr_foods.items(), key=lambda x: x[1]["name"]):
        fdc_id = food.get("fdc_id")
        if not fdc_id or fdc_id not in verified:
            continue

        bls_code = verified[fdc_id]
        bls_rec = bls_by_code.get(bls_code)
        if not bls_rec:
            continue

        props = food.get("properties", {})
        for t_name, mapping_info in col_map.items():
            t_val = props.get(t_name)
            if t_val is None:
                continue

            # Get BLS raw value
            col_name = mapping_info['col']
            bls_raw = bls_rec.get(col_name)
            if bls_raw is None or str(bls_raw).strip() in ("-", "", "0"):
                continue
            try:
                bls_val = float(bls_raw)
            except (ValueError, TypeError):
                continue

            # ── UNIT CONVERSION ───────────────────────────────────────────
            bls_unit = mapping_info['info']['bls_unit']
            tandoor_unit = mapping_info['info']['fdc_unit']  # Tandoor = FDC unit

            converted_bls_val = bls_val
            conversion_applied = False
            if bls_unit and tandoor_unit and bls_unit != tandoor_unit:
                factor = get_unit_conversion_factor(bls_unit, tandoor_unit)
                if factor is not None:
                    converted_bls_val = bls_val * factor
                    conversion_applied = True
                    unit_conversions.append({
                        'food': food['name'],
                        'prop': t_name,
                        'bls_val': bls_val,
                        'bls_unit': bls_unit,
                        'converted': round(converted_bls_val, 6),
                        'tandoor_unit': tandoor_unit,
                        'factor': factor,
                    })
                else:
                    # Unknown conversion — skip this comparison
                    continue

            if t_val == 0 and converted_bls_val > 0:
                tandoor_zero.append({"food": food["name"], "prop": t_name, "bls_val": round(converted_bls_val, 3)})
                continue

            # Compare with tolerance
            tolerance = TOLERANCE.get(t_name, TOLERANCE['default'])
            if converted_bls_val != 0:
                rel_diff = abs(t_val - converted_bls_val) / abs(converted_bls_val)
                abs_diff = abs(t_val - converted_bls_val)
                if rel_diff > 0.20 and abs_diff > tolerance:
                    diffs.append({
                        "food": food["name"],
                        "bls_food": bls_rec.get("Lebensmittelbezeichnung", "")[:30],
                        "prop": t_name,
                        "tandoor": round(t_val, 3),
                        "bls_raw": round(bls_val, 3),
                        "bls_unit": bls_unit or "?",
                        "converted_bls": round(converted_bls_val, 3),
                        "rel_diff": round(rel_diff * 100, 1),
                        "unit_conv": conversion_applied,
                    })

    diffs.sort(key=lambda x: -x["rel_diff"])
    return {
        "diffs": diffs[:50],
        "missing_bls": missing_bls[:30],
        "tandoor_zero": tandoor_zero[:30],
        "unit_conversions": unit_conversions,
        "stats": {
            "diffs": len(diffs),
            "missing_in_tandoor": len(missing_bls),
            "tandoor_zero_but_bls_has_data": len(tandoor_zero),
            "unit_conversions_applied": len(unit_conversions),
        }
    }


# ── REPORT FORMATTER ───────────────────────────────────────────────────────
def print_report(diff, fdc_val, mapping, nutrition):
    lines = []
    now = datetime.now(timezone.utc).strftime("%Y-%m-%d %H:%M")

    lines.append("🌅 *TANDOOR MORNING REPORT* — " + now)
    lines.append("=" * 50)

    # ── 1. Changes ─────────────────────────────────────────────────────────
    if diff.get("status") == "no_previous":
        lines.append(f"\n📦 Erster Lauf — {len(diff['added'])} Lebensmittel geladen.")
    elif not diff["added"] and not diff["removed"] and not diff["modified"]:
        lines.append("\n✅ Keine Änderungen seit gestern.")
    else:
        lines.append("\n📊 *ÄNDERUNGEN*")
        if diff["added"]:
            lines.append(f"  ➕ Neu: {len(diff['added'])}")
            for f in diff["added"][:5]:
                lines.append(f"     • {f['name']}")
            if len(diff["added"]) > 5:
                lines.append(f"     ... und {len(diff['added']) - 5} weitere")
        if diff["removed"]:
            lines.append(f"  ➖ Gelöscht: {len(diff['removed'])}")
        if diff["modified"]:
            lines.append(f"  ✏️  Geändert: {len(diff['modified'])}")

    # ── 2. FDC Validation ──────────────────────────────────────────────────
    lines.append("\n🔍 *FDC ID VALIDIERUNG*")
    suspicious = fdc_val.get("suspicious", [])
    not_found = [r for r in fdc_val["results"] if r["status"] == "not_found"]
    if not suspicious and not not_found:
        lines.append("  ✅ Alle FDC IDs gültig und Namen passen.")
    else:
        if not_found:
            lines.append(f"  ❌ {len(not_found)} FDC IDs nicht gefunden:")
            for r in not_found[:5]:
                lines.append(f"     • {r['name']} (fdc_id={r['fdc_id']})")
        if suspicious:
            lines.append(f"  ⚠️  {len(suspicious)} FDC IDs mit suspekter Zuordnung:")
            for r in suspicious[:5]:
                lines.append(f"     • {r['name']} → FDC: '{r['fdc_name']}'")

    # ── 3. Mapping Consistency ─────────────────────────────────────────────
    lines.append("\n🔗 *MAPPING STATUS (Tandoor ↔ FDC ↔ BLS)*")
    s = mapping["stats"]
    lines.append(f"  Total mit fdc_id: {s['total_foods_with_fdc']}")
    lines.append(f"  ✅ Mapped OK:     {s['mapped_ok']}")
    lines.append(f"  🟡 Unmapped:      {s['unmapped']}")
    if s["mapped_invalid"]:
        lines.append(f"  ❌ Invalid BLS:   {s['mapped_invalid']}")
    if s["not_in_mapping"]:
        lines.append(f"  ⚠️  Nicht in DB:  {s['not_in_mapping']}")

    if mapping["unmapped"]:
        lines.append(f"\n  *Unmapped Foods ({len(mapping['unmapped'])}):*")
        for u in mapping["unmapped"][:8]:
            lines.append(f"    • {u['name']} (fdc: {u['fdc_desc'][:30] if u['fdc_desc'] else '?'})")
        if len(mapping["unmapped"]) > 8:
            lines.append(f"    ... und {len(mapping['unmapped']) - 8} weitere")

    # ── 4. Nutrition Check ─────────────────────────────────────────────────
    lines.append("\n🥗 *NÄHRWERT-ABWEICHUNGEN (Tandoor vs BLS)*")
    ns = nutrition["stats"]
    if ns["diffs"] == 0 and ns["tandoor_zero_but_bls_has_data"] == 0:
        lines.append("  ✅ Alles im grünen Bereich.")
    else:
        lines.append(f"  Abweichungen ≥20%:          {ns['diffs']}")
        lines.append(f"  Tandoor=0, BLS hat Daten:   {ns['tandoor_zero_but_bls_has_data']}")
        lines.append(f"  Fehlend in Tandoor:         {ns['missing_in_tandoor']}")

        if nutrition["stats"]["unit_conversions_applied"] > 0:
            lines.append(f"  Einheits-Umrechnungen:      {nutrition['stats']['unit_conversions_applied']}")

        if nutrition["diffs"]:
            lines.append(f"\n  *Größte Abweichungen:*")
            for d in nutrition["diffs"][:8]:
                arrow = "🔴" if d["rel_diff"] > 50 else "🟠"
                conv_note = " (umgerechnet)" if d.get("unit_conv") else ""
                lines.append(f"    {arrow} {d['food'][:20]}: {d['prop']} = {d['tandoor']} (T) vs {d['converted_bls']} (BLS){conv_note}  Δ{d['rel_diff']:.0f}%")

        if nutrition["tandoor_zero"]:
            lines.append(f"\n  *Tandoor=0 aber BLS hat Werte:*")
            for z in nutrition["tandoor_zero"][:5]:
                lines.append(f"    • {z['food'][:20]}: {z['prop']} = {z['bls_val']}")

    # ── Action items ───────────────────────────────────────────────────────
    action_items = []
    if mapping["unmapped"]:
        action_items.append(f"🟡 {len(mapping['unmapped'])} Lebensmittel ohne BLS-Mapping — manuell zuordnen?")
    if nutrition["stats"]["diffs"] > 0:
        action_items.append(f"🟠 {nutrition['stats']['diffs']} Nährwert-Abweichungen prüfen (FDC Sync nötig?)")
    if not_found:
        action_items.append(f"🔴 {len(not_found)} ungültige FDC IDs korrigieren")

    if action_items:
        lines.append("\n📋 *ACTION ITEMS:*")
        for ai in action_items:
            lines.append(f"  {ai}")

    lines.append("\n_Guten Morgen! ☕_")

    return "\n".join(lines)


# ── MAIN ───────────────────────────────────────────────────────────────────
def main():
    # Ensure current DB exists
    if not os.path.exists(CURR_PATH):
        print(f"❌ Keine aktuelle DB gefunden: {CURR_PATH}")
        print(f"   Bitte zuerst export_tandoor_to_sqlite.py ausführen.")
        sys.exit(1)

    curr_foods = load_foods(CURR_PATH)
    print(f"📦 {len(curr_foods)} Lebensmittel geladen aus {CURR_PATH}")

    print("🔍 1/4 Diff-Check ...")
    diff = section_diff()

    print("🔍 2/4 FDC-Validierung ...")
    fdc_val = section_fdc_validation(curr_foods)

    print("🔍 3/4 Mapping-Konsistenz ...")
    mapping = section_mapping_consistency(curr_foods)

    print("🔍 4/4 Nährwert-Vergleich ...")
    nutrition = section_nutrition_check(curr_foods, mapping)

    # Print report
    report = print_report(diff, fdc_val, mapping, nutrition)
    print(report)

    # Also save raw JSON for programmatic use
    raw = {
        "timestamp": datetime.now(timezone.utc).isoformat(),
        "diff": diff,
        "fdc_validation": fdc_val,
        "mapping": mapping,
        "nutrition": nutrition,
    }
    json_path = os.path.join(SKILL_DIR, "morning_report.json")
    with open(json_path, "w", encoding="utf-8") as f:
        json.dump(raw, f, indent=2, ensure_ascii=False, default=str)
    print(f"\n💾 Raw JSON gespeichert: {json_path}")


if __name__ == "__main__":
    main()