ti/app.py

import sqlite3
import os
import re
import unicodedata
import socket
from pathlib import Path
from datetime import datetime
from flask import Flask, render_template, request, jsonify, send_from_directory

app = Flask(__name__)
DATA_DIR = Path(os.environ.get("DATA_DIR", "."))
UPLOAD_DIR = DATA_DIR / "uploads"
UPLOAD_DIR.mkdir(parents=True, exist_ok=True)
DB = str(DATA_DIR / "data.db")

SECTIONS = [
    {"id": "koed", "emoji": "🥩", "title": "Kød & fisk", "est": 1580, "varer": [
        {"key": "entrecote",       "name": "Entrecote ½ kg",          "de": "Entrecôte ½ kg",                    "note": "Frisk sydamerikansk", "qty": "6 × ½ kg",  "est": 605.94, "badges": ["card"]},
        {"key": "nakkekoteletter", "name": "Nakkekoteletter ½ kg",     "de": "Schweinenackenkoteletten ½ kg",     "note": "Med/uden krydderi",   "qty": "6 × ½ kg",  "est": 191.94},
        {"key": "kyllingebryst",   "name": "Kyllingebryst ½ kg",       "de": "Hähnchenbrustfilet ½ kg",          "note": "Texas-marinade",      "qty": "6 × ½ kg",  "est": 359.94},
        {"key": "hakket-oksekoed", "name": "Hakket oksekød",           "de": "Rinderhackfleisch",                "note": "Køddisk — spørg personalet", "qty": "3 kg", "est": 240},
        {"key": "rumpsteak",       "name": "Rumpsteak ½ kg",           "de": "Rumpsteak ½ kg",                   "note": "Modnet, tyske kvier", "qty": "1 × ½ kg",  "est": 167.99},
        {"key": "lakseside",       "name": "Lakseside premium",        "de": "Lachsseite premium",               "note": "15,99/100g · tag ~700g", "qty": "~700g",  "est": 112},
        {"key": "grillpoelser",    "name": "Gerstand grillpølser",     "de": "Gerstand Grillwürste",             "note": "10 stk. à 100g",      "qty": "1 kg",      "est": 74.99},
        {"key": "bacon",           "name": "Steinemann bacon i skiver","de": "Steinemann Bauchspeck in Scheiben", "note": "Røget · fryseegnet",  "qty": "1 kg",      "est": 74.99},
    ]},
    {"id": "frost", "emoji": "🧊", "title": "Frostgrønt · 3 kg poser", "est": 100, "varer": [
        {"key": "wok-mix",      "name": "Wok-mix blandede grøntsager", "de": "Wok-Mix Gemüsemischung",  "qty": "3 kg", "est": 40},
        {"key": "aerter",       "name": "Ærter",                        "de": "Erbsen",                  "qty": "3 kg", "est": 30},
        {"key": "broccoli",     "name": "Broccoli",                     "de": "Brokkoli",                "qty": "3 kg", "est": 35},
        {"key": "frost-andet",  "name": "Hvad der ellers frister",     "de": "Was sonst verlockend ist", "note": "Tjek frysedisken", "qty": "—", "est": 0},
    ]},
    {"id": "mejeri", "emoji": "🧀", "title": "Mejeri, ost & frost", "est": 185, "varer": [
        {"key": "feta",         "name": "Greco Schaffeta feta 150g", "de": "Greco Schaffeta Feta 150g",       "note": "70% fåremælk",                   "qty": "3 stk.",  "est": 44.97},
        {"key": "frutti-mare",  "name": "Frutti di Mare",             "de": "Frutti di Mare",                  "note": "Rejer, muslinger, blæksprutte",  "qty": "1 kg",    "est": 59.99},
        {"key": "tigerrejer",   "name": "Sorte tigerrejer frost",     "de": "Schwarze Tigergarnelen TK",       "note": "Rå, pillede · wok/grill",        "qty": "1 kg",    "est": 79.99},
    ]},
    {"id": "pasta", "emoji": "🍝", "title": "Pasta frisk køl · Gut & Günstig ~800g", "est": 160, "varer": [
        {"key": "tortelloni-skinke",  "name": "Tortelloni Skinke",           "de": "Tortelloni Schinken",          "qty": "2 stk.", "est": 55},
        {"key": "tortelloni-spinat",  "name": "Tortelloni Spinat & ricotta", "de": "Tortelloni Spinat & Ricotta",  "qty": "2 stk.", "est": 55},
        {"key": "tortelloni-valg",    "name": "Tortelloni valgfri variant",  "de": "Tortelloni nach Wahl",         "note": "Svampe, tomat-mozzarella o.l.", "qty": "2 stk.", "est": 55},
    ]},
    {"id": "groent", "emoji": "🥗", "title": "Grønt, olier & tørvarer", "est": 245, "varer": [
        {"key": "avocado",      "name": "Avocado Hass",                       "de": "Avocado Hass",                        "note": "Fra Peru, SanLucar",   "qty": "5 stk.",    "est": 74.95},
        {"key": "olivenolie",   "name": "Bertolli olivenolie extra vergine",  "de": "Bertolli Olivenöl extra vergine",     "qty": "1L",                    "est": 99.99},
        {"key": "mutti",        "name": "Mutti passata",                      "de": "Mutti Passata",                       "note": "Natur eller basilikum", "qty": "3 × 700g", "est": 44.97},
        {"key": "rustichella",  "name": "Rustichella d'Abruzzo pasta",        "de": "Rustichella d'Abruzzo Pasta",         "qty": "4 × 500g",              "est": 75.96},
    ]},
    {"id": "konserves", "emoji": "🥫", "title": "Konserves — tomat & basis", "est": 185, "varer": [
        {"key": "hakkede-tomater", "name": "Hakkede tomater 400g",    "de": "Gehackte Tomaten 400g",        "qty": "8 dåser", "est": 50},
        {"key": "flaede-tomater",  "name": "Flåede tomater 400g",     "de": "Geschälte Tomaten 400g",       "qty": "4 dåser", "est": 25},
        {"key": "tomatpure",       "name": "Tomatpuré / koncentrat",  "de": "Tomatenmark / -konzentrat",    "qty": "4 stk.",  "est": 35},
        {"key": "tun",             "name": "Tun i olie 80g",          "de": "Thunfisch in Öl 80g",          "qty": "6 dåser", "est": 47},
        {"key": "majs",            "name": "Majs 340g",               "de": "Mais 340g",                    "qty": "4 dåser", "est": 28},
    ]},
    {"id": "indisk", "emoji": "🌶️", "title": "Indisk & karry", "est": 160, "varer": [
        {"key": "tikka-masala",     "name": "Tikka masala sauce",  "de": "Tikka Masala Sauce",    "note": "350–500g · Taste of India", "qty": "5 stk.", "est": 70},
        {"key": "karrypasta-roed",  "name": "Karrypasta rød 50g",  "de": "Rote Currypaste 50g",   "qty": "2 stk.", "est": 20},
        {"key": "karrypasta-groen", "name": "Karrypasta grøn 50g", "de": "Grüne Currypaste 50g",  "qty": "2 stk.", "est": 20},
        {"key": "karrypasta-gul",   "name": "Karrypasta gul/mild", "de": "Gelbe Currypaste/mild", "qty": "2 stk.", "est": 20},
        {"key": "mango-chutney",    "name": "Mango chutney 300g",  "de": "Mango-Chutney 300g",    "qty": "2 stk.", "est": 24},
        {"key": "basmatiris",       "name": "Basmatiris 1 kg",     "de": "Basmatireis 1 kg",      "qty": "2 stk.", "est": 28},
    ]},
    {"id": "mex", "emoji": "🌮", "title": "Mexicansk & asiatisk", "est": 185, "varer": [
        {"key": "tacokrydderi", "name": "Tacokrydderi pakke",        "de": "Taco-Gewürzmischung",         "qty": "6 stk.",  "est": 33},
        {"key": "jalapenos",    "name": "Jalapeños på glas 200g",    "de": "Jalapeños im Glas 200g",      "qty": "2 stk.",  "est": 16},
        {"key": "salsa",        "name": "Salsa / tomatdip 300g",     "de": "Salsa / Tomaten-Dip 300g",    "qty": "2 stk.",  "est": 19},
        {"key": "tacoshells",   "name": "Tacoshells / tortilla wraps","de": "Taco-Shells / Tortilla-Wraps","qty": "2 stk.", "est": 24},
        {"key": "kokosmalk",    "name": "Kokosmælk 400ml",           "de": "Kokosmilch 400ml",            "qty": "6 dåser", "est": 47},
        {"key": "sojasauce",    "name": "Sojasauce 250ml",           "de": "Sojasoße 250ml",              "qty": "1 stk.",  "est": 10},
        {"key": "bambusskud",   "name": "Bambusskud / vandkastanjer","de": "Bambussprossen / Wasserkastanien","qty": "2 dåser", "est": 14},
    ]},
    {"id": "drikke", "emoji": "🍺", "title": "Drikkevarer", "est": 530, "varer": [
        {"key": "carlsberg",     "name": "Carlsberg / Carls Special 0,33L", "de": "Carlsberg / Carls Special 0,33L",    "qty": "24 stk.", "est": 79,     "badges": ["kasse"]},
        {"key": "pepsi-max",     "name": "Pepsi Max 1,5L",                  "de": "Pepsi Max 1,5L",                      "qty": "6 stk.",  "est": 49.99,  "badges": ["kasse"]},
        {"key": "faxe-kondi",    "name": "Faxe Kondi Appelsin 0,33L",       "de": "Faxe Kondi Orange 0,33L",             "qty": "24 stk.", "est": 54.99,  "badges": ["kasse"]},
        {"key": "kuehling-vin",  "name": "Kühling hvid-/rosévin 0,75L",     "de": "Kühling Weiß-/Roséwein 0,75L",       "qty": "4 stk.",  "est": 159.96, "badges": ["card"]},
        {"key": "grauburgunder", "name": "Grauburgunder hvidvin 1,5L",      "de": "Grauburgunder Weißwein 1,5L",         "qty": "1 stk.",  "est": 59.99,  "badges": ["card"]},
        {"key": "kaffe",         "name": "Eduscho Caffè Crema hele bønner", "de": "Eduscho Caffè Crema ganze Bohnen",   "qty": "1 kg",    "est": 104.99},
    ]},
    {"id": "hus", "emoji": "🏠", "title": "Husholdning & skafferi", "est": 535, "varer": [
        {"key": "ariel",        "name": "Ariel vaskemiddel",           "de": "Ariel Waschmittel",               "note": "100–111 vaske", "qty": "1 stk.", "est": 199.90},
        {"key": "finish",       "name": "Finish opvasketabs",          "de": "Finish Spülmaschinentabs",        "note": "64 stk.",       "qty": "1 stk.", "est": 59.99},
        {"key": "cashew",       "name": "BASE Culinar cashewnødder",   "de": "BASE Culinar Cashewkerne",        "qty": "1 kg",           "est": 79.99},
        {"key": "nutella",      "name": "Nutella 1 kg glas",           "de": "Nutella 1 kg Glas",               "qty": "1 stk.",         "est": 49.99, "badges": ["card"]},
        {"key": "biscoff",      "name": "Lotus Biscoff smørepålæg 1,6 kg", "de": "Lotus Biscoff Brotaufstrich 1,6 kg", "qty": "1 stk.", "est": 99.99, "badges": ["card"]},
        {"key": "haribo",       "name": "Haribo vingummi / lakrids",   "de": "Haribo Weingummi / Lakritz",      "note": "800–1000g",     "qty": "1 stk.", "est": 44.99},
    ]},
]


