docs: correct rollback instructions for the initial release

docs: fix release documentation findings (RLS-001..003)
2026-09-13 19:48:12 -04:00 · 2026-09-13 19:46:34 -04:00
14 changed files with 74 additions and 4836 deletions
@@ -4,7 +4,7 @@ Home Assistant custom integration for managing chores, custody schedules, and ho
 ## Overview
-NestQuest helps households coordinate daily life, chore tracking, custody schedules, and shared tasks directly within Home Assistant. It provides sensors, calendar events, and service calls designed for busy households and modern co-parenting setups.
+NestQuest helps households coordinate daily life, chore tracking, custody schedules, and shared tasks directly within Home Assistant. Version 0.1.0 is the integration foundation: a configuration scaffold providing the package and manifest, shared constants, a single-instance config flow, setup/unload lifecycle handling with an options-reload listener, an options flow (horizon days, day rollover time, panel idle timeout), SQLite storage-path resolution, and HACS metadata. Sensors, calendar events, and service calls are forthcoming in later releases and are not yet part of the integration.
 ## Installation
@@ -0,0 +1,45 @@
 # NestQuest Release Notes
 ## Version 0.1.0 — 2026-09-13
 ### Scope
 First release of NestQuest, a Home Assistant custom integration for managing chores, custody schedules, and house coordination. This release ships the integration foundation/configuration scaffold only (Feature 01):
 - Integration package and manifest, plus HACS metadata (`hacs.json`).
 - Shared constants module.
 - Single-instance config flow for initial setup.
 - Setup/unload lifecycle handling with an options-reload listener.
 - Options flow for configuring `horizon_days`, `day_rollover_time`, and `panel_idle_timeout`.
 - SQLite storage path resolver — path resolution only; the database itself is not created in this release.
 - Test harness (`pytest` via `uv`).
 ### Requirements
 - Minimum Home Assistant version: **2024.6.0**.
 ### Known Limitations
 - No entities, services, or calendar platforms yet — the integration does not expose sensors, calendar events, or service calls in this release.
 - Single-instance only — one NestQuest config entry is permitted per Home Assistant instance.
 - The integration does not yet create its database file; storage is limited to resolved configuration paths.
 ### Rollback
 v0.1.0 is the first release — there is no previous NestQuest version to downgrade to, so rollback means complete removal. This release performs no data or schema migrations.
 **HACS installs:**
 1. Remove the NestQuest integration via HACS (HACS > Integrations > NestQuest > Remove).
 2. Restart Home Assistant.
 3. Remove the integration in Home Assistant (Settings > Devices & Services > NestQuest > Delete), then restart Home Assistant again.
 **Manual installs:**
 1. Delete the `custom_components/nestquest/` directory from your Home Assistant configuration directory.
 2. Restart Home Assistant.
 3. Remove the integration in Home Assistant (Settings > Devices & Services > NestQuest > Delete), then restart Home Assistant again.
 **Data:** the integration does not yet create its own database file, so there is no application data. However, Home Assistant persists the config entry and its selected options — removing the integration deletes that configuration, and the option values (`horizon_days`, `day_rollover_time`, `panel_idle_timeout`) will be lost on removal.
 **Repository operators:** instead of uninstalling, you may git-revert the release merge on `main`, which removes the integration files entirely. End users should use the uninstall path above.
@@ -10,9 +10,6 @@ from homeassistant.config_entries import ConfigEntry
 from homeassistant.core import HomeAssistant
 from .const import DOMAIN, LOGGER
 from .db import NestQuestDatabase
 from .migrations import apply_migrations
 from .store import async_get_db_path
 _LOGGER = LOGGER
@@ -23,7 +20,6 @@ class NestQuestRuntimeData:
    entry_id: str
    options: dict[str, Any]
    database: NestQuestDatabase
    remove_update_listener: Callable[[], Any]
@@ -35,14 +31,6 @@ async def async_setup(hass: HomeAssistant, config: dict[str, Any]) -> bool:
 async def async_setup_entry(hass: HomeAssistant, entry: ConfigEntry) -> bool:
    """Set up NestQuest from a config entry."""
    hass.data.setdefault(DOMAIN, {})
    existing = hass.data[DOMAIN].get(entry.entry_id)
    if existing is not None:
        db = getattr(existing, "database", None)
        if db is None:
            db = await NestQuestDatabase(hass).open(await async_get_db_path(hass))
            existing.database = db
        entry.runtime_data = existing
        return True
    async def _async_update_listener(
        listener_hass: HomeAssistant, listener_entry: ConfigEntry
@@ -50,18 +38,10 @@ async def async_setup_entry(hass: HomeAssistant, entry: ConfigEntry) -> bool:
        """Reload the entry when its options change."""
        await listener_hass.config_entries.async_reload(listener_entry.entry_id)
    database = await NestQuestDatabase(hass).open(await async_get_db_path(hass))
    try:
        await apply_migrations(database)
        remove_update_listener = entry.add_update_listener(_async_update_listener)
    except BaseException:
        await database.close()
        raise
    runtime_data = NestQuestRuntimeData(
        entry_id=entry.entry_id,
        options=dict(entry.options),
-        database=database,
+        remove_update_listener=entry.add_update_listener(_async_update_listener),
        remove_update_listener=remove_update_listener,
    )
    entry.runtime_data = runtime_data
    hass.data[DOMAIN][entry.entry_id] = runtime_data
@@ -78,22 +58,10 @@ async def async_unload_entry(hass: HomeAssistant, entry: ConfigEntry) -> bool:
        runtime_data = getattr(entry, "runtime_data", None)
    if runtime_data is not None:
        remove_update_listener = getattr(runtime_data, "remove_update_listener", None)
-        unload_error: BaseException | None = None
+        if remove_update_listener is not None:
-        try:
+            result = remove_update_listener()
-            if remove_update_listener is not None:
+            if inspect.isawaitable(result):
-                result = remove_update_listener()
+                await result
                if inspect.isawaitable(result):
                    await result
        except BaseException as err:
            unload_error = err
        finally:
            database = getattr(runtime_data, "database", None)
            if database is not None:
                await database.close()
        if unload_error is not None:
            if getattr(entry, "runtime_data", None) is not None:
                entry.runtime_data = None
            raise unload_error
    if getattr(entry, "runtime_data", None) is not None:
        entry.runtime_data = None
    return True
@@ -1,422 +0,0 @@
 """Async SQLite connection wrapper for the NestQuest integration."""
 from __future__ import annotations
 import asyncio
 import sqlite3
 from collections.abc import AsyncIterator, Awaitable, Sequence
 from contextlib import asynccontextmanager
 from dataclasses import dataclass
 from pathlib import Path
 from typing import Any
 from homeassistant.core import HomeAssistant
@dataclass(frozen=True)
 class ExecutionResult:
    """Plain-data outcome of one statement.
    ``rowcount`` and ``lastrowid`` mirror the corresponding
    :class:`sqlite3.Cursor` attributes; both are captured on the executor
    thread so no sqlite3 object ever crosses back to the event loop.
    """
    rowcount: int
    lastrowid: int | None
 class NestQuestDatabase:
    """Async wrapper around a single SQLite connection.
    Every sqlite3 call is delegated to the Home Assistant executor through
    ``hass.async_add_executor_job`` so the event loop is never blocked, and
    only plain Python data (row tuples and :class:`ExecutionResult`) is
    returned to the event loop.
    Every operation is serialized through one asyncio.Lock per wrapper.  A
    ``transaction()`` holds that lock for its whole BEGIN..COMMIT span, so
    interleaved coroutines cannot corrupt the transaction and concurrent
    BEGINs are impossible; statements issued while a transaction is open
    queue until it completes.  Concurrent transactions on one wrapper are
    not supported: a second ``transaction()`` entered while one is active
    raises :class:`RuntimeError`.  ``close()`` serializes on the same lock
    and is idempotent.
    Every connection-affecting executor job (connect, pragmas, statements,
    transaction lifecycle and close) is awaited through the settlement
    helper :meth:`_await_settled`: if the awaiting task is cancelled while
    a job is in flight, the job still settles on the worker thread before
    the cancellation propagates, so the wrapper lock is never released
    while the executor thread is mid-job.
    """
    def __init__(self, hass: HomeAssistant) -> None:
        """Initialize the wrapper around a Home Assistant instance."""
        self._hass = hass
        self._conn: sqlite3.Connection | None = None
        self._rowcount: int = -1
        self._lastrowid: int | None = None
        self._lock: asyncio.Lock | None = None
        self._lock_loop: asyncio.AbstractEventLoop | None = None
        self._in_transaction = False
        self._tx_task: asyncio.Task | None = None
    def _get_lock(self) -> asyncio.Lock:
        """Return the per-wrapper lock, (re)bound to the running loop.
        HA drives every wrapper call from one event loop, so the lock is
        created once and reused; re-binding only when the running loop
        differs keeps the wrapper usable from plain synchronous drivers
        that run each call on a fresh loop.
        """
        loop = asyncio.get_running_loop()
        if self._lock is None or self._lock_loop is not loop:
            self._lock = asyncio.Lock()
            self._lock_loop = loop
        return self._lock
    @property
    def connected(self) -> bool:
        """Return True while the underlying connection is open."""
        return self._conn is not None
    @property
    def rowcount(self) -> int:
        """Row count of the most recent statement, or -1 when unset."""
        return self._rowcount
    @property
    def lastrowid(self) -> int | None:
        """Row id of the most recent insert, or None when unset."""
        return self._lastrowid
    @asynccontextmanager
    async def _serialized(self) -> AsyncIterator[None]:
        """Hold the wrapper lock unless the caller owns the active transaction.
        A transaction holds the lock for its whole BEGIN..COMMIT span;
        statements issued by the transaction's own task are already
        serialized by it and must not re-acquire the non-reentrant lock.
        Any other task queues on the lock until the transaction ends.
        """
        if (
            self._in_transaction
            and self._tx_task is not None
            and asyncio.current_task() is self._tx_task
        ):
            yield
            return
        async with self._get_lock():
            yield
    async def open(self, path: Path | str) -> NestQuestDatabase:
        """Open the SQLite file with WAL journal mode and foreign keys on.
        Cancellation-safe: if this call is cancelled while the connect job
        is in flight, the job still settles (see :meth:`_await_settled`).
        A connection the settled job created is never adopted by the
        cancelled call; it is closed through a settled executor job instead
        of being abandoned to garbage collection, and the cancellation
        propagates afterwards, so a follow-up ``open()`` starts from a
        clean state.
        """
        if self._conn is not None:
            return self
        async with self._get_lock():
            if self._conn is not None:
                return self
            def _open() -> sqlite3.Connection:
                conn = sqlite3.connect(
                    path, check_same_thread=False, isolation_level=None
                )
                try:
                    conn.execute("PRAGMA journal_mode=WAL")
                    conn.execute("PRAGMA foreign_keys=ON")
                except BaseException:
                    conn.close()
                    raise
                return conn
            settled, cancelled = await self._await_settled_state(
                self._hass.async_add_executor_job(_open)
            )
            if cancelled:
                if isinstance(settled, sqlite3.Connection):
                    def _close_abandoned() -> None:
                        settled.close()
                    await self._await_settled(
                        self._hass.async_add_executor_job(_close_abandoned)
                    )
                raise asyncio.CancelledError()
            self._conn = settled
        return self
    async def execute(
        self, sql: str, parameters: Sequence[Any] = ()
    ) -> ExecutionResult:
        """Run one statement in the executor and return its plain result."""
        async with self._serialized():
            conn = self._require_conn()
            def _execute() -> ExecutionResult:
                cursor = conn.execute(sql, parameters)
                return ExecutionResult(cursor.rowcount, cursor.lastrowid)
            result = await self._await_settled(
                self._hass.async_add_executor_job(_execute)
            )
            self._rowcount = result.rowcount
            self._lastrowid = result.lastrowid
            return result
    async def execute_many(
        self, sql: str, parameters: Sequence[Sequence[Any]]
    ) -> ExecutionResult:
        """Run executemany in the executor and return its plain result."""
        async with self._serialized():
            conn = self._require_conn()
            def _execute_many() -> ExecutionResult:
                cursor = conn.executemany(sql, parameters)
                return ExecutionResult(cursor.rowcount, cursor.lastrowid)
            result = await self._await_settled(
                self._hass.async_add_executor_job(_execute_many)
            )
            self._rowcount = result.rowcount
            self._lastrowid = result.lastrowid
            return result
    async def fetch_one(
        self, sql: str, parameters: Sequence[Any] = ()
    ) -> tuple | None:
        """Run a query in the executor and return its first row, or None."""
        async with self._serialized():
            conn = self._require_conn()
            def _fetch_one() -> tuple[tuple | None, int, int | None]:
                cursor = conn.execute(sql, parameters)
                return cursor.fetchone(), cursor.rowcount, cursor.lastrowid
            row, rowcount, lastrowid = await self._await_settled(
                self._hass.async_add_executor_job(_fetch_one)
            )
            self._rowcount = rowcount
            self._lastrowid = lastrowid
            return row
    async def fetch_all(
        self, sql: str, parameters: Sequence[Any] = ()
    ) -> list[tuple]:
        """Run a query in the executor and return all of its rows."""
        async with self._serialized():
            conn = self._require_conn()
            def _fetch_all() -> tuple[list[tuple], int, int | None]:
                cursor = conn.execute(sql, parameters)
                return cursor.fetchall(), cursor.rowcount, cursor.lastrowid
            rows, rowcount, lastrowid = await self._await_settled(
                self._hass.async_add_executor_job(_fetch_all)
            )
            self._rowcount = rowcount
            self._lastrowid = lastrowid
            return rows
    @asynccontextmanager
    async def transaction(self) -> AsyncIterator[None]:
        """Run a block inside BEGIN/COMMIT, rolling back on any exception.
        The wrapper lock is held for the entire BEGIN..COMMIT span, so no
        other statement can interleave inside the transaction.  Entering a
        second transaction while one is active raises RuntimeError.  A
        failing COMMIT (e.g. a deferred foreign-key violation) also triggers
        a ROLLBACK before the commit error propagates, so the real
        connection is never left inside a stray transaction.
        The lifecycle is cancellation-safe.  Transaction ownership and the
        wrapper lock are only released after the executor work has fully
        settled, even when the owning task is cancelled mid-flight: if a
        cancelled BEGIN or COMMIT still completes on the worker thread,
        the corresponding ROLLBACK is shielded and awaited before any state
        is cleared.  A lifecycle statement that fails plus a failing
        follow-up cleanup surface both errors in an ExceptionGroup.
        """
        if self._in_transaction:
            raise RuntimeError(
                "A NestQuest transaction is already active on this "
                "connection; concurrent transactions are not supported"
            )
        async with self._get_lock():
            conn = self._require_conn()
            self._in_transaction = True
            self._tx_task = asyncio.current_task()
            try:
                def _begin() -> None:
                    conn.execute("BEGIN")
                await self._settle_lifecycle(
                    conn,
                    self._hass.async_add_executor_job(_begin),
                    "BEGIN did not settle cleanly and the follow-up "
                    "cleanup also raised; the transaction may need manual "
                    "cleanup",
                )
                try:
                    yield
                except BaseException:
                    def _rollback() -> None:
                        conn.execute("ROLLBACK")
                    await self._settle_lifecycle(
                        conn,
                        self._hass.async_add_executor_job(_rollback),
                        "ROLLBACK failed and the follow-up cleanup also "
                        "raised; the transaction may need manual cleanup",
                    )
                    raise
                def _commit() -> None:
                    conn.execute("COMMIT")
                await self._settle_lifecycle(
                    conn,
                    self._hass.async_add_executor_job(_commit),
                    "COMMIT failed and the follow-up cleanup also raised; "
                    "the transaction may need manual cleanup",
                )
            finally:
                self._in_transaction = False
                self._tx_task = None
    async def _settle_lifecycle(
        self, conn: sqlite3.Connection, job: Awaitable[Any], message: str
    ) -> None:
        """Run one BEGIN/ROLLBACK/COMMIT job, then force the connection clean.
        The job always settles first (see :meth:`_await_settled`), so a
        cancelled statement that the worker thread completed anyway is
        never abandoned mid-flight.  If the job raised, a follow-up
        ROLLBACK — itself settled — guarantees the connection ends outside
        any SQLite transaction before the error propagates.  When both the
        job and the cleanup raise, both errors are preserved in an
        ExceptionGroup instead of the original being lost.
        """
        try:
            await self._await_settled(job)
        except BaseException as settle_error:
            errors: list[BaseException] = [settle_error]
            try:
                await self._ensure_no_stray_transaction(conn)
            except BaseException as cleanup_error:
                errors.append(cleanup_error)
            if len(errors) == 2:
                raise BaseExceptionGroup(message, errors) from settle_error
            raise
    async def _ensure_no_stray_transaction(
        self, conn: sqlite3.Connection
    ) -> None:
        """Force the connection out of any transaction via ROLLBACK.
        Runs after a failed or cancelled lifecycle statement.  The worker
        thread may still be finishing (or may already have completed) the
        very statement whose await was cancelled, so both the check and
        the ROLLBACK settle in the executor before the caller releases
        transaction ownership.
        """
        def _cleanup() -> bool:
            if conn.in_transaction:
                conn.execute("ROLLBACK")
            return conn.in_transaction
        stray = await self._await_settled(
            self._hass.async_add_executor_job(_cleanup)
        )
        if stray:
            raise RuntimeError(
                "The SQLite connection is still inside a transaction after "
                "cleanup; the transaction may need manual cleanup"
            )
    async def _await_settled(self, job: Awaitable[Any]) -> Any:
        """Await ``job``, letting it settle even if this task is cancelled.
        Cancellation is observed, not swallowed: it is re-raised only once
        the job has fully settled, so the caller can finalize against a
        known connection state instead of abandoning in-flight work.  The
        job is wrapped in a task because a bare coroutine dies un-awaited
        once its await is cancelled.  A second cancellation during
        settlement is absorbed the same way; if the job itself fails after
        a cancellation, both errors surface in an ExceptionGroup.
        """
        result, cancelled = await self._await_settled_state(job)
        if cancelled:
            raise asyncio.CancelledError()
        return result
    async def _await_settled_state(self, job: Awaitable[Any]) -> tuple[Any, bool]:
        """Await ``job`` to settlement and report ``(result, cancelled)``.
        Same settlement guarantees as :meth:`_await_settled`, but returns
        the settled outcome together with whether the awaiting task was
        cancelled, so callers that must act on a settled result before
        re-raising cancellation (e.g. closing an abandoned connection)
        can do so without losing the result.
        """
        cancelled = False
        inner = asyncio.ensure_future(job)
        while not inner.done():
            try:
                await asyncio.shield(inner)
            except asyncio.CancelledError:
                cancelled = True
        try:
            result = inner.result()
        except BaseException as job_error:
            if cancelled:
                raise BaseExceptionGroup(
                    "executor job failed while settling after cancellation",
                    [asyncio.CancelledError(), job_error],
                ) from job_error
            raise
        return result, cancelled
    async def close(self) -> None:
        """Close the connection in the executor; safe to call repeatedly.
        Serialized on the wrapper lock, so an in-flight operation or open
        transaction always completes before the connection is closed.
        """
        if (
            self._in_transaction
            and self._tx_task is not None
            and asyncio.current_task() is self._tx_task
        ):
            raise RuntimeError(
                "Cannot close the NestQuest database from inside its own "
                "transaction; exit the transaction first"
            )
        async with self._get_lock():
            conn, self._conn = self._conn, None
            self._rowcount, self._lastrowid = -1, None
            if conn is None:
                return
            def _close() -> None:
                conn.close()
            await self._await_settled(self._hass.async_add_executor_job(_close))
    def _require_conn(self) -> sqlite3.Connection:
        if self._conn is None:
            raise RuntimeError(
                "The NestQuest database connection is closed; call open() first"
            )
        return self._conn
@@ -3,10 +3,10 @@
  "name": "NestQuest",
  "codeowners": ["@overseer"],
  "config_flow": true,
-  "documentation": "https://code.cubecraftlabs.com/Maniacs_Mansion/NestQuest",
+  "documentation": "https://github.com/talon2king/NestQuest",
  "integration_type": "hub",
  "iot_class": "local_polling",
-  "issue_tracker": "https://code.cubecraftlabs.com/Maniacs_Mansion/NestQuest/issues",
+  "issue_tracker": "https://github.com/talon2king/NestQuest/issues",
  "requirements": [],
  "version": "0.1.0"
 }
