use std::fmt::Display;
use serde::{Deserialize, Serialize};
use sqlx::{Any, Pool};
use crate::error::OrmError;
use crate::pagination::{TotalPagination};

#[derive(Clone, Debug, Serialize, Deserialize)]
pub enum WhereType {
    And,
    Or
}

#[derive(Clone, Debug, Serialize, Deserialize)]
pub enum JoinType {
    LeftJoin,
    RightJoin,
    InnerJoin,
    OuterJoin,
}

impl Display for JoinType {
    fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
        write!(f, "{}", match self {
            JoinType::InnerJoin => "INNER JOIN",
            JoinType::LeftJoin => "LEFT JOIN",
            JoinType::OuterJoin => "OUTER JOIN",
            JoinType::RightJoin => "RIGHT JOIN",
        })
    }
}

impl Display for WhereType {
    fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
        let str = match self {
            WhereType::And => "AND".to_string(),
            WhereType::Or => "OR".to_string()
        };
        write!(f, "{}", str)
    }
}

#[derive(Clone, Debug, Serialize, Deserialize)]
pub struct WhereStatement {
    pub where_type: WhereType,
    pub statement: String,
}

#[derive(Clone, Debug, Serialize, Deserialize)]
pub struct DropQuery {
    table_name: String,
}

impl DropQuery {
    pub fn build(&self) -> String {
        let query: Vec<String> = vec![format!("DROP TABLE {}", self.table_name)];
        format!("{};", query.join("\n"))
    }
}

#[derive(Clone, Debug, Serialize, Deserialize)]
pub struct DeleteQuery {
    table_name: String,
    columns: Vec<String>,
    where_statements: Vec<WhereStatement>,
}

impl DeleteQuery {
    pub fn _where(&mut self, where_statement: WhereStatement) -> DeleteQuery {
        self.where_statements.push(where_statement);
        self.clone()
    }
    pub fn build(&self) -> String {
        let mut query: Vec<String> = vec![format!("DELETE FROM {}", self.table_name)];

        if !self.where_statements.is_empty() {
            query.push(format!("WHERE {}", self.where_statements.iter().enumerate().map(|(index, statement)| {
                if index == 0 {
                    statement.statement.to_string()
                } else {
                    format!("{} {}", statement.where_type, statement.statement)
                }
            }).collect::<Vec<String>>().join(" ")));
        }
        
        format!("{};", query.join("\n"))
    }
}

#[derive(Clone, Debug, Serialize, Deserialize)]
pub struct InsertQuery {
    table_name: String,
    primary_key: String,
    sequence_name: Option<String>,
    columns: Vec<String>,
    rows: usize,
}

impl InsertQuery {
    pub fn remove_column(&mut self, column_name: String) -> InsertQuery {
        self.columns = self.columns.iter().filter(|&column| *column != column_name).cloned().collect::<Vec<String>>();
        self.clone()
    }
    
    pub fn set_rows(&mut self, rows: usize) -> InsertQuery {
        self.rows = rows;
        self.clone()
    }
    
    pub fn get_columns_fragment(&self) -> String {
        let mut fragment: Vec<String> = Vec::new();
        let mut index: usize = 1;
        if !self.columns.contains(&self.primary_key) {
            for _ in 1..(self.rows + 1) {
                let mut values: Vec<String> = if self.sequence_name.is_some() {
                    let sequence_name = self.sequence_name.clone().unwrap();
                    vec![format!("nextval('{}')", sequence_name)]
                } else {
                    vec![]
                };
                values.extend(self.columns
                    .iter()
                    .map(|_column| {
                        let result = format!("${}", index);
                        index += 1;
                        result
                    }).collect::<Vec<String>>());
                fragment.push(format!("({})", values.join(", ")));
            }
        } else {
            for _ in 1..(self.rows + 1) {
                let values = self.columns
                    .iter()
                    .map(|_column| {
                        let result = format!("${}", index);
                        index += 1;
                        result
                    }).collect::<Vec<String>>();
                fragment.push(format!("({})", values.join(", ")));
            }
        };
        fragment.join(",")
    }

    pub fn build(&self) -> String {
        let mut columns = self.columns
            .iter()
            .map(|column| {
                column.to_string()
            }).collect::<Vec<String>>();
        if !columns.contains(&self.primary_key) && self.sequence_name.is_some() {
            columns = vec![self.primary_key.clone()];
            columns.extend(self.columns.clone());
        }
        let query: Vec<String> = vec![format!("INSERT INTO {} ({}) VALUES {} RETURNING {}", self.table_name, 
                columns.join(", "), 
                self.get_columns_fragment(),
            self.primary_key)];
        format!("{};", query.join("\n"))
    }
}

