# -*- coding: utf-8 -*-
"""SQLite store for captured Sota VPN nodes."""
from __future__ import annotations

import json
import os
import sqlite3
import subprocess
from datetime import datetime
from pathlib import Path
from typing import Any


def proxy_home() -> Path:
    return Path(os.environ["USERPROFILE"]) / ".claude-proxy-control"


def db_path() -> Path:
    p = proxy_home() / "merged" / "sota-captured" / "nodes.db"
    p.parent.mkdir(parents=True, exist_ok=True)
    return p


def connect() -> sqlite3.Connection:
    con = sqlite3.connect(str(db_path()))
    con.row_factory = sqlite3.Row
    return con


def _ensure_column(con: sqlite3.Connection, name: str, decl: str) -> None:
    cols = {str(r[1]) for r in con.execute("PRAGMA table_info(nodes)").fetchall()}
    if name not in cols:
        con.execute(f"ALTER TABLE nodes ADD COLUMN {name} {decl}")


def init_db() -> None:
    with connect() as con:
        con.execute(
            """
            CREATE TABLE IF NOT EXISTS nodes (
              id INTEGER PRIMARY KEY AUTOINCREMENT,
              captured_at TEXT NOT NULL,
              server TEXT NOT NULL,
              port INTEGER NOT NULL,
              file_path TEXT NOT NULL,
              sha256 TEXT,
              vless_count INTEGER DEFAULT 1,
              is_active INTEGER DEFAULT 0,
              last_probe_ok INTEGER,
              last_probe_at TEXT,
              last_egress TEXT,
              note TEXT,
              provider TEXT DEFAULT 'Sota',
              country TEXT,
              country_code TEXT
            )
            """
        )
        _ensure_column(con, "provider", "TEXT DEFAULT 'Sota'")
        _ensure_column(con, "country", "TEXT")
        _ensure_column(con, "country_code", "TEXT")
        con.execute(
            "UPDATE nodes SET provider = 'Sota' WHERE provider IS NULL OR TRIM(provider) = ''"
        )
        con.execute(
            "CREATE UNIQUE INDEX IF NOT EXISTS idx_nodes_sha ON nodes(sha256) WHERE sha256 IS NOT NULL"
        )


def flag_emoji(country_code: str | None) -> str:
    """ISO 3166-1 alpha-2 → regional-indicator flag, or empty."""
    cc = (country_code or "").strip().upper()
    if len(cc) != 2 or not cc.isalpha():
        return ""
    return chr(0x1F1E6 + ord(cc[0]) - 65) + chr(0x1F1E6 + ord(cc[1]) - 65)


def country_label(row: dict[str, Any] | None) -> str:
    if not row:
        return "—"
    code = str(row.get("country_code") or "").strip()
    name = str(row.get("country") or "").strip()
    if code == "?":
        return "—"
    flag = flag_emoji(code)
    if flag and name:
        return f"{flag} {name}"
    if flag and code:
        return f"{flag} {code}"
    if name:
        return name
    if code:
        return code
    return "—"


def lookup_ip_geo(ip: str, timeout: float = 6.0) -> tuple[str, str]:
    """Return (country, country_code) via ip-api.com. Empty on failure.

    Prefer curl.exe — plain urllib is often reset on Windows here.
    """
    ip = (ip or "").strip()
    if not ip or ip in {"?", "0.0.0.0"}:
        return "", ""
    url = f"http://ip-api.com/json/{ip}?fields=status,country,countryCode"
    try:
        flags = getattr(subprocess, "CREATE_NO_WINDOW", 0x08000000)
        p = subprocess.run(
            ["curl.exe", "-s", "-m", str(int(timeout)), url],
            capture_output=True,
            text=True,
            timeout=timeout + 3,
            creationflags=flags,
        )
        raw = (p.stdout or "").strip()
        if not raw:
            return "", ""
        data = json.loads(raw)
        if str(data.get("status") or "") != "success":
            return "", ""
        return str(data.get("country") or "").strip(), str(data.get("countryCode") or "").strip()
    except Exception:
        return "", ""


def set_node_geo(node_id: int, country: str, country_code: str) -> None:
    init_db()
    with connect() as con:
        con.execute(
            "UPDATE nodes SET country = ?, country_code = ? WHERE id = ?",
            (country or None, country_code or None, int(node_id)),
        )


def enrich_missing_geo(limit: int = 6) -> int:
    """Fill country for nodes that don't have it yet. Returns how many updated."""
    init_db()
    with connect() as con:
        rows = con.execute(
            """
            SELECT id, server FROM nodes
            WHERE (country_code IS NULL OR TRIM(country_code) = '')
              AND server IS NOT NULL AND TRIM(server) != ''
            ORDER BY is_active DESC, id DESC
            LIMIT ?
            """,
            (int(limit),),
        ).fetchall()
    updated = 0
    for row in rows:
        country, code = lookup_ip_geo(str(row["server"]))
        if not country and not code:
            # Mark attempt so we don't hammer ip-api every UI refresh
            set_node_geo(int(row["id"]), "", "?")
            continue
        set_node_geo(int(row["id"]), country, code)
        updated += 1
    return updated


def _now() -> str:
    return datetime.now().isoformat(timespec="seconds")


