path: root/src/db/incomes.rs

use chrono::NaiveDate;
use std::collections::HashMap;
use std::iter::FromIterator;
use tokio_rusqlite::{named_params, Connection, Row};

use crate::db::utils;
use crate::model::income::{Create, Form, Stat, Table, Update};
use crate::model::report::Report;

fn row_to_table(row: &Row) -> Result<Table, rusqlite::Error> {
    Ok(Table {
        id: row.get(0)?,
        date: row.get(1)?,
        user: row.get(2)?,
        amount: row.get(3)?,
    })
}

fn row_to_form(row: &Row) -> Result<Form, rusqlite::Error> {
    Ok(Form {
        id: row.get(0)?,
        amount: row.get(1)?,
        user_id: row.get(2)?,
        month: row.get(3)?,
        year: row.get(4)?,
    })
}

fn row_to_stat(row: &Row) -> Result<Stat, rusqlite::Error> {
    Ok(Stat {
        date: row.get(0)?,
        amount: row.get(1)?,
    })
}

pub async fn count(conn: &Connection) -> i64 {
    let query = r#"
        SELECT COUNT(*)
        FROM incomes
        WHERE incomes.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 incomes: {:?}", err);
            0
        }
    }
}

pub async fn list(conn: &Connection, 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 :limit
        OFFSET :offset
    "#;

    let res = conn.call(move |conn| {
        let mut stmt = conn.prepare(query)?;

        let incomes = stmt
            .query_map(
                named_params![":limit": per_page, ":offset": (page - 1) * per_page],
                row_to_table
            )?
            .collect::<Result<Vec<Table>, _>>()?;

        Ok(incomes)
    })
    .await;

    match res {
        Ok(incomes) => incomes,
        Err(err) => {
            log::error!("Error listing incomes: {:?}", err);
            vec![]
        }
    }
}

pub async fn get_row(conn: &Connection, 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 = :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 income row: {:?}", err);
            1
        }
    }
}

pub async fn get(conn: &Connection, 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 = :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_form)?;
            utils::one(&mut iter)
        })
        .await;

    match res {
        Ok(income) => Some(income),
        Err(err) => {
            log::error!("Error looking for income {}: {:?}", id, err);
            None
        }
    }
}

pub async fn create(conn: &Connection, i: Create) -> Option<i64> {
    let query = r#"
        INSERT INTO incomes(user_id, date, amount)
        VALUES (:user_id, :date, :amount)
    "#;

    let res = conn
        .call(move |conn| {
            conn.execute(
                query,
                named_params![
                    ":user_id": i.user_id,
                    ":date": i.date,
                    ":amount": i.amount
                ],
            )?;
            Ok(conn.last_insert_rowid())
        })
        .await;

    match res {
        Ok(income_id) => Some(income_id),
        Err(err) => {
            log::error!("Error creating income: {:?}", err);
            None
        }
    }
}

pub async fn defined_at(
    conn: &Connection,
    user_id: i64,
    date: NaiveDate,
) -> Vec<i64> {
    let query = r#"
        SELECT id
        FROM incomes
        WHERE
            user_id = :user_id
            AND date = :date
            AND deleted_at IS NULL
    "#;

    let res = conn
        .call(move |conn| {
            let mut stmt = conn.prepare(query)?;

            let incomes = stmt
                .query_map(
                    named_params![":user_id": user_id, ":date": date],
                    |row| row.get(0),
                )?
                .collect::<Result<Vec<i64>, _>>()?;

            Ok(incomes)
        })
        .await;

    match res {
        Ok(ids) => ids,
        Err(err) => {
            log::error!("Error looking if income is defined: {:?}", err);
            vec![]
        }
    }
}

pub async fn update(conn: &Connection, id: i64, i: Update) -> bool {
    let query = r#"
    UPDATE incomes
    SET
        user_id = :user_id,
        date = :date,
        amount = :amount,
        updated_at = datetime()
    WHERE id = :id
    "#;

    let res = conn
        .call(move |conn| {
            Ok(conn.execute(
                query,
                named_params![
                    ":user_id": i.user_id,
                    ":date": i.date,
                    ":amount": i.amount,
                    ":id": id
                ],
            )?)
        })
        .await;

    match res {
        Ok(_) => true,
        Err(err) => {
            log::error!("Error updating income {}: {:?}", id, err);
            false
        }
    }
}

pub async fn delete(conn: &Connection, id: i64) -> bool {
    let query = r#"UPDATE incomes SET deleted_at = datetime() WHERE id = :id"#;

    let res = conn
        .call(move |conn| Ok(conn.execute(query, named_params![":id": id])?))
        .await;

    match res {
        Ok(_) => true,
        Err(err) => {
            log::error!("Error deleting income {}: {:?}", id, err);
            false
        }
    }
}

pub async fn defined_for_all(conn: &Connection) -> Option<NaiveDate> {
    let 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;
    "#;

    let res = conn
        .call(move |conn| {
            let mut stmt = conn.prepare(query)?;
            let mut iter = stmt.query_map([], |row| row.get(0))?;
            utils::one::<NaiveDate, _>(&mut iter)
        })
        .await;

    match res {
        Ok(d) => Some(d),
        Err(err) => {
            log::error!("Error looking for incomes defined for all: {:?}", err);
            None
        }
    }
}

pub async fn cumulative(
    conn: &Connection,
    from: NaiveDate,
) -> HashMap<i64, i64> {
    let res = conn
        .call(move |conn| {
            let mut stmt = conn.prepare(&cumulative_query(from))?;
            let incomes = stmt
                .query_map([], |row| Ok((row.get(0)?, row.get(1)?)))?
                .collect::<Result<Vec<(i64, i64)>, _>>()?;
            Ok(incomes)
        })
        .await;

    match res {
        Ok(incomes) => HashMap::from_iter(incomes),
        Err(err) => {
            log::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(conn: &Connection) -> 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 = conn
        .call(move |conn| {
            let mut stmt = conn.prepare(query)?;
            let stats = stmt
                .query_map([], row_to_stat)?
                .collect::<Result<Vec<Stat>, _>>()?;

            Ok(stats)
        })
        .await;

    match res {
        Ok(xs) => xs,
        Err(err) => {
            log::error!("Error listing incomes for statistics: {:?}", err);
            vec![]
        }
    }
}

pub async fn last_week(conn: &Connection) -> 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 = conn
        .call(move |conn| {
            let mut stmt = conn.prepare(query)?;
            let xs = stmt
                .query_map([], utils::row_to_report)?
                .collect::<Result<Vec<Report>, _>>()?;

            Ok(xs)
        })
        .await;

    match res {
        Ok(xs) => xs,
        Err(err) => {
            log::error!("Error listing payments for report: {:?}", err);
            vec![]
        }
    }
}