"""SQLite storage for the hive (swarm address book).

Single-writer, single-process, tiny — SQLite is deliberately enough here
(the hive is a notebook, not a Navi database). ``online`` is derived at read
time: a machine is considered present if it announced within the TTL window.
"""

from __future__ import annotations

import json
import sqlite3
import time
from pathlib import Path

_SCHEMA = """
CREATE TABLE IF NOT EXISTS machines (
    instance_id TEXT PRIMARY KEY,
    name        TEXT NOT NULL,
    host        TEXT,
    port        INTEGER,
    version     TEXT,
    meta        TEXT,
    first_seen  REAL NOT NULL,
    last_seen   REAL NOT NULL
);
"""


class HiveDB:
    def __init__(self, path: str | Path = "hive.db") -> None:
        self._path = str(path)
        self._conn = sqlite3.connect(self._path, check_same_thread=False)
        self._conn.row_factory = sqlite3.Row
        self._conn.execute("PRAGMA journal_mode=WAL")
        self._conn.executescript(_SCHEMA)
        self._conn.commit()

    def close(self) -> None:
        self._conn.close()

    def upsert(
        self,
        *,
        instance_id: str,
        name: str,
        host: str | None,
        port: int,
        version: str,
        meta: dict,
    ) -> None:
        now = time.time()
        self._conn.execute(
            """
            INSERT INTO machines
                (instance_id, name, host, port, version, meta, first_seen, last_seen)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?)
            ON CONFLICT(instance_id) DO UPDATE SET
                name = excluded.name,
                host = COALESCE(excluded.host, machines.host),
                port = excluded.port,
                version = excluded.version,
                meta = excluded.meta,
                last_seen = excluded.last_seen
            """,
            (
                instance_id,
                name,
                host,
                port,
                version,
                json.dumps(meta),
                now,
                now,
            ),
        )
        self._conn.commit()

    def list_peers(self, ttl_sec: int) -> list[dict]:
        now = time.time()
        rows = self._conn.execute(
            "SELECT * FROM machines ORDER BY name COLLATE NOCASE"
        ).fetchall()
        peers = []
        for row in rows:
            try:
                meta = json.loads(row["meta"] or "{}")
            except json.JSONDecodeError:
                meta = {}
            peers.append(
                {
                    "name": row["name"],
                    "instance_id": row["instance_id"],
                    "host": row["host"],
                    "port": row["port"],
                    "address": f"{row['host']}:{row['port']}" if row["host"] else None,
                    "version": row["version"],
                    "online": (now - row["last_seen"]) <= ttl_sec,
                    "first_seen": row["first_seen"],
                    "last_seen": row["last_seen"],
                    "meta": meta,
                }
            )
        return peers