use tokio_rusqlite::{named_params, Connection, Row};

use crate::db::utils;
use crate::model::category::{Category, Create, Update};

fn row_to_category(row: &Row) -> Result<Category, rusqlite::Error> {
    Ok(Category {
        id: row.get(0)?,
        name: row.get(1)?,
        color: row.get(2)?,
    })
}

pub async fn list(conn: &Connection) -> Vec<Category> {
    let query = r#"
        SELECT
            id,
            name,
            color
        FROM categories
        WHERE deleted_at IS NULL
        ORDER BY name
    "#;

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

            let users = stmt
                .query_map([], row_to_category)?
                .collect::<Result<Vec<Category>, _>>()?;

            Ok(users)
        })
        .await;

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

pub async fn get(conn: &Connection, id: i64) -> Option<Category> {
    let query = r#"
        SELECT
            id,
            name,
            color
        FROM categories
        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_category)?;
            utils::one(&mut iter)
        })
        .await;

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

pub async fn create(conn: &Connection, c: Create) -> Option<i64> {
    let query = r#"INSERT INTO categories(name, color) VALUES (:name, :color)"#;

    let res = conn
        .call(move |conn| {
            conn.execute(
                query,
                named_params![":name": c.name, ":color": c.color],
            )?;
            Ok(conn.last_insert_rowid())
        })
        .await;

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

pub async fn update(conn: &Connection, id: i64, c: Update) -> bool {
    let query = r#"
        UPDATE categories
        SET
            name = :name,
            color = :color,
            updated_at = datetime()
        WHERE id = :id
    "#;

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

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

pub async fn delete(conn: &Connection, id: i64) -> bool {
    let res = conn
        .call(move |conn| {
            Ok(conn.execute(
                r#"
            UPDATE
                categories
            SET
                deleted_at = datetime()
            WHERE
                id = :id
            "#,
                named_params![":id": id],
            )?)
        })
        .await;

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