use crate::types::{DbMode, Mode, Params, TableOptions};
use crate::Connection;
use async_std::net::TcpStream;
use chrono::Local;
use json::{array, object, JsonValue, Null};
use log::{error, info, warn};
use std::thread;
use tiberius::numeric::Numeric;
use tiberius::time::time::PrimitiveDateTime;
use tiberius::{AuthMethod, Client, ColumnType, Config, EncryptionLevel, Query};

lazy_static::lazy_static! {
    static ref MSSQL_RUNTIME: tokio::runtime::Runtime = {
        tokio::runtime::Builder::new_multi_thread()
            .worker_threads(4)
            .enable_all()
            .build()
            .expect("Failed to create MSSQL tokio runtime")
    };
}

#[derive(Clone, Debug)]
pub struct Mssql {
    /// 当前连接配置
    pub connection: Connection,
    /// 当前选中配置
    pub default: String,

    pub params: Params,
}

impl Mssql {
    pub fn connect(connection: Connection, default: String) -> Result<Self, String> {
        Ok(Self {
            connection,
            default,
            params: Params::default("mssql"),
        })
    }

    async fn create_client(config: &Connection) -> Result<Client<TcpStream>, String> {
        let mut conf = Config::new();
        conf.host(&config.hostname);
        let port = config.hostport.parse::<u16>().unwrap_or(1433);
        conf.port(port);
        conf.database(&config.database);
        conf.authentication(AuthMethod::sql_server(&config.username, &config.userpass));
        conf.encryption(EncryptionLevel::NotSupported);

        let tcp = TcpStream::connect(conf.get_addr())
            .await
            .map_err(|e| format!("TCP connect failed: {e}"))?;

        if let Err(e) = tcp.set_nodelay(true) {
            warn!("Failed to set TCP nodelay: {e}");
        }

        Client::connect(conf, tcp)
            .await
            .map_err(|e| format!("Client connect failed: {e}"))
    }

    async fn execute_query(config: Connection, sql: String) -> (bool, JsonValue) {
        let mut client = match Self::create_client(&config).await {
            Ok(c) => c,
            Err(e) => {
                error!("MSSQL connection failed: {e}");
                return (false, array![]);
            }
        };

        let select = Query::new(sql.clone());
        let stream = match select.query(&mut client).await {
            Ok(e) => e,
            Err(e) => {
                error!("MSSQL query failed: {e}");
                return (false, array![]);
            }
        };

        let rows = match stream.into_results().await {
            Ok(e) => e,
            Err(e) => {
                error!("MSSQL results failed: {e}");
                return (false, array![]);
            }
        };

        if rows.is_empty() || rows[0].is_empty() {
            return (true, array![]);
        }

        let cols = match rows[0].first() {
            Some(row) => row.columns(),
            None => return (true, array![]),
        };

        let mut fields = object! {};
        for col in cols.iter() {
            fields[col.name()] = match col.column_type() {
                ColumnType::Image
                | ColumnType::Datetimen
                | ColumnType::Datetime
                | ColumnType::Text
                | ColumnType::NVarchar
                | ColumnType::NText
                | ColumnType::BigChar
                | ColumnType::BigVarChar => "string",
                ColumnType::Int2 | ColumnType::Int1 | ColumnType::Int4 | ColumnType::Int8 => "i64",
                ColumnType::Intn => "i32",
                ColumnType::Numericn | ColumnType::Money => "f64",
                _ => {
                    info!("未知: {} : {:?}", col.name(), col.column_type());
                    "string"
                }
            }
            .into()
        }

        let mut list = array![];
        for row in rows[0].iter() {
            let mut row_data = object! {};
            for column in row.columns() {
                let field = column.name();
                row_data[field] = Self::extract_column_value(row, field, column.column_type());
            }
            let _ = list.push(row_data);
        }
        (true, list)
    }