#[derive(Clone, Debug, Serialize, Deserialize)]
pub struct SelectQuery {
    table_name: String,
    columns: Vec<String>,
    joins: Vec<String>,
    where_statements: Vec<WhereStatement>,
    group_by: Vec<String>,
    having: Option<String>,
    order_by: Vec<String>,
    limit: Option<i64>,
    offset: Option<i64>,
}

impl SelectQuery {
    pub fn join(&mut self, join: String) -> SelectQuery {
        self.joins.push(join);
        self.clone()
    }

    pub fn limit(&mut self, limit: i64) -> SelectQuery {
        self.limit = Some(limit);
        self.clone()
    }

    pub fn offset(&mut self, offset: i64) -> SelectQuery {
        self.offset = Some(offset);
        self.clone()
    }

    pub fn _where(&mut self, where_statement: WhereStatement) -> SelectQuery {
        self.where_statements.push(where_statement);
        self.clone()
    }

    pub fn group_by(&mut self, group_by: String) -> SelectQuery {
        self.group_by.push(group_by);
        self.clone()
    }

    pub fn having(&mut self, having: String) -> SelectQuery {
        self.having = Some(having);
        self.clone()
    }

    pub fn order_by(&mut self, order_by: String) -> SelectQuery {
        self.order_by.push(order_by);
        self.clone()
    }

    pub fn remove_column(mut self, column_name: String) -> SelectQuery {
        self.columns = self.columns.iter().filter(|&column| *column != column_name).cloned().collect::<Vec<String>>();
        self.clone()
    }

    fn build_query(&self, is_pagination: bool) -> String {
        let mut query: Vec<String> = if is_pagination {
            vec![format!("SELECT COUNT(1) as rows\nFROM {}", self.table_name)]
        } else {
            vec![format!("SELECT {}\nFROM {}", self.columns.join(", "), self.table_name)]
        };
        self.joins.iter().for_each(|join| {
            query.push(join.to_string());
        });
        if !self.where_statements.is_empty() {
            query.push(format!("WHERE {}", self.where_statements.iter().enumerate().map(|(index, statement)| {
                if index == 0 {
                    statement.statement.to_string()
                } else {
                    format!("{} {}", statement.where_type, statement.statement)
                }
            }).collect::<Vec<String>>().join(" ")));
        }
        if !self.group_by.is_empty() {
            query.push(format!("GROUP BY {}", self.group_by.join(", ")));
        }
        if self.having.is_some() {
            query.push(format!("HAVING {}", self.having.clone().unwrap()));
        }
        if !self.order_by.is_empty() {
            query.push(format!("ORDER BY {}", self.order_by.join(", ")));
        }
        if self.limit.is_some() {
            query.push(format!("LIMIT {}", self.limit.unwrap()));
        }
        if self.offset.is_some() {
            query.push(format!("OFFSET {}", self.offset.unwrap()));
        }
        format!("{};", query.join("\n"))
    }

    pub async fn get_pagination_results_query(&self, pool: &Pool<Any>) -> Result<TotalPagination, OrmError> {
        let query = self.build_query(true);
        let result = sqlx::query_as::<_, TotalPagination>(query.as_str()).fetch_one(pool).await?;
        Ok(result)
    }

    pub fn build(&self) -> String {
        self.build_query(false)
    }
}

#[derive(Clone, Debug, Serialize, Deserialize)]
pub struct UpdateQuery {
    table_name: String,
    columns: Vec<String>,
    where_statements: Vec<WhereStatement>,
}

impl UpdateQuery {

    pub fn _where(&mut self, where_statement: WhereStatement) -> UpdateQuery {
        self.where_statements.push(where_statement);
        self.clone()
    }
    
    pub fn get_index(&self) -> usize {
        self.columns.len()
    }
    
    pub fn build(&self) -> String {
        let mut query: Vec<String> = vec![format!("UPDATE {}", self.table_name), 
                                      format!("SET {}", self.columns.iter().enumerate().map(|(index, column)| {
            format!("{} = ${}", column, index + 1)
        }).collect::<Vec<String>>().join(", "))];
        if !self.where_statements.is_empty() {
            query.push(format!("WHERE {}", self.where_statements.iter().enumerate().map(|(index, statement)| {
                if index == 0 {
                    statement.statement.to_string()
                } else {
                    format!("{} {}", statement.where_type, statement.statement)
                }
            }).collect::<Vec<String>>().join(" ")));
        }
        format!("{};", query.join("\n"))
    }
}

