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 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 = ?
    {} {} {} {} {} {}
        "#,
        name_query(payment_query.name.clone()),
        cost_query(payment_query.cost.clone()),
        user_query(payment_query.user),
        category_query(payment_query.category),
        date_query(
            "payments.date >=".to_string(),
            payment_query.start_date.clone()
        ),
        date_query(
            "payments.date <=".to_string(),
            payment_query.end_date.clone()
        )
    );

    let res = bind_date(
        bind_date(
            bind_category(
                bind_user(
                    bind_cost(
                        bind_name(
                            sqlx::query_as::<_, Count>(&query).bind(
                                payment_query
                                    .frequency
                                    .unwrap_or(Frequency::Punctual),
                            ),
                            payment_query.name.clone(),
                        ),
                        payment_query.cost.clone(),
                    ),
                    payment_query.user,
                ),
                payment_query.category,
            ),
            payment_query.start_date.clone(),
        ),
        payment_query.end_date.clone(),
    )
    .fetch_one(pool)
    .await;

    match res {
        Ok(count) => count,
        Err(err) => {
            log::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 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 ?
        "#,
        name_query(payment_query.name.clone()),
        cost_query(payment_query.cost.clone()),
        user_query(payment_query.user),
        category_query(payment_query.category),
        date_query(
            "payments.date >=".to_string(),
            payment_query.start_date.clone()
        ),
        date_query(
            "payments.date <=".to_string(),
            payment_query.end_date.clone()
        )
    );

    let res = bind_date(
        bind_date(
            bind_category(
                bind_user(
                    bind_cost(
                        bind_name(
                            sqlx::query_as::<_, payment::Table>(&query).bind(
                                payment_query
                                    .frequency
                                    .unwrap_or(Frequency::Punctual),
                            ),
                            payment_query.name.clone(),
                        ),
                        payment_query.cost.clone(),
                    ),
                    payment_query.user,
                ),
                payment_query.category,
            ),
            payment_query.start_date.clone(),
        ),
        payment_query.end_date.clone(),
    )
    .bind(per_page)
    .bind(offset)
    .fetch_all(pool)
    .await;

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

fn name_query(name: Option<String>) -> String {
    if name.map_or_else(|| false, |str| !str.is_empty()) {
        format!(
            "AND {} LIKE ?",
            utils::format_key_for_search("payments.name")
        )
    } else {
        "".to_string()
    }
}

fn bind_name<'a, Row: FromRow<'a, SqliteRow>>(
    query: sqlx::query::QueryAs<'a, Sqlite, Row, SqliteArguments<'a>>,
    name: Option<String>,
) -> sqlx::query::QueryAs<'a, Sqlite, Row, SqliteArguments<'a>> {
    match name {
        Some(str) => {
            if str.is_empty() {
                query
            } else {
                query.bind(text::format_search(&str))
            }
        }
        _ => query,
    }
}

fn cost_query(cost: Option<String>) -> String {
    if cost.map_or_else(|| false, |str| !str.is_empty()) {
        "AND payments.cost = ?".to_string()
    } else {
        "".to_string()
    }
}

fn bind_cost<'a, Row: FromRow<'a, SqliteRow>>(
    query: sqlx::query::QueryAs<'a, Sqlite, Row, SqliteArguments<'a>>,
    cost: Option<String>,
) -> sqlx::query::QueryAs<'a, Sqlite, Row, SqliteArguments<'a>> {
    match cost {
        Some(str) => {
            if str.is_empty() {
                query
            } else {
                query.bind(str)
            }
        }
        _ => query,
    }
}

fn user_query(user: Option<i64>) -> String {
    if user.is_some() {
        "AND payments.user_id = ?".to_string()
    } else {
        "".to_string()
    }
}

fn bind_user<'a, Row: FromRow<'a, SqliteRow>>(
    query: sqlx::query::QueryAs<'a, Sqlite, Row, SqliteArguments<'a>>,
    user: Option<i64>,
) -> sqlx::query::QueryAs<'a, Sqlite, Row, SqliteArguments<'a>> {
    match user {
        Some(id) => query.bind(id),
        _ => query,
    }
}

fn category_query(category: Option<i64>) -> String {
    if category.is_some() {
        "AND payments.category_id = ?".to_string()
    } else {
        "".to_string()
    }
}

fn bind_category<'a, Row: FromRow<'a, SqliteRow>>(
    query: sqlx::query::QueryAs<'a, Sqlite, Row, SqliteArguments<'a>>,
    category: Option<i64>,
) -> sqlx::query::QueryAs<'a, Sqlite, Row, SqliteArguments<'a>> {
    match category {
        Some(id) => query.bind(id),
        _ => query,
    }
}

fn date_query(name_and_op: String, date: Option<String>) -> String {
    if date.map_or_else(|| false, |str| !str.is_empty()) {
        format!("AND {} ?", name_and_op)
    } else {
        "".to_string()
    }
}

fn bind_date<'a, Row: FromRow<'a, SqliteRow>>(
    query: sqlx::query::QueryAs<'a, Sqlite, Row, SqliteArguments<'a>>,
    date: Option<String>,
) -> sqlx::query::QueryAs<'a, Sqlite, Row, SqliteArguments<'a>> {
    match date {
        Some(d) => {
            if d.is_empty() {
                query
            } else {
                query.bind(d)
            }
        }
        _ => query,
    }
}
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) => {
            log::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) => {
            log::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) => {
            log::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) => {
            log::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) => {
            log::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) => {
            log::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(&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) => {
            log::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) => {
            log::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) => {
            log::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) => log::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) => {
            log::error!("Error listing payments for report: {:?}", err);
            vec![]
        }
    }
}