#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""Build SQLite DB + JSON export of delivery-service data for 47829 Krefeld-Uerdingen.
Snapshot: 2026-11-17. Sources tracked per item/service."""
import sqlite3, json, os

SNAP = "2026-11-17"
HERE = os.path.dirname(os.path.abspath(__file__))
DB = os.path.join(HERE, "lieferdienste_47829.sqlite3")
JS = os.path.join(HERE, "lieferdienste_47829.json")

services = [
 dict(id="pizza_live", name="Pizza Live Uerdingen", kind="direct", address="Traarer Str. 2, 47829 Krefeld",
      phone="02151-158888", website="https://www.pizzalive-uerdingen.de/",
      min_order=15.0, delivery_fee=0.0, plz="47829,47800,47802,47809 + Duisburg-Süd, Meerbusch",
      hours="Mo-Fr 11-22, Sa 15-23, So/Feiertag 11-22",
      cuisines="Pizza, Pasta, Schnitzel, Asia, Burger, Baguette, Salate, Desserts, Fingerfood, Kidz",
      notes="Mittagsaktion Mo-Fr 11-14 Uhr: Pizza(26cm)/Pasta/Schnitzel je 8 EUR, Asia 9,50 EUR, Baguette 5,50 EUR. Bestellung via pizza-taxi-24.de/APP."),
 dict(id="bei_genc", name="Bei Genc", kind="direct", address="Am Röttgen 5, 47829 Krefeld",
      phone="02151-8919300", website="https://bei-genc.de/",
      min_order=None, delivery_fee=0.0, plz="47829 (eigene Lieferung; auch via Wolt)",
      hours="Mo, Mi-Sa 11:15-21:30, Di geschlossen, So 16:00-21:30",
      cuisines="Türkisch (Döner, Dürüm, Lahmacun, Pide), Pizza, Pizzabrötchen, Burger, Menüs",
      notes="ca. 5% Online-Rabatt im eigenen Shop (bei-genc.de) ggü. Listenpreis. Mindestbestellwert nicht dokumentiert. Auch auf Wolt (anderer Betreiber-Eintrag, Preise leicht abweichend)."),
 dict(id="pizza_pazza", name="Pizza Pazza", kind="direct", address="Marktstr. 84, 47798 Krefeld",
      phone="02151-770006", website="https://www.pizzapazzakrefeld.de/",
      min_order=25.0, delivery_fee=0.0, plz="47798,47800,47802,47809,47829",
      hours=None, cuisines="Italienisch, Deutsch, Griechisch, Französisch",
      notes="Bewertung 4.3 (67). Liefert nach 47829 Uerdingen/Gartenstadt ab 25 EUR ohne Liefergebühr."),
 dict(id="stern_pizza", name="Stern Pizza", kind="direct", address="Schwertstr. 67, 47799 Krefeld",
      phone="02151-5674741", website="https://www.sternpizza.de/",
      min_order=15.0, delivery_fee=0.0, plz="Zone 1 inkl. Uerdingen",
      hours=None, cuisines="Pizza u.a.",
      notes="Liefert laut Website nach Krefeld Uerdingen, Zone 1: Min 15 EUR, Gebühr 0 EUR."),
 dict(id="sushi_hao", name="Sushi Hao Krefeld", kind="direct", address="Krefeld 47798",
      phone=None, website=None, min_order=None, delivery_fee=None, plz="47829",
      hours=None, cuisines="Japanisch/Sushi", notes="liefert laut eatloc nach 47829"),
 dict(id="pizzeria_ciao", name="Pizzeria Ciao", kind="direct", address="Krefeld 47800",
      phone=None, website=None, min_order=None, delivery_fee=None, plz="47829",
      hours=None, cuisines="Pizza & Pasta", notes="liefert laut eatloc nach 47829"),
 dict(id="china_city", name="China-Restaurant China-City", kind="direct", address="Rheinstr. 47, 47799 Krefeld",
      phone="02151-24979", website=None, min_order=None, delivery_fee=None, plz=None,
      hours=None, cuisines="Chinesisch/Asiatisch", notes="Lieferservice laut 11880, Bewertung 4/5"),
 dict(id="da_benny", name="Da Benny Pizzeria", kind="direct", address="Oberstr. 8, 47829 Krefeld",
      phone="02151-5246620", website=None, min_order=None, delivery_fee=None, plz=None,
      hours=None, cuisines="Pizza", notes="laut 11880: geschlossen (Stand Snapshot)"),
 dict(id="piccola_sorrento", name="Trattoria Piccola Sorrento", kind="direct", address="Breslauer Str. 7a, 47829 Krefeld",
      phone="02151-789767", website=None, min_order=None, delivery_fee=None, plz=None,
      hours=None, cuisines="Italienisch", notes="laut 11880: geschlossen (Stand Snapshot)"),
 dict(id="boom_ubereats", name="BOOM Smashburger", kind="platform:Uber Eats", address="Alte Krefelder Str. 23, 47829 Krefeld",
      phone=None, website="https://www.ubereats.com/de/city/krefeld-nw",
      min_order=None, delivery_fee=None, plz="47829 (Standort selbst in 47829)",
      hours=None, cuisines="Burger, Hot Dog, Salate, Halal", notes=" uber-eats-Preise/gebuehren app-abhaengig"),
 dict(id="pizza_cab", name="Pizza Cab Krefeld", kind="platform:Uber Eats", address="Alte Linner Str. 77, 47799 Krefeld",
      phone=None, website="https://www.ubereats.com/de/city/krefeld-nw",
      min_order=None, delivery_fee=None, plz=None, hours=None, cuisines="Pizza",
      notes="Uber Eats: Ersparnis ab 15 EUR (Aktion)"),
 dict(id="platform_wolt", name="Wolt Krefeld", kind="platform:Wolt", address=None, phone=None,
      website="https://wolt.com/de/deu/krefeld", min_order=None, delivery_fee=None, plz="47829 (Adresse-abhaengig)",
      hours=None, cuisines="u.a. Bei Genc, FAVO, DOENALADN, BOOM Smashburger",
      notes="Plattform-Aufschlag, Liefergebuehren je Adresse"),
 dict(id="platform_lieferando", name="Lieferando Krefeld", kind="platform:Lieferando", address=None, phone=None,
      website="https://www.lieferando.de/lieferdienst/krefeld", min_order=None, delivery_fee=None, plz="je Adresse",
      hours=None, cuisines="Tele Pizza, Pizza Hero, Al Lupo, Pizza Yolo, REWE Express u.v.m.",
      notes="Verfuegbarkeit fuer Bergstr. 47829 in App pruefen"),
]

sources = [
 dict(service_id="pizza_live", url="https://www.pizzalive-uerdingen.de/", what="Startseite: Liefergebiete, Mindestbestellwert 15 EUR, Oeffnungszeiten, Mittagsaktion, Impressum"),
 dict(service_id="pizza_live", url="https://www.pizzalive-uerdingen.de/speisekarte/pizza/", what="Pizzakarte mit Preisen 22/26/29/45 cm"),
 dict(service_id="pizza_live", url="https://www.pizzalive-uerdingen.de/speisekarte/burger/", what="Burgerpreise"),
 dict(service_id="pizza_live", url="https://www.pizzalive-uerdingen.de/speisekarte/desserts/", what="Dessertpreise"),
 dict(service_id="bei_genc", url="https://bei-genc.de/takeaway", what="Onlineshop-Preise (mit ~5% Online-Rabatt ggue. Listenpreis)"),
 dict(service_id="bei_genc", url="https://bei-genc.de/delivery", what="Onlineshop-Preise + Beliebtheit"),
 dict(service_id="bei_genc", url="https://www.speisekarte.de/krefeld/restaurant/bei_genc_krefeld/speisekarte", what="Alternative Karte (Stand 25.08.2025, teils aeltere Preise)"),
 dict(service_id="bei_genc", url="https://wolt.com/de/deu/krefeld/restaurant/bei-genc-krefeld", what="Wolt-Eintrag: Oeffnungszeiten, einige Preise"),
 dict(service_id="bei_genc", url="https://www.lieferando.de/speisekarte/bei-genc-47829", what="Lieferando-Menues (andere Plattformpreise)"),
 dict(service_id="pizza_pazza", url="https://www.pizzapazzakrefeld.de/", what="Liefergebiete 47829: Min 25 EUR, 0 EUR Gebuehr"),
 dict(service_id="stern_pizza", url="https://www.sternpizza.de/en/pizza-delivery-krefeld-uerdingen", what="Lieferung nach Uerdingen, Zone 1: Min 15 EUR, Fee 0"),
 dict(service_id="platform_wolt", url="https://wolt.com/de/deu/krefeld/restaurant/bei-genc-krefeld", what="Wolt-Verfuegbarkeit in Krefeld"),
 dict(service_id="platform_lieferando", url="https://www.lieferando.de/lieferdienst/krefeld", what="Lieferando-Uebersicht Krefeld"),
 dict(service_id="platform_lieferando", url="https://www.lieferando.de/lieferservice/essen/duesseldorf-krefeld-47799", what="Lieferando-Partnerliste (REWE Express, Flink u.a.)"),
 dict(service_id="boom_ubereats", url="https://www.ubereats.com/de/city/krefeld-nw", what="Uber Eats Krefeld: BOOM Smashburger 47829, Pizza Cab u.a."),
 dict(service_id="sushi_hao", url="https://eatloc.de/lieferservice/47829", what="Direkt-Lieferdienste nach 47829 (Sushi Hao, Bei Genc, Pizza Live, Pizzeria Ciao)"),
 dict(service_id="china_city", url="https://www.11880.com/suche/essenslieferservice/krefeld", what="Branchenverzeichnis: China City, Pizza Live, Da Benny (geschlossen), Piccola Sorrento (geschlossen), Bei Genc 5/5"),
 dict(service_id="pizza_pazza", url="https://www.gopizzago.de/lieferservice-krefeld", what="goPIZZAgo-Liste Krefeld, Bewertung 4.67 (64)"),
]

# Pizza Live: (name, desc, [22,26,29,45] or [26,29] for calzone)
pl_pizzas = [
 ("Pizza Margherita","Tomatensauce, Käse",[5.00,8.10,9.50,13.70]),
 ("Pizza Sophie","Sauce Hollandaise",[5.90,9.00,10.40,18.60]),
 ("Pizza Salami","Salami",[5.50,8.40,10.10,15.10]),
 ("Pizza BBQ","Barbeque-Sauce",[5.70,9.10,10.30,16.50]),
 ("Pizza Pirata","Hackfleischsauce",[5.50,9.00,10.40,16.30]),
 ("Pizza Prosciutto","Formschinken",[5.80,9.10,10.40,15.90]),
 ("Pizza Funghi","Champignons",[5.60,9.00,11.00,15.70]),
 ("Pizza Tonno","Thunfisch",[5.90,10.10,11.40,17.30]),
 ("Pizza Spinaci","Spinat, Knoblauch",[5.90,8.70,10.70,15.90]),
 ("Pizza Chili","Chili Con Carne (scharf)",[5.70,9.10,10.40,16.00]),
 ("Pizza Sucuk","Knoblauchwurst",[5.90,10.10,11.40,16.70]),
 ("Pizza Frutti di Mare","Meeresfrüchte, Knoblauch",[6.20,10.10,11.70,17.90]),
 ("Pizza Scampi","Krabben, Knoblauch",[6.60,10.10,12.40,19.90]),
 ("Pizza Gyros","Gyros",[6.50,10.00,11.90,18.70]),
 ("Pizza Döner","mit Hähnchenfleisch",[6.50,10.00,11.90,18.70]),
 ("Pizza Inferno","Salami, Peperoni",[6.30,9.10,10.70,17.20]),
 ("Pizza Mista","Formschinken, Salami, Champignons",[6.40,10.00,12.30,19.70]),
 ("Pizza Hawaii","Formschinken, Ananas",[6.50,9.90,12.30,19.20]),
 ("Pizza Bella Italia","Bolognesesauce mit 4 Nudelsorten",[6.60,10.70,12.10,19.90]),
 ("Pizza Kentucky","Hähnchenstreifen, Mozzarella überbacken",[6.40,10.60,12.00,19.90]),
 ("Pizza Live","Tomaten, Mozzarella überbacken",[6.70,10.20,11.50,15.70]),
 ("Pizza Marion Spezial","Paprika, Spinat, Zwiebeln, Champignons",[6.60,11.00,12.20,20.70]),
 ("Pizza Mafiosi","Salami, Thunfisch, Zwiebeln",[6.60,11.00,12.20,20.70]),
 ("Pizza Anatolia","Knoblauchwurst, frische Tomaten, Spiegelei",[6.70,10.90,12.30,21.20]),
 ("Pizza Hollywood","Broccoli, Hähnchenfleisch, Sauce Hollandaise",[7.10,11.00,12.40,21.30]),
 ("Pizza HamHam","Lachs, Spinat, Sauce Hollandaise",[7.20,11.40,13.00,21.40]),
 ("Pizza Milano","Sauce Hollandaise, Formschinken, Broccoli",[6.70,11.40,12.50,21.50]),
 ("Pizza Vegetaria","Spinat, Champignons, Artischocken, Knoblauch",[6.90,11.00,13.10,21.50]),
 ("Pizza Käsesorten","4 verschiedene Käsesorten",[7.10,11.10,12.70,21.90]),
 ("Pizza Mexiko","Mais, Jalapenos, Paprika",[6.30,10.40,12.10,18.50]),
 ("Pizza Quattro Stagioni","Formschinken, Thunfisch, Champignons, Artischocken",[7.20,11.00,12.40,21.90]),
 ("Pizza Calzone","gefüllt: Formschinken, Thunfisch, Champignons",[None,10.20,12.50,None]),
 ("Pizza Nevada","BBQ, Rindersteakstreifen, fr. Tomate, Paprika",[7.10,11.40,13.10,20.90]),
 ("Pizza Miami","Mais, Hähnchenstreifen, Broccoli",[7.30,11.50,13.40,21.50]),
 ("Pizza Rex","Gyros, Paprika, frische Tomaten",[7.30,11.40,13.10,21.50]),
 ("Pizza Gourmet","Lachs, Krabben, frische Tomaten, Knoblauch",[7.60,12.80,14.30,22.50]),
]
pl_burgers = [
 ("Hamburger","gegrillt mit Salat, Gurken, frische Tomaten",8.40),
 ("Cheeseburger","gerillt mit Salat, Gurken, Tomaten, Zwiebeln, Käse",8.70),
 ("Chickenburger","gegrillt mit Salat, Gurken, Tomaten, Zwiebeln",9.10),
 ("LIVE-Burger","mit Chilisauce, Jalapenos",9.20),
 ("BBQ-Burger","mit BBQ-Sauce",9.40),
 ("Hawaii Burger","mit Schinken, Ananas",9.40),
 ("Nuggets Burger","mit Salat, Gurken",9.70),
]
pl_desserts = [
 ("Tiramisu (hausgemacht)","",5.70),
 ("Mousse au Chocolat (hausgemacht)","",4.70),
 ("Fruchtsalat (hausgemacht)","",5.70),
 ("Gebackene Ananas mit Honig","",5.20),
 ("Gebackene Banane mit Honig","",5.20),
]
pl_actions = [  # Mittagsaktion Mo-Fr 11-14 Uhr
 ("Aktions-Pizza 26 cm","Mittagsaktion Mo-Fr 11-14 Uhr, jede 26-cm-Pizza",8.00),
 ("Aktions-Pasta","Mittagsaktion, jede Pasta",8.00),
 ("Aktions-Schnitzel","Mittagsaktion, alle Schnitzel",8.00),
 ("Aktions-Baguette","Mittagsaktion, alle Baguettes",5.50),
 ("Aktions-Asia","Mittagsaktion, alle Asia-Gerichte (außer Ente, Hummer)",9.50),
]

# Bei Genc (bei-genc.de Onlineshop-Preise, ~5% Rabatt aktiv)
genc_items = [
 ("Pizzen","Pizza Margherita","mit Käse",4.75),("Pizzen","Pizza Tonno","mit Thunfisch",6.65),
 ("Pizzen","Pizza Funghi","mit Champignons",5.70),("Pizzen","Pizza Spinaci","mit Spinat",5.70),
 ("Pizzen","Pizza Prosciutto","mit Schinken",6.18),("Pizzen","Pizza Genc 1","mit Dönerfleisch, Peperoni",7.60),
 ("Pizzen","Pizza ala Casa","Schinken, Salami, Thunfisch, Paprika",7.60),
 ("Pizzen","Pizza Hawaii","türkischer Schinken, Ananas",7.60),
 ("Pizzen","Pizza Genc 2","Kalbfleisch, Zwiebeln",7.60),("Pizzen","Pizza Rialto","Thunfisch, Spinat",7.60),
 ("Pizzen","Pizza Diavolo","Salami, Peperoni, Zwiebeln",6.65),("Pizzen","Pizza Toscana","Schinken, Champignons",7.60),
 ("Pizzen","Pizza Mexico","Schinken, Salami, Jalapenos",7.60),
 ("Pizzen","Pizza Genc Spezial","Kalbfleisch, Paprika, Zwiebel, Peperoni",8.55),
 ("Pizzen","Pizza Calzone","Schinken, Salami, Champignons, Thunfisch",9.50),
 ("Pizzen","Pizza da Gimmy","Thunfisch, Champignons, Spinat, Paprika",7.60),
 ("Pizzen","Pizza Mista","Thunfisch, Schinken, Champignons",7.60),
 ("Pizzen","Pizza 4 Stagioni","Thunfisch, Schinken, Champignons, Paprika",7.60),
 ("Pizzen","Pizza Genc 3","Kalbfleisch, Peperoni, Schafskäse",8.55),
 ("Pizzen","Pizza Vegetaria","Champignons, Spinat, Zwiebel, Paprika",6.65),
 ("Pizzen","Pizza Frutti di Mare","mit Meeresfrüchten",7.60),
 ("Pizzen","Pizza Sucuk","türkische Knoblauchwurst",7.13),
 ("Pizzen","Pizza Rustica","Schinken, Thunfisch, Paprika, Zwiebeln",8.08),
 ("Pizzen","Pizza Calzone gefüllt","Champignons, Thunfisch, Spinat",9.50),
 ("Pizzen","Pizza Sucuk Spezial","Knoblauchwurst, Peperoni, Zwiebeln, Oliven, Knoblauch",8.55),
 ("Pizzen","Pizza Hollandaise Hähnchen","Hollandaise, fr. Champignons, Hähnchenbrust",7.60),
 ("Pizzen","Calzone Döner","Dönerfleisch, fr. Tomaten, Zwiebeln, Schafskäse",9.98),
 ("Pizzen","Pizza Hollandaise Schinken","Hollandaise, Schinken, Broccoli",6.65),
 ("Pizzen","Pizzablech Margherita 60x40 cm","",19.95),
 ("Pizzabrötchen","Pizzabrötchen mit Kräuterbutter","10 Stück",2.85),
 ("Pizzabrötchen","Pizzabrötchen mit Thunfisch & Käse","10 Stück",6.18),
 ("Pizzabrötchen","Pizzabrötchen mit Schinken & Käse","10 Stück",6.18),
 ("Pizzabrötchen","Pizzabrötchen mit Salami & Käse","10 Stück",6.18),
 ("Pizzabrötchen","Pizzabrötchen mit Spinat & Käse","10 Stück",6.18),
 ("Pizzabrötchen","Pizzabrötchen mit Paprika & Käse","10 Stück",6.18),
 ("Pizzabrötchen","Pizzabrötchen mit Drehspießfleisch","10 Stück",7.60),
 ("Pizzabrötchen","Pizzabrötchen mit Knoblauchwurst","10 Stück",7.13),
 ("Pide","Pide Drehspießfleisch","Drehspießfleisch, Käse, Gouda",8.08),
 ("Pide","Pide Käse","Käse, Gouda, Spinat",8.08),
 ("Pide","Pide Sucuk","Knoblauchwurst, Spinat, Gouda",8.08),
 ("Pide","Pide Ei","Spiegelei, Spinat, Gouda",8.08),
 ("Pide","Sigara Börek (6 Stück)","",5.23),
 ("Drehspieß Kebap","Drehspieß Tasche","mit Salat, Sauce (Kalb/Hähnchen)",6.65),
 ("Drehspieß Kebap","Kinder-Drehspießtasche","",4.75),
 ("Drehspieß Kebap","Vegetarische Tasche","",4.75),
 ("Drehspieß Kebap","Drehspieß Tasche groß","",10.45),
 ("Drehspieß Kebap","Vegetarische Tasche groß","",7.13),
 ("Drehspieß Kebap","Drehspieß Teller Kalb groß","Kalbfleisch, Salat, Sauce",11.40),
 ("Drehspieß Kebap","Drehspieß Teller Hähnchen groß","Hähnchenfleisch, Salat, Sauce",11.40),
 ("Drehspieß Kebap","Iskender Teller","Döner auf Brot, Tomatensauce, Joghurt",12.35),
 ("Drehspieß Kebap","Dürüm Kalb","Kalbfleisch, Salat, Sauce",8.08),
 ("Drehspieß Kebap","Dürüm Hähnchen","Hähnchenfleisch, Salat, Sauce",8.08),
 ("Drehspieß Kebap","Dürüm","mit Salat, Sauce",5.70),
 ("Drehspieß Kebap","Lahmacun","ohne alles",3.33),
 ("Drehspieß Kebap","Lahmacun mit Salat","mit Sauce",5.70),
 ("Drehspieß Kebap","Lahmacun Spezial","Kalbfleisch, Salat, Sauce",8.55),
 ("Drehspieß Kebap","Falafel Dürüm (5 Stück)","mit Salat, Sauce",7.13),
 ("Drehspieß Kebap","Falafel Tasche (4 Stück)","mit Salat, Sauce",5.70),
 ("Drehspieß Kebap","Drehspieß Box Mini","",4.28),
 ("Drehspieß Kebap","Drehspieß Box Normal","",5.70),
 ("Drehspieß Kebap","Drehspieß Box Big","",7.60),
 ("Pasta","Spaghetti Genc","Dönerfleisch, fr. Champignons, Paprika in Sahne/Tomatensauce",9.41),
 ("Pasta","Penne Nudel Auflauf","fr. Champignons, Dönerfleisch",9.50),
 ("Menüs","Döner Spar Menü 1","Kalbdöner + Pommes + Getränk 0,33 l (Lieferando)",12.25),
 ("Menüs","Menü 2","Dönerteller Hähnchen + 2 Baklava + Getränk (Lieferando)",14.00),
 ("Menüs","Menü 3","2 Döner-Taschen + 2 Pommes + Getränk 1,0 l (Lieferando)",21.00),
 ("Menüs","Menü 4","Dürüm Hähnchen + Pommes + Getränk (Lieferando)",12.00),
 ("Menüs","Menü 5","Dürüm Kalb + Pommes + Getränk (Lieferando)",13.00),
 ("Menüs","Chicken Nuggets Menü","Nuggets + Pommes + Getränk 0,33 l (Wolt-Shop)",8.55),
 ("Menüs","Chickenburger Menü","mit Pommes + Getränk 0,33 l",8.55),
 ("Menüs","Hamburger Menü","mit Pommes + Getränk 0,33 l",9.03),
 ("Menüs","Cheeseburger Menü","mit Pommes + Getränk 0,33 l",9.50),
 ("Menüs","Drehspieß Tasche Menü","mit Pommes + Getränk 0,33 l",10.45),
 ("Menüs","Dürüm Menü","mit Pommes + Getränk 0,33 l",11.40),
 ("Menüs","Lahmacun Menü","mit Pommes + Getränk 0,33 l",10.45),
]

rows = []
def add(sid, cat, name, desc, size, price, ptype="standard", src=None, avail=1):
    rows.append(dict(service_id=sid, category=cat, name=name, description=desc,
                     size=size, price=price, price_type=ptype, available=avail,
                     source_url=src))

for name, desc, sizes in pl_pizzas:
    for sz, price in zip(["22 cm","26 cm","29 cm","45 cm"], sizes):
        if price: add("pizza_live","Pizza",name,desc,sz,price)
for name, desc, price in pl_burgers:
    add("pizza_live","Burger",name,desc,None,price)
for name, desc, price in pl_desserts:
    add("pizza_live","Desserts",name,desc,None,price)
for name, desc, price in pl_actions:
    add("pizza_live","Mittagsaktion",name,desc,None,price,ptype="action",
        src="https://www.pizzalive-uerdingen.de/")
for cat, name, desc, price in genc_items:
    ptype = "standard" if cat not in ("Menüs",) else "platform"
    src = "https://bei-genc.de/takeaway" if cat != "Menüs" else "https://www.lieferando.de/speisekarte/bei-genc-47829"
    add("bei_genc",cat,name,desc,None,price,ptype=ptype,src=src)

# --- Build SQLite ---
if os.path.exists(DB): os.remove(DB)
con = sqlite3.connect(DB)
cur = con.cursor()
cur.executescript("""
CREATE TABLE services(
  id TEXT PRIMARY KEY, name TEXT NOT NULL, kind TEXT, address TEXT, phone TEXT,
  website TEXT, min_order REAL, delivery_fee REAL, delivery_plz TEXT, hours TEXT,
  cuisines TEXT, notes TEXT, snapshot_date TEXT);
