#!/usr/bin/env python3
"""
gen_dashboard.py — Tandoor Nutrition Dashboard Generator
Generates a self-contained HTML dashboard with food detail modal.
"""
import json
import os
import sys
import sqlite3
import urllib.request
import time

# ── paths ──────────────────────────────────────────────────────────────────
SCRIPT_DIR = os.path.dirname(os.path.abspath(__file__))
SKILL_DIR  = SCRIPT_DIR  # script lives directly in skill dir
WORKSPACE  = os.path.normpath(os.path.join(SKILL_DIR, "../.."))

REPORT_JSON    = os.path.join(SKILL_DIR, 'morning_report.json')
DB_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')
CACHE_FILE     = os.path.join(SKILL_DIR, 'fdc_cache.json')
OUT_HTML       = os.path.join(SKILL_DIR, 'dashboard.html')
SECRETS        = os.path.join(WORKSPACE, '.secrets')

# ── FDC API key ────────────────────────────────────────────────────────────
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'

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

def load_cache():
    if os.path.exists(CACHE_FILE):
        with open(CACHE_FILE) as f:
            return json.load(f)
    return {}

def save_cache(cache):
    with open(CACHE_FILE, 'w') as f:
        json.dump(cache, f, indent=2)

def fdc_food(fdc_id, cache, retries=2):
    sid = str(fdc_id)
    if sid in cache:
        return cache[sid]
    if not FDC_API_KEY:
        return None
    url = f'{FDC_BASE}/food/{sid}?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())
                cache[sid] = data
                time.sleep(0.3)
                return data
        except urllib.error.HTTPError as e:
            if e.code == 429:
                print(f'  ⚠ Rate limit fdc_id={sid}, warte 30s...', file=sys.stderr)
                time.sleep(30)
                continue
            elif e.code == 404:
                cache[sid] = {'_error': '404', '_not_found': True}
                return cache[sid]
            else:
                cache[sid] = {'_error': str(e.code)}
                return cache[sid]
        except Exception as e:
            if attempt < retries:
                time.sleep(2)
                continue
            cache[sid] = {'_error': str(e)}
            return cache[sid]
    return None

def load_foods():
    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 load_property_mappings():
    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']] = {
            'fdc_nutrient_id': row['fdc_id'],
            'fdc_unit': row['fdc_unit'],
            'bls_code': row['bls_code'],
            'bls_unit': row['bls_unit'],
        }
    conn.close()
    return mappings

def load_bls_data():
    conn = sqlite3.connect(BLS_DB)
    cur = conn.cursor()
    cur.execute('SELECT * FROM bls_daten')
    rows = cur.fetchall()
    cols = [d[0] for d in cur.description]
    conn.close()
    bls_by_code = {}
    for row in rows:
        rec = dict(zip(cols, row))
        code = rec.get('BLS Code', '')
        if code:
            bls_by_code[code] = rec
    return bls_by_code, cols

def resolve_bls_col(all_cols, bls_code):
    for col in all_cols:
        if col.startswith(bls_code + ' ') or col == bls_code:
            return col
    return None

def get_fdc_nutrient(fdc_data, nutrient_id):
    if not fdc_data or fdc_data.get('_not_found'):
        return None
    for fn in fdc_data.get('foodNutrients', []):
        nid = fn.get('nutrient', {}).get('id') if isinstance(fn.get('nutrient'), dict) else fn.get('nutrientId')
        if nid == nutrient_id:
            amt = fn.get('amount') if 'amount' in fn else fn.get('value')
            return amt
    return None

# ── HTML parts ─────────────────────────────────────────────────────────────

