use std::collections::HashMap;
use std::iter::FromIterator;
use tokio_rusqlite::{
    named_params, params_from_iter, types::ToSql, Connection, Row,
};

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;

pub struct Count {
    pub count: i64,
    pub total_cost: i64,
}

fn row_to_count(row: &Row) -> Result<Count, rusqlite::Error> {
    Ok(Count {
        count: row.get(0)?,
        total_cost: row.get(1)?,
    })
}

fn row_to_table(row: &Row) -> Result<payment::Table, rusqlite::Error> {
    Ok(payment::Table {
        id: row.get(0)?,
        name: row.get(1)?,
        cost: row.get(2)?,
        user: row.get(3)?,
        category_name: row.get(4)?,
        category_color: row.get(5)?,
        date: row.get(6)?,
        frequency: row.get(7)?,
    })
}

fn row_to_stat(row: &Row) -> Result<payment::Stat, rusqlite::Error> {
    Ok(payment::Stat {
        start_date: row.get(0)?,
        cost: row.get(1)?,
        category_id: row.get(2)?,
    })
}

fn row_to_form(row: &Row) -> Result<payment::Form, rusqlite::Error> {
    Ok(payment::Form {
        id: row.get(0)?,
        name: row.get(1)?,
        cost: row.get(2)?,
        user_id: row.get(3)?,
        category_id: row.get(4)?,
        date: row.get(5)?,
        frequency: row.get(6)?,
    })
}

pub async fn count(
    conn: &Connection,
    payment_query: &queries::Payments,
) -> Count {
    let mut query = 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
    "#
    .to_string();

    let mut params = Vec::<Box<dyn ToSql + Send>>::new();
    complete_search_query(payment_query, &mut query, &mut params);

    let res = conn
        .call(move |conn| {
            let mut stmt = conn.prepare(&query)?;
            let mut iter =
                stmt.query_map(params_from_iter(params), row_to_count)?;
            utils::one(&mut iter)
        })
        .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(
    conn: &Connection,
    payment_query: &queries::Payments,
    per_page: i64,
) -> Vec<payment::Table> {
    let mut query = 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
    "#
    .to_string();

    let mut params = Vec::<Box<dyn ToSql + Send>>::new();
    complete_search_query(payment_query, &mut query, &mut params);

    query.push_str("ORDER BY payments.date DESC LIMIT ? OFFSET ?");
    params.push(Box::new(per_page));
    params.push(Box::new((payment_query.page.unwrap_or(1) - 1) * per_page));

    let res = conn
        .call(move |conn| {
            let mut stmt = conn.prepare(&query)?;
            let payments = stmt
                .query_map(params_from_iter(params), row_to_table)?
                .collect::<Result<Vec<payment::Table>, _>>()?;
            Ok(payments)
        })
        .await;

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

fn complete_search_query(
    q: &queries::Payments,
    query: &mut String,
    params: &mut Vec<Box<dyn ToSql + Send>>,
) {
    complete_frequency(q.frequency, query, params);
    complete_name(q.name.clone(), query, params);
    complete_cost(q.cost.clone(), query, params);
    complete_user(q.user, query, params);
    complete_category(q.category, query, params);
    complete_date(
        "payments.date >=".to_string(),
        q.start_date.clone(),
        query,
        params,
    );
    complete_date(
        "payments.date <=".to_string(),
        q.end_date.clone(),
        query,
        params,
    );
}

fn complete_frequency(
    frequency: Option<Frequency>,
    query: &mut String,
    params: &mut Vec<Box<dyn ToSql + Send>>,
) {
    query.push_str("AND payments.frequency = ?");
    params.push(Box::new(
        frequency.unwrap_or(Frequency::Punctual).to_string(),
    ));
}

fn complete_name(
    name: Option<String>,
    query: &mut String,
    params: &mut Vec<Box<dyn ToSql + Send>>,
) {
    if let Some(name) = name {
        if !name.is_empty() {
            query.push_str(
                format!(
                    "AND {} LIKE ?",
                    utils::format_key_for_search("payments.name")
                )
                .as_str(),
            );
            params.push(Box::new(name));
        }
    }
}

fn complete_cost(
    cost: Option<String>,
    query: &mut String,
    params: &mut Vec<Box<dyn ToSql + Send>>,
) {
    if let Some(cost) = cost {
        if !cost.is_empty() {
            query.push_str("AND payments.cost = ?");
            params.push(Box::new(cost))
        }
    }
}

fn complete_user(
    user: Option<i64>,
    query: &mut String,
    params: &mut Vec<Box<dyn ToSql + Send>>,
) {
    if let Some(user) = user {
        query.push_str("AND payments.user_id = ?");
        params.push(Box::new(user))
    }
}

fn complete_category(
    category: Option<i64>,
    query: &mut String,
    params: &mut Vec<Box<dyn ToSql + Send>>,
) {
    if let Some(category) = category {
        query.push_str("AND payments.category_id = ?");
        params.push(Box::new(category));
    }
}

fn complete_date(
    name_and_op: String,
    date: Option<String>,
    query: &mut String,
    params: &mut Vec<Box<dyn ToSql + Send>>,
) {
    if let Some(date) = date {
        if !date.is_empty() {
            query.push_str(format!("AND {name_and_op} ?").as_str());
            params.push(Box::new(date));
        }
    }
}

pub async fn list_for_stats(conn: &Connection) -> 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 res = conn
        .call(move |conn| {
            let mut stmt = conn.prepare(query)?;
            let payments = stmt
                .query_map([], row_to_stat)?
                .collect::<Result<Vec<payment::Stat>, _>>()?;
            Ok(payments)
        })
        .await;

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

pub async fn get_row(conn: &Connection, 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 = :frequency
        )
        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, ":frequency": frequency.to_string()],
                |row| row.get(0),
            )?;
            utils::one::<i64, _>(&mut iter)
        })
        .await;

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

