dbcli 0.1.0

//! # MySQL → JSON Conversion Module
//!
//! Provides functionality to convert a list of [`sqlx::mysql::MySqlRow`] into
//! [`serde_json::Value`]. The [`to_json`] function processes an entire query result
//! set at once and returns both a JSON array and column metadata.
//!
//! Type mappings follow MySQL official type names (uppercase);
//! see [`crate::to_json::ToJsonCustomizer`] for the full reference table.

use crate::column_info::ColumnBaseInfo;
use crate::to_json::MySqlRowParse;
use base64::{engine::general_purpose, Engine};
use chrono::{DateTime, Local, NaiveDate, NaiveDateTime, NaiveTime, Utc};
use rust_decimal::Decimal;
use serde_json::Value as JsonValue;
use sqlx::mysql::MySqlRow;
use sqlx::{Column, Row, TypeInfo};
use crate::decode::decode_auto;
use super::f64_to_json_safe;

// Converts a MySQL query result set into a JSON array.
//
// Input: `Vec<MySqlRow>`, output:
// - `Vec<serde_json::Value>`: each element is a JSON object for one row, keyed by column name
// - `Vec<ColumnBaseInfo>`: column metadata (name, type, index)
//
// Returns `(vec![], vec![])` immediately for empty result sets.
//
// # Type Support
//
// | MySQL Type | JSON output |
// |-----------|-------------|
// | VARCHAR / CHAR / TEXT series | JSON String |
// | INTEGER / INT / BIGINT etc. | JSON Number (i64) |
// | DOUBLE / FLOAT / REAL | JSON Number (f64, NaN/Inf → String) |
// | DECIMAL / NUMERIC | JSON String (precision preserved) |
// | DATETIME | JSON String `"%Y-%m-%d %H:%M:%S"` |
// | DATE | JSON String `"%Y-%m-%d"` |
// | TIME | JSON String `"%H:%M:%S"` |
// | TIMESTAMP | JSON String "%Y-%m-%d %H:%M:%S %Z" (local timezone, via DateTime<Utc>) |
// | BOOLEAN / BOOL | JSON Bool |
// | JSON | JSON Value (pass-through) |
// | BLOB / TINYBLOB | Auto-detect: text → String, binary → Base64 String |
// | MEDIUMBLOB / LONGBLOB | Base64 String |
// | BINARY | UUID String (16-byte) or Base64 String |
// | VARBINARY | Auto encoding detection (UTF-8/GBK/BIG5) → String |
// | ENUM | JSON String (single value) |
// | SET | JSON Array (comma-separated values) |
// | GEOMETRY series | `"Does not support parsing"` |
// | VECTOR (MySQL HeatWave / MySQL 9.0+) | JSON Array (f64 values) |
//
// # Example
//
// ```rust,no_run
// use sqlx::MySqlPool;
// use dbcli::to_json::mysql::to_json;
//
// async fn example(pool: &MySqlPool) -> anyhow::Result<()> {
//     let rows = sqlx::query("SELECT id, name, created_at FROM users")
//         .fetch_all(pool)
//         .await?;
//     let (data, columns) = to_json(rows)?;
//     println!("{}", serde_json::to_string_pretty(&data)?);
//     Ok(())
// }
// ```
crate::impl_to_json!(MySqlRow, MySqlRowParse);

fn determine_parsing_methods(row: &MySqlRow) -> anyhow::Result<MySqlRowParse> {
    let customizer = super::get_customizer();
    let columns = row.columns();
    let mut methods: Vec<fn(&MySqlRow, usize) -> JsonValue> = Vec::with_capacity(columns.len());
    let mut new_columns = Vec::with_capacity(columns.len());

    for col in columns {
        let col_index: usize = col.ordinal();
        let colum_name = col.name();
        let field_type = col.type_info().name().to_uppercase();

        let method: fn(&MySqlRow, usize) -> JsonValue = customizer
            .customize_mysql(&field_type)
            .map(Ok)
            .unwrap_or_else(|| default_mysql_method(&field_type, col))?;

        new_columns.push(ColumnBaseInfo {
            name: colum_name.to_string(),
            r#type: field_type,
            index: col_index as u64,
        });
        methods.push(method);
    }

    Ok(MySqlRowParse {
        methods,
        columns: new_columns,
    })
}