def slugify(text):
    text = unicodedata.normalize('NFKD', text).encode('ascii', 'ignore').decode()
    return re.sub(r'[-\s]+', '-', re.sub(r'[^\w\s-]', '', text.lower())).strip('-')


def get_db():
    conn = sqlite3.connect(DB)
    conn.row_factory = sqlite3.Row
    return conn


def init_db():
    with get_db() as c:
        c.execute("""CREATE TABLE IF NOT EXISTS prices (
            key TEXT PRIMARY KEY, price REAL, checked INT DEFAULT 0, qty TEXT, updated TEXT)""")
        c.execute("""CREATE TABLE IF NOT EXISTS extras (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT, section_key TEXT, price REAL,
            photo TEXT, note TEXT, created TEXT)""")
        c.execute("""CREATE TABLE IF NOT EXISTS custom_sections (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            emoji TEXT DEFAULT '📦', title TEXT, created TEXT)""")
        try:
            c.execute("ALTER TABLE extras ADD COLUMN section_key TEXT")
        except Exception:
            pass
        try:
            c.execute("ALTER TABLE prices ADD COLUMN qty TEXT")
        except Exception:
            pass


@app.route('/')
def index():
    with get_db() as c:
        saved = {r['key']: dict(r) for r in c.execute("SELECT * FROM prices")}
        extras = [dict(r) for r in c.execute("SELECT * FROM extras ORDER BY created DESC")]
        custom_secs = [dict(r) for r in c.execute("SELECT * FROM custom_sections ORDER BY created")]
    by_sec = {}
    for e in extras:
        by_sec.setdefault(e.get('section_key') or '', []).append(e)
    return render_template('index.html', sections=SECTIONS, saved=saved,
                           by_sec=by_sec, custom_secs=custom_secs)


