"""Operational services introduced for Sentinel V6."""

from __future__ import annotations

import csv
import io
import json
import shutil
import sqlite3
import tempfile
import zipfile
from datetime import datetime
from pathlib import Path


class V6Services:
    """Provide diagnostics, calibration, backup and report exports."""

    def __init__(self, project_root: Path, data_dir: Path) -> None:
        self.project_root = Path(project_root)
        self.data_dir = Path(data_dir)
        self.maintenance_db = self.data_dir / "sentinel_maintenance.db"
        self._initialize()

    def _initialize(self) -> None:
        self.data_dir.mkdir(parents=True, exist_ok=True)
        with sqlite3.connect(self.maintenance_db) as connection:
            connection.execute(
                """
                CREATE TABLE IF NOT EXISTS calibrations (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    equipment_id TEXT NOT NULL,
                    sensor TEXT NOT NULL,
                    sensor_id TEXT,
                    sentinel_value REAL NOT NULL,
                    reference_value REAL NOT NULL,
                    offset REAL NOT NULL,
                    reference_instrument TEXT,
                    technician TEXT NOT NULL,
                    comment TEXT,
                    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
                )
                """
            )
            columns = {row[1] for row in connection.execute("PRAGMA table_info(calibrations)").fetchall()}
            if "sensor_id" not in columns:
                connection.execute("ALTER TABLE calibrations ADD COLUMN sensor_id TEXT")
            if "reference_instrument" not in columns:
                connection.execute("ALTER TABLE calibrations ADD COLUMN reference_instrument TEXT")
            connection.execute(
                """
                CREATE TABLE IF NOT EXISTS sensors (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    sensor_id TEXT NOT NULL UNIQUE,
                    equipment_id TEXT NOT NULL,
                    device_id TEXT NOT NULL,
                    role TEXT NOT NULL,
                    metric TEXT NOT NULL,
                    sensor_type TEXT NOT NULL,
                    channel TEXT,
                    serial_number TEXT,
                    calibratable INTEGER NOT NULL DEFAULT 1,
                    simulated INTEGER NOT NULL DEFAULT 0,
                    enabled INTEGER NOT NULL DEFAULT 1,
                    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
                )
                """
            )
            connection.execute(
                "CREATE INDEX IF NOT EXISTS idx_calibration_equipment ON calibrations(equipment_id, created_at)"
            )
            connection.execute(
                "CREATE INDEX IF NOT EXISTS idx_sensors_equipment ON sensors(equipment_id, enabled)"
            )
            connection.commit()

    def register_sensor(
        self, *, sensor_id: str, equipment_id: str, device_id: str, role: str,
        metric: str, sensor_type: str, channel: str | None = None,
        serial_number: str | None = None, calibratable: bool = True,
        simulated: bool = False,
    ) -> None:
        """Register or update one physical/logical sensor assignment."""
        with sqlite3.connect(self.maintenance_db) as connection:
            connection.execute(
                """
                INSERT INTO sensors(sensor_id,equipment_id,device_id,role,metric,sensor_type,channel,serial_number,calibratable,simulated)
                VALUES(?,?,?,?,?,?,?,?,?,?)
                ON CONFLICT(sensor_id) DO UPDATE SET
                    equipment_id=excluded.equipment_id, device_id=excluded.device_id, role=excluded.role,
                    metric=excluded.metric, sensor_type=excluded.sensor_type, channel=excluded.channel,
                    serial_number=excluded.serial_number, calibratable=excluded.calibratable,
                    simulated=excluded.simulated, enabled=1, updated_at=CURRENT_TIMESTAMP
                """,
                (sensor_id,equipment_id,device_id,role,metric,sensor_type,channel,serial_number,1 if calibratable else 0,1 if simulated else 0),
            )
            connection.commit()

    def ensure_default_sensors(self, equipment_id: str, device_id: str, simulated: bool = False) -> None:
        """Create deterministic sensor identities for an equipment when missing."""
        defaults = (
            ("INT-PT100", "Internal temperature", "internal_temperature_c", "PT100", "CH1", True),
            ("COMP-PT100", "Compressor temperature", "compressor_temperature_c", "PT100", "CH2", True),
            ("AMB-DS18B20", "Ambient temperature", "ambient_temperature_c", "DS18B20", "1-Wire", False),
        )
        for suffix, role, metric, sensor_type, channel, calibratable in defaults:
            self.register_sensor(
                sensor_id=f"{equipment_id}:{suffix}", equipment_id=equipment_id, device_id=device_id,
                role=role, metric=metric, sensor_type=sensor_type, channel=channel,
                calibratable=calibratable, simulated=simulated,
            )

    def sensors(self, equipment_id: str | None = None, calibratable_only: bool = False) -> list[dict[str, object]]:
        """Return registered sensors, optionally filtered by equipment/calibratability."""
        sql="SELECT * FROM sensors WHERE enabled=1"
        args=[]
        if equipment_id is not None:
            sql += " AND equipment_id=?"; args.append(equipment_id)
        if calibratable_only:
            sql += " AND calibratable=1"
        sql += " ORDER BY equipment_id, role, sensor_id"
        with sqlite3.connect(self.maintenance_db) as connection:
            connection.row_factory=sqlite3.Row
            rows=connection.execute(sql,args).fetchall()
        return [dict(r) for r in rows]

    def sensor(self, sensor_id: str) -> dict[str, object] | None:
        with sqlite3.connect(self.maintenance_db) as connection:
            connection.row_factory=sqlite3.Row
            row=connection.execute("SELECT * FROM sensors WHERE sensor_id=? AND enabled=1",(sensor_id,)).fetchone()
        return dict(row) if row else None

    def record_calibration(
        self, equipment_id: str, sensor_id: str, sentinel_value: float,
        reference_value: float, technician: str, reference_instrument: str | None = None,
        comment: str | None = None,
    ) -> int:
        """Store a reference comparison against a concrete registered sensor."""
        sensor=self.sensor(sensor_id)
        if not sensor or str(sensor["equipment_id"]) != equipment_id:
            raise ValueError("Selected sensor is not assigned to this equipment.")
        if not int(sensor["calibratable"]):
            raise ValueError("Selected sensor is not configured as calibratable.")
        offset = float(reference_value) - float(sentinel_value)
        with sqlite3.connect(self.maintenance_db) as connection:
            cursor = connection.execute(
                """
                INSERT INTO calibrations (
                    equipment_id, sensor, sensor_id, sentinel_value, reference_value,
                    offset, reference_instrument, technician, comment
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
                """,
                (equipment_id, str(sensor["role"]), sensor_id, sentinel_value, reference_value, offset,
                 (reference_instrument or "").strip() or None, technician.strip(), (comment or "").strip() or None),
            )
            connection.commit()
            return int(cursor.lastrowid)

    def calibrations(self, equipment_id: str, limit: int = 50) -> list[dict[str, object]]:
        with sqlite3.connect(self.maintenance_db) as connection:
            connection.row_factory = sqlite3.Row
            rows = connection.execute(
                """
                SELECT * FROM calibrations
                WHERE equipment_id = ?
                ORDER BY id DESC LIMIT ?
                """,
                (equipment_id, max(1, min(limit, 500))),
            ).fetchall()
        return [dict(row) for row in rows]

    def diagnostics(self, database_path: Path, monitoring_path: Path) -> dict[str, object]:
        checks: list[dict[str, object]] = []
        for name, path in (
            ("measurement_database", Path(database_path)),
            ("monitoring_database", Path(monitoring_path)),
            ("maintenance_database", self.maintenance_db),
        ):
            ok = path.exists()
            detail = "missing"
            if ok:
                try:
                    with sqlite3.connect(path) as connection:
                        result = connection.execute("PRAGMA quick_check").fetchone()
                    ok = bool(result and result[0] == "ok")
                    detail = str(result[0]) if result else "no result"
                except sqlite3.Error as error:
                    ok = False
                    detail = str(error)
            checks.append({"name": name, "ok": ok, "detail": detail})

        disk = shutil.disk_usage(self.project_root)
        free_gb = disk.free / (1024 ** 3)
        checks.append({
            "name": "disk_space",
            "ok": free_gb >= 0.25,
            "detail": f"{free_gb:.2f} GB free",
        })
        return {
            "status": "OK" if all(bool(item["ok"]) for item in checks) else "ATTENTION",
            "generated_at": datetime.now().isoformat(timespec="seconds"),
            "checks": checks,
        }

    def build_backup(self) -> Path:
        timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
        target = Path(tempfile.gettempdir()) / f"sentinel_v6_backup_{timestamp}.zip"
        include_roots = ["config", "data", "docs", "sentinel", "static", "templates", "tools", "tests"]
        include_files = [
            "main.py", "pyproject.toml", "requirements.txt", "requirements-dev.txt",
            "requirements-hardware.txt", "README.md", "CHANGELOG.md", "RELEASE_NOTES.md",
        ]
        with zipfile.ZipFile(target, "w", compression=zipfile.ZIP_DEFLATED) as archive:
            for root_name in include_roots:
                root = self.project_root / root_name
                if not root.exists():
                    continue
                for path in root.rglob("*"):
                    if path.is_file() and "__pycache__" not in path.parts:
                        archive.write(path, path.relative_to(self.project_root))
            for name in include_files:
                path = self.project_root / name
                if path.exists():
                    archive.write(path, path.relative_to(self.project_root))
        return target

    @staticmethod
    def csv_bytes(rows: list[dict[str, object]]) -> bytes:
        if not rows:
            return b"No data\n"
        output = io.StringIO()
        writer = csv.DictWriter(output, fieldnames=list(rows[0].keys()), extrasaction="ignore", delimiter=";")
        writer.writeheader()
        writer.writerows(rows)
        return output.getvalue().encode("utf-8-sig")

    @staticmethod
    def json_bytes(payload: object) -> bytes:
        return json.dumps(payload, indent=2, ensure_ascii=False, default=str).encode("utf-8")
