//! `presence` — current-location tracking schema.
//!
//! This module owns the durable schema for per-player current place.
//! Movement and transactional APIs are now available for atomic movement
//! transitions with a callback hook.
//!
//! In a **space exploration** game, a row here tracks that a captain is
//! currently at a specific docking node. In a **dungeon crawler**, it
//! tracks which chamber or corridor the player currently occupies. The
//! table intentionally stays minimal so every genre authorship decision
//! remains outside the kit.

use thiserror::Error;

use crate::world_db::{WorldDb, WorldMigration};

/// Per-player current location row shape.
///
/// The table itself is introduced in and consumed by
/// later API tasks:
///
/// - `set_presence(player_id, place_id, metadata_json)` creates/updates
///   this record.
/// - `move_player(player_id, dest_place_id, on_commit)` updates it in a
///   single transaction with movement callbacks.
/// - `get_presence(player_id)` and `players_at(place_id)` read this row
///   ( 5f).
///
/// Both game families are kept in mind:
///
/// - In a **space exploration** game, `player_id` and `place_id` are
///   crew/member and station IDs.
/// - In a **dungeon crawler**, they are player/chamber IDs.
#[derive(Debug, Clone, PartialEq, Eq)]
pub struct PresenceRecord {
    /// Stable player identifier from `players.id`.
    pub player_id: i64,
    /// Place identifier from `places.id` for the current location.
    pub place_id: i64,
    /// Time this presence row was created and last updated by movement.
    pub entered_at: String,
    /// Optional opaque JSON payload for game-authored metadata.
    pub metadata_json: Option<String>,
}

/// Errors for presence write operations.
///
/// The error surface is intentionally explicit so caller code can
/// preserve player-facing diagnostics for failures while still surfacing
/// low-level database details through `#[source]`.
#[derive(Debug, Error)]
pub enum PresenceError {
    /// A presence SQL statement failed (constraint, lock timeout, invalid
    /// foreign key, or corruption).
    #[error("failed to set presence for player `{player_id}`: {source}")]
    SetFailed {
        /// Player identifier supplied to `set_presence`.
        player_id: i64,
        /// Underlying `rusqlite` error with the true SQLite cause.
        #[source]
        source: rusqlite::Error,
    },
    /// A presence query failed while reading a player's row.
    #[error("failed to read presence for player `{player_id}`: {source}")]
    GetFailed {
        /// Player identifier supplied to `get_presence`.
        player_id: i64,
        /// Underlying `rusqlite` error with the true SQLite cause.
        #[source]
        source: rusqlite::Error,
    },
    /// A move was requested for a player that has no existing row.
    #[error("player `{player_id}` has no presence row; call `set_presence` before moving")]
    MissingPresence {
        /// Player identifier supplied to `move_player`.
        player_id: i64,
    },
    /// SQL or transaction failure while moving the player.
    #[error("failed to move player `{player_id}` to place `{place_id}`: {source}")]
    MoveFailed {
        /// Player identifier supplied to `move_player`.
        player_id: i64,
        /// Destination place supplied to `move_player`.
        place_id: i64,
        /// Underlying `rusqlite` error from the update or commit.
        #[source]
        source: rusqlite::Error,
    },
    /// A movement callback rejected or failed.
    #[error(
        "movement callback rejected move of player `{player_id}` to place `{place_id}`: {source}"
    )]
    MoveRejected {
        /// Player identifier supplied to `move_player`.
        player_id: i64,
        /// Destination place supplied to `move_player`.
        place_id: i64,
        /// Underlying callback error surfaced through the `rusqlite::Error` channel.
        #[source]
        source: rusqlite::Error,
    },
    /// A query failed while loading occupants for a place.
    #[error("failed to list occupants for place `{place_id}`: {source}")]
    ListFailed {
        /// Place identifier supplied to `players_at`.
        place_id: i64,
        /// Underlying `rusqlite` error from the SQL execution.
        #[source]
        source: rusqlite::Error,
    },
}