@@ -1,236 +0,0 @@
 """Versioned schema migration runner for the NestQuest SQLite database.
 The runner owns one metadata table, ``nestquest_schema_version``, holding
 a single row with the schema version the database file currently
 reflects.  Migrations are declared as an ordered list: entry ``n``
 upgrades version ``n-1`` to version ``n``.  Applying a migration means
 running its DDL inside one transaction and stamping the new version in
 that same transaction, so a database never persists a half-applied
 migration: any failure rolls the statements and the version stamp back
 together, leaving the file at its prior version.
 Version 0 means "the file exists but carries no NestQuest schema yet"
 — a fresh file, or a file this integration has never written.  Starting
 the runner against version 0 applies migration 1, which applies the
 full v1 DDL from :mod:`.schema` and stamps version 1.
 Each migration's statements and its version stamp run inside ONE
 transaction, and the stamp is written as DELETE + INSERT rather than
 UPDATE because a version-0 database has no version row to update yet.
 An UPDATE form would silently match zero rows there and leave the
 database migrated but unstamped — reporting version 0 forever, and
 re-applying migration 1 on every startup.  The DELETE+INSERT shape is
 also what makes the stamp a replace, so the row never duplicates.
 Idempotence across restarts: a database already at the latest version
 has no pending migrations, so the runner opens a read-only check, finds
 nothing to do, and leaves the file byte-untouched (no write transaction
 is opened, so no WAL churn).
 Migration list policy: entries are append-only.  Once shipped, a
 migration's SQL must never be edited — an old database applying it for
 the first time and a fresh database would then diverge.  Fix-forward
 with a new migration instead.  The version stamp lives in the same
 SQLite file as the data it describes, so restoring a backup restores
 its matching schema version with it.
 Corrupt or missing files are not this module's concern: startup
 handling of those cases is task d5bca74b, which decides whether to
 open fresh or raise ConfigEntryNotReady.  This runner assumes it is
 handed an open, working connection.
 """
 from __future__ import annotations
 import asyncio
 import logging
 from collections.abc import Sequence
 from .db import NestQuestDatabase
 from .schema import SCHEMA_V1_STATEMENTS
 LOGGER = logging.getLogger(__name__)
 #: Metadata table holding the applied schema version.  Prefixed with the
 #: integration domain so a config directory shared with other tools can
 #: never collide with a foreign ``schema_version`` table.
 VERSION_TABLE = "nestquest_schema_version"
 #: DDL for the version metadata table.  The singleton-row invariant is
 #: enforced by the schema itself: ``id`` is fixed at 1 by CHECK, keyed
 #: PRIMARY KEY, so the table can never hold a second row, and the
 #: stamp write is an UPDATE (no DELETE+INSERT needed).  ``version`` is
 #: a positive integer: version 0 means "no row" and is never stored,
 #: so a negative or zero stored value is corruption, not a state the
 #: runner can act on.
 VERSION_TABLE_DDL = f"""
    CREATE TABLE IF NOT EXISTS {VERSION_TABLE} (
        id INTEGER NOT NULL PRIMARY KEY CHECK (id = 1),
        version INTEGER NOT NULL CHECK (version >= 1)
    )
 """
 #: One-based index into :data:`MIGRATIONS`: entry ``n`` upgrades
 #: version ``n-1`` to version ``n``.
 MIGRATION_1_V1_DDL: list[str] = list(SCHEMA_V1_STATEMENTS)
 #: The ordered migration list.  Append-only: never edit an applied
 #: entry, add the next one instead.
 MIGRATIONS: Sequence[Sequence[str]] = [
    MIGRATION_1_V1_DDL,
 ]
 async def _create_version_table(database: NestQuestDatabase) -> None:
    """Create the version metadata table if it does not exist.
    The table is created before any version check: it is the runner's
    own bookkeeping, and creating it is idempotent.  The DDL enforces
    the singleton-row invariant (``id`` fixed at 1) and a positive
    integer version at the database level.
    """
    await database.execute(VERSION_TABLE_DDL)
 async def read_schema_version(database: NestQuestDatabase) -> int:
    """Return the schema version recorded in the database.
    Returns 0 when the version table or its row is missing — the
    "fresh file" case.  A stored version higher than the runner knows
    is surfaced as-is; the caller decides how to treat a future version.
    Reading is deliberately passive: a missing table raises, it is not
    swallowed into 0, because :func:`apply_migrations` always creates
    the table before reading and a direct reader hitting "no such
    table" is a caller bug worth surfacing, not a fresh file.
    """
    row = await database.fetch_one(
        f"SELECT version FROM {VERSION_TABLE} LIMIT 1"
    )
    if row is None:
        return 0
    version = row[0]
    if not isinstance(version, int) or isinstance(version, bool):
        raise RuntimeError(
            f"Schema version metadata is not an integer: {version!r}"
        )
    if version < 1:
        raise RuntimeError(
            f"Schema version metadata is invalid (must be >= 1): {version!r}"
        )
    return version
 #: One asyncio.Lock per connection wrapper, created lazily and bound to
 #: the running loop, serializing whole apply_migrations runs so two
 #: concurrent callers on one connection queue instead of colliding on
 #: the wrapper's single-transaction rule.
 _MIGRATION_LOCKS: dict[int, asyncio.Lock] = {}
 def _migration_lock(database: NestQuestDatabase) -> asyncio.Lock:
    """Return the migration lock bound to ``database``'s running loop."""
    loop = asyncio.get_running_loop()
    key = (id(database), id(loop))
    lock = _MIGRATION_LOCKS.get(key)
    if lock is None:
        lock = asyncio.Lock()
        _MIGRATION_LOCKS[key] = lock
    return lock
 async def apply_migrations(
    database: NestQuestDatabase,
    migrations: Sequence[Sequence[str]] = MIGRATIONS,
 ) -> int:
    """Bring the database schema up to the latest version.
    Reads the current version, applies every pending migration in order
    — each inside one transaction that also stamps the new version —
    and returns the resulting version.  Applying zero pending
    migrations (database already current) is a no-op that logs at debug
    level and opens no write transaction.
    The version is re-read inside each migration transaction before
    deciding to apply it: the outer read is advisory only (loop bounds,
    no-op fast path), and on one shared connection two interleaved
    runners would otherwise both read the same version and both apply
    a non-idempotent migration.  Under the transaction's lock the
    second runner sees the first runner's stamp and skips.
    Runners on one connection are serialized by an asyncio lock held
    for the whole decide-and-apply span: NestQuestDatabase rejects a
    second transaction while one is active (RuntimeError), so
    concurrent callers must queue, and the lock makes that queueing
    explicit.  The loser of the race re-reads the version once it
    acquires the lock, sees the winner's stamp, and applies nothing.
    Raises whatever the failing migration raised, with the database
    rolled back to its prior version and prior rows untouched.
    """
    async with _migration_lock(database):
        await _create_version_table(database)
        current = await read_schema_version(database)
        latest = len(migrations)
        if current > latest:
            raise RuntimeError(
                f"Database schema version {current} is newer than this "
                f"integration understands (latest known: {latest}); "
                "downgrades are not supported"
            )
        if current == latest:
            LOGGER.debug(
                "NestQuest schema already at version %d; "
                "no migrations to apply",
                current,
            )
            return current
        for target in range(current + 1, latest + 1):
            applied = False
            async with database.transaction():
                # Re-read the version INSIDE the write transaction: the
                # outer read was advisory, and the wrapper serializes
                # individual statements, not the read-decide-apply
                # decision.  Under the transaction's lock the version
                # cannot change between the read and the stamp, and the
                # loser of an interleaved race (a queued second caller
                # re-running apply_migrations after the winner already
                # migrated) re-reads here, sees the winner's stamp, and
                # skips instead of re-applying a non-idempotent
                # migration with a stale version.
                in_tx_version = await read_schema_version(database)
                if in_tx_version > latest:
                    raise RuntimeError(
                        f"Database schema version {in_tx_version} is newer "
                        f"than this integration understands (latest known: "
                        f"{latest}); downgrades are not supported"
                    )
                if in_tx_version < target:
                    for sql in migrations[target - 1]:
                        await database.execute(sql)
                    # INSERT keyed on the singleton row (id = 1): a
                    # fresh file has no row yet, and migration 1 is the
                    # only migration a version-0 database can run, so
                    # the row is seeded by the same statement batch
                    # when needed.  UPDATE alone would silently match
                    # zero rows there.
                    await database.execute(
                        f"INSERT INTO {VERSION_TABLE} (id, version) "
                        "VALUES (1, ?) "
                        "ON CONFLICT (id) DO UPDATE SET version = "
                        "excluded.version",
                        (target,),
                    )
                    applied = True
            if applied:
                LOGGER.info(
                    "NestQuest schema migrated to version %d "
                    "(%d statement%s)",
                    target,
                    len(migrations[target - 1]),
                    "s" if len(migrations[target - 1]) != 1 else "",
                )
        return latest
