/// sqlserver mod
/// ```ignore
/// fn main() {
/// }
/// ```
///
#[allow(warnings)]
#[cfg(feature = "sqlserver")]
pub mod sqlserver {
    use anyhow::{Context, Result};
    use deadpool_tiberius::tiberius::time::DateTime;
    use deadpool_tiberius::tiberius::time::SmallDateTime;
    use std::net::{IpAddr, SocketAddr, TcpListener as StdTcpListener};
    // Use chrono crate for time operations
    use chrono::Timelike;

    // Define a trait for converting datetime to timestamp and formatting datetime strings
    pub trait ToTimeStamp {
        // Convert datetime to custom timestamp
        fn to_time_stamp(&self) -> u64;
        // Format datetime string
        fn format_date_time(&self) -> String;
    }

    // Calculate days between 1753 and 1900, accounting for leap years
    fn days_between_1753_and_1900() -> i32 {
        let mut days = 0;
        for year in 1753..1900 {
            // 366 days for leap years, 365 for normal years
            if year % 4 == 0 && (year % 100 != 0 || year % 400 == 0) {
                days += 366;
            } else {
                days += 365;
            }
        }
        days
    }
    // now_with_fmt("%Y-%m-%d %H:%M:%S%.3f")
    pub fn now_with_fmt(fmt: &str) -> String {
        use chrono::{DateTime, Local};
        let now: DateTime<Local> = Local::now();
        now.format(fmt).to_string()
    }
    // tiberius::tds::time::time::Time;
    // Implement ToTimeStamp trait for SmallDateTime type
    impl ToTimeStamp for SmallDateTime {
        // Convert DateTime to custom timestamp
        fn to_time_stamp(&self) -> u64 {
            (self.days() as u64 * 86400) + (self.seconds_fragments() / 300) as u64
        }
        // Format DateTime as string
        fn format_date_time(&self) -> String {
            let time_stamp = self.to_time_stamp();
            // Use chrono crate's date operations
            use chrono::Datelike;
            use chrono::{DateTime, Utc};
            // Create DateTime object from timestamp
            let date_time: DateTime<Utc> = DateTime::from_utc(
                chrono::NaiveDateTime::from_timestamp(time_stamp as i64, 0),
                Utc,
            );
            // Format datetime string
            let time = format!(
                "{}-{}-{} {}:{}:{}",
                date_time.year() - 70,
                date_time.month(),
                date_time.day(),
                date_time.hour(),
                date_time.minute(),
                date_time.second()
            );
            time
        }
    }
    // Implement ToTimeStamp trait for DateTime type
    impl ToTimeStamp for DateTime {
        // Convert DateTime to custom timestamp
        fn to_time_stamp(&self) -> u64 {
            (self.days() as u64 * 86400) + (self.seconds_fragments() / 300) as u64
        }
        // Format DateTime as string
        fn format_date_time(&self) -> String {
            let time_stamp = self.to_time_stamp();
            // Use chrono crate's date operations
            use chrono::Datelike;
            use chrono::{DateTime, Utc};
            // Create DateTime object from timestamp
            let date_time: DateTime<Utc> = DateTime::from_utc(
                chrono::NaiveDateTime::from_timestamp(time_stamp as i64, 0),
                Utc,
            );
            // Format datetime string
            let time = format!(
                "{}-{}-{} {}:{}:{}",
                date_time.year() - 70,
                date_time.month(),
                date_time.day(),
                date_time.hour(),
                date_time.minute(),
                date_time.second()
            );
            time
        }
    }