/// Migration for `presence`.
///
/// The schema is intentionally compact:
///
/// - `player_id` is the primary key and one-to-one locator for the
///   current player position.
/// - `place_id` points at the player's current place.
/// - `entered_at` captures when this row became current.
/// - `metadata_json` stores any game-authored payload.
///
/// The `` contract requires this exact column shape; no
/// movement semantics or auto-placement policy live here.
pub const PRESENCE_MIGRATION: WorldMigration = WorldMigration {
    version: 12,
    name: "create_presence",
    sql: "\
CREATE TABLE IF NOT EXISTS presence (\n\
    player_id      INTEGER PRIMARY KEY,\n\
    place_id       INTEGER NOT NULL,\n\
    entered_at     TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,\n\
    metadata_json  TEXT,\n\
    FOREIGN KEY (player_id) REFERENCES players(id),\n\
    FOREIGN KEY (place_id) REFERENCES places(id)\n\
);\n\
",
};

impl WorldDb {
    /// Create or replace a presence row for a player.
    ///
    /// This is the "initial-placement" API:
    ///
    /// - It writes one presence row for a player if absent.
    /// - It refreshes `entered_at` whenever called again, which lets
    ///   callers reuse one helper in both bootstrap and explicit
    ///   re-placement flows.
    ///
    /// Why this method uses UPSERT semantics:
    ///
    /// - It keeps the primitive focused on "set current presence"
    ///   instead of forcing call sites to choose between two write
    ///   paths.
    /// - `place_id` can be reset if a game intentionally remaps a
    ///   player between rooms before movement rules begin (for example.
    ///   moving from a loading room to a dock in a **space exploration**
    ///   flow or from an entry hall to a vault in a **dungeon crawler**
    ///   flow).
    ///
    /// Transactionality note for later tasks:
    ///
    /// This method intentionally writes a single row with `INSERT …
    /// ON CONFLICT... DO UPDATE` and returns the committed row via
    /// `RETURNING`. A future movement wrapper (`move_player`) should
    /// use an explicit `Connection::transaction` to keep callbacks and
    /// presence updates atomic.
    pub fn set_presence(
        &self,
        player_id: i64,
        place_id: i64,
        metadata_json: Option<&str>,
    ) -> Result<PresenceRecord, PresenceError> {
        const SQL: &str = "\
INSERT INTO presence (player_id, place_id, metadata_json, entered_at)\n\
VALUES (?1, ?2, ?3, CURRENT_TIMESTAMP)\n\
ON CONFLICT(player_id) DO UPDATE\n\
SET place_id = excluded.place_id,\n\
    metadata_json = excluded.metadata_json,\n\
    entered_at = CURRENT_TIMESTAMP\n\
RETURNING player_id, place_id, entered_at, metadata_json";

        self.connection()
            .query_row(
                SQL,
                rusqlite::params![player_id, place_id, metadata_json],
                row_to_presence,
            )
            .map_err(|source| PresenceError::SetFailed { player_id, source })
    }

