use super::{BusserStateId, DbBusserState, SqlitePool, TIMESTAMP_FMT, Utc};

#[derive(Clone)]
/// Repository for busser key-value state (cursors, tokens, pagination markers)
pub struct StateRepository {
    pool: SqlitePool,
}

impl StateRepository {
    /// Create a new state repository backed by the given pool.
    #[tracing::instrument(skip_all)]
    pub fn new(pool: SqlitePool) -> Self {
        Self { pool }
    }

    /// Get a single state value for a busser by key.
    #[tracing::instrument(skip_all)]
    pub async fn get(&self, busser_id: &str, key: &str) -> Result<Option<String>, sqlx::Error> {
        let row: Option<(String,)> =
            sqlx::query_as("SELECT value FROM busser_state WHERE busser_id = ?1 AND key = ?2")
                .bind(busser_id)
                .bind(key)
                .fetch_optional(&self.pool)
                .await?;
        Ok(row.map(|(v,)| v))
    }

    /// Set a state value, inserting or updating on conflict.
    /// Uses upsert on the `(busser_id, key)` composite unique constraint so
    /// callers don't need to check existence first.
    #[tracing::instrument(skip_all)]
    pub async fn set(&self, busser_id: &str, key: &str, value: &str) -> Result<(), sqlx::Error> {
        let id = BusserStateId::new();
        let now = Utc::now().format(TIMESTAMP_FMT).to_string();
        sqlx::query(
            r"
            INSERT INTO busser_state (id, busser_id, key, value, created_at, updated_at)
            VALUES (?1, ?2, ?3, ?4, ?5, ?5)
            ON CONFLICT (busser_id, key) DO UPDATE SET value = EXCLUDED.value, updated_at = EXCLUDED.updated_at
            ",
        )
        .bind(id)
        .bind(busser_id)
        .bind(key)
        .bind(value)
        .bind(&now)
        .execute(&self.pool)
        .await?;
        Ok(())
    }

    /// Delete a single state entry by busser ID and key.
    #[tracing::instrument(skip_all)]
    pub async fn delete(&self, busser_id: &str, key: &str) -> Result<(), sqlx::Error> {
        sqlx::query("DELETE FROM busser_state WHERE busser_id = ?1 AND key = ?2")
            .bind(busser_id)
            .bind(key)
            .execute(&self.pool)
            .await?;
        Ok(())
    }

    /// Delete all state entries for a busser. Returns the number of rows removed.
    #[tracing::instrument(skip_all)]
    pub async fn delete_all(&self, busser_id: &str) -> Result<u64, sqlx::Error> {
        let result = sqlx::query("DELETE FROM busser_state WHERE busser_id = ?1")
            .bind(busser_id)
            .execute(&self.pool)
            .await?;
        Ok(result.rows_affected())
    }

    /// List all state entries for a busser, ordered by key.
    #[tracing::instrument(skip_all)]
    pub async fn list(&self, busser_id: &str) -> Result<Vec<DbBusserState>, sqlx::Error> {
        sqlx::query_as("SELECT * FROM busser_state WHERE busser_id = ?1 ORDER BY key")
            .bind(busser_id)
            .fetch_all(&self.pool)
            .await
    }
}