spa_database.py

"""
Spa Database - SQLite persistence layer for spa status, connection events, and schedules.

Provides thread-safe database operations for:
- Status readings (temperature, heat mode, pump states, etc.)
- Connection event tracking (connected/disconnected/error)
- Schedule management (heat mode, temperature, smart temp schedules)
- Schedule execution history
"""

import json
import os
import sqlite3
import threading
from datetime import datetime, timedelta
from pathlib import Path
from typing import Optional

from spa_control import SpaStatus


class SpaDatabase:
    """
    SQLite database layer for spa data persistence.

    Thread-safe with connection-per-thread pattern.
    Creates tables automatically on first use.
    """

    def __init__(self, db_path: str = "data/spadata/spa.db"):
        """
        Initialize database connection.

        Args:
            db_path: Path to SQLite database file (created if doesn't exist)
        """
        self.db_path = db_path
        self._local = threading.local()

        # Ensure directory exists
        Path(db_path).parent.mkdir(parents=True, exist_ok=True)

        # Initialize schema
        self._init_schema()

    def _get_connection(self) -> sqlite3.Connection:
        """Get thread-local database connection."""
        if not hasattr(self._local, "connection") or self._local.connection is None:
            self._local.connection = sqlite3.connect(
                self.db_path,
                check_same_thread=False,
                timeout=30.0
            )
            self._local.connection.row_factory = sqlite3.Row
            # Enable foreign keys
            self._local.connection.execute("PRAGMA foreign_keys = ON")
        return self._local.connection

    def _init_schema(self):
        """Create database tables if they don't exist."""
        conn = self._get_connection()
        cursor = conn.cursor()

        # Spa readings table - stores status snapshots
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS spa_readings (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                timestamp TEXT NOT NULL,
                current_temp REAL,
                target_temp REAL NOT NULL,
                temp_unit TEXT NOT NULL,
                heat_mode TEXT NOT NULL,
                temp_range TEXT NOT NULL,
                heating INTEGER NOT NULL,
                heat_state TEXT NOT NULL,
                pumps_json TEXT,
                lights_json TEXT,
                blower_state INTEGER,
                circ_pump_running INTEGER,
                model TEXT,
                software_version TEXT
            )
        """)

        # Index for efficient time-range queries
        cursor.execute("""
            CREATE INDEX IF NOT EXISTS idx_readings_timestamp
            ON spa_readings(timestamp)
        """)

        # Connection events table
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS connection_events (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                timestamp TEXT NOT NULL,
                event_type TEXT NOT NULL,
                error_message TEXT
            )
        """)

        cursor.execute("""
            CREATE INDEX IF NOT EXISTS idx_connection_timestamp
            ON connection_events(timestamp)
        """)

        # Schedules table
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS schedules (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                enabled INTEGER NOT NULL DEFAULT 1,
                schedule_type TEXT NOT NULL,
                action_value TEXT NOT NULL,
                target_time TEXT NOT NULL,
                recurrence_type TEXT NOT NULL,
                days_of_week TEXT,
                next_run TEXT,
                smart_start_offset_minutes INTEGER,
                created_at TEXT NOT NULL,
                updated_at TEXT NOT NULL
            )
        """)

        # Schedule executions table
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS schedule_executions (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                schedule_id INTEGER NOT NULL,
                scheduled_time TEXT NOT NULL,
                executed_at TEXT,
                status TEXT NOT NULL,
                result_message TEXT,
                starting_temp REAL,
                FOREIGN KEY (schedule_id) REFERENCES schedules(id) ON DELETE CASCADE
            )
        """)

        cursor.execute("""
            CREATE INDEX IF NOT EXISTS idx_executions_schedule
            ON schedule_executions(schedule_id)
        """)

        cursor.execute("""
            CREATE INDEX IF NOT EXISTS idx_executions_scheduled
            ON schedule_executions(scheduled_time)
        """)

        # Command queue table - stores commands when spa is offline
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS command_queue (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                command_type TEXT NOT NULL,
                endpoint TEXT NOT NULL,
                data_json TEXT,
                source TEXT NOT NULL,
                created_at TEXT NOT NULL,
                status TEXT NOT NULL DEFAULT 'pending',
                executed_at TEXT,
                error_message TEXT
            )
        """)

        cursor.execute("""
            CREATE INDEX IF NOT EXISTS idx_command_queue_status
            ON command_queue(status)
        """)

        # Settings table - stores user preferences
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS settings (
                key TEXT PRIMARY KEY,
                value TEXT NOT NULL,
                updated_at TEXT NOT NULL
            )
        """)

        # Initialize default temperature preferences if not set
        cursor.execute("""
            INSERT OR IGNORE INTO settings (key, value, updated_at)
            VALUES ('high_temp_preference', '104', datetime('now'))
        """)
        cursor.execute("""
            INSERT OR IGNORE INTO settings (key, value, updated_at)
            VALUES ('low_temp_preference', '70', datetime('now'))
        """)

        conn.commit()

    # =========================================================================
    # Reading Operations
    # =========================================================================

    def save_reading(self, status: SpaStatus) -> int:
        """
        Save a spa status reading to the database.

        Args:
            status: SpaStatus object from spa_control

        Returns:
            Row ID of inserted reading
        """
        conn = self._get_connection()
        cursor = conn.cursor()

        # Extract blower state
        blower_state = None
        if status.blower:
            blower_state = status.blower.get("state")

        # Extract circ pump state
        circ_pump_running = None
        if status.circ_pump:
            circ_pump_running = 1 if status.circ_pump.get("running") else 0

        cursor.execute("""
            INSERT INTO spa_readings (
                timestamp, current_temp, target_temp, temp_unit,
                heat_mode, temp_range, heating, heat_state,
                pumps_json, lights_json, blower_state, circ_pump_running,
                model, software_version
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        """, (
            datetime.now().isoformat(),
            status.current_temp,
            status.target_temp,
            status.temp_unit,
            status.heat_mode,
            status.temp_range,
            1 if status.heating else 0,
            status.heat_state,
            json.dumps(status.pumps),
            json.dumps(status.lights),
            blower_state,
            circ_pump_running,
            status.model,
            status.software_version,
        ))

        conn.commit()
        return cursor.lastrowid

    def get_latest_reading(self) -> Optional[dict]:
        """
        Get the most recent spa status reading.

        Returns:
            Dictionary with 'status' (dict) and 'timestamp' (str), or None
        """
        conn = self._get_connection()
        cursor = conn.cursor()

        cursor.execute("""
            SELECT * FROM spa_readings
            ORDER BY timestamp DESC
            LIMIT 1
        """)

        row = cursor.fetchone()
        if row is None:
            return None

        return self._row_to_reading(row)

    def get_readings(
        self,
        since: datetime,
        until: Optional[datetime] = None,
        limit: int = 100
    ) -> list[dict]:
        """
        Get status readings within a time range.

        Args:
            since: Start of time range
            until: End of time range (default: now)
            limit: Maximum number of readings to return

        Returns:
            List of reading dictionaries, newest first
        """
        conn = self._get_connection()
        cursor = conn.cursor()

        if until is None:
            until = datetime.now()

        cursor.execute("""
            SELECT * FROM spa_readings
            WHERE timestamp >= ? AND timestamp <= ?
            ORDER BY timestamp DESC
            LIMIT ?
        """, (since.isoformat(), until.isoformat(), limit))

        return [self._row_to_reading(row) for row in cursor.fetchall()]

    def _row_to_reading(self, row: sqlite3.Row) -> dict:
        """Convert database row to reading dictionary."""
        status = {
            "current_temp": row["current_temp"],
            "target_temp": row["target_temp"],
            "temp_unit": row["temp_unit"],
            "heat_mode": row["heat_mode"],
            "temp_range": row["temp_range"],
            "heating": bool(row["heating"]),
            "heat_state": row["heat_state"],
            "pumps": json.loads(row["pumps_json"]) if row["pumps_json"] else [],
            "lights": json.loads(row["lights_json"]) if row["lights_json"] else [],
            "blower": {"state": row["blower_state"]} if row["blower_state"] is not None else None,
            "circ_pump": {"running": bool(row["circ_pump_running"])} if row["circ_pump_running"] is not None else None,
            "model": row["model"],
            "software_version": row["software_version"],
            "connected": True,  # If we have a reading, it was connected
        }

        return {
            "status": status,
            "timestamp": row["timestamp"],
            "id": row["id"],
        }

    def cleanup_old_readings(self, days: int = 30) -> int:
        """
        Delete readings older than specified days.

        Args:
            days: Number of days to retain

        Returns:
            Number of rows deleted
        """
        conn = self._get_connection()
        cursor = conn.cursor()

        cutoff = datetime.now() - timedelta(days=days)
        cursor.execute(
            "DELETE FROM spa_readings WHERE timestamp < ?",
            (cutoff.isoformat(),)
        )

        deleted = cursor.rowcount
        conn.commit()
        return deleted

    # =========================================================================
    # Connection Event Operations
    # =========================================================================

    def record_connection_event(self, event_type: str, error: Optional[str] = None):
        """
        Record a connection state change.

        Args:
            event_type: 'connected', 'disconnected', or 'error'
            error: Error message (for 'disconnected' or 'error' events)
        """
        conn = self._get_connection()
        cursor = conn.cursor()

        cursor.execute("""
            INSERT INTO connection_events (timestamp, event_type, error_message)
            VALUES (?, ?, ?)
        """, (datetime.now().isoformat(), event_type, error))

        conn.commit()

    def get_connection_history(self, limit: int = 50) -> list[dict]:
        """
        Get recent connection events.

        Args:
            limit: Maximum number of events to return

        Returns:
            List of event dictionaries, newest first
        """
        conn = self._get_connection()
        cursor = conn.cursor()

        cursor.execute("""
            SELECT * FROM connection_events
            ORDER BY timestamp DESC
            LIMIT ?
        """, (limit,))

        return [
            {
                "id": row["id"],
                "timestamp": row["timestamp"],
                "event_type": row["event_type"],
                "error_message": row["error_message"],
            }
            for row in cursor.fetchall()
        ]

    def is_connected(self) -> tuple[bool, Optional[datetime]]:
        """
        Check current connection state based on last event.

        Returns:
            Tuple of (is_connected, last_event_timestamp)
        """
        conn = self._get_connection()
        cursor = conn.cursor()

        cursor.execute("""
            SELECT * FROM connection_events
            ORDER BY timestamp DESC
            LIMIT 1
        """)

        row = cursor.fetchone()
        if row is None:
            return False, None

        is_connected = row["event_type"] == "connected"
        timestamp = datetime.fromisoformat(row["timestamp"])
        return is_connected, timestamp

    # =========================================================================
    # Schedule Operations
    # =========================================================================

    def create_schedule(self, schedule: dict) -> int:
        """
        Create a new schedule.

        Args:
            schedule: Dictionary with schedule configuration:
                - name: Display name
                - schedule_type: 'heat_mode', 'temperature', or 'smart_temp'
                - action_value: JSON string or dict with action details
                - target_time: "HH:MM" format
                - recurrence_type: 'once', 'daily', or 'weekly'
                - days_of_week: List of day numbers (0=Mon) for weekly
                - enabled: Boolean (default True)

        Returns:
            ID of created schedule
        """
        conn = self._get_connection()
        cursor = conn.cursor()

        now = datetime.now().isoformat()

        # Serialize action_value if it's a dict
        action_value = schedule["action_value"]
        if isinstance(action_value, dict):
            action_value = json.dumps(action_value)

        # Serialize days_of_week if provided
        days_of_week = schedule.get("days_of_week")
        if days_of_week and isinstance(days_of_week, list):
            days_of_week = json.dumps(days_of_week)

        # Calculate initial next_run
        next_run = self._calculate_next_run(
            schedule["target_time"],
            schedule["recurrence_type"],
            days_of_week,
        )

        cursor.execute("""
            INSERT INTO schedules (
                name, enabled, schedule_type, action_value,
                target_time, recurrence_type, days_of_week,
                next_run, smart_start_offset_minutes,
                created_at, updated_at
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        """, (
            schedule["name"],
            1 if schedule.get("enabled", True) else 0,
            schedule["schedule_type"],
            action_value,
            schedule["target_time"],
            schedule["recurrence_type"],
            days_of_week,
            next_run.isoformat() if next_run else None,
            schedule.get("smart_start_offset_minutes"),
            now,
            now,
        ))

        conn.commit()
        return cursor.lastrowid

    def get_schedules(self, enabled_only: bool = False) -> list[dict]:
        """
        Get all schedules.

        Args:
            enabled_only: If True, only return enabled schedules

        Returns:
            List of schedule dictionaries
        """
        conn = self._get_connection()
        cursor = conn.cursor()

        if enabled_only:
            cursor.execute(
                "SELECT * FROM schedules WHERE enabled = 1 ORDER BY target_time"
            )
        else:
            cursor.execute("SELECT * FROM schedules ORDER BY target_time")

        return [self._row_to_schedule(row) for row in cursor.fetchall()]

    def get_schedule(self, schedule_id: int) -> Optional[dict]:
        """Get a single schedule by ID."""
        conn = self._get_connection()
        cursor = conn.cursor()

        cursor.execute("SELECT * FROM schedules WHERE id = ?", (schedule_id,))
        row = cursor.fetchone()

        if row is None:
            return None
        return self._row_to_schedule(row)

    def get_due_schedules(self) -> list[dict]:
        """
        Get schedules that are due to execute (next_run <= now).

        Returns:
            List of schedule dictionaries ready to execute
        """
        conn = self._get_connection()
        cursor = conn.cursor()

        now = datetime.now().isoformat()
        cursor.execute("""
            SELECT * FROM schedules
            WHERE enabled = 1 AND next_run IS NOT NULL AND next_run <= ?
            ORDER BY next_run
        """, (now,))

        return [self._row_to_schedule(row) for row in cursor.fetchall()]

    def update_schedule(self, schedule_id: int, updates: dict):
        """
        Update an existing schedule.

        Args:
            schedule_id: ID of schedule to update
            updates: Dictionary of fields to update
        """
        conn = self._get_connection()
        cursor = conn.cursor()

        # Build update query dynamically
        allowed_fields = {
            "name", "enabled", "schedule_type", "action_value",
            "target_time", "recurrence_type", "days_of_week",
            "next_run", "smart_start_offset_minutes"
        }

        set_clauses = []
        values = []

        for field, value in updates.items():
            if field not in allowed_fields:
                continue

            # Serialize if needed
            if field == "action_value" and isinstance(value, dict):
                value = json.dumps(value)
            elif field == "days_of_week" and isinstance(value, list):
                value = json.dumps(value)
            elif field == "enabled":
                value = 1 if value else 0

            set_clauses.append(f"{field} = ?")
            values.append(value)

        if not set_clauses:
            return

        # Add updated_at
        set_clauses.append("updated_at = ?")
        values.append(datetime.now().isoformat())

        # Add schedule_id for WHERE clause
        values.append(schedule_id)

        cursor.execute(f"""
            UPDATE schedules
            SET {', '.join(set_clauses)}
            WHERE id = ?
        """, values)

        # Recalculate next_run if timing changed
        if any(f in updates for f in ["target_time", "recurrence_type", "days_of_week"]):
            schedule = self.get_schedule(schedule_id)
            if schedule:
                next_run = self._calculate_next_run(
                    schedule["target_time"],
                    schedule["recurrence_type"],
                    schedule.get("days_of_week"),
                )
                cursor.execute(
                    "UPDATE schedules SET next_run = ? WHERE id = ?",
                    (next_run.isoformat() if next_run else None, schedule_id)
                )

        conn.commit()

    def delete_schedule(self, schedule_id: int):
        """Delete a schedule and its execution history."""
        conn = self._get_connection()
        cursor = conn.cursor()

        # Cascade delete will handle executions due to foreign key
        cursor.execute("DELETE FROM schedules WHERE id = ?", (schedule_id,))
        conn.commit()

    def _row_to_schedule(self, row: sqlite3.Row) -> dict:
        """Convert database row to schedule dictionary."""
        days_of_week = None
        if row["days_of_week"]:
            days_of_week = json.loads(row["days_of_week"])

        action_value = row["action_value"]
        try:
            action_value = json.loads(action_value)
        except (json.JSONDecodeError, TypeError):
            pass

        return {
            "id": row["id"],
            "name": row["name"],
            "enabled": bool(row["enabled"]),
            "schedule_type": row["schedule_type"],
            "action_value": action_value,
            "target_time": row["target_time"],
            "recurrence_type": row["recurrence_type"],
            "days_of_week": days_of_week,
            "next_run": row["next_run"],
            "smart_start_offset_minutes": row["smart_start_offset_minutes"],
            "created_at": row["created_at"],
            "updated_at": row["updated_at"],
        }

    def _calculate_next_run(
        self,
        target_time: str,
        recurrence_type: str,
        days_of_week: Optional[str] = None,
    ) -> Optional[datetime]:
        """
        Calculate the next run time for a schedule.

        Args:
            target_time: "HH:MM" format
            recurrence_type: 'once', 'daily', or 'weekly'
            days_of_week: JSON string of day numbers for weekly

        Returns:
            datetime of next scheduled run
        """
        if recurrence_type == "once":
            # For one-time schedules, return None after execution
            # Initial creation should set to today/tomorrow at target_time
            pass

        try:
            hour, minute = map(int, target_time.split(":"))
        except ValueError:
            return None

        now = datetime.now()
        today_target = now.replace(hour=hour, minute=minute, second=0, microsecond=0)

        if recurrence_type == "once":
            # If target time today hasn't passed, use today; otherwise tomorrow
            if today_target > now:
                return today_target
            return today_target + timedelta(days=1)

        elif recurrence_type == "daily":
            if today_target > now:
                return today_target
            return today_target + timedelta(days=1)

        elif recurrence_type == "weekly":
            if not days_of_week:
                return None

            try:
                if isinstance(days_of_week, str):
                    days = json.loads(days_of_week)
                else:
                    days = days_of_week
            except (json.JSONDecodeError, TypeError):
                return None

            if not days:
                return None

            # Python weekday: Monday=0, Sunday=6
            current_weekday = now.weekday()

            # Find next matching day
            for i in range(7):
                check_day = (current_weekday + i) % 7
                if check_day in days:
                    candidate = today_target + timedelta(days=i)
                    if candidate > now:
                        return candidate

            # Wrap to next week
            for day in sorted(days):
                days_until = (day - current_weekday) % 7
                if days_until == 0:
                    days_until = 7
                return today_target + timedelta(days=days_until)

        return None

    def advance_schedule(self, schedule_id: int):
        """
        Calculate and set next_run after a schedule executes.

        Args:
            schedule_id: ID of executed schedule
        """
        schedule = self.get_schedule(schedule_id)
        if not schedule:
            return

        if schedule["recurrence_type"] == "once":
            # One-time schedule - disable it
            self.update_schedule(schedule_id, {"enabled": False, "next_run": None})
        else:
            # Recurring - calculate next run
            next_run = self._calculate_next_run(
                schedule["target_time"],
                schedule["recurrence_type"],
                schedule.get("days_of_week"),
            )
            self.update_schedule(
                schedule_id,
                {"next_run": next_run.isoformat() if next_run else None}
            )

    # =========================================================================
    # Schedule Execution History
    # =========================================================================

    def record_execution(
        self,
        schedule_id: int,
        status: str,
        message: Optional[str] = None,
        starting_temp: Optional[float] = None
    ) -> int:
        """
        Record a schedule execution attempt.

        Args:
            schedule_id: ID of the executed schedule
            status: 'pending', 'executed', 'failed', or 'skipped'
            message: Result message or error details
            starting_temp: Temperature when execution started (for smart_temp)

        Returns:
            ID of created execution record
        """
        conn = self._get_connection()
        cursor = conn.cursor()

        now = datetime.now().isoformat()

        cursor.execute("""
            INSERT INTO schedule_executions (
                schedule_id, scheduled_time, executed_at, status,
                result_message, starting_temp
            ) VALUES (?, ?, ?, ?, ?, ?)
        """, (
            schedule_id,
            now,  # scheduled_time
            now if status in ("executed", "failed") else None,
            status,
            message,
            starting_temp,
        ))

        conn.commit()
        return cursor.lastrowid

    def get_execution_history(
        self,
        schedule_id: Optional[int] = None,
        limit: int = 50
    ) -> list[dict]:
        """
        Get schedule execution history.

        Args:
            schedule_id: If provided, filter to specific schedule
            limit: Maximum number of records to return

        Returns:
            List of execution records, newest first
        """
        conn = self._get_connection()
        cursor = conn.cursor()

        if schedule_id:
            cursor.execute("""
                SELECT e.*, s.name as schedule_name
                FROM schedule_executions e
                JOIN schedules s ON e.schedule_id = s.id
                WHERE e.schedule_id = ?
                ORDER BY e.scheduled_time DESC
                LIMIT ?
            """, (schedule_id, limit))
        else:
            cursor.execute("""
                SELECT e.*, s.name as schedule_name
                FROM schedule_executions e
                JOIN schedules s ON e.schedule_id = s.id
                ORDER BY e.scheduled_time DESC
                LIMIT ?
            """, (limit,))

        return [
            {
                "id": row["id"],
                "schedule_id": row["schedule_id"],
                "schedule_name": row["schedule_name"],
                "scheduled_time": row["scheduled_time"],
                "executed_at": row["executed_at"],
                "status": row["status"],
                "result_message": row["result_message"],
                "starting_temp": row["starting_temp"],
            }
            for row in cursor.fetchall()
        ]

    def cleanup_old_executions(self, days: int = 90) -> int:
        """
        Delete execution history older than specified days.

        Args:
            days: Number of days to retain

        Returns:
            Number of rows deleted
        """
        conn = self._get_connection()
        cursor = conn.cursor()

        cutoff = datetime.now() - timedelta(days=days)
        cursor.execute(
            "DELETE FROM schedule_executions WHERE scheduled_time < ?",
            (cutoff.isoformat(),)
        )

        deleted = cursor.rowcount
        conn.commit()
        return deleted

    # =========================================================================
    # Command Queue Operations
    # =========================================================================

    def queue_command(
        self,
        command_type: str,
        endpoint: str,
        data: dict = None,
        source: str = "user"
    ) -> int:
        """
        Add a command to the queue for later execution.

        Args:
            command_type: Type of command (temperature, heat_mode, temp_range, pump, light, etc.)
            endpoint: API endpoint the command targets
            data: Command data/parameters
            source: 'user' for manual commands, 'schedule' for scheduled commands

        Returns:
            Row ID of queued command
        """
        conn = self._get_connection()
        cursor = conn.cursor()

        cursor.execute("""
            INSERT INTO command_queue (command_type, endpoint, data_json, source, created_at, status)
            VALUES (?, ?, ?, ?, ?, 'pending')
        """, (
            command_type,
            endpoint,
            json.dumps(data) if data else None,
            source,
            datetime.now().isoformat(),
        ))

        conn.commit()
        return cursor.lastrowid

    def get_pending_commands(self) -> list[dict]:
        """Get all pending commands in queue order."""
        conn = self._get_connection()
        cursor = conn.cursor()

        cursor.execute("""
            SELECT id, command_type, endpoint, data_json, source, created_at
            FROM command_queue
            WHERE status = 'pending'
            ORDER BY created_at ASC
        """)

        commands = []
        for row in cursor.fetchall():
            commands.append({
                "id": row["id"],
                "command_type": row["command_type"],
                "endpoint": row["endpoint"],
                "data": json.loads(row["data_json"]) if row["data_json"] else None,
                "source": row["source"],
                "created_at": row["created_at"],
            })
        return commands

    def mark_command_executed(self, command_id: int):
        """Mark a queued command as executed."""
        conn = self._get_connection()
        cursor = conn.cursor()

        cursor.execute("""
            UPDATE command_queue
            SET status = 'executed', executed_at = ?
            WHERE id = ?
        """, (datetime.now().isoformat(), command_id))

        conn.commit()

    def mark_command_failed(self, command_id: int, error: str):
        """Mark a queued command as failed."""
        conn = self._get_connection()
        cursor = conn.cursor()

        cursor.execute("""
            UPDATE command_queue
            SET status = 'failed', executed_at = ?, error_message = ?
            WHERE id = ?
        """, (datetime.now().isoformat(), error, command_id))

        conn.commit()

    def cancel_command(self, command_id: int) -> bool:
        """Cancel a pending command. Returns True if cancelled, False if not found or not pending."""
        conn = self._get_connection()
        cursor = conn.cursor()

        cursor.execute("""
            UPDATE command_queue
            SET status = 'cancelled', executed_at = ?
            WHERE id = ? AND status = 'pending'
        """, (datetime.now().isoformat(), command_id))

        conn.commit()
        return cursor.rowcount > 0

    def get_command_history(self, limit: int = 10) -> list[dict]:
        """Get recent command history for audit log."""
        conn = self._get_connection()
        cursor = conn.cursor()

        cursor.execute("""
            SELECT id, command_type, endpoint, data_json, source, created_at,
                   status, executed_at, error_message
            FROM command_queue
            ORDER BY created_at DESC
            LIMIT ?
        """, (limit,))

        commands = []
        for row in cursor.fetchall():
            commands.append({
                "id": row["id"],
                "command_type": row["command_type"],
                "endpoint": row["endpoint"],
                "data": json.loads(row["data_json"]) if row["data_json"] else None,
                "source": row["source"],
                "created_at": row["created_at"],
                "status": row["status"],
                "executed_at": row["executed_at"],
                "error_message": row["error_message"],
            })
        return commands

    def cleanup_old_commands(self, days: int = 7) -> int:
        """Delete command history older than specified days."""
        conn = self._get_connection()
        cursor = conn.cursor()

        cutoff = datetime.now() - timedelta(days=days)
        cursor.execute(
            "DELETE FROM command_queue WHERE created_at < ?",
            (cutoff.isoformat(),)
        )

        deleted = cursor.rowcount
        conn.commit()
        return deleted

    # =========================================================================
    # Settings Operations
    # =========================================================================

    def get_setting(self, key: str, default: str = None) -> Optional[str]:
        """Get a setting value by key."""
        conn = self._get_connection()
        cursor = conn.cursor()

        cursor.execute("SELECT value FROM settings WHERE key = ?", (key,))
        row = cursor.fetchone()
        return row["value"] if row else default

    def set_setting(self, key: str, value: str):
        """Set a setting value."""
        conn = self._get_connection()
        cursor = conn.cursor()

        cursor.execute("""
            INSERT INTO settings (key, value, updated_at)
            VALUES (?, ?, datetime('now'))
            ON CONFLICT(key) DO UPDATE SET value = ?, updated_at = datetime('now')
        """, (key, value, value))

        conn.commit()

    def get_temp_preferences(self) -> dict:
        """Get temperature preferences for high/low modes."""
        return {
            "high_temp": float(self.get_setting("high_temp_preference", "104")),
            "low_temp": float(self.get_setting("low_temp_preference", "70")),
        }

    def set_temp_preference(self, mode: str, temp: float):
        """Set temperature preference for a mode (high or low)."""
        if mode not in ("high", "low"):
            raise ValueError("Mode must be 'high' or 'low'")
        key = f"{mode}_temp_preference"
        self.set_setting(key, str(temp))

    def close(self):
        """Close database connection for current thread."""
        if hasattr(self._local, "connection") and self._local.connection:
            self._local.connection.close()
            self._local.connection = None