def create_beamtime_schema(conn: sqlite3.Connection) -> None:
    """
    Create all per-beamtime tables, indexes, and pragmas.

    Parameters
    ----------
    conn : sqlite3.Connection
        Open connection to the beamtime SQLite database file.

    Raises
    ------
    sqlite3.DatabaseError
        If DDL execution fails due to a malformed database or I/O error.

    Notes
    -----
    Each beamtime session is stored in its own SQLite `.db` file alongside
    a Zarr store directory. The `image_refs` table holds references into
    the Zarr array (group path and frame index) rather than binary BLOBs,
    keeping the SQLite file small and enabling memory-mapped array access.
    Foreign keys are enforced on every connection via ``PRAGMA foreign_keys = ON``,
    which must be re-applied per connection because SQLite resets it on open.
    """
    conn.executescript(_DDL)
    conn.execute(f"PRAGMA user_version = {BEAMTIME_SCHEMA_VERSION}")
    conn.commit()

def migrate_beamtime_schema(
    conn: sqlite3.Connection, target_version: int = BEAMTIME_SCHEMA_VERSION
) -> None:
    """Apply pending schema migrations up to target_version.

    Parameters
    ----------
    conn : sqlite3.Connection
        Open connection to the beamtime SQLite database file.
    target_version : int, optional
        Schema version to migrate to. Defaults to BEAMTIME_SCHEMA_VERSION.

    Raises
    ------
    RuntimeError
        If target_version is less than the current schema version (downgrade not supported).
    """
    current = conn.execute("PRAGMA user_version").fetchone()[0]
    if current == target_version:
        return
    if current > target_version:
        raise RuntimeError(
            f"Cannot downgrade schema from version {current} to {target_version}."
        )
    if current < 1:
        create_beamtime_schema(conn)
        return
    if current == 1 and target_version >= 2:
        conn.execute("ALTER TABLE image_refs ADD COLUMN compression_codec TEXT")
        conn.execute("PRAGMA user_version = 2")
        conn.commit()
        current = 2
    if current < target_version:
        raise NotImplementedError(
            f"No migration path from schema version {current} to {target_version}. "
            "Schema migrations have not yet been implemented."
        )

def create_index_schema(conn: sqlite3.Connection) -> None:
    """
    Create master index tables, indexes, and pragmas.

    Parameters
    ----------
    conn : sqlite3.Connection
        Open connection to the master index SQLite database file.

    Raises
    ------
    sqlite3.DatabaseError
        If DDL execution fails due to a malformed database or I/O error.

    Notes
    -----
    The master index database aggregates metadata from all per-beamtime
    databases into three tables: ``researchers``, ``beamtimes``, and
    ``runs_index``. This allows cross-beamtime queries without opening
    individual session databases. The ``runs_index`` table mirrors key
    fields from the per-beamtime ``runs`` table, identified by the same
    ``uid``. Foreign keys are enforced via ``PRAGMA foreign_keys = ON``,
    which must be re-applied per connection.
    """
    conn.executescript(_INDEX_DDL)
    conn.execute(f"PRAGMA user_version = {INDEX_SCHEMA_VERSION}")
    conn.commit()

def migrate_index_schema(
    conn: sqlite3.Connection,
    target_version: int = INDEX_SCHEMA_VERSION,
) -> None:
    """
    Apply pending index schema migrations up to target_version.

    Parameters
    ----------
    conn : sqlite3.Connection
        Open connection to the master index SQLite database file.
    target_version : int, optional
        Schema version to migrate to. Defaults to ``INDEX_SCHEMA_VERSION``.

    Raises
    ------
    RuntimeError
        If the database's current ``user_version`` is greater than
        ``target_version``, indicating a downgrade attempt or a database
        written by a newer version of this software.
    NotImplementedError
        If migrations are required (current_version < target_version) but
        no migration path has been implemented yet.

    Notes
    -----
    Migration steps are applied sequentially from current_version + 1 up to
    target_version. Version 1 is the initial schema; future versions will add
    individual ``ALTER TABLE`` or data-transform statements here.
    """
    (current_version,) = conn.execute("PRAGMA user_version").fetchone()
    if current_version == target_version:
        return
    if current_version > target_version:
        raise RuntimeError(
            f"Index schema version {current_version} is newer than "
            f"target version {target_version}. Downgrade is not supported."
        )
    raise NotImplementedError(
        f"No migration path from index schema version {current_version} to {target_version}. "
        "Schema migrations have not yet been implemented."
    )