#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""Append platform data (Lieferando/Uber Eats partner lists + partial menus)
to the 47829 delivery-service DB. Snapshot 2026-11-17."""
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")

con = sqlite3.connect(DB)
cur = con.cursor()
LF = "https://www.lieferando.de"
UE = "https://www.ubereats.com/de/city/krefeld-nw"
LF47829 = "https://www.lieferando.de/lieferservice/essen/krefeld-47829"

# --- new / corrected services ---
new_services = [
 dict(id="master_pizza_more", name="Master Pizza & More", kind="direct-lieferando",
      address="Traarer Straße 60, 47829 Krefeld", phone=None, fax="0800 202 07 702",
      website=f"{LF}/speisekarte/master-pizza-more", min_order=15.0, delivery_fee=0.0,
      plz="47829", hours=None, cuisines="Pizza, Döner/Türkisch, Dürüm, Lahmacun, Pide, Burger, Baguettes, Schnitzel, Pasta, Salate, Desserts, Kindergerichte, Pommbox",
      notes="Inhaber Erkan Solgun, 4.4★ (2100+), 30 min/1,3 km. Gratis Lieferung möglich. MENÜ-LESEFEHLER: nur Kategorie 'Master Menüs' erfasst, Pizza/Döner/Burger-Preise fehlen (JS-Lazyload).", rating=4.4, rating_count=2100),
 dict(id="dopi", name="DöPi", kind="direct-lieferando",
      address="Breslauer Straße 13b, 47829 Krefeld", phone=None, fax="0800 202 07 702",
      website=f"{LF}/speisekarte/dopi-47829", min_order=15.0, delivery_fee=0.0,
      plz="47829", hours=None, cuisines="Pizza, Drehspieß, Pide, Imbiss, Nudelgerichte, Dessert, Schnitzel, Grillgerichte",
      notes="Inhaber Ali Rahimov, Neu auf Lieferando, 4.3★ (7), 35 min/1,3 km. 'ab'-Preise (Größe wählbar). Menü teils erfasst (Pizza-Kategorie).", rating=4.3, rating_count=7),
 dict(id="sorrento", name="Sorrento Trattoria Pizzeria", kind="direct-lieferando",
      address="Breslauer Str. 7a, 47829 Krefeld (Gartenstadt)", phone=None, fax="0800 202 07 702",
      website=f"{LF}/speisekarte/sorrento-trattoria-pizzeria", min_order=15.0, delivery_fee=0.0,
      plz="47829", hours=None, cuisines="Italienisch: Pizza, Pasta, Überbackenes, Spezialitäten, Salate, Menüs",
      notes="Inhaber Francesco La Pietra. 4.4★ (1000+), 10 min/1,3 km, kostenlose Lieferung. WICHTIG: 11880 verzeichnete 'Trattoria Piccola Sorrento' als geschlossen – auf Lieferando aktiv! Dienstag = Pasta-Tag 8 EUR. Nur Pasta-Tag-Kategorie erfasst.", rating=4.4, rating_count=1000),
 dict(id="boom_smash", name="BOOM Smash", kind="platform:Lieferando+Uber Eats",
      address="Alte Krefelder Straße 23, 47829 Krefeld", phone=None,
      website=f"{LF}/speisekarte/boom-smash", min_order=15.0, delivery_fee=0.0,
      plz="47829", hours=None, cuisines="Smash-Burger, Hot Dog, Salate, Halal",
      notes="4.9★ (640+), 20 min/1,7 km. Auf Uber Eats als 'BOOM Smashburger' (2-für-1-Deal), auf Lieferando min 15 EUR. Burger-Preise nicht erfasst (JS).", rating=4.9, rating_count=640),
]
# schema extras (must run before inserts)
cur.executescript("""
ALTER TABLE services ADD COLUMN rating REAL;
ALTER TABLE services ADD COLUMN rating_count INTEGER;
CREATE TABLE IF NOT EXISTS platform_partners(
  id INTEGER PRIMARY KEY AUTOINCREMENT, platform TEXT, name TEXT, plz TEXT,
  rating REAL, rating_count INTEGER, delivery_min TEXT, distance TEXT,
  min_order REAL, free_delivery INTEGER, promo TEXT, url TEXT, notes TEXT, snapshot_date TEXT);
