"""
Persistent Brain diagnostic history for Sentinel.

This module uses a separate SQLite database from raw measurements.
"""

from __future__ import annotations

from dataclasses import asdict
from pathlib import Path
import sqlite3
from typing import Any


class BrainHistoryService:
    """
    Store and query Sentinel Brain diagnostics.

    Raw measurements remain in the acquisition database.
    Brain conclusions are stored in a separate database.
    """

    def __init__(self, database_path: str | Path) -> None:
        """
        Initialize the Brain History database.
        """
        self.database_path = Path(database_path)
        self.database_path.parent.mkdir(
            parents=True,
            exist_ok=True,
        )
        self._initialize_database()

    def _connect(self) -> sqlite3.Connection:
        """
        Open one short-lived SQLite connection.
        """
        connection = sqlite3.connect(
            self.database_path,
            timeout=10.0,
        )
        connection.row_factory = sqlite3.Row
        return connection

    def _initialize_database(self) -> None:
        """
        Create the Brain History schema when needed.
        """
        with self._connect() as connection:
            connection.execute(
                """
                CREATE TABLE IF NOT EXISTS brain_history (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    equipment_id TEXT NOT NULL,
                    measurement_id INTEGER NOT NULL,
                    measurement_recorded_at TEXT NOT NULL,
                    analysis_recorded_at TEXT NOT NULL
                        DEFAULT CURRENT_TIMESTAMP,

                    health_score REAL NOT NULL,
                    status TEXT NOT NULL,

                    thermal_score REAL,
                    electrical_score REAL,
                    cooling_score REAL,
                    sensor_score REAL,

                    risk_index REAL,
                    failure_probability_30d REAL,
                    prediction_horizon_days INTEGER,
                    prediction_confidence REAL,

                    root_cause_code TEXT,
                    root_cause TEXT,
                    root_cause_confidence REAL,

                    analysis TEXT NOT NULL,
                    recommendation TEXT NOT NULL,
                    confidence REAL NOT NULL,

                    UNIQUE (
                        equipment_id,
                        measurement_id
                    )
                );
                """
            )

            connection.execute(
                """
                CREATE INDEX IF NOT EXISTS
                    idx_brain_history_equipment_time
                ON brain_history (
                    equipment_id,
                    measurement_recorded_at
                );
                """
            )

    def save_analysis(
        self,
        equipment_id: str,
        measurement_id: int,
        measurement_recorded_at: str,
        analysis_result: Any,
    ) -> bool:
        """
        Save one Brain analysis.

        Returns:
            True when a new row was inserted.
            False when this measurement was already analyzed.
        """
        data = asdict(analysis_result)

        with self._connect() as connection:
            cursor = connection.execute(
                """
                INSERT OR IGNORE INTO brain_history (
                    equipment_id,
                    measurement_id,
                    measurement_recorded_at,

                    health_score,
                    status,

                    thermal_score,
                    electrical_score,
                    cooling_score,
                    sensor_score,

                    risk_index,
                    failure_probability_30d,
                    prediction_horizon_days,
                    prediction_confidence,

                    root_cause_code,
                    root_cause,
                    root_cause_confidence,

                    analysis,
                    recommendation,
                    confidence
                )
                VALUES (
                    ?, ?, ?,
                    ?, ?,
                    ?, ?, ?, ?,
                    ?, ?, ?, ?,
                    ?, ?, ?,
                    ?, ?, ?
                );
                """,
                (
                    equipment_id,
                    measurement_id,
                    measurement_recorded_at,

                    data["health_score"],
                    data["status"],

                    data.get("thermal_score"),
                    data.get("electrical_score"),
                    data.get("cooling_score"),
                    data.get("sensor_score"),

                    data.get("risk_index"),
                    data.get("failure_probability_30d"),
                    data.get("prediction_horizon_days"),
                    data.get("prediction_confidence"),

                    data.get("root_cause_code"),
                    data.get("root_cause"),
                    data.get("root_cause_confidence"),

                    data["analysis"],
                    data["recommendation"],
                    data["confidence"],
                ),
            )

        return cursor.rowcount == 1

    def get_history(
        self,
        equipment_id: str,
        limit: int = 500,
    ) -> list[dict[str, object]]:
        """
        Return Brain diagnostics from oldest to newest.
        """
        safe_limit = max(
            1,
            min(limit, 5000),
        )

        with self._connect() as connection:
            rows = connection.execute(
                """
                SELECT
                    id,
                    equipment_id,
                    measurement_id,
                    measurement_recorded_at,
                    analysis_recorded_at,

                    health_score,
                    status,

                    thermal_score,
                    electrical_score,
                    cooling_score,
                    sensor_score,

                    risk_index,
                    failure_probability_30d,
                    prediction_horizon_days,
                    prediction_confidence,

                    root_cause_code,
                    root_cause,
                    root_cause_confidence,

                    analysis,
                    recommendation,
                    confidence
                FROM brain_history
                WHERE equipment_id = ?
                ORDER BY id DESC
                LIMIT ?;
                """,
                (
                    equipment_id,
                    safe_limit,
                ),
            ).fetchall()

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

    def get_latest(
        self,
        equipment_id: str,
    ) -> dict[str, object] | None:
        """
        Return the latest stored Brain diagnostic.
        """
        history = self.get_history(
            equipment_id=equipment_id,
            limit=1,
        )

        if not history:
            return None

        return history[0]

    def get_health_trend(
        self,
        equipment_id: str,
        limit: int = 500,
    ) -> dict[str, object]:
        """
        Return a simple explainable Health Score trend.
        """
        history = self.get_history(
            equipment_id=equipment_id,
            limit=limit,
        )

        if len(history) < 2:
            return {
                "equipment_id": equipment_id,
                "sample_count": len(history),
                "health_change": 0.0,
                "trend": "INSUFFICIENT_DATA",
            }

        first_score = float(
            history[0]["health_score"]
        )
        latest_score = float(
            history[-1]["health_score"]
        )

        change = round(
            latest_score - first_score,
            2,
        )

        if change <= -10.0:
            trend = "DEGRADING_FAST"
        elif change <= -3.0:
            trend = "DEGRADING"
        elif change >= 3.0:
            trend = "IMPROVING"
        else:
            trend = "STABLE"

        return {
            "equipment_id": equipment_id,
            "sample_count": len(history),
            "first_health_score": first_score,
            "latest_health_score": latest_score,
            "health_change": change,
            "trend": trend,
        }
