use deadpool_postgres::GenericClient;
use std::fmt::Write as _;

use super::BoxedQueryExpression;
use super::NonNullTableAlias;
use super::NullableTableAlias;
use super::QueryBindings;
use super::QueryResult;
use super::QuerySelectable;
use super::Q;
use crate::entity::Entity;
use crate::entity::EntityIdLike;

enum QueryTable<'a> {
    From {
        table: &'static str,
        alias: &'static str,
    },
    InnerJoin {
        table: &'static str,
        alias: &'static str,
        cond: BoxedQueryExpression<'a>,
    },
    LeftJoin {
        table: &'static str,
        alias: &'static str,
        cond: BoxedQueryExpression<'a>,
    },
}

#[derive(Debug, Copy, Clone, Eq, PartialEq, Hash)]
pub enum OrderDirection {
    /// Ascending order
    Asc,
    /// Descending order
    Desc,
}

impl OrderDirection {
    pub const fn flip(&self) -> Self {
        match self {
            OrderDirection::Asc => OrderDirection::Desc,
            OrderDirection::Desc => OrderDirection::Asc,
        }
    }

    pub const fn as_str(&self) -> &'static str {
        match self {
            OrderDirection::Asc => "ASC",
            OrderDirection::Desc => "DESC",
        }
    }
}

pub struct QueryBuilder<'a> {
    tables: Vec<QueryTable<'a>>,
    conditions: Vec<BoxedQueryExpression<'a>>,
    ordering: Vec<(BoxedQueryExpression<'a>, OrderDirection)>,
    row_lock: Option<&'static str>,
}

impl<'a> QueryBuilder<'a> {
    pub fn from<E: Entity>(root_table: &NonNullTableAlias<E>) -> Self {
        return QueryBuilder {
            tables: vec![QueryTable::From {
                table: E::TABLE_NAME,
                alias: root_table.alias,
            }],
            conditions: Vec::with_capacity(4),
            ordering: Vec::new(),
            row_lock: None,
        };
    }

    pub fn inner_join<E: Entity>(
        &mut self,
        join_table: &NonNullTableAlias<E>,
        condition: BoxedQueryExpression<'a>,
    ) -> &mut Self {
        self.tables.push(QueryTable::InnerJoin {
            table: E::TABLE_NAME,
            alias: join_table.alias,
            cond: condition,
        });

        return self;
    }

    pub fn inner_join_on_id<E: Entity>(
        &mut self,
        join_table: &NonNullTableAlias<E>,
        id_col: BoxedQueryExpression<'a>,
    ) -> &mut Self {
        let condition = Q::op(Q::col(join_table, E::ID_COLUMN), "=", id_col);
        self.tables.push(QueryTable::InnerJoin {
            table: E::TABLE_NAME,
            alias: join_table.alias,
            cond: condition,
        });

        return self;
    }

    pub fn left_join<E: Entity>(
        &mut self,
        join_table: &NullableTableAlias<E>,
        condition: BoxedQueryExpression<'a>,
    ) -> &mut Self {
        self.tables.push(QueryTable::LeftJoin {
            table: E::TABLE_NAME,
            alias: join_table.alias,
            cond: condition,
        });

        return self;
    }

    pub fn left_join_on_id<E: Entity>(
        &mut self,
        join_table: &NullableTableAlias<E>,
        id_col: BoxedQueryExpression<'a>,
    ) -> &mut Self {
        let condition = Q::op(Q::col(join_table, E::ID_COLUMN), "=", id_col);
        self.tables.push(QueryTable::LeftJoin {
            table: E::TABLE_NAME,
            alias: join_table.alias,
            cond: condition,
        });

        return self;
    }

    pub fn where_cond(&mut self, cond: BoxedQueryExpression<'a>) -> &mut Self {
        self.conditions.push(cond);

        return self;
    }

    pub fn where_id<'b: 'a, E: Entity>(
        &mut self,
        table: &NonNullTableAlias<E>,
        id: impl EntityIdLike<'a, E::EntityIdType>,
    ) -> &mut Self {
        self.conditions
            .push(Q::op(Q::col(table, E::ID_COLUMN), "=", Q::val(id.as_str())));

        return self;
    }