    fn extract_column_value(row: &tiberius::Row, field: &str, col_type: ColumnType) -> JsonValue {
        match col_type {
            ColumnType::BigVarChar
            | ColumnType::BigChar
            | ColumnType::Text
            | ColumnType::NText
            | ColumnType::NVarchar => match row.try_get::<&str, _>(field) {
                Ok(Some(e)) => e.into(),
                Ok(None) => Null,
                Err(e) => {
                    error!("String column {field}: {e}");
                    "".into()
                }
            },
            ColumnType::Image => match row.try_get::<&[u8], _>(field) {
                Ok(Some(e)) => e.into(),
                Ok(None) => Null,
                Err(e) => {
                    error!("Image column {field}: {e}");
                    "".into()
                }
            },
            ColumnType::Datetimen | ColumnType::Datetime => {
                match row.try_get::<PrimitiveDateTime, _>(field) {
                    Ok(Some(e)) => e.to_string().into(),
                    Ok(None) => Null,
                    Err(e) => {
                        error!("Datetime column {field}: {e}");
                        "".into()
                    }
                }
            }
            ColumnType::Intn
            | ColumnType::Int1
            | ColumnType::Int2
            | ColumnType::Int4
            | ColumnType::Int8 => Self::extract_int_value(row, field),
            ColumnType::Numericn | ColumnType::Money => Self::extract_numeric_value(row, field),
            _ => {
                error!("Unknown column type: {:?}", col_type);
                "".into()
            }
        }
    }

    fn extract_int_value(row: &tiberius::Row, field: &str) -> JsonValue {
        if let Ok(Some(v)) = row.try_get::<i32, _>(field) {
            return v.into();
        }
        if let Ok(Some(v)) = row.try_get::<i16, _>(field) {
            return v.into();
        }
        if let Ok(Some(v)) = row.try_get::<u8, _>(field) {
            return v.into();
        }
        if let Ok(Some(v)) = row.try_get::<i64, _>(field) {
            return v.into();
        }
        if let Ok(None) = row.try_get::<i32, _>(field) {
            return Null;
        }
        0.into()
    }

    fn extract_numeric_value(row: &tiberius::Row, field: &str) -> JsonValue {
        if let Ok(Some(v)) = row.try_get::<f64, _>(field) {
            return v.into();
        }
        if let Ok(Some(v)) = row.try_get::<Numeric, _>(field) {
            return v.to_string().parse::<f64>().unwrap_or(0.0).into();
        }
        if let Ok(None) = row.try_get::<f64, _>(field) {
            return Null;
        }
        0.0.into()
    }

    fn query(&mut self, sql: String) -> (bool, JsonValue) {
        if self.connection.debug {
            info!("sql: {sql}");
        }
        MSSQL_RUNTIME.block_on(Self::execute_query(self.connection.clone(), sql))
    }

    async fn execute_sql(config: Connection, sql: String) -> (bool, JsonValue) {
        let mut client = match Self::create_client(&config).await {
            Ok(c) => c,
            Err(e) => {
                error!("MSSQL connection failed: {e}");
                return (false, JsonValue::from(e));
            }
        };

        let result = client.execute(sql.clone(), &[]).await;
        match result {
            Ok(r) => {
                let rows = r.total();
                (true, JsonValue::from(rows))
            }
            Err(e) => {
                error!("MSSQL execute failed: {e}");
                (false, JsonValue::from(format!("{e}")))
            }
        }
    }

    fn execute(&mut self, sql: &str) -> (bool, JsonValue) {
        if self.connection.debug {
            info!("sql: {sql}");
        }
        MSSQL_RUNTIME.block_on(Self::execute_sql(self.connection.clone(), sql.to_string()))
    }
}

impl DbMode for Mssql {
    fn database_tables(&mut self) -> JsonValue {
        let sql = "SELECT table_name FROM INFORMATION_SCHEMA.TABLES".to_string();
        match self.sql(sql.as_str()) {
            Ok(e) => {
                let mut list = vec![];
                for item in e.members() {
                    list.push(item["table_name"].clone());
                }
                list.into()
            }
            Err(_) => {
                array![]
            }
        }
    }
    fn database_create(&mut self, name: &str) -> bool {
        let sql = format!("CREATE DATABASE [{name}]");
        let (state, _) = self.execute(sql.as_str());
        state
    }

