use sqlx::error::Error;
use sqlx::sqlite::{Sqlite, SqliteArguments};
use sqlx::sqlite::{SqlitePool, SqliteRow};
use sqlx::FromRow;
use sqlx_core::row::Row;
use std::collections::HashMap;
use std::iter::FromIterator;

use crate::db::utils;
use crate::model::frequency::Frequency;
use crate::model::payment;
use crate::model::report::Report;
use crate::queries;
use crate::utils::text;

#[derive(FromRow)]
pub struct Count {
    pub count: i64,
    pub total_cost: i64,
}

pub async fn count(
    pool: &SqlitePool,
    payment_query: &queries::Payments,
) -> Count {
    let search = payment_query.search.clone().unwrap_or("".to_string());

    let query = format!(
        r#"
SELECT
    COUNT(*) AS count,
    SUM(payments.cost) AS total_cost
FROM
    payments
INNER JOIN
    users ON users.id = payments.user_id
INNER JOIN
    categories ON categories.id = payments.category_id
WHERE
    payments.deleted_at IS NULL
    AND payments.frequency = ?
    {}
        "#,
        search_query(search.clone())
    );

    let res = bind_search(
        sqlx::query_as::<_, Count>(&query)
            .bind(payment_query.frequency.unwrap_or(Frequency::Punctual)),
        search,
    )
    .fetch_one(pool)
    .await;

    match res {
        Ok(count) => count,
        Err(err) => {
            error!("Error counting payments: {:?}", err);
            Count {
                count: 0,
                total_cost: 0,
            }
        }
    }
}