@@ -1,336 +0,0 @@
 """Declarative schema DDL for the NestQuest SQLite database.
 Every v1 table is declared here as plain SQL statements so later schema
 tasks can append their own DDL to :data:`SCHEMA_V1_STATEMENTS` without
 touching this module's tables.  All statements use
 ``CREATE TABLE IF NOT EXISTS`` so application is idempotent; the versioned
 migration runner is a separate later concern.
 Timestamp policy: all timestamps are UTC ISO-8601 strings, set by the
 caller/DAO rather than a DB default, per the feature guardrails.  No
 ``length(created_at) = 25`` CHECK is added: the exact string shape
 produced by callers is not fixed yet (``datetime.isoformat()`` may
 include microseconds, changing the length), so enforcing a fixed length
 here would reject valid values.  Only non-null is enforced for now.
 Date policy: ``start_date``/``end_date`` are ISO date strings (YYYY-MM-DD,
 no time component) compared lexically by the ``end_date >= start_date``
 CHECK; due_date/due_time string shapes are caller-validated by the rule
 engine and DAO layers, not by DDL.
 ``weekday_set`` format: comma-separated weekday integers (CSV), 0 =
 Monday .. 6 = Sunday, e.g. ``'0,2,4'`` for Mon/Wed/Fri.  Chosen over JSON
 because it sorts, compares and parses trivially in SQL.  The format is
 enforced at the DB level: a CHECK matches ``weekday_set`` against GLOB
 patterns for element counts 1..7 of single digits 0-6 (GLOB has no
 repetition quantifier, so the element count is enumerated; more than 7
 elements is impossible for a weekday set).  ``''``, ``'x'``, ``'7'`` and
 ``'0,x'`` are all rejected; NULL stays allowed for non-weekly rules.
 This keeps the schema layer storage-only — the DAO layer (tasks
 8c72300c+) does not need its own validator, it can rely on the CHECK.
 Type affinity: SQLite applies column affinity on insert, but CHECKs
 still observe text and fractional values ('abc' stays text, '1.5'
 becomes REAL), so every INTEGER column carrying rule semantics carries
 a ``typeof(...) = 'integer'`` guard in its CHECK.
 Rule-type coherence: two CHECKs enforce that ``weekly`` carries a
 non-null ``weekday_set``, ``monthly`` carries a ``day_of_month`` or an
 ``nth_weekday``, ``yearly`` carries a ``month``, and ``daily``/``custom``
 carry none of the month-specific fields.  Relaxed deliberately: the DDL
 does NOT forbid ``month`` on weekly/monthly rules (an "every 2 weeks"
 rule or a monthly rule could later be pinned to a month) nor extra
 fields on weekly/monthly/yearly rules generally, so the CHECKs only
 reject what the rule engine definitively cannot express now.
 Presence pattern format: ``presence_schedules.pattern`` encodes the
 whole N-week repeating cycle in one TEXT column as pipe-separated
 segments, one per week of the cycle: segment ``i`` is a ``weekday_set``
 CSV (same single-digit 0-6 convention as ``schedule_rules.weekday_set``)
 for week ``i``, e.g. ``'0,2,4|1,3'`` = Mon/Wed/Fri in week 0, Tue/Thu in
 week 1.  An empty segment means the child is absent every day of that
 week; a wholly empty pattern with ``cycle_length_weeks = 1`` means
 absent every day of every week, which is distinct from having no
 schedule row at all (a child with no schedule is present every day, per
 the feature guardrails).  Chosen over JSON or per-week rows to stay
 consistent with the CSV ``weekday_set`` convention and fully checkable
 by plain CHECKs.  DB-level validation is pure CHECK, no triggers or
 custom functions: char-class GLOBs reject every character outside
 digits 0-6, comma and pipe; adjacency GLOBs reject empty elements
 (double comma, comma next to a pipe, leading/trailing comma) and
 two-digit runs; a further GLOB rejects contiguous runs of 8+ comma
 separated digits, which caps a segment at 7 elements — the GLOB cannot
 match across a ``|``, so cross-segment boundaries stay safe; and the
 segment count is pinned to ``cycle_length_weeks`` by
 ``LENGTH(pattern) - LENGTH(REPLACE(pattern, '|', '')) + 1``.  Per
 weekday_set this is not full CSV-set normalisation (duplicates like
 ``'0,0'`` or unsorted sets stay storable, as with ``schedule_rules``);
 the engine treats the segment as a set.  A
 one-row-per-``week_index`` normalization was considered and rejected:
 the flat single-column encoding keeps the done-condition's table shape
 and proved fully checkable with plain CHECKs.
 ``cycle_length_weeks`` is capped at 4: 2 (alternating weeks) is the
 common case, and a small cap keeps anchor arithmetic and the encoded
 pattern human-readable.  Revisit the cap — and re-run the pattern
 validation matrix in tests — if a longer cycle is ever needed.
 ``anchor_date`` is an ISO date string pinning the cycle: the week index
 of a date is (days since anchor) // 7 mod ``cycle_length_weeks``,
 computed by the presence engine (Feature 05) from anchor-date
 arithmetic, never ISO week parity.  Like other date columns it carries
 no shape CHECK, matching the established date policy.
 Presence overrides: ``presence_overrides.end_date`` is NOT NULL,
 unlike ``schedule_rules.end_date`` — an override is always a concrete
 date range; a single-day override stores the same date in both columns.
 ``is_present`` 0/1 marks the child absent/present for the whole range;
 ``note`` is optional free text.
 Task instances: ``task_instances`` deliberately carries NO completion
 status column.  An instance's current state derives from the latest row
 in ``completion_events`` (Feature 08), so materialization (Feature 07)
 can insert instances without knowing anything about completion and the
 append-only log stays the single source of truth.  ``definition_id``,
 ``due_date`` and ``child_id`` snapshot the materialization-time facts
 ("why this instance exists"): reassignment and rule edits change future
 instances only and never rewrite these rows.  ``due_time`` snapshots
 the definition's optional due time at generation for the same reason —
 the instance keeps its generation-time value even if the definition is
 edited later.  ``generated_at`` is a caller-set UTC ISO-8601 timestamp
 per the module timestamp policy (no DB default, so a generation batch
 stamps one coherent time).
 Uniqueness: the (definition_id, due_date) pair is declared as a
 table-level ``UNIQUE`` constraint rather than a separate ``CREATE
 UNIQUE INDEX`` statement.  SQLite materializes it as an implicit unique
 index (``sqlite_autoindex_task_instances_1``) — the required "unique
 index prevents duplicate instances" — and the constraint form cannot be
 silently dropped the way a standalone index can, so the duplicate door
 stays shut.  Idempotent materialization keys on exactly this pair.  No
 secondary indexes beyond it are declared yet: history and board queries
 (Features 08/10/13) should justify their indexes as a later migration
 once their query shapes exist.
 No ``ON DELETE`` clauses are declared on any foreign key in this
 module: children and definitions are deactivated, never deleted, so a
 deletion that would orphan instances or events is a bug and must fail
 loudly rather than cascade.
 Completion events: ``completion_events`` is strictly append-only.  That
 property is enforced at the DAO layer — the completion-events DAO
 exposes no UPDATE or DELETE path (task a9ea75fc) — and deliberately NOT
 by database triggers, matching the module's no-trigger policy: a RAISE
 trigger would be the one exception to "pure CHECK, no triggers",
 complicate the later migration runner, and still prove nothing (any
 writer can run raw SQL).  ``event_type`` is constrained to
 ``completed``/``uncompleted``; a ``missed`` row is intentionally not
 representable — missed is a derived state announced on the HA event bus
 (Feature 10), never an appended event.
 ``child_id`` is deliberately denormalized (it is also derivable via
 ``task_instances``): history reporting (Feature 13) filters and rates
 per child directly against the log, and the log should stay
 self-describing without joins.
 Actor policy: ``actor_source`` distinguishes an authenticated HA user
 (``'user'``, with ``actor_user_id`` holding the HA user id) from a
 panel tap with no user context (``'panel'``, with ``actor_user_id``
 NULL — the tapped child profile is already ``child_id``).  A coherence
 CHECK makes the pair shape non-negotiable: ``'user'`` requires
 ``actor_user_id``, ``'panel'`` forbids it.  Other actor sources are
 rejected; if an unattended system actor is ever needed, that is a
 schema migration, not a silent widening.
 On-time policy: ``was_on_time`` is required (integer 0 or 1) for
 ``completed`` events — at completion time the due date is known and the
 flag is decidable.  For ``uncompleted`` events the flag is optional:
 Feature 08 decides whether a reversal carries the reversed completion's
 on-time flag or NULL, and the DDL deliberately accepts both rather than
 guessing.
 """
 from __future__ import annotations
 from .db import NestQuestDatabase
 #: GLOB patterns matching a valid ``weekday_set`` CSV: one GLOB per
 #: possible element count 1..7 (a weekday set cannot exceed 7 elements).
 #: Each element is a single digit 0-6, separated by a literal comma.
 #: GLOB has no repetition quantifier, so the element count is enumerated.
 #: ``''`` and NULL are deliberately NOT matched: NULL is allowed for
 #: non-weekly rules via a separate ``IS NULL`` arm, and '' is invalid
 #: because weekly rules need at least one weekday.
 WEEKDAY_SET_GLOB_PATTERNS: list[str] = [
    "[0-6]",
    "[0-6],[0-6]",
    "[0-6],[0-6],[0-6]",
    "[0-6],[0-6],[0-6],[0-6]",
    "[0-6],[0-6],[0-6],[0-6],[0-6]",
    "[0-6],[0-6],[0-6],[0-6],[0-6],[0-6]",
    "[0-6],[0-6],[0-6],[0-6],[0-6],[0-6],[0-6]",
 ]
 SCHEMA_V1_CHILDREN_DDL: list[str] = [
    """
    CREATE TABLE IF NOT EXISTS children (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        display_name TEXT NOT NULL,
        colour TEXT,
        avatar_ref TEXT,
        sort_order INTEGER NOT NULL DEFAULT 0 CHECK (typeof(sort_order) = 'integer'),
        is_active INTEGER NOT NULL DEFAULT 1 CHECK (typeof(is_active) = 'integer' AND is_active IN (0, 1)),
        created_at TEXT NOT NULL
    )
    """,
 ]
 SCHEMA_V1_ADMIN_USERS_DDL: list[str] = [
    """
    CREATE TABLE IF NOT EXISTS admin_users (
        ha_user_id TEXT PRIMARY KEY NOT NULL,
        added_at TEXT NOT NULL
    )
    """,
 ]
 _WEEKDAY_SET_CHECK = " OR ".join(
    f"weekday_set GLOB '{pattern}'" for pattern in WEEKDAY_SET_GLOB_PATTERNS
 )
 SCHEMA_V1_SCHEDULE_RULES_DDL: list[str] = [
    f"""
    CREATE TABLE IF NOT EXISTS schedule_rules (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        rule_type TEXT NOT NULL CHECK (rule_type IN ('daily', 'weekly', 'monthly', 'yearly', 'custom')),
        interval INTEGER NOT NULL DEFAULT 1 CHECK (typeof(interval) = 'integer' AND interval >= 1),
        weekday_set TEXT CHECK (weekday_set IS NULL OR ({_WEEKDAY_SET_CHECK})),
        day_of_month INTEGER CHECK (day_of_month IS NULL OR (typeof(day_of_month) = 'integer' AND day_of_month >= 1 AND day_of_month <= 31)),
        nth_weekday INTEGER CHECK (nth_weekday IS NULL OR (typeof(nth_weekday) = 'integer' AND nth_weekday IN (-1, 1, 2, 3, 4, 5))),
        month INTEGER CHECK (month IS NULL OR (typeof(month) = 'integer' AND month >= 1 AND month <= 12)),
        start_date TEXT NOT NULL,
        end_date TEXT,
        CHECK (end_date IS NULL OR end_date >= start_date),
        CHECK (
            (rule_type = 'weekly' AND weekday_set IS NOT NULL)
            OR (rule_type = 'monthly' AND (day_of_month IS NOT NULL OR nth_weekday IS NOT NULL))
            OR (rule_type = 'yearly' AND month IS NOT NULL)
            OR (rule_type IN ('daily', 'custom') AND day_of_month IS NULL AND nth_weekday IS NULL AND month IS NULL)
        )
    )
    """,
 ]
 SCHEMA_V1_TASK_DEFINITIONS_DDL: list[str] = [
    """
    CREATE TABLE IF NOT EXISTS task_definitions (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        description TEXT,
        icon TEXT,
        child_id INTEGER NOT NULL REFERENCES children(id),
        schedule_rule_id INTEGER NOT NULL REFERENCES schedule_rules(id),
        due_time TEXT,
        is_active INTEGER NOT NULL DEFAULT 1 CHECK (typeof(is_active) = 'integer' AND is_active IN (0, 1)),
        created_at TEXT NOT NULL
    )
    """,
 ]
 SCHEMA_V1_PRESENCE_SCHEDULES_DDL: list[str] = [
    f"""
    CREATE TABLE IF NOT EXISTS presence_schedules (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        child_id INTEGER NOT NULL UNIQUE REFERENCES children(id),
        cycle_length_weeks INTEGER NOT NULL CHECK (typeof(cycle_length_weeks) = 'integer' AND cycle_length_weeks >= 1 AND cycle_length_weeks <= 4),
        anchor_date TEXT NOT NULL,
        pattern TEXT NOT NULL CHECK (
            NOT pattern GLOB '*[^0-6,|]*'
            AND NOT pattern GLOB '*[0-6][0-6]*'
            AND NOT pattern GLOB '*,,*'
            AND NOT pattern GLOB '*,|*'
            AND NOT pattern GLOB '*|,*'
            AND NOT pattern GLOB ',*'
            AND NOT pattern GLOB '*,'
            AND NOT pattern GLOB '*[0-6],[0-6],[0-6],[0-6],[0-6],[0-6],[0-6],[0-6]*'
            AND (LENGTH(pattern) - LENGTH(REPLACE(pattern, '|', '')) + 1) = cycle_length_weeks
        )
    )
    """,
 ]
 SCHEMA_V1_PRESENCE_OVERRIDES_DDL: list[str] = [
    """
    CREATE TABLE IF NOT EXISTS presence_overrides (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        child_id INTEGER NOT NULL REFERENCES children(id),
        start_date TEXT NOT NULL,
        end_date TEXT NOT NULL,
        is_present INTEGER NOT NULL CHECK (typeof(is_present) = 'integer' AND is_present IN (0, 1)),
        note TEXT,
        CHECK (end_date >= start_date)
    )
    """,
 ]
 SCHEMA_V1_TASK_INSTANCES_DDL: list[str] = [
    """
    CREATE TABLE IF NOT EXISTS task_instances (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        definition_id INTEGER NOT NULL REFERENCES task_definitions(id),
        child_id INTEGER NOT NULL REFERENCES children(id),
        due_date TEXT NOT NULL,
        due_time TEXT,
        generated_at TEXT NOT NULL,
        UNIQUE (definition_id, due_date)
    )
    """,
 ]
 SCHEMA_V1_COMPLETION_EVENTS_DDL: list[str] = [
    """
    CREATE TABLE IF NOT EXISTS completion_events (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        instance_id INTEGER NOT NULL REFERENCES task_instances(id),
        child_id INTEGER NOT NULL REFERENCES children(id),
        event_type TEXT NOT NULL CHECK (event_type IN ('completed', 'uncompleted')),
        actor_source TEXT NOT NULL CHECK (actor_source IN ('user', 'panel')),
        actor_user_id TEXT,
        occurred_at TEXT NOT NULL,
        was_on_time INTEGER CHECK (
            was_on_time IS NULL
            OR (typeof(was_on_time) = 'integer' AND was_on_time IN (0, 1))
        ),
        CHECK (
            (actor_source = 'user' AND actor_user_id IS NOT NULL)
            OR (actor_source = 'panel' AND actor_user_id IS NULL)
        ),
        CHECK (
            (event_type = 'completed' AND was_on_time IS NOT NULL)
            OR (event_type = 'uncompleted')
        )
    )
    """,
 ]
 SCHEMA_V1_STATEMENTS: list[str] = [
    *SCHEMA_V1_CHILDREN_DDL,
    *SCHEMA_V1_ADMIN_USERS_DDL,
    *SCHEMA_V1_SCHEDULE_RULES_DDL,
    *SCHEMA_V1_TASK_DEFINITIONS_DDL,
    *SCHEMA_V1_PRESENCE_SCHEDULES_DDL,
    *SCHEMA_V1_PRESENCE_OVERRIDES_DDL,
    *SCHEMA_V1_TASK_INSTANCES_DDL,
    *SCHEMA_V1_COMPLETION_EVENTS_DDL,
 ]
 async def async_apply_ddl(
    database: NestQuestDatabase, statements: list[str]
 ) -> None:
    """Apply each DDL statement inside one transaction on ``database``.
    Idempotent: every statement is ``CREATE TABLE IF NOT EXISTS``, so
    applying the same list twice leaves the schema and its rows intact.
    """
    async with database.transaction():
        for sql in statements:
            await database.execute(sql)
