"""Central cloud persistence for Sentinel V7.

The cloud database is deliberately separate from the edge measurement database.
It tracks device connectivity, sync receipts and a de-duplicated copy of data
received from remote Sentinel Devices.
"""
from __future__ import annotations

import json
import sqlite3
from contextlib import contextmanager
from datetime import datetime, timedelta
from pathlib import Path
from typing import Iterable


class CloudStore:
    def __init__(self, database_path: Path) -> None:
        self.database_path = Path(database_path)

    def initialize(self) -> None:
        self.database_path.parent.mkdir(parents=True, exist_ok=True)
        with self._connect() as connection:
            connection.executescript(
                """
                CREATE TABLE IF NOT EXISTS cloud_devices (
                    device_id TEXT PRIMARY KEY,
                    equipment_id TEXT NOT NULL,
                    site_code TEXT,
                    display_name TEXT,
                    edge_version TEXT,
                    registered_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    last_heartbeat_at TEXT,
                    heartbeat_interval_seconds INTEGER NOT NULL DEFAULT 15,
                    online INTEGER NOT NULL DEFAULT 1,
                    last_ip TEXT,
                    metadata_json TEXT NOT NULL DEFAULT '{}'
                );
                CREATE INDEX IF NOT EXISTS idx_cloud_devices_heartbeat
                    ON cloud_devices(last_heartbeat_at);

                CREATE TABLE IF NOT EXISTS cloud_device_events (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    device_id TEXT NOT NULL,
                    equipment_id TEXT,
                    event_type TEXT NOT NULL,
                    occurred_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    detail_json TEXT NOT NULL DEFAULT '{}'
                );
                CREATE INDEX IF NOT EXISTS idx_cloud_events_device
                    ON cloud_device_events(device_id, occurred_at);

                CREATE TABLE IF NOT EXISTS cloud_measurements (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    source_id TEXT NOT NULL,
                    recorded_at TEXT NOT NULL,
                    received_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,
                    payload_json TEXT NOT NULL DEFAULT '{}',
                    UNIQUE(device_id, source_id)
                );
                CREATE INDEX IF NOT EXISTS idx_cloud_measurements_equipment
                    ON cloud_measurements(equipment_id, recorded_at);

                CREATE TABLE IF NOT EXISTS cloud_sync_batches (
                    batch_id TEXT PRIMARY KEY,
                    device_id TEXT NOT NULL,
                    received_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    accepted_count INTEGER NOT NULL DEFAULT 0,
                    duplicate_count INTEGER NOT NULL DEFAULT 0
                );
                """
            )

    def register_device(
        self,
        *,
        device_id: str,
        equipment_id: str,
        site_code: str | None = None,
        display_name: str | None = None,
        edge_version: str | None = None,
        heartbeat_interval_seconds: int = 15,
        last_ip: str | None = None,
        metadata: dict[str, object] | None = None,
        now_iso: str | None = None,
    ) -> str | None:
        now_iso = now_iso or datetime.now().isoformat(timespec="seconds")
        transition: str | None = None
        with self._connect() as connection:
            previous = connection.execute(
                "SELECT online FROM cloud_devices WHERE device_id=?", (device_id,)
            ).fetchone()
            if previous is not None and not int(previous["online"]):
                transition = "DEVICE_ONLINE"
            connection.execute(
                """
                INSERT INTO cloud_devices(
                    device_id,equipment_id,site_code,display_name,edge_version,
                    last_heartbeat_at,heartbeat_interval_seconds,online,last_ip,metadata_json
                ) VALUES(?,?,?,?,?,?,?,1,?,?)
                ON CONFLICT(device_id) DO UPDATE SET
                    equipment_id=excluded.equipment_id,
                    site_code=COALESCE(excluded.site_code, cloud_devices.site_code),
                    display_name=COALESCE(excluded.display_name, cloud_devices.display_name),
                    edge_version=COALESCE(excluded.edge_version, cloud_devices.edge_version),
                    last_heartbeat_at=excluded.last_heartbeat_at,
                    heartbeat_interval_seconds=excluded.heartbeat_interval_seconds,
                    online=1,
                    last_ip=COALESCE(excluded.last_ip, cloud_devices.last_ip),
                    metadata_json=excluded.metadata_json
                """,
                (
                    device_id, equipment_id, site_code, display_name, edge_version,
                    now_iso, max(5, int(heartbeat_interval_seconds)), last_ip,
                    json.dumps(metadata or {}, ensure_ascii=False),
                ),
            )
            if previous is None:
                self._event(connection, device_id, equipment_id, "DEVICE_REGISTERED", now_iso, {})
            elif transition:
                self._event(connection, device_id, equipment_id, transition, now_iso, {})
        return transition

    def heartbeat(
        self,
        *,
        device_id: str,
        equipment_id: str,
        edge_version: str | None = None,
        heartbeat_interval_seconds: int = 15,
        last_ip: str | None = None,
        metadata: dict[str, object] | None = None,
        now_iso: str | None = None,
    ) -> str | None:
        return self.register_device(
            device_id=device_id,
            equipment_id=equipment_id,
            edge_version=edge_version,
            heartbeat_interval_seconds=heartbeat_interval_seconds,
            last_ip=last_ip,
            metadata=metadata,
            now_iso=now_iso,
        )

    def sweep_offline(
        self,
        *,
        grace_seconds: int = 180,
        now: datetime | None = None,
    ) -> list[dict[str, object]]:
        now = now or datetime.now()
        cutoff = now - timedelta(seconds=max(30, int(grace_seconds)))
        changes: list[dict[str, object]] = []
        with self._connect() as connection:
            rows = connection.execute(
                """SELECT * FROM cloud_devices
                   WHERE online=1 AND last_heartbeat_at IS NOT NULL"""
            ).fetchall()
            for row in rows:
                try:
                    last = datetime.fromisoformat(str(row["last_heartbeat_at"]).replace("Z", "+00:00")).replace(tzinfo=None)
                except ValueError:
                    continue
                # Use the larger of server grace or 3 expected heartbeat intervals.
                device_cutoff = now - timedelta(
                    seconds=max(max(30, int(grace_seconds)), 3 * int(row["heartbeat_interval_seconds"] or 15))
                )
                if last <= device_cutoff:
                    connection.execute(
                        "UPDATE cloud_devices SET online=0 WHERE device_id=?", (row["device_id"],)
                    )
                    event_time = now.isoformat(timespec="seconds")
                    detail = {"last_heartbeat_at": row["last_heartbeat_at"], "grace_seconds": grace_seconds}
                    self._event(
                        connection, str(row["device_id"]), str(row["equipment_id"]),
                        "DEVICE_OFFLINE", event_time, detail,
                    )
                    changes.append({
                        "device_id": str(row["device_id"]),
                        "equipment_id": str(row["equipment_id"]),
                        "event_type": "DEVICE_OFFLINE",
                        "occurred_at": event_time,
                        "last_heartbeat_at": str(row["last_heartbeat_at"]),
                    })
        return changes

    def ingest_measurement(self, payload: dict[str, object]) -> bool:
        source_id = str(payload.get("source_id") or payload.get("id") or "")
        device_id = str(payload.get("device_id") or "")
        equipment_id = str(payload.get("equipment_id") or "")
        recorded_at = str(payload.get("recorded_at") or "")
        if not source_id or not device_id or not equipment_id or not recorded_at:
            raise ValueError("source_id, recorded_at, device_id and equipment_id are required")
        with self._connect() as connection:
            cursor = connection.execute(
                """
                INSERT OR IGNORE INTO cloud_measurements(
                    source_id,recorded_at,device_id,equipment_id,
                    ambient_temperature_c,internal_temperature_c,evaporator_temperature_c,
                    compressor_temperature_c,compressor_current_a,health_score,payload_json
                ) VALUES(?,?,?,?,?,?,?,?,?,?,?)
                """,
                (
                    source_id, recorded_at, device_id, equipment_id,
                    payload.get("ambient_temperature_c"), payload.get("internal_temperature_c"),
                    payload.get("evaporator_temperature_c"), payload.get("compressor_temperature_c"),
                    payload.get("compressor_current_a"), payload.get("health_score"),
                    json.dumps(payload, ensure_ascii=False, default=str),
                ),
            )
            return cursor.rowcount > 0

    def record_batch(self, batch_id: str, device_id: str, accepted: int, duplicates: int) -> None:
        with self._connect() as connection:
            connection.execute(
                """INSERT OR REPLACE INTO cloud_sync_batches(batch_id,device_id,accepted_count,duplicate_count)
                   VALUES(?,?,?,?)""",
                (batch_id, device_id, accepted, duplicates),
            )

    def batch_exists(self, batch_id: str) -> bool:
        with self._connect() as connection:
            return connection.execute(
                "SELECT 1 FROM cloud_sync_batches WHERE batch_id=?", (batch_id,)
            ).fetchone() is not None

    def devices(self) -> list[dict[str, object]]:
        with self._connect() as connection:
            rows = connection.execute(
                "SELECT * FROM cloud_devices ORDER BY online DESC,equipment_id,device_id"
            ).fetchall()
        return [dict(row) for row in rows]

    def events(self, limit: int = 250) -> list[dict[str, object]]:
        with self._connect() as connection:
            rows = connection.execute(
                "SELECT * FROM cloud_device_events ORDER BY id DESC LIMIT ?", (max(1, min(limit, 5000)),)
            ).fetchall()
        result=[]
        for row in rows:
            item=dict(row)
            try:
                item["detail"] = json.loads(str(item.get("detail_json") or "{}"))
            except json.JSONDecodeError:
                item["detail"] = {}
            result.append(item)
        return result

    def stats(self) -> dict[str, int]:
        with self._connect() as connection:
            row = connection.execute(
                """SELECT COUNT(*) total, SUM(CASE WHEN online=1 THEN 1 ELSE 0 END) online
                   FROM cloud_devices"""
            ).fetchone()
            measurements = connection.execute("SELECT COUNT(*) n FROM cloud_measurements").fetchone()["n"]
        total=int(row["total"] or 0)
        online=int(row["online"] or 0)
        return {"devices": total, "online": online, "offline": total-online, "synced_measurements": int(measurements)}

    def _event(self, connection, device_id: str, equipment_id: str | None, event_type: str, occurred_at: str, detail: dict[str, object]) -> None:
        connection.execute(
            """INSERT INTO cloud_device_events(device_id,equipment_id,event_type,occurred_at,detail_json)
               VALUES(?,?,?,?,?)""",
            (device_id, equipment_id, event_type, occurred_at, json.dumps(detail, ensure_ascii=False)),
        )

    @contextmanager
    def _connect(self):
        connection = sqlite3.connect(self.database_path)
        try:
            connection.row_factory = sqlite3.Row
            connection.execute("PRAGMA journal_mode=WAL")
            connection.execute("PRAGMA foreign_keys=ON")
            yield connection
            connection.commit()
        except Exception:
            connection.rollback()
            raise
        finally:
            connection.close()
