"""Final DB state snapshot — full A→B→C chain evidence."""
import sys
from pathlib import Path

_HERE = Path(__file__).resolve().parent
sys.path.insert(0, str(_HERE.parent / "src"))

from cq2_sdk.env_loader import load_env
load_env(_HERE.parent / ".env")

from cq2_sdk.store import connection

with connection() as conn:
    with conn.cursor() as cur:
        cur.execute(
            "SELECT id, username, phone, LEFT(token, 24) AS tok, "
            "password_hash IS NOT NULL AS has_pwd FROM users "
            "WHERE username = 'longwu988'"
        )
        u = cur.fetchone()
        print("[users]")
        print(f"  id={u['id']}  username={u['username']!r}  phone={u['phone']!r}")
        print(f"  token={u['tok']}...  password_preserved={bool(u['has_pwd'])}")

        cur.execute("SELECT COUNT(*) AS n FROM zones")
        zc = cur.fetchone()["n"]
        cur.execute(
            "SELECT zone_id, zone_name, host, port, status, open_time "
            "FROM zones WHERE host IS NOT NULL"
        )
        print(f"\n[zones] total={zc}, with gateway:")
        for z in cur.fetchall():
            print(f"  zone_id={z['zone_id']}  {z['zone_name']!r}  "
                  f"{z['host']}:{z['port']}  status={z['status']}  "
                  f"open={str(z['open_time'])[:16]}")

        cur.execute(
            "SELECT id, user_id, server_id, zone_id, status, snapshot_path "
            "FROM runs ORDER BY id DESC LIMIT 2"
        )
        print(f"\n[runs] latest:")
        for r in cur.fetchall():
            env = str(r["snapshot_path"] or "").split("\\")[-1]
            print(f"  run id={r['id']}  user={r['user_id']}  zone={r['zone_id']}  "
                  f"status={r['status']!r}  env_file={env}")