import json, os, time, re, hashlib
from pathlib import Path
import pymysql
import requests
ROOT = Path(__file__).resolve().parent

def config():
    with open(os.environ.get('CHATREPORT_CONFIG', ROOT / 'config.json'), encoding='utf-8') as f:
        c = json.load(f)
    for k in ('api_token', 'db_password', 'secret_key', 'dashboard_password'):
        if not c.get(k) or 'REPLACE' in c[k]:
            raise RuntimeError('Complete config.json before starting')
    return c

def connect(c):
    return pymysql.connect(host=c['db_host'], user=c['db_user'], password=c['db_password'], database=c['db_name'], charset='utf8mb4', autocommit=True, cursorclass=pymysql.cursors.DictCursor, connect_timeout=10)

def init(db):
    with db.cursor() as q:
        q.execute('CREATE TABLE IF NOT EXISTS cr_state (k VARCHAR(100) PRIMARY KEY, v LONGTEXT NOT NULL)')
        q.execute('CREATE TABLE IF NOT EXISTS cr_chats (id BIGINT PRIMARY KEY, status VARCHAR(30), assignee VARCHAR(200), activity DOUBLE, updated DOUBLE, synced DOUBLE DEFAULT 0, before_id BIGINT DEFAULT 0, dirty TINYINT DEFAULT 1, category VARCHAR(200), summary TEXT, resolution TEXT, fingerprint VARCHAR(64), INDEX(activity), INDEX(dirty))')
        q.execute('CREATE TABLE IF NOT EXISTS cr_messages (id BIGINT PRIMARY KEY, chat_id BIGINT NOT NULL, ts DOUBLE NOT NULL, kind VARCHAR(20), content TEXT, INDEX(chat_id,ts))')
        q.execute('CREATE TABLE IF NOT EXISTS cr_login (ip VARCHAR(64) PRIMARY KEY, failures INT, until_ts DOUBLE)')

def state(db, k, default=None):
    with db.cursor() as q:
        q.execute('SELECT v FROM cr_state WHERE k=%s', (k,)); row=q.fetchone()
    return json.loads(row['v']) if row else default

def put(db,k,v):
    with db.cursor() as q:
        q.execute('REPLACE INTO cr_state(k,v) VALUES(%s,%s)', (k,json.dumps(v)))

def timestamp(v):
    if isinstance(v,(int,float)): return float(v)
    if isinstance(v,str):
        from datetime import datetime
        try: return datetime.fromisoformat(v.replace('Z','+00:00')).timestamp()
        except ValueError: return 0
    return 0

def clean(s):
    s=re.sub('<[^>]+>', ' ',s or '')
    s=re.sub(r'[\w.+-]+@[\w.-]+\.[a-zA-Z]+','[email]',s)
    return re.sub(r'\+?\d[\d ()-]{7,}\d','[number]',s).strip()[:4000]

def categorize(text,c):
    hits=[(sum(text.lower().count(w.lower()) for w in words), name) for name,words in c['categories'].items()]
    score,name=max(hits,default=(0,'Needs review'))
    return name if score else 'Needs review'

def report(db,c):
    cutoff=time.time()-86400
    with db.cursor() as q:
        q.execute('SELECT c.* FROM cr_chats c WHERE EXISTS (SELECT 1 FROM cr_messages m WHERE m.chat_id=c.id AND m.ts >= %s) OR c.activity >= %s ORDER BY c.activity DESC',(cutoff,cutoff)); rows=q.fetchall()
    groups={}
    for r in rows:
        name=r['category'] or 'Awaiting analysis'
        g=groups.setdefault(name,dict(name=name,count=0,resolved=0,pending=0,examples=[],resolutions=[],chats=[]))
        g['count']+=1; g['resolved' if r['status']=='resolved' else 'pending']+=1
        g['chats'].append(r)
        if r['summary'] and len(g['examples'])<3: g['examples'].append(r['summary'])
        if r['resolution'] and len(g['resolutions'])<3: g['resolutions'].append(r['resolution'])
    return sorted(groups.values(),key=lambda g:(-g['count'],g['name'])),rows