#[inline]
fn default_mysql_method(
    field_type: &str,
    col: &sqlx::mysql::MySqlColumn,
) -> anyhow::Result<fn(&MySqlRow, usize) -> JsonValue> {
    let method = match field_type {
        // Text types
        "VARCHAR" | "CHAR" | "TEXT" | "TINYTEXT" | "MEDIUMTEXT" | "LONGTEXT" => parse_text_value,

        // Integer types (including unsigned variants)
        "INTEGER" | "INT" | "SMALLINT" | "BIGINT" | "TINYINT" | "MEDIUMINT" | "SERIAL"
        | "INT UNSIGNED" | "BIGINT UNSIGNED" | "SMALLINT UNSIGNED" | "TINYINT UNSIGNED"
        | "MEDIUMINT UNSIGNED" | "UNSIGNED BIG INT" | "BIT" => parse_integer_value,

        // Floating-point types
        "DOUBLE" | "FLOAT" | "REAL" | "DOUBLE UNSIGNED" | "FLOAT UNSIGNED" => parse_real_value,

        // Decimal types
        "DECIMAL" | "NUMERIC" | "DECIMAL UNSIGNED" | "NUMERIC UNSIGNED" => parse_decimal,

        // Boolean types
        "BOOLEAN" | "BOOL" => parse_bool_value,

        // Date and time types
        "DATETIME" => parse_datetime_value,
        "DATE" => parse_date_value,
        "TIME" => parse_time_value,
        "TIMESTAMP" => parse_timestamp_with_timezone,
        "YEAR" => parse_year_value,

        // JSON type
        "JSON" => parse_json_value,

        // Binary types
        "BLOB" | "TINYBLOB" => parse_blob_or_text,
        "MEDIUMBLOB" | "LONGBLOB" => parse_blob_value,
        "BINARY" => parse_binary_value,
        "VARBINARY" => parse_varbinary_value,

        // Enum and Set
        "ENUM" => parse_text_value,
        "SET" => parse_set_value,

        // National character set types
        "NCHAR" | "NVARCHAR" | "NATIONAL CHAR" | "NATIONAL VARCHAR" => parse_text_value,

        // Spatial data types
        "GEOMETRY" | "POINT" | "LINESTRING" | "POLYGON" | "MULTIPOINT" | "MULTILINESTRING"
        | "MULTIPOLYGON" | "GEOMETRYCOLLECTION" => parse_geometry_value,

        // Vector type (MySQL HeatWave / MySQL 9.0+)
        "VECTOR" => parse_vector_value,

        _ => {
            return Err(anyhow::anyhow!("Unknown field type: {}", col.type_info().name()));
        }
    };
    Ok(method)
}

fn parse_text_value(row: &MySqlRow, col_index: usize) -> JsonValue {
    if let Ok(Some(s)) = row.try_get::<Option<String>, _>(col_index) {
        JsonValue::String(s)
    } else if let Ok(Some(b)) = row.try_get::<Option<Vec<u8>>, _>(col_index) {
        JsonValue::String(decode_auto(&b))
    } else {
        JsonValue::Null
    }
}

fn parse_date_value(row: &MySqlRow, col_index: usize) -> JsonValue {
    if let Ok(Some(date)) = row.try_get::<Option<NaiveDate>, _>(col_index) {
        JsonValue::String(date.format("%Y-%m-%d").to_string())
    } else {
        JsonValue::Null
    }
}

fn parse_datetime_value(row: &MySqlRow, col_index: usize) -> JsonValue {
    if let Ok(Some(dt)) = row.try_get::<Option<NaiveDateTime>, _>(col_index) {
        JsonValue::String(dt.format("%Y-%m-%d %H:%M:%S").to_string())
    } else {
        JsonValue::Null
    }
}

