"""ITNA centralized RADIUS provisioner. Reconciles sold/disabled vouchers every cycle.
The web SQLite database remains the source of truth. Fails closed for newly sold vouchers
if SQL is unavailable. Does not disconnect existing sessions (CoA not implemented).
"""
import os, sqlite3, time, logging, ipaddress, json, pathlib
import pymysql
logging.basicConfig(level=logging.INFO,format='%(asctime)s %(levelname)s %(message)s')
DB='/data/app.sqlite'
INTERVAL=max(10,int(os.environ.get('SYNC_INTERVAL','30')))

def desired_vouchers(sqlite_path=DB):
    # Explicitly select SOLD and active packages; never provision unsold vouchers.
    uri=f'file:{sqlite_path}?mode=ro'
    with sqlite3.connect(uri,uri=True,timeout=5) as db:
        db.row_factory=sqlite3.Row
        data=db.execute("""SELECT v.code,v.pin,p.minutes,p.speed FROM vouchers v
           JOIN packages p ON p.id=v.package_id WHERE v.status='sold' AND p.active=1""").fetchall()
        return {r['code']:dict(r) for r in data}

def radius_attributes(row):
    speed=row['speed']
    # This protects provisioning from invalid values inserted outside the web UI.
    import re
    if not re.fullmatch(r'[0-9]+[kMG]/[0-9]+[kMG]',speed,re.I):raise ValueError('Invalid rate')
    seconds=int(row['minutes'])*60
    if seconds<=0 or seconds>525600*60:raise ValueError('Invalid duration')
    return [('Cleartext-Password',':=',str(row['pin'])),('Max-All-Session',':=',str(seconds))], [('Mikrotik-Rate-Limit',':=',speed),('Acct-Interim-Interval',':=','60'),('Session-Timeout',':=','3600')]

def reconcile(db,desired):
    # Keep ownership of ONLY ITNA- accounts; don't modify non-ITNA RADIUS identities.
    with db.cursor() as cur:
      cur.execute("SELECT DISTINCT username FROM radcheck WHERE username LIKE 'ITNA-%'")
      existing={r[0] for r in cur.fetchall()}
      for username in existing-set(desired):
        cur.execute('DELETE FROM radcheck WHERE username=%s',(username,))
        cur.execute('DELETE FROM radreply WHERE username=%s',(username,))
      for username,row in desired.items():
        checks,replies=radius_attributes(row)
        # Small installation: upsert by replace inside a SQL transaction.
        # Accounting history deliberately remains intact across re-provisioning.
        cur.execute('SELECT attribute,op,value FROM radcheck WHERE username=%s',(username,))
        old_checks=set(cur.fetchall())
        cur.execute('SELECT attribute,op,value FROM radreply WHERE username=%s',(username,))
        old_replies=set(cur.fetchall())
        if old_checks==set(checks) and old_replies==set(replies):continue
        cur.execute('DELETE FROM radcheck WHERE username=%s',(username,))
        cur.execute('DELETE FROM radreply WHERE username=%s',(username,))
        cur.executemany('INSERT INTO radcheck(username,attribute,op,value) VALUES(%s,%s,%s,%s)',[(username,*r) for r in checks])
        cur.executemany('INSERT INTO radreply(username,attribute,op,value) VALUES(%s,%s,%s,%s)',[(username,*r) for r in replies])
    db.commit()
    return len(desired)

def write_accounting_snapshot(db):
    """Publish non-personal session counts, without credentials or device identifiers."""
    with db.cursor() as cur:
        cur.execute("SELECT COUNT(*) FROM radacct WHERE acctstoptime IS NULL")
        online=int(cur.fetchone()[0])
        cur.execute("SELECT COUNT(*) FROM radacct WHERE acctstarttime >= UTC_DATE()")
        sessions_today=int(cur.fetchone()[0])
        cur.execute("SELECT COALESCE(SUM(COALESCE(acctinputoctets,0)+COALESCE(acctoutputoctets,0)),0) FROM radacct WHERE acctstarttime >= UTC_DATE()")
        octets_today=int(cur.fetchone()[0])
    out=pathlib.Path('/runtime');out.mkdir(exist_ok=True)
    snapshot={'updated_at_utc':time.strftime('%Y-%m-%dT%H:%M:%SZ',time.gmtime()),'online_sessions':online,'sessions_started_today':sessions_today,'traffic_bytes_sessions_started_today':octets_today}
    temp=out/'accounting.json.tmp';temp.write_text(json.dumps(snapshot));temp.replace(out/'accounting.json')

def main():
    last_err=None
    while True:
      try:
        wanted=desired_vouchers()
        db=pymysql.connect(host=os.environ.get('DB_HOST','db'),user='radius',password=os.environ['DB_PASSWORD'],database='radius',charset='utf8mb4',autocommit=False,connect_timeout=5)
        try:
            count=reconcile(db,wanted)
            write_accounting_snapshot(db)
        except Exception: db.rollback();raise
        finally: db.close()
        pathlib.Path('/runtime').mkdir(exist_ok=True)
        pathlib.Path('/runtime/status.json').write_text(json.dumps({'last_success_utc':time.strftime('%Y-%m-%dT%H:%M:%SZ',time.gmtime()),'provisioned':count}))
        if last_err:logging.info('Synchronization recovered')
        last_err=None
      except Exception as exc:
        message=type(exc).__name__+': '+str(exc)
        if message!=last_err:logging.error('RADIUS synchronization failed: %s',message)
        last_err=message
      time.sleep(INTERVAL)
if __name__=='__main__':main()
