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
        }
    }
}