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