Add balancing capabilities

If payment are too unbalanced, it’s easier to make a transfer.

1 files changed, 260 insertions, 0 deletions

diff --git a/src/db/balancing.rs b/src/db/balancing.rs
new file mode 100644
index 0000000..1641b97
--- /dev/null
+++ b/src/db/balancing.rs
@@ -0,0 +1,260 @@
+use tokio_rusqlite::{Connection, Row, named_params};
+
+use crate::db::utils;
+use crate::model::balancing::{Balancing, Create, Update, TableRow};
+
+fn row_to_balancing(row: &Row) -> Result<Balancing, rusqlite::Error> {
+    Ok(Balancing {
+        id: row.get(0)?,
+        source: row.get(1)?,
+        destination: row.get(2)?,
+        amount: row.get(3)?,
+    })
+}
+
+fn row_to_table_row(row: &Row) -> Result<TableRow, rusqlite::Error> {
+    Ok(TableRow {
+        id: row.get(0)?,
+        source: row.get(1)?,
+        destination: row.get(2)?,
+        amount: row.get(3)?,
+    })
+}
+
+pub async fn count(conn: &Connection) -> i64 {
+    let query = r#"
+        SELECT COUNT(*)
+        FROM balancing
+        WHERE balancing.deleted_at IS NULL
+    "#;
+
+    let res = conn
+        .call(move |conn| {
+            let mut stmt = conn.prepare(query)?;
+            let mut iter = stmt.query_map([], |row| row.get(0))?;
+            utils::one::<i64, _>(&mut iter)
+        })
+        .await;
+
+    match res {
+        Ok(count) => count,
+        Err(err) => {
+            log::error!("Error counting balancing: {:?}", err);
+            0
+        }
+    }
+}
+
+pub async fn list_for_table(conn: &Connection, page: i64, per_page: i64) -> Vec<TableRow> {
+    let query = r#"
+        SELECT
+            balancing.id,
+            users_src.name,
+            users_dest.name,
+            balancing.amount
+        FROM balancing
+        INNER JOIN users AS users_src ON users_src.id = balancing.source
+        INNER JOIN users AS users_dest ON users_dest.id = balancing.destination
+        WHERE balancing.deleted_at IS NULL
+        ORDER BY balancing.created_at DESC
+        LIMIT :limit
+        OFFSET :offset
+    "#;
+
+    let res = conn
+        .call(move |conn| {
+            let mut stmt = conn.prepare(query)?;
+
+            stmt.query_map(
+                named_params![":limit": per_page, ":offset": (page - 1) * per_page],
+                row_to_table_row
+                )?
+                .collect::<Result<Vec<TableRow>, _>>()
+        })
+        .await;
+
+    match res {
+        Ok(xs) => xs,
+        Err(err) => {
+            log::error!("Error listing balancing: {:?}", err);
+            vec![]
+        }
+    }
+}
+pub async fn list(conn: &Connection) -> Vec<Balancing> {
+    let query = r#"
+        SELECT
+            id,
+            source,
+            destination,
+            amount
+        FROM balancing
+        WHERE deleted_at IS NULL
+        ORDER BY created_at DESC
+    "#;
+
+    let res = conn
+        .call(move |conn| {
+            let mut stmt = conn.prepare(query)?;
+
+            stmt.query_map([], row_to_balancing)?
+                .collect::<Result<Vec<Balancing>, _>>()
+        })
+        .await;
+
+    match res {
+        Ok(xs) => xs,
+        Err(err) => {
+            log::error!("Error listing balancing: {:?}", err);
+            vec![]
+        }
+    }
+}
+
+pub async fn get_row(conn: &Connection, id: i64) -> i64 {
+    let query = r#"
+        SELECT row
+        FROM (
+            SELECT
+                ROW_NUMBER () OVER (ORDER BY created_at DESC) AS row,
+                id
+            FROM balancing
+            WHERE deleted_at IS NULL
+        )
+        WHERE id = :id
+    "#;
+
+    let res = conn
+        .call(move |conn| {
+            let mut stmt = conn.prepare(query)?;
+            let mut iter =
+                stmt.query_map(named_params![":id": id], |row| row.get(0))?;
+            utils::one::<i64, _>(&mut iter)
+        })
+        .await;
+
+    match res {
+        Ok(row) => row,
+        Err(err) => {
+            log::error!("Error getting balancing row: {:?}", err);
+            1
+        }
+    }
+}
+
+pub async fn get(conn: &Connection, id: i64) -> Option<Balancing> {
+    let query = r#"
+        SELECT
+            id,
+            source,
+            destination,
+            amount
+        FROM balancing
+        WHERE
+            id = :id
+            AND deleted_at IS NULL
+    "#;
+
+    let res = conn
+        .call(move |conn| {
+            let mut stmt = conn.prepare(query)?;
+            let mut iter =
+                stmt.query_map(named_params![":id": id], row_to_balancing)?;
+            utils::one(&mut iter)
+        })
+        .await;
+
+    match res {
+        Ok(balancing) => Some(balancing),
+        Err(err) => {
+            log::error!("Error looking for balancing {}: {:?}", id, err);
+            None
+        }
+    }
+}
+
+pub async fn create(conn: &Connection, c: Create) -> Option<i64> {
+    let query = r#"INSERT INTO balancing(source, destination, amount) VALUES (:source, :destination, :amount)"#;
+
+    let res: Result<_, tokio_rusqlite::Error<rusqlite::Error>> = conn
+        .call(move |conn| {
+            conn.execute(
+                query,
+                named_params![
+                    ":source": c.source,
+                    ":destination": c.destination,
+                    ":amount": c.amount,
+                ],
+            )?;
+            Ok(conn.last_insert_rowid())
+        })
+        .await;
+
+    match res {
+        Ok(balancing_id) => Some(balancing_id),
+        Err(err) => {
+            log::error!("Error creating balancing: {:?}", err);
+            None
+        }
+    }
+}
+
+pub async fn update(conn: &Connection, id: i64, c: Update) -> bool {
+    let query = r#"
+        UPDATE balancing
+        SET
+            source = :source,
+            destination = :destination,
+            amount = :amount,
+            updated_at = datetime()
+        WHERE id = :id
+    "#;
+
+    let res = conn
+        .call(move |conn| {
+            conn.execute(
+                query,
+                named_params![
+                    ":source": c.source,
+                    ":destination": c.destination,
+                    ":amount": c.amount,
+                    ":id": id
+                ],
+            )
+        })
+        .await;
+
+    match res {
+        Ok(_) => true,
+        Err(err) => {
+            log::error!("Error updating balancing {}: {:?}", id, err);
+            false
+        }
+    }
+}
+
+pub async fn delete(conn: &Connection, id: i64) -> bool {
+    let res = conn
+        .call(move |conn| {
+            conn.execute(
+                r#"
+            UPDATE
+                balancing
+            SET
+                deleted_at = datetime()
+            WHERE
+                id = :id
+            "#,
+                named_params![":id": id],
+            )
+        })
+        .await;
+
+    match res {
+        Ok(_) => true,
+        Err(err) => {
+            log::error!("Error deleting balancing {}: {:?}", id, err);
+            false
+        }
+    }
+}