"""Query Pi-hole v6 FTL database from host.
Requires: the DB copied to /tmp/pihole-FTL.db (see SKILL.md for copy command).
Usage: python3 query-db.py recent|clients|status14|blocks|all
"""
import sqlite3, sys

DB = '/tmp/pihole-FTL.db'

def recent(n=30):
    cur.execute("SELECT datetime(timestamp,'unixepoch','localtime'), domain, client, status FROM queries ORDER BY id DESC LIMIT ?", (n,))
    for ts, dom, cl, st in cur.fetchall():
        print(f"{ts} | {cl:22} | {st:2} | {dom[:60]}")

def clients(hours=1):
    cur.execute("SELECT client, count(*) FROM queries WHERE timestamp > strftime('%s','now',?||' hours') GROUP BY client ORDER BY count(*) DESC", (f'-{hours}',))
    for cl, cnt in cur.fetchall():
        print(f"  {cl:22} -> {cnt} queries")

def status14(n=20):
    cur.execute("SELECT domain, count(*) as cnt FROM queries WHERE status=14 GROUP BY domain ORDER BY cnt DESC LIMIT ?", (n,))
    for dom, cnt in cur.fetchall():
        print(f"  {cnt:5}x | {dom}")

def blocks(hours=1):
    cur.execute("SELECT datetime(timestamp,'unixepoch','localtime'), domain, client FROM queries WHERE status IN (1,4,5) AND timestamp > strftime('%s','now',?||' hours') ORDER BY id DESC LIMIT 30", (f'-{hours}',))
    for ts, dom, cl in cur.fetchall():
        print(f"  {ts} | {cl:22} | {dom}")

def all_stats():
    print("=== Status code distribution (last hour) ===")
    cur.execute("SELECT status, count(*) FROM queries WHERE timestamp > strftime('%s','now','-1 hour') GROUP BY status ORDER BY count(*) DESC")
    for st, cnt in cur.fetchall():
        print(f"  {st}: {cnt}")
    print("\n=== Blocked vs Allowed (last hour) ===")
    cur.execute("SELECT CASE WHEN status IN (1,4,5,6) THEN 'BLOCKED' ELSE 'ALLOWED' END, count(*) FROM queries WHERE timestamp > strftime('%s','now','-1 hour') GROUP BY 1")
    for lbl, cnt in cur.fetchall():
        print(f"  {lbl}: {cnt}")

if __name__ == '__main__':
    conn = sqlite3.connect(DB)
    cur = conn.cursor()
    cmd = sys.argv[1] if len(sys.argv) > 1 else 'all'
    {'recent': recent, 'clients': clients, 'status14': status14, 'blocks': blocks, 'all': all_stats}[cmd]()
    conn.close()