use super::{deserialize::deserialize, schema::Table};
use crate::storage::Error;
use crate::storage::config_store::config_store::FindOptions;
use change_case::snake_case;
use futures_util::TryStreamExt;
use pbbson::Model;
use pbbson::bson::Bson;
use sqlx::{Pool, Postgres};

pub(super) async fn perform(pool: &Pool<Postgres>, table: &Table, options: FindOptions) -> Result<Vec<Model>, Error> {
    // SELECT * FROM <table>
    let table_name = &table.name;
    let mut sql = format!("SELECT * FROM {table_name}");

    // WHERE ...
    for (index, (k, v)) in options.filter.iter().enumerate() {
        if index == 0 {
            sql = format!("{sql} WHERE ");
        } else if index > 1 {
            sql = format!("{sql} AND ");
        }
        let field_name = snake_case(k);
        let param_num = index + 1;
        match v {
            Bson::Null => {
                sql = format!("{sql}{field_name} = NULL");
            }
            _ => {
                sql = format!("{sql}{field_name} = ${param_num}");
            }
        }
    }

    // OFFSET start
    if let (Some(offset), _) = options.pagination {
        sql = format!("{sql} OFFSET ${offset}");
    }

    // LIMIT count
    if let (_, Some(limit)) = options.pagination {
        sql = format!("{sql} LIMIT ${limit}");
    }

    // Bind params
    let mut query = sqlx::query(&sql);
    for (_k, v) in options.filter.iter() {
        match v {
            Bson::Null => {
                // already handled above in WHERE loop
            }
            Bson::String(s) => {
                query = query.bind(s);
            }
            _ => {
                query = query.bind(v.to_string());
            }
        }
    }

    // Perform query
    let mut rows = query.fetch(pool);

    // Collect results
    let mut models = vec![];
    while let Some(row) = rows.try_next().await.map_err(|e| Error::internal(e.to_string()))? {
        let model = deserialize(row, table)?;
        models.push(model);
    }
    Ok(models)
}
