use crate::db::Database;
use crate::models::User;

impl Database {
    /// Creates the account and consumes its invite atomically.
    pub async fn create_user_with_invite(&self, user: &User, invite: &str) -> Result<bool, String> {
        let conn = self.conn().await?;
        conn.execute("BEGIN IMMEDIATE", ())
            .await
            .map_err(|e| e.to_string())?;

        let result: Result<bool, String> = async {
            let mut rows = conn
                .query("SELECT 1 FROM invites WHERE token = ?1", turso::params![invite])
                .await
                .map_err(|e| e.to_string())?;
            if rows.next().await.map_err(|e| e.to_string())?.is_none() {
                return Ok(false);
            }

            conn.execute(
                "INSERT INTO users (id, username, email, password_hash, created_at) VALUES (?1, ?2, ?3, ?4, ?5)",
                turso::params![
                    user.id.clone(),
                    user.username.clone(),
                    user.email.clone(),
                    user.password_hash.clone(),
                    user.created_at.clone()
                ],
            )
            .await
            .map_err(|e| e.to_string())?;
            conn.execute(
                "DELETE FROM invites WHERE token = ?1",
                turso::params![invite],
            )
            .await
            .map_err(|e| e.to_string())?;
            Ok(true)
        }
        .await;

        match result {
            Ok(true) => {
                conn.execute("COMMIT", ())
                    .await
                    .map_err(|e| e.to_string())?;
                Ok(true)
            }
            Ok(false) => {
                conn.execute("ROLLBACK", ())
                    .await
                    .map_err(|e| e.to_string())?;
                Ok(false)
            }
            Err(error) => {
                conn.execute("ROLLBACK", ())
                    .await
                    .map_err(|rollback| format!("{error} (rollback also failed: {rollback})"))?;
                Err(error)
            }
        }
    }

    pub async fn get_user_by_username(&self, username: &str) -> Result<Option<User>, String> {
        let mut rows = self
            .conn()
            .await?
            .query(
                "SELECT id, username, email, password_hash, created_at FROM users WHERE username = ?1",
                turso::params![username],
            )
            .await
            .map_err(|e| e.to_string())?;

        Self::row_to_user(rows.next().await.map_err(|e| e.to_string())?)
    }

    pub async fn get_user_by_id(&self, user_id: &str) -> Result<Option<User>, String> {
        let mut rows = self
            .conn()
            .await?
            .query(
                "SELECT id, username, email, password_hash, created_at FROM users WHERE id = ?1",
                turso::params![user_id],
            )
            .await
            .map_err(|e| e.to_string())?;

        Self::row_to_user(rows.next().await.map_err(|e| e.to_string())?)
    }

    fn row_to_user(row: Option<turso::Row>) -> Result<Option<User>, String> {
        match row {
            Some(row) => Ok(Some(User {
                id: row.get(0).map_err(|e| e.to_string())?,
                username: row.get(1).map_err(|e| e.to_string())?,
                email: row.get(2).map_err(|e| e.to_string())?,
                password_hash: row.get(3).map_err(|e| e.to_string())?,
                created_at: row.get(4).map_err(|e| e.to_string())?,
            })),
            None => Ok(None),
        }
    }
}

#[cfg(test)]
mod tests {
    use super::*;

    async fn setup_db() -> (Database, String) {
        let db_path = format!("/tmp/test_pear_users_{}.db", uuid::Uuid::new_v4());
        let db = Database::new(&db_path);
        db.init().await.expect("init");
        (db, db_path)
    }

    fn test_user() -> User {
        User {
            id: uuid::Uuid::new_v4().to_string(),
            username: "testuser".to_string(),
            email: Some("test@example.com".to_string()),
            password_hash: "hash".to_string(),
            created_at: chrono::Utc::now().to_rfc3339(),
        }
    }

    #[tokio::test]
    async fn test_create_and_get_user() {
        let (db, db_path) = setup_db().await;
        let user = test_user();
        db.create_invite("test-invite")
            .await
            .expect("create invite");
        assert!(
            db.create_user_with_invite(&user, "test-invite")
                .await
                .expect("create user")
        );

        let by_name = db.get_user_by_username("testuser").await.unwrap();
        assert_eq!(by_name.as_ref().map(|u| &u.id), Some(&user.id));

        let by_id = db.get_user_by_id(&user.id).await.unwrap();
        assert_eq!(by_id.map(|u| u.username), Some("testuser".to_string()));

        let _ = std::fs::remove_file(&db_path);
    }

    #[tokio::test]
    async fn test_get_missing_user_is_none() {
        let (db, db_path) = setup_db().await;
        assert!(db.get_user_by_username("nobody").await.unwrap().is_none());
        let _ = std::fs::remove_file(&db_path);
    }
}