HTML_TOP = '''<!DOCTYPE html>
<html lang="de">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Tandoor Nährwert Dashboard</title>
<style>
:root { --bg:#0f172a; --card:#1e293b; --text:#e2e8f0; --accent:#38bdf8; --ok:#22c55e; --warn:#eab308; --err:#ef4444; }
* { box-sizing:border-box; margin:0; padding:0; }
body { font-family:system-ui,-apple-system,sans-serif; background:var(--bg); color:var(--text); line-height:1.5; padding:20px; }
h1 { text-align:center; margin-bottom:10px; }
.subtitle { text-align:center; color:#94a3b8; margin-bottom:30px; }
.kpis { display:grid; grid-template-columns:repeat(auto-fit,minmax(150px,1fr)); gap:15px; margin-bottom:30px; }
.kpi { background:var(--card); padding:20px; border-radius:12px; text-align:center; }
.kpi .val { font-size:2rem; font-weight:bold; color:var(--accent); }
.kpi .lbl { color:#94a3b8; font-size:.85rem; margin-top:5px; }
.card { background:var(--card); border-radius:12px; padding:20px; margin-bottom:20px; }
.card h2 { margin-bottom:15px; font-size:1.1rem; display:flex; align-items:center; gap:8px; }
table { width:100%; border-collapse:collapse; font-size:.9rem; }
th { text-align:left; padding:10px; background:rgba(255,255,255,.05); position:sticky; top:0; }
td { padding:8px 10px; border-bottom:1px solid rgba(255,255,255,.05); }
tr:hover { background:rgba(255,255,255,.03); }
.ok { color:var(--ok); }
.warn { color:var(--warn); }
.err { color:var(--err); }
.filter { width:100%; padding:10px; margin-bottom:15px; background:rgba(255,255,255,.05); border:1px solid rgba(255,255,255,.1); color:var(--text); border-radius:6px; }
.scroll { max-height:400px; overflow:auto; }
.food-link { color:var(--accent); cursor:pointer; text-decoration:underline; }
.food-link:hover { color:#7dd3fc; }

/* Modal */
.modal-overlay { display:none; position:fixed; inset:0; background:rgba(0,0,0,.7); z-index:1000; align-items:center; justify-content:center; }
.modal-overlay.active { display:flex; }
.modal { background:var(--card); border-radius:12px; width:90%; max-width:900px; max-height:85vh; overflow:hidden; display:flex; flex-direction:column; }
.modal-header { padding:20px; border-bottom:1px solid rgba(255,255,255,.1); display:flex; justify-content:space-between; align-items:center; }
.modal-header h3 { margin:0; color:var(--accent); font-size:1.2rem; }
.modal-close { background:none; border:none; color:var(--text); font-size:1.5rem; cursor:pointer; }
.modal-close:hover { color:var(--err); }
.modal-body { padding:20px; overflow:auto; }
.modal-table th { position:sticky; top:0; }
.modal-table td { text-align:right; }
.modal-table td:first-child { text-align:left; }
.missing { color:#64748b; font-style:italic; }
</style>
</head>
<body>
<h1>🍳 Tandoor Nährwert Dashboard</h1>
<p class="subtitle">'''

HTML_MODAL = '''
<div class="modal-overlay" id="detailModal">
<div class="modal">
  <div class="modal-header">
    <h3 id="modalTitle">Food Details</h3>
    <button class="modal-close" onclick="closeModal()">&times;</button>
  </div>
  <div class="modal-body">
    <table class="modal-table">
      <thead><tr><th>Eigenschaft</th><th>Tandoor</th><th>FDC</th><th>BLS</th></tr></thead>
      <tbody id="modalBody"></tbody>
    </table>
  </div>
</div>
</div>
'''

HTML_SCRIPT = '''
<script>
function filterTable(id,filter) {
  const rows = document.getElementById(id).getElementsByTagName('tr');
  for(let r of rows) r.style.display = r.textContent.toLowerCase().includes(filter.toLowerCase())?'':'none';
}
function openModal(foodId) {
  const food = foodData[foodId];
  if(!food) return;
  document.getElementById('modalTitle').textContent = food.name;
  const tbody = document.getElementById('modalBody');
  tbody.innerHTML = '';
  for(const row of food.props) {
    const tr = document.createElement('tr');
    tr.innerHTML = '<td><b>'+row.name+'</b></td><td>'+(row.tandoor!=null?row.tandoor+' '+row.unit:'<span class="missing">—</span>')+'</td><td>'+(row.fdc!=null?row.fdc+' '+row.unit:'<span class="missing">—</span>')+'</td><td>'+(row.bls!=null?row.bls+' '+row.unit:'<span class="missing">—</span>')+'</td>';
    tbody.appendChild(tr);
  }
  document.getElementById('detailModal').classList.add('active');
}
function closeModal() {
  document.getElementById('detailModal').classList.remove('active');
}
document.getElementById('detailModal').addEventListener('click', function(e) {
  if(e.target === this) closeModal();
});
document.addEventListener('keydown', function(e) {
  if(e.key === 'Escape') closeModal();
});
</script>
</body>
</html>
'''

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