@app.route('/api/price', methods=['POST'])
def save_price():
    d = request.json
    key = d.get('key')
    price = d.get('price')
    checked = int(d.get('checked', 0))
    qty = d.get('qty') or None
    if not key:
        return jsonify(error='missing key'), 400
    with get_db() as c:
        c.execute("""INSERT INTO prices(key,price,checked,qty,updated) VALUES(?,?,?,?,?)
            ON CONFLICT(key) DO UPDATE SET price=excluded.price,
            checked=excluded.checked, qty=excluded.qty, updated=excluded.updated""",
            (key, price, checked, qty, datetime.now().isoformat()))
    return jsonify(ok=True)


@app.route('/api/item', methods=['POST'])
def add_item():
    name = request.form.get('name', '').strip()
    if not name:
        return jsonify(error='name required'), 400
    price_s = request.form.get('price', '').strip().replace(',', '.')
    price = float(price_s) if price_s else None
    section_key = request.form.get('section_key', '').strip() or None
    note = request.form.get('note', '').strip()

    photo = None
    f = request.files.get('photo')
    if f and f.filename:
        ext = (os.path.splitext(f.filename)[1] or '.jpg').lower()
        fname = f"{slugify(name)}-{int(datetime.now().timestamp())}{ext}"
        f.save(UPLOAD_DIR / fname)
        photo = fname

    with get_db() as c:
        row = c.execute(
            "INSERT INTO extras(name,section_key,price,photo,note,created) VALUES(?,?,?,?,?,?)",
            (name, section_key, price, photo, note, datetime.now().isoformat()))
        item_id = row.lastrowid

    return jsonify(ok=True, id=item_id, photo=photo,
                   name=name, section_key=section_key, price=price, note=note)


