#!/usr/bin/env python3
import sqlite3, json, csv, os, re
from urllib.parse import urlparse
HERE=os.path.dirname(os.path.abspath(__file__))
con=sqlite3.connect(os.path.join(HERE,'bitrix24_messenger_integrations.db'))
con.row_factory=sqlite3.Row

apps=[dict(r) for r in con.execute("SELECT * FROM apps ORDER BY num_installs DESC")]
contacts={r['app_id']:dict(r) for r in con.execute("SELECT * FROM app_contacts")}

def j(v):
    try:return json.loads(v) if v else []
    except:return []

def merge_uniq(*lists):
    seen=[]; out=[]
    for l in lists:
        for x in (l or []):
            if x not in seen: seen.append(x); out.append(x)
    return out

rows=[]
for a in apps:
    c=contacts.get(a['app_id'],{})
    emails=merge_uniq(j(a['emails']), j(c.get('emails')))
    # убираем служебные/агрегаторные почты
    emails=[e for e in emails if not re.search(r'@(bitrix24|1c-bitrix)',e,re.I)]
    tgs=merge_uniq(j(a['telegram']))
    tgs=[t.replace('https://t.me/','@') for t in tgs]
    tgs=[t for t in tgs if t.lower() not in ('@bitrix24_ru','@bitrix24')]
    was=merge_uniq(j(a['whatsapp']))
    phones=merge_uniq(j(a['phones']), j(c.get('phones')))
    socials=j(c.get('socials'))
    if not isinstance(socials,dict): socials={}
    geo=merge_uniq(j(c.get('geo')))
    geo=[g for g in geo if 'неоднозначно' not in g]

    rows.append({
        'app_id': a['app_id'], 'code': a['code'], 'name': a['name'],
        'short_desc': a['short_desc'],
        'partner_id': a['partner_id'], 'partner_name': a['partner_name'],
        'num_installs': a['num_installs'], 'rating': a['rating_float'],
        'reviews': a['reviews_number'], 'date_public': a['date_public'],
        'price': a['price_policy_name'],
        'site': a['primary_site'] or '',
        'emails': '; '.join(emails),
        'telegram': '; '.join(tgs),
        'whatsapp': '; '.join(was),
        'phones': '; '.join(phones),
        'other_socials': '; '.join(sum([socials.get(k,[]) for k in ('vk','youtube')],[])),
        'geo': '; '.join(geo),
        'app_url': a['app_url'],
    })

cols=['app_id','code','name','short_desc','partner_id','partner_name','num_installs',
      'rating','reviews','date_public','price','site','emails','telegram','whatsapp',
      'phones','other_socials','geo','app_url']
with open(os.path.join(HERE,'bitrix24_messengers_enriched.csv'),'w',newline='',encoding='utf-8-sig') as f:
    w=csv.DictWriter(f,fieldnames=cols); w.writeheader(); w.writerows(rows)
with open(os.path.join(HERE,'bitrix24_messengers_enriched.json'),'w',encoding='utf-8') as f:
    json.dump(rows,f,ensure_ascii=False,indent=2)

# stats
stats={
 'apps':len(rows),
 'with_email':sum(1 for r in rows if r['emails']),
 'with_tg':sum(1 for r in rows if r['telegram']),
 'with_wa':sum(1 for r in rows if r['whatsapp']),
 'with_phone':sum(1 for r in rows if r['phones']),
 'with_site':sum(1 for r in rows if r['site']),
 'with_geo':sum(1 for r in rows if r['geo']),
 'unique_emails':len({e for r in rows for e in r['emails'].split('; ') if e}),
 'unique_partners':len({r['partner_id'] for r in rows}),
}
print(json.dumps(stats,ensure_ascii=False,indent=2))