    use deadpool_tiberius::tiberius;
        /// Executes a SQL query and returns the result as a string.
    ///
    /// # Arguments
    ///
    /// * `pool` - A connection pool to the database.
    /// * `sql` - The SQL query to execute.
    /// * `params` - A vector of parameters to bind to the query.
    ///
    /// # Returns
    ///
    /// Returns `Ok(String)` with the query result if successful.
    /// Returns `Err(String)` if the query execution fails.
    ///
    /// # Examples
    ///
    /// ```
    /// use doe::sqlserver;
    /// use deadpool_tiberius::Pool;
    ///
    /// async fn example(pool: Pool) {
    ///     let result = sqlserver::execute_sql(pool, "SELECT 1", vec![]).await.unwrap();
    ///     println!("Query result: {}", result);
    /// }
    /// ```
    pub async fn execute_sql(
        pool: Pool,
        sql: impl ToString,
        params: Vec<String>,
    ) -> Result<String, String> {
        let mut client = pool
            .get()
            .await
            .map_err(|e| format!("Failed to get database client: {}", e))?;
        let mut select: tiberius::Query<'_> =
            tiberius::Query::new(sql.to_string().trim().to_string());
        for p in params {
            select.bind(p);
        }
        let stream = select
            .execute(&mut client)
            .await
            .map_err(|e| e.to_string())
            .map(|s| format!("{:?}", s.rows_affected()));
        stream
    }

