"""License server logic (port of Code.gs) on SQLite.

Speaks exactly the same protocol as the old Google Apps Script, so the desktop app and keygen.py keep working
(only their URL / keys change), and the website uses the same database.
"""
import datetime
import hmac
import os
import re
import sqlite3
import threading
import time

import config

SCHEMA = """
CREATE TABLE IF NOT EXISTS licenses(
  code TEXT PRIMARY KEY, seats INTEGER NOT NULL DEFAULT 1, customer TEXT DEFAULT '',
  created TEXT, status TEXT DEFAULT 'active', expires TEXT DEFAULT '');
CREATE TABLE IF NOT EXISTS activations(
  code TEXT, mid TEXT, name TEXT, pc TEXT, activated TEXT, last_seen TEXT, PRIMARY KEY(code, mid));
CREATE INDEX IF NOT EXISTS act_mid ON activations(mid);
CREATE TABLE IF NOT EXISTS trials(
  mid TEXT PRIMARY KEY, pc TEXT, started TEXT, started_ms INTEGER, ip TEXT DEFAULT '');
CREATE TABLE IF NOT EXISTS settings(key TEXT PRIMARY KEY, value TEXT);
"""

_local = threading.local()


def db():
    c = getattr(_local, "c", None)
    if c is None:
        os.makedirs(os.path.dirname(config.DB_PATH), exist_ok=True)
        c = sqlite3.connect(config.DB_PATH, timeout=30, isolation_level=None)
        c.row_factory = sqlite3.Row
        try:
            c.execute("PRAGMA journal_mode=WAL")
        except Exception:
            pass
        c.executescript(SCHEMA)
        _local.c = c
    return c


# ---------- helpers ----------
def norm(c):
    return re.sub(r"[^A-Z0-9]", "", str(c or "").upper())


def norm_name(n):
    s = re.sub(r"\s+", " ", str(n or "")).strip().lower()
    return s


def _tz():
    return datetime.timezone(datetime.timedelta(hours=config.EXPIRY_TZ_HOURS))


def today():
    return datetime.datetime.now(_tz()).strftime("%Y-%m-%d")


def stamp():
    return datetime.datetime.now(datetime.timezone.utc).strftime("%Y-%m-%d %H:%M") + " UTC"


def exp_str(v):
    s = str(v or "").strip()
    return s if re.fullmatch(r"\d{4}-\d{2}-\d{2}", s) else ""


def is_expired(exp):
    return bool(exp) and today() > exp


def same(a, b):
    return hmac.compare_digest(str(a or "").encode("utf-8"), str(b or "").encode("utf-8"))


def get_setting(key):
    r = db().execute("SELECT value FROM settings WHERE key=?", (key,)).fetchone()
    return str(r["value"]).strip() if r and r["value"] is not None else ""


def set_setting(key, val):
    db().execute("INSERT INTO settings(key,value) VALUES(?,?) ON CONFLICT(key) DO UPDATE SET value=excluded.value",
                 (key, str(val)))


def update_info():
    return {"version": get_setting("latest_version"), "url": get_setting("download_url"),
            "notes": get_setting("update_notes")}


def buy_url():
    return get_setting("buy_url") or config.BUY_URL


def find_license(code):
    r = db().execute("SELECT * FROM licenses WHERE code=?", (code,)).fetchone()
    if not r:
        return None
    try:
        seats = int(r["seats"]) or 1
    except Exception:
        seats = 1
    return {"seats": seats, "customer": str(r["customer"] or ""), "status": str(r["status"] or "active").lower(),
            "expires": exp_str(r["expires"])}


# ---------- customer actions ----------
def cfg(_p, _ip=""):
    u = update_info()
    return {"ok": True, "buy_url": buy_url(), "latest_version": u["version"],
            "download_url": u["url"], "update_notes": u["notes"]}


def activate(p, _ip=""):
    code, mid = norm(p.get("code")), str(p.get("mid") or "")
    name = str(p.get("name") or "")[:80]
    pc = str(p.get("pc") or "")[:60]
    if not code or not mid:
        return {"ok": False, "error": "invalid"}
    lic = find_license(code)
    if not lic:
        return {"ok": False, "error": "invalid"}
    if lic["status"] != "active":
        return {"ok": False, "error": "revoked"}
    if is_expired(lic["expires"]):
        return {"ok": False, "error": "expired", "expires": lic["expires"]}
    if norm_name(lic["customer"]) and norm_name(name) != norm_name(lic["customer"]):
        return {"ok": False, "error": "name"}
    c = db()
    rows = c.execute("SELECT mid FROM activations WHERE code=?", (code,)).fetchall()
    used = len(rows)
    if any(r["mid"] == mid for r in rows):                       # same PC / browser activating again
        c.execute("UPDATE activations SET name=?, last_seen=? WHERE code=? AND mid=?", (name, stamp(), code, mid))
        return {"ok": True, "seats": lic["seats"], "used": used, "reactivated": True}
    if used >= lic["seats"]:
        return {"ok": False, "error": "used", "seats": lic["seats"], "used": used}
    c.execute("INSERT INTO activations(code,mid,name,pc,activated,last_seen) VALUES(?,?,?,?,?,?)",
              (code, mid, name, pc, stamp(), stamp()))
    return {"ok": True, "seats": lic["seats"], "used": used + 1}


