use crate::common::ColumnBaseInfo;
use crate::to_json::SqliteRowParse;
use base64::{engine::general_purpose, Engine};
use serde_json::{json, Value as JsonValue};
use sqlx::sqlite::{ SqliteRow};
use sqlx::{ Column,  Row, TypeInfo};

pub fn to_json(results: Vec<SqliteRow>) -> anyhow::Result<(Vec<JsonValue>, Vec<ColumnBaseInfo>)> {
    if results.is_empty() {
        return Ok((vec![], vec![]));
    }
    // 1.根据第一行数据生成 hashMap, 确定解析方法
    let first_row = &results[0];
    let SqliteRowParse { methods, columns } = determine_parsing_methods(first_row)?;

    // 2.根据对应col_index,对应的方法, 进行解析;
    let mut data: Vec<JsonValue> = Vec::with_capacity(results.len()); // with_capacity 可以预先分配足够的内存，从而减少内存分配和复制的次数。这样可以提高性能
    for row in results {
        let mut row_data = json!({});
        for col in row.columns() {
            let col_name = col.name();
            let col_index: usize = col.ordinal();
            let value = parse_value(&row, col_index, &methods);
            row_data[col_name] = value;
        }
        data.push(row_data);
    }
    Ok((data, columns))
}

fn determine_parsing_methods(
    // 确定每个字段的解析方法,返回hashmap
    row: &SqliteRow,
) -> anyhow::Result<SqliteRowParse> {
    // let mut methods = HashMap::new();
    let columns = row.columns();
    let mut methods: Vec<fn(&SqliteRow, usize) -> JsonValue> = Vec::with_capacity(columns.len());
    let mut new_columns = vec![];
    for col in columns {
        let col_index: usize = col.ordinal();
        let col_name = col.name();
        let field_type = col.type_info().name().to_uppercase();
        let method = match field_type.as_str() {
            "TEXT" | "TIME" | "DATETIME" | "DATE" => parse_text_value,
            "INTEGER" | "BOOLEAN" => parse_integer_value,
            "REAL" => parse_real_value,
            "BLOB" => parse_blob_value,
            "NUMERIC" => parse_numeric_value,
            "NULL" => parse_null_value, // 多处解析尝试
            _ => {
                return Err(anyhow::anyhow!(
                    "sqlite未知数据类型!  字段信息: {:#?},字段名:{}",
                    col.type_info(),
                    col.name(),
                ))
            }
        };
        methods.push(method);
        // columns.push(json!({"name":colum_name,"source_type":field_type,"index":col_index}));
        new_columns.push(ColumnBaseInfo {
            name: col_name.to_string(),
            r#type: field_type,
            index: col_index as u64,
        });
    }
    Ok(SqliteRowParse {
        methods,
        columns: new_columns,
    })
}

fn parse_value(
    // 解析数据
    row: &SqliteRow,
    col_index: usize,
    parsing_methods: &[fn(&SqliteRow, usize) -> JsonValue],
) -> JsonValue {
    let method = parsing_methods[col_index];
    method(row, col_index)
}

// 解析sqLite text字段
fn parse_text_value(row: &SqliteRow, col_index: usize) -> JsonValue {
    // 使用 as_ref() 避免提前克隆 String
    let text_ref: Option<&str> = row.try_get(col_index).ok().flatten();
    match text_ref {
        Some(text) => {
            // 提前过滤非 JSON 内容
            if text.starts_with('{') || text.starts_with('[') {
                match serde_json::from_str::<JsonValue>(text) {
                    Ok(parsed) => match parsed {
                        JsonValue::Array(_) | JsonValue::Object(_) => parsed,
                        _ => JsonValue::String(text.to_string()),
                    },
                    Err(_) => JsonValue::String(text.to_string()),
                }
            } else {
                JsonValue::String(text.to_string())
            }
        }
        None => JsonValue::Null,
    }
}

fn parse_integer_value(row: &SqliteRow, col_index: usize) -> JsonValue {
    match row.try_get::<Option<i64>, _>(col_index) {
        Ok(value) => {
            if let Some(i) = value {
                JsonValue::Number(i.into())
            } else {
                JsonValue::Null
            }
        }
        Err(_) => JsonValue::Null,
    }
}

fn parse_real_value(row: &SqliteRow, col_index: usize) -> JsonValue {
    match row.try_get::<Option<f64>, _>(col_index) {
        Ok(value) => {
            if let Some(i) = value {
                JsonValue::Number(serde_json::Number::from_f64(i).unwrap())
            } else {
                JsonValue::Null
            }
        }
        Err(_) => JsonValue::Null,
    }
}

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

fn parse_numeric_value(row: &SqliteRow, col_index: usize) -> JsonValue {
    // NUMERIC 类型根据实际数据选择最合适的存储方式，可以存储为 INTEGER、REAL 或 TEXT。
    row.try_get::<String, _>(col_index)
        .map(|v| {
            if let Ok(i) = v.parse::<i64>() {
                JsonValue::Number(i.into())
            } else if let Ok(f) = v.parse::<f64>() {
                JsonValue::Number(
                    serde_json::Number::from_f64(f).expect("Failed to create JSON number from f64"),
                )
            } else {
                JsonValue::String(v)
            }
        })
        .unwrap_or(JsonValue::Null)
}

// 类型为null,尝试多种解析
fn parse_null_value(row: &SqliteRow, col_index: usize) -> JsonValue {
    match row.try_get::<Option<i64>, _>(col_index) {
        Ok(value) => {
            if let Some(i) = value {
                JsonValue::Number(i.into())
            } else {
                JsonValue::Null
            }
        }
        Err(_) => {
            match row.try_get::<Option<String>, _>(col_index) {
                Ok(optional_text) => {
                    if let Some(text) = optional_text {
                        // 尝试解析为 JSON
                        match serde_json::from_str::<JsonValue>(&text) {
                            Ok(parsed) => match parsed {
                                JsonValue::Array(_) | JsonValue::Object(_) => parsed,
                                _ => JsonValue::String(text),
                            },
                            Err(_) => JsonValue::String(text),
                        }
                    } else {
                        JsonValue::Null
                    }
                }
                Err(_) => match row.try_get::<Option<f64>, _>(col_index) {
                    Ok(value) => {
                        if let Some(i) = value {
                            JsonValue::Number(serde_json::Number::from_f64(i).unwrap())
                        } else {
                            JsonValue::Null
                        }
                    }
                    Err(_) => JsonValue::Null,
                },
            }
        }
    }
}