def main():
    print('Loading report...', file=sys.stderr)
    with open(REPORT_JSON) as f:
        data = json.load(f)

    # ── Extract sections (FIXED KEYS) ──────────────────────────────────────
    nutri = data.get('nutrition', {})
    diffs = nutri.get('diffs', [])
    unit_convs = nutri.get('unit_conversions', [])
    tandoor_zero = nutri.get('tandoor_zero', [])
    missing = nutri.get('missing_bls', [])

    mapping = data.get('mapping', {})
    unmapped = mapping.get('unmapped', [])
    mapped_invalid = mapping.get('mapped_invalid', [])
    not_in_mapping = mapping.get('not_in_mapping', [])

    fdc = data.get('fdc_validation', {})
    fdc_results = fdc.get('results', [])
    fdc_suspicious = fdc.get('suspicious', [])

    ts = data.get('timestamp', '')[:19]

    # ── Load databases ─────────────────────────────────────────────────────
    print('Loading Tandoor foods...', file=sys.stderr)
    foods = load_foods()

    print('Loading property mappings...', file=sys.stderr)
    prop_mappings = load_property_mappings()

    print('Loading BLS data...', file=sys.stderr)
    bls_by_code, bls_cols = load_bls_data()
    col_map = {}
    for t_name, info in prop_mappings.items():
        col = resolve_bls_col(bls_cols, info['bls_code']) if info['bls_code'] else None
        if col:
            col_map[t_name] = col

    print('Loading FDC cache...', file=sys.stderr)
    cache = load_cache()

    # ── Build foodData JS object ───────────────────────────────────────────
    print('Building food detail data...', file=sys.stderr)
    food_data_js = {}

    # Pre-fetch FDC data for all foods that have fdc_id
    fdc_ids_to_fetch = set()
    for fid, food in foods.items():
        fdc_id = food.get('fdc_id')
        if fdc_id and str(fdc_id) not in cache:
            fdc_ids_to_fetch.add(fdc_id)

    if fdc_ids_to_fetch:
        print(f'Fetching {len(fdc_ids_to_fetch)} FDC foods...', file=sys.stderr)
        for i, fdc_id in enumerate(sorted(fdc_ids_to_fetch), 1):
            fdc_food(fdc_id, cache)
            if i % 10 == 0:
                print(f'  {i}/{len(fdc_ids_to_fetch)}...', file=sys.stderr)
                save_cache(cache)
        save_cache(cache)
        print('FDC cache saved.', file=sys.stderr)

    # Build mapping from fdc_id -> bls_code
    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()

    # Name -> ID lookup
    name_to_id = {f['name']: fid for fid, f in foods.items()}

    for fid, food in foods.items():
        fdc_id = food.get('fdc_id')
        bls_code = verified.get(fdc_id) if fdc_id else None
        bls_rec = bls_by_code.get(bls_code) if bls_code else None
        fdc_data = cache.get(str(fdc_id)) if fdc_id else None

        props = []
        for t_name in sorted(prop_mappings.keys()):
            info = prop_mappings[t_name]
            t_val = food.get('properties', {}).get(t_name)

            # FDC value
            fdc_val = None
            if fdc_data and not fdc_data.get('_not_found'):
                fdc_val = get_fdc_nutrient(fdc_data, info['fdc_nutrient_id'])

            # BLS value
            bls_val = None
            if bls_rec and t_name in col_map:
                bls_raw = bls_rec.get(col_map[t_name])
                if bls_raw is not None and str(bls_raw).strip() not in ('-', ''):
                    try:
                        bls_val = float(bls_raw)
                    except (ValueError, TypeError):
                        pass

            props.append({
                'name': t_name,
                'unit': info.get('fdc_unit') or info.get('bls_unit') or '',
                'tandoor': round(t_val, 3) if t_val is not None else None,
                'fdc': round(fdc_val, 3) if fdc_val is not None else None,
                'bls': round(bls_val, 3) if bls_val is not None else None,
            })

        food_data_js[fid] = {
            'name': food.get('name', ''),
            'props': props,
        }

    # ── Build HTML ─────────────────────────────────────────────────────────
    html = HTML_TOP + ts + '''</p>

<div class="kpis">
  <div class="kpi"><div class="val">''' + str(len(diffs)) + '''</div><div class="lbl">Abweichungen<br>≥20%</div></div>
  <div class="kpi"><div class="val">''' + str(len(unmapped)+len(not_in_mapping)) + '''</div><div class="lbl">Unmapped<br>Foods</div></div>
  <div class="kpi"><div class="val">''' + str(len(tandoor_zero)) + '''</div><div class="lbl">Tandoor=0<br>BLS hat Werte</div></div>
  <div class="kpi"><div class="val">''' + str(len(unit_convs)) + '''</div><div class="lbl">Einheits-<br>Umrechnungen</div></div>
  <div class="kpi"><div class="val">''' + str(len(fdc_suspicious)) + '''</div><div class="lbl">Suspicious<br>FDC IDs</div></div>
</div>

<div class="card">
  <h2>🔴 Top Abweichungen</h2>
  <div class="scroll">
  <table>
    <thead><tr><th>Food</th><th>BLS Food</th><th>Nährstoff</th><th>Tandoor</th><th>BLS</th><th>Δ</th></tr></thead>
    <tbody>
'''

    for d in diffs[:50]:
        fname = d.get('food', '')
        fid = name_to_id.get(fname, '')
        html += f'<tr><td><span class="food-link" onclick="openModal({fid})">{fname}</span></td><td>{d.get("bls_food","")}</td><td>{d.get("prop","")}</td><td>{d.get("tandoor","")}</td><td>{d.get("converted_bls","")}</td><td class="err">{d.get("rel_diff","")}%</td></tr>\n'

    html += '''    </tbody>
  </table>
  </div>
</div>

<div class="card">
  <h2>🟡 Unmapped Foods (''' + str(len(unmapped)+len(not_in_mapping)) + ''')</h2>
  <input type="text" class="filter" placeholder="Filter..." onkeyup="filterTable('unmapped',this.value)">
  <div class="scroll">
  <table>
    <thead><tr><th>Name</th><th>FDC ID</th><th>Typ</th></tr></thead>
    <tbody id="unmapped">
'''

    for u in unmapped:
        fname = u.get('name', '')
        fid = u.get('tandoor_id', '')
        html += f'<tr><td><span class="food-link" onclick="openModal({fid})">{fname}</span></td><td>{u.get("fdc_id","")}</td><td class="warn">Unmapped</td></tr>\n'
    for u in not_in_mapping:
        fname = u.get('name', '')
        fid = u.get('tandoor_id', '')
        html += f'<tr><td><span class="food-link" onclick="openModal({fid})">{fname}</span></td><td>{u.get("fdc_id","")}</td><td class="warn">Nicht in DB</td></tr>\n'

    html += '''    </tbody>
  </table>
  </div>
</div>

<div class="card">
  <h2>🟠 FDC Validierung (''' + str(len(fdc_results)) + ''')</h2>
  <div class="scroll">
  <table>
    <thead><tr><th>Name</th><th>FDC ID</th><th>FDC Name</th><th>Status</th></tr></thead>
    <tbody>
'''

    for r in fdc_results:
        cls = 'ok' if r.get('status')=='ok' else 'warn'
        fname = r.get('name', '')
        fid = r.get('tandoor_id', '')
        html += f'<tr><td><span class="food-link" onclick="openModal({fid})">{fname}</span></td><td>{r.get("fdc_id","")}</td><td>{r.get("fdc_name","")[:50]}</td><td class="{cls}">{r.get("status","")}</td></tr>\n'

    html += '''    </tbody>
  </table>
  </div>
</div>

<div class="card">
  <h2>⚪ Tandoor=0, BLS hat Werte (''' + str(len(tandoor_zero)) + ''')</h2>
  <div class="scroll">
  <table>
    <thead><tr><th>Food</th><th>Nährstoff</th><th>BLS Wert</th></tr></thead>
    <tbody>
'''

    for z in tandoor_zero[:100]:
        fname = z.get('food', '')
        # find food_id
        fid = ''
        for f_id, f in foods.items():
            if f['name'] == fname:
                fid = f_id
                break
        html += f'<tr><td><span class="food-link" onclick="openModal({fid})">{fname}</span></td><td>{z.get("prop","")}</td><td>{z.get("bls_val","")}</td></tr>\n'

    html += '''    </tbody>
  </table>
  </div>
</div>
'''

    # ── Add diff section ───────────────────────────────────────────────────
    diff_data = data.get('diff', {})
    added = diff_data.get('added', [])
    removed = diff_data.get('removed', [])
    modified = diff_data.get('modified', [])

    if added or removed or modified:
        html += '''
<div class="card">
  <h2>📊 Änderungen seit letztem Check</h2>
  <div class="scroll">
'''
        if added:
            html += '<h3 style="margin:10px 0 5px;color:var(--ok)">+ Neu hinzugefügt</h3><table><thead><tr><th>Name</th><th>Typ</th></tr></thead><tbody>'
            for a in added:
                fname = a.get('name', '')
                fid = a.get('id', '')
                html += f'<tr><td><span class="food-link" onclick="openModal({fid})">{fname}</span></td><td>{a.get("food_type","")}</td></tr>'
            html += '</tbody></table>'
        if removed:
            html += '<h3 style="margin:15px 0 5px;color:var(--err)">− Entfernt</h3><table><thead><tr><th>Name</th></tr></thead><tbody>'
            for r in removed:
                fname = r.get('name', '')
                fid = r.get('id', '')
                html += f'<tr><td><span class="food-link" onclick="openModal({fid})">{fname}</span></td></tr>'
            html += '</tbody></table>'
        if modified:
            html += '<h3 style="margin:15px 0 5px;color:var(--warn)">✎ Geändert</h3><table><thead><tr><th>Name</th><th>Feld</th><th>Alt</th><th>Neu</th></tr></thead><tbody>'
            for m in modified:
                fname = m.get('name', '')
                fid = m.get('id', '')
                changes = m.get('changes', {})
                for field, vals in changes.items():
                    if field == 'properties':
                        for pc in vals:
                            html += f'<tr><td><span class="food-link" onclick="openModal({fid})">{fname}</span></td><td>{pc.get("prop","")}</td><td>{pc.get("old","")}</td><td>{pc.get("new","")}</td></tr>'
                    else:
                        html += f'<tr><td><span class="food-link" onclick="openModal({fid})">{fname}</span></td><td>{field}</td><td>{vals.get("old","")}</td><td>{vals.get("new","")}</td></tr>'
            html += '</tbody></table>'
        html += '</div></div>'

    # ── Embed food data + modal + script ───────────────────────────────────
    html += HTML_MODAL
    html += '<script>\nconst foodData = ' + json.dumps(food_data_js, ensure_ascii=False) + ';\n</script>\n'
    html += HTML_SCRIPT

    with open(OUT_HTML, 'w') as f:
        f.write(html)

    size = os.path.getsize(OUT_HTML)
    print(f'Dashboard geschrieben: {OUT_HTML} ({size:,} bytes)', file=sys.stderr)
    print(f'  Foods: {len(foods)}, Properties mapped: {len(prop_mappings)}, FDC cached: {len(cache)}', file=sys.stderr)

if __name__ == '__main__':
    main()