#!/usr/bin/env python3
"""Personal food tolerance and histamine mapping helper.

Examples:
  nutrition_tolerance.py list-open
  nutrition_tolerance.py set-tolerance --food "Joghurt" --status unclear --notes "testweise beobachten"
  nutrition_tolerance.py map-alias --alias "feta" --canonical "Feta" --score 2 --category "Milchprodukt" --tags fermented

No raw YAZIO credentials or private payloads are read here; this only updates local
HealthManager DB mapping metadata and optionally refreshes insights/dashboard.
"""
from __future__ import annotations

import argparse
import sqlite3
import subprocess
import sys
from pathlib import Path

BASE = Path('/home/agent/.hermes/assets/Gesundheit')
DB = BASE / 'health_data.db'
INSIGHTS = BASE / 'scripts' / 'nutrition_insights.py'
PIPELINE = BASE / 'scripts' / 'health_pipeline.py'


def conn() -> sqlite3.Connection:
    c = sqlite3.connect(DB)
    c.row_factory = sqlite3.Row
    return c


def ensure_schema(c: sqlite3.Connection) -> None:
    c.executescript('''
    CREATE TABLE IF NOT EXISTS personal_food_tolerance (
        canonical_food TEXT PRIMARY KEY,
        personal_status TEXT NOT NULL DEFAULT 'unknown' CHECK(personal_status IN ('safe','problematic','unclear','unknown')),
        evidence_level TEXT DEFAULT 'low' CHECK(evidence_level IN ('low','medium','high')),
        notes TEXT,
        updated_at TEXT DEFAULT CURRENT_TIMESTAMP
    );
    CREATE TABLE IF NOT EXISTS nutrition_review_queue (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        normalized_name TEXT NOT NULL UNIQUE,
        example_name TEXT NOT NULL,
        occurrence_count INTEGER DEFAULT 0,
        first_seen TEXT,
        last_seen TEXT,
        suggested_canonical_food TEXT,
        suggested_score INTEGER,
        reason TEXT,
        status TEXT DEFAULT 'open' CHECK(status IN ('open','mapped','ignored')),
        updated_at TEXT DEFAULT CURRENT_TIMESTAMP
    );
    ''')


def refresh(dashboard: bool) -> None:
    subprocess.run([sys.executable, str(INSIGHTS)], check=True)
    if dashboard:
        subprocess.run([sys.executable, str(PIPELINE), 'dashboard'], check=True)


def list_open(limit: int) -> None:
    c = conn()
    try:
        ensure_schema(c)
        rows = list(c.execute('''SELECT example_name, occurrence_count, first_seen, last_seen, reason
                                 FROM nutrition_review_queue WHERE status='open'
                                 ORDER BY occurrence_count DESC,last_seen DESC LIMIT ?''', (limit,)))
        if not rows:
            print('Keine offenen Mapping-Reviews.')
            return
        for r in rows:
            print(f"{r['occurrence_count']:>3}x | {r['first_seen']}..{r['last_seen']} | {r['example_name']} | {r['reason']}")
    finally:
        c.close()


def set_tolerance(food: str, status: str, evidence: str, notes: str | None, dashboard: bool) -> None:
    c = conn()
    try:
        ensure_schema(c)
        c.execute('''INSERT INTO personal_food_tolerance(canonical_food,personal_status,evidence_level,notes,updated_at)
                     VALUES(?,?,?,?,CURRENT_TIMESTAMP)
                     ON CONFLICT(canonical_food) DO UPDATE SET personal_status=excluded.personal_status,
                         evidence_level=excluded.evidence_level, notes=excluded.notes, updated_at=CURRENT_TIMESTAMP''',
                  (food, status, evidence, notes or ''))
        c.commit()
    finally:
        c.close()
    refresh(dashboard)
    print(f'Personal tolerance gesetzt: {food} -> {status} ({evidence})')


def map_alias(alias: str, canonical: str, score: int, category: str | None, tags: str | None, notes: str | None, confidence: str, dashboard: bool) -> None:
    c = conn()
    try:
        ensure_schema(c)
        c.execute('''INSERT INTO histamine_food_rules(canonical_food,sighi_score,category,tags,notes,confidence,updated_at)
                     VALUES(?,?,?,?,?,?,CURRENT_TIMESTAMP)
                     ON CONFLICT(canonical_food) DO UPDATE SET sighi_score=excluded.sighi_score,
                         category=excluded.category, tags=excluded.tags, notes=excluded.notes,
                         confidence=excluded.confidence, updated_at=CURRENT_TIMESTAMP''',
                  (canonical, score, category or '', tags or '', notes or '', confidence))
        c.execute('INSERT OR REPLACE INTO histamine_food_aliases(alias, canonical_food) VALUES(?,?)', (alias, canonical))
        # Mark matching review row as mapped where possible.
        c.execute("UPDATE nutrition_review_queue SET status='mapped', suggested_canonical_food=?, suggested_score=?, updated_at=CURRENT_TIMESTAMP WHERE lower(example_name)=lower(?) OR normalized_name=lower(?)",
                  (canonical, score, alias, alias))
        c.commit()
    finally:
        c.close()
    refresh(dashboard)
    print(f'Alias gemappt: {alias} -> {canonical} (SIGHi {score})')


def main() -> int:
    ap = argparse.ArgumentParser()
    sub = ap.add_subparsers(dest='cmd', required=True)
    p_list = sub.add_parser('list-open'); p_list.add_argument('--limit', type=int, default=30)
    p_tol = sub.add_parser('set-tolerance')
    p_tol.add_argument('--food', required=True)
    p_tol.add_argument('--status', choices=['safe','problematic','unclear','unknown'], required=True)
    p_tol.add_argument('--evidence', choices=['low','medium','high'], default='low')
    p_tol.add_argument('--notes')
    p_tol.add_argument('--dashboard', action='store_true')
    p_map = sub.add_parser('map-alias')
    p_map.add_argument('--alias', required=True)
    p_map.add_argument('--canonical', required=True)
    p_map.add_argument('--score', type=int, choices=[0,1,2,3], required=True)
    p_map.add_argument('--category')
    p_map.add_argument('--tags')
    p_map.add_argument('--notes')
    p_map.add_argument('--confidence', choices=['low','medium','high'], default='medium')
    p_map.add_argument('--dashboard', action='store_true')
    args = ap.parse_args()
    if args.cmd == 'list-open':
        list_open(args.limit)
    elif args.cmd == 'set-tolerance':
        set_tolerance(args.food, args.status, args.evidence, args.notes, args.dashboard)
    elif args.cmd == 'map-alias':
        map_alias(args.alias, args.canonical, args.score, args.category, args.tags, args.notes, args.confidence, args.dashboard)
    return 0


if __name__ == '__main__':
    raise SystemExit(main())