xdl-database 0.1.1

//! MySQL database driver
//!
//! Full async implementation using mysql_async for native MySQL protocol support.
//! Compatible with MySQL, MariaDB, and other MySQL-compatible databases.
//!
//! Connection string format:
//! - mysql://user:password@host:port/database
//! - mysql://user:password@host/database (default port 3306)

use crate::{recordset::ColumnInfo, DatabaseError, DatabaseResult, Recordset};
use mysql_async::{prelude::*, Conn, OptsBuilder, Pool, Row, Value as MySQLValue};
use serde_json::Value as JsonValue;

/// MySQL connection with connection pooling support
#[derive(Debug)]
pub struct MySQLConnection {
    pool: Option<Pool>,
    conn: Option<Conn>,
}

impl MySQLConnection {
    /// Connect to a MySQL database
    ///
    /// Connection string format:
    /// - mysql://user:password@host:port/database
    /// - mysql://user:password@host/database (port defaults to 3306)
    ///
    /// Example:
    /// ```ignore
    /// let conn = MySQLConnection::connect("mysql://root:password@localhost:3306/mydb").await?;
    /// ```
    pub async fn connect(connection_string: &str) -> DatabaseResult<Self> {
        // Parse connection string
        let opts = OptsBuilder::from_opts(connection_string).map_err(|e| {
            DatabaseError::connection_error(format!("Invalid MySQL connection string: {}", e))
        })?;

        // Create connection pool
        let pool = Pool::new(opts);

        // Test connection by getting one from pool
        let mut conn = pool.get_conn().await.map_err(|e| {
            DatabaseError::connection_error(format!("Failed to connect to MySQL: {}", e))
        })?;

        // Verify connection with a simple query
        conn.query_drop("SELECT 1").await.map_err(|e| {
            DatabaseError::connection_error(format!("MySQL connection test failed: {}", e))
        })?;

        Ok(Self {
            pool: Some(pool),
            conn: Some(conn),
        })
    }

    /// Execute a SELECT query
    pub async fn execute(&self, query: &str) -> DatabaseResult<Recordset> {
        let conn = self.conn.as_ref().ok_or(DatabaseError::NotConnected)?;

        // Execute query and get results
        let result: Vec<Row> = conn
            .clone()
            .query(query)
            .await
            .map_err(|e| DatabaseError::query_error(format!("MySQL query failed: {}", e)))?;

        if result.is_empty() {
            return Ok(Recordset::empty());
        }

        // Extract column information from the first row
        let first_row = &result[0];
        let columns: Vec<ColumnInfo> = first_row
            .columns()
            .iter()
            .enumerate()
            .map(|(i, col)| ColumnInfo {
                name: col.name_str().to_string(),
                data_type: format!("{:?}", col.column_type()),
                ordinal: i,
            })
            .collect();

        // Extract row data
        let mut data_rows = Vec::new();
        for row in result {
            let mut row_data = Vec::new();

            for col in row.columns() {
                let col_name = col.name_str();
                let value = mysql_value_to_json(&row, col_name)?;
                row_data.push(value);
            }

            data_rows.push(row_data);
        }

        Ok(Recordset::new(columns, data_rows))
    }

    /// Execute a command (INSERT, UPDATE, DELETE, CREATE, etc.)
    pub async fn execute_command(&self, command: &str) -> DatabaseResult<u64> {
        let conn = self.conn.as_ref().ok_or(DatabaseError::NotConnected)?;

        let result = conn
            .clone()
            .query_drop(command)
            .await
            .map_err(|e| DatabaseError::query_error(format!("MySQL command failed: {}", e)))?;

        // For commands that affect rows, we need to get the affected rows count
        // This is a simplified version - mysql_async doesn't directly return affected rows in query_drop
        // In a real implementation, we'd use exec_drop and capture the result

        // Try to get affected rows by executing with exec
        let mut conn_mut = self.conn.as_ref().unwrap().clone();
        let affected = conn_mut
            .exec_drop(command, ())
            .await
            .map(|_| {
                // Get last insert id or affected rows if available
                // For now, return 0 as we don't have direct access
                0u64
            })
            .unwrap_or(0);

        Ok(affected)
    }