@app.route('/api/item/<int:item_id>', methods=['DELETE'])
def delete_item(item_id):
    with get_db() as c:
        row = c.execute("SELECT photo FROM extras WHERE id=?", (item_id,)).fetchone()
        if row and row['photo']:
            p = UPLOAD_DIR / row['photo']
            if p.exists():
                p.unlink()
        c.execute("DELETE FROM extras WHERE id=?", (item_id,))
    return jsonify(ok=True)


@app.route('/uploads/<path:filename>')
def serve_upload(filename):
    return send_from_directory(UPLOAD_DIR, filename)


@app.route('/health')
def health():
    return jsonify({'status': 'healthy', 'service': 'citti'})


@app.route('/api/category', methods=['POST'])
def add_category():
    d = request.json
    emoji = (d.get('emoji') or '📦').strip() or '📦'
    title = (d.get('title') or '').strip()
    if not title:
        return jsonify(error='title required'), 400
    with get_db() as c:
        row = c.execute("INSERT INTO custom_sections(emoji,title,created) VALUES(?,?,?)",
            (emoji, title, datetime.now().isoformat()))
        cat_id = row.lastrowid
    return jsonify(ok=True, id=cat_id, key=f"custom-{cat_id}", emoji=emoji, title=title)


@app.route('/api/category/<int:cat_id>', methods=['DELETE'])
def delete_category(cat_id):
    key = f"custom-{cat_id}"
    with get_db() as c:
        for r in c.execute("SELECT photo FROM extras WHERE section_key=?", (key,)).fetchall():
            if r['photo']:
                p = UPLOAD_DIR / r['photo']
                if p.exists():
                    p.unlink()
        c.execute("DELETE FROM extras WHERE section_key=?", (key,))
        c.execute("DELETE FROM custom_sections WHERE id=?", (cat_id,))
    return jsonify(ok=True)


@app.route('/analyse')
def analyse():
    with get_db() as c:
        saved = {r['key']: dict(r) for r in c.execute("SELECT * FROM prices")}
        extras = [dict(r) for r in c.execute("SELECT * FROM extras ORDER BY price DESC NULLS LAST")]
        custom_secs = [dict(r) for r in c.execute("SELECT * FROM custom_sections ORDER BY created")]

    rows = []
    total_est = total_actual = 0
    for sec in SECTIONS:
        sec_est = sec_actual = 0
        for item in sec['varer']:
            s = saved.get(item['key'], {})
            actual = s.get('price')
            sec_est += item['est']
            if actual:
                sec_actual += actual
        rows.append({
            'title': sec['title'],
            'emoji': sec['emoji'],
            'est': sec_est,
            'actual': sec_actual or None,
            'diff': (sec_actual - sec_est) if sec_actual else None,
        })
        total_est += sec_est
        if sec_actual:
            total_actual += sec_actual

    return render_template('analyse.html', sections=SECTIONS, saved=saved,
                           extras=extras, rows=rows, custom_secs=custom_secs,
                           total_est=total_est,
                           total_actual=total_actual or None)


if __name__ == '__main__':
    init_db()
    port = int(os.environ.get('PORT', 9756))
    try:
        s = socket.socket(socket.AF_INET, socket.SOCK_DGRAM)
        s.connect(("8.8.8.8", 80))
        local_ip = s.getsockname()[0]
        s.close()
    except Exception:
        local_ip = "127.0.0.1"
    print(f"\n  Computer: http://localhost:{port}")
    print(f"  Telefon (samme WiFi): http://{local_ip}:{port}\n")
    app.run(host='0.0.0.0', port=port, debug=False)