#!/usr/bin/env python3
"""
Compare Tandoor foods between two SQLite snapshots (previous vs current).
Detects: new foods, deleted foods, modified properties, new fdc_ids.

Usage:
    python3 compare_tandoor_db.py [--previous PATH] [--current PATH]

Exits:
    0 = no changes
    1 = changes detected (prints JSON summary to stdout)
"""
import sqlite3
import json
import os
import sys
import argparse
from datetime import datetime, timezone

SCRIPT_DIR = os.path.dirname(os.path.abspath(__file__))
SKILL_DIR  = os.path.dirname(SCRIPT_DIR)

# ── defaults ───────────────────────────────────────────────────────────────
DEFAULT_PREV = os.path.join(SKILL_DIR, "tandoor_foods_previous.db")
DEFAULT_CURR = os.path.join(SKILL_DIR, "tandoor_foods_current.db")


def load_foods(db_path):
    """Load all foods with their properties into a dict keyed by food_id."""
    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


def diff_properties(old_props, new_props, threshold=0.01):
    """Return list of property changes above threshold."""
    changes = []
    all_keys = set(old_props.keys()) | set(new_props.keys())
    for k in sorted(all_keys):
        ov = old_props.get(k)
        nv = new_props.get(k)
        if ov is None and nv is not None:
            changes.append({"prop": k, "old": None, "new": nv})
        elif ov is not None and nv is None:
            changes.append({"prop": k, "old": ov, "new": None})
        elif ov is not None and nv is not None:
            if abs(ov - nv) > threshold:
                changes.append({"prop": k, "old": ov, "new": nv, "delta": round(nv - ov, 4)})
    return changes


def main():
    ap = argparse.ArgumentParser()
    ap.add_argument("--previous", default=DEFAULT_PREV)
    ap.add_argument("--current",  default=DEFAULT_CURR)
    ap.add_argument("--threshold", type=float, default=0.01)
    ap.add_argument("--json", action="store_true", help="Output raw JSON")
    args = ap.parse_args()

    prev = load_foods(args.previous)
    curr = load_foods(args.current)

    if not prev and not curr:
        print("⚠️  No previous or current database found.", file=sys.stderr)
        sys.exit(0)

    if not prev:
        print("⚠️  No previous snapshot — treating everything as new.", file=sys.stderr)

    # ── compute differences ─────────────────────────────────────────────────
    prev_ids = set(prev.keys())
    curr_ids = set(curr.keys())

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

    for fid in (prev_ids & curr_ids):
        p = prev[fid]
        c = curr[fid]
        changes = {}

        # Check metadata 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)}

        # Check property changes
        prop_changes = diff_properties(p.get("properties", {}), c.get("properties", {}), args.threshold)
        if prop_changes:
            changes["properties"] = prop_changes

        if changes:
            modified.append({
                "id": fid,
                "name": c["name"],
                "changes": changes
            })

    # ── build report ────────────────────────────────────────────────────────
    report = {
        "timestamp": datetime.now(timezone.utc).isoformat(),
        "previous_db": args.previous,
        "current_db": args.current,
        "previous_count": len(prev),
        "current_count": len(curr),
        "added": [{"id": f["id"], "name": f["name"], "fdc_id": f.get("fdc_id")} for f in added],
        "removed": [{"id": f["id"], "name": f["name"]} for f in removed],
        "modified": modified,
    }

    has_changes = bool(added or removed or modified)

    if args.json:
        print(json.dumps(report, indent=2, ensure_ascii=False))
        sys.exit(1 if has_changes else 0)

    # ── human readable ──────────────────────────────────────────────────────
    print("=" * 60)
    print("🌅  TANDOOR MORNING SYNC REPORT")
    print("=" * 60)
    print(f"Previous: {len(prev)} Lebensmittel  |  Current: {len(curr)} Lebensmittel")
    print(f"Zeit:     {report['timestamp'][:19]}")

    if not has_changes:
        print("\n✅ Keine Änderungen seit letztem Check.")
        sys.exit(0)

    if added:
        print(f"\n➕ NEU ({len(added)}):")
        for f in added:
            print(f"   • {f['name']} (fdc_id={f.get('fdc_id')})")

    if removed:
        print(f"\n➖ GELÖSCHT ({len(removed)}):")
        for f in removed:
            print(f"   • {f['name']}")

    if modified:
        print(f"\n✏️  GEÄNDERT ({len(modified)}):")
        for m in modified:
            print(f"\n   📌 {m['name']} (id={m['id']})")
            ch = m["changes"]
            for field, diff in ch.items():
                if field == "properties":
                    print(f"      Eigenschaften:")
                    for pc in diff:
                        if pc["old"] is None:
                            print(f"        + {pc['prop']}: {pc['new']}")
                        elif pc["new"] is None:
                            print(f"        − {pc['prop']}: war {pc['old']}")
                        else:
                            sign = "↑" if pc["delta"] > 0 else "↓"
                            print(f"        {sign} {pc['prop']}: {pc['old']} → {pc['new']} (Δ {pc['delta']:+.3f})")
                else:
                    print(f"      {field}: {diff['old']} → {diff['new']}")

    # Also print compact JSON as last line for programmatic consumption
    print(f"\n📊 JSON_SUMMARY: {json.dumps(report, ensure_ascii=False)}")
    sys.exit(1)


if __name__ == "__main__":
    main()