""")

for s in new_services:
    cur.execute("""INSERT OR REPLACE INTO services
      (id,name,kind,address,phone,website,min_order,delivery_fee,delivery_plz,hours,cuisines,notes,snapshot_date)
      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))
    cur.execute("UPDATE services SET rating=?, rating_count=? WHERE id=?",(s["rating"],s["rating_count"],s["id"]))
    cur.execute("""UPDATE services SET notes=notes||' | KORREKTUR: auf Lieferando aktiv mit 4.4★ (1000+), min 15 EUR, kostenlose Lieferung, 10 min entfernt (Breslauer Str. 7a, Gartenstadt)'
      WHERE id='piccola_sorrento'""")
    cur.execute("""UPDATE services SET notes=notes||' | KORREKTUR: auf Lieferando als ''Pizza Live'' gelistet: 4.6★ (290+), min 12,50 EUR (!), 30 min'
      WHERE id='pizza_live'""")
    cur.execute("""UPDATE services SET notes=notes||' | KORREKTUR: auf Lieferando gelistet: 4.8★ (200+), min 35 EUR, 40 min (Bockumer Platz)'
      WHERE id='pizzeria_ciao'""")

# --- Lieferando partner list for 47829 (from /lieferservice/essen/krefeld-47829) ---
lf_partners = [
 # (name, rating, count, time, dist, min, free_delivery, promo, url)
 ("Master Pizza & More",4.4,2100,"30 min","1,3 km",15.00,1,None,"/speisekarte/master-pizza-more"),
 ("Just Smashed ®",3.3,57,"30 min","7,6 km",19.99,1,None,"/speisekarte/just-smashed-krefeld-prinz-ferdinand-strasse"),
 ("BOOM Smash",4.9,640,"20 min","1,7 km",15.00,0,None,"/speisekarte/boom-smash"),
 ("Pizza XPO",4.3,2300,"25 min","7,6 km",18.00,0,None,"/speisekarte/pizza-xpo-krefeld-47798"),
 ("Sorrento Trattoria Pizzeria",4.4,1000,"10 min","1,3 km",15.00,1,None,"/speisekarte/sorrento-trattoria-pizzeria"),
 ("Pizza King",4.7,38,"40 min","1,5 km",16.00,1,None,"/speisekarte/pizza-king-krefeld"),
 ("Pizza & Burger Bulls",4.1,470,"25 min","2,3 km",9.90,1,None,"/speisekarte/pizza-burger-bulls-krefeld"),
 ("Pizza Live",4.6,290,"30 min","1,5 km",12.50,0,None,"/speisekarte/pizza-live-traarer-strasse"),
 ("Pizzeria Ciao",4.8,200,"40 min","2,9 km",35.00,0,None,"/speisekarte/pizzeria-ciao-bockumer-platz"),
 ("Tiger Snacks",4.7,94,"35 min","7 km",35.00,0,None,"/speisekarte/tiger-food"),
 ("Rumelner Grill",4.8,3100,"15 min","3,9 km",20.00,1,None,"/speisekarte/rumelner-grill"),
 ("Melt n Munch",3.8,43,"25 min","2,3 km",9.90,1,None,"/speisekarte/melt-n-munch-krefeld-uerdinger-strasse"),
 ("Indische Küche",4.3,430,"35 min","5,9 km",20.00,1,None,"/speisekarte/indische-kuche-krefeld"),
 ("Chicken Chuck",3.8,130,"25 min","2,3 km",9.90,1,None,"/speisekarte/chicken-chuck-krefeld"),
 ("Pizza Cab",4.6,900,"30 min","6,2 km",19.90,0,None,"/speisekarte/pizza-cab-alte-linner-strasse"),
 ("McDonald's®",3.5,550,"20 min","2,8 km",15.00,0,None,"/speisekarte/mcdonalds-krefeld-bruchfeld"),
 ("Loco Chicken | Krefeld Mitte",4.0,140,"30 min","7,6 km",25.00,0,None,"/speisekarte/loco-chicken-krefeld-mitte-1"),
 ("Pizza Hero",4.2,4500,"20 min","6 km",15.90,1,None,"/speisekarte/pizza-hero-1"),
 ("Green Club (Duisburg)",4.8,1000,"25 min","11,3 km",29.00,0,None,"/speisekarte/green-club-duisburg"),
 ("finefine Healthy Food",4.5,590,"25 min","11 km",35.00,0,None,"/speisekarte/finefine-healthy-food-krefeld-hochbendweg"),
 ("Pizza Boyz",4.0,110,"30 min","6,3 km",23.00,0,None,"/speisekarte/pizza-boys-krefeld"),
 ("Sorrento Bros",3.9,150,"35 min","6,5 km",50.00,0,None,"/speisekarte/sorrento-bros-krefeld"),
 ("DöPi (47829)",4.3,7,"35 min","1,3 km",15.00,0,None,"/speisekarte/dopi-47829"),
 ("Bodrum Pizza & Döner",4.4,140,"35 min","7,3 km",40.00,0,None,"/speisekarte/bodrum-pizza-doner-marktstrasse-121-krefeld"),
 ("Thunderbuns | Smash Burgers",4.4,110,"35 min","6,3 km",25.00,0,None,"/speisekarte/thunderbuns-smash-burgers-krefeld-krefeld"),
]
for p in lf_partners:
    cur.execute("INSERT INTO platform_partners(platform,name,plz,rating,rating_count,delivery_min,distance,min_order,free_delivery,promo,url,notes,snapshot_date) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)",
      ("Lieferando",p[0],"47829",p[1],p[2],p[3],p[4],p[5],p[6],p[7],LF+p[8],"Partner liefert nach 47829 Bergstr.",SNAP))