    fn truncate(&mut self, table: &str) -> bool {
        let sql = format!("TRUNCATE TABLE [{table}]");
        let (state, _) = self.execute(sql.as_str());
        state
    }
}

impl Mode for Mssql {
    fn table_create(&mut self, _options: TableOptions) -> JsonValue {
        todo!()
    }

    fn table_update(&mut self, _options: TableOptions) -> JsonValue {
        todo!()
    }

    fn table_info(&mut self, table: &str) -> JsonValue {
        let sql = format!("SELECT COLUMN_NAME as name, DATA_TYPE as type FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '{table}'");
        let (state, data) = self.query(sql);
        if state {
            let mut result = object! {};
            for item in data.members() {
                let name = item["name"].to_string();
                result[name.as_str()] = item.clone();
            }
            result
        } else {
            object! {}
        }
    }

    fn table_is_exist(&mut self, name: &str) -> bool {
        let sql = format!(
            "SELECT COUNT(*) as count FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '{name}'"
        );
        let (state, data) = self.query(sql);
        if state {
            data[0]["count"].as_i64().unwrap_or(0) > 0
        } else {
            false
        }
    }
    fn table(&mut self, name: &str) -> &mut Self {
        self.params = Params::default(self.connection.mode.str().as_str());
        let table_name = format!("{}{}", self.connection.prefix, name);
        if !super::sql_safety::validate_table_name(&table_name) {
            error!("Invalid table name: {}", name);
        }
        self.params.table = table_name.clone();
        self.params.join_table = table_name;
        self
    }

    fn change_table(&mut self, name: &str) -> &mut Self {
        self.params.join_table = name.to_string();
        self
    }

    fn autoinc(&mut self) -> &mut Self {
        self.params.autoinc = true;
        self
    }

    fn timestamps(&mut self) -> &mut Self {
        self.params.timestamps = true;
        self
    }

    fn fetch_sql(&mut self) -> &mut Self {
        self.params.sql = true;
        self
    }

    fn order(&mut self, field: &str, by: bool) -> &mut Self {
        self.params.order[field] = {
            if by {
                "DESC"
            } else {
                "ASC"
            }
        }
        .into();
        self
    }

    fn group(&mut self, field: &str) -> &mut Self {
        let fields: Vec<&str> = field.split(",").collect();
        for field in fields.iter() {
            let field = field.to_string();
            self.params.group[field.as_str()] = field.clone().into();
            self.params.fields[field.as_str()] = field.clone().into();
        }
        self
    }

    fn distinct(&mut self) -> &mut Self {
        self.params.distinct = true;
        self
    }

    fn json(&mut self, field: &str) -> &mut Self {
        self.params.json[field] = field.into();
        self
    }

    fn column(&mut self, field: &str) -> JsonValue {
        self.field(field);
        self.group(field);
        let sql = self.params.select_sql();
        let (state, data) = self.query(sql);
        if state {
            let mut list = array![];
            for item in data.members() {
                let _ = list.push(item[field].clone());
            }
            list
        } else {
            array![]
        }
    }

    fn where_and(&mut self, field: &str, compare: &str, value: JsonValue) -> &mut Self {
        for f in field.split('|') {
            if !super::sql_safety::validate_field_name(f) {
                error!("Invalid field name: {}", f);
            }
        }
        if !super::sql_safety::validate_compare_orator(compare) {
            error!("Invalid compare operator: {}", compare);
        }
        match compare {
            "is" => {
                self.params.where_and.push(format!("{field} is {value}"));
            }
            "between" => {
                self.params.where_and.push(format!(
                    "{} between '{}' AND '{}'",
                    field, value[0], value[1]
                ));
            }
            "notin" => {
                let mut text = String::new();
                for item in value.members() {
                    text = format!("{text},'{item}'");
                }
                text = text.trim_start_matches(",").into();
                self.params
                    .where_and
                    .push(format!("{field} not in ({text})"));
            }
            "in" => {
                let mut text = String::new();
                for item in value.members() {
                    text = format!("{text},'{item}'");
                }
                text = text.trim_start_matches(",").into();
                self.params
                    .where_and
                    .push(format!("{field} {compare} ({text})"));
            }
            _ => {
                self.params
                    .where_and
                    .push(format!("{field} {compare} '{value}'"));
            }
        }
        self
    }

