"""
SQLite database management for Sentinel.
"""

import sqlite3
from pathlib import Path


class DatabaseManager:
    """
    Manage the local Sentinel SQLite database.

    This class is responsible for creating the database schema,
    registering monitored devices, and storing measurements.
    """

    def __init__(self, database_path: Path) -> None:
        """
        Initialize the database manager.

        Args:
            database_path: Path to the SQLite database file.
        """
        self._database_path = database_path

    def initialize(self) -> None:
        """
        Create the database and its tables when they do not exist.
        """
        with self._connect() as connection:
            connection.execute("PRAGMA foreign_keys = ON;")

            connection.execute(
                """
                CREATE TABLE IF NOT EXISTS devices (
                    device_id TEXT PRIMARY KEY,
                    equipment_id TEXT NOT NULL,
                    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    last_seen_at TEXT
                );
                """
            )

            connection.execute(
                """
                CREATE TABLE IF NOT EXISTS measurements (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    recorded_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    device_id TEXT NOT NULL,
                    equipment_id TEXT NOT NULL,
                    ambient_temperature_c REAL,
                    internal_temperature_c REAL,
                    evaporator_temperature_c REAL,
                    compressor_temperature_c REAL,
                    compressor_current_a REAL,
                    health_score REAL,
                    FOREIGN KEY (device_id)
                        REFERENCES devices(device_id)
                );
                """
            )

            connection.execute(
                """
                CREATE INDEX IF NOT EXISTS
                idx_measurements_device_recorded_at
                ON measurements(device_id, recorded_at);
                """
            )

            connection.commit()

    def register_device(
        self,
        device_id: str,
        equipment_id: str,
    ) -> None:
        """
        Register or update a Sentinel device.

        Args:
            device_id: Unique identifier of the Raspberry Pi.
            equipment_id: Identifier of the monitored equipment.
        """
        with self._connect() as connection:
            connection.execute(
                """
                INSERT INTO devices (
                    device_id,
                    equipment_id,
                    last_seen_at
                )
                VALUES (?, ?, CURRENT_TIMESTAMP)
                ON CONFLICT(device_id) DO UPDATE SET
                    equipment_id = excluded.equipment_id,
                    last_seen_at = CURRENT_TIMESTAMP;
                """,
                (device_id, equipment_id),
            )

            connection.commit()

    def insert_measurement(
        self,
        device_id: str,
        equipment_id: str,
        ambient_temperature_c: float,
        internal_temperature_c: float,
        evaporator_temperature_c: float,
        compressor_temperature_c: float,
        compressor_current_a: float,
        health_score: float,
    ) -> None:
        """
        Store one equipment measurement in the local database.

        Args:
            device_id: Unique identifier of the Raspberry Pi.
            equipment_id: Identifier of the monitored equipment.
            ambient_temperature_c: Ambient temperature in degrees Celsius.
            internal_temperature_c: Internal equipment temperature in degrees Celsius.
            evaporator_temperature_c: Evaporator temperature in degrees Celsius.
            compressor_temperature_c: Compressor surface temperature in degrees Celsius.
            compressor_current_a: Compressor current in amperes.
            health_score: Equipment health score from 0 to 100.
        """
        with self._connect() as connection:
            connection.execute(
                """
                INSERT INTO measurements (
                    device_id,
                    equipment_id,
                    ambient_temperature_c,
                    internal_temperature_c,
                    evaporator_temperature_c,
                    compressor_temperature_c,
                    compressor_current_a,
                    health_score
                )
                VALUES (?, ?, ?, ?, ?, ?, ?, ?);
                """,
                (
                    device_id,
                    equipment_id,
                    ambient_temperature_c,
                    internal_temperature_c,
                    evaporator_temperature_c,
                    compressor_temperature_c,
                    compressor_current_a,
                    health_score,
                ),
            )

            connection.commit()

    def insert_measurement_at(
        self,
        *,
        recorded_at: str,
        device_id: str,
        equipment_id: str,
        ambient_temperature_c: float | None = None,
        internal_temperature_c: float | None = None,
        evaporator_temperature_c: float | None = None,
        compressor_temperature_c: float | None = None,
        compressor_current_a: float | None = None,
        health_score: float | None = None,
    ) -> None:
        """Insert a measurement preserving its edge timestamp (V7 cloud sync)."""
        self.register_device(device_id, equipment_id)
        with self._connect() as connection:
            connection.execute(
                """
                INSERT INTO measurements(
                    recorded_at,device_id,equipment_id,ambient_temperature_c,
                    internal_temperature_c,evaporator_temperature_c,
                    compressor_temperature_c,compressor_current_a,health_score
                ) VALUES(?,?,?,?,?,?,?,?,?)
                """,
                (
                    recorded_at, device_id, equipment_id, ambient_temperature_c,
                    internal_temperature_c, evaporator_temperature_c,
                    compressor_temperature_c, compressor_current_a, health_score,
                ),
            )
            connection.commit()

    def get_latest_measurement(self) -> dict[str, object] | None:
        """
        Return the most recent measurement stored in the database.

        Returns:
            Latest measurement as a dictionary, or None when the
            database contains no measurements.
        """
        with self._connect() as connection:
            connection.row_factory = sqlite3.Row

            row = connection.execute(
                """
                SELECT
                    id,
                    recorded_at,
                    device_id,
                    equipment_id,
                    ambient_temperature_c,
                    internal_temperature_c,
                    evaporator_temperature_c,
                    compressor_temperature_c,
                    compressor_current_a,
                    health_score
                FROM measurements
                ORDER BY id DESC
                LIMIT 1;
                """
            ).fetchone()

        if row is None:
            return None

        return dict(row)

    def get_recent_measurements(
        self,
        limit: int = 200,
    ) -> list[dict[str, object]]:
        """
        Return the most recent measurements in chronological order.

        Args:
            limit: Maximum number of measurements to return.

        Returns:
            Recent measurements as dictionaries.
        """
        safe_limit = max(1, min(limit, 5000))

        with self._connect() as connection:
            connection.row_factory = sqlite3.Row

            rows = connection.execute(
                """
                SELECT
                    id,
                    recorded_at,
                    device_id,
                    equipment_id,
                    ambient_temperature_c,
                    internal_temperature_c,
                    evaporator_temperature_c,
                    compressor_temperature_c,
                    compressor_current_a,
                    health_score
                FROM measurements
                ORDER BY id DESC
                LIMIT ?;
                """,
                (safe_limit,),
            ).fetchall()

        return [
            dict(row)
            for row in reversed(rows)
        ]

    def get_equipment_fleet(self) -> list[dict[str, object]]:
        """
        Return every registered equipment with its latest measurement.
 
        Returns:
            Fleet equipment ordered by equipment identifier.
        """
        with self._connect() as connection:
            connection.row_factory = sqlite3.Row
 
            rows = connection.execute(
                """
                SELECT
                    d.equipment_id,
                    d.device_id,
                    m.id AS measurement_id,
                    m.recorded_at,
                    m.internal_temperature_c,
                    m.compressor_temperature_c,
                    m.compressor_current_a,
                    m.health_score
                FROM devices AS d
                LEFT JOIN measurements AS m
                    ON m.id = (
                        SELECT latest.id
                        FROM measurements AS latest
                        WHERE latest.equipment_id = d.equipment_id
                        ORDER BY latest.id DESC
                        LIMIT 1
                    )
                ORDER BY d.equipment_id;
                """
            ).fetchall()
 
        return [dict(row) for row in rows]
 
    def get_latest_equipment_measurement(
        self,
        equipment_id: str,
    ) -> dict[str, object] | None:
        """
        Return the latest measurement for one equipment.
 
        Args:
            equipment_id: Equipment identifier.
 
        Returns:
            Latest equipment measurement or None.
        """
        with self._connect() as connection:
            connection.row_factory = sqlite3.Row
 
            row = connection.execute(
                """
                SELECT
                    id,
                    recorded_at,
                    device_id,
                    equipment_id,
                    ambient_temperature_c,
                    internal_temperature_c,
                    evaporator_temperature_c,
                    compressor_temperature_c,
                    compressor_current_a,
                    health_score
                FROM measurements
                WHERE equipment_id = ?
                ORDER BY id DESC
                LIMIT 1;
                """,
                (equipment_id,),
            ).fetchone()
 
        if row is None:
            return None
 
        return dict(row)

    def get_equipment_measurement_history(
        self,
        equipment_id: str,
        limit: int = 720,
    ) -> list[dict[str, object]]:
        """
        Return recent measurements for one equipment.

        Args:
            equipment_id: Equipment identifier.
            limit: Maximum number of measurements to return.

        Returns:
            Measurements ordered from oldest to newest.
        """
        safe_limit = max(1, min(limit, 5000))

        with self._connect() as connection:
            connection.row_factory = sqlite3.Row

            rows = connection.execute(
                """
                SELECT
                    id,
                    recorded_at,
                    device_id,
                    equipment_id,
                    ambient_temperature_c,
                    internal_temperature_c,
                    evaporator_temperature_c,
                    compressor_temperature_c,
                    compressor_current_a,
                    health_score
                FROM measurements
                WHERE equipment_id = ?
                ORDER BY id DESC
                LIMIT ?;
                """,
                (
                    equipment_id,
                    safe_limit,
                ),
            ).fetchall()

        return [
            dict(row)
            for row in reversed(rows)
        ] 

    def _connect(self) -> sqlite3.Connection:
        """
        Open a connection to the Sentinel database.

        Returns:
            Active SQLite connection.
        """
        return sqlite3.connect(self._database_path)