# --- Uber Eats stores visible on city page (Krefeld) ---
ue_stores = [
 ("FAVO Krefeld","Asiatische Fusionsküche/Sushi","Breite Str. 48, 47798","Neu",None,"/de/store/favo-krefeld/c2J2IYDRUPKRvZc0FQsbKQ"),
 ("Maki & Tea Krefeld","Japanisch/Sushi","Kölner Straße 58, 47805","2 für 1 Deal",None,"/de/store/maki-%26-tea-krefeld/z3LjhXu-VT2OJCnJumaj7w"),
 ("DÖNALADN meats(s) BÖRGALADN","Burger/Halal/Kebab","Ostwall 45, 47798","1+1 gratis",None,"/de/store/donaladn-meats-borgaladn-krefeld/G_wpArGhXEaU93IJdSsXGA"),
 ("BOOM Smashburger","Burger/Hot Dog/Salate/Halal","Alte Krefelder Straße 23, 47829","2 für 1 Deal",None,"/de/store/boom-smashburger/Mrd62bBtQTqNp1eY6nsA7A"),
 ("Köstlicher Krefeld","Kebab/Street Food","Friedrichstraße 16, 47798","4 € Ersparnis ab 15 €",None,"/de/store/kostlicher-krefeld/9G8fuBMGVQyBxV4lXIunVQ"),
 ("Domino's Krefeld Inrath","Pizza/Amerikanisch","Hülser Straße 88, 47803","30% Rabatt",None,"/de/store/dominos-krefeld-inrath/fnsTe9_CU5GYR4Cf_dHVHg"),
 ("Pizza Cab Krefeld","Pizza/Italienisch","Alte Linner Straße 77, 47799","2 für 1 Deal",None,"/de/store/pizza-cab-krefeld/buXkCGb5VD6pB1XLm1_WyA"),
 ("New York Pizza Willich","Pizza","Parkstraße 40, Willich","2 für 1 Deal",None,"/de/store/new-york-pizza-willich/C3f1Q7FLX669VP0KCWBBMQ"),
 ("XPO Pizza","Pizza/Italienisch/Asiatisch/Burger","Frankenring 27, 47798","2 für 1 Deal",None,"/de/store/xpo-pizza/G1bsGw8aUQuti4wZ9UqVXA"),
 ("TAKE – THE GOOD FOOD","Burger/Amerikanisch/Sandwiches/Halal","Untergath 30, 47805",None,None,"/de/store/take-–-the-good-food-krefeld/8E58k_Q4Wvuo1NzSdABWwQ"),
 ("R'n'Beef","Burger/Amerikanisch/Halal","Carl-Wilhelm-Strasse 28, 47798","20% Rabatt",None,"/de/store/rnbeef-krefeld/DjyfgNZdXzSKbX0KtAj4Kw"),
 ("Indian Heritage","Indisch/Vegetarisch","Carl-Wilhelm-Straße 27, 47798",None,None,"/de/store/indian-heritage/dTSVTWHHVsi4ARuFlla20A"),
 ("Loco Chicken Krefeld Mitte","Hühnchen/Halal/Fried Chicken/Burger/Wraps","Frankenring 27, 47798","20% Rabatt",None,"/de/store/loco-chicken-krefeld-mitte/0XtgEQsaXSyJMElDZhND9w"),
 ("Ali Baba","Nahöstlich/Arabisch/Hühnchen","Ostwall 20, 47798","20% Rabatt",None,"/de/store/ali-baba/LGjSm98xVyupy5n2eDw-4Q"),
 ("Tele Pizza Krefeld","Pizza","Nordwall 76, 47798",None,None,"/de/store/tele-pizza-krefeld/-WgF6FMjUHmtas7Spsv1XQ"),
 ("Pizzeria Tosca Krefeld","Pizza/Italienisch/Pasta","Kölner Str. 547, 47807","10% Rabatt",None,"/de/store/pizzeria-tosca-krefeld/tBgRFfrGVJ-OaqIlErBLQA"),
 ("Burger Kitchen Parkstraße","Burger/Amerikanisch/Sandwiches","Parkstraße 40, Willich",None,None,"/de/store/burger-kitchen-parkstrasse/PALUfwq9Vfmt6hoQzOZj1A"),
 ("Master Pizza & More","Kebab/Street Food","Traarer Straße 60, 47829",None,None,"/de/store/master-pizza-%26-more/t-6CM9sqRYeQtpOI9FNmOw"),
 ("finefine Healthy Food","Poke/Hawaiianisch","Hochbendweg 17, 47804","2 für 1 Deal",None,"/de/store/finefine-healthy-food-krefeld/11AKyPG9QMuNqQ6CN655Yw"),
 ("Pizzeria Da Nico","Pizza/Amerikanisch/Italienisch","Türkenbruch 16, 47809",None,None,"/de/store/pizzeria-da-nico/pXkX6Pw7WUinOOTHlCfvhQ"),
]
for n,c,a,promo,r,url in ue_stores:
    cur.execute("INSERT INTO platform_partners(platform,name,plz,rating,rating_count,delivery_min,distance,min_order,free_delivery,promo,url,notes,snapshot_date) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)",
      ("Uber Eats",n,None,None,None,None,None,0,promo,UE," | ".join(filter(None,[c,a])),
       f"Seite {UE}{url}",SNAP))

