dbcli 0.1.0

//! # SQLite → JSON Conversion Module
//!
//! Provides functionality to convert a list of [`sqlx::sqlite::SqliteRow`] 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.
//!
//! SQLite uses a weak type system; dates and times are stored as TEXT, so no chrono
//! dependency is needed. Type mappings follow SQLite official type names (uppercase);
//! see [`crate::to_json::ToJsonCustomizer`] for the full reference table.

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

// Converts a SQLite query result set into a JSON array.
//
// Input: `Vec<SqliteRow>`, 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
//
// | SQLite Type | JSON output |
// |------------|-------------|
// | TEXT / DATETIME / DATE / TIME | JSON String (auto-parses JSON if starts with `{`/`[`) |
// | INTEGER / BOOLEAN | JSON Number (i64) |
// | REAL | JSON Number (f64, NaN/Inf → String) |
// | BLOB | Base64 String |
// | NUMERIC | Auto-infer i64 / f64 / String |
// | NULL | Dynamic inference: try i64 / String / f64 in order |
// | VECTOR (sqlite-vec extension) | JSON Array (f64 values) |
//
// # Example
//
// ```rust,no_run
// use sqlx::SqlitePool;
// use dbcli::to_json::sqlite::to_json;
//
// async fn example(pool: &SqlitePool) -> anyhow::Result<()> {
//     let rows = sqlx::query("SELECT id, name, data FROM items")
//         .fetch_all(pool)
//         .await?;
//     let (data, columns) = to_json(rows)?;
//     println!("{}", serde_json::to_string_pretty(&data)?);
//     Ok(())
// }
// ```
crate::impl_to_json!(SqliteRow, SqliteRowParse);


fn determine_parsing_methods(
    row: &SqliteRow,
) -> anyhow::Result<SqliteRowParse> {
    let customizer = super::get_customizer();

    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: fn(&SqliteRow, usize) -> JsonValue = customizer
            .customize_sqlite(&field_type)
            .map(Ok)
            .unwrap_or_else(|| default_sqlite_method(&field_type, col))?;

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

#[inline]
fn default_sqlite_method(
    field_type: &str,
    col: &sqlx::sqlite::SqliteColumn,
) -> anyhow::Result<fn(&SqliteRow, usize) -> JsonValue> {
    let method = match field_type {
        "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,
        // Vector type (sqlite-vec extension)
        "VECTOR" | "FLOAT32" | "FLOAT64" | "INT8" => parse_vector_value,
        _ => {
            return Err(anyhow::anyhow!(
                "SQLite: unknown data type! Column info: {:#?}, column name: {}",
                col.type_info(),
                col.name(),
            ))
        }
    };
    Ok(method)
}


fn parse_text_value(row: &SqliteRow, col_index: usize) -> JsonValue {
    let text_ref: Option<&str> = row.try_get(col_index).ok().flatten();
    match text_ref {
        Some(text) => {
            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(Some(f)) => f64_to_json_safe(f),
        _ => JsonValue::Null,
    }
}

fn parse_blob_value(row: &SqliteRow, 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,
    }
}

fn parse_numeric_value(row: &SqliteRow, col_index: usize) -> JsonValue {
    // NUMERIC type stores data in the most appropriate form: INTEGER, REAL, or 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>() {
                f64_to_json_safe(f)
            } else {
                JsonValue::String(v)
            }
        })
        .unwrap_or(JsonValue::Null)
}

fn parse_null_value(row: &SqliteRow, col_index: usize) -> JsonValue {
    try_as_i64(row, col_index)
        .or_else(|| try_as_string(row, col_index))
        .or_else(|| try_as_f64(row, col_index))
        .unwrap_or(JsonValue::Null)
}

#[inline]
fn try_as_i64(row: &SqliteRow, col_index: usize) -> Option<JsonValue> {
    row.try_get::<Option<i64>, _>(col_index)
        .ok()
        .flatten()
        .map(|i| JsonValue::Number(i.into()))
}

#[inline]
fn try_as_string(row: &SqliteRow, col_index: usize) -> Option<JsonValue> {
    row.try_get::<Option<String>, _>(col_index)
        .ok()
        .flatten()
        .map(|text| {
            serde_json::from_str::<JsonValue>(&text)
                .ok()
                .filter(|v| matches!(v, JsonValue::Array(_) | JsonValue::Object(_)))
                .unwrap_or_else(|| JsonValue::String(text))
        })
}

#[inline]
fn try_as_f64(row: &SqliteRow, col_index: usize) -> Option<JsonValue> {
    row.try_get::<Option<f64>, _>(col_index)
        .ok()
        .flatten()
        .map(|f| f64_to_json_safe(f))
}

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