pub async fn get_for_form(conn: &Connection, 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 = :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_form)?;
            utils::one(&mut iter)
        })
        .await;

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

pub async fn create(conn: &Connection, p: payment::Create) -> Option<i64> {
    let query = r#"
        INSERT INTO payments(name, cost, user_id, category_id, date, frequency)
        VALUES (:name, :cost, :user_id, :category_id, :date, :frequency)
    "#;

    let res = conn
        .call(move |conn| {
            conn.execute(
                query,
                named_params![
                    ":name": p.name,
                    ":cost": p.cost,
                    ":user_id": p.user_id,
                    ":category_id": p.category_id,
                    ":date": p.date,
                    ":frequency": p.frequency.to_string()
                ],
            )?;
            Ok(conn.last_insert_rowid())
        })
        .await;

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

pub async fn update(conn: &Connection, id: i64, p: payment::Update) -> bool {
    let query = r#"
        UPDATE payments
        SET
            name = :name,
            cost = :cost,
            user_id = :user_id,
            category_id = :category_id,
            date = :date,
            updated_at = datetime()
        WHERE id = :id
    "#;

    let res = conn
        .call(move |conn| {
            Ok(conn.execute(
                query,
                named_params![
                    ":name": p.name,
                    ":cost": p.cost,
                    ":user_id": p.user_id,
                    ":category_id": p.category_id,
                    ":date": p.date,
                    ":id": id
                ],
            )?)
        })
        .await;

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

pub async fn delete(conn: &Connection, id: i64) -> bool {
    let query = r#"UPDATE payments SET deleted_at = datetime() WHERE id = :id"#;

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

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

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

    let payment_name_closure = payment_name.clone();

    let res = conn
        .call(move |conn| {
            let mut stmt = conn.prepare(&query)?;
            let mut iter = stmt.query_map(
                named_params![":name": text::format_search(&payment_name_closure)],
                |row| row.get(0)
            )?;
            utils::one::<i64, _>(&mut iter)
        })
        .await;

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

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

    let res = conn
        .call(move |conn| {
            let mut stmt = conn.prepare(query)?;
            Ok(stmt.exists(named_params![":category_id": category_id])?)
        })
        .await;

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

pub async fn repartition(conn: &Connection) -> HashMap<i64, i64> {
    let query = r#"
        SELECT
            users.id,
            COALESCE(payments.sum, 0)
        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 = conn
        .call(move |conn| {
            let mut stmt = conn.prepare(query)?;
            let payments = stmt
                .query_map([], |row| Ok((row.get(0)?, row.get(1)?)))?
                .collect::<Result<Vec<(i64, i64)>, _>>()?;
            Ok(payments)
        })
        .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(conn: &Connection) {
    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 = conn.call(move |conn| Ok(conn.execute(query, [])?)).await;

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

pub async fn last_week(conn: &Connection) -> 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 = conn
        .call(move |conn| {
            let mut stmt = conn.prepare(query)?;
            let xs = stmt
                .query_map([], utils::row_to_report)?
                .collect::<Result<Vec<Report>, _>>()?;

            Ok(xs)
        })
        .await;

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