# --- new sources ---
new_sources = [
 ("master_pizza_more", f"{LF}/speisekarte/master-pizza-more","Lieferando-Speisekarte: Master Menüs erfasst, weitere Kategorien JS-geladen. Lieferpartner-Daten 47829-Liste"),
 ("dopi", f"{LF}/speisekarte/dopi-47829","Lieferando-Speisekarte: Pizza-Kategorie erfasst"),
 ("sorrento", f"{LF}/speisekarte/sorrento-trattoria-pizzeria","Lieferando-Speisekarte: Pasta-Tag erfasst, Rest JS"),
 ("boom_smash", f"{LF}/speisekarte/boom-smash","Lieferando-Liste 47829: 4.9★, min 15"),
 ("platform_lieferando", LF47829,"Partnerliste für 47829: 25 Partner mit Bewertung/Mindestbestellwert/Lieferzeit (Tabelle platform_partners)"),
 ("platform_lieferando", "https://www.ubereats.com/de/city/krefeld-nw","Uber Eats Krefeld-Store-Liste (Tabelle platform_partners)"),
]
for sid,u,w in new_sources:
    cur.execute("INSERT INTO sources(service_id,url,what,fetched_date) VALUES(?,?,?,?)",(sid,u,w,SNAP))

# --- partial menu items from Lieferando pages ---
def add(sid, cat, name, desc, price, note="ab-Preis (Größe wählbar)"):
    cur.execute("""INSERT INTO menu_items(service_id,category,name,description,size,price,price_type,available,source_url)
      VALUES(?,?,?,?,?,?,?,1,?)""",(sid,cat,name,desc,None,price,"standard",f"{LF}/speisekarte/"))