CREATE TABLE menu_items(
  id INTEGER PRIMARY KEY AUTOINCREMENT, service_id TEXT REFERENCES services(id),
  category TEXT, name TEXT, description TEXT, size TEXT, price REAL,
  price_type TEXT, available INTEGER DEFAULT 1, source_url TEXT);
CREATE TABLE sources(
  id INTEGER PRIMARY KEY AUTOINCREMENT, service_id TEXT REFERENCES services(id),
  url TEXT, what TEXT, fetched_date TEXT);
CREATE INDEX idx_items_service ON menu_items(service_id);
CREATE INDEX idx_items_name ON menu_items(name);
""")
for s in services:
    cur.execute("INSERT INTO services VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)",
        (s["id"],s["name"],s["kind"],s["address"],s["phone"],s["website"],
         s["min_order"],s["delivery_fee"],s["plz"],s["hours"],s["cuisines"],s["notes"],SNAP))
for src in sources:
    cur.execute("INSERT INTO sources(service_id,url,what,fetched_date) VALUES(?,?,?,?)",
        (src["service_id"],src["url"],src["what"],SNAP))
for r in rows:
    cur.execute("INSERT INTO menu_items(service_id,category,name,description,size,price,price_type,available,source_url) VALUES(?,?,?,?,?,?,?,?,?)",
        (r["service_id"],r["category"],r["name"],r["description"],r["size"],r["price"],r["price_type"],r["available"],r["source_url"]))
con.commit()
n_items = cur.execute("SELECT COUNT(*) FROM menu_items").fetchone()[0]
n_src = cur.execute("SELECT COUNT(*) FROM sources").fetchone()[0]
con.close()

# --- JSON export (same content, agent-friendly) ---
data = {"snapshot_date":SNAP, "services":services, "sources":sources,
        "menu_items":[dict(i, id=j) for j,i in enumerate(rows,1)]}
with open(JS,"w",encoding="utf-8") as f:
    json.dump(data,f,ensure_ascii=False,indent=1)

print(f"OK: {DB} ({n_items} items, {len(services)} services, {n_src} sources)")
print(f"OK: {JS}")