    fn where_or(&mut self, field: &str, compare: &str, value: JsonValue) -> &mut Self {
        for f in field.split('|') {
            if !super::sql_safety::validate_field_name(f) {
                error!("Invalid field name: {}", f);
            }
        }
        if !super::sql_safety::validate_compare_orator(compare) {
            error!("Invalid compare operator: {}", compare);
        }
        match compare {
            "between" => {
                self.params.where_or.push(format!(
                    "{} between '{}' AND '{}'",
                    field, value[0], value[1]
                ));
            }
            "notin" => {
                let mut text = String::new();
                for item in value.members() {
                    text = format!("{text},'{item}'");
                }
                text = text.trim_start_matches(",").into();
                self.params
                    .where_or
                    .push(format!("{field} not in ({text})"));
            }
            "in" => {
                let mut text = String::new();
                for item in value.members() {
                    text = format!("{text},'{item}'");
                }
                text = text.trim_start_matches(",").into();
                self.params
                    .where_or
                    .push(format!("{field} {compare} ({text})"));
            }
            _ => {
                self.params
                    .where_or
                    .push(format!("{field} {compare} '{value}'"));
            }
        }
        self
    }

    fn where_raw(&mut self, expr: &str) -> &mut Self {
        self.params.where_and.push(expr.to_string());
        self
    }

    fn where_in_sub(&mut self, field: &str, sub_sql: &str) -> &mut Self {
        self.params
            .where_and
            .push(format!("[{field}] IN ({sub_sql})"));
        self
    }

    fn where_not_in_sub(&mut self, field: &str, sub_sql: &str) -> &mut Self {
        self.params
            .where_and
            .push(format!("[{field}] NOT IN ({sub_sql})"));
        self
    }

    fn where_exists(&mut self, sub_sql: &str) -> &mut Self {
        self.params.where_and.push(format!("EXISTS ({sub_sql})"));
        self
    }

    fn where_not_exists(&mut self, sub_sql: &str) -> &mut Self {
        self.params
            .where_and
            .push(format!("NOT EXISTS ({sub_sql})"));
        self
    }

    fn where_column(&mut self, field_a: &str, compare: &str, field_b: &str) -> &mut Self {
        self.params.where_column = format!(
            "{}.`{}` {} {}.`{}`",
            self.params.table, field_a, compare, self.params.table, field_b
        );
        self
    }

    fn count(&mut self) -> JsonValue {
        self.params.fields = object! {};
        self.params.order = object! {};
        self.params.fields["count"] = "count(*) as count".into();
        let sql = self.params.select_sql();
        if self.params.sql {
            return JsonValue::from(sql.clone());
        }
        let (state, data) = self.query(sql);
        if state {
            data[0]["count"].clone()
        } else {
            JsonValue::from(0)
        }
    }

    fn max(&mut self, field: &str) -> JsonValue {
        self.params.fields[field] = format!("max({field}) as {field}").into();
        let sql = self.params.select_sql();
        let (state, data) = self.query(sql);
        if state {
            if data.len() > 1 {
                return data;
            }
            data[0][field].clone()
        } else {
            array![]
        }
    }

    fn min(&mut self, field: &str) -> JsonValue {
        self.params.fields[field] = format!("min({field}) as {field}").into();
        let sql = self.params.select_sql();
        let (state, data) = self.query(sql);
        if state {
            if data.len() > 1 {
                return data;
            }
            data[0][field].clone()
        } else {
            array![]
        }
    }

    fn sum(&mut self, field: &str) -> JsonValue {
        self.params.fields[field] = format!("sum({field}) as {field}").into();
        let sql = self.params.select_sql();
        let (state, data) = self.query(sql);
        if state {
            if data.len() > 1 {
                return data;
            }
            data[0][field].clone()
        } else {
            array![]
        }
    }