fn parse_timestamp_with_timezone(row: &MySqlRow, col_index: usize) -> JsonValue {
    // Try DateTime<Utc> first (native TIMESTAMP mapping)
    if let Ok(Some(dt)) = row.try_get::<Option<DateTime<Utc>>, _>(col_index) {
        return JsonValue::String(dt.with_timezone(&Local).format("%Y-%m-%d %H:%M:%S %Z").to_string());
    }
    // Fallback to DateTime<Local>
    if let Ok(Some(dt)) = row.try_get::<Option<DateTime<Local>>, _>(col_index) {
        return JsonValue::String(dt.format("%Y-%m-%d %H:%M:%S %Z").to_string());
    }
    // Last resort: NaiveDateTime
    if let Ok(Some(dt)) = row.try_get::<Option<NaiveDateTime>, _>(col_index) {
        return JsonValue::String(dt.format("%Y-%m-%d %H:%M:%S").to_string());
    }
    JsonValue::Null
}

fn parse_year_value(row: &MySqlRow, col_index: usize) -> JsonValue {
    if let Ok(Some(year)) = row.try_get::<Option<i32>, _>(col_index) {
        return JsonValue::String(year.to_string());
    }
    if let Ok(Some(year)) = row.try_get::<Option<i16>, _>(col_index) {
        return JsonValue::String(year.to_string());
    }
    if let Ok(Some(year)) = row.try_get::<Option<u16>, _>(col_index) {
        return JsonValue::String(year.to_string());
    }
    JsonValue::Null
}

fn parse_time_value(row: &MySqlRow, col_index: usize) -> JsonValue {
    // Try NaiveTime first (standard time-of-day)
    if let Ok(Some(t)) = row.try_get::<Option<NaiveTime>, _>(col_index) {
        return JsonValue::String(t.format("%H:%M:%S").to_string());
    }
    // Fallback to String
    if let Ok(Some(s)) = row.try_get::<Option<String>, _>(col_index) {
        return JsonValue::String(s);
    }
    JsonValue::Null
}

fn parse_json_value(row: &MySqlRow, col_index: usize) -> JsonValue {
    match row.try_get::<Option<JsonValue>, _>(col_index) {
        Ok(Some(json_value)) => json_value,
        _ => JsonValue::Null,
    }
}

fn parse_integer_value(row: &MySqlRow, col_index: usize) -> JsonValue {
    // Try from largest to smallest signed types
    if let Ok(Some(i)) = row.try_get::<Option<i64>, _>(col_index) {
        return JsonValue::Number(i.into());
    }
    if let Ok(Some(i)) = row.try_get::<Option<i32>, _>(col_index) {
        return JsonValue::Number((i as i64).into());
    }
    if let Ok(Some(i)) = row.try_get::<Option<i16>, _>(col_index) {
        return JsonValue::Number((i as i64).into());
    }
    if let Ok(Some(i)) = row.try_get::<Option<i8>, _>(col_index) {
        return JsonValue::Number((i as i64).into());
    }
    // Try unsigned types
    if let Ok(Some(u)) = row.try_get::<Option<u64>, _>(col_index) {
        if let Some(n) = serde_json::Number::from_f64(u as f64) {
            return JsonValue::Number(n);
        }
    }
    if let Ok(Some(u)) = row.try_get::<Option<u32>, _>(col_index) {
        return JsonValue::Number((u as i64).into());
    }
    if let Ok(Some(u)) = row.try_get::<Option<u16>, _>(col_index) {
        return JsonValue::Number((u as i64).into());
    }
    if let Ok(Some(u)) = row.try_get::<Option<u8>, _>(col_index) {
        return JsonValue::Number((u as i64).into());
    }
    JsonValue::Null
}

fn parse_bool_value(row: &MySqlRow, col_index: usize) -> JsonValue {
    if let Ok(Some(value)) = row.try_get::<Option<bool>, _>(col_index) {
        JsonValue::Bool(value)
    } else if let Ok(Some(value)) = row.try_get::<Option<i8>, _>(col_index) {
        JsonValue::Bool(value != 0)
    } else if let Ok(Some(value)) = row.try_get::<Option<String>, _>(col_index) {
        match value.to_lowercase().as_str() {
            "true" => JsonValue::Bool(true),
            "false" => JsonValue::Bool(false),
            _ => JsonValue::Null,
        }
    } else {
        JsonValue::Null
    }
}