    /// Move a player to a new place inside one SQLite transaction.
    ///
    /// This is the `move_player` primitive. `move_player`
    /// always updates one presence row and passes the post-move snapshot to
    /// `on_commit` so game logic can run additional checks inside the same
    /// transaction.
    ///
    /// - The callback receives the active [`rusqlite::Transaction`]
    ///   plus the canonical post-move presence row.
    /// - If the callback returns `Err`, the `UPDATE` rolls back and this
    ///   method returns [`PresenceError::MoveRejected`].
    /// - The caller gets the committed presence snapshot on success.
    ///
    /// This shape stays genre-neutral:
    ///
    /// - In a **space exploration** game, a callback can append a
    ///   transfer-log row or enforce docking restrictions before commit.
    /// - In a **dungeon crawler** game, the same callback can debit
    ///   movement stamina or log discovered chambers while keeping the
    ///   update atomic.
    pub fn move_player<F>(
        &mut self,
        player_id: i64,
        dest_place_id: i64,
        on_commit: F,
    ) -> Result<PresenceRecord, PresenceError>
    where
        F: FnOnce(&rusqlite::Transaction<'_>, &PresenceRecord) -> Result<(), rusqlite::Error>,
    {
        const SQL: &str = "\
UPDATE presence\n\
SET place_id = ?2,\n\
    entered_at = CURRENT_TIMESTAMP\n\
WHERE player_id = ?1\n\
RETURNING player_id, place_id, entered_at, metadata_json";

        let tx =
            self.connection_mut()
                .transaction()
                .map_err(|source| PresenceError::MoveFailed {
                    player_id,
                    place_id: dest_place_id,
                    source,
                })?;

        let moved = match tx.query_row(
            SQL,
            rusqlite::params![player_id, dest_place_id],
            row_to_presence,
        ) {
            Ok(row) => row,
            Err(rusqlite::Error::QueryReturnedNoRows) => {
                return Err(PresenceError::MissingPresence { player_id });
            }
            Err(source) => {
                return Err(PresenceError::MoveFailed {
                    player_id,
                    place_id: dest_place_id,
                    source,
                });
            }
        };

        on_commit(&tx, &moved).map_err(|source| PresenceError::MoveRejected {
            player_id,
            place_id: dest_place_id,
            source,
        })?;

        tx.commit().map_err(|source| PresenceError::MoveFailed {
            player_id,
            place_id: dest_place_id,
            source,
        })?;

        Ok(moved)
    }

    /// Read the current presence row for one player, if present.
    ///
    /// This is the query:
    ///
    /// - It returns `Ok(Some(...))` when the player has an active
    ///   presence row.
    /// - It returns `Ok(None)` before any initial placement.
    /// - It performs no write side effects so it stays cheap for
    ///   frequent lookup.
    ///
    /// Why this is useful:
    ///
    /// - In a **space exploration** game, UI can resolve a captain's
    ///   current dock without forcing movement.
    /// - In a **dungeon crawler**, a room map can start with `None`
    ///   and defer spawning checks.
    pub fn get_presence(&self, player_id: i64) -> Result<Option<PresenceRecord>, PresenceError> {
        get_presence_on(self.connection(), player_id)
    }

    /// List the players currently present at a place.
    ///
    /// This query is intentionally strict: it returns only rows whose
    /// `presence.place_id` exactly matches the argument at read time.
    /// Because movement is always executed by mutating the row's
    /// `place_id`, occupants that have since moved away are naturally
    /// excluded.
    ///
    /// Why this is useful:
    ///
    /// - In a **space exploration** flow, a station controller can
    ///   render the dock manifest by calling `players_at` for a specific
    ///   `place_id`.
    /// - In a **dungeon crawler** flow, room fog and encounter logic can
    ///   list monsters/players currently in that chamber.
    ///
    /// Deterministic ordering:
    ///
    /// The result is sorted by ascending `player_id`, which keeps callers
    /// from depending on SQLite insertion ordering and makes tests
    /// straightforward.
    pub fn players_at(&self, place_id: i64) -> Result<Vec<PresenceRecord>, PresenceError> {
        const SQL: &str = "\
SELECT player_id, place_id, entered_at, metadata_json\n\
FROM presence\n\
WHERE place_id = ?1\n\
ORDER BY player_id";

        let mut statement = self
            .connection()
            .prepare(SQL)
            .map_err(|source| PresenceError::ListFailed { place_id, source })?;

        let rows = statement
            .query_map(rusqlite::params![place_id], row_to_presence)
            .map_err(|source| PresenceError::ListFailed { place_id, source })?
            .collect::<Result<Vec<_>, _>>()
            .map_err(|source| PresenceError::ListFailed { place_id, source })?;

        Ok(rows)
    }
}

/// Read the current presence row for one player using an existing
/// connection or transaction.
///
/// The function takes `&rusqlite::Connection`, so a
/// `&rusqlite::Transaction` works through deref coercion. It opens no
/// nested transaction and returns `Ok(None)` when the player has not
/// been placed yet.
pub fn get_presence_on(
    conn: &rusqlite::Connection,
    player_id: i64,
) -> Result<Option<PresenceRecord>, PresenceError> {
    const SQL: &str = "\
SELECT player_id, place_id, entered_at, metadata_json\n\
FROM presence\n\
WHERE player_id = ?1";

    match conn.query_row(SQL, rusqlite::params![player_id], row_to_presence) {
        Ok(presence) => Ok(Some(presence)),
        Err(rusqlite::Error::QueryReturnedNoRows) => Ok(None),
        Err(source) => Err(PresenceError::GetFailed { player_id, source }),
    }
}

fn row_to_presence(row: &rusqlite::Row<'_>) -> rusqlite::Result<PresenceRecord> {
    Ok(PresenceRecord {
        player_id: row.get(0)?,
        place_id: row.get(1)?,
        entered_at: row.get(2)?,
        metadata_json: row.get(3)?,
    })
}

#[cfg(test)]
mod tests {
    use super::{row_to_presence, PresenceError, PRESENCE_MIGRATION};
    use crate::foglet::{ContextSource, FogletContext};
    use crate::place_recall::{PlaceRecallRecord, PLACE_RECALL_MIGRATION};
    use crate::players::PLAYERS_MIGRATION;
    use crate::spatial::PLACES_MIGRATION;
    use crate::world_db::WorldDb;
    use std::thread::sleep;
    use std::time::Duration;
    use tempfile::tempdir;