    pub fn order_by(
        &mut self,
        ordering: Vec<(BoxedQueryExpression<'a>, OrderDirection)>,
    ) -> &mut Self {
        self.ordering = ordering;

        return self;
    }

    pub fn lock_for(&mut self, lock_mode: &'static str) -> &mut Self {
        self.row_lock = Some(lock_mode);

        return self;
    }

    pub fn build_query<S: QuerySelectable>(
        &self,
        selectable: &S,
        limit: Option<usize>,
        offset: u64,
    ) -> (String, QueryBindings<'a>) {
        let mut bindings: QueryBindings<'a> = QueryBindings::new();

        let mut q = String::with_capacity(2048);
        q.write_str("SELECT ").unwrap();
        selectable.select_columns(&mut q);

        add_tables_to_query(&self.tables, &mut q, &mut bindings);
        add_conditions_to_query(&self.conditions, &mut q, &mut bindings);

        if !self.ordering.is_empty() {
            q.write_str(" ORDER BY ").unwrap();

            let (col, dir) = &self.ordering[0];
            col.add_to_query(&mut q, &mut bindings);
            write!(q, " {}", dir.as_str()).unwrap();

            for (col, dir) in &self.ordering[1..] {
                q.write_str(", ").unwrap();
                col.add_to_query(&mut q, &mut bindings);
                write!(q, " {}", dir.as_str()).unwrap();
            }
        }

        if let Some(limit) = limit {
            // Just inline the value for simplicity and efficiency since it's a value, not an expression
            write!(q, " LIMIT {limit}").unwrap();
        }

        if offset != 0 {
            // Just inline the value for simplicity and efficiency since it's a value, not an expression
            write!(q, " OFFSET {offset}").unwrap();
        }

        if let Some(lock_mode) = self.row_lock {
            write!(q, " FOR {lock_mode}").unwrap();
        }

        return (q, bindings);
    }

    pub async fn fetch_one<S: QuerySelectable>(
        &self,
        db: &impl GenericClient,
        selectable: &S,
    ) -> QueryResult<S::SelectedType> {
        // LIMIT of 2 so that it will still error if more than on result is found
        let (query, bindings) = self.build_query(selectable, Some(2), 0);
        let row = db.query_one(&query, &bindings.to_params()).await?;

        return Ok(S::convert_row(&row, 0));
    }

    pub async fn fetch_optional<S: QuerySelectable>(
        &self,
        db: &impl GenericClient,
        selectable: &S,
    ) -> QueryResult<Option<S::SelectedType>> {
        // LIMIT of 2 so that it will still error if more than on result is found
        let (query, bindings) = self.build_query(selectable, Some(2), 0);
        let row = db.query_opt(&query, &bindings.to_params()).await?;

        return Ok(row.map(|r| S::convert_row(&r, 0)));
    }

    pub async fn fetch_all<S: QuerySelectable>(
        &self,
        db: &impl GenericClient,
        selectable: &S,
    ) -> QueryResult<Vec<S::SelectedType>> {
        if self.ordering.is_empty() {
            panic!("Tried to fetch a list without specifying the order. If this is intentional, use `fetch_unordered`.");
        }

        return self.fetch_unordered(db, selectable, None, 0).await;
    }

    pub async fn fetch_limited<S: QuerySelectable>(
        &self,
        db: &impl GenericClient,
        selectable: &S,
        limit: Option<usize>,
        offset: u64,
    ) -> QueryResult<Vec<S::SelectedType>> {
        if self.ordering.is_empty() {
            panic!("Tried to fetch a list without specifying the order. If this is intentional, use `fetch_unordered`.");
        }

        return self.fetch_unordered(db, selectable, limit, offset).await;
    }

    pub async fn fetch_unordered<S: QuerySelectable>(
        &self,
        db: &impl GenericClient,
        selectable: &S,
        limit: Option<usize>,
        offset: u64,
    ) -> QueryResult<Vec<S::SelectedType>> {
        let (query, bindings) = self.build_query(selectable, limit, offset);
        let rows = db.query(&query, &bindings.to_params()).await?;

        return Ok(rows.into_iter().map(|r| S::convert_row(&r, 0)).collect());
    }

    pub async fn fetch_page<S: QuerySelectable>(
        &self,
        db: &impl GenericClient,
        selectable: &S,
        limit: Option<usize>,
    ) -> QueryResult<(Vec<S::SelectedType>, bool)> {
        if self.ordering.is_empty() {
            panic!("Tried to fetch a page without specifying the order. If this is intentional, use `fetch_unordered`.");
        }

        // +1 for us to determine if there is a continuation
        let (query, bindings) = self.build_query(selectable, limit.map(|n| n + 1), 0);
        let mut rows = db.query(&query, &bindings.to_params()).await?;

        let mut has_continuation = false;
        if let Some(limit) = limit {
            if rows.len() > limit {
                has_continuation = true;
                rows.truncate(limit);
            }
        }

        return Ok((
            rows.into_iter().map(|r| S::convert_row(&r, 0)).collect(),
            has_continuation,
        ));
    }

    pub fn build_count_query(&self) -> (String, QueryBindings<'a>) {
        let mut bindings = QueryBindings::new();

        let mut q = String::with_capacity(2048);
        q.write_str("SELECT COUNT(*)").unwrap();

        add_tables_to_query(&self.tables, &mut q, &mut bindings);
        add_conditions_to_query(&self.conditions, &mut q, &mut bindings);

        return (q, bindings);
    }

    pub async fn count(&self, db: &impl GenericClient) -> QueryResult<i64> {
        let (query, bindings) = self.build_count_query();
        let row = db.query_one(&query, &bindings.to_params()).await?;

        return Ok(row.get::<_, i64>(0));
    }
}

fn add_tables_to_query<'a>(
    tables: &[QueryTable<'a>],
    query: &mut String,
    bindings: &mut QueryBindings<'a>,
) {
    for t in tables {
        match t {
            QueryTable::From { table, alias } => {
                write!(query, " FROM {table} AS {alias}").unwrap();
            }
            QueryTable::InnerJoin { table, alias, cond } => {
                write!(query, " INNER JOIN {table} AS {alias} ON ").unwrap();
                cond.add_to_query(query, bindings);
            }
            QueryTable::LeftJoin { table, alias, cond } => {
                write!(query, " LEFT JOIN {table} AS {alias} ON ").unwrap();
                cond.add_to_query(query, bindings);
            }
        }
    }
}

fn add_conditions_to_query<'a>(
    conditions: &[BoxedQueryExpression<'a>],
    query: &mut String,
    bindings: &mut QueryBindings<'a>,
) {
    if !conditions.is_empty() {
        query.write_str(" WHERE ").unwrap();
        conditions[0].add_to_query(query, bindings);

        for cond in &conditions[1..] {
            query.write_str(" AND ").unwrap();
            cond.add_to_query(query, bindings);
        }
    }
}
