#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""Post-scrape cleanup + JSON rebuild.
- decode %5C-urlencoded artifacts and \\n in descriptions/names
- drop 'Wetterhinweis' info rows and empty names
- set notes on scraped services, rebuild lieferdienste_47829.json"""
import sqlite3, json, os, urllib.parse

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()

def clean(t):
    if not t: return t
    t=str(t)
    t=t.replace('%5Cn',' • ').replace('%5Ct',' ').replace('%5C%5Cn',' • ')
    if '%' in t:
        try:
            dec=urllib.parse.unquote(t)
            # only accept if it doesn't inject quotes/spurious chars en masse
            if dec.count('"')<=t.count('"')+2: t=dec
        except Exception: pass
    t=t.replace('\\n',' • ').replace('\\"','"')
    t=' '.join(t.split())
    return t.strip(' •') or None

cur.execute("SELECT id,name,description FROM menu_items")
rows=cur.fetchall()
for i,n,d in rows:
    n2=clean(n); d2=clean(d)
    if n2!=n or d2!=d:
        cur.execute("UPDATE menu_items SET name=?, description=? WHERE id=?",(n2,d2,i))
cur.execute("DELETE FROM menu_items WHERE name IS NULL OR name='' OR category='Info'")
# manual pre-scrape partial rows now superseded by full scrapes
cur.execute("""DELETE FROM menu_items WHERE item_uid IS NULL
 AND source_url LIKE '%lieferando.de/speisekarte%'
 AND service_id IN ('dopi','master_pizza_more','sorrento','bei_genc')""")

# mark scraped services
cur.execute("""UPDATE services SET notes=notes||' | Volle Speisekarte via headless-chromium-Scrape 17.11.2026 (Cache data/cache/), item_uid = Lieferando-ID.'
 WHERE kind LIKE 'platform:Lieferando' AND id IN (SELECT DISTINCT service_id FROM menu_items WHERE item_uid IS NOT NULL)""")

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

# rebuild JSON
data={"snapshot_date":SNAP}
for tab in ("services","menu_items","sources","platform_partners"):
    cur.execute(f"SELECT * FROM {tab}"); cols=[d[0] for d in cur.description]
    data[tab]=[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:",JS, sum(len(v) for v in data.values() if isinstance(v,list)),"rows")
