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

#[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"),
        })
    }
    fn query(&mut self, sql: String) -> (bool, JsonValue) {
        if self.connection.debug{
            info!("sql: {sql}");
        }
        let rt = tokio::runtime::Runtime::new().unwrap();
        async fn execute(config: Connection, sql: String) -> (bool, JsonValue) {
            let mut conf = Config::new();
            conf.host(config.hostname);
            conf.port(config.hostport.parse::<u16>().unwrap());
            conf.database(config.database);
            conf.authentication(AuthMethod::sql_server(config.username, config.userpass));
            conf.encryption(EncryptionLevel::NotSupported);

            let tcp = match TcpStream::connect(conf.get_addr()).await {
                Ok(e) => e,
                Err(_) => {
                    return (false, array![]);
                }
            };
            tcp.set_nodelay(true).unwrap();

            let mut client = match Client::connect(conf, tcp).await {
                Ok(e) => e,
                Err(_) => {
                    return (false, array![]);
                }
            };
            let select = Query::new(sql.clone());

            let stream = match select.query(&mut client).await {
                Ok(e) => e,
                Err(_) => {
                    return (false, array![]);
                }
            };


            let rows = match stream.into_results().await {
                Ok(e) => e,
                Err(e) => {
                    error!("{e}");
                    return (false, array![]);
                }
            };
            let mut list = array![];
            if rows.is_empty() {
                return (false, list);
            }
            if rows[0].is_empty() {
                return (false, list);
            }
            let cols = rows[0].first().unwrap().columns();
            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()
            }
            for row in rows[0].iter() {
                let mut row_data = object! {};
                for column in row.columns() {
                    let field = column.name();


                    row_data[field] = match column.column_type() {
                        ColumnType::BigVarChar | ColumnType::BigChar => {
                            match row.try_get::<&str, _>(field) {
                                Ok(e) => {
                                    match e {
                                        None => Null,
                                        Some(e) => e.into()
                                    }
                                }
                                Err(e) => {
                                    error!("BigVarChar: {e}");
                                    "".into()
                                }
                            }
                        }
                        ColumnType::Text | ColumnType::NText | ColumnType::NVarchar => {
                            match row.try_get::<&str, _>(field) {
                                Ok(e) => {
                                    match e {
                                        None => Null,
                                        Some(e) => e.into()
                                    }
                                }
                                Err(e) => {
                                    error!("Text: {e}");
                                    "".into()
                                }
                            }
                        }
                        ColumnType::Image => {
                            match row.try_get::<&[u8], _>(field) {
                                Ok(e) => {
                                    match e {
                                        None => Null,
                                        Some(e) => e.into()
                                    }
                                }
                                Err(e) => {
                                    error!("Image: {e}");
                                    "".into()
                                }
                            }
                        }
                        ColumnType::Datetimen | ColumnType::Datetime => {
                            match row.try_get::<PrimitiveDateTime, _>(field) {
                                Ok(e) => {
                                    match e {
                                        None => Null,
                                        Some(e) => e.to_string().into()
                                    }
                                }
                                Err(e) => {
                                    error!("Datetimen: {e}");
                                    "".into()
                                }
                            }
                        }
                        ColumnType::Intn | ColumnType::Int1 | ColumnType::Int2 | ColumnType::Int4 | ColumnType::Int8 => {
                            match row.try_get::<i32, _>(field) {
                                Ok(e) => {
                                    match e {
                                        None => Null,
                                        Some(e) => e.into()
                                    }
                                }
                                Err(_) => {
                                    match row.try_get::<i16, _>(field) {
                                        Ok(e) => {
                                            match e {
                                                None => 0.into(),
                                                Some(e) => e.into()
                                            }
                                        }
                                        Err(_) => {
                                            match row.try_get::<u8, _>(field) {
                                                Ok(e) => {
                                                    match e {
                                                        None => 0.into(),
                                                        Some(e) => e.into()
                                                    }
                                                }
                                                Err(_) => {
                                                    match row.try_get::<i64, _>(field) {
                                                        Ok(e) => {
                                                            match e {
                                                                None => 0.into(),
                                                                Some(e) => e.into()
                                                            }
                                                        }
                                                        Err(e) => {
                                                            error!("Intn: {e}");
                                                            0.into()
                                                        }
                                                    }
                                                }
                                            }
                                        }
                                    }
                                }
                            }
                        }
                        ColumnType::Numericn | ColumnType::Money => {
                            match row.try_get::<f64, _>(field) {
                                Ok(e) => {
                                    match e {
                                        None => Null,
                                        Some(e) => e.into()
                                    }
                                }
                                Err(_) => {
                                    match row.try_get::<Numeric, _>(field) {
                                        Ok(e) => {
                                            match e {
                                                None => Null,
                                                Some(e) => e.to_string().parse::<f64>().unwrap().into()
                                            }
                                        }
                                        Err(e) => {
                                            error!("Numericn1: {e}");
                                            0.0.into()
                                        }
                                    }
                                }
                            }
                        }
                        // ColumnType::Int8 => {
                        //     match row.try_get::<u64, _>(field) {
                        //         Ok(e) => {
                        //             if e == None {
                        //                 JsonValue::from(Null)
                        //             } else {
                        //                 e.unwrap().into()
                        //             }
                        //         }
                        //         Err(_) => {
                        //             match row.try_get::<Numeric, _>(field) {
                        //                 Ok(e) => {
                        //                     if e == None {
                        //                         JsonValue::from(Null)
                        //                     } else {
                        //                         e.unwrap().to_string().parse::<f64>().unwrap().into()
                        //                     }
                        //                 }
                        //                 Err(e) => {
                        //                     info!("Int8: {field} {}", e);
                        //                     0.0.into()
                        //                 }
                        //             }
                        //         }
                        //     }
                        // }
                        _ => {
                            error!("默认列类型: {:?}", column.column_type());
                            "".into()
                        }
                    }
                }
                list.push(row_data).unwrap();
            }
            (true, list)
        }
        rt.block_on(execute(self.connection.clone(), sql))
    }
}

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 {
        todo!()
    }
}

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 {
        todo!()
    }
    fn table_is_exist(&mut self, name: &str) -> bool {
        let sql = format!("SELECT count(*) as count FROM sqlite_master WHERE type='table' AND name='{name}'");
        let (state, data) = self.query(sql);
        if state {
            if data[0]["count"].as_i64().unwrap() > 0 {
                return true;
            }
            false
        } else {
            false
        }
    }
    fn table(&mut self, name: &str) -> &mut Self {
        self.params = Params::default(self.connection.mode.str().as_str());
        self.params.table = format!("{}{}", self.connection.prefix, name);
        self.params.join_table = self.params.table.clone();
        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 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() {
                list.push(item[field].clone()).unwrap()
            }
            list
        } else {
            array![]
        }
    }

    fn where_and(&mut self, field: &str, compare: &str, value: JsonValue) -> &mut Self {
        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 {
        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_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 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, _data: JsonValue) -> JsonValue {
        todo!()
        // let mut fields = String::new();
        // let mut values = String::new();
        // if !self.params.autoinc && data["id"].is_empty() {
        //     data["id"] = JsonValue::from(Local::now().timestamp_nanos())
        // }
        // for (field, value) in data.entries() {
        //     fields = format!("{},{}", fields, field);
        //     values = format!("{},\"{}\"", values, value);
        // }
        // fields = fields.trim_start_matches(",").parse().unwrap();
        // values = values.trim_start_matches(",").parse().unwrap();
        //
        // let sql = format!("INSERT INTO {} ({}) VALUES ({});", self.params.table, fields, values);
        // let (state, ids) = self.execute(sql);
        // if state {
        //     return data["id"].clone();
        // } else {
        //     if self.connection.debug {
        //         info!("{:?}", ids);
        //     }
        //     return JsonValue::from("");
        // }
    }
    fn insert_all(&mut self, _data: JsonValue) -> 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 update(&mut self, _data: JsonValue) -> JsonValue {
        todo!()
    }

    fn update_all(&mut self, _data: JsonValue) -> JsonValue {
        todo!()
    }

    fn delete(&mut self) -> JsonValue {
        todo!()
    }

    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 hidden(&mut self, name: &str) -> &mut Self {
        let hidden: Vec<&str> = name.split(",").collect();
        let sql = format!("PRAGMA table_info({})", self.params.table);
        let (_, data) = self.query(sql);
        for item in data.members() {
            let name = item["name"].as_str().unwrap();
            if !hidden.contains(&name) {
                self.params.fields[name] = name.into();
            }
        }
        self
    }

    fn transaction(&mut self) -> bool {
        todo!()
    }


    fn commit(&mut self) -> bool {
        todo!()
    }

    fn rollback(&mut self) -> bool {
        todo!()
    }

    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> {
        todo!()
    }

    fn inc(&mut self, _field: &str, _num: f64) -> &mut Self {
        todo!()
    }

    fn dec(&mut self, _field: &str, _num: f64) -> &mut Self {
        todo!()
    }

    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 location(&mut self, _field: &str) -> &mut Self {
        todo!()
    }

    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
    }
}