#!/usr/bin/env python3
"""Stage 1: parse the B24 marketplace collection -> SQLite."""
import re, json, sqlite3, urllib.request, time, os

COLLECTION = "21378648"
BASE = "https://www.bitrix24.ru"
UA = "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/125.0 Safari/537.36"
HERE = os.path.dirname(os.path.abspath(__file__))
CACHE = os.path.join(HERE, "cache_collection")
DB = os.path.join(HERE, "bitrix24_messenger_integrations.db")
os.makedirs(CACHE, exist_ok=True)

def fetch(url):
    fn = os.path.join(CACHE, re.sub(r'[^a-zA-Z0-9]+','_',url)[-120:]+".html")
    if os.path.exists(fn):
        return open(fn, encoding="utf-8").read()
    req = urllib.request.Request(url, headers={"User-Agent": UA, "Accept-Language": "ru,en"})
    html = urllib.request.urlopen(req, timeout=60).read().decode("utf-8", "replace")
    open(fn, "w", encoding="utf-8").write(html)
    time.sleep(1.2)
    return html

def extract_json(html, varname="marketListData"):
    i = html.find(f"const {varname} = ")
    if i < 0: return None
    s = html[i+len(f"const {varname} = "):]
    depth = 0; end = None; instr = False; esc = False
    for j, c in enumerate(s):
        if instr:
            if esc: esc = False
            elif c == '\\': esc = True
            elif c == '"': instr = False
            continue
        if c == '"': instr = True
        elif c == '{': depth += 1
        elif c == '}':
            depth -= 1
            if depth == 0: end = j+1; break
    return json.loads(s[:end])

def init_db(con):
    con.executescript("""
    DROP TABLE IF EXISTS apps;
    DROP TABLE IF EXISTS partners;
    CREATE TABLE apps (
        app_id TEXT PRIMARY KEY, code TEXT, name TEXT, short_desc TEXT, descr TEXT,
        free TEXT, num_installs INTEGER, partner_id TEXT, partner_name TEXT,
        rating REAL, rating_float REAL, reviews_number INTEGER,
        date_public TEXT, date_public_ts INTEGER, price_policy TEXT, price_policy_name TEXT,
        labels TEXT, icon TEXT, app_url TEXT, collection_id TEXT, page INTEGER,
        parsed_at TEXT DEFAULT CURRENT_TIMESTAMP
    );
    CREATE TABLE partners (
        partner_id TEXT PRIMARY KEY, name TEXT, apps_count INTEGER
    );
    """)

def main():
    con = sqlite3.connect(DB); init_db(con)
    seen = {}
    for page in range(1, 6):
        url = f"{BASE}/apps/collection/{COLLECTION}/" + ("" if page==1 else f"?PAGEN_1={page}")
        html = fetch(url)
        data = extract_json(html)
        if not data:
            print("page", page, "no json"); continue
        for a in data["result"]["APPS"]:
            seen[a["ID"]] = (a, page)
        print(f"page {page}: {len(data['result']['APPS'])} apps (total {len(seen)})")

    partner_apps = {}
    for app_id, (a, page) in seen.items():
        labels = json.dumps(a.get("LABELS"), ensure_ascii=False)
        con.execute("""INSERT OR REPLACE INTO apps
            (app_id,code,name,short_desc,descr,free,num_installs,partner_id,partner_name,
             rating,rating_float,reviews_number,date_public,date_public_ts,price_policy,
             price_policy_name,labels,icon,app_url,collection_id,page)
            VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)""",
            (a["ID"], a["CODE"], a["NAME"], a.get("SHORT_DESC"), a.get("DESC"),
             a.get("FREE"), int(a.get("NUM_INSTALLS") or 0), a.get("PARTNER_ID"),
             a.get("PARTNER_NAME"), a.get("RATING"), a.get("RATING_FLOAT"),
             a.get("REVIEWS_NUMBER"), a.get("DATE_PUBLIC"), a.get("DATE_PUBLIC_TS"),
             a.get("PRICE_POLICY"), a.get("PRICE_POLICY_NAME"), labels, a.get("ICON"),
             f"{BASE}/apps/app/{a['CODE']}/", COLLECTION, page))
        pid = a.get("PARTNER_ID")
        if pid:
            partner_apps.setdefault(pid, {"name": a.get("PARTNER_NAME"), "n": 0})
            partner_apps[pid]["n"] += 1

    for pid, info in partner_apps.items():
        con.execute("INSERT OR REPLACE INTO partners (partner_id,name,apps_count) VALUES (?,?,?)",
                    (pid, info["name"], info["n"]))
    con.commit()
    n = con.execute("SELECT COUNT(*) FROM apps").fetchone()[0]
    p = con.execute("SELECT COUNT(*) FROM partners").fetchone()[0]
    print(f"\nDB saved: {DB}")
    print(f"apps={n}, partners={p}")

if __name__ == "__main__":
    main()