    ///  requires that the `presence` migration applies
    /// and creates the documented columns in table order.
    #[test]
    fn applies_presence_migration_with_documented_columns() {
        let dir = tempdir().expect("tempdir creates");
        let db_path = dir.path().join("world.sqlite");
        let mut world = WorldDb::open(&db_path).expect("open succeeds");

        // Presence references `places`, so we reuse the spatial migration
        // from to keep the FK target available for CREATE TABLE.
        world
            .apply_migration(&PLACES_MIGRATION)
            .expect("places migration applies");
        world
            .apply_migration(&PRESENCE_MIGRATION)
            .expect("presence migration applies");

        let mut stmt = world
            .connection()
            .prepare("SELECT name FROM pragma_table_info('presence') ORDER BY cid")
            .expect("pragma_table_info preparable");
        let columns: Vec<String> = stmt
            .query_map([], |row| row.get::<_, String>(0))
            .expect("query_map runs")
            .collect::<Result<_, _>>()
            .expect("rows decode");
        assert_eq!(
            columns,
            vec![
                "player_id".to_string(),
                "place_id".to_string(),
                "entered_at".to_string(),
                "metadata_json".to_string(),
            ],
            "presence schema must match exactly"
        );

        let row_count: i64 = world
            .connection()
            .query_row(
                "SELECT version FROM world_migrations WHERE name = ?1",
                rusqlite::params![PRESENCE_MIGRATION.name],
                |row| row.get(0),
            )
            .expect("recorded migration row is queryable");
        assert_eq!(row_count, PRESENCE_MIGRATION.version);
    }