    /// Execute SQL statement and return data in CSV format
    /// type_vec parameter types => String, i64, i32, f64, date, datetime, bool
        /// Executes a SQL query and returns the result as a CSV-formatted vector of vectors.
    ///
    /// # Arguments
    ///
    /// * `pool` - A connection pool to the database.
    /// * `sql` - The SQL query to execute.
    /// * `params` - A vector of parameters to bind to the query.
    /// * `type_vec` - A vector of types for the parameters (e.g., `["String", "i64", "f64"]`).
    ///
    /// # Returns
    ///
    /// Returns `Ok(Vec<Vec<String>>)` with the query result in CSV format if successful.
    /// Returns `Err(String)` if the query execution fails.
    ///
    /// # Examples
    ///
    /// ```
    /// use doe::sqlserver;
    /// use deadpool_tiberius::Pool;
    ///
    /// async fn example(pool: Pool) {
    ///     let result = sqlserver::run_sql_get_csv_with_type(
    ///         pool,
    ///         "SELECT * FROM users WHERE id = ?",
    ///         vec!["1".to_string()],
    ///         vec!["i64".to_string()],
    ///     ).await.unwrap();
    ///     println!("Query result: {:?}", result);
    /// }
    /// ```
    pub async fn run_sql_get_csv_with_type(
        pool: Pool,
        sql: impl ToString,
        params: Vec<String>,
        type_vec: Vec<impl ToString>,
    ) -> Result<Vec<Vec<String>>, String> {
        // Get a database client from the pool
        let mut client = pool
            .get()
            .await
            .map_err(|e| format!("Failed to get database client: {}", e))?;
        // Prepare the query with parameters
        let mut select: tiberius::Query<'_> =
            tiberius::Query::new(sql.to_string().trim().to_string());
        for (p, ty) in params.iter().zip(type_vec.iter()) {
            let ty = ty.to_string();
            if ty.eq("String") {
                select.bind(p);
            } else if ty.eq("i64") {
                let val = p
                    .parse::<i64>()
                    .map_err(|_| "Failed to parse i64".to_string())?;
                select.bind(val);
            } else if ty.eq("i32") {
                let val = p
                    .parse::<i32>()
                    .map_err(|_| "Failed to parse i32".to_string())?;
                select.bind(val);
            } else if ty.eq("f64") {
                let val = p
                    .parse::<f64>()
                    .map_err(|_| "Failed to parse f64".to_string())?;
                select.bind(val);
            } else if ty.eq("bool") {
                let val = p
                    .parse::<bool>()
                    .map_err(|_| "Failed to parse bool".to_string())?;
                select.bind(val);
            }
        }
        // Execute the query and get the stream of results
        let stream = select.query(&mut client).await.map_err(|e| e.to_string())?;
        let results = stream.into_results().await.map_err(|e| e.to_string())?;
        // Process the results
        let mut output: Vec<Vec<String>> = vec![];

        // Extract column names from the first row
        if let Some(first_result) = results.first() {
            if let Some(first_row) = first_result.first() {
                let col_names: Vec<String> = first_row
                    .columns()
                    .iter()
                    .map(|c| c.name().to_string())
                    .collect();
                output.push(col_names);
            }
        }

        // Process rows in parallel using rayon (optional, if you want to parallelize)
        for result in results {
            for row in result {
                let values: Vec<String> = row
                    .into_iter()
                    .map(|col| match col {
                        tiberius::ColumnData::U8(Some(val)) => val.to_string(),
                        tiberius::ColumnData::I16(Some(val)) => val.to_string(),
                        tiberius::ColumnData::I32(Some(val)) => val.to_string(),
                        tiberius::ColumnData::I64(Some(val)) => val.to_string(),
                        tiberius::ColumnData::F32(Some(val)) => val.to_string(),
                        tiberius::ColumnData::F64(Some(val)) => val.to_string(),
                        tiberius::ColumnData::Bit(Some(val)) => val.to_string(),
                        tiberius::ColumnData::String(Some(val)) => val.to_string(),
                        tiberius::ColumnData::Numeric(Some(val)) => val.to_string(),
                        tiberius::ColumnData::Guid(Some(val)) => val.to_string(),
                        tiberius::ColumnData::Xml(Some(val)) => val.to_string(),
                        tiberius::ColumnData::DateTime(Some(val)) => val.format_date_time(),
                        tiberius::ColumnData::SmallDateTime(Some(val)) => val.format_date_time(),
                        _ => "".to_string(),
                    })
                    .collect();

                output.push(values);
            }
        }

        // Clear output if no results were found
        if output.len() == 1 && output[0].is_empty() {
            output.clear();
        }

        Ok(output)
    }
    /// Executes a SQL query and returns the result as a CSV-formatted vector of vectors.
    ///
    /// # Arguments
    ///
    /// * `pool` - A connection pool to the database.
    /// * `sql` - The SQL query to execute.
    /// * `params` - A vector of parameters to bind to the query.
    ///
    /// # Returns
    ///
    /// Returns `Ok(Vec<Vec<String>>)` with the query result in CSV format if successful.
    /// Returns `Err(String)` if the query execution fails.
    ///
    /// # Examples
    ///
    /// ```
    /// use doe::sqlserver;
    /// use deadpool_tiberius::Pool;
    ///
    /// async fn example(pool: Pool) {
    ///     let result = sqlserver::run_sql_get_csv(
    ///         pool,
    ///         "SELECT * FROM users WHERE id = ?",
    ///         vec!["1".to_string()],
    ///     ).await.unwrap();
    ///     println!("Query result: {:?}", result);
    /// }
    /// ```
    pub async fn run_sql_get_csv(
        pool: Pool,
        sql: impl ToString,
        params: Vec<String>,
    ) -> Result<Vec<Vec<String>>, String> {
        // Get a database client from the pool
        let mut client = pool
            .get()
            .await
            .map_err(|e| format!("Failed to get database client: {}", e))?;

        // Create a query with the provided SQL and bind parameters
        let sql = sql.to_string().trim().to_string();
        let mut query = tiberius::Query::new(sql);
        for param in params {
            query.bind(param);
        }

        // Execute the query and get the stream of results
        let stream = query.query(&mut client).await.map_err(|e| e.to_string())?;
        let results = stream.into_results().await.map_err(|e| e.to_string())?;

        // Process the results
        let mut output: Vec<Vec<String>> = vec![];

        // Extract column names from the first row
        if let Some(first_result) = results.first() {
            if let Some(first_row) = first_result.first() {
                let col_names: Vec<String> = first_row
                    .columns()
                    .iter()
                    .map(|c| c.name().to_string())
                    .collect();
                output.push(col_names);
            }
        }

        // Process rows in parallel using rayon (optional, if you want to parallelize)
        for result in results {
            for row in result {
                let values: Vec<String> = row
                    .into_iter()
                    .map(|col| match col {
                        tiberius::ColumnData::U8(Some(val)) => val.to_string(),
                        tiberius::ColumnData::I16(Some(val)) => val.to_string(),
                        tiberius::ColumnData::I32(Some(val)) => val.to_string(),
                        tiberius::ColumnData::I64(Some(val)) => val.to_string(),
                        tiberius::ColumnData::F32(Some(val)) => val.to_string(),
                        tiberius::ColumnData::F64(Some(val)) => val.to_string(),
                        tiberius::ColumnData::Bit(Some(val)) => val.to_string(),
                        tiberius::ColumnData::String(Some(val)) => val.to_string(),
                        tiberius::ColumnData::Numeric(Some(val)) => val.to_string(),
                        tiberius::ColumnData::Guid(Some(val)) => val.to_string(),
                        tiberius::ColumnData::Xml(Some(val)) => val.to_string(),
                        tiberius::ColumnData::DateTime(Some(val)) => val.format_date_time(),
                        tiberius::ColumnData::SmallDateTime(Some(val)) => val.format_date_time(),
                        _ => "".to_string(),
                    })
                    .collect();

                output.push(values);
            }
        }

        // Clear output if no results were found
        if output.len() == 1 && output[0].is_empty() {
            output.clear();
        }

        Ok(output)
    }
    /// Executes a SQL query and returns the first value of the first row as a string.
    ///
    /// # Arguments
    ///
    /// * `pool` - A connection pool to the database.
    /// * `sql` - The SQL query to execute.
    /// * `params` - A vector of parameters to bind to the query.
    ///
    /// # Returns
    ///
    /// Returns `Ok(String)` with the first value of the first row if successful.
    /// Returns `Err(String)` if the query execution fails or no results are found.
    ///
    /// # Examples
    ///
    /// ```
    /// use doe::sqlserver;
    /// use deadpool_tiberius::Pool;
    ///
    /// async fn example(pool: Pool) {
    ///     let result = sqlserver::run_sql_get_string(
    ///         pool,
    ///         "SELECT name FROM users WHERE id = ?",
    ///         vec!["1".to_string()],
    ///     ).await.unwrap();
    ///     println!("Query result: {}", result);
    /// }
    /// ```
    pub async fn run_sql_get_string(
        pool: Pool,
        sql: impl ToString,
        params: Vec<String>,
    ) -> Result<String, String> {
        // Get a database client from the pool
        let mut client = pool
            .get()
            .await
            .map_err(|e| format!("Failed to get database client: {}", e))?;

        // Create a query with the provided SQL and bind parameters
        let sql = sql.to_string().trim().to_string();
        let mut query = tiberius::Query::new(sql);
        for param in params {
            query.bind(param);
        }

        // Execute the query and get the stream of results
        let stream = query.query(&mut client).await.map_err(|e| e.to_string())?;
        let results = stream.into_results().await.map_err(|e| e.to_string())?;

        // Process the results
        for result in results {
            for row in result {
                let col_names: Vec<String> =
                    row.columns().iter().map(|c| c.name().to_string()).collect();
                let values: Vec<String> = row
                    .into_iter()
                    .map(|col| match col {
                        tiberius::ColumnData::U8(Some(val)) => val.to_string(),
                        tiberius::ColumnData::I16(Some(val)) => val.to_string(),
                        tiberius::ColumnData::I32(Some(val)) => val.to_string(),
                        tiberius::ColumnData::I64(Some(val)) => val.to_string(),
                        tiberius::ColumnData::F32(Some(val)) => val.to_string(),
                        tiberius::ColumnData::F64(Some(val)) => val.to_string(),
                        tiberius::ColumnData::Bit(Some(val)) => val.to_string(),
                        tiberius::ColumnData::String(Some(val)) => val.to_string(),
                        tiberius::ColumnData::Numeric(Some(val)) => val.to_string(),
                        tiberius::ColumnData::Guid(Some(val)) => val.to_string(),
                        tiberius::ColumnData::Xml(Some(val)) => val.to_string(),
                        tiberius::ColumnData::DateTime(Some(val)) => val.format_date_time(),
                        tiberius::ColumnData::SmallDateTime(Some(val)) => val.format_date_time(),
                        _ => "".to_string(),
                    })
                    .collect();

                // Return the first value of the second row if it exists
                if values.len() > 0 {
                    return Ok(values[0].clone());
                }
            }
        }

        Err("sql get string Error".into())
    }

