#!/usr/bin/env python3
"""Stage 2b: enrich developer/integrator contacts from their public sites.
- visits primary_site + common contact pages
- extracts emails (incl. obfuscated), phones, socials, geo hints
- resumable via on-disk cache
"""
import re, os, json, time, sqlite3, urllib.request, urllib.error
from urllib.parse import urlparse, urljoin
from html import unescape

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, "raw_cache", "sites")
DB = os.path.join(HERE, "bitrix24_messenger_integrations.db")
os.makedirs(CACHE, exist_ok=True)

CONTACT_PATHS = ["", "contacts", "contact", "kontakty", "about", "o-kompanii", "company", "support"]

def fetch(url):
    fn = os.path.join(CACHE, re.sub(r'[^a-zA-Z0-9]+','_',url)[-140:]+".html")
    if os.path.exists(fn):
        return open(fn, encoding="utf-8", errors="replace").read()
    req = urllib.request.Request(url, headers={"User-Agent": UA, "Accept-Language": "ru,en"})
    try:
        with urllib.request.urlopen(req, timeout=25) as r:
            data = r.read()
            ctype = r.headers.get("Content-Type","")
            if "html" not in ctype and "text" not in ctype:
                data = b""
            html = data.decode("utf-8","replace")
    except Exception as e:
        html = ""
    open(fn,"w",encoding="utf-8").write(html)
    time.sleep(0.8)
    return html

OBF_AT = re.compile(r'\s*(?:\(|\[)?\s*(?:at|dog|собака|@)\s*(?:\)|\])?\s*', re.I)
def find_emails(text):
    text = unescape(text)
    # direct
    out = set(re.findall(r'[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}', text))
    # obfuscated: name [at] domain [dot] tld
    for m in re.finditer(r'([a-zA-Z0-9._%+-]{2,})\s*(?:\[|\(|\{)?\s*(?:at|dog|собака)\s*(?:\]|\)|\})?\s*([a-zA-Z0-9.-]{2,})\s*(?:\[|\(|\{)?\s*(?:dot|точка)\s*(?:\]|\)|\})?\s*([a-zA-Z]{2,})', text, re.I):
        out.add(f"{m.group(1)}@{m.group(2)}.{m.group(3)}")
    bad = ('.png','.jpg','.jpeg','.svg','.webp','.gif','.css','.js','example.com','sentry')
    return sorted(e for e in out if not e.lower().endswith(bad) and 'example.' not in e.lower())

def find_phones(text):
    text = re.sub(r'[^\d+()\-\s]', ' ', text)
    cands = re.findall(r'(?:\+7|\b8|\+[1-9]\d{0,3})[\s\-\(\)]?\d{3}[\s\-\(\)]?\d{3}[\s\-]?\d{2}[\s\-]?\d{2}', text)
    norm = []
    for c in cands:
        d = re.sub(r'\D','',c)
        if len(d) == 11 and d[0] in '78':
            norm.append('+7'+d[1:])
        elif len(d) >= 10:
            norm.append('+'+d)
    return sorted(set(norm))

def find_socials(html):
    out = {}
    for name, pat in [('telegram', r'https?://t\.me/[A-Za-z0-9_]+'),
                      ('whatsapp', r'https?://wa\.me/[0-9]+'),
                      ('vk', r'https?://vk\.com/[A-Za-z0-9_.]+'),
                      ('youtube', r'https?://(?:www\.)?youtube\.com/[^\s"\'<>]+')]:
        out[name] = sorted(set(re.findall(pat, html)))[:5]
    return out

GEO = {
 'ru':'Россия','by':'Беларусь','kz':'Казахстан','ua':'Украина','uz':'Узбекистан',
 'com':'com (неоднозначно)','pro':'pro (неоднозначно)',
}
CITIES = {
 'москва':'Москва','санкт-петербург':'Санкт-Петербург','новосибирск':'Новосибирск',
 'екатеринбург':'Екатеринбург','казань':'Казань','минск':'Минск','алматы':'Алматы',
 'астана':'Астана','нур-султан':'Астана','киев':'Киев','тбилиси':'Тбилиси','бишкек':'Бишкек',
 'московская':'Московская обл.','петербург':'СПб',
}

def guess_geo(url, html, emails):
    hints = set()
    dom = urlparse(url).netloc.lower()
    tld = dom.rsplit('.',1)[-1]
    if tld in GEO: hints.add(GEO[tld])
    for em in emails:
        t = em.rsplit('.',1)[-1].lower()
        if t in GEO: hints.add(GEO[t]+' (email)')
    low = re.sub(r'<[^>]+>',' ',html).lower()
    for city, name in CITIES.items():
        if city in low: hints.add(name)
    # инн/огрн → точно РФ
    if re.search(r'\bИНН\b', low) or re.search(r'\bОГРН\b', low):
        hints.add('Россия (ИНН/ОГРН)')
    return sorted(hints)

def parse(text, html, url, emails):
    return {
        'emails': emails,
        'phones': find_phones(re.sub(r'<[^>]+>',' ',text)),
        'socials': find_socials(html),
        'geo': guess_geo(url, html, emails),
    }

def main():
    con = sqlite3.connect(DB)
    con.executescript("""
    CREATE TABLE IF NOT EXISTS app_contacts (
        app_id TEXT PRIMARY KEY,
        site TEXT, emails TEXT, phones TEXT, socials TEXT, geo TEXT,
        status TEXT, enriched_at TEXT DEFAULT CURRENT_TIMESTAMP
    );""")
    rows = con.execute("""SELECT app_id, primary_site, emails FROM apps
                          WHERE primary_site LIKE 'http%' ORDER BY num_installs DESC""").fetchall()
    total = len(rows)
    for k,(app_id, site, known_emails) in enumerate(rows,1):
        if con.execute("SELECT 1 FROM app_contacts WHERE app_id=?", (app_id,)).fetchone():
            continue
        all_emails=set(json.loads(known_emails or '[]'))
        all_phones=set(); socials={}; geo=set(); got=0
        base = site.rstrip('/')+'/'
        for p in CONTACT_PATHS:
            url = urljoin(base, p)
            html = fetch(url)
            if not html: continue
            got += 1
            em = find_emails(html)
            all_emails.update(em)
            txt = re.sub(r'<[^>]+>',' ', html)
            parsed = parse(txt, html, url, em)
            all_phones.update(parsed['phones'])
            for k2,v in parsed['socials'].items():
                socials.setdefault(k2, [])
                for x in v:
                    if x not in socials[k2]: socials[k2].append(x)
            geo.update(parsed['geo'])
        status = 'ok' if got else 'unreachable'
        con.execute("INSERT OR REPLACE INTO app_contacts (app_id,site,emails,phones,socials,geo,status) VALUES (?,?,?,?,?,?,?)",
                    (app_id, site,
                     json.dumps(sorted(all_emails),ensure_ascii=False),
                     json.dumps(sorted(all_phones),ensure_ascii=False),
                     json.dumps(socials,ensure_ascii=False),
                     json.dumps(sorted(geo),ensure_ascii=False),
                     status))
        con.commit()
        print(f"[{k}/{total}] {urlparse(site).netloc}: +{len(all_emails)-len(json.loads(known_emails or '[]'))} emails, {len(all_phones)} phones, geo={sorted(geo)}")
    n=con.execute("SELECT COUNT(*) FROM app_contacts WHERE status='ok'").fetchone()[0]
    print(f"\nEnriched OK: {n}/{total}")

if __name__ == "__main__":
    main()