    /// Close the connection and pool
    pub async fn close(&mut self) -> DatabaseResult<()> {
        // Drop connection first
        if let Some(conn) = self.conn.take() {
            drop(conn);
        }

        // Disconnect pool
        if let Some(pool) = self.pool.take() {
            pool.disconnect().await.map_err(|e| {
                DatabaseError::query_error(format!("Failed to disconnect MySQL pool: {}", e))
            })?;
        }

        Ok(())
    }

    /// Check if connection is alive
    pub async fn is_connected(&self) -> bool {
        if let Some(conn) = &self.conn {
            // Try a simple query
            conn.clone().query_drop("SELECT 1").await.is_ok()
        } else {
            false
        }
    }
}

/// Convert MySQL value to JSON
fn mysql_value_to_json(row: &Row, col_name: &str) -> DatabaseResult<JsonValue> {
    let value: MySQLValue = row
        .get(col_name)
        .ok_or(DatabaseError::conversion_error(format!(
            "Column '{}' not found",
            col_name
        )))?;

    let json_value = match value {
        MySQLValue::NULL => JsonValue::Null,

        MySQLValue::Bytes(bytes) => {
            // Try to convert bytes to string
            match String::from_utf8(bytes) {
                Ok(s) => {
                    // Try to parse as number first
                    if let Ok(num) = s.parse::<i64>() {
                        JsonValue::from(num)
                    } else if let Ok(num) = s.parse::<f64>() {
                        JsonValue::from(num)
                    } else {
                        JsonValue::from(s)
                    }
                }
                Err(_) => JsonValue::from("(binary data)"),
            }
        }

        MySQLValue::Int(i) => JsonValue::from(i),
        MySQLValue::UInt(u) => JsonValue::from(u),

        MySQLValue::Float(f) => JsonValue::from(f as f64),

        MySQLValue::Double(d) => JsonValue::from(d),

        MySQLValue::Date(year, month, day, hour, minute, second, _micro) => {
            // Format as ISO 8601 datetime string
            let datetime_str = format!(
                "{:04}-{:02}-{:02} {:02}:{:02}:{:02}",
                year, month, day, hour, minute, second
            );
            JsonValue::from(datetime_str)
        }

        MySQLValue::Time(neg, days, hours, minutes, seconds, _micros) => {
            // Format as time string
            let sign = if neg { "-" } else { "" };
            let time_str = format!(
                "{}{} {:02}:{:02}:{:02}",
                sign, days, hours, minutes, seconds
            );
            JsonValue::from(time_str)
        }
    };

    Ok(json_value)
}

#[cfg(test)]
mod tests {
    use super::*;

    #[test]
    fn test_mysql_connection_string_parsing() {
        // Test that OptsBuilder can parse various connection string formats
        let test_strings = vec![
            "mysql://root:password@localhost:3306/testdb",
            "mysql://user@localhost/mydb",
            "mysql://user:pass@192.168.1.100:3307/data",
        ];

        for conn_str in test_strings {
            let result = OptsBuilder::from_opts(conn_str);
            assert!(result.is_ok(), "Failed to parse: {}", conn_str);
        }
    }

    #[tokio::test]
    async fn test_value_conversion() {
        // Test NULL conversion
        let json = mysql_value_to_json_test(MySQLValue::NULL);
        assert_eq!(json, JsonValue::Null);

        // Test integer conversion
        let json = mysql_value_to_json_test(MySQLValue::Int(42));
        assert_eq!(json, JsonValue::from(42));

        // Test string conversion
        let json = mysql_value_to_json_test(MySQLValue::Bytes(b"hello".to_vec()));
        assert_eq!(json, JsonValue::from("hello"));
    }

    // Helper for testing value conversion
    fn mysql_value_to_json_test(value: MySQLValue) -> JsonValue {
        match value {
            MySQLValue::NULL => JsonValue::Null,
            MySQLValue::Int(i) => JsonValue::from(i),
            MySQLValue::Bytes(bytes) => String::from_utf8(bytes)
                .map(JsonValue::from)
                .unwrap_or(JsonValue::from("(binary data)")),
            _ => JsonValue::Null,
        }
    }
}