    use deadpool_tiberius::deadpool::managed::Metrics;
    use deadpool_tiberius::{Client, Manager, Pool};
    pub use deadpool_tiberius;
    use std::env;
    pub fn db_pool_from_env() -> Result<Pool> {
        // Get database host from environment variables, ensure DB_HOST is set
        let db_host = env::var("DB_HOST").context("DB_HOST must be set");
        // Get database user from environment variables, ensure DB_USER is set
        let db_user = env::var("DB_USER").context("DB_USER must be set");
        // Get database password from environment variables, ensure DB_PASSWORD is set
        let db_password = env::var("DB_PASSWORD").context("DB_PASSWORD must be set");
        // Get database name from environment variables, ensure DB_NAME is set
        let db_name = env::var("DB_NAME").context("DB_NAME must be set");
        // Get database port from environment variables, ensure DB_PORT is set and is a number
        let db_port = env::var("DB_PORT")
            .context("DB_PORT must be set")?
            .parse()
            .context("DB_PORT must be a number");

        // Configure and create database connection pool
        let pool = Manager::new()
            // Set database host
            .host(db_host?)
            // Set database port
            .port(db_port?)
            // Set database username and password
            .basic_authentication(db_user?, db_password?)
            // Set database name
            .database(db_name?)
            // Set maximum pool size
            .max_size(1000)
            // Trust all certificates
            .trust_cert()
            // Set connection wait timeout
            .wait_timeout(std::time::Duration::from_secs(50))
            // Operation to perform before recycling connection (none in this case)
            .pre_recycle_sync(
                |_client: &mut Client, _metrics: &Metrics| std::result::Result::Ok(()),
            )
            // Create database connection pool
            .create_pool()?;
        // Return result, if any errors occurred in previous operations return error
        Ok(pool)
    }