def register_capture(
    file_path: str,
    server: str,
    port: int,
    sha256: str | None = None,
    vless_count: int = 1,
    make_active: bool = True,
    provider: str = "Sota",
) -> int:
    init_db()
    captured_at = _now()
    provider = (provider or "Sota").strip() or "Sota"
    with connect() as con:
        if sha256:
            row = con.execute("SELECT id FROM nodes WHERE sha256 = ?", (sha256,)).fetchone()
            if row:
                con.execute(
                    """
                    UPDATE nodes
                    SET captured_at = ?, file_path = ?, server = ?, port = ?, vless_count = ?,
                        provider = COALESCE(NULLIF(provider, ''), ?)
                    WHERE id = ?
                    """,
                    (captured_at, file_path, server, port, vless_count, provider, row["id"]),
                )
                node_id = int(row["id"])
            else:
                cur = con.execute(
                    """
                    INSERT INTO nodes (
                      captured_at, server, port, file_path, sha256, vless_count, is_active, provider
                    )
                    VALUES (?, ?, ?, ?, ?, ?, 0, ?)
                    """,
                    (captured_at, server, port, file_path, sha256, vless_count, provider),
                )
                node_id = int(cur.lastrowid)
        else:
            cur = con.execute(
                """
                INSERT INTO nodes (
                  captured_at, server, port, file_path, sha256, vless_count, is_active, provider
                )
                VALUES (?, ?, ?, ?, NULL, ?, 0, ?)
                """,
                (captured_at, server, port, file_path, vless_count, provider),
            )
            node_id = int(cur.lastrowid)

        if make_active:
            con.execute("UPDATE nodes SET is_active = 0")
            con.execute("UPDATE nodes SET is_active = 1 WHERE id = ?", (node_id,))
        return node_id


def list_nodes() -> list[dict[str, Any]]:
    init_db()
    with connect() as con:
        rows = con.execute(
            "SELECT * FROM nodes ORDER BY datetime(captured_at) DESC, id DESC"
        ).fetchall()
    return [dict(r) for r in rows]


def count_nodes() -> int:
    init_db()
    with connect() as con:
        return int(con.execute("SELECT COUNT(*) AS c FROM nodes").fetchone()["c"])


def get_active() -> dict[str, Any] | None:
    init_db()
    with connect() as con:
        row = con.execute("SELECT * FROM nodes WHERE is_active = 1 ORDER BY id DESC LIMIT 1").fetchone()
    return dict(row) if row else None


def set_active(node_id: int) -> dict[str, Any] | None:
    init_db()
    home = proxy_home()
    latest = home / "merged" / "sota-captured" / "latest.json"
    with connect() as con:
        row = con.execute("SELECT * FROM nodes WHERE id = ?", (node_id,)).fetchone()
        if not row:
            return None
        src = Path(row["file_path"])
        if not src.is_file():
            raise FileNotFoundError(f"node file missing: {src}")
        latest.write_text(src.read_text(encoding="utf-8"), encoding="utf-8")
        con.execute("UPDATE nodes SET is_active = 0")
        con.execute("UPDATE nodes SET is_active = 1 WHERE id = ?", (node_id,))
        row = con.execute("SELECT * FROM nodes WHERE id = ?", (node_id,)).fetchone()
    return dict(row) if row else None


def mark_probe(node_id: int, ok: bool, egress: str | None = None) -> None:
    init_db()
    with connect() as con:
        con.execute(
            """
            UPDATE nodes
            SET last_probe_ok = ?, last_probe_at = ?, last_egress = COALESCE(?, last_egress)
            WHERE id = ?
            """,
            (1 if ok else 0, _now(), egress, node_id),
        )


def import_existing_captures() -> int:
    """Import timestamped capture JSON files into DB if empty/missing."""
    init_db()
    cap_dir = proxy_home() / "merged" / "sota-captured"
    added = 0
    import hashlib

    files = sorted(cap_dir.glob("sota-singbox-*.json"))
    for f in files:
        try:
            raw = f.read_bytes()
            sha = hashlib.sha256(raw).hexdigest()
            data = json.loads(raw.decode("utf-8"))
            vless = [o for o in data.get("outbounds", []) if isinstance(o, dict) and o.get("type") == "vless"]
            if not vless:
                continue
            server = str(vless[0].get("server") or "?")
            port = int(vless[0].get("server_port") or 0)
            with connect() as con:
                exists = con.execute("SELECT id FROM nodes WHERE sha256 = ?", (sha,)).fetchone()
            if exists:
                continue
            register_capture(
                file_path=str(f),
                server=server,
                port=port,
                sha256=sha,
                vless_count=len(vless),
                make_active=False,
            )
            added += 1
        except Exception:
            continue

    # If nothing active but latest.json exists, activate matching/latest
    if get_active() is None and files:
        latest = cap_dir / "latest.json"
        if latest.is_file():
            try:
                import hashlib

                sha = hashlib.sha256(latest.read_bytes()).hexdigest()
                with connect() as con:
                    row = con.execute("SELECT id FROM nodes WHERE sha256 = ?", (sha,)).fetchone()
                if row:
                    set_active(int(row["id"]))
                else:
                    # activate newest
                    nodes = list_nodes()
                    if nodes:
                        set_active(int(nodes[0]["id"]))
            except Exception:
                nodes = list_nodes()
                if nodes:
                    set_active(int(nodes[0]["id"]))
        else:
            nodes = list_nodes()
            if nodes:
                set_active(int(nodes[0]["id"]))
    return added


if __name__ == "__main__":
    init_db()
    n = import_existing_captures()
    print(f"db={db_path()}")
    print(f"imported={n}")
    print(f"count={count_nodes()}")
    act = get_active()
    print(f"active={act}")