    fn avg(&mut self, field: &str) -> JsonValue {
        self.params.fields[field] = format!("avg({field}) as {field}").into();
        let sql = self.params.select_sql();
        let (state, data) = self.query(sql);
        if state {
            if data.len() > 1 {
                return data;
            }
            data[0][field].clone()
        } else {
            array![]
        }
    }

    fn having(&mut self, expr: &str) -> &mut Self {
        self.params.having.push(expr.to_string());
        self
    }

    fn select(&mut self) -> JsonValue {
        let sql = self.params.select_sql();
        if self.params.sql {
            return JsonValue::from(sql.clone());
        }
        let (state, data) = self.query(sql.clone());
        if state {
            data.clone()
        } else {
            if self.connection.debug {
                info!("{data:?}");
            }
            array![]
        }
    }

    fn find(&mut self) -> JsonValue {
        self.page(1, 1);
        let sql = self.params.select_sql();
        if self.params.sql {
            return JsonValue::from(sql.clone());
        }
        let (state, data) = self.query(sql.clone());
        if state {
            data[0].clone()
        } else {
            if self.connection.debug {
                info!("{data:#?}");
            }
            object! {}
        }
    }

    fn value(&mut self, field: &str) -> JsonValue {
        self.params.fields = object! {};
        self.params.fields[field] = field.into();
        self.params.page = 1;
        self.params.limit = 1;
        let sql = self.params.select_sql();
        if self.params.sql {
            return JsonValue::from(sql.clone());
        }
        let (state, data) = self.query(sql.clone());
        match state {
            true => data[0][field].clone(),
            false => {
                if self.connection.debug {
                    println!("{data:?}");
                }
                Null
            }
        }
    }

    fn insert(&mut self, mut data: JsonValue) -> JsonValue {
        let mut fields = vec![];
        let mut values = vec![];
        if !self.params.autoinc && data["id"].is_empty() {
            let thread_id = format!("{:?}", thread::current().id());
            let thread_num: u64 = thread_id
                .trim_start_matches("ThreadId(")
                .trim_end_matches(")")
                .parse()
                .unwrap_or(0);
            data["id"] = format!(
                "{:X}{:X}",
                Local::now().timestamp_nanos_opt().unwrap_or(0),
                thread_num
            )
            .into();
        }
        for (field, value) in data.entries() {
            fields.push(format!("[{field}]"));
            if value.is_string() || value.is_array() || value.is_object() {
                values.push(format!("'{}'", value.to_string().replace("'", "''")));
            } else if value.is_number() || value.is_boolean() || value.is_null() {
                values.push(format!("{value}"));
            } else {
                values.push(format!("'{value}'"));
            }
        }
        let fields_str = fields.join(",");
        let values_str = values.join(",");
        let sql = format!(
            "INSERT INTO {} ({}) VALUES ({});",
            self.params.table, fields_str, values_str
        );
        if self.params.sql {
            return JsonValue::from(sql.clone());
        }
        let (state, result) = self.execute(sql.as_str());
        match state {
            true => match self.params.autoinc {
                true => result,
                false => data["id"].clone(),
            },
            false => {
                let thread_id = format!("{:?}", thread::current().id());
                error!("insert失败: {thread_id} {result:?} {sql}");
                JsonValue::from("")
            }
        }
    }
    fn insert_all(&mut self, _data: JsonValue) -> JsonValue {
        todo!()
    }
    fn upsert(&mut self, _data: JsonValue, _conflict_fields: Vec<&str>) -> JsonValue {
        todo!()
    }
    fn page(&mut self, page: i32, limit: i32) -> &mut Self {
        self.params.page = page;
        self.params.limit = limit;
        // self.params.top = format!("(select ROW_NUMBER() OVER(order by rand()) as ROW,* from {}) as ", self.params.table);
        // self.params.top2 = format!("t.ROW between {} and {}", (page - 1) * limit + 1, page * limit);
        self.params.top2 = format!(
            "t.ROW between {} and {}",
            (page - 1) * limit + 1,
            page * limit
        );
        self
    }