def check(p, _ip=""):
    code, mid = norm(p.get("code")), str(p.get("mid") or "")
    lic = find_license(code)
    if not lic:
        return {"ok": False, "error": "invalid"}
    if lic["status"] != "active":
        return {"ok": False, "error": "revoked"}
    if is_expired(lic["expires"]):
        return {"ok": False, "error": "expired", "expires": lic["expires"]}
    c = db()
    r = c.execute("SELECT 1 FROM activations WHERE code=? AND mid=?", (code, mid)).fetchone()
    if r:
        c.execute("UPDATE activations SET last_seen=? WHERE code=? AND mid=?", (stamp(), code, mid))
        return {"ok": True}
    return {"ok": False, "error": "not_activated"}


def delete_activations(code, mid=""):
    c = db()
    if mid:
        cur = c.execute("DELETE FROM activations WHERE code=? AND mid=?", (code, mid))
    else:
        cur = c.execute("DELETE FROM activations WHERE code=?", (code,))
    return cur.rowcount


def deactivate(p, _ip=""):
    delete_activations(norm(p.get("code")), str(p.get("mid") or ""))
    return {"ok": True}


def trial(p, ip=""):
    mid = str(p.get("mid") or "")
    if len(mid) < 8:
        return {"ok": False, "error": "invalid"}
    pc = str(p.get("pc") or "")[:60]
    c = db()
    now = int(time.time() * 1000)
    r = c.execute("SELECT started_ms FROM trials WHERE mid=?", (mid,)).fetchone()
    start_ms = int(r["started_ms"] or 0) if r else 0
    if not start_ms:
        # web browsers can clear cookies, so also limit new trials per IP address
        if mid.startswith("web-") and ip and config.TRIALS_PER_IP:
            since = now - 30 * 86400 * 1000
            n = c.execute("SELECT COUNT(*) AS n FROM trials WHERE ip=? AND started_ms>?", (ip, since)).fetchone()["n"]
            if n >= config.TRIALS_PER_IP:
                return {"ok": False, "error": "trial_expired"}
        start_ms = now
        c.execute("INSERT OR REPLACE INTO trials(mid,pc,started,started_ms,ip) VALUES(?,?,?,?,?)",
                  (mid, pc, stamp(), start_ms, ip if mid.startswith("web-") else ""))
    left = start_ms + int(config.TRIAL_HOURS * 3600000) - now
    if left <= 0:
        return {"ok": False, "error": "trial_expired"}
    return {"ok": True, "remaining": left // 1000}


# ---------- admin actions ----------
def admin_add(p, _ip=""):
    code = norm(p.get("code"))
    try:
        seats = int(p.get("seats"))
    except Exception:
        seats = 0
    if not code or seats < 1:
        return {"ok": False, "error": "bad_request"}
    if find_license(code):
        return {"ok": False, "error": "exists"}
    db().execute("INSERT INTO licenses(code,seats,customer,created,status,expires) VALUES(?,?,?,?,?,?)",
                 (code, seats, str(p.get("customer") or "")[:80], stamp(), "active", exp_str(p.get("expires"))))
    return {"ok": True}


def admin_list(_p, _ip=""):
    c = db()
    used, acts = {}, []
    for r in c.execute("SELECT * FROM activations ORDER BY rowid"):
        cd = norm(r["code"])
        used[cd] = used.get(cd, 0) + 1
        acts.append({"code": cd, "mid": str(r["mid"]), "name": str(r["name"] or ""), "pc": str(r["pc"] or ""),
                     "activated": str(r["activated"] or ""), "last_seen": str(r["last_seen"] or "")})
    lics = []
    for r in c.execute("SELECT * FROM licenses ORDER BY rowid"):
        cd = norm(r["code"])
        try:
            seats = int(r["seats"]) or 1
        except Exception:
            seats = 1
        lics.append({"code": cd, "seats": seats, "customer": str(r["customer"] or ""),
                     "created": str(r["created"] or ""), "status": str(r["status"] or "active"),
                     "used": used.get(cd, 0), "expires": exp_str(r["expires"])})
    return {"ok": True, "licenses": lics, "activations": acts, "update": update_info()}


def admin_reset(p, _ip=""):
    return {"ok": True, "removed": delete_activations(norm(p.get("code")))}


def admin_free(p, _ip=""):
    return {"ok": True, "removed": delete_activations(norm(p.get("code")), str(p.get("mid") or ""))}


def _status(p, status):
    code = norm(p.get("code"))
    if not find_license(code):
        return {"ok": False, "error": "invalid"}
    db().execute("UPDATE licenses SET status=? WHERE code=?", (status, code))
    return {"ok": True}


def admin_setseats(p, _ip=""):
    code = norm(p.get("code"))
    try:
        seats = int(p.get("seats"))
    except Exception:
        seats = 0
    if not find_license(code) or seats < 1:
        return {"ok": False, "error": "bad_request"}
    db().execute("UPDATE licenses SET seats=? WHERE code=?", (seats, code))
    return {"ok": True}


def admin_setupdate(p, _ip=""):
    v = str(p.get("version") or "").strip()
    u = str(p.get("url") or "").strip()
    n = str(p.get("notes") or "")[:300]
    if v:
        if not re.fullmatch(r"\d+(\.\d+){0,3}", v) or not re.match(r"https?://", u, re.I):
            return {"ok": False, "error": "bad_request"}
    else:
        u, n = "", ""
    set_setting("latest_version", v)
    set_setting("download_url", u)
    set_setting("update_notes", n)
    return {"ok": True}


def admin_set(p, _ip=""):
    """Change a setting. Only 'buy_url' is allowed.  ?action=admin_set&key=ADMIN&name=buy_url&value=https://..."""
    if str(p.get("name")) != "buy_url":
        return {"ok": False, "error": "bad_request"}
    v = str(p.get("value") or "").strip()
    if v and not re.match(r"https?://", v, re.I):
        return {"ok": False, "error": "bad_request"}
    set_setting("buy_url", v)
    return {"ok": True}


def admin_delete(p, _ip=""):
    code = norm(p.get("code"))
    if not code:
        return {"ok": False, "error": "bad_request"}
    removed = delete_activations(code)
    db().execute("DELETE FROM licenses WHERE code=?", (code,))
    return {"ok": True, "removed": removed}


CUSTOMER = {"activate": activate, "check": check, "deactivate": deactivate, "config": cfg, "trial": trial}
ADMIN = {
    "admin_add": admin_add, "admin_list": admin_list, "admin_reset": admin_reset, "admin_free": admin_free,
    "admin_revoke": lambda p, ip="": _status(p, "revoked"), "admin_restore": lambda p, ip="": _status(p, "active"),
    "admin_setseats": admin_setseats, "admin_setupdate": admin_setupdate, "admin_delete": admin_delete,
    "admin_set": admin_set,
}


def run(action, p, ip=""):
    """Runs one action inside a database transaction (like the script lock in Code.gs)."""
    fn = CUSTOMER.get(action) or ADMIN.get(action)
    if not fn:
        return {"ok": False, "error": "bad_request"}
    c = db()
    c.execute("BEGIN IMMEDIATE")
    try:
        out = fn(p, ip)
        c.execute("COMMIT")
        return out
    except Exception as e:
        try:
            c.execute("ROLLBACK")
        except Exception:
            pass
        return {"ok": False, "error": "server", "message": str(e)}


def route(p, ip=""):
    """Entry for the desktop app / keygen protocol (checks APP_KEY or ADMIN_KEY)."""
    a = str(p.get("action") or "")
    if a in CUSTOMER:
        if not same(p.get("k"), config.APP_KEY):
            time.sleep(0.4)
            return {"ok": False, "error": "auth"}
        return run(a, p, ip)
    if a.startswith("admin_") and a in ADMIN:
        if not same(p.get("key"), config.ADMIN_KEY):
            time.sleep(0.4)
            return {"ok": False, "error": "auth"}
        return run(a, p, ip)
    return {"ok": False, "error": "bad_request"}


# ---------- website helpers ----------
def web_status(mid, touch=False):
    """What may this browser do?  -> {'mode': 'licensed'|'trial'|'none', ...}"""
    c = db()
    reason = ""
    rows = c.execute("SELECT a.code AS code, a.name AS name, a.activated AS activated, l.status AS status, "
                     "l.expires AS expires FROM activations a JOIN licenses l ON l.code=a.code WHERE a.mid=?",
                     (mid,)).fetchall()
    for r in rows:
        st = str(r["status"] or "active").lower()
        ex = exp_str(r["expires"])
        if st != "active":
            reason = "revoked"
            continue
        if is_expired(ex):
            reason = "expired"
            continue
        if touch:
            c.execute("UPDATE activations SET last_seen=? WHERE code=? AND mid=?", (stamp(), r["code"], mid))
        code = str(r["code"])
        masked = (code[:4] + "-****-****-****-" + code[-4:]) if len(code) >= 8 else code
        return {"mode": "licensed", "name": str(r["name"] or ""), "code": masked,
                "activated": str(r["activated"] or ""), "expires": ex or "Lifetime"}
    t = c.execute("SELECT started_ms FROM trials WHERE mid=?", (mid,)).fetchone()
    if t and int(t["started_ms"] or 0):
        left = int(t["started_ms"]) + int(config.TRIAL_HOURS * 3600000) - int(time.time() * 1000)
        if left > 0:
            return {"mode": "trial", "trial_left": left // 1000}
        reason = reason or "trial_expired"
    return {"mode": "none", "reason": reason}


def web_code_for(mid):
    """The license code this browser is activated with (for deactivate)."""
    r = db().execute("SELECT code FROM activations WHERE mid=?", (mid,)).fetchall()
    return [str(x["code"]) for x in r]