@@ -25,9 +25,6 @@ import asyncio
 import importlib.util
 import inspect
 import sys
 import tempfile
 import weakref
 from pathlib import Path
 from types import SimpleNamespace
 from unittest.mock import MagicMock
@@ -221,35 +218,13 @@ def wire_entry_to_registry(entry, registry: ListenerRegistry):
    return entry
 class _HassNamespace(SimpleNamespace):
    """SimpleNamespace subclass so the hass stub can hold weak references."""
 def make_hass() -> tuple:
    """Create a hass stub plus its listener registry, mirroring HA's shape."""
-    config_dir = Path(tempfile.mkdtemp(prefix="nestquest-hass-"))
+    hass = SimpleNamespace(
    hass = _HassNamespace(
        data={},
        config=MagicMock(),
        config_entries=SimpleNamespace(async_reload=None),
    )
    def _cleanup_config_dir():
        import shutil
        shutil.rmtree(config_dir, ignore_errors=True)
    weakref.finalize(hass, _cleanup_config_dir)
    hass.config.path = MagicMock(
        side_effect=lambda name: str(config_dir / (name or "config"))
    )
    def _run_executor_job(fn, *args, **kwargs):
        return asyncio.get_running_loop().run_in_executor(
            None, lambda: fn(*args, **kwargs)
        )
    hass.async_add_executor_job = _run_executor_job
    registry = ListenerRegistry(hass)
    async def _async_reload(entry_id: str) -> None:
@@ -1,998 +0,0 @@
 """Tests for db.py, the async SQLite connection wrapper of NestQuest."""
 from __future__ import annotations
 import ast
 import asyncio
 import sqlite3
 import threading
 from pathlib import Path
 from typing import Any
 from unittest.mock import AsyncMock, MagicMock
 import pytest
 from conftest import make_hass
 from custom_components.nestquest import db as db_module
 from custom_components.nestquest.db import NestQuestDatabase
 def _run(coro):
    return asyncio.new_event_loop().run_until_complete(coro)
 def _make_hass_mock():
    hass = MagicMock()
    hass.async_add_executor_job = AsyncMock(side_effect=(lambda fn, *a: fn(*a)))
    return hass
 def _open_db(path) -> tuple[NestQuestDatabase, MagicMock]:
    hass = _make_hass_mock()
    database = NestQuestDatabase(hass)
    _run(database.open(path))
    return database, hass
 # ---------------------------------------------------------------------------
 # open(): pragmas, executor delegation, idempotence
 # ---------------------------------------------------------------------------
 def test_open_enables_wal_journal_mode(tmp_path) -> None:
    database, _hass = _open_db(tmp_path / "wal.db")
    row = _run(database.fetch_one("PRAGMA journal_mode"))
    assert row is not None and str(row[0]).lower() == "wal"
    _run(database.close())
 def test_open_enables_foreign_key_enforcement(tmp_path) -> None:
    database, _hass = _open_db(tmp_path / "fk.db")
    row = _run(database.fetch_one("PRAGMA foreign_keys"))
    assert row is not None and int(row[0]) == 1
    _run(database.close())
 def test_open_delegates_connection_to_executor(tmp_path) -> None:
    hass = _make_hass_mock()
    database = NestQuestDatabase(hass)
    _run(database.open(tmp_path / "exec.db"))
    first_call = hass.async_add_executor_job.call_args_list[0]
    assert callable(first_call.args[0])
    _run(database.close())
 def test_open_twice_returns_same_wrapper_without_second_connect(tmp_path) -> None:
    path = tmp_path / "once.db"
    hass = _make_hass_mock()
    database = NestQuestDatabase(hass)
    first = _run(database.open(path))
    second = _run(database.open(path))
    assert first is database
    assert second is database
    assert database.connected is True
    opens = [
        c
        for c in hass.async_add_executor_job.call_args_list
        if getattr(c.args[0], "__name__", "") == "_open"
    ]
    assert len(opens) == 1
    _run(database.close())
 def test_open_accepts_str_path(tmp_path) -> None:
    database, _hass = _open_db(str(tmp_path / "str.db"))
    assert database.connected is True
    _run(database.close())
 # ---------------------------------------------------------------------------
 # Executor delegation: every sqlite3 call happens off the event loop
 # ---------------------------------------------------------------------------
 def test_every_method_routes_sqlite3_through_executor(tmp_path) -> None:
    """Each wrapper call must go via hass.async_add_executor_job."""
    database, hass = _open_db(tmp_path / "route.db")
    calls_before = hass.async_add_executor_job.await_count
    _run(database.execute("CREATE TABLE t (id INTEGER PRIMARY KEY, name TEXT)"))
    _run(database.execute("INSERT INTO t (name) VALUES (?)", ("a",)))
    _run(database.execute_many("INSERT INTO t (name) VALUES (?)", [("b",), ("c",)]))
    row = _run(database.fetch_one("SELECT name FROM t WHERE id = 1"))
    rows = _run(database.fetch_all("SELECT name FROM t ORDER BY id"))
    assert row == ("a",)
    assert rows == [("a",), ("b",), ("c",)]
    assert hass.async_add_executor_job.await_count == calls_before + 5
    _run(database.close())
 async def test_every_wrapper_call_awaits_executor_job(tmp_path) -> None:
    """The executor surface is awaited per wrapper call on a live loop."""
    hass = _make_hass_mock()
    database = NestQuestDatabase(hass)
    await database.open(tmp_path / "loop.db")
    await database.execute("CREATE TABLE t (id INTEGER PRIMARY KEY)")
    await database.fetch_one("SELECT 1")
    await database.fetch_all("SELECT 1")
    await database.execute_many("INSERT INTO t (id) VALUES (?)", [(1,)])
    async with database.transaction():
        pass
    assert hass.async_add_executor_job.await_count == 7
    assert all(callable(c.args[0]) for c in hass.async_add_executor_job.call_args_list)
    await database.close()
 async def test_public_methods_return_only_plain_data(tmp_path) -> None:
    """execute/execute_many return plain dataclasses, never sqlite3 objects."""
    hass = _make_hass_mock()
    database = NestQuestDatabase(hass)
    await database.open(tmp_path / "plain.db")
    result = await database.execute(
        "CREATE TABLE t (id INTEGER PRIMARY KEY, name TEXT)"
    )
    assert not isinstance(result, (sqlite3.Connection, sqlite3.Cursor))
    insert = await database.execute("INSERT INTO t (name) VALUES (?)", ("a",))
    assert not isinstance(insert, (sqlite3.Connection, sqlite3.Cursor))
    assert insert.rowcount == 1
    assert insert.lastrowid == 1
    many = await database.execute_many(
        "INSERT INTO t (name) VALUES (?)", [("b",), ("c",)]
    )
    assert not isinstance(many, (sqlite3.Connection, sqlite3.Cursor))
    assert many.rowcount == 2
    row = await database.fetch_one("SELECT name FROM t WHERE id = 1")
    assert row == ("a",)
    rows = await database.fetch_all("SELECT name FROM t ORDER BY id")
    assert rows == [("a",), ("b",), ("c",)]
    for value in (*rows, row):
        assert not isinstance(value, sqlite3.Cursor)
    await database.close()
 async def test_no_sqlite3_call_on_event_loop_with_real_executor(tmp_path) -> None:
    """With the conftest hass (real run_in_executor), queries still succeed."""
    hass, _registry = make_hass()
    database = NestQuestDatabase(hass)
    await database.open(tmp_path / "real.db")
    await database.execute("CREATE TABLE t (id INTEGER PRIMARY KEY)")
    assert await database.fetch_one("SELECT 1") == (1,)
    await database.close()
 async def test_real_executor_never_touches_loop_thread(tmp_path, monkeypatch) -> None:
    """Every real sqlite3 call must run on a non-event-loop thread.
    sqlite3.connect is instrumented so a proxy wraps the connection and
    every cursor it hands out; each proxied call records the thread it ran
    on.  A recorded thread id equal to the event-loop thread would mean a
    sqlite3 call crossed the executor boundary.
    """
    loop_thread = threading.get_ident()
    recorded: list[int] = []
    guard = threading.Lock()
    def _note() -> None:
        with guard:
            recorded.append(threading.get_ident())
    class _Proxy:
        """Generic proxy recording the thread of every attribute call."""
        def __init__(self, target: Any) -> None:
            object.__setattr__(self, "_target", target)
        def __getattr__(self, name: str):
            attr = getattr(object.__getattribute__(self, "_target"), name)
            if not callable(attr):
                return attr
            def _wrapped(*args, **kwargs):
                _note()
                result = attr(*args, **kwargs)
                if isinstance(result, sqlite3.Cursor):
                    return _Proxy(result)
                return result
            return _wrapped
    real_connect = sqlite3.connect
    connect_threads: list[int] = []
    def _traced_connect(*args, **kwargs):
        _note()
        connect_threads.append(threading.get_ident())
        return _Proxy(real_connect(*args, **kwargs))
    monkeypatch.setattr(db_module.sqlite3, "connect", _traced_connect)
    hass, _registry = make_hass()
    database = NestQuestDatabase(hass)
    await database.open(tmp_path / "threads.db")
    await database.execute("CREATE TABLE t (id INTEGER PRIMARY KEY)")
    await database.execute("INSERT INTO t (id) VALUES (?)", (1,))
    await database.execute_many("INSERT INTO t (id) VALUES (?)", [(2,), (3,)])
    assert await database.fetch_one("SELECT id FROM t WHERE id = 1") == (1,)
    assert await database.fetch_all("SELECT id FROM t") == [(1,), (2,), (3,)]
    async with database.transaction():
        await database.execute("INSERT INTO t (id) VALUES (?)", (4,))
    await database.close()
    assert recorded, "expected at least one recorded sqlite3 call"
    assert len(connect_threads) == 1, (
        f"expected exactly one traced sqlite3.connect, got {len(connect_threads)}"
    )
    assert loop_thread not in recorded, (
        f"sqlite3 call ran on the event-loop thread {loop_thread}; "
        f"recorded threads: {sorted(set(recorded))}"
    )
 # ---------------------------------------------------------------------------
 # Source scan: sqlite3.* only inside executor-delegated functions
 # ---------------------------------------------------------------------------
 def _iter_with_scope(node, chain):
    """Yield (node, enclosing-function chain), depth-first."""
    for child in ast.iter_child_nodes(node):
        yield child, chain
        next_chain = chain + (child,) if isinstance(
            child, (ast.FunctionDef, ast.AsyncFunctionDef)
        ) else chain
        yield from _iter_with_scope(child, next_chain)
 def test_db_sqlite3_calls_only_inside_executor_delegated_functions() -> None:
    """Every sqlite3.* call in db.py sits inside an executor-delegating function.
    A function is executor-delegating when it (or, for nested closures, any of
    its enclosing functions) passes work to ``hass.async_add_executor_job``.
    """
    source = Path(db_module.__file__).read_text(encoding="utf-8")
    tree = ast.parse(source, filename=str(db_module.__file__))
    delegated = {
        node.name
        for node in ast.walk(tree)
        if isinstance(node, (ast.FunctionDef, ast.AsyncFunctionDef))
        and any(
            isinstance(sub, ast.Call)
            and isinstance(sub.func, ast.Attribute)
            and sub.func.attr == "async_add_executor_job"
            for sub in ast.walk(node)
        )
    }
    assert delegated, "expected executor-delegated functions in db.py"
    violations = []
    for node, chain in _iter_with_scope(tree, ()):
        if (
            isinstance(node, ast.Call)
            and isinstance(node.func, ast.Attribute)
            and isinstance(node.func.value, ast.Name)
            and node.func.value.id == "sqlite3"
            and not any(f.name in delegated for f in chain)
        ):
            violations.append((node.lineno, node.func.attr))
    assert violations == [], (
        f"Direct sqlite3 calls outside executor-delegated functions: {violations}"
    )
 def test_package_wide_sqlite3_scan_excludes_only_db_module() -> None:
    """The package-wide store scan exempts exactly the db module."""
    pkg_dir = Path(db_module.__file__).parent
    scanned = [p.name for p in pkg_dir.glob("*.py") if p.name != "db.py"]
    assert scanned, "expected other package modules to be scanned"
    assert "db.py" not in scanned
 # ---------------------------------------------------------------------------
 # close(): idempotence and post-close errors
 # ---------------------------------------------------------------------------
 def test_close_is_idempotent(tmp_path) -> None:
    database, hass = _open_db(tmp_path / "idem.db")
    _run(database.close())
    assert database.connected is False
    _run(database.close())
    assert database.connected is False
    assert hass.async_add_executor_job.await_count == 2
    assert database.rowcount == -1
    assert database.lastrowid is None
 def test_operations_after_close_raise_clear_error(tmp_path) -> None:
    database, _hass = _open_db(tmp_path / "closed.db")
    _run(database.close())
    async def _txn():
        async with database.transaction():
            pass
    with pytest.raises(RuntimeError, match="closed"):
        _run(database.execute("SELECT 1"))
    with pytest.raises(RuntimeError, match="closed"):
        _run(database.fetch_one("SELECT 1"))
    with pytest.raises(RuntimeError, match="closed"):
        _run(database.fetch_all("SELECT 1"))
    with pytest.raises(RuntimeError, match="closed"):
        _run(database.execute_many("SELECT 1", []))
    with pytest.raises(RuntimeError, match="closed"):
        _run(_txn())
 # ---------------------------------------------------------------------------
 # Transactions: commit on success, rollback on exception
 # ---------------------------------------------------------------------------
 def test_transaction_commits_on_success(tmp_path) -> None:
    database, _hass = _open_db(tmp_path / "commit.db")
    _run(database.execute("CREATE TABLE t (id INTEGER PRIMARY KEY, name TEXT)"))
    async def _block():
        async with database.transaction():
            await database.execute("INSERT INTO t (name) VALUES (?)", ("kept",))
    _run(_block())
    assert _run(database.fetch_all("SELECT name FROM t")) == [("kept",)]
    _run(database.close())
 def test_transaction_rolls_back_on_exception(tmp_path) -> None:
    database, _hass = _open_db(tmp_path / "rollback.db")
    _run(database.execute("CREATE TABLE t (id INTEGER PRIMARY KEY, name TEXT)"))
    _run(database.execute("INSERT INTO t (name) VALUES (?)", ("before",)))
    async def _block():
        with pytest.raises(ValueError, match="boom"):
            async with database.transaction():
                await database.execute(
                    "INSERT INTO t (name) VALUES (?)", ("dropped",)
                )
                raise ValueError("boom")
    _run(_block())
    assert _run(database.fetch_all("SELECT name FROM t")) == [("before",)]
    _run(database.close())
 async def test_commit_failure_rolls_back_and_next_transaction_works(
    tmp_path,
 ) -> None:
    """A COMMIT failure rolls back and leaves the wrapper reusable.
    With PRAGMA defer_foreign_keys=ON a violating insert succeeds inside
    the transaction and the foreign-key violation only surfaces at COMMIT.
    The commit error must propagate, the real connection must end up
    outside any transaction, and the next transaction() plus statement
    must run cleanly.
    """
    hass, _registry = make_hass()
    database = NestQuestDatabase(hass)
    await database.open(tmp_path / "deferred.db")
    await database.execute("CREATE TABLE parent (id INTEGER PRIMARY KEY)")
    await database.execute(
        "CREATE TABLE child ("
        "id INTEGER PRIMARY KEY, "
        "parent_id INTEGER NOT NULL REFERENCES parent(id))"
    )
    with pytest.raises(sqlite3.IntegrityError, match="FOREIGN KEY"):
        async with database.transaction():
            await database.execute("PRAGMA defer_foreign_keys=ON")
            await database.execute(
                "INSERT INTO child (parent_id) VALUES (?)", (999,)
            )
    assert database._in_transaction is False
    assert database._tx_task is None
    assert database._conn is not None
    assert database._conn.in_transaction is False
    assert await database.fetch_all("SELECT COUNT(*) FROM child") == [(0,)]
    async with database.transaction():
        await database.execute("INSERT INTO parent (id) VALUES (?)", (1,))
        await database.execute(
            "INSERT INTO child (parent_id) VALUES (?)", (1,)
        )
    assert await database.fetch_all("SELECT COUNT(*) FROM child") == [(1,)]
    await database.close()
 async def test_cancel_during_open_closes_abandoned_connection(tmp_path) -> None:
    """NQ-PR12-010: cancel during open() must close the settled connection.
    The _open executor job is gated: the open task is cancelled while the
    connect is still in flight, then the worker is allowed to complete it
    anyway.  The settled connection must never be adopted, must be closed
    through a settled executor-backed close before the cancellation
    propagates, and a follow-up open() must succeed cleanly.
    """
    hass, _registry = make_hass()
    database = NestQuestDatabase(hass)
    open_started = asyncio.Event()
    release_open = asyncio.Event()
    jobs: list[str] = []
    captured: dict[str, Any] = {}
    real_add = hass.async_add_executor_job
    async def _gated_add_executor_job(fn, *args, **kwargs):
        name = getattr(fn, "__name__", "")
        jobs.append(name)
        if name == "_open":
            open_started.set()
            await release_open.wait()
        result = await real_add(fn, *args, **kwargs)
        if name == "_open":
            captured["conn"] = result
        return result
    hass.async_add_executor_job = _gated_add_executor_job
    open_task = asyncio.create_task(database.open(tmp_path / "cancel-open.db"))
    await open_started.wait()
    open_task.cancel()
    await asyncio.sleep(0.05)
    assert database._conn is None, (
        "the cancelled open adopted a connection that was still settling"
    )
    release_open.set()
    with pytest.raises(asyncio.CancelledError):
        await open_task
    assert database.connected is False
    assert "_close_abandoned" in jobs, (
        "the abandoned connection was not closed via an executor job"
    )
    conn = captured.get("conn")
    assert conn is not None
    def _probe() -> str:
        try:
            conn.execute("SELECT 1")
        except sqlite3.ProgrammingError:
            return "closed"
        return "open"
    assert await real_add(_probe) == "closed"
    hass.async_add_executor_job = real_add
    await database.open(tmp_path / "cancel-open.db")
    assert database.connected is True
    assert await database.fetch_one("SELECT 1") == (1,)
    await database.close()
 async def test_cancel_during_begin_settles_then_rolls_back(tmp_path) -> None:
    """NQ-PR12-007: cancel during BEGIN must not leak a stray transaction.
    The BEGIN executor job is gated: the transaction task is cancelled
    while BEGIN is still in flight, then the worker is allowed to
    complete it anyway.  The wrapper must keep transaction ownership
    until the submitted work settles, roll the completed BEGIN back,
    clear its state, and stay usable for the next transaction.
    """
    hass, _registry = make_hass()
    database = NestQuestDatabase(hass)
    await database.open(tmp_path / "cancel-begin.db")
    await database.execute("CREATE TABLE t (id INTEGER PRIMARY KEY, name TEXT)")
    await database.execute("INSERT INTO t (name) VALUES (?)", ("before",))
    begin_started = asyncio.Event()
    release_begin = asyncio.Event()
    real_add = hass.async_add_executor_job
    async def _gated_add_executor_job(fn, *args, **kwargs):
        if getattr(fn, "__name__", "") == "_begin":
            begin_started.set()
            await release_begin.wait()
        return await real_add(fn, *args, **kwargs)
    hass.async_add_executor_job = _gated_add_executor_job
    async def _txn() -> None:
        async with database.transaction():
            await database.execute("INSERT INTO t (name) VALUES (?)", ("kept",))
    txn_task = asyncio.create_task(_txn())
    await begin_started.wait()
    txn_task.cancel()
    await asyncio.sleep(0.05)
    assert database._in_transaction is True, (
        "ownership was released while the cancelled BEGIN was still in flight"
    )
    release_begin.set()
    with pytest.raises(asyncio.CancelledError):
        await txn_task
    assert database._in_transaction is False
    assert database._tx_task is None
    assert database._conn is not None
    assert database._conn.in_transaction is False
    hass.async_add_executor_job = real_add
    assert await database.fetch_all("SELECT name FROM t ORDER BY id") == [
        ("before",)
    ]
    async with database.transaction():
        await database.execute("INSERT INTO t (name) VALUES (?)", ("kept",))
    assert await database.fetch_all("SELECT name FROM t ORDER BY id") == [
        ("before",),
        ("kept",),
    ]
    await database.close()
 async def test_cancel_during_commit_settles_then_propagates_cancellation(
    tmp_path,
 ) -> None:
    """NQ-PR12-007: cancel during COMMIT must not mask the cancellation.
    COMMIT is gated; the task is cancelled while it is in flight and the
    worker then completes it anyway.  The cancellation must propagate as
    CancelledError (not be converted by a doomed ROLLBACK of an already
    committed transaction), the connection must end outside any
    transaction, and the wrapper must stay usable.
    """
    hass, _registry = make_hass()
    database = NestQuestDatabase(hass)
    await database.open(tmp_path / "cancel-commit.db")
    await database.execute("CREATE TABLE t (id INTEGER PRIMARY KEY, name TEXT)")
    commit_started = asyncio.Event()
    release_commit = asyncio.Event()
    real_add = hass.async_add_executor_job
    async def _gated_add_executor_job(fn, *args, **kwargs):
        if getattr(fn, "__name__", "") == "_commit":
            commit_started.set()
            await release_commit.wait()
        return await real_add(fn, *args, **kwargs)
    hass.async_add_executor_job = _gated_add_executor_job
    async def _txn() -> None:
        async with database.transaction():
            await database.execute("INSERT INTO t (name) VALUES (?)", ("kept",))
    txn_task = asyncio.create_task(_txn())
    await commit_started.wait()
    txn_task.cancel()
    await asyncio.sleep(0.05)
    assert database._in_transaction is True, (
        "ownership was released while the cancelled COMMIT was still in flight"
    )
    release_commit.set()
    with pytest.raises(asyncio.CancelledError):
        await txn_task
    assert database._in_transaction is False
    assert database._tx_task is None
    assert database._conn is not None
    assert database._conn.in_transaction is False
    hass.async_add_executor_job = real_add
    assert await database.fetch_all("SELECT name FROM t") == [("kept",)]
    async with database.transaction():
        await database.execute("INSERT INTO t (name) VALUES (?)", ("again",))
    assert await database.fetch_all("SELECT name FROM t ORDER BY id") == [
        ("kept",),
        ("again",),
    ]
    await database.close()
 async def test_cancel_during_in_transaction_execute_settles_then_rolls_back(
    tmp_path,
 ) -> None:
    """NQ-PR12-009: cancel during an in-transaction statement must not race ROLLBACK.
    The in-transaction INSERT executor job is gated: the transaction task
    is cancelled while the statement is still in flight, then the worker
    is allowed to complete it anyway.  The ROLLBACK must only run after
    the statement has settled — while the statement is in flight the
    transaction is still open — and the cancelled statement's write must
    not persist.
    """
    hass, _registry = make_hass()
    database = NestQuestDatabase(hass)
    await database.open(tmp_path / "cancel-execute.db")
    await database.execute("CREATE TABLE t (id INTEGER PRIMARY KEY, name TEXT)")
    await database.execute("INSERT INTO t (name) VALUES (?)", ("before",))
    insert_started = asyncio.Event()
    release_insert = asyncio.Event()
    real_add = hass.async_add_executor_job
    async def _gated_add_executor_job(fn, *args, **kwargs):
        if getattr(fn, "__name__", "") == "_execute":
            insert_started.set()
            await release_insert.wait()
        return await real_add(fn, *args, **kwargs)
    hass.async_add_executor_job = _gated_add_executor_job
    async def _txn() -> None:
        async with database.transaction():
            await database.execute("INSERT INTO t (name) VALUES (?)", ("lost",))
    txn_task = asyncio.create_task(_txn())
    await insert_started.wait()
    txn_task.cancel()
    await asyncio.sleep(0.05)
    assert database._in_transaction is True, (
        "ownership was released while the cancelled statement was in flight"
    )
    assert database._conn is not None
    assert database._conn.in_transaction is True, (
        "ROLLBACK ran before the cancelled statement settled"
    )
    release_insert.set()
    with pytest.raises(asyncio.CancelledError):
        await txn_task
    assert database._in_transaction is False
    assert database._tx_task is None
    assert database._conn is not None
    assert database._conn.in_transaction is False
    hass.async_add_executor_job = real_add
    assert await database.fetch_all("SELECT name FROM t ORDER BY id") == [
        ("before",)
    ]
    async with database.transaction():
        await database.execute("INSERT INTO t (name) VALUES (?)", ("kept",))
    assert await database.fetch_all("SELECT name FROM t ORDER BY id") == [
        ("before",),
        ("kept",),
    ]
    await database.close()
 async def test_commit_and_rollback_failures_are_both_preserved(tmp_path) -> None:
    """NQ-PR12-008: both errors survive when COMMIT and ROLLBACK both fail.
    The COMMIT executor call is forced to fail, and the follow-up
    cleanup ROLLBACK is forced to fail as well.  The raised error must
    keep both original exceptions inspectable instead of discarding the
    commit failure behind the rollback failure.
    """
    hass, _registry = make_hass()
    database = NestQuestDatabase(hass)
    await database.open(tmp_path / "both-fail.db")
    await database.execute("CREATE TABLE t (id INTEGER PRIMARY KEY)")
    commit_failure = sqlite3.OperationalError("commit exploded")
    rollback_failure = sqlite3.OperationalError("rollback exploded")
    real_add = hass.async_add_executor_job
    async def _failing_add_executor_job(fn, *args, **kwargs):
        name = getattr(fn, "__name__", "")
        if name == "_commit":
            raise commit_failure
        if name == "_cleanup":
            raise rollback_failure
        return await real_add(fn, *args, **kwargs)
    hass.async_add_executor_job = _failing_add_executor_job
    with pytest.raises(BaseExceptionGroup) as exc_info:
        async with database.transaction():
            pass
    assert commit_failure in exc_info.value.exceptions
    assert rollback_failure in exc_info.value.exceptions
    assert database._in_transaction is False
    assert database._tx_task is None
    await database.close()
 async def test_transaction_persists_across_connections(tmp_path) -> None:
    """A committed transaction is visible to a brand-new connection."""
    hass, _registry = make_hass()
    database = NestQuestDatabase(hass)
    await database.open(tmp_path / "persist.db")
    await database.execute("CREATE TABLE t (id INTEGER PRIMARY KEY, name TEXT)")
    async with database.transaction():
        await database.execute("INSERT INTO t (name) VALUES (?)", ("kept",))
    await database.close()
    other = NestQuestDatabase(hass)
    await other.open(tmp_path / "persist.db")
    assert await other.fetch_all("SELECT name FROM t") == [("kept",)]
    await other.close()
 # ---------------------------------------------------------------------------
 # Serialization: one lock per wrapper, no cross-commit interleaving
 # ---------------------------------------------------------------------------
 async def test_concurrent_writers_do_not_interleave_commits(tmp_path) -> None:
    """Real-executor proof: nothing commits inside another transaction's span.
    A transaction runner repeatedly counts rows, inserts, and counts again
    inside BEGIN..COMMIT; the count must grow by exactly one, proving no
    other writer committed in between even though several writer tasks fire
    statements concurrently on real executor threads.  Statements issued
    while a transaction is open queue behind it on the wrapper lock.
    """
    hass, _registry = make_hass()
    database = NestQuestDatabase(hass)
    await database.open(tmp_path / "serial.db")
    await database.execute("CREATE TABLE t (kind TEXT, seq INTEGER)")
    tx_rounds, writers, writer_rounds = 6, 5, 8
    total = tx_rounds + writers * writer_rounds
    async def _tx_runner() -> None:
        for rnd in range(tx_rounds):
            async with database.transaction():
                rows = await database.fetch_all("SELECT COUNT(*) FROM t")
                seen = rows[0][0] if rows else -1
                await database.execute(
                    "INSERT INTO t (kind, seq) VALUES (?, ?)", ("tx", rnd)
                )
                rows = await database.fetch_all("SELECT COUNT(*) FROM t")
                grown = rows[0][0] if rows else -1
                if grown != seen + 1:
                    raise AssertionError(
                        f"interleaved commit: count {seen} -> {grown} "
                        f"in tx round {rnd}"
                    )
    async def _writer(worker: int) -> None:
        for rnd in range(writer_rounds):
            await database.execute(
                "INSERT INTO t (kind, seq) VALUES (?, ?)", ("w", worker)
            )
    await asyncio.gather(
        _tx_runner(), *(_writer(w) for w in range(writers))
    )
    rows = await database.fetch_all("SELECT COUNT(*) FROM t")
    assert rows[0][0] == total, f"expected {total} rows, got {rows[0][0]}"
    await database.close()
 async def test_transaction_span_excludes_concurrent_transaction(tmp_path) -> None:
    """A second transaction caller is rejected with a clear error."""
    hass, _registry = make_hass()
    database = NestQuestDatabase(hass)
    await database.open(tmp_path / "reject.db")
    await database.execute("CREATE TABLE t (id INTEGER)")
    started = asyncio.Event()
    async def _first() -> None:
        async with database.transaction():
            started.set()
            await asyncio.sleep(0.05)
    first = asyncio.create_task(_first())
    await started.wait()
    with pytest.raises(RuntimeError, match="transaction"):
        async with database.transaction():
            pass
    await first
    await database.execute("INSERT INTO t (id) VALUES (?)", (1,))
    assert await database.fetch_one("SELECT id FROM t") == (1,)
    await database.close()
 async def test_transaction_blocks_other_statements_until_commit(tmp_path) -> None:
    """A statement issued during an open transaction queues behind it."""
    hass, _registry = make_hass()
    database = NestQuestDatabase(hass)
    await database.open(tmp_path / "block.db")
    await database.execute("CREATE TABLE t (id INTEGER PRIMARY KEY, name TEXT)")
    order: list[str] = []
    started = asyncio.Event()
    release = asyncio.Event()
    async def _txn() -> None:
        async with database.transaction():
            await database.execute("INSERT INTO t (name) VALUES (?)", ("in_tx",))
            order.append("tx-start")
            started.set()
            await release.wait()
            order.append("tx-end")
    async def _outsider() -> None:
        await started.wait()
        await database.execute("INSERT INTO t (name) VALUES (?)", ("after",))
        order.append("outside")
    txn_task = asyncio.create_task(_txn())
    await started.wait()
    outsider_task = asyncio.create_task(_outsider())
    await asyncio.sleep(0.05)
    assert order == ["tx-start"], f"outsider ran inside transaction: {order}"
    release.set()
    await asyncio.gather(txn_task, outsider_task)
    assert order == ["tx-start", "tx-end", "outside"]
    rows = await database.fetch_all("SELECT name FROM t ORDER BY id")
    assert rows == [("in_tx",), ("after",)]
    await database.close()
 async def test_concurrent_transaction_is_rejected_with_clear_error(
    tmp_path,
 ) -> None:
    """A second concurrent transaction() gets a clear RuntimeError."""
    hass, _registry = make_hass()
    database = NestQuestDatabase(hass)
    await database.open(tmp_path / "concurrent-tx.db")
    await database.execute("CREATE TABLE t (id INTEGER)")
    started = asyncio.Event()
    async def _first() -> None:
        async with database.transaction():
            started.set()
            await asyncio.sleep(0.05)
    first = asyncio.create_task(_first())
    await started.wait()
    with pytest.raises(RuntimeError, match="transaction"):
        async with database.transaction():
            pass
    await first
    await database.close()
 async def test_close_waits_for_in_flight_transaction(tmp_path) -> None:
    """close() serializes: an open transaction finishes before shutdown."""
    hass, _registry = make_hass()
    database = NestQuestDatabase(hass)
    await database.open(tmp_path / "closerace.db")
    await database.execute("CREATE TABLE t (id INTEGER PRIMARY KEY, name TEXT)")
    started = asyncio.Event()
    release = asyncio.Event()
    async def _txn() -> None:
        async with database.transaction():
            await database.execute("INSERT INTO t (name) VALUES (?)", ("kept",))
            started.set()
            await release.wait()
    txn_task = asyncio.create_task(_txn())
    await started.wait()
    close_task = asyncio.create_task(database.close())
    await asyncio.sleep(0.05)
    assert database.connected is True, "close() raced an open transaction"
    release.set()
    await asyncio.gather(txn_task, close_task)
    assert database.connected is False
    witness = NestQuestDatabase(hass)
    await witness.open(tmp_path / "closerace.db")
    assert await witness.fetch_all("SELECT name FROM t") == [("kept",)]
    await witness.close()
 async def test_close_during_transaction_from_same_task_is_rejected(
    tmp_path,
 ) -> None:
    """close() inside its own transaction body raises instead of deadlocking."""
    hass, _registry = make_hass()
    database = NestQuestDatabase(hass)
    await database.open(tmp_path / "selfclose.db")
    await database.execute("CREATE TABLE t (id INTEGER)")
    with pytest.raises(RuntimeError, match="transaction"):
        async with database.transaction():
            await database.close()
    assert database.connected is True
    await database.close()
 # ---------------------------------------------------------------------------
 # rowcount / lastrowid surface
 # ---------------------------------------------------------------------------
 def test_rowcount_and_lastrowid_before_any_operation(tmp_path) -> None:
    database, _hass = _open_db(tmp_path / "unset.db")
    assert database.rowcount == -1
    assert database.lastrowid is None
    _run(database.close())
 def test_execute_exposes_rowcount_and_lastrowid(tmp_path) -> None:
    database, _hass = _open_db(tmp_path / "counts.db")
    _run(database.execute("CREATE TABLE t (id INTEGER PRIMARY KEY, name TEXT)"))
    _run(database.execute("INSERT INTO t (name) VALUES (?)", ("x",)))
    assert database.rowcount == 1
    assert database.lastrowid == 1
    _run(database.execute("INSERT INTO t (name) VALUES (?)", ("y",)))
    assert database.rowcount == 1
    assert database.lastrowid == 2
    _run(database.close())
 def test_execute_many_exposes_rowcount(tmp_path) -> None:
    database, _hass = _open_db(tmp_path / "many.db")
    _run(database.execute("CREATE TABLE t (id INTEGER PRIMARY KEY, name TEXT)"))
    _run(database.execute_many("INSERT INTO t (name) VALUES (?)", [("a",), ("b",)]))
    assert database.rowcount == 2
    _run(database.execute("INSERT INTO t (name) VALUES (?)", ("c",)))
    assert database.lastrowid == 3
    _run(database.close())
 # ---------------------------------------------------------------------------
 # Lifecycle wiring: opened once per entry, closed on unload
 # ---------------------------------------------------------------------------
 async def test_setup_entry_opens_database_in_runtime_data(hass, make_entry) -> None:
    from conftest import wire_entry_to_registry
    from custom_components.nestquest import async_setup_entry, async_unload_entry
    entry = wire_entry_to_registry(make_entry(), hass.registry)
    assert await async_setup_entry(hass, entry) is True
    runtime = entry.runtime_data
    assert runtime.database is not None
    assert runtime.database.connected is True
    await async_unload_entry(hass, entry)
 async def test_unload_entry_closes_database(hass, make_entry) -> None:
    from conftest import wire_entry_to_registry
    from custom_components.nestquest import async_setup_entry, async_unload_entry
    entry = wire_entry_to_registry(make_entry(), hass.registry)
    await async_setup_entry(hass, entry)
    database = entry.runtime_data.database
    assert database.connected is True
    assert await async_unload_entry(hass, entry) is True
    assert database.connected is False
    with pytest.raises(RuntimeError, match="closed"):
        await database.fetch_one("SELECT 1")
 async def test_second_setup_does_not_open_second_connection(hass, make_entry) -> None:
    from conftest import wire_entry_to_registry
    from custom_components.nestquest import async_setup_entry, async_unload_entry
    entry = wire_entry_to_registry(make_entry(), hass.registry)
    await async_setup_entry(hass, entry)
    first = entry.runtime_data.database
    await async_setup_entry(hass, entry)
    second = entry.runtime_data.database
    assert first is second
    assert first.connected is True
    await async_unload_entry(hass, entry)
 async def test_setup_unload_cycles_reopen_fresh_connection(hass, make_entry) -> None:
    from conftest import wire_entry_to_registry
    from custom_components.nestquest import async_setup_entry, async_unload_entry
    entry = wire_entry_to_registry(make_entry(), hass.registry)
    seen: list[NestQuestDatabase] = []
    for _ in range(3):
        await async_setup_entry(hass, entry)
        runtime = entry.runtime_data
        assert runtime.database.connected is True
        seen.append(runtime.database)
        await async_unload_entry(hass, entry)
        assert runtime.database.connected is False
    assert len(set(seen)) == 3
    assert hass.data == {}
@@ -3,8 +3,6 @@ from __future__ import annotations
 import asyncio
 import pytest
 from conftest import make_config_entry, make_hass, wire_entry_to_registry
 from custom_components.nestquest import DOMAIN, async_setup_entry, async_unload_entry
@@ -199,60 +197,3 @@ async def test_setup_awaitable_listener_removal_supported() -> None:
    assert removed == [True]
    assert hass.data == {}
    assert entry.runtime_data is None
 async def test_setup_closes_database_when_listener_registration_fails() -> None:
    """A listener-registration failure must not leak the opened database."""
    from custom_components.nestquest.db import NestQuestDatabase
    class _Boom(Exception):
        pass
    entry = make_config_entry(entry_id="listener-boom")
    entry.add_update_listener = lambda listener: (_ for _ in ()).throw(_Boom())
    hass, _registry = make_hass()
    with pytest.raises(_Boom):
        await async_setup_entry(hass, entry)
    assert hass.data.get(DOMAIN, {}) == {}
    assert not hasattr(entry, "runtime_data")
 async def test_unload_closes_database_even_when_listener_removal_raises() -> None:
    """Unload removes the listener, closes the DB in a finally, then raises."""
    from custom_components.nestquest.db import NestQuestDatabase
    class _RemoveBoom(Exception):
        pass
    def _remover():
        raise _RemoveBoom()
    entry = make_config_entry(entry_id="remove-boom")
    entry.add_update_listener = lambda listener: _remover
    hass, _registry = make_hass()
    await async_setup_entry(hass, entry)
    database = entry.runtime_data.database
    with pytest.raises(_RemoveBoom):
        await async_unload_entry(hass, entry)
    assert database.connected is False, "database leaked despite remover error"
    assert entry.runtime_data is None
    assert DOMAIN not in hass.data
 async def test_setup_applies_all_v1_tables(hass, make_entry) -> None:
    """Setup applies the full v1 DDL: all four v1 tables exist afterwards."""
    entry = _wire(make_entry(), hass.registry)
    assert await async_setup_entry(hass, entry) is True
    database = entry.runtime_data.database
    rows = await database.fetch_all(
        "SELECT name FROM sqlite_master WHERE type = 'table' "
        "AND name IN ('children', 'admin_users', 'schedule_rules', "
        "'task_definitions') ORDER BY name"
    )
    assert [row[0] for row in rows] == [
        "admin_users",
        "children",
        "schedule_rules",
        "task_definitions",
    ]
    await async_unload_entry(hass, entry)
@@ -56,6 +56,24 @@ def test_manifest_requirements(manifest: dict) -> None:
    assert manifest.get("requirements") == []
 def test_manifest_documentation_url_is_public_github_mirror(manifest: dict) -> None:
    """Verify documentation URL points at the public GitHub mirror."""
    assert manifest.get("documentation") == "https://github.com/talon2king/NestQuest"
 def test_manifest_issue_tracker_url_is_public_github_mirror(manifest: dict) -> None:
    """Verify issue_tracker URL points at the public GitHub mirror."""
    assert manifest.get("issue_tracker") == "https://github.com/talon2king/NestQuest/issues"
@pytest.mark.parametrize("key", ["documentation", "issue_tracker"])
 def test_manifest_urls_use_github_com(manifest: dict, key: str) -> None:
    """Verify manifest URLs use github.com rather than the private Gitea host."""
    url = manifest.get(key, "")
    assert url.startswith("https://github.com/talon2king/NestQuest")
    assert "cubecraftlabs" not in url
@pytest.mark.parametrize(
    "key",
    ["name", "version", "documentation", "issue_tracker", "codeowners"],
@@ -1,677 +0,0 @@
 """Tests for migrations.py: the versioned schema migration runner."""
 from __future__ import annotations
 import asyncio
 import hashlib
 import logging
 import sqlite3
 from unittest.mock import AsyncMock
 import pytest
 from custom_components.nestquest.db import NestQuestDatabase
 from custom_components.nestquest.migrations import (
    MIGRATIONS,
    VERSION_TABLE,
    VERSION_TABLE_DDL,
    apply_migrations,
    read_schema_version,
 )
 from custom_components.nestquest.schema import SCHEMA_V1_STATEMENTS
 def _run(coro):
    return asyncio.new_event_loop().run_until_complete(coro)
 def _make_hass_mock():
    from unittest.mock import AsyncMock, MagicMock
    hass = MagicMock()
    hass.async_add_executor_job = AsyncMock(side_effect=(lambda fn, *a: fn(*a)))
    return hass
 def _open_db(path) -> NestQuestDatabase:
    database = NestQuestDatabase(_make_hass_mock())
    _run(database.open(path))
    return database
 def _migrate(database, migrations=MIGRATIONS):
    return _run(apply_migrations(database, migrations))
 def _version(database) -> int:
    return _run(read_schema_version(database))
 def _tables(database) -> set[str]:
    rows = _run(
        database.fetch_all(
            "SELECT name FROM sqlite_master WHERE type = 'table' "
            "AND name NOT LIKE 'sqlite_%'"
        )
    )
    return {row[0] for row in rows}
 def _counts(database) -> dict[str, int]:
    """Row counts for every v1 data table plus the version table."""
    tables = (
        "children",
        "admin_users",
        "schedule_rules",
        "task_definitions",
        "presence_schedules",
        "presence_overrides",
        "task_instances",
        "completion_events",
    )
    counts = {}
    for table in tables:
        row = _run(database.fetch_one(f"SELECT COUNT(*) FROM {table}"))
        counts[table] = row[0]
    return counts
 # ---------------------------------------------------------------------------
 # Fresh file: version 0 -> version 1
 # ---------------------------------------------------------------------------
 def test_fresh_file_migrates_to_version_1(tmp_path) -> None:
    database = _open_db(tmp_path / "fresh.db")
    try:
        final = _migrate(database)
        assert final == 1
        assert _version(database) == 1
    finally:
        _run(database.close())
 def test_fresh_file_migration_creates_all_v1_tables(tmp_path) -> None:
    database = _open_db(tmp_path / "fresh-tables.db")
    try:
        _migrate(database)
        expected = {
            "children",
            "admin_users",
            "schedule_rules",
            "task_definitions",
            "presence_schedules",
            "presence_overrides",
            "task_instances",
            "completion_events",
            VERSION_TABLE,
        }
        assert _tables(database) == expected
    finally:
        _run(database.close())
 def test_missing_version_table_raises_on_direct_read(tmp_path) -> None:
    database = _open_db(tmp_path / "no-table.db")
    try:
        with pytest.raises(sqlite3.OperationalError, match="no such table"):
            _run(read_schema_version(database))
        row = _run(
            database.fetch_one(
                "SELECT name FROM sqlite_master WHERE type = 'table' "
                f"AND name = '{VERSION_TABLE}'"
            )
        )
        assert row is None, "read_schema_version must not create the table"
    finally:
        _run(database.close())
 def test_empty_version_table_reads_as_version_0(tmp_path) -> None:
    database = _open_db(tmp_path / "empty-table.db")
    try:
        _run(database.execute(VERSION_TABLE_DDL))
        assert _version(database) == 0
    finally:
        _run(database.close())
 def test_migration_1_is_the_full_v1_ddl() -> None:
    assert MIGRATIONS[0] == SCHEMA_V1_STATEMENTS
    assert len(MIGRATIONS) == 1
 # ---------------------------------------------------------------------------
 # Idempotence: second start is a no-op
 # ---------------------------------------------------------------------------
 def test_second_run_applies_nothing_and_keeps_rows(tmp_path) -> None:
    database = _open_db(tmp_path / "idempotent.db")
    try:
        _migrate(database)
        _run(
            database.execute(
                "INSERT INTO children (display_name, created_at) VALUES (?, ?)",
                ("Ada", "2026-09-13T00:00:00+00:00"),
            )
        )
        _run(
            database.execute(
                "INSERT INTO admin_users (ha_user_id, added_at) VALUES (?, ?)",
                ("user-1", "2026-09-13T00:00:00+00:00"),
            )
        )
        final = _migrate(database)
        assert final == 1
        assert _version(database) == 1
        assert _counts(database)["children"] == 1
        assert _counts(database)["admin_users"] == 1
    finally:
        _run(database.close())
 def test_second_run_writes_nothing_to_the_file(tmp_path, caplog) -> None:
    database = _open_db(tmp_path / "noop.db")
    try:
        _migrate(database)
        _run(database.execute("PRAGMA wal_checkpoint(TRUNCATE)"))
        db_file = tmp_path / "noop.db"
        wal_file = tmp_path / "noop.db-wal"
        digest_before = hashlib.sha256(db_file.read_bytes()).hexdigest()
        mtime_before = db_file.stat().st_mtime_ns
        wal_digest_before = (
            hashlib.sha256(wal_file.read_bytes()).hexdigest()
            if wal_file.exists()
            else None
        )
        _migrate(database)
        # A no-op run must not open a write transaction: the main
        # database file is byte-identical (content hash, not just size)
        # and untouched (mtime too), and the WAL gained no frames.
        assert db_file.stat().st_mtime_ns == mtime_before
        assert (
            hashlib.sha256(db_file.read_bytes()).hexdigest() == digest_before
        )
        if wal_digest_before is None:
            assert not wal_file.exists()
        else:
            assert (
                hashlib.sha256(wal_file.read_bytes()).hexdigest()
                == wal_digest_before
            )
    finally:
        _run(database.close())
 def test_noop_run_logs_no_migration(caplog, tmp_path) -> None:
    database = _open_db(tmp_path / "noop-log.db")
    try:
        _migrate(database)
        caplog.clear()
        with caplog.at_level(logging.INFO, logger="custom_components.nestquest.migrations"):
            _migrate(database)
        migrated = [
            r for r in caplog.records if "migrated" in r.getMessage().lower()
        ]
        assert migrated == []
    finally:
        _run(database.close())
 def test_noop_run_is_logged_at_debug_level(caplog, tmp_path) -> None:
    database = _open_db(tmp_path / "noop-debug.db")
    try:
        _migrate(database)
        caplog.clear()
        with caplog.at_level(logging.DEBUG, logger="custom_components.nestquest.migrations"):
            _migrate(database)
        assert any(
            "already at version 1" in r.getMessage()
            for r in caplog.records
        )
    finally:
        _run(database.close())
 # ---------------------------------------------------------------------------
 # Failure atomicity: a failing migration leaves the prior version intact
 # ---------------------------------------------------------------------------
 def _failing_migrations(prior_statements: list[str]) -> list[list[str]]:
    """Two-migration list where migration 2 fails mid-application."""
    return [
        prior_statements,
        [
            "CREATE TABLE IF NOT EXISTS later_table (id INTEGER PRIMARY KEY)",
            "CREATE TABLE broken (",
        ],
    ]
 def test_failing_migration_rolls_back_to_prior_version(tmp_path) -> None:
    database = _open_db(tmp_path / "rollback.db")
    try:
        # Manually simulate a database at version 1: apply migration 1's
        # statements directly and stamp version 1 through the runner.
        _migrate(database)
        version_row = _run(
            database.fetch_one(f"SELECT version FROM {VERSION_TABLE}")
        )
        assert version_row == (1,)
        with pytest.raises(sqlite3.OperationalError):
            _migrate(database, _failing_migrations(SCHEMA_V1_STATEMENTS))
        # Version stamp unchanged, statements from the failed migration
        # absent, and the v1 tables untouched.
        assert _version(database) == 1
        assert "later_table" not in _tables(database)
        assert _counts(database)["children"] == 0
        assert _counts(database)["completion_events"] == 0
    finally:
        _run(database.close())
 def test_failing_migration_on_fresh_file_leaves_version_0(tmp_path) -> None:
    database = _open_db(tmp_path / "fresh-rollback.db")
    try:
        # The fresh file still reaches migration 2 (the failing one):
        # migration 1 commits, stamping version 1, before migration 2
        # fails and rolls back.  The run aborts at version 1 — the last
        # fully-applied migration — not at version 0.
        with pytest.raises(sqlite3.OperationalError):
            _migrate(database, _failing_migrations(SCHEMA_V1_STATEMENTS))
        assert _version(database) == 1
        # Migration 2's tables must not survive the failed attempt.
        assert "later_table" not in _tables(database)
        # Migration 1's tables do: it committed before the failure.
        assert "children" in _tables(database)
        assert "task_instances" in _tables(database)
    finally:
        _run(database.close())
 def test_failed_migration_can_be_retried_after_fix(tmp_path) -> None:
    database = _open_db(tmp_path / "retry.db")
    try:
        _migrate(database)
        broken = _failing_migrations(SCHEMA_V1_STATEMENTS)
        with pytest.raises(sqlite3.OperationalError):
            _migrate(database, broken)
        # "Fix" the migration and retry: it now applies cleanly.
        broken[1] = [
            "CREATE TABLE IF NOT EXISTS later_table (id INTEGER PRIMARY KEY)"
        ]
        final = _migrate(database, broken)
        assert final == 2
        assert _version(database) == 2
        assert "later_table" in _tables(database)
    finally:
        _run(database.close())
 def test_failing_migration_error_is_preserved(tmp_path) -> None:
    database = _open_db(tmp_path / "error-preserved.db")
    try:
        with pytest.raises(sqlite3.OperationalError, match="incomplete input"):
            _migrate(database, _failing_migrations(SCHEMA_V1_STATEMENTS))
    finally:
        _run(database.close())
 # ---------------------------------------------------------------------------
 # Version guard rails
 # ---------------------------------------------------------------------------
 def test_future_version_is_rejected(tmp_path) -> None:
    database = _open_db(tmp_path / "future.db")
    try:
        _migrate(database)
        _run(
            database.execute(
                f"UPDATE {VERSION_TABLE} SET version = ?", (99,)
            )
        )
        with pytest.raises(RuntimeError, match="newer than"):
            _migrate(database)
        # Nothing was modified by the rejected run.
        assert _version(database) == 99
    finally:
        _run(database.close())
 def test_non_integer_version_raises_runtime_error(tmp_path) -> None:
    database = _open_db(tmp_path / "string-version.db")
    try:
        _run(database.execute(VERSION_TABLE_DDL))
        # The CHECK enforces version >= 1 but SQLite's loose typing
        # stores TEXT under an INTEGER-affinity column when it cannot
        # be losslessly coerced; guard the read path against it.
        _run(
            database.execute(
                f"INSERT INTO {VERSION_TABLE} (id, version) VALUES (1, ?)",
                ("one",),
            )
        )
        with pytest.raises(RuntimeError, match="not an integer"):
            _run(read_schema_version(database))
    finally:
        _run(database.close())
 def test_negative_version_read_guard_rejects_below_one() -> None:
    """The read path rejects sub-1 versions even if storage missed them.
    The VERSION_TABLE CHECK already makes negative/zero unstorable; this
    exercises the defense-in-depth guard in read_schema_version itself
    against a database stub returning such a row (e.g. a future schema
    relaxing the CHECK, or a hand-edited file).
    """
    from unittest.mock import AsyncMock, MagicMock
    for bad in (-1, 0):
        database = MagicMock()
        database.fetch_one = AsyncMock(return_value=(bad,))
        with pytest.raises(RuntimeError, match="must be >= 1"):
            _run(read_schema_version(database))
 def test_non_integer_version_read_guard_rejects_text() -> None:
    from unittest.mock import AsyncMock, MagicMock
    for bad in ("one", 1.5, True):
        database = MagicMock()
        database.fetch_one = AsyncMock(return_value=(bad,))
        with pytest.raises(RuntimeError, match="not an integer"):
            _run(read_schema_version(database))
 def test_zero_version_row_is_unstorable(tmp_path) -> None:
    """The CHECK makes sub-1 versions impossible through SQL.
    PRAGMA writable_schema does not bypass CHECKs on ordinary tables,
    so the negative/zero read guard above is pure defense-in-depth;
    here we prove the storage-level rule the normal path relies on.
    """
    database = _open_db(tmp_path / "zero-version.db")
    try:
        _run(database.execute(VERSION_TABLE_DDL))
        for bad in (0, -1):
            with pytest.raises(sqlite3.IntegrityError, match="CHECK"):
                _run(
                    database.execute(
                        f"INSERT INTO {VERSION_TABLE} (id, version) "
                        "VALUES (1, ?)",
                        (bad,),
                    )
                )
    finally:
        _run(database.close())
 def test_version_table_enforces_singleton_row(tmp_path) -> None:
    database = _open_db(tmp_path / "singleton.db")
    try:
        _run(database.execute(VERSION_TABLE_DDL))
        _run(
            database.execute(
                f"INSERT INTO {VERSION_TABLE} (id, version) VALUES (1, 1)"
            )
        )
        with pytest.raises(sqlite3.IntegrityError, match="CHECK"):
            _run(
                database.execute(
                    f"INSERT INTO {VERSION_TABLE} (id, version) "
                    "VALUES (2, 1)"
                )
            )
    finally:
        _run(database.close())
 def test_version_table_check_rejects_version_zero_and_negative(
    tmp_path,
 ) -> None:
    database = _open_db(tmp_path / "version-check.db")
    try:
        _run(database.execute(VERSION_TABLE_DDL))
        for bad in (0, -1):
            with pytest.raises(sqlite3.IntegrityError, match="CHECK"):
                _run(
                    database.execute(
                        f"INSERT INTO {VERSION_TABLE} (id, version) "
                        "VALUES (1, ?)",
                        (bad,),
                    )
                )
    finally:
        _run(database.close())
 def test_concurrent_runners_serialize_and_apply_once(tmp_path) -> None:
    """Two concurrent runners on one connection apply each step once.
    Deterministic race: runner B starts while runner A is queued into
    its migration.  A applies everything and stamps; B then acquires
    the migration lock, re-reads the already-stamped version, and
    applies nothing.  Without serialization B's transaction() would
    raise RuntimeError (the wrapper forbids concurrent transactions),
    so this also proves the queueing works.
    """
    migrations = [
        ["CREATE TABLE IF NOT EXISTS step_a (id INTEGER PRIMARY KEY)"],
        ["CREATE TABLE IF NOT EXISTS step_b (id INTEGER PRIMARY KEY)"],
    ]
    results: dict[str, int] = {}
    async def _main(tmp_name: str) -> None:
        database = NestQuestDatabase(_make_hass_mock())
        await database.open(tmp_name)
        try:
            # Start A, yield so it acquires the lock and enters its
            # first transaction, then start B while A is mid-apply.
            task_a = asyncio.ensure_future(
                apply_migrations(database, migrations)
            )
            await asyncio.sleep(0)
            task_b = asyncio.ensure_future(
                apply_migrations(database, migrations)
            )
            results["a"], results["b"] = await asyncio.gather(
                task_a, task_b
            )
            tables = {
                row[0]
                for row in await database.fetch_all(
                    "SELECT name FROM sqlite_master WHERE type = 'table' "
                    "AND name NOT LIKE 'sqlite_%'"
                )
            }
            assert {"step_a", "step_b"} <= tables
            # Each migration's statements ran exactly once: the CREATE
            # IF NOT EXISTS would hide double-runs, so count via sqlite
            # sequence/DDL fingerprint instead — re-check version and
            # that both runners reported the same final version.
            row = await database.fetch_one(
                f"SELECT version FROM {VERSION_TABLE}"
            )
            assert row == (2,)
        finally:
            await database.close()
    loop = asyncio.new_event_loop()
    try:
        loop.run_until_complete(_main(str(tmp_path / "concurrent.db")))
    finally:
        loop.close()
    assert results["a"] == 2
    assert results["b"] == 2
 def test_multiple_pending_migrations_apply_in_order(tmp_path) -> None:
    database = _open_db(tmp_path / "multi-step.db")
    try:
        migrations = [
            ["CREATE TABLE IF NOT EXISTS step_a (id INTEGER PRIMARY KEY)"],
            ["CREATE TABLE IF NOT EXISTS step_b (id INTEGER PRIMARY KEY)"],
            ["CREATE TABLE IF NOT EXISTS step_c (id INTEGER PRIMARY KEY)"],
        ]
        final = _run(apply_migrations(database, migrations))
        assert final == 3
        assert _version(database) == 3
        assert {"step_a", "step_b", "step_c"} <= _tables(database)
    finally:
        _run(database.close())
 def test_partial_pending_migrations_resume_from_current_version(
    tmp_path,
 ) -> None:
    database = _open_db(tmp_path / "resume.db")
    try:
        migrations = [
            ["CREATE TABLE IF NOT EXISTS step_a (id INTEGER PRIMARY KEY)"],
            ["CREATE TABLE IF NOT EXISTS step_b (id INTEGER PRIMARY KEY)"],
        ]
        # Advance to version 1 only, by applying a truncated list of the
        # same migrations: patch apply_migrations' latest by passing a
        # single-migration list first, then the full list.
        _run(apply_migrations(database, migrations[:1]))
        assert _version(database) == 1
        final = _run(apply_migrations(database, migrations))
        assert final == 2
        assert _version(database) == 2
        assert "step_b" in _tables(database)
    finally:
        _run(database.close())
 # ---------------------------------------------------------------------------
 # Startup wiring: setup runs the migration runner, not raw DDL
 # ---------------------------------------------------------------------------
 async def _setup_entry(hass, entry, registry) -> object:
    from tests.test_lifecycle import _wire  # reuse the established wiring
    entry = _wire(entry, registry)
    from custom_components.nestquest import async_setup_entry
    await async_setup_entry(hass, entry)
    return entry
 async def test_setup_entry_migrates_fresh_database(hass, make_entry) -> None:
    import sqlite3 as sqlite3_mod
    from custom_components.nestquest.migrations import read_schema_version
    from custom_components.nestquest.store import async_get_db_path
    entry = await _setup_entry(hass, make_entry(), hass.registry)
    database = entry.runtime_data.database
    assert await read_schema_version(database) == 1
    # The v1 tables exist because the runner applied migration 1.
    names = {
        row[0]
        for row in await database.fetch_all(
            "SELECT name FROM sqlite_master WHERE type = 'table' "
            "AND name NOT LIKE 'sqlite_%'"
        )
    }
    assert "children" in names and "completion_events" in names
    # The version table exists with its singleton row stamped.
    columns = await database.fetch_all(f"PRAGMA table_info({VERSION_TABLE})")
    assert [(row[1], row[3]) for row in columns] == [("id", 1), ("version", 1)]
    db_path = await async_get_db_path(hass)
    conn = sqlite3_mod.connect(db_path)
    try:
        conn.execute(f"INSERT INTO {VERSION_TABLE} (id, version) VALUES (2, 1)")
        violated = False
    except sqlite3_mod.IntegrityError:
        violated = True
    finally:
        conn.close()
    assert violated, "singleton-row CHECK must be live in the setup-created DB"
    await database.close()
 async def test_options_reload_reopens_and_rechecks_version(
    hass, make_entry
 ) -> None:
    """A listener-triggered reload re-runs setup against the same file.
    Unload then setup is the reload lifecycle: the old connection is
    closed, apply_migrations runs again on the persisted file, keeps
    version and rows intact, and the reopened connection is live.
    """
    from custom_components.nestquest import async_unload_entry
    from custom_components.nestquest.migrations import read_schema_version
    entry = await _setup_entry(hass, make_entry(), hass.registry)
    database_a = entry.runtime_data.database
    await database_a.execute(
        "INSERT INTO children (display_name, created_at) VALUES (?, ?)",
        ("Ada", "2026-09-13T00:00:00+00:00"),
    )
    assert await async_unload_entry(hass, entry) is True
    assert not database_a.connected
    entry_reloaded = await _setup_entry(hass, make_entry(), hass.registry)
    database_b = entry_reloaded.runtime_data.database
    assert database_b is not database_a
    try:
        assert await read_schema_version(database_b) == 1
        row = await database_b.fetch_one("SELECT COUNT(*) FROM children")
        assert row == (1,)
    finally:
        await database_b.close()
 async def test_setup_entry_failure_closes_database(
    hass, make_entry, monkeypatch
 ) -> None:
    """A failing migration closes the opened connection before re-raising.
    Spies on the real wrapper instance: records whether close() ran and
    whether the connection is left disconnected, rather than inferring
    from hass.data (a failed setup never stores a runtime record).
    """
    import pytest as pytest_module
    import custom_components.nestquest as nestquest_module
    from custom_components.nestquest.db import NestQuestDatabase
    from custom_components.nestquest.migrations import (
        MIGRATIONS as _MIGRATIONS,
    )
    async def _failing(database, migrations=_MIGRATIONS):
        raise RuntimeError("boom")
    monkeypatch.setattr(
        nestquest_module, "apply_migrations", _failing, raising=True
    )
    close_calls: list[bool] = []
    original_close = NestQuestDatabase.close
    async def _spy_close(self):
        close_calls.append(True)
        return await original_close(self)
    monkeypatch.setattr(NestQuestDatabase, "close", _spy_close)
    entry = make_entry()
    with pytest_module.raises(RuntimeError, match="boom"):
        await _setup_entry(hass, entry, hass.registry)
    assert close_calls == [True], (
        "failed setup must close the connection it opened"
    )
    record = hass.data["nestquest"].get(entry.entry_id)
    assert record is None, "failed setup must not store runtime data"
@@ -100,14 +100,11 @@ def test_executor_helper_creates_directory(tmp_path) -> None:
    assert nested.is_dir()
-def test_no_sqlite3_import_outside_db_module() -> None:
+def test_no_sqlite3_import_in_package() -> None:
    """sqlite3 is banned package-wide except db.py, the connection wrapper."""
    pkg_dir = Path(store.__file__).parent
    py_files = list(pkg_dir.glob("*.py"))
    assert py_files
    for py_file in py_files:
        if py_file.name == "db.py":
            continue
        tree = ast.parse(py_file.read_text(encoding="utf-8"), filename=str(py_file))
        for node in ast.walk(tree):
            if isinstance(node, ast.Import):
Author	SHA1	Message	Date
Joshua King	0f4b53fd4d	docs: correct rollback instructions for the initial release	2026-09-13 19:48:12 -04:00
Joshua King	cb2be413bd	docs: fix release documentation findings (RLS-001..003)	2026-09-13 19:46:34 -04:00