fn parse_real_value(row: &MySqlRow, col_index: usize) -> JsonValue {
    match row.try_get::<Option<f64>, _>(col_index) {
        Ok(Some(f)) => f64_to_json_safe(f),
        _ => JsonValue::Null,
    }
}

fn parse_blob_value(row: &MySqlRow, col_index: usize) -> JsonValue {
    match row.try_get::<Option<Vec<u8>>, _>(col_index) {
        Ok(Some(bytes)) => JsonValue::String(general_purpose::STANDARD.encode(bytes)),
        _ => JsonValue::Null,
    }
}

/// Parse BINARY data, attempting UUID detection for 16-byte values.
fn parse_binary_value(row: &MySqlRow, col_index: usize) -> JsonValue {
    match row.try_get::<Option<Vec<u8>>, _>(col_index) {
        Ok(Some(bytes)) => {
            // Try UUID for 16-byte binary values
            if bytes.len() == 16 {
                if let Ok(uuid) = uuid::Uuid::from_slice(&bytes) {
                    return JsonValue::String(uuid.to_string());
                }
            }
            JsonValue::String(general_purpose::STANDARD.encode(bytes))
        }
        _ => JsonValue::Null,
    }
}

/// Parse MySQL SET type as a JSON array.
/// SET values are stored as comma-separated strings (e.g. "read,write,admin").
fn parse_set_value(row: &MySqlRow, col_index: usize) -> JsonValue {
    match row.try_get::<Option<String>, _>(col_index) {
        Ok(Some(s)) if !s.is_empty() => JsonValue::Array(
            s.split(',')
                .map(|v| JsonValue::String(v.trim().to_string()))
                .collect(),
        ),
        Ok(Some(_)) => JsonValue::Array(vec![]),
        _ => JsonValue::Null,
    }
}

fn parse_blob_or_text(row: &MySqlRow, col_index: usize) -> JsonValue {
    match row.try_get::<Option<Vec<u8>>, _>(col_index) {
        Ok(value) => {
            if let Some(i) = value {
                let u = super::blob_is_text(&i);
                if u {
                    return JsonValue::String(decode_auto(&i));
                }
                JsonValue::String(general_purpose::STANDARD.encode(i))
            } else {
                JsonValue::Null
            }
        }
        Err(_) => JsonValue::Null,
    }
}

fn parse_varbinary_value(row: &MySqlRow, col_index: usize) -> JsonValue {
    match row.try_get::<Option<Vec<u8>>, _>(col_index) {
        Ok(value) => {
            if let Some(i) = value {
                JsonValue::String(decode_auto(&i))
            } else {
                JsonValue::Null
            }
        }
        Err(_) => JsonValue::Null,
    }
}

fn parse_decimal(row: &MySqlRow, col_index: usize) -> serde_json::Value {
    match row.try_get::<Option<Decimal>, _>(col_index) {
        Ok(Some(d)) => serde_json::Value::String(d.to_string()),
        _ => serde_json::Value::Null,
    }
}

fn parse_geometry_value(_row: &MySqlRow, _col_index: usize) -> serde_json::Value {
    serde_json::Value::String("Does not support parsing".to_string())
}

/// Parse vector type as a JSON array of floating-point numbers.
/// Supports both text format `[0.1,0.2,0.3]` and binary format (packed f32 array).
fn parse_vector_value(row: &MySqlRow, col_index: usize) -> JsonValue {
    // Try as String first (JSON-like text format)
    if let Ok(Some(s)) = row.try_get::<Option<String>, _>(col_index) {
        return super::parse_vector_string(&s);
    }
    // Try as binary (packed f32 array)
    if let Ok(Some(bytes)) = row.try_get::<Option<Vec<u8>>, _>(col_index) {
        return super::parse_vector_bytes(&bytes);
    }
    JsonValue::Null
}