import json
import sqlite3
import time
from contextlib import contextmanager
import config


@contextmanager
def conn():
    c = sqlite3.connect(config.DB_PATH, timeout=10)
    c.row_factory = sqlite3.Row
    try:
        yield c
        c.commit()
    finally:
        c.close()


def init():
    with conn() as c:
        c.executescript("""
        CREATE TABLE IF NOT EXISTS settings (k TEXT PRIMARY KEY, v TEXT);
        CREATE TABLE IF NOT EXISTS state (chat_id INTEGER PRIMARY KEY, action TEXT, data TEXT);
        CREATE TABLE IF NOT EXISTS dns_servers (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL,
            ip1 TEXT NOT NULL, ip2 TEXT, cat TEXT DEFAULT 'default', active INTEGER DEFAULT 1);
        CREATE TABLE IF NOT EXISTS ads (id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT NOT NULL, image_url TEXT,
            link TEXT NOT NULL, clicks INTEGER DEFAULT 0, active INTEGER DEFAULT 1);
        CREATE TABLE IF NOT EXISTS devices (device TEXT PRIMARY KEY, first_seen INTEGER, last_seen INTEGER, version TEXT);
        CREATE TABLE IF NOT EXISTS applies (id INTEGER PRIMARY KEY AUTOINCREMENT, dns_id INTEGER, device TEXT, ts INTEGER);
        """)


def get(k, default=None):
    with conn() as c:
        r = c.execute("SELECT v FROM settings WHERE k = ?", (k,)).fetchone()
        return r["v"] if r else default


def put(k, v):
    with conn() as c:
        c.execute("INSERT INTO settings (k, v) VALUES (?, ?) ON CONFLICT(k) DO UPDATE SET v = excluded.v", (k, str(v)))


def set_state(chat_id, action, data=None):
    with conn() as c:
        c.execute("INSERT INTO state (chat_id, action, data) VALUES (?, ?, ?) ON CONFLICT(chat_id) DO UPDATE "
                  "SET action = excluded.action, data = excluded.data", (chat_id, action, json.dumps(data or {})))


def get_state(chat_id):
    with conn() as c:
        r = c.execute("SELECT action, data FROM state WHERE chat_id = ?", (chat_id,)).fetchone()
        return {"action": r["action"], "data": json.loads(r["data"] or "{}")} if r else None


def clear_state(chat_id):
    with conn() as c:
        c.execute("DELETE FROM state WHERE chat_id = ?", (chat_id,))


def add_dns(name, ip1, ip2, cat):
    with conn() as c:
        return c.execute("INSERT INTO dns_servers (name, ip1, ip2, cat) VALUES (?, ?, ?, ?)",
                         (name, ip1, ip2, cat if cat in ("default", "pick") else "default")).lastrowid


def list_dns():
    with conn() as c:
        return [dict(r) for r in c.execute("SELECT * FROM dns_servers WHERE active = 1 ORDER BY id")]


def del_dns(i):
    with conn() as c:
        return c.execute("DELETE FROM dns_servers WHERE id = ?", (i,)).rowcount > 0


def add_ad(title, image, link):
    with conn() as c:
        return c.execute("INSERT INTO ads (title, image_url, link) VALUES (?, ?, ?)", (title, image, link)).lastrowid


def list_ads():
    with conn() as c:
        return [dict(r) for r in c.execute("SELECT * FROM ads WHERE active = 1 ORDER BY id")]


def del_ad(i):
    with conn() as c:
        return c.execute("DELETE FROM ads WHERE id = ?", (i,)).rowcount > 0


def click_ad(i):
    with conn() as c:
        r = c.execute("SELECT link FROM ads WHERE id = ? AND active = 1", (i,)).fetchone()
        if not r:
            return None
        c.execute("UPDATE ads SET clicks = clicks + 1 WHERE id = ?", (i,))
        return r["link"]


def touch_device(device, version=None):
    now = int(time.time())
    with conn() as c:
        c.execute("INSERT INTO devices (device, first_seen, last_seen, version) VALUES (?, ?, ?, ?) "
                  "ON CONFLICT(device) DO UPDATE SET last_seen = excluded.last_seen, "
                  "version = COALESCE(excluded.version, devices.version)", (device, now, now, version))


def log_apply(dns_id, device):
    with conn() as c:
        c.execute("INSERT INTO applies (dns_id, device, ts) VALUES (?, ?, ?)", (dns_id, device, int(time.time())))


def stats():
    now = int(time.time())
    with conn() as c:
        one = lambda q, a=(): c.execute(q, a).fetchone()[0]
        return {
            "total": one("SELECT COUNT(*) FROM devices"),
            "d1": one("SELECT COUNT(*) FROM devices WHERE last_seen >= ?", (now - 86400,)),
            "d7": one("SELECT COUNT(*) FROM devices WHERE last_seen >= ?", (now - 7 * 86400,)),
            "versions": [dict(r) for r in c.execute(
                "SELECT COALESCE(version, 'نامشخص') AS v, COUNT(*) AS n FROM devices WHERE last_seen >= ? "
                "GROUP BY v ORDER BY n DESC", (now - 30 * 86400,))],
            "per_dns": [dict(r) for r in c.execute(
                "SELECT d.name, COUNT(DISTINCT a.device) AS devs, COUNT(a.id) AS n FROM dns_servers d "
                "LEFT JOIN applies a ON a.dns_id = d.id AND a.ts >= ? GROUP BY d.id ORDER BY devs DESC",
                (now - 7 * 86400,))],
        }