    fn limit(&mut self, count: i32) -> &mut Self {
        self.params.limit_only = count;
        self
    }

    fn update(&mut self, data: JsonValue) -> JsonValue {
        let mut values = vec![];
        for (field, value) in data.entries() {
            if field == "id" {
                continue;
            }
            if value.is_string() || value.is_array() || value.is_object() {
                values.push(format!(
                    "[{field}]='{}'",
                    value.to_string().replace("'", "''")
                ));
            } else if value.is_number() || value.is_boolean() || value.is_null() {
                values.push(format!("[{field}]={value}"));
            } else {
                values.push(format!("[{field}]='{value}'"));
            }
        }
        let values_str = values.join(",");
        let where_sql = self.params.where_sql();
        let sql = format!(
            "UPDATE {} SET {} {};",
            self.params.table, values_str, where_sql
        );
        if self.params.sql {
            return JsonValue::from(sql.clone());
        }
        let (state, result) = self.execute(sql.as_str());
        match state {
            true => result,
            false => {
                error!("update失败: {result:?} {sql}");
                JsonValue::from(0)
            }
        }
    }

    fn update_all(&mut self, data: JsonValue) -> JsonValue {
        let mut success_count = 0;
        for item in data.members() {
            if item["id"].is_empty() {
                continue;
            }
            let id = item["id"].to_string();
            let mut values = vec![];
            for (field, value) in item.entries() {
                if field == "id" {
                    continue;
                }
                if value.is_string() || value.is_array() || value.is_object() {
                    values.push(format!(
                        "[{field}]='{}'",
                        value.to_string().replace("'", "''")
                    ));
                } else if value.is_number() || value.is_boolean() || value.is_null() {
                    values.push(format!("[{field}]={value}"));
                } else {
                    values.push(format!("[{field}]='{value}'"));
                }
            }
            let values_str = values.join(",");
            let sql = format!(
                "UPDATE {} SET {} WHERE [id]='{}';",
                self.params.table, values_str, id
            );
            let (state, _) = self.execute(sql.as_str());
            if state {
                success_count += 1;
            }
        }
        JsonValue::from(success_count)
    }

    fn delete(&mut self) -> JsonValue {
        let where_sql = self.params.where_sql();
        let sql = format!("DELETE FROM {} {};", self.params.table, where_sql);
        if self.params.sql {
            return JsonValue::from(sql.clone());
        }
        let (state, result) = self.execute(sql.as_str());
        match state {
            true => result,
            false => {
                error!("delete失败: {result:?} {sql}");
                JsonValue::from(0)
            }
        }
    }

    fn field(&mut self, field: &str) -> &mut Self {
        let list: Vec<&str> = field.split(",").collect();
        for item in list.iter() {
            self.params.fields[item.to_string().as_str()] = item.to_string().into();
        }
        self
    }

    fn field_raw(&mut self, expr: &str) -> &mut Self {
        self.params.fields[expr] = expr.into();
        self
    }

    fn hidden(&mut self, name: &str) -> &mut Self {
        let hidden: Vec<&str> = name.split(",").collect();
        let sql = format!(
            "SELECT COLUMN_NAME as name FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '{}'",
            self.params.table
        );
        let (_, data) = self.query(sql);
        for item in data.members() {
            if let Some(name) = item["name"].as_str() {
                if !hidden.contains(&name) {
                    self.params.fields[name] = name.into();
                }
            }
        }
        self
    }

    fn transaction(&mut self) -> bool {
        let sql = "BEGIN TRANSACTION";
        let (state, _) = self.execute(sql);
        state
    }

    fn commit(&mut self) -> bool {
        let sql = "COMMIT";
        let (state, _) = self.execute(sql);
        state
    }

    fn rollback(&mut self) -> bool {
        let sql = "ROLLBACK";
        let (state, _) = self.execute(sql);
        state
    }

