
"""
Persistence and operational services for Sentinel monitoring.

V5.7 turns the previous administration foundations into real,
persistent CRUD operations for users, groups, alarm rules, group
assignments and alarm suppression schedules.
"""

from __future__ import annotations

import json
import sqlite3
from datetime import datetime
from pathlib import Path
from typing import Iterable


ROLE_DEFINITIONS = (
    {
        "code": "ADMIN",
        "label": "Admin",
        "description": "Full platform and configuration access.",
    },
    {
        "code": "MANAGER",
        "label": "Manager",
        "description": "Fleet, reports, alarms and maintenance oversight.",
    },
    {
        "code": "OPERATOR",
        "label": "Operator",
        "description": "Daily monitoring and alarm acknowledgement.",
    },
    {
        "code": "VIEWER",
        "label": "Viewer",
        "description": "Read-only access to assigned equipment.",
    },
)


class MonitoringStore:
    """SQLite persistence for monitoring and administration data."""

    def __init__(self, database_path: Path) -> None:
        """Create a monitoring store bound to one SQLite database."""
        self.database_path = Path(database_path)

    def initialize(self) -> None:
        """Create and migrate monitoring tables."""
        self.database_path.parent.mkdir(parents=True, exist_ok=True)

        with self._connect() as connection:
            connection.executescript(
                """
                CREATE TABLE IF NOT EXISTS users (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    email TEXT NOT NULL UNIQUE,
                    display_name TEXT NOT NULL,
                    role_code TEXT NOT NULL,
                    enabled INTEGER NOT NULL DEFAULT 1,
                    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
                );

                CREATE TABLE IF NOT EXISTS groups (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    group_code TEXT NOT NULL UNIQUE,
                    display_name TEXT NOT NULL,
                    site_code TEXT,
                    description TEXT,
                    enabled INTEGER NOT NULL DEFAULT 1,
                    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
                );

                CREATE TABLE IF NOT EXISTS equipment_groups (
                    equipment_id TEXT NOT NULL,
                    group_id INTEGER NOT NULL,
                    assigned_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    PRIMARY KEY(equipment_id, group_id),
                    FOREIGN KEY(group_id) REFERENCES groups(id)
                );

                CREATE TABLE IF NOT EXISTS user_groups (
                    user_id INTEGER NOT NULL,
                    group_id INTEGER NOT NULL,
                    can_view INTEGER NOT NULL DEFAULT 1,
                    can_acknowledge INTEGER NOT NULL DEFAULT 0,
                    can_manage_alarms INTEGER NOT NULL DEFAULT 0,
                    PRIMARY KEY(user_id, group_id),
                    FOREIGN KEY(user_id) REFERENCES users(id),
                    FOREIGN KEY(group_id) REFERENCES groups(id)
                );

                CREATE TABLE IF NOT EXISTS alarm_rules (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    name TEXT NOT NULL,
                    scope_type TEXT NOT NULL,
                    scope_id TEXT NOT NULL,
                    category TEXT NOT NULL,
                    severity TEXT NOT NULL,
                    rule_code TEXT NOT NULL,
                    parameters_json TEXT NOT NULL DEFAULT '{}',
                    enabled INTEGER NOT NULL DEFAULT 1,
                    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
                );

                CREATE INDEX IF NOT EXISTS idx_alarm_rules_scope
                    ON alarm_rules(scope_type, scope_id);

                CREATE TABLE IF NOT EXISTS alarm_rule_recipients (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    rule_id INTEGER NOT NULL,
                    step_order INTEGER NOT NULL DEFAULT 1,
                    channel TEXT NOT NULL DEFAULT 'EMAIL',
                    recipient TEXT NOT NULL,
                    delay_minutes INTEGER NOT NULL DEFAULT 0,
                    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY(rule_id) REFERENCES alarm_rules(id) ON DELETE CASCADE
                );

                CREATE TABLE IF NOT EXISTS alarm_schedules (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    name TEXT NOT NULL,
                    scope_type TEXT NOT NULL,
                    scope_id TEXT NOT NULL,
                    alarm_category TEXT,
                    alarm_code TEXT,
                    starts_at TEXT NOT NULL,
                    ends_at TEXT NOT NULL,
                    timezone TEXT NOT NULL DEFAULT 'Europe/Paris',
                    reason TEXT NOT NULL,
                    created_by TEXT,
                    enabled INTEGER NOT NULL DEFAULT 1,
                    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
                );

                CREATE INDEX IF NOT EXISTS idx_alarm_schedules_scope
                    ON alarm_schedules(scope_type, scope_id);

                CREATE TABLE IF NOT EXISTS alarms (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    equipment_id TEXT NOT NULL,
                    category TEXT NOT NULL,
                    severity TEXT NOT NULL,
                    state TEXT NOT NULL DEFAULT 'ACTIVE',
                    code TEXT NOT NULL,
                    title TEXT NOT NULL,
                    message TEXT NOT NULL,
                    source TEXT NOT NULL DEFAULT 'SENTINEL',
                    confidence REAL,
                    suppressed INTEGER NOT NULL DEFAULT 0,
                    suppression_schedule_id INTEGER,
                    opened_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    acknowledged_at TEXT,
                    acknowledged_by TEXT,
                    acknowledgement_comment TEXT,
                    resolved_at TEXT,
                    resolution_comment TEXT
                );

                CREATE INDEX IF NOT EXISTS idx_alarms_equipment
                    ON alarms(equipment_id);

                CREATE INDEX IF NOT EXISTS idx_alarms_state
                    ON alarms(state);

                CREATE INDEX IF NOT EXISTS idx_alarms_code
                    ON alarms(code);

                CREATE TABLE IF NOT EXISTS alarm_events (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    alarm_id INTEGER,
                    equipment_id TEXT NOT NULL,
                    event_type TEXT NOT NULL,
                    actor TEXT,
                    comment TEXT,
                    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY(alarm_id) REFERENCES alarms(id)
                );

                CREATE INDEX IF NOT EXISTS idx_alarm_events_created
                    ON alarm_events(created_at);

                CREATE TABLE IF NOT EXISTS alarm_resolution_locks (
                    equipment_id TEXT NOT NULL,
                    alarm_code TEXT NOT NULL,
                    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    PRIMARY KEY(equipment_id, alarm_code)
                );

                CREATE TABLE IF NOT EXISTS alarm_notifications (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    alarm_id INTEGER NOT NULL,
                    rule_id INTEGER,
                    step_order INTEGER NOT NULL,
                    channel TEXT NOT NULL,
                    recipient TEXT NOT NULL,
                    scheduled_at TEXT NOT NULL,
                    sent_at TEXT,
                    status TEXT NOT NULL DEFAULT 'PENDING',
                    detail TEXT,
                    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY(alarm_id) REFERENCES alarms(id)
                );
                CREATE INDEX IF NOT EXISTS idx_alarm_notifications_alarm
                    ON alarm_notifications(alarm_id, step_order, id);
                """
            )

            self._ensure_column(
                connection,
                "users",
                "updated_at",
                "TEXT NOT NULL DEFAULT ''",
            )
            self._ensure_column(
                connection,
                "groups",
                "updated_at",
                "TEXT NOT NULL DEFAULT ''",
            )
            self._ensure_column(
                connection,
                "alarm_schedules",
                "updated_at",
                "TEXT NOT NULL DEFAULT ''",
            )
            self._ensure_column(
                connection,
                "alarms",
                "suppressed",
                "INTEGER NOT NULL DEFAULT 0",
            )
            self._ensure_column(
                connection,
                "alarms",
                "suppression_schedule_id",
                "INTEGER",
            )

    # ------------------------- users -------------------------

    def get_users(self) -> list[dict[str, object]]:
        """Return users with their group memberships."""
        with self._connect() as connection:
            rows = connection.execute(
                """
                SELECT
                    u.*,
                    GROUP_CONCAT(g.group_code, ', ') AS group_codes
                FROM users u
                LEFT JOIN user_groups ug ON ug.user_id = u.id
                LEFT JOIN groups g ON g.id = ug.group_id
                GROUP BY u.id
                ORDER BY u.display_name COLLATE NOCASE
                """
            ).fetchall()
        return [dict(row) for row in rows]

    def create_user(
        self,
        *,
        email: str,
        display_name: str,
        role_code: str,
    ) -> int:
        """Create a persistent user."""
        role = role_code.upper()
        valid_roles = {role["code"] for role in ROLE_DEFINITIONS}
        if role not in valid_roles:
            raise ValueError("Invalid role.")

        with self._connect() as connection:
            cursor = connection.execute(
                """
                INSERT INTO users(email, display_name, role_code)
                VALUES (?, ?, ?)
                """,
                (email.strip().lower(), display_name.strip(), role),
            )
            user_id = int(cursor.lastrowid)
            self._event(
                connection,
                event_type="USER_CREATED",
                actor=email.strip().lower(),
                comment=f"{display_name.strip()} · {role}",
            )
            return user_id

    def update_user(
        self,
        user_id: int,
        *,
        email: str,
        display_name: str,
        role_code: str,
        enabled: bool,
    ) -> None:
        """Update one user."""
        role = role_code.upper()
        valid_roles = {role["code"] for role in ROLE_DEFINITIONS}
        if role not in valid_roles:
            raise ValueError("Invalid role.")

        with self._connect() as connection:
            connection.execute(
                """
                UPDATE users
                SET email = ?,
                    display_name = ?,
                    role_code = ?,
                    enabled = ?,
                    updated_at = CURRENT_TIMESTAMP
                WHERE id = ?
                """,
                (
                    email.strip().lower(),
                    display_name.strip(),
                    role,
                    1 if enabled else 0,
                    user_id,
                ),
            )
            self._event(
                connection,
                event_type="USER_UPDATED",
                actor=email.strip().lower(),
                comment=f"user_id={user_id}",
            )

    def delete_user(self, user_id: int) -> None:
        """Delete a user and its group assignments."""
        with self._connect() as connection:
            row = connection.execute(
                "SELECT email FROM users WHERE id = ?",
                (user_id,),
            ).fetchone()
            connection.execute(
                "DELETE FROM user_groups WHERE user_id = ?",
                (user_id,),
            )
            connection.execute(
                "DELETE FROM users WHERE id = ?",
                (user_id,),
            )
            self._event(
                connection,
                event_type="USER_DELETED",
                actor=str(row["email"]) if row else None,
                comment=f"user_id={user_id}",
            )

    # ------------------------- groups -------------------------

    def get_groups(self) -> list[dict[str, object]]:
        """Return configured groups and member counts."""
        with self._connect() as connection:
            rows = connection.execute(
                """
                SELECT
                    g.*,
                    COUNT(DISTINCT eg.equipment_id) AS equipment_count,
                    COUNT(DISTINCT ug.user_id) AS user_count
                FROM groups g
                LEFT JOIN equipment_groups eg ON eg.group_id = g.id
                LEFT JOIN user_groups ug ON ug.group_id = g.id
                GROUP BY g.id
                ORDER BY g.display_name COLLATE NOCASE
                """
            ).fetchall()
        return [dict(row) for row in rows]

    def create_group(
        self,
        *,
        group_code: str,
        display_name: str,
        site_code: str | None,
        description: str | None,
    ) -> int:
        """Create a laboratory group."""
        code = group_code.strip().upper()
        with self._connect() as connection:
            cursor = connection.execute(
                """
                INSERT INTO groups(
                    group_code,
                    display_name,
                    site_code,
                    description
                )
                VALUES (?, ?, ?, ?)
                """,
                (
                    code,
                    display_name.strip(),
                    (site_code or "").strip() or None,
                    (description or "").strip() or None,
                ),
            )
            group_id = int(cursor.lastrowid)
            self._event(
                connection,
                event_type="GROUP_CREATED",
                comment=f"{code} · {display_name.strip()}",
            )
            return group_id

    def update_group(
        self,
        group_id: int,
        *,
        group_code: str,
        display_name: str,
        site_code: str | None,
        description: str | None,
        enabled: bool,
    ) -> None:
        """Update one group."""
        code = group_code.strip().upper()
        with self._connect() as connection:
            connection.execute(
                """
                UPDATE groups
                SET group_code = ?,
                    display_name = ?,
                    site_code = ?,
                    description = ?,
                    enabled = ?,
                    updated_at = CURRENT_TIMESTAMP
                WHERE id = ?
                """,
                (
                    code,
                    display_name.strip(),
                    (site_code or "").strip() or None,
                    (description or "").strip() or None,
                    1 if enabled else 0,
                    group_id,
                ),
            )
            self._event(
                connection,
                event_type="GROUP_UPDATED",
                comment=f"{code} · group_id={group_id}",
            )

    def delete_group(self, group_id: int) -> None:
        """Delete a group and all assignments to it."""
        with self._connect() as connection:
            row = connection.execute(
                "SELECT group_code FROM groups WHERE id = ?",
                (group_id,),
            ).fetchone()
            connection.execute(
                "DELETE FROM equipment_groups WHERE group_id = ?",
                (group_id,),
            )
            connection.execute(
                "DELETE FROM user_groups WHERE group_id = ?",
                (group_id,),
            )
            connection.execute(
                "DELETE FROM groups WHERE id = ?",
                (group_id,),
            )
            self._event(
                connection,
                event_type="GROUP_DELETED",
                comment=str(row["group_code"]) if row else f"group_id={group_id}",
            )

    def assign_equipment_to_group(
        self,
        *,
        equipment_id: str,
        group_id: int,
    ) -> None:
        """Assign one equipment to one group."""
        with self._connect() as connection:
            connection.execute(
                """
                INSERT OR IGNORE INTO equipment_groups(
                    equipment_id,
                    group_id
                )
                VALUES (?, ?)
                """,
                (equipment_id, group_id),
            )
            self._event(
                connection,
                event_type="EQUIPMENT_GROUP_ASSIGNED",
                equipment_id=equipment_id,
                comment=f"group_id={group_id}",
            )

    def remove_equipment_from_group(
        self,
        *,
        equipment_id: str,
        group_id: int,
    ) -> None:
        """Remove one equipment from one group."""
        with self._connect() as connection:
            connection.execute(
                """
                DELETE FROM equipment_groups
                WHERE equipment_id = ? AND group_id = ?
                """,
                (equipment_id, group_id),
            )
            self._event(
                connection,
                event_type="EQUIPMENT_GROUP_REMOVED",
                equipment_id=equipment_id,
                comment=f"group_id={group_id}",
            )

    def assign_user_to_group(
        self,
        *,
        user_id: int,
        group_id: int,
        can_view: bool = True,
        can_acknowledge: bool = False,
        can_manage_alarms: bool = False,
    ) -> None:
        """Assign a user to a group with group-level permissions."""
        with self._connect() as connection:
            connection.execute(
                """
                INSERT INTO user_groups(
                    user_id,
                    group_id,
                    can_view,
                    can_acknowledge,
                    can_manage_alarms
                )
                VALUES (?, ?, ?, ?, ?)
                ON CONFLICT(user_id, group_id)
                DO UPDATE SET
                    can_view = excluded.can_view,
                    can_acknowledge = excluded.can_acknowledge,
                    can_manage_alarms = excluded.can_manage_alarms
                """,
                (
                    user_id,
                    group_id,
                    1 if can_view else 0,
                    1 if can_acknowledge else 0,
                    1 if can_manage_alarms else 0,
                ),
            )
            self._event(
                connection,
                event_type="USER_GROUP_ASSIGNED",
                comment=f"user_id={user_id} · group_id={group_id}",
            )

    def remove_user_from_group(
        self,
        *,
        user_id: int,
        group_id: int,
    ) -> None:
        """Remove one user's group assignment."""
        with self._connect() as connection:
            connection.execute(
                """
                DELETE FROM user_groups
                WHERE user_id = ? AND group_id = ?
                """,
                (user_id, group_id),
            )
            self._event(
                connection,
                event_type="USER_GROUP_REMOVED",
                comment=f"user_id={user_id} · group_id={group_id}",
            )

    def get_equipment_group_map(self) -> dict[str, list[dict[str, object]]]:
        """Return groups indexed by equipment id."""
        with self._connect() as connection:
            rows = connection.execute(
                """
                SELECT
                    eg.equipment_id,
                    g.id,
                    g.group_code,
                    g.display_name,
                    g.site_code
                FROM equipment_groups eg
                JOIN groups g ON g.id = eg.group_id
                WHERE g.enabled = 1
                ORDER BY g.display_name COLLATE NOCASE
                """
            ).fetchall()

        result: dict[str, list[dict[str, object]]] = {}
        for row in rows:
            equipment_id = str(row["equipment_id"])
            result.setdefault(equipment_id, []).append(dict(row))
        return result

    def get_group_equipment_ids(
        self,
        group_id: int,
    ) -> list[str]:
        """Return equipment ids belonging to a group."""
        with self._connect() as connection:
            rows = connection.execute(
                """
                SELECT equipment_id
                FROM equipment_groups
                WHERE group_id = ?
                ORDER BY equipment_id
                """,
                (group_id,),
            ).fetchall()
        return [str(row["equipment_id"]) for row in rows]

    # ------------------------- alarm rules -------------------------

    def get_alarm_rules(self) -> list[dict[str, object]]:
        """Return alarm rules with decoded parameters."""
        with self._connect() as connection:
            rows = connection.execute(
                """
                SELECT *
                FROM alarm_rules
                ORDER BY enabled DESC, scope_type, scope_id, name
                """
            ).fetchall()

        result: list[dict[str, object]] = []
        for row in rows:
            item = dict(row)
            try:
                item["parameters"] = json.loads(
                    str(item["parameters_json"])
                )
            except json.JSONDecodeError:
                item["parameters"] = {}
            result.append(item)
        return result

    def create_alarm_rule(
        self,
        *,
        name: str,
        scope_type: str,
        scope_id: str,
        category: str,
        severity: str,
        metric: str,
        operator: str,
        threshold: float,
        sensor_id: str | None = None,
        delay_minutes: int = 0,
        hysteresis: float = 0.0,
    ) -> int:
        """Create an executable threshold-style alarm rule."""
        scope = scope_type.upper()
        if scope not in {"SITE", "GROUP", "EQUIPMENT"}:
            raise ValueError("Invalid alarm-rule scope.")
        op = operator.lower()
        if op not in {"above", "below"}:
            raise ValueError("Invalid alarm-rule operator.")

        parameters = {
            "metric": metric,
            "sensor_id": sensor_id or None,
            "operator": op,
            "threshold": float(threshold),
            "delay_minutes": max(0, int(delay_minutes)),
            "hysteresis": max(0.0, float(hysteresis)),
        }

        with self._connect() as connection:
            cursor = connection.execute(
                """
                INSERT INTO alarm_rules(
                    name,
                    scope_type,
                    scope_id,
                    category,
                    severity,
                    rule_code,
                    parameters_json
                )
                VALUES (?, ?, ?, ?, ?, ?, ?)
                """,
                (
                    name.strip(),
                    scope,
                    scope_id.strip(),
                    category.upper(),
                    severity.upper(),
                    "TEMPERATURE_THRESHOLD",
                    json.dumps(parameters),
                ),
            )
            rule_id = int(cursor.lastrowid)
            self._event(
                connection,
                event_type="ALARM_RULE_CREATED",
                comment=f"rule_id={rule_id} · {name.strip()}",
            )
            return rule_id

    def update_alarm_rule(
        self,
        rule_id: int,
        *,
        name: str,
        scope_type: str,
        scope_id: str,
        category: str,
        severity: str,
        metric: str,
        operator: str,
        threshold: float,
        enabled: bool,
        sensor_id: str | None = None,
        delay_minutes: int = 0,
        hysteresis: float = 0.0,
    ) -> None:
        """Update one executable alarm rule."""
        parameters = {
            "metric": metric,
            "sensor_id": sensor_id or None,
            "operator": operator.lower(),
            "threshold": float(threshold),
            "delay_minutes": max(0, int(delay_minutes)),
            "hysteresis": max(0.0, float(hysteresis)),
        }
        with self._connect() as connection:
            connection.execute(
                """
                UPDATE alarm_rules
                SET name = ?,
                    scope_type = ?,
                    scope_id = ?,
                    category = ?,
                    severity = ?,
                    parameters_json = ?,
                    enabled = ?,
                    updated_at = CURRENT_TIMESTAMP
                WHERE id = ?
                """,
                (
                    name.strip(),
                    scope_type.upper(),
                    scope_id.strip(),
                    category.upper(),
                    severity.upper(),
                    json.dumps(parameters),
                    1 if enabled else 0,
                    rule_id,
                ),
            )
            self._event(
                connection,
                event_type="ALARM_RULE_UPDATED",
                comment=f"rule_id={rule_id}",
            )

    def get_alarm_rule_recipients(self) -> list[dict[str, object]]:
        with self._connect() as connection:
            rows=connection.execute("SELECT * FROM alarm_rule_recipients ORDER BY rule_id, step_order, id").fetchall()
        return [dict(row) for row in rows]

    def add_alarm_rule_recipient(self, *, rule_id: int, step_order: int, channel: str, recipient: str, delay_minutes: int = 0) -> int:
        channel=channel.upper()
        if channel not in {"EMAIL","SMS","BOTH"}: raise ValueError("Invalid notification channel.")
        if not recipient.strip(): raise ValueError("Recipient is required.")
        with self._connect() as connection:
            cursor=connection.execute("INSERT INTO alarm_rule_recipients(rule_id,step_order,channel,recipient,delay_minutes) VALUES(?,?,?,?,?)",(rule_id,max(1,int(step_order)),channel,recipient.strip(),max(0,int(delay_minutes))))
            self._event(connection,event_type="ALARM_RECIPIENT_ADDED",comment=f"rule_id={rule_id} · {recipient.strip()}")
            return int(cursor.lastrowid)

    def delete_alarm_rule_recipient(self, recipient_id: int) -> None:
        with self._connect() as connection:
            connection.execute("DELETE FROM alarm_rule_recipients WHERE id=?",(recipient_id,))
            self._event(connection,event_type="ALARM_RECIPIENT_DELETED",comment=f"recipient_id={recipient_id}")

    def delete_alarm_rule(self, rule_id: int) -> None:
        """Delete one alarm rule."""
        with self._connect() as connection:
            connection.execute("DELETE FROM alarm_rule_recipients WHERE rule_id = ?", (rule_id,))
            connection.execute(
                "DELETE FROM alarm_rules WHERE id = ?",
                (rule_id,),
            )
            self._event(
                connection,
                event_type="ALARM_RULE_DELETED",
                comment=f"rule_id={rule_id}",
            )

    def get_group_memberships(self) -> list[dict[str, object]]:
        """Return every equipment/group membership for administration UI."""
        with self._connect() as connection:
            rows = connection.execute(
                """
                SELECT eg.equipment_id, g.id AS group_id, g.group_code, g.display_name
                FROM equipment_groups eg
                JOIN groups g ON g.id = eg.group_id
                ORDER BY eg.equipment_id, g.group_code
                """
            ).fetchall()
        return [dict(row) for row in rows]

    def alarm_events_for(self, alarm_ids: Iterable[int]) -> dict[int, list[dict[str, object]]]:
        ids = [int(value) for value in alarm_ids]
        if not ids:
            return {}
        placeholders = ",".join("?" for _ in ids)
        with self._connect() as connection:
            rows = connection.execute(
                f"SELECT * FROM alarm_events WHERE alarm_id IN ({placeholders}) ORDER BY created_at, id", ids
            ).fetchall()
        result: dict[int, list[dict[str, object]]] = {}
        for row in rows:
            result.setdefault(int(row["alarm_id"]), []).append(dict(row))
        return result

    def get_alarm_notifications(self, alarm_id: int | None = None) -> list[dict[str, object]]:
        with self._connect() as connection:
            if alarm_id is None:
                rows = connection.execute(
                    "SELECT * FROM alarm_notifications ORDER BY created_at DESC, id DESC LIMIT 500"
                ).fetchall()
            else:
                rows = connection.execute(
                    "SELECT * FROM alarm_notifications WHERE alarm_id=? ORDER BY step_order,id",
                    (alarm_id,),
                ).fetchall()
        return [dict(row) for row in rows]

    def queue_alarm_notifications(self, *, alarm_id: int, rule_id: int) -> None:
        """Create a traceable escalation plan once for a newly opened alarm."""
        with self._connect() as connection:
            exists = connection.execute(
                "SELECT 1 FROM alarm_notifications WHERE alarm_id=? LIMIT 1", (alarm_id,)
            ).fetchone()
            if exists:
                return
            recipients = connection.execute(
                "SELECT * FROM alarm_rule_recipients WHERE rule_id=? ORDER BY step_order,id", (rule_id,)
            ).fetchall()
            opened = connection.execute(
                "SELECT opened_at,equipment_id FROM alarms WHERE id=?", (alarm_id,)
            ).fetchone()
            if not opened:
                return
            base = datetime.fromisoformat(str(opened["opened_at"]).replace("Z", "+00:00")).replace(tzinfo=None)
            cumulative = 0
            for recipient in recipients:
                cumulative += max(0, int(recipient["delay_minutes"] or 0))
                scheduled = (base + __import__('datetime').timedelta(minutes=cumulative)).isoformat(timespec="seconds")
                connection.execute(
                    """INSERT INTO alarm_notifications(alarm_id,rule_id,step_order,channel,recipient,scheduled_at,status)
                    VALUES(?,?,?,?,?,?, 'PENDING')""",
                    (alarm_id, rule_id, int(recipient["step_order"]), str(recipient["channel"]), str(recipient["recipient"]), scheduled),
                )
            if recipients:
                self._event(connection, alarm_id=alarm_id, equipment_id=str(opened["equipment_id"]), event_type="ALARM_ESCALATION_QUEUED", comment=f"{len(recipients)} notification step(s) queued")

    def queue_direct_notification(
        self,
        *,
        alarm_id: int,
        recipient: str,
        channel: str = "EMAIL",
        step_order: int = 1,
        delay_minutes: int = 0,
    ) -> None:
        """Queue a notification not tied to a threshold rule (V7 device/system alarms)."""
        with self._connect() as connection:
            exists = connection.execute(
                "SELECT 1 FROM alarm_notifications WHERE alarm_id=? AND recipient=? AND channel=? LIMIT 1",
                (alarm_id, recipient, channel.upper()),
            ).fetchone()
            if exists:
                return
            alarm = connection.execute(
                "SELECT opened_at,equipment_id FROM alarms WHERE id=?", (alarm_id,)
            ).fetchone()
            if not alarm:
                return
            base = datetime.fromisoformat(str(alarm["opened_at"]).replace("Z", "+00:00")).replace(tzinfo=None)
            scheduled = (base + __import__('datetime').timedelta(minutes=max(0, int(delay_minutes)))).isoformat(timespec="seconds")
            connection.execute(
                """INSERT INTO alarm_notifications(
                    alarm_id,rule_id,step_order,channel,recipient,scheduled_at,status
                ) VALUES(?,NULL,?,?,?,?, 'PENDING')""",
                (alarm_id, int(step_order), channel.upper(), recipient, scheduled),
            )
            self._event(
                connection, alarm_id=alarm_id, equipment_id=str(alarm["equipment_id"]),
                event_type="ALARM_NOTIFICATION_QUEUED", comment=f"{channel.upper()} → {recipient}",
            )

    def pending_notifications(self, now_iso: str) -> list[dict[str, object]]:
        with self._connect() as connection:
            rows = connection.execute(
                """
                SELECT n.*, a.state, a.equipment_id, a.title, a.message, a.severity, a.opened_at
                FROM alarm_notifications n JOIN alarms a ON a.id=n.alarm_id
                WHERE n.status='PENDING' AND n.scheduled_at <= ?
                ORDER BY n.scheduled_at,n.step_order,n.id
                """, (now_iso,)
            ).fetchall()
        return [dict(row) for row in rows]

    def mark_notification(self, notification_id: int, *, status: str, detail: str | None = None) -> None:
        with self._connect() as connection:
            row=connection.execute("SELECT alarm_id FROM alarm_notifications WHERE id=?",(notification_id,)).fetchone()
            connection.execute(
                "UPDATE alarm_notifications SET status=?, detail=?, sent_at=CASE WHEN ?='SENT' THEN CURRENT_TIMESTAMP ELSE sent_at END WHERE id=?",
                (status, detail, status, notification_id),
            )
            if row:
                self._event(connection, alarm_id=int(row["alarm_id"]), event_type=f"NOTIFICATION_{status}", comment=detail)

    def cancel_pending_notifications(self, alarm_id: int, reason: str) -> None:
        with self._connect() as connection:
            connection.execute(
                "UPDATE alarm_notifications SET status='CANCELLED',detail=? WHERE alarm_id=? AND status IN ('PENDING','WAITING_TRANSPORT')",
                (reason, alarm_id),
            )

    def is_resolution_locked(self, equipment_id: str, alarm_code: str) -> bool:
        with self._connect() as connection:
            return connection.execute("SELECT 1 FROM alarm_resolution_locks WHERE equipment_id=? AND alarm_code=?",(equipment_id,alarm_code)).fetchone() is not None

    def clear_resolution_lock(self, equipment_id: str, alarm_code: str) -> None:
        with self._connect() as connection:
            connection.execute("DELETE FROM alarm_resolution_locks WHERE equipment_id=? AND alarm_code=?",(equipment_id,alarm_code))

    # ------------------------- schedules -------------------------

    def get_alarm_schedules(self) -> list[dict[str, object]]:
        """Return suppression/maintenance schedules."""
        with self._connect() as connection:
            rows = connection.execute(
                """
                SELECT *
                FROM alarm_schedules
                ORDER BY enabled DESC, starts_at DESC, id DESC
                """
            ).fetchall()
        return [dict(row) for row in rows]

    def create_alarm_schedule(
        self,
        *,
        name: str,
        scope_type: str,
        scope_id: str,
        starts_at: str,
        ends_at: str,
        reason: str,
        alarm_category: str | None = None,
        alarm_code: str | None = None,
        timezone: str = "Europe/Paris",
        created_by: str | None = None,
    ) -> int:
        """Create a real suppression/maintenance window."""
        if datetime.fromisoformat(ends_at) <= datetime.fromisoformat(starts_at):
            raise ValueError("End must be after start.")

        with self._connect() as connection:
            cursor = connection.execute(
                """
                INSERT INTO alarm_schedules(
                    name,
                    scope_type,
                    scope_id,
                    alarm_category,
                    alarm_code,
                    starts_at,
                    ends_at,
                    timezone,
                    reason,
                    created_by
                )
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                """,
                (
                    name.strip(),
                    scope_type.upper(),
                    scope_id.strip(),
                    (alarm_category or "").upper() or None,
                    (alarm_code or "").strip() or None,
                    starts_at,
                    ends_at,
                    timezone,
                    reason.strip(),
                    (created_by or "").strip() or None,
                ),
            )
            schedule_id = int(cursor.lastrowid)
            self._event(
                connection,
                event_type="ALARM_SCHEDULE_CREATED",
                actor=created_by,
                comment=f"schedule_id={schedule_id} · {reason.strip()}",
            )
            return schedule_id

    def replace_alarm_schedule(
        self,
        schedule_id: int,
        *,
        name: str, scope_type: str, scope_id: str, starts_at: str, ends_at: str, reason: str,
        alarm_category: str | None = None, alarm_code: str | None = None, timezone: str = "Europe/Paris",
        created_by: str | None = None, enabled: bool = True,
    ) -> None:
        if datetime.fromisoformat(ends_at) <= datetime.fromisoformat(starts_at):
            raise ValueError("End must be after start.")
        with self._connect() as connection:
            connection.execute(
                """UPDATE alarm_schedules SET name=?,scope_type=?,scope_id=?,alarm_category=?,alarm_code=?,starts_at=?,ends_at=?,timezone=?,reason=?,created_by=?,enabled=?,updated_at=CURRENT_TIMESTAMP WHERE id=?""",
                (name.strip(),scope_type.upper(),scope_id.strip(),(alarm_category or "").upper() or None,(alarm_code or "").strip() or None,starts_at,ends_at,timezone,reason.strip(),(created_by or "").strip() or None,1 if enabled else 0,schedule_id),
            )
            self._event(connection,event_type="ALARM_SCHEDULE_EDITED",actor=created_by,comment=f"schedule_id={schedule_id} · {reason.strip()}")

    def update_alarm_schedule(
        self,
        schedule_id: int,
        *,
        enabled: bool,
    ) -> None:
        """Enable or disable a suppression window."""
        with self._connect() as connection:
            connection.execute(
                """
                UPDATE alarm_schedules
                SET enabled = ?,
                    updated_at = CURRENT_TIMESTAMP
                WHERE id = ?
                """,
                (1 if enabled else 0, schedule_id),
            )
            self._event(
                connection,
                event_type="ALARM_SCHEDULE_UPDATED",
                comment=f"schedule_id={schedule_id} · enabled={enabled}",
            )

    def delete_alarm_schedule(self, schedule_id: int) -> None:
        """Delete one suppression window."""
        with self._connect() as connection:
            connection.execute(
                "DELETE FROM alarm_schedules WHERE id = ?",
                (schedule_id,),
            )
            self._event(
                connection,
                event_type="ALARM_SCHEDULE_DELETED",
                comment=f"schedule_id={schedule_id}",
            )

    def active_suppression_for(
        self,
        *,
        equipment_id: str,
        category: str,
        alarm_code: str,
        now_iso: str,
    ) -> dict[str, object] | None:
        """Return the first active schedule matching an alarm."""
        now = datetime.fromisoformat(now_iso)

        with self._connect() as connection:
            rows = connection.execute(
                """
                SELECT *
                FROM alarm_schedules
                WHERE enabled = 1
                ORDER BY id DESC
                """
            ).fetchall()

            groups = {
                str(row["group_code"])
                for row in connection.execute(
                    """
                    SELECT g.group_code
                    FROM equipment_groups eg
                    JOIN groups g ON g.id = eg.group_id
                    WHERE eg.equipment_id = ? AND g.enabled = 1
                    """,
                    (equipment_id,),
                ).fetchall()
            }

        for row in rows:
            item = dict(row)
            try:
                start = datetime.fromisoformat(str(item["starts_at"]))
                end = datetime.fromisoformat(str(item["ends_at"]))
            except ValueError:
                continue
            if not (start <= now <= end):
                continue

            scope_type = str(item["scope_type"]).upper()
            scope_id = str(item["scope_id"])
            scope_match = (
                scope_type == "SITE"
                or (scope_type == "EQUIPMENT" and scope_id == equipment_id)
                or (scope_type == "GROUP" and scope_id in groups)
            )
            if not scope_match:
                continue

            schedule_category = item["alarm_category"]
            if schedule_category and str(schedule_category).upper() != category.upper():
                continue

            schedule_code = item["alarm_code"]
            if schedule_code and str(schedule_code) != alarm_code:
                continue

            return item

        return None

    # ------------------------- alarms -------------------------

    def get_alarm_summary(self) -> dict[str, int]:
        """Return counts for the main alarm states."""
        with self._connect() as connection:
            rows = connection.execute(
                """
                SELECT state, COUNT(*) AS count
                FROM alarms
                GROUP BY state
                """
            ).fetchall()
        counts = {
            str(row["state"]).upper(): int(row["count"])
            for row in rows
        }
        return {
            "active": counts.get("ACTIVE", 0),
            "acknowledged": counts.get("ACKNOWLEDGED", 0),
            "resolved": counts.get("RESOLVED", 0),
        }

    def get_alarms(
        self,
        *,
        states: Iterable[str] | None = None,
        group_code: str | None = None,
        limit: int = 200,
    ) -> list[dict[str, object]]:
        """Return recent alarms, optionally filtered by state/group."""
        parameters: list[object] = []
        clauses: list[str] = []

        if states:
            normalized = [str(state).upper() for state in states]
            placeholders = ",".join("?" for _ in normalized)
            clauses.append(f"a.state IN ({placeholders})")
            parameters.extend(normalized)

        join = ""
        if group_code:
            join = """
                JOIN equipment_groups eg ON eg.equipment_id = a.equipment_id
                JOIN groups g ON g.id = eg.group_id
            """
            clauses.append("g.group_code = ?")
            parameters.append(group_code.upper())

        where = "WHERE " + " AND ".join(clauses) if clauses else ""
        parameters.append(max(1, min(int(limit), 1000)))

        with self._connect() as connection:
            rows = connection.execute(
                f"""
                SELECT DISTINCT a.*
                FROM alarms a
                {join}
                {where}
                ORDER BY a.opened_at DESC, a.id DESC
                LIMIT ?
                """,
                parameters,
            ).fetchall()
        return [dict(row) for row in rows]

    def open_or_update_alarm(
        self,
        *,
        equipment_id: str,
        category: str,
        severity: str,
        code: str,
        title: str,
        message: str,
        suppressed: bool,
        suppression_schedule_id: int | None,
    ) -> int:
        """Open a new alarm or refresh an already-open alarm."""
        with self._connect() as connection:
            row = connection.execute(
                """
                SELECT id
                FROM alarms
                WHERE equipment_id = ?
                  AND code = ?
                  AND state IN ('ACTIVE', 'ACKNOWLEDGED')
                ORDER BY id DESC
                LIMIT 1
                """,
                (equipment_id, code),
            ).fetchone()

            if row:
                alarm_id = int(row["id"])
                connection.execute(
                    """
                    UPDATE alarms
                    SET category = ?,
                        severity = ?,
                        title = ?,
                        message = ?,
                        suppressed = ?,
                        suppression_schedule_id = ?
                    WHERE id = ?
                    """,
                    (
                        category.upper(),
                        severity.upper(),
                        title,
                        message,
                        1 if suppressed else 0,
                        suppression_schedule_id,
                        alarm_id,
                    ),
                )
                return alarm_id

            cursor = connection.execute(
                """
                INSERT INTO alarms(
                    equipment_id,
                    category,
                    severity,
                    state,
                    code,
                    title,
                    message,
                    source,
                    suppressed,
                    suppression_schedule_id
                )
                VALUES (?, ?, ?, 'ACTIVE', ?, ?, ?, 'RULE_ENGINE', ?, ?)
                """,
                (
                    equipment_id,
                    category.upper(),
                    severity.upper(),
                    code,
                    title,
                    message,
                    1 if suppressed else 0,
                    suppression_schedule_id,
                ),
            )
            alarm_id = int(cursor.lastrowid)
            self._event(
                connection,
                alarm_id=alarm_id,
                equipment_id=equipment_id,
                event_type="ALARM_OPENED_SUPPRESSED" if suppressed else "ALARM_OPENED",
                comment=message,
            )
            return alarm_id

    def auto_resolve_alarm(
        self,
        *,
        equipment_id: str,
        code: str,
        comment: str,
    ) -> None:
        """Resolve an open alarm automatically when condition clears."""
        with self._connect() as connection:
            rows = connection.execute(
                """
                SELECT id
                FROM alarms
                WHERE equipment_id = ?
                  AND code = ?
                  AND state IN ('ACTIVE', 'ACKNOWLEDGED')
                """,
                (equipment_id, code),
            ).fetchall()

            for row in rows:
                alarm_id = int(row["id"])
                connection.execute(
                    """
                    UPDATE alarms
                    SET state = 'RESOLVED',
                        resolved_at = CURRENT_TIMESTAMP,
                        resolution_comment = ?
                    WHERE id = ?
                    """,
                    (comment, alarm_id),
                )
                self._event(
                    connection,
                    alarm_id=alarm_id,
                    equipment_id=equipment_id,
                    event_type="ALARM_AUTO_RESOLVED",
                    actor="Sentinel",
                    comment=comment,
                )

    def acknowledge_alarm(
        self,
        alarm_id: int,
        *,
        actor: str,
        comment: str,
    ) -> None:
        """Acknowledge an active alarm."""
        with self._connect() as connection:
            row = connection.execute(
                "SELECT equipment_id FROM alarms WHERE id = ?",
                (alarm_id,),
            ).fetchone()
            if not row:
                raise ValueError("Alarm not found.")

            connection.execute(
                """
                UPDATE alarms
                SET state = 'ACKNOWLEDGED',
                    acknowledged_at = CURRENT_TIMESTAMP,
                    acknowledged_by = ?,
                    acknowledgement_comment = ?
                WHERE id = ? AND state = 'ACTIVE'
                """,
                (actor.strip(), comment.strip(), alarm_id),
            )
            connection.execute(
                "UPDATE alarm_notifications SET status='CANCELLED',detail='Alarm acknowledged; escalation stopped' WHERE alarm_id=? AND status IN ('PENDING','WAITING_TRANSPORT')",
                (alarm_id,),
            )
            self._event(
                connection,
                alarm_id=alarm_id,
                equipment_id=str(row["equipment_id"]),
                event_type="ALARM_ACKNOWLEDGED",
                actor=actor.strip(),
                comment=comment.strip(),
            )

    def resolve_alarm(
        self,
        alarm_id: int,
        *,
        actor: str,
        comment: str,
    ) -> None:
        """Resolve an active or acknowledged alarm."""
        with self._connect() as connection:
            row = connection.execute(
                "SELECT equipment_id FROM alarms WHERE id = ?",
                (alarm_id,),
            ).fetchone()
            if not row:
                raise ValueError("Alarm not found.")

            connection.execute(
                """
                UPDATE alarms
                SET state = 'RESOLVED',
                    resolved_at = CURRENT_TIMESTAMP,
                    resolution_comment = ?
                WHERE id = ?
                """,
                (comment.strip(), alarm_id),
            )
            code_row = connection.execute("SELECT code FROM alarms WHERE id = ?", (alarm_id,)).fetchone()
            if code_row:
                connection.execute(
                    "INSERT OR REPLACE INTO alarm_resolution_locks(equipment_id,alarm_code,created_at) VALUES(?,?,CURRENT_TIMESTAMP)",
                    (str(row["equipment_id"]), str(code_row["code"])),
                )
            connection.execute(
                "UPDATE alarm_notifications SET status='CANCELLED',detail='Alarm manually resolved' WHERE alarm_id=? AND status IN ('PENDING','WAITING_TRANSPORT')",
                (alarm_id,),
            )
            self._event(
                connection,
                alarm_id=alarm_id,
                equipment_id=str(row["equipment_id"]),
                event_type="ALARM_RESOLVED",
                actor=actor.strip(),
                comment=comment.strip(),
            )

    # ------------------------- events -------------------------

    def get_recent_events(
        self,
        *,
        limit: int = 200,
    ) -> list[dict[str, object]]:
        """Return recent alarm/audit events."""
        with self._connect() as connection:
            rows = connection.execute(
                """
                SELECT *
                FROM alarm_events
                ORDER BY created_at DESC, id DESC
                LIMIT ?
                """,
                (max(1, min(int(limit), 1000)),),
            ).fetchall()
        return [dict(row) for row in rows]

    # ------------------------- internals -------------------------

    def _event(
        self,
        connection: sqlite3.Connection,
        *,
        event_type: str,
        equipment_id: str = "*",
        alarm_id: int | None = None,
        actor: str | None = None,
        comment: str | None = None,
    ) -> None:
        """Insert one audit/event row."""
        connection.execute(
            """
            INSERT INTO alarm_events(
                alarm_id,
                equipment_id,
                event_type,
                actor,
                comment
            )
            VALUES (?, ?, ?, ?, ?)
            """,
            (
                alarm_id,
                equipment_id,
                event_type,
                actor,
                comment,
            ),
        )

    def _ensure_column(
        self,
        connection: sqlite3.Connection,
        table: str,
        column: str,
        declaration: str,
    ) -> None:
        """Add one SQLite column when migrating an older database."""
        columns = {
            str(row["name"])
            for row in connection.execute(
                f"PRAGMA table_info({table})"
            ).fetchall()
        }
        if column not in columns:
            connection.execute(
                f"ALTER TABLE {table} ADD COLUMN {column} {declaration}"
            )

    def _connect(self) -> sqlite3.Connection:
        """Open a row-enabled SQLite connection."""
        connection = sqlite3.connect(self.database_path)
        connection.row_factory = sqlite3.Row
        connection.execute("PRAGMA foreign_keys = ON")
        return connection