dopi_pizzas = [
 ("Pizza Margherita","Tomaten, Gouda",6.40),("Pizza Salami","Rindersalami",7.90),
 ("Pizza Tonno","Thunfisch",7.90),("Pizza Funghi","Champignons",7.90),
 ("Pizza Spinaci","Spinat",7.90),("Pizza Prosciutto","Putenschinken",7.90),
 ("Pizza Döpi","Drehspieß, Peperoni",8.40),("Pizza Chef","Putenschinken, Rindersalami, Thunfisch, Paprika",8.40),
 ("Pizza Grek","Käseimitat Puma, Paprika, Oliven",8.40),("Pizza Hawaii","Putenschinken, Ananas",8.40),
 ("Pizza Drehspieß 1","Drehspieß, Zwiebeln",8.40),("Pizza Rialto","Spinat, Thunfisch",8.40),
 ("Pizza Diavolo","Rindersalami, Zwiebeln, Peperoni",8.40),("Pizza Toscana","Putenschinken, Champignons",8.40),
 ("Pizza Mexico","Putenschinken, Rindersalami, Jalapenos",8.90),
 ("Pizza Döpi Hollandaise 1","Hollandaise, Drehspieß",8.90),
 ("Pizza Döpi Hollandaise 2","Hollandaise, Drehspieß, Jalapenos",8.90),
 ("Pizza Mozzarella","frische Tomaten, Mozzarella",8.90),
 ("Pizza Napolicana","Sardellen, Zwiebeln, Oliven",8.90),
 ("Pizza Döpi Spezial","Drehspieß, Paprika, Zwiebeln, Peperoni",8.90),
]
for n,d,p in dopi_pizzas: add("dopi","Pizza",n,d,p)
cur.execute("""INSERT INTO menu_items(service_id,category,name,description,size,price,price_type,available,source_url)
  VALUES('sorrento','Pasta-Tag','Pasta-Angebot','1 Nudelgericht nach Wahl',NULL,8.00,'action',1,?)""",
  (f"{LF}/speisekarte/sorrento-trattoria-pizzeria",))
master_menus = [
 ("Menü 1","Drehspießtasche + Pommes + Getränk 0,33 l",10.80),("Menü 2","Dönerteller + 2 Baklava + Getränk 0,33 l",12.80),
 ("Menü 3 (2 Pers.)","2 Dönertaschen + 2 Pommes + Getränk 1,0 l",21.00),("Menü 4","Burger + kleine Pommes + Getränk 0,33 l",14.30),
 ("Menü 5","Dürüm + Pommes + Getränk 0,33 l",11.80),("Menü 6","Lahmacun + Pommes + Getränk 0,33 l",12.80),
 ("Menü 7","Pizza 29 cm + kleiner Mixed Salat + Getränk 0,33 l",14.30),("Menü 8","Nudelgericht + kleiner Mixed Salat + Getränk 0,33 l",14.30),
]
for n,d,p in master_menus:
    cur.execute("""INSERT INTO menu_items(service_id,category,name,description,size,price,price_type,available,source_url)
      VALUES('master_pizza_more','Master Menüs',?,?,'ab',?,NULL,1,?)""",
      (n,d,p,f"{LF}/speisekarte/master-pizza-more"))
cur.execute("INSERT INTO menu_items(service_id,category,name,description,size,price,price_type,available,source_url) VALUES('dopi','Info','Wetterhinweis','Alle Fahrer unterwegs – Lieferung zeitweise nicht verfügbar, nur Abholung',NULL,NULL,'info',1,?)",
  (f"{LF}/speisekarte/dopi-47829",))

con.commit()
for t in ("services","menu_items","sources","platform_partners"):
    print(t, cur.execute(f"SELECT COUNT(*) FROM {t}").fetchone()[0])
con.close()

# rebuild JSON export
data = {"snapshot_date":SNAP}
cur = con.cursor()
cur.execute("SELECT * FROM services"); cols=[d[0] for d in cur.description]
data["services"]=[dict(zip(cols,r)) for r in cur.fetchall()]
cur.execute("SELECT * FROM menu_items"); cols=[d[0] for d in cur.description]
data["menu_items"]=[dict(zip(cols,r)) for r in cur.fetchall()]
cur.execute("SELECT * FROM sources"); cols=[d[0] for d in cur.description]
data["sources"]=[dict(zip(cols,r)) for r in cur.fetchall()]
cur.execute("SELECT * FROM platform_partners"); cols=[d[0] for d in cur.description]
data["platform_partners"]=[dict(zip(cols,r)) for r in cur.fetchall()]
con.close()
with open(JS,"w",encoding="utf-8") as f:
    json.dump(data,f,ensure_ascii=False,indent=1)
print("JSON rebuilt:", JS)