#[derive(Clone, Debug, Serialize, Deserialize)]
pub struct Query;

impl Query {
    
    pub fn update(table: String, columns: Vec<String>) -> UpdateQuery {
        UpdateQuery {
            columns,
            table_name: table,
            where_statements: Vec::new(),
        }
    }
    
    pub fn select(columns: Vec<String>, from: String) -> SelectQuery {
        SelectQuery {
            table_name: from,
            columns,
            joins: Vec::new(),
            limit: None,
            order_by: Vec::new(),
            group_by: Vec::new(),
            having: None,
            where_statements: Vec::new(),
            offset: None,
        }
    }

    pub fn insert(into: String, columns: Vec<String>, primary_key: String, sequence_name: Option<String>) -> InsertQuery {
        InsertQuery {
            table_name: into,
            columns,
            primary_key,
            sequence_name,
            rows: 1,
        }
    }

    pub fn delete(from: String) -> DeleteQuery {
        DeleteQuery {
            table_name: from,
            columns: Vec::new(),
            where_statements: Vec::new(),
        }
    }

    pub fn drop(table_name: String) -> DropQuery {
        DropQuery {
            table_name,
        }
    }
    
}

#[cfg(test)]
mod query_tests {
    use crate::query::{Query, WhereStatement};
    use crate::query::WhereType::And;

    #[test]
    pub fn build_select() {
        let query = Query::select(vec!["id".to_string(), "username".to_string(), "email".to_string()], "User".to_string())
            .join("LEFT JOIN Book ON Book.user_id = User.id".to_string())
            ._where(WhereStatement {
                statement: "User.username = $1".to_string(),
                where_type: And,
            })
            .group_by("User.email".to_string())
            .having("COUNT(User.id) > 0".to_string())
            .order_by("User.email".to_string())
            .limit(200)
            .offset(5);
        assert_eq!(
            "SELECT id, username, email\n\
            FROM User\n\
            LEFT JOIN Book ON Book.user_id = User.id\n\
            WHERE User.username = $1\n\
            GROUP BY User.email\n\
            HAVING COUNT(User.id) > 0\n\
            ORDER BY User.email\n\
            LIMIT 200\n\
            OFFSET 5;", query.build());
    }
    
    #[test]
    pub fn build_update() {
        let mut query = Query::update("User".to_string(), vec!["username".to_string()]);
        query = query._where(WhereStatement {
            statement: "id = $2".to_string(),
            where_type: And,
        });
        let query = query.build();
        assert_eq!(query, "UPDATE User\nSET username = $1\nWHERE id = $2;");
    }

    #[test]
    pub fn build_insert() {
        let query = Query::insert("User".to_string(), vec!["id".to_string(), "username".to_string(), "email".to_string()], "id".to_string(), None).build();
        assert_eq!(query, "INSERT INTO User (id, username, email) VALUES ($1, $2, $3) RETURNING id;");
        let query = Query::insert("User".to_string(), vec!["id".to_string(), "username".to_string(), "email".to_string()], "id".to_string(), Some("some_sequence".to_string())).build();
        assert_eq!(query, "INSERT INTO User (id, username, email) VALUES ($1, $2, $3) RETURNING id;");
        let query = Query::insert("User".to_string(), vec!["username".to_string(), "email".to_string()], "id".to_string(), Some("some_sequence".to_string())).build();
        assert_eq!(query, "INSERT INTO User (id, username, email) VALUES (nextval('some_sequence'), $1, $2) RETURNING id;");
        let mut query = Query::insert("User".to_string(), vec!["username".to_string(), "email".to_string()], "id".to_string(), Some("some_sequence".to_string()));
        query.set_rows(2);
        assert_eq!(query.build(), "INSERT INTO User (id, username, email) VALUES (nextval('some_sequence'), $1, $2),(nextval('some_sequence'), $3, $4) RETURNING id;");
    }

    #[test]
    pub fn build_delete() {
        let mut query = Query::delete("User".to_string());
        query._where(WhereStatement {
            statement: "User.username = $1".to_string(),
            where_type: And,
        });
        assert_eq!(query.build(), "DELETE FROM User\nWHERE User.username = $1;");
    }

    #[test]
    pub fn build_drop() {
        let query = Query::drop("User".to_string());
        assert_eq!(query.build(), "DROP TABLE User;");
    }
}