"""Speed-sample storage for qBittorrent widgets.

This module defines the storage concern for qBittorrent speed data and a
bespoke store with ``append``/``window`` operations. It is registered with the
:class:`~media_library_viewer_api.services.service_data.ServiceDataHarness` as
the first real consumer of the harness lifecycle layer.
"""

from __future__ import annotations

import logging
from typing import TYPE_CHECKING, Any

from media_library_viewer_api.services.service_data import StorageConcern

if TYPE_CHECKING:
    from media_library_viewer_api.services.service_data import ServiceDataHarness

logger = logging.getLogger(__name__)


QBITTORRENT_CONCERN = StorageConcern(
    concern_key="qbittorrent",
    db_filename="qbittorrent.db",
    migrations=[
        """
        CREATE TABLE IF NOT EXISTS qbittorrent_speed_samples (
            service_id TEXT NOT NULL,
            ts INTEGER NOT NULL,
            dl_speed INTEGER NOT NULL DEFAULT 0,
            up_speed INTEGER NOT NULL DEFAULT 0
        );
        CREATE INDEX IF NOT EXISTS idx_qbit_samples_service_ts
            ON qbittorrent_speed_samples(service_id, ts);
        """
    ],
    tables=["qbittorrent_speed_samples"],
)

#: Maximum samples kept per service (~2 min at 1 s poll, ~4 min at 2 s poll).
MAX_SAMPLES = 120


class QbittorrentSampleStore:
    """Bespoke speed-sample store for qBittorrent widgets.

    Each ``append`` inserts a new sample and prunes entries beyond
    :data:`MAX_SAMPLES`, keeping only the most recent rows for the given
    ``service_id``.
    """

    def __init__(self, harness: ServiceDataHarness | None = None) -> None:
        if harness is None:
            from media_library_viewer_api.services.service_data import get_service_data_harness

            harness = get_service_data_harness()
        self._harness = harness

    def append(self, service_id: str, ts: int, dl_speed: int, up_speed: int) -> None:
        """Append a sample and prune old entries beyond ``MAX_SAMPLES``."""
        with self._harness.connect("qbittorrent") as conn:
            conn.execute(
                "INSERT INTO qbittorrent_speed_samples (service_id, ts, dl_speed, up_speed) VALUES (?, ?, ?, ?)",
                (service_id, ts, dl_speed, up_speed),
            )
            conn.execute(
                "DELETE FROM qbittorrent_speed_samples "
                "WHERE service_id = ? AND ts NOT IN ("
                "    SELECT ts FROM qbittorrent_speed_samples"
                "    WHERE service_id = ?"
                "    ORDER BY ts DESC LIMIT ?"
                ")",
                (service_id, service_id, MAX_SAMPLES),
            )
            conn.commit()

    def window(self, service_id: str, since_ts: int | None = None) -> list[dict[str, Any]]:
        """Return all samples for a service since a timestamp (or all if ``None``)."""
        with self._harness.connect("qbittorrent") as conn:
            if since_ts is not None:
                rows = conn.execute(
                    "SELECT ts, dl_speed, up_speed FROM qbittorrent_speed_samples "
                    "WHERE service_id = ? AND ts >= ? ORDER BY ts ASC",
                    (service_id, since_ts),
                ).fetchall()
            else:
                rows = conn.execute(
                    "SELECT ts, dl_speed, up_speed FROM qbittorrent_speed_samples WHERE service_id = ? ORDER BY ts ASC",
                    (service_id,),
                ).fetchall()
        return [{"ts": r[0], "dl_speed": r[1], "up_speed": r[2]} for r in rows]