    pub fn db_pool(
        db_host: impl ToString,
        db_port: u16,
        db_user: impl ToString,
        db_password: impl ToString,
        db_name: impl ToString,
    ) -> Result<Pool> {
        // Get database host from environment variables, ensure DB_HOST is set
        let db_host = db_host.to_string();
        // Get database user from environment variables, ensure DB_USER is set
        let db_user = db_user.to_string();
        // Get database password from environment variables, ensure DB_PASSWORD is set
        let db_password = db_password.to_string();
        // Get database name from environment variables, ensure DB_NAME is set
        let db_name = db_name.to_string();

        // Configure and create database connection pool
        let pool = Manager::new()
            // Set database host
            .host(db_host)
            // Set database port
            .port(db_port)
            // Set database username and password
            .basic_authentication(db_user, db_password)
            // Set database name
            .database(db_name)
            // Set maximum pool size
            .max_size(1000)
            // Trust all certificates
            .trust_cert()
            // Set connection wait timeout
            .wait_timeout(std::time::Duration::from_secs(50))
            // Operation to perform before recycling connection (none in this case)
            .pre_recycle_sync(
                |_client: &mut Client, _metrics: &Metrics| std::result::Result::Ok(()),
            )
            // Create database connection pool
            .create_pool()?;
        // Return result, if any errors occurred in previous operations return error
        Ok(pool)
    }
}
#[cfg(feature = "sqlserver")]
pub use sqlserver::*;
