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

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

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

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

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

pub struct QueryBuilder<'a> {
    tables: Vec<QueryTable<'a>>,
    conditions: Vec<Box<QueryExpr<'a>>>,
    ordering: Vec<(Box<QueryExpr<'a>>, OrderDirection)>,
    limit: u32,
    offset: u32,
    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(),
            limit: 0,
            offset: 0,
            row_lock: None,
        };
    }

    pub fn inner_join<E: Entity>(
        mut self,
        join_table: &NonNullTableAlias<E>,
        condition: Box<QueryExpr<'a>>,
    ) -> 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: Box<QueryExpr<'a>>,
    ) -> 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: Box<QueryExpr<'a>>,
    ) -> 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: Box<QueryExpr<'a>>,
    ) -> 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: Box<QueryExpr<'a>>) -> Self {
        self.conditions.push(cond);

        return self;
    }

    pub fn where_id<E: Entity>(
        mut self,
        table: &NonNullTableAlias<E>,
        id: &'a impl EntityIdLike<E::EntityIdType>,
    ) -> 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<(Box<QueryExpr<'a>>, OrderDirection)>) -> Self {
        self.ordering = ordering;

        return self;
    }

    pub fn with_limit(mut self, limit: u32) -> Self {
        self.limit = limit;

        return self;
    }

    pub fn with_offset(mut self, offset: u32) -> Self {
        self.offset = offset;

        return self;
    }

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

        return self;
    }

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

        let mut q = String::with_capacity(2048);
        q.write_str("SELECT ").expect(STRING_WRITE_OK);
        selectable.select_columns(&mut q, &mut bindings);

        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 ").expect(STRING_WRITE_OK);

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

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

        if self.limit > 0 {
            let param_num = bindings.add(&self.limit);
            write!(q, " LIMIT ${param_num}").expect(STRING_WRITE_OK);
        }

        if self.offset > 0 {
            let param_num = bindings.add(&self.offset);
            write!(q, " OFFSET ${param_num}").expect(STRING_WRITE_OK);
        }

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

        return (q, bindings);
    }

    pub async fn fetch_one<S: QuerySelectable>(
        &'a self,
        db: &impl GenericClient,
        selectable: &S,
    ) -> QueryResult<S::SelectedType> {
        let (query, bindings) = self.build_query(selectable);
        let row = db.query_one(&query, bindings.as_ref()).await?;

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

    pub async fn fetch_optional<S: QuerySelectable>(
        &'a self,
        db: &impl GenericClient,
        selectable: &S,
    ) -> QueryResult<Option<S::SelectedType>> {
        let (query, bindings) = self.build_query(selectable);
        let row = db.query_opt(&query, bindings.as_ref()).await?;

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

    pub async fn fetch_list<S: QuerySelectable>(
        &'a 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).await;
    }

    pub async fn fetch_unordered<S: QuerySelectable>(
        &'a self,
        db: &impl GenericClient,
        selectable: &S,
    ) -> QueryResult<Vec<S::SelectedType>> {
        let (query, bindings) = self.build_query(selectable);
        let rows = db.query(&query, bindings.as_ref()).await?;

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

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

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

        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.as_ref()).await?;

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

fn add_tables_to_query<'a>(
    tables: &'a [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}").expect(STRING_WRITE_OK);
            }
            QueryTable::InnerJoin { table, alias, cond } => {
                write!(query, " INNER JOIN {table} AS {alias} ON ").expect(STRING_WRITE_OK);
                cond.add_to_query(query, bindings);
            }
            QueryTable::LeftJoin { table, alias, cond } => {
                write!(query, " LEFT JOIN {table} AS {alias} ON ").expect(STRING_WRITE_OK);
                cond.add_to_query(query, bindings);
            }
        }
    }
}

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

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