Rewrite in Rust

7 files changed, 1360 insertions, 0 deletions

diff --git a/src/db/categories.rs b/src/db/categories.rs
new file mode 100644
index 0000000..05b1323
--- /dev/null
+++ b/src/db/categories.rs
@@ -0,0 +1,132 @@
+use sqlx::sqlite::SqlitePool;
+
+use crate::model::category::{Category, Create, Update};
+
+pub async fn list(pool: &SqlitePool) -> Vec<Category> {
+    let res = sqlx::query_as::<_, Category>(
+        r#"
+SELECT
+    id,
+    name,
+    color
+FROM
+    categories
+WHERE
+    deleted_at IS NULL
+ORDER BY
+    name
+    "#,
+    )
+    .fetch_all(pool)
+    .await;
+
+    match res {
+        Ok(categories) => categories,
+        Err(err) => {
+            error!("Error listing categories: {:?}", err);
+            vec![]
+        }
+    }
+}
+
+pub async fn get(pool: &SqlitePool, id: i64) -> Option<Category> {
+    let query = r#"
+SELECT
+    id,
+    name,
+    color
+FROM
+    categories
+WHERE
+    id = ?
+    AND deleted_at IS NULL
+    "#;
+
+    let res = sqlx::query_as::<_, Category>(query)
+        .bind(id)
+        .fetch_one(pool)
+        .await;
+
+    match res {
+        Ok(p) => Some(p),
+        Err(err) => {
+            error!("Error looking for category {}: {:?}", id, err);
+            None
+        }
+    }
+}
+
+pub async fn create(pool: &SqlitePool, c: &Create) -> Option<i64> {
+    let res = sqlx::query(
+        r#"
+INSERT INTO
+    categories(name, color, created_at)
+VALUES
+    (?, ?, datetime())
+    "#,
+    )
+    .bind(c.name.clone())
+    .bind(c.color.clone())
+    .execute(pool)
+    .await;
+
+    match res {
+        Ok(x) => Some(x.last_insert_rowid()),
+        Err(err) => {
+            error!("Error creating category: {:?}", err);
+            None
+        }
+    }
+}
+
+pub async fn update(pool: &SqlitePool, id: i64, c: &Update) -> bool {
+    let res = sqlx::query(
+        r#"
+UPDATE
+    categories
+SET
+    name = ?,
+    color = ?,
+    updated_at = datetime()
+WHERE
+    id = ?
+    "#,
+    )
+    .bind(c.name.clone())
+    .bind(c.color.clone())
+    .bind(id)
+    .execute(pool)
+    .await;
+
+    match res {
+        Ok(_) => true,
+        Err(err) => {
+            error!("Error updating category {}: {:?}", id, err);
+            false
+        }
+    }
+}
+
+pub async fn delete(pool: &SqlitePool, id: i64) -> bool {
+    let res = sqlx::query(
+        r#"
+UPDATE
+    categories
+SET
+    deleted_at = datetime()
+WHERE
+    id = ?
+    "#,
+    )
+    .bind(id)
+    .execute(pool)
+    .await;
+
+    match res {
+        Ok(_) => true,
+        Err(err) => {
+            error!("Error deleting category {}: {:?}", id, err);
+            false
+        }
+    }
+}
diff --git a/src/db/incomes.rs b/src/db/incomes.rs
new file mode 100644
index 0000000..cbbfce7
--- /dev/null
+++ b/src/db/incomes.rs
@@ -0,0 +1,494 @@
+use chrono::NaiveDate;
+use sqlx::error::Error;
+use sqlx::sqlite::{SqlitePool, SqliteRow};
+use sqlx_core::row::Row;
+use std::collections::HashMap;
+use std::iter::FromIterator;
+
+use crate::model::income::{Create, Form, Stat, Table, Update};
+use crate::model::report::Report;
+
+pub async fn count(pool: &SqlitePool) -> i64 {
+    let query = r#"
+SELECT
+    COUNT(*) AS count
+FROM
+    incomes
+WHERE
+    incomes.deleted_at IS NULL
+        "#;
+
+    let res = sqlx::query(&query)
+        .map(|row: SqliteRow| row.get("count"))
+        .fetch_one(pool)
+        .await;
+
+    match res {
+        Ok(count) => count,
+        Err(err) => {
+            error!("Error counting incomes: {:?}", err);
+            0
+        }
+    }
+}
+
+pub async fn list(pool: &SqlitePool, page: i64, per_page: i64) -> Vec<Table> {
+    let query = r#"
+SELECT
+    incomes.id,
+    users.name AS user,
+    strftime('%m/%Y', incomes.date) AS date,
+    incomes.amount
+FROM
+    incomes
+INNER JOIN
+    users
+ON
+    incomes.user_id = users.id
+WHERE
+    incomes.deleted_at IS NULL
+ORDER BY
+    incomes.date DESC
+LIMIT ?
+OFFSET ?
+    "#;
+
+    let res = sqlx::query_as::<_, Table>(query)
+        .bind(per_page)
+        .bind((page - 1) * per_page)
+        .fetch_all(pool)
+        .await;
+
+    match res {
+        Ok(incomes) => incomes,
+        Err(err) => {
+            error!("Error listing incomes: {:?}", err);
+            vec![]
+        }
+    }
+}
+
+pub async fn get_row(pool: &SqlitePool, id: i64) -> i64 {
+    let query = r#"
+SELECT
+    row
+FROM (
+    SELECT 
+        ROW_NUMBER () OVER (ORDER BY date DESC) AS row,
+        id
+    FROM
+        incomes
+    WHERE
+        deleted_at IS NULL
+)
+WHERE
+    id = ?
+    "#;
+
+    let res = sqlx::query(query)
+        .bind(id)
+        .map(|row: SqliteRow| row.get("row"))
+        .fetch_one(pool)
+        .await;
+
+    match res {
+        Ok(count) => count,
+        Err(err) => {
+            error!("Error getting income row: {:?}", err);
+            1
+        }
+    }
+}
+
+pub async fn get(pool: &SqlitePool, id: i64) -> Option<Form> {
+    let query = r#"
+SELECT
+    id,
+    amount,
+    user_id,
+    CAST(strftime('%m', date) AS INTEGER) as month,
+    CAST(strftime('%Y', date) AS INTEGER) as year
+FROM
+    incomes
+WHERE
+    id = ?
+    AND deleted_at IS NULL
+    "#;
+
+    let res = sqlx::query_as::<_, Form>(query)
+        .bind(id)
+        .fetch_one(pool)
+        .await;
+
+    match res {
+        Ok(p) => Some(p),
+        Err(err) => {
+            error!("Error looking for income {}: {:?}", id, err);
+            None
+        }
+    }
+}
+
+pub async fn create(pool: &SqlitePool, i: &Create) -> Option<i64> {
+    let res = sqlx::query(
+        r#"
+INSERT INTO
+    incomes(user_id, date, amount, created_at)
+VALUES
+    (?, ?, ?, datetime())
+    "#,
+    )
+    .bind(i.user_id)
+    .bind(NaiveDate::from_ymd(i.year, i.month, 1))
+    .bind(i.amount)
+    .execute(pool)
+    .await;
+
+    match res {
+        Ok(x) => Some(x.last_insert_rowid()),
+        Err(err) => {
+            error!("Error creating income: {:?}", err);
+            None
+        }
+    }
+}
+
+pub async fn defined_at(
+    pool: &SqlitePool,
+    user_id: i64,
+    month: u32,
+    year: i32,
+) -> Vec<i64> {
+    let query = r#"
+SELECT
+    id
+FROM
+    incomes
+WHERE
+    user_id = ?
+    AND date = ?
+    AND deleted_at IS NULL
+    "#;
+
+    let res = sqlx::query(&query)
+        .bind(user_id)
+        .bind(NaiveDate::from_ymd(year, month, 1))
+        .map(|row: SqliteRow| row.get("id"))
+        .fetch_all(pool)
+        .await;
+
+    match res {
+        Ok(ids) => ids,
+        Err(Error::RowNotFound) => vec![],
+        Err(err) => {
+            error!("Error looking if income is defined: {:?}", err);
+            vec![]
+        }
+    }
+}
+
+pub async fn update(pool: &SqlitePool, id: i64, i: &Update) -> bool {
+    let res = sqlx::query(
+        r#"
+UPDATE
+    incomes
+SET
+    user_id = ?,
+    date = ?,
+    amount = ?,
+    updated_at = datetime()
+WHERE
+    id = ?
+    "#,
+    )
+    .bind(i.user_id)
+    .bind(NaiveDate::from_ymd(i.year, i.month, 1))
+    .bind(i.amount)
+    .bind(id)
+    .execute(pool)
+    .await;
+
+    match res {
+        Ok(_) => true,
+        Err(err) => {
+            error!("Error updating income {}: {:?}", id, err);
+            false
+        }
+    }
+}
+
+pub async fn delete(pool: &SqlitePool, id: i64) -> bool {
+    let res = sqlx::query(
+        r#"
+UPDATE
+    incomes
+SET
+    deleted_at = datetime()
+WHERE
+    id = ?
+    "#,
+    )
+    .bind(id)
+    .execute(pool)
+    .await;
+
+    match res {
+        Ok(_) => true,
+        Err(err) => {
+            error!("Error deleting income {}: {:?}", id, err);
+            false
+        }
+    }
+}
+
+pub async fn defined_for_all(pool: &SqlitePool) -> Option<NaiveDate> {
+    let res = sqlx::query(
+        r#"
+SELECT
+    (CASE COUNT(users.id) == COUNT(min_income.date)
+        WHEN 1 THEN MIN(min_income.date)
+        ELSE NULL
+    END) AS date
+FROM
+    users
+LEFT OUTER JOIN (
+    SELECT
+        user_id,
+        MIN(date) AS date
+    FROM
+        incomes
+    WHERE
+        deleted_at IS NULL
+    GROUP BY
+        user_id
+) min_income
+ON
+    users.id = min_income.user_id;
+    "#,
+    )
+    .map(|row: SqliteRow| row.get("date"))
+    .fetch_one(pool)
+    .await;
+
+    match res {
+        Ok(d) => d,
+        Err(err) => {
+            error!("Error looking for incomes defined for all: {:?}", err);
+            None
+        }
+    }
+}
+
+pub async fn cumulative(
+    pool: &SqlitePool,
+    from: NaiveDate,
+) -> HashMap<i64, i64> {
+    let res = sqlx::query(&cumulative_query(from))
+        .map(|row: SqliteRow| (row.get("user_id"), row.get("income")))
+        .fetch_all(pool)
+        .await;
+
+    match res {
+        Ok(incomes) => HashMap::from_iter(incomes),
+        Err(err) => {
+            error!("Error computing cumulative income: {:?}", err);
+            HashMap::new()
+        }
+    }
+}
+
+/// Select cumulative income of users from the given date and until now.
+///
+/// Associate each month income to its start and end bounds,
+/// then compute the total income of each period,
+/// sum it to get the final result.
+///
+/// Considering each month to be 365 / 12 days long.
+fn cumulative_query(from: NaiveDate) -> String {
+    format!(
+        r#"
+SELECT
+    users.id AS user_id,
+    COALESCE(incomes.income, 0) AS income
+FROM
+    users
+LEFT OUTER JOIN (
+    SELECT
+        user_id,
+        CAST(ROUND(SUM(count)) AS INTEGER) AS income
+    FROM (
+        SELECT
+            I1.user_id,
+            ((JULIANDAY(MIN(I2.date)) - JULIANDAY(I1.date)) * I1.amount * 12 / 365) AS count
+        FROM
+            ({}) AS I1
+        INNER JOIN
+            ({}) AS I2
+        ON
+            I2.date > I1.date
+            AND I2.user_id == I1.user_id
+        GROUP BY
+            I1.date, I1.user_id
+    )
+    GROUP BY
+        user_id
+) incomes
+ON
+    users.id = incomes.user_id
+    "#,
+        bounded_query(">".to_string(), from.format("%Y-%m-%d").to_string()),
+        bounded_query("<".to_string(), "date()".to_string())
+    )
+}
+
+/// Select bounded incomes to the operator and date.
+///
+/// It filters incomes according to the operator and date,
+/// and adds the income at this date.
+fn bounded_query(op: String, date: String) -> String {
+    format!(
+        r#"
+SELECT
+    user_id,
+    date,
+    amount
+FROM (
+    SELECT
+        user_id,
+        {} AS date,
+        amount,
+        MAX(date) AS max_date
+    FROM
+        incomes
+    WHERE
+        date <= {}
+        AND deleted_at IS NULL
+    GROUP BY
+        user_id
+) UNION
+SELECT
+    user_id,
+    date,
+    amount
+FROM
+    incomes
+WHERE
+    date {} {}
+    AND deleted_at IS NULL
+    "#,
+        date, date, op, date
+    )
+}
+
+/// Select total income each month.
+///
+/// For each month, from the first defined income and until now,
+/// compute the total income of the users.
+pub async fn total_each_month(pool: &SqlitePool) -> Vec<Stat> {
+    let query = r#"
+WITH RECURSIVE dates(date) AS (
+    VALUES((
+        SELECT
+            strftime('%Y-%m-01', MIN(date))
+        FROM
+            incomes
+        WHERE
+            deleted_at IS NULL
+    ))
+    UNION ALL
+    SELECT
+        date(date, '+1 month')
+    FROM
+        dates
+    WHERE
+        date < date(date(), '-1 month')
+)
+SELECT
+    strftime('%Y-%m-01', dates.date) AS date,
+    (
+        SELECT
+            SUM(amount) AS amount
+        FROM (
+            SELECT (
+                SELECT
+                    amount
+                FROM
+                    incomes
+                WHERE
+                    user_id = users.id
+                    AND date < date(dates.date, '+1 month')
+                    AND deleted_at IS NULL
+                ORDER BY
+                    date DESC
+                LIMIT
+                    1
+            ) AS amount
+            FROM
+                users
+        )
+    ) AS amount
+FROM
+    dates;
+    "#;
+
+    let res = sqlx::query_as::<_, Stat>(query).fetch_all(pool).await;
+
+    match res {
+        Ok(xs) => xs,
+        Err(err) => {
+            error!("Error listing incomes for statistics: {:?}", err);
+            vec![]
+        }
+    }
+}
+
+pub async fn last_week(pool: &SqlitePool) -> Vec<Report> {
+    let query = r#"
+SELECT
+    strftime('%m/%Y', incomes.date) AS date,
+    users.name AS name,
+    incomes.amount AS amount,
+    (CASE
+        WHEN
+            incomes.deleted_at IS NOT NULL
+        THEN
+            'Deleted'
+        WHEN
+            incomes.updated_at IS NOT NULL
+            AND incomes.created_at < date('now', 'weekday 0', '-13 days')
+        THEN
+            'Updated'
+        ELSE
+            'Created'
+    END) AS action
+FROM
+    incomes
+INNER JOIN
+    users
+ON
+    incomes.user_id = users.id
+WHERE
+    (
+        incomes.created_at >= date('now', 'weekday 0', '-13 days')
+        AND incomes.created_at < date('now', 'weekday 0', '-6 days')
+    ) OR (
+        incomes.updated_at >= date('now', 'weekday 0', '-13 days')
+        AND incomes.updated_at < date('now', 'weekday 0', '-6 days')
+    ) OR (
+        incomes.deleted_at >= date('now', 'weekday 0', '-13 days')
+        AND incomes.deleted_at < date('now', 'weekday 0', '-6 days')
+    )
+ORDER BY
+    incomes.date
+    "#;
+
+    let res = sqlx::query_as::<_, Report>(query).fetch_all(pool).await;
+
+    match res {
+        Ok(payments) => payments,
+        Err(err) => {
+            error!("Error listing payments for report: {:?}", err);
+            vec![]
+        }
+    }
+}
diff --git a/src/db/jobs.rs b/src/db/jobs.rs
new file mode 100644
index 0000000..88c2005
--- /dev/null
+++ b/src/db/jobs.rs
@@ -0,0 +1,56 @@
+use sqlx::error::Error;
+use sqlx::sqlite::SqlitePool;
+
+use crate::model::job::Job;
+
+pub async fn should_run(pool: &SqlitePool, job: Job) -> bool {
+    let run_from = match job {
+        Job::WeeklyReport => "date('now', 'weekday 0', '-6 days')",
+        Job::MonthlyPayment => "date('now', 'start of month')",
+    };
+
+    let query = format!(
+        r#"
+SELECT
+    1
+FROM
+    jobs
+WHERE
+    name = ?
+    AND last_execution < {}
+    "#,
+        run_from
+    );
+
+    let res = sqlx::query(&query).bind(job).fetch_one(pool).await;
+
+    match res {
+        Ok(_) => true,
+        Err(Error::RowNotFound) => false,
+        Err(err) => {
+            error!("Error looking if job should run: {:?}", err);
+            false
+        }
+    }
+}
+
+pub async fn actualize_last_execution(pool: &SqlitePool, job: Job) -> () {
+    let query = r#"
+UPDATE
+    jobs
+SET
+    last_execution = datetime()
+WHERE
+    name = ?
+    "#;
+
+    let res = sqlx::query(query).bind(job).execute(pool).await;
+
+    match res {
+        Ok(_) => (),
+        Err(err) => {
+            error!("Error actualizing job last execution: {:?}", err);
+            ()
+        }
+    }
+}
diff --git a/src/db/mod.rs b/src/db/mod.rs
new file mode 100644
index 0000000..a0aa3dc
--- /dev/null
+++ b/src/db/mod.rs
@@ -0,0 +1,6 @@
+pub mod categories;
+pub mod incomes;
+pub mod jobs;
+pub mod payments;
+pub mod users;
+mod utils;
diff --git a/src/db/payments.rs b/src/db/payments.rs
new file mode 100644
index 0000000..0197375
--- /dev/null
+++ b/src/db/payments.rs
@@ -0,0 +1,525 @@
+use sqlx::error::Error;
+use sqlx::sqlite::{Sqlite, SqliteArguments};
+use sqlx::sqlite::{SqlitePool, SqliteRow};
+use sqlx::FromRow;
+use sqlx_core::row::Row;
+use std::collections::HashMap;
+use std::iter::FromIterator;
+
+use crate::db::utils;
+use crate::model::frequency::Frequency;
+use crate::model::payment;
+use crate::model::report::Report;
+use crate::queries;
+use crate::utils::text;
+
+#[derive(FromRow)]
+pub struct Count {
+    pub count: i64,
+    pub total_cost: i64,
+}
+
+pub async fn count(
+    pool: &SqlitePool,
+    payment_query: &queries::Payments,
+) -> Count {
+    let search = payment_query.search.clone().unwrap_or("".to_string());
+
+    let query = format!(
+        r#"
+SELECT
+    COUNT(*) AS count,
+    SUM(payments.cost) AS total_cost
+FROM
+    payments
+INNER JOIN
+    users ON users.id = payments.user_id
+INNER JOIN
+    categories ON categories.id = payments.category_id
+WHERE
+    payments.deleted_at IS NULL
+    AND payments.frequency = ?
+    {}
+        "#,
+        search_query(search.clone())
+    );
+
+    let res = bind_search(
+        sqlx::query_as::<_, Count>(&query)
+            .bind(payment_query.frequency.unwrap_or(Frequency::Punctual)),
+        search,
+    )
+    .fetch_one(pool)
+    .await;
+
+    match res {
+        Ok(count) => count,
+        Err(err) => {
+            error!("Error counting payments: {:?}", err);
+            Count {
+                count: 0,
+                total_cost: 0,
+            }
+        }
+    }
+}
+
+pub async fn list_for_table(
+    pool: &SqlitePool,
+    payment_query: &queries::Payments,
+    per_page: i64,
+) -> Vec<payment::Table> {
+    let offset = (payment_query.page.unwrap_or(1) - 1) * per_page;
+    let search = payment_query.search.clone().unwrap_or("".to_string());
+
+    let query = format!(
+        r#"
+SELECT
+    payments.id,
+    payments.name,
+    payments.cost,
+    users.name AS user,
+    categories.name AS category_name,
+    categories.color AS category_color,
+    strftime('%d/%m/%Y', date) AS date,
+    payments.frequency AS frequency
+FROM
+    payments
+INNER JOIN
+    users ON users.id = payments.user_id
+INNER JOIN
+    categories ON categories.id = payments.category_id
+WHERE
+    payments.deleted_at IS NULL
+    AND payments.frequency = ?
+    {}
+ORDER BY
+    payments.date DESC
+LIMIT ?
+OFFSET ?
+        "#,
+        search_query(search.clone())
+    );
+
+    let res = bind_search(
+        sqlx::query_as::<_, payment::Table>(&query)
+            .bind(payment_query.frequency.unwrap_or(Frequency::Punctual)),
+        search,
+    )
+    .bind(per_page)
+    .bind(offset)
+    .fetch_all(pool)
+    .await;
+
+    match res {
+        Ok(payments) => payments,
+        Err(err) => {
+            error!("Error listing payments: {:?}", err);
+            vec![]
+        }
+    }
+}
+
+fn search_query(search: String) -> String {
+    let payments_name = utils::format_key_for_search("payments.name");
+    let users_name = utils::format_key_for_search("users.name");
+    let categories_name = utils::format_key_for_search("categories.name");
+
+    search
+        .split_ascii_whitespace()
+        .map(|_| {
+            format!(
+                r#"
+AND (
+    {} LIKE ?
+    OR payments.cost LIKE ?
+    OR {} LIKE ?
+    OR {} LIKE ?
+    OR strftime('%d/%m/%Y', date) LIKE ?
+)
+        "#,
+                payments_name, users_name, categories_name
+            )
+        })
+        .collect::<Vec<String>>()
+        .join(" ")
+}
+
+fn bind_search<'a, Row: FromRow<'a, SqliteRow>>(
+    query: sqlx::query::QueryAs<'a, Sqlite, Row, SqliteArguments<'a>>,
+    search: String,
+) -> sqlx::query::QueryAs<'a, Sqlite, Row, SqliteArguments<'a>> {
+    search.split_ascii_whitespace().fold(query, |q, word| {
+        let s = format!("%{}%", text::format_search(&word.to_string()));
+        q.bind(s.clone())
+            .bind(s.clone())
+            .bind(s.clone())
+            .bind(s.clone())
+            .bind(s.clone())
+    })
+}
+
+pub async fn list_for_stats(pool: &SqlitePool) -> Vec<payment::Stat> {
+    let query = r#"
+SELECT
+    strftime('%Y-%m-01', payments.date) AS start_date,
+    SUM(payments.cost) AS cost,
+    payments.category_id AS category_id
+FROM
+    payments
+WHERE
+    payments.deleted_at IS NULL
+    AND payments.frequency = 'Punctual'
+GROUP BY
+    start_date,
+    payments.category_id;
+    "#;
+
+    let result = sqlx::query_as::<_, payment::Stat>(query)
+        .fetch_all(pool)
+        .await;
+
+    match result {
+        Ok(payments) => payments,
+        Err(err) => {
+            error!("Error listing payments for statistics: {:?}", err);
+            vec![]
+        }
+    }
+}
+
+pub async fn get_row(pool: &SqlitePool, id: i64, frequency: Frequency) -> i64 {
+    let query = r#"
+SELECT
+    row
+FROM (
+    SELECT
+        ROW_NUMBER () OVER (ORDER BY date DESC) AS row,
+        id
+    FROM
+        payments
+    WHERE
+        deleted_at IS NULL
+        AND frequency = ?
+)
+WHERE
+    id = ?
+    "#;
+
+    let res = sqlx::query(query)
+        .bind(frequency)
+        .bind(id)
+        .map(|row: SqliteRow| row.get("row"))
+        .fetch_one(pool)
+        .await;
+
+    match res {
+        Ok(count) => count,
+        Err(err) => {
+            error!("Error getting payment row: {:?}", err);
+            1
+        }
+    }
+}
+
+pub async fn get_for_form(pool: &SqlitePool, id: i64) -> Option<payment::Form> {
+    let query = r#"
+SELECT
+    id,
+    name,
+    cost,
+    user_id,
+    category_id,
+    strftime('%Y-%m-%d', date) AS date,
+    frequency AS frequency
+FROM
+    payments
+WHERE
+    id = ?
+    AND deleted_at IS NULL
+    "#;
+
+    let res = sqlx::query_as::<_, payment::Form>(query)
+        .bind(id)
+        .fetch_one(pool)
+        .await;
+
+    match res {
+        Ok(p) => Some(p),
+        Err(err) => {
+            error!("Error looking for payment {}: {:?}", id, err);
+            None
+        }
+    }
+}
+
+pub async fn create(pool: &SqlitePool, p: &payment::Create) -> Option<i64> {
+    let res = sqlx::query(
+        r#"
+INSERT INTO
+    payments(name, cost, user_id, category_id, date, frequency, created_at)
+VALUES
+    (?, ?, ?, ?, ?, ?, datetime())
+    "#,
+    )
+    .bind(p.name.clone())
+    .bind(p.cost)
+    .bind(p.user_id)
+    .bind(p.category_id)
+    .bind(p.date)
+    .bind(p.frequency)
+    .execute(pool)
+    .await;
+
+    match res {
+        Ok(x) => Some(x.last_insert_rowid()),
+        Err(err) => {
+            error!("Error creating payment: {:?}", err);
+            None
+        }
+    }
+}
+
+pub async fn update(pool: &SqlitePool, id: i64, p: &payment::Update) -> bool {
+    let res = sqlx::query(
+        r#"
+UPDATE
+    payments
+SET
+    name = ?,
+    cost = ?,
+    user_id = ?,
+    category_id = ?,
+    date = ?,
+    updated_at = datetime()
+WHERE
+    id = ?
+    "#,
+    )
+    .bind(p.name.clone())
+    .bind(p.cost)
+    .bind(p.user_id)
+    .bind(p.category_id)
+    .bind(p.date)
+    .bind(id)
+    .execute(pool)
+    .await;
+
+    match res {
+        Ok(_) => true,
+        Err(err) => {
+            error!("Error updating payment {}: {:?}", id, err);
+            false
+        }
+    }
+}
+
+pub async fn delete(pool: &SqlitePool, id: i64) -> bool {
+    let res = sqlx::query(
+        r#"
+UPDATE
+    payments
+SET
+    deleted_at = datetime()
+WHERE
+    id = ?
+    "#,
+    )
+    .bind(id)
+    .execute(pool)
+    .await;
+
+    match res {
+        Ok(_) => true,
+        Err(err) => {
+            error!("Error deleting payment {}: {:?}", id, err);
+            false
+        }
+    }
+}
+
+pub async fn search_category(
+    pool: &SqlitePool,
+    payment_name: String,
+) -> Option<i64> {
+    let query = format!(
+        r#"
+SELECT
+    category_id
+FROM
+    payments
+WHERE
+    deleted_at IS NULL
+    AND {} LIKE ?
+ORDER BY
+    updated_at, created_at
+        "#,
+        utils::format_key_for_search("name")
+    );
+
+    let res = sqlx::query(&query)
+        .bind(text::format_search(&format!("%{}%", payment_name)))
+        .map(|row: SqliteRow| row.get("category_id"))
+        .fetch_one(pool)
+        .await;
+
+    match res {
+        Ok(category) => Some(category),
+        Err(Error::RowNotFound) => None,
+        Err(err) => {
+            error!(
+                "Error looking for the category of {}: {:?}",
+                payment_name, err
+            );
+            None
+        }
+    }
+}
+
+pub async fn is_category_used(pool: &SqlitePool, category_id: i64) -> bool {
+    let query = r#"
+SELECT
+    1
+FROM
+    payments
+WHERE
+    category_id = ?
+    AND deleted_at IS NULL
+LIMIT
+    1
+    "#;
+
+    let res = sqlx::query(&query).bind(category_id).fetch_one(pool).await;
+
+    match res {
+        Ok(_) => true,
+        Err(Error::RowNotFound) => false,
+        Err(err) => {
+            error!(
+                "Error looking if category {} is used: {:?}",
+                category_id, err
+            );
+            false
+        }
+    }
+}
+
+pub async fn repartition(pool: &SqlitePool) -> HashMap<i64, i64> {
+    let query = r#"
+SELECT
+    users.id AS user_id,
+    COALESCE(payments.sum, 0) AS sum
+FROM
+    users
+LEFT OUTER JOIN (
+    SELECT
+        user_id,
+        SUM(cost) AS sum
+    FROM
+        payments
+    WHERE
+        deleted_at IS NULL
+        AND frequency = 'Punctual'
+    GROUP BY
+        user_id
+) payments
+ON
+    users.id = payments.user_id"#;
+
+    let res = sqlx::query(&query)
+        .map(|row: SqliteRow| (row.get("user_id"), row.get("sum")))
+        .fetch_all(pool)
+        .await;
+
+    match res {
+        Ok(costs) => HashMap::from_iter(costs),
+        Err(err) => {
+            error!("Error getting payments repartition: {:?}", err);
+            HashMap::new()
+        }
+    }
+}
+
+pub async fn create_monthly_payments(pool: &SqlitePool) -> () {
+    let query = r#"
+INSERT INTO
+    payments(name, cost, user_id, category_id, date, frequency, created_at)
+SELECT
+    name,
+    cost,
+    user_id,
+    category_id,
+    date() AS date,
+    'Punctual' AS frequency,
+    datetime() AS created_at
+FROM
+    payments
+WHERE
+    frequency = 'Monthly'
+    AND deleted_at IS NULL
+    "#;
+
+    let res = sqlx::query(query).execute(pool).await;
+
+    match res {
+        Ok(_) => (),
+        Err(err) => {
+            error!("Error creating monthly payments: {:?}", err);
+            ()
+        }
+    }
+}
+
+pub async fn last_week(pool: &SqlitePool) -> Vec<Report> {
+    let query = r#"
+SELECT
+    strftime('%d/%m/%Y', payments.date) AS date,
+    (payments.name || ' (' || users.name || ')') AS name,
+    payments.cost AS amount,
+    (CASE
+        WHEN
+            payments.deleted_at IS NOT NULL
+        THEN
+            'Deleted'
+        WHEN
+            payments.updated_at IS NOT NULL
+            AND payments.created_at < date('now', 'weekday 0', '-13 days')
+        THEN
+            'Updated'
+        ELSE
+            'Created'
+    END) AS action
+FROM
+    payments
+INNER JOIN
+    users
+ON
+    payments.user_id = users.id
+WHERE
+    payments.frequency = 'Punctual'
+    AND (
+        (
+            payments.created_at >= date('now', 'weekday 0', '-13 days')
+            AND payments.created_at < date('now', 'weekday 0', '-6 days')
+        ) OR (
+            payments.updated_at >= date('now', 'weekday 0', '-13 days')
+            AND payments.updated_at < date('now', 'weekday 0', '-6 days')
+        ) OR (
+            payments.deleted_at >= date('now', 'weekday 0', '-13 days')
+            AND payments.deleted_at < date('now', 'weekday 0', '-6 days')
+        )
+    )
+ORDER BY
+    payments.date
+    "#;
+
+    let res = sqlx::query_as::<_, Report>(query).fetch_all(pool).await;
+
+    match res {
+        Ok(payments) => payments,
+        Err(err) => {
+            error!("Error listing payments for report: {:?}", err);
+            vec![]
+        }
+    }
+}
diff --git a/src/db/users.rs b/src/db/users.rs
new file mode 100644
index 0000000..82326a9
--- /dev/null
+++ b/src/db/users.rs
@@ -0,0 +1,144 @@
+use sqlx::error::Error;
+use sqlx::sqlite::{SqlitePool, SqliteRow};
+use sqlx_core::row::Row;
+
+use crate::model::user::User;
+
+pub async fn list(pool: &SqlitePool) -> Vec<User> {
+    let res = sqlx::query_as::<_, User>(
+        r#"
+SELECT
+    id,
+    name,
+    email
+FROM
+    users
+ORDER BY
+    name
+    "#,
+    )
+    .fetch_all(pool)
+    .await;
+
+    match res {
+        Ok(users) => users,
+        Err(err) => {
+            error!("Error listing users: {:?}", err);
+            vec![]
+        }
+    }
+}
+
+pub async fn set_login_token(
+    pool: &SqlitePool,
+    email: String,
+    login_token: String,
+) -> bool {
+    let res = sqlx::query(
+        r#"
+UPDATE
+    users
+SET
+    login_token = ?,
+    updated_at = datetime()
+WHERE
+    email = ?
+    "#,
+    )
+    .bind(login_token)
+    .bind(email)
+    .execute(pool)
+    .await;
+
+    match res {
+        Ok(_) => true,
+        Err(err) => {
+            error!("Error updating login token: {:?}", err);
+            false
+        }
+    }
+}
+
+pub async fn remove_login_token(pool: &SqlitePool, id: i64) -> bool {
+    let res = sqlx::query(
+        r#"
+UPDATE
+    users
+SET
+    login_token = NULL,
+    updated_at = datetime()
+WHERE
+    id = ?
+    "#,
+    )
+    .bind(id)
+    .execute(pool)
+    .await;
+
+    match res {
+        Ok(_) => true,
+        Err(err) => {
+            error!("Error removing login token: {:?}", err);
+            false
+        }
+    }
+}
+
+pub async fn get_by_login_token(
+    pool: &SqlitePool,
+    login_token: String,
+) -> Option<User> {
+    let res = sqlx::query_as::<_, User>(
+        r#"
+SELECT
+    id, 
+    name,
+    email
+FROM
+    users
+WHERE
+    login_token = ?
+    "#,
+    )
+    .bind(login_token)
+    .fetch_one(pool)
+    .await;
+
+    match res {
+        Ok(user) => Some(user),
+        Err(Error::RowNotFound) => None,
+        Err(err) => {
+            error!("Error getting user from login token: {:?}", err);
+            None
+        }
+    }
+}
+
+pub async fn get_password_hash(
+    pool: &SqlitePool,
+    email: String,
+) -> Option<String> {
+    let res = sqlx::query(
+        r#"
+SELECT
+    password
+FROM
+    users
+WHERE
+    email = ?
+    "#,
+    )
+    .bind(email)
+    .map(|row: SqliteRow| row.get("password"))
+    .fetch_one(pool)
+    .await;
+
+    match res {
+        Ok(hash) => Some(hash),
+        Err(Error::RowNotFound) => None,
+        Err(err) => {
+            error!("Error getting password hash: {:?}", err);
+            None
+        }
+    }
+}
diff --git a/src/db/utils.rs b/src/db/utils.rs
new file mode 100644
index 0000000..621a69c
--- /dev/null
+++ b/src/db/utils.rs
@@ -0,0 +1,3 @@
+pub fn format_key_for_search(value: &str) -> String {
+    format!("replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(lower({}), 'à', 'a'), 'â', 'a'), 'ç', 'c'), 'è', 'e'), 'é', 'e'), 'ê', 'e'), 'ë', 'e'), 'î', 'i'), 'ï', 'i'), 'ô', 'o'), 'ù', 'u'), 'û', 'u'), 'ü', 'u')", value)
+}