    fn sql(&mut self, sql: &str) -> Result<JsonValue, String> {
        let (state, data) = self.query(sql.to_string());
        match state {
            true => Ok(data),
            false => Err("".to_string()),
        }
    }
    fn sql_execute(&mut self, sql: &str) -> Result<JsonValue, String> {
        let (state, data) = self.execute(sql);
        match state {
            true => Ok(data),
            false => Err(data.to_string()),
        }
    }

    fn inc(&mut self, field: &str, num: f64) -> &mut Self {
        self.params.inc_dec[field] = num.into();
        self
    }

    fn dec(&mut self, field: &str, num: f64) -> &mut Self {
        self.params.inc_dec[field] = (-num).into();
        self
    }

    fn buildsql(&mut self) -> String {
        self.fetch_sql();
        let sql = self.select().to_string();
        format!("( {} ) {}", sql, self.params.table)
    }

    fn join(
        &mut self,
        main_table: &str,
        main_fields: &str,
        right_table: &str,
        right_fields: &str,
    ) -> &mut Self {
        let main_table = if main_table.is_empty() {
            self.params.table.clone()
        } else {
            main_table.to_string()
        };
        self.params.join_table = right_table.to_string();
        self.params.join.push(format!(" LEFT JOIN {right_table} ON {main_table}.{main_fields} = {right_table}.{right_fields} "));
        self
    }

    fn join_inner(&mut self, table: &str, main_fields: &str, second_fields: &str) -> &mut Self {
        let main_fields = if main_fields.is_empty() {
            "id"
        } else {
            main_fields
        };
        let second_fields = if second_fields.is_empty() {
            self.params.table.clone()
        } else {
            second_fields.to_string().clone()
        };
        let sec_table_name = format!("{}{}", table, "_2");
        let second_table = format!("{} {}", table, sec_table_name.clone());
        self.params.join_table = sec_table_name.clone();
        self.params.join.push(format!(
            " INNER JOIN {} ON {}.{} = {}.{}",
            second_table, self.params.table, main_fields, sec_table_name, second_fields
        ));
        self
    }

    fn join_right(
        &mut self,
        main_table: &str,
        main_fields: &str,
        right_table: &str,
        right_fields: &str,
    ) -> &mut Self {
        let main_table = if main_table.is_empty() {
            self.params.table.clone()
        } else {
            main_table.to_string()
        };
        self.params.join_table = right_table.to_string();
        self.params.join.push(format!(" RIGHT JOIN {right_table} ON {main_table}.{main_fields} = {right_table}.{right_fields} "));
        self
    }

    fn join_full(
        &mut self,
        main_table: &str,
        main_fields: &str,
        right_table: &str,
        right_fields: &str,
    ) -> &mut Self {
        let main_table = if main_table.is_empty() {
            self.params.table.clone()
        } else {
            main_table.to_string()
        };
        self.params.join_table = right_table.to_string();
        self.params.join.push(format!(" FULL OUTER JOIN {right_table} ON {main_table}.{main_fields} = {right_table}.{right_fields} "));
        self
    }

    fn union(&mut self, sub_sql: &str) -> &mut Self {
        self.params.unions.push(format!("UNION {sub_sql}"));
        self
    }

    fn union_all(&mut self, sub_sql: &str) -> &mut Self {
        self.params.unions.push(format!("UNION ALL {sub_sql}"));
        self
    }

    fn lock_for_update(&mut self) -> &mut Self {
        self.params.lock_mode = "FOR UPDATE".to_string();
        self
    }

    fn lock_for_share(&mut self) -> &mut Self {
        self.params.lock_mode = "FOR SHARE".to_string();
        self
    }

    fn location(&mut self, field: &str) -> &mut Self {
        self.params.location[field] = field.into();
        self
    }

    fn join_fields(&mut self, fields: Vec<&str>) -> &mut Self {
        for field in fields {
            self.params.fields[field] = format!("{field} as {}", field.replace(".", "_")).into();
        }
        self
    }

    fn update_column(&mut self, field_a: &str, compare: &str) -> &mut Self {
        self.params
            .update_column
            .push(format!("{field_a} = {compare}"));
        self
    }
}