pub async fn list_for_table(
    pool: &SqlitePool,
    payment_query: &queries::Payments,
    per_page: i64,
) -> Vec<payment::Table> {
    let offset = (payment_query.page.unwrap_or(1) - 1) * per_page;
    let search = payment_query.search.clone().unwrap_or("".to_string());

    let query = format!(
        r#"
SELECT
    payments.id,
    payments.name,
    payments.cost,
    users.name AS user,
    categories.name AS category_name,
    categories.color AS category_color,
    strftime('%d/%m/%Y', date) AS date,
    payments.frequency AS frequency
FROM
    payments
INNER JOIN
    users ON users.id = payments.user_id
INNER JOIN
    categories ON categories.id = payments.category_id
WHERE
    payments.deleted_at IS NULL
    AND payments.frequency = ?
    {}
ORDER BY
    payments.date DESC
LIMIT ?
OFFSET ?
        "#,
        search_query(search.clone())
    );

    let res = bind_search(
        sqlx::query_as::<_, payment::Table>(&query)
            .bind(payment_query.frequency.unwrap_or(Frequency::Punctual)),
        search,
    )
    .bind(per_page)
    .bind(offset)
    .fetch_all(pool)
    .await;

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

fn search_query(search: String) -> String {
    let payments_name = utils::format_key_for_search("payments.name");
    let users_name = utils::format_key_for_search("users.name");
    let categories_name = utils::format_key_for_search("categories.name");

    search
        .split_ascii_whitespace()
        .map(|_| {
            format!(
                r#"
AND (
    {} LIKE ?
    OR payments.cost LIKE ?
    OR {} LIKE ?
    OR {} LIKE ?
    OR strftime('%d/%m/%Y', date) LIKE ?
)
        "#,
                payments_name, users_name, categories_name
            )
        })
        .collect::<Vec<String>>()
        .join(" ")
}

fn bind_search<'a, Row: FromRow<'a, SqliteRow>>(
    query: sqlx::query::QueryAs<'a, Sqlite, Row, SqliteArguments<'a>>,
    search: String,
) -> sqlx::query::QueryAs<'a, Sqlite, Row, SqliteArguments<'a>> {
    search.split_ascii_whitespace().fold(query, |q, word| {
        let s = format!("%{}%", text::format_search(&word.to_string()));
        q.bind(s.clone())
            .bind(s.clone())
            .bind(s.clone())
            .bind(s.clone())
            .bind(s.clone())
    })
}

pub async fn list_for_stats(pool: &SqlitePool) -> Vec<payment::Stat> {
    let query = r#"
SELECT
    strftime('%Y-%m-01', payments.date) AS start_date,
    SUM(payments.cost) AS cost,
    payments.category_id AS category_id
FROM
    payments
WHERE
    payments.deleted_at IS NULL
    AND payments.frequency = 'Punctual'
GROUP BY
    start_date,
    payments.category_id;
    "#;

    let result = sqlx::query_as::<_, payment::Stat>(query)
        .fetch_all(pool)
        .await;

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

pub async fn get_row(pool: &SqlitePool, id: i64, frequency: Frequency) -> i64 {
    let query = r#"
SELECT
    row
FROM (
    SELECT
        ROW_NUMBER () OVER (ORDER BY date DESC) AS row,
        id
    FROM
        payments
    WHERE
        deleted_at IS NULL
        AND frequency = ?
)
WHERE
    id = ?
    "#;

    let res = sqlx::query(query)
        .bind(frequency)
        .bind(id)
        .map(|row: SqliteRow| row.get("row"))
        .fetch_one(pool)
        .await;

    match res {
        Ok(count) => count,
        Err(err) => {
            error!("Error getting payment row: {:?}", err);
            1
        }
    }
}

pub async fn get_for_form(pool: &SqlitePool, id: i64) -> Option<payment::Form> {
    let query = r#"
SELECT
    id,
    name,
    cost,
    user_id,
    category_id,
    strftime('%Y-%m-%d', date) AS date,
    frequency AS frequency
FROM
    payments
WHERE
    id = ?
    AND deleted_at IS NULL
    "#;

    let res = sqlx::query_as::<_, payment::Form>(query)
        .bind(id)
        .fetch_one(pool)
        .await;

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

pub async fn create(pool: &SqlitePool, p: &payment::Create) -> Option<i64> {
    let res = sqlx::query(
        r#"
INSERT INTO
    payments(name, cost, user_id, category_id, date, frequency)
VALUES
    (?, ?, ?, ?, ?, ?)
    "#,
    )
    .bind(p.name.clone())
    .bind(p.cost)
    .bind(p.user_id)
    .bind(p.category_id)
    .bind(p.date)
    .bind(p.frequency)
    .execute(pool)
    .await;

    match res {
        Ok(x) => Some(x.last_insert_rowid()),
        Err(err) => {
            error!("Error creating payment: {:?}", err);
            None
        }
    }
}

pub async fn update(pool: &SqlitePool, id: i64, p: &payment::Update) -> bool {
    let res = sqlx::query(
        r#"
UPDATE
    payments
SET
    name = ?,
    cost = ?,
    user_id = ?,
    category_id = ?,
    date = ?,
    updated_at = datetime()
WHERE
    id = ?
    "#,
    )
    .bind(p.name.clone())
    .bind(p.cost)
    .bind(p.user_id)
    .bind(p.category_id)
    .bind(p.date)
    .bind(id)
    .execute(pool)
    .await;

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

pub async fn delete(pool: &SqlitePool, id: i64) -> bool {
    let res = sqlx::query(
        r#"
UPDATE
    payments
SET
    deleted_at = datetime()
WHERE
    id = ?
    "#,
    )
    .bind(id)
    .execute(pool)
    .await;

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

pub async fn search_category(
    pool: &SqlitePool,
    payment_name: String,
) -> Option<i64> {
    let query = format!(
        r#"
SELECT
    category_id
FROM
    payments
WHERE
    deleted_at IS NULL
    AND {} LIKE ?
ORDER BY
    updated_at, created_at
        "#,
        utils::format_key_for_search("name")
    );

    let res = sqlx::query(&query)
        .bind(text::format_search(&format!("%{}%", payment_name)))
        .map(|row: SqliteRow| row.get("category_id"))
        .fetch_one(pool)
        .await;

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

pub async fn is_category_used(pool: &SqlitePool, category_id: i64) -> bool {
    let query = r#"
SELECT
    1
FROM
    payments
WHERE
    category_id = ?
    AND deleted_at IS NULL
LIMIT
    1
    "#;

    let res = sqlx::query(&query).bind(category_id).fetch_one(pool).await;

    match res {
        Ok(_) => true,
        Err(Error::RowNotFound) => false,
        Err(err) => {
            error!(
                "Error looking if category {} is used: {:?}",
                category_id, err
            );
            false
        }
    }
}

pub async fn repartition(pool: &SqlitePool) -> HashMap<i64, i64> {
    let query = r#"
SELECT
    users.id AS user_id,
    COALESCE(payments.sum, 0) AS sum
FROM
    users
LEFT OUTER JOIN (
    SELECT
        user_id,
        SUM(cost) AS sum
    FROM
        payments
    WHERE
        deleted_at IS NULL
        AND frequency = 'Punctual'
    GROUP BY
        user_id
) payments
ON
    users.id = payments.user_id"#;

    let res = sqlx::query(&query)
        .map(|row: SqliteRow| (row.get("user_id"), row.get("sum")))
        .fetch_all(pool)
        .await;

    match res {
        Ok(costs) => HashMap::from_iter(costs),
        Err(err) => {
            error!("Error getting payments repartition: {:?}", err);
            HashMap::new()
        }
    }
}

pub async fn create_monthly_payments(pool: &SqlitePool) -> () {
    let query = r#"
INSERT INTO
    payments(name, cost, user_id, category_id, date, frequency)
SELECT
    name,
    cost,
    user_id,
    category_id,
    date() AS date,
    'Punctual' AS frequency
FROM
    payments
WHERE
    frequency = 'Monthly'
    AND deleted_at IS NULL
    "#;

    let res = sqlx::query(query).execute(pool).await;

    match res {
        Ok(_) => (),
        Err(err) => {
            error!("Error creating monthly payments: {:?}", err);
            ()
        }
    }
}

pub async fn last_week(pool: &SqlitePool) -> Vec<Report> {
    let query = r#"
SELECT
    strftime('%d/%m/%Y', payments.date) AS date,
    (payments.name || ' (' || users.name || ')') AS name,
    payments.cost AS amount,
    (CASE
        WHEN
            payments.deleted_at IS NOT NULL
        THEN
            'Deleted'
        WHEN
            payments.updated_at IS NOT NULL
            AND payments.created_at < date('now', 'weekday 0', '-13 days')
        THEN
            'Updated'
        ELSE
            'Created'
    END) AS action
FROM
    payments
INNER JOIN
    users
ON
    payments.user_id = users.id
WHERE
    payments.frequency = 'Punctual'
    AND (
        (
            payments.created_at >= date('now', 'weekday 0', '-13 days')
            AND payments.created_at < date('now', 'weekday 0', '-6 days')
        ) OR (
            payments.updated_at >= date('now', 'weekday 0', '-13 days')
            AND payments.updated_at < date('now', 'weekday 0', '-6 days')
        ) OR (
            payments.deleted_at >= date('now', 'weekday 0', '-13 days')
            AND payments.deleted_at < date('now', 'weekday 0', '-6 days')
        )
    )
ORDER BY
    payments.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![]
        }
    }
}