path: root/src/db/incomes.rs

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)
VALUES
    (?, ?, ?)
    "#,
    )
    .bind(i.user_id)
    .bind(NaiveDate::from_ymd_opt(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![]
        }
    }
}