    ///  requires a timestamped placement row for the
    /// first call to `set_presence`.
    ///
    /// A valid player and place are required because both foreign-key
    /// constraints exist on `presence`, so the test seeds fixtures
    /// from the adjacent module migrations and then asserts the returned
    /// row is stable and persists in the database.
    #[test]
    fn set_presence_inserts_row_with_timestamp() -> Result<(), PresenceError> {
        let dir = tempdir().expect("tempdir creates");
        let db_path = dir.path().join("world.sqlite");
        let mut world = WorldDb::open(&db_path).expect("open succeeds");

        world
            .apply_migration(&PLAYERS_MIGRATION)
            .expect("players migration applies");
        world
            .apply_migration(&PLACES_MIGRATION)
            .expect("places migration applies");
        world
            .apply_migration(&PRESENCE_MIGRATION)
            .expect("presence migration applies");

        let player_id: i64 = world
            .connection()
            .query_row(
                "INSERT INTO players (handle) VALUES (?1) RETURNING id",
                rusqlite::params!["Captain Quill"],
                |row| row.get(0),
            )
            .expect("fixture player insert works");

        let docking_gate = world
            .insert_place("port-alpha", "Port Alpha", "harbor", None)
            .expect("fixture place insert works");

        let placed = world.set_presence(player_id, docking_gate.id, Some(r#"{"zone":"entry"}"#))?;

        let loaded = world
            .connection()
            .query_row(
                "SELECT player_id, place_id, entered_at, metadata_json FROM presence WHERE player_id = ?1",
                rusqlite::params![player_id],
                row_to_presence,
            )
            .expect("presence row is queryable");

        assert_eq!(placed.player_id, player_id);
        assert_eq!(placed.place_id, docking_gate.id);
        assert_eq!(
            placed.metadata_json,
            Some(r#"{"zone":"entry"}"#.to_string())
        );
        assert!(
            !placed.entered_at.is_empty(),
            "placement must persist a non-empty timestamp"
        );
        assert_eq!(placed, loaded);

        Ok(())
    }

    ///  requires `move_player` to update the player's
    /// current location atomically and refresh `entered_at`.
    ///
    /// This test also proves the callback receives the committed
    /// destination row by asserting destination place and metadata inside
    /// the callback.
    #[test]
    fn move_player_updates_presence_and_entered_at() -> Result<(), PresenceError> {
        let dir = tempdir().expect("tempdir creates");
        let db_path = dir.path().join("world.sqlite");
        let mut world = WorldDb::open(&db_path).expect("open succeeds");

        world
            .apply_migration(&PLAYERS_MIGRATION)
            .expect("players migration applies");
        world
            .apply_migration(&PLACES_MIGRATION)
            .expect("places migration applies");
        world
            .apply_migration(&PRESENCE_MIGRATION)
            .expect("presence migration applies");

        let player_id: i64 = world
            .connection()
            .query_row(
                "INSERT INTO players (handle) VALUES (?1) RETURNING id",
                rusqlite::params!["Captain Quill"],
                |row| row.get(0),
            )
            .expect("fixture player insert works");

        let docking_gate = world
            .insert_place("port-alpha", "Port Alpha", "harbor", None)
            .expect("fixture place insert works");
        let market_square = world
            .insert_place("cargo-hub", "Cargo Hub", "dock", None)
            .expect("fixture place insert works");

        let initial =
            world.set_presence(player_id, docking_gate.id, Some(r#"{"zone":"entry"}"#))?;
        sleep(Duration::from_secs(1));

        let moved = world.move_player(player_id, market_square.id, |_, row| {
            assert_eq!(row.player_id, player_id);
            assert_eq!(row.place_id, market_square.id);
            assert_eq!(row.metadata_json, Some(r#"{"zone":"entry"}"#.to_string()));
            Ok(())
        })?;

        let loaded = world
            .connection()
            .query_row(
                "SELECT player_id, place_id, entered_at, metadata_json FROM presence WHERE player_id = ?1",
                rusqlite::params![player_id],
                row_to_presence,
            )
            .expect("presence row is queryable");

        assert_eq!(moved, loaded);
        assert_eq!(moved.player_id, player_id);
        assert_eq!(moved.place_id, market_square.id);
        assert_eq!(moved.metadata_json, Some(r#"{"zone":"entry"}"#.to_string()));
        assert_ne!(
            moved.entered_at, initial.entered_at,
            "move should refresh entered_at via CURRENT_TIMESTAMP"
        );

        Ok(())
    }

    /// A movement callback rejection must roll back the write, because
    /// `move_player` is defined as an atomic transaction that includes
    /// both the row mutation and the callback side effects.
    ///
    /// This proof is genre-neutral:
    ///
    /// - In a **space exploration** game, a rejected docking authorization
    ///   callback must not teleport a captain even temporarily.
    /// - In a **dungeon crawler** game, a failed "trap check" should not
    ///   alter the player's chamber just because the callback failed.
    #[test]
    fn move_player_rolls_back_when_callback_rejects() {
        let dir = tempdir().expect("tempdir creates");
        let db_path = dir.path().join("world.sqlite");
        let mut world = WorldDb::open(&db_path).expect("open succeeds");

        world
            .apply_migration(&PLAYERS_MIGRATION)
            .expect("players migration applies");
        world
            .apply_migration(&PLACES_MIGRATION)
            .expect("places migration applies");
        world
            .apply_migration(&PRESENCE_MIGRATION)
            .expect("presence migration applies");

        let player_id: i64 = world
            .connection()
            .query_row(
                "INSERT INTO players (handle) VALUES (?1) RETURNING id",
                rusqlite::params!["Captain Quill"],
                |row| row.get(0),
            )
            .expect("fixture player insert works");

        let docking_gate = world
            .insert_place("port-alpha", "Port Alpha", "harbor", None)
            .expect("fixture place insert works");
        let market_square = world
            .insert_place("cargo-hub", "Cargo Hub", "dock", None)
            .expect("fixture place insert works");

        let initial = world
            .set_presence(player_id, docking_gate.id, Some(r#"{"zone":"entry"}"#))
            .expect("initial presence row exists");
        sleep(Duration::from_secs(1));

        let reject_result = world.move_player(player_id, market_square.id, |_, _| {
            Err(rusqlite::Error::InvalidQuery)
        });
        match reject_result {
            Err(PresenceError::MoveRejected {
                player_id: row_id, ..
            }) => {
                assert_eq!(row_id, player_id);
            }
            _ => panic!("expected a move-reject error"),
        }

        let loaded = world
            .connection()
            .query_row(
                "SELECT player_id, place_id, entered_at, metadata_json FROM presence WHERE player_id = ?1",
                rusqlite::params![player_id],
                row_to_presence,
            )
            .expect("presence row is queryable");

        assert_eq!(loaded.player_id, player_id);
        assert_eq!(loaded.place_id, docking_gate.id);
        assert_eq!(
            loaded.metadata_json,
            Some(r#"{"zone":"entry"}"#.to_string())
        );
        assert_eq!(loaded.entered_at, initial.entered_at);
    }

    ///  requires `move_player` to remain a pure presence
    /// transition unless the caller explicitly invokes place recall.
    ///
    /// This test writes a recall row for the source place, executes a move
    /// with a no-op callback, and then confirms recall metadata is
    /// byte-identical and still has the same row cardinality.
    ///
    /// This keeps the kit genre-neutral:
    ///
    /// - A **space exploration** captain can move through waypoints
    ///   without forcing a fog-map update unless the game explicitly
    ///   touches recall on movement.
    /// - A **dungeon crawler** hero can walk through chambers while
    ///   leaving per-room fog entries unchanged unless the encounter layer
    ///   decides to register a touch.
    #[test]
    fn move_player_does_not_auto_touch_recall() {
        let dir = tempdir().expect("tempdir creates");
        let db_path = dir.path().join("world.sqlite");
        let mut world = WorldDb::open(&db_path).expect("open succeeds");

        world
            .apply_migration(&PLAYERS_MIGRATION)
            .expect("players migration applies");
        world
            .apply_migration(&PLACES_MIGRATION)
            .expect("places migration applies");
        world
            .apply_migration(&PRESENCE_MIGRATION)
            .expect("presence migration applies");
        world
            .apply_migration(&PLACE_RECALL_MIGRATION)
            .expect("place_recall migration applies");

        let player_id: i64 = world
            .connection()
            .query_row(
                "INSERT INTO players (handle) VALUES (?1) RETURNING id",
                rusqlite::params!["Captain Quill"],
                |row| row.get(0),
            )
            .expect("fixture player insert works");

        let docking_gate = world
            .insert_place("port-alpha", "Port Alpha", "harbor", None)
            .expect("fixture place insert works");
        let cargo_bay = world
            .insert_place("cargo-bay", "Cargo Bay", "hub", None)
            .expect("fixture place insert works");

        world
            .set_presence(player_id, docking_gate.id, Some(r#"{"zone":"entry"}"#))
            .expect("initial presence exists before move");

        let before_recall = world
            .touch_recall(player_id, docking_gate.id, Some(r#"{"vision":"baseline"}"#))
            .expect("explicit recall touch pre-populates memory");

        let before_total: i64 = world
            .connection()
            .query_row(
                "SELECT COUNT(*) FROM place_recall WHERE player_id = ?1",
                rusqlite::params![player_id],
                |row| row.get(0),
            )
            .expect("recall row count is queryable");
        let before_destination: Option<PlaceRecallRecord> =
            query_recall(world.connection(), player_id, cargo_bay.id);

        world
            .move_player(player_id, cargo_bay.id, |_, _| Ok(()))
            .expect("move_player runs without touching recall");

        let after_recall = query_recall(world.connection(), player_id, docking_gate.id)
            .expect("existing source recall row still exists");
        let after_total: i64 = world
            .connection()
            .query_row(
                "SELECT COUNT(*) FROM place_recall WHERE player_id = ?1",
                rusqlite::params![player_id],
                |row| row.get(0),
            )
            .expect("recall row count is queryable");

        assert_eq!(before_recall, after_recall);
        assert_eq!(before_total, after_total);
        assert!(
            before_destination.is_none(),
            "move should not create destination recall without explicit touch_recall"
        );
    }

    ///  proves the read path and the optional return:
    /// players that have never been placed report `None`.
    ///
    /// This keeps the primitive intentionally genre-neutral:
    ///
    /// - A **space exploration** captain can be created without a
    ///   starting station until story setup assigns one.
    /// - A **dungeon crawler** hero can exist in persistence before
    ///   entering the first room or chamber.
    #[test]
    fn get_presence_returns_none_for_unplaced_player() -> Result<(), PresenceError> {
        let dir = tempdir().expect("tempdir creates");
        let db_path = dir.path().join("world.sqlite");
        let mut world = WorldDb::open(&db_path).expect("open succeeds");

        world
            .apply_migration(&PLAYERS_MIGRATION)
            .expect("players migration applies");
        world
            .apply_migration(&PLACES_MIGRATION)
            .expect("places migration applies");
        world
            .apply_migration(&PRESENCE_MIGRATION)
            .expect("presence migration applies");

        let player_id: i64 = world
            .connection()
            .query_row(
                "INSERT INTO players (handle) VALUES (?1) RETURNING id",
                rusqlite::params!["Captain Quill"],
                |row| row.get(0),
            )
            .expect("fixture player insert works");

        let none = world.get_presence(player_id)?;
        assert!(
            none.is_none(),
            "fresh players should start without presence"
        );

        Ok(())
    }

    #[test]
    fn transaction_scoped_presence_lookup_observes_rollback() -> Result<(), PresenceError> {
        let dir = tempdir().expect("tempdir creates");
        let db_path = dir.path().join("world.sqlite");
        let mut world = WorldDb::open(&db_path).expect("open succeeds");

        world
            .apply_migration(&PLAYERS_MIGRATION)
            .expect("players migration applies");
        world
            .apply_migration(&PLACES_MIGRATION)
            .expect("places migration applies");
        world
            .apply_migration(&PRESENCE_MIGRATION)
            .expect("presence migration applies");

        let player_id: i64 = world
            .connection()
            .query_row(
                "INSERT INTO players (handle) VALUES (?1) RETURNING id",
                rusqlite::params!["Scout Vale"],
                |row| row.get(0),
            )
            .expect("fixture player insert works");
        let origin = world
            .insert_place("origin-dock", "Origin Dock", "dock", None)
            .expect("origin place insert works");
        let destination = world
            .insert_place("destination-vault", "Destination Vault", "vault", None)
            .expect("destination place insert works");

        world.set_presence(player_id, origin.id, None)?;

        {
            let tx = world
                .connection_mut()
                .transaction()
                .expect("transaction begins");
            tx.execute(
                "UPDATE presence SET place_id = ?1 WHERE player_id = ?2",
                rusqlite::params![destination.id, player_id],
            )
            .expect("presence update inside transaction succeeds");

            let in_tx = super::get_presence_on(&tx, player_id)?
                .expect("presence should be visible inside transaction");
            assert_eq!(in_tx.place_id, destination.id);
        }

        let after_rollback = world
            .get_presence(player_id)?
            .expect("presence should still exist after rollback");
        assert_eq!(
            after_rollback.place_id, origin.id,
            "dropped transaction should roll back the presence move"
        );

        Ok(())
    }

    /// A freshly upserted player must not be auto-placed.
    ///
    ///  keeps player creation and presence orthogonal:
    /// `upsert_player` writes the `players` table only, and
    /// explicit `set_presence` calls own the initial location decision.
    ///
    /// This test is genre-neutral:
    ///
    /// - In a **space exploration** game, the captain can persist
    ///   first and wait for launch placement at a docking node.
    /// - In a **dungeon crawler**, the hero can exist in shared
    ///   storage before being dropped into the first chamber.
    #[test]
    fn upsert_player_does_not_auto_create_presence_row() -> Result<(), PresenceError> {
        let dir = tempdir().expect("tempdir creates");
        let db_path = dir.path().join("world.sqlite");
        let mut world = WorldDb::open(&db_path).expect("open succeeds");

        world
            .apply_migration(&PLAYERS_MIGRATION)
            .expect("players migration applies");
        world
            .apply_migration(&PLACES_MIGRATION)
            .expect("places migration applies");
        world
            .apply_migration(&PRESENCE_MIGRATION)
            .expect("presence migration applies");

        let player = world
            .upsert_player(&FogletContext {
                door_id: "space-dock-01".to_string(),
                user_id: Some("user-captain-quill".to_string()),
                username: Some("Captain Quill".to_string()),
                role: Some("user".to_string()),
                session_id: Some("session-9f0f".to_string()),
                terminal_width: 80,
                terminal_height: 24,
                source: ContextSource::LocalDev,
            })
            .expect("upsert creates the player row");

        let none_before = world.get_presence(player.id)?;
        assert!(
            none_before.is_none(),
            "freshly upserted players should not auto-place"
        );

        let place = world
            .insert_place("dock-alpha", "Dock Alpha", "harbor", None)
            .expect("fixture place insert works");
        let placed = world.set_presence(player.id, place.id, Some(r#"{"zone":"depot"}"#))?;
        assert_eq!(placed.player_id, player.id);
        assert_eq!(placed.place_id, place.id);

        let present = world
            .get_presence(player.id)?
            .expect("presence should exist after explicit placement");
        assert_eq!(present.place_id, place.id);

        Ok(())
    }

    ///  proves occupant enumeration reflects the current place only:
    ///
    /// 1) Only players whose presence row is currently at the queried
    ///    place are returned.
    /// 2) A moved player is not included in the old place's occupancy.
    /// 3) Returned occupants are ordered by ascending `player_id`.
    #[test]
    fn players_at_returns_only_current_occupants() -> Result<(), PresenceError> {
        let dir = tempdir().expect("tempdir creates");
        let db_path = dir.path().join("world.sqlite");
        let mut world = WorldDb::open(&db_path).expect("open succeeds");

        world
            .apply_migration(&PLAYERS_MIGRATION)
            .expect("players migration applies");
        world
            .apply_migration(&PLACES_MIGRATION)
            .expect("places migration applies");
        world
            .apply_migration(&PRESENCE_MIGRATION)
            .expect("presence migration applies");

        let player_a: i64 = world
            .connection()
            .query_row(
                "INSERT INTO players (handle) VALUES (?1) RETURNING id",
                rusqlite::params!["Captain Quill"],
                |row| row.get(0),
            )
            .expect("fixture player insert works");
        let player_b: i64 = world
            .connection()
            .query_row(
                "INSERT INTO players (handle) VALUES (?1) RETURNING id",
                rusqlite::params!["Captain Finch"],
                |row| row.get(0),
            )
            .expect("fixture player insert works");
        let player_c: i64 = world
            .connection()
            .query_row(
                "INSERT INTO players (handle) VALUES (?1) RETURNING id",
                rusqlite::params!["Captain Mora"],
                |row| row.get(0),
            )
            .expect("fixture player insert works");

        let docking_gate = world
            .insert_place("port-alpha", "Port Alpha", "harbor", None)
            .expect("fixture place insert works");
        let cargo_bay = world
            .insert_place("cargo-bay", "Cargo Bay", "hub", None)
            .expect("fixture place insert works");
        let vault = world
            .insert_place("crypt", "Crypt", "vault", None)
            .expect("fixture place insert works");

        world.set_presence(player_a, docking_gate.id, None)?;
        world.set_presence(player_b, docking_gate.id, None)?;
        world.set_presence(player_c, vault.id, None)?;

        world.move_player(player_b, cargo_bay.id, |_, _| Ok(()))?;

        let gate_occupants = world.players_at(docking_gate.id)?;
        let cargo_occupants = world.players_at(cargo_bay.id)?;
        let vault_occupants = world.players_at(vault.id)?;

        assert_eq!(gate_occupants.len(), 1);
        assert_eq!(cargo_occupants.len(), 1);
        assert_eq!(vault_occupants.len(), 1);
        assert_eq!(gate_occupants[0].player_id, player_a);
        assert_eq!(cargo_occupants[0].player_id, player_b);
        assert_eq!(vault_occupants[0].player_id, player_c);

        let gate_ids: Vec<i64> = gate_occupants.iter().map(|row| row.player_id).collect();
        let cargo_ids: Vec<i64> = cargo_occupants.iter().map(|row| row.player_id).collect();
        let vault_ids: Vec<i64> = vault_occupants.iter().map(|row| row.player_id).collect();

        assert_eq!(gate_ids, vec![player_a]);
        assert_eq!(cargo_ids, vec![player_b]);
        assert_eq!(vault_ids, vec![player_c]);

        Ok(())
    }

    fn query_recall(
        connection: &rusqlite::Connection,
        player_id: i64,
        place_id: i64,
    ) -> Option<PlaceRecallRecord> {
        connection
            .query_row(
                "SELECT player_id, place_id, first_seen_at, last_seen_at, snapshot_json FROM place_recall WHERE player_id = ?1 AND place_id = ?2",
                rusqlite::params![player_id, place_id],
                |row| {
                    Ok(PlaceRecallRecord {
                        player_id: row.get(0)?,
                        place_id: row.get(1)?,
                        first_seen_at: row.get(2)?,
                        last_seen_at: row.get(3)?,
                        snapshot_json: row.get(4)?,
                    })
                },
            )
            .ok()
    }
}
