query-forge 0.2.0

use std::{
    collections::HashSet,
    fmt::Write as _,
    fs,
    sync::OnceLock,
};
use std::path::Path;

use anyhow::{Context, Result, anyhow, bail};
use calamine::{Data, Reader, open_workbook_auto};
use regex::Regex;
use roxmltree::{Document, Node};
use serde_json::Value as JsonValue;
use rusqlite::{
    Connection, params_from_iter,
    types::{Value, ValueRef},
};
use rust_xlsxwriter::Workbook;

#[derive(Debug, Clone, PartialEq)]
pub enum QueryValue {
    Null,
    Integer(i64),
    Real(f64),
    Text(String),
}

#[derive(Debug, Clone, PartialEq)]
pub struct QueryResult {
    pub columns: Vec<String>,
    pub rows: Vec<Vec<QueryValue>>,
}

#[derive(Debug, Clone, PartialEq)]
pub struct QueryParam {
    pub name: String,
    pub value: QueryValue,
}

#[derive(Debug, Clone, Copy)]
pub struct WorkbookInput<'a> {
    pub path: &'a Path,
    pub sheet_name: Option<&'a str>,
    pub table_name: Option<&'a str>,
}

/// The SQL-compatible type inferred for a column.
#[derive(Debug, Clone, PartialEq)]
pub enum InferredType {
    Integer,
    Real,
    Text,
    /// Column contains a mix of numeric and text values.
    Mixed,
}

impl InferredType {
    pub fn as_str(&self) -> &'static str {
        match self {
            InferredType::Integer => "INTEGER",
            InferredType::Real => "REAL",
            InferredType::Text => "TEXT",
            InferredType::Mixed => "MIXED",
        }
    }
}

/// Per-column information returned by inspection commands.
#[derive(Debug, Clone)]
pub struct ColumnInfo {
    pub table_name: String,
    pub column_name: String,
    pub inferred_type: InferredType,
    pub nullable: bool,
}

/// Optional per-column statistics (enabled by `--stats`).
#[derive(Debug, Clone)]
pub struct ColumnStats {
    pub distinct_count: usize,
    pub min_value: Option<String>,
    pub max_value: Option<String>,
}

/// Summary of a single loaded table, returned by `load_table_summaries`.
#[derive(Debug, Clone)]
pub struct TableSummary {
    /// The logical SQL table name (explicit or auto-generated).
    pub table_name: String,
    /// The original file path as provided on the command line.
    pub source_path: String,
    /// The sheet/tag/key/index selector, if any.
    pub source_selector: Option<String>,
    pub row_count: usize,
    pub columns: Vec<ColumnInfo>,
    /// Up to `sample` rows of actual data (column values in column order).
    pub sample_rows: Vec<Vec<QueryValue>>,
    /// Non-fatal warnings (e.g. mixed-type columns, duplicate header normalisations).
    pub warnings: Vec<String>,
    /// Per-column statistics; `None` when `--stats` is not requested.
    pub column_stats: Option<Vec<ColumnStats>>,
}

/// Load metadata for every input without running a SQL query.
///
/// Returns one [`TableSummary`] per input in declaration order.
pub fn load_table_summaries(
    workbook_inputs: &[WorkbookInput<'_>],
    sample: usize,
    include_stats: bool,
) -> Result<Vec<TableSummary>> {
    let mut summaries = Vec::with_capacity(workbook_inputs.len());

    for (index, workbook_input) in workbook_inputs.iter().enumerate() {
        let sheet = load_input(workbook_input.path, workbook_input.sheet_name, true)?;

        let table_name = workbook_input.table_name.map(str::to_owned).unwrap_or_else(|| {
            if index == 0 { "table".to_owned() } else { format!("table{}", index + 1) }
        });

        let columns = infer_column_infos(&table_name, &sheet);
        let mut warnings = collect_warnings(&table_name, &sheet, &columns);

        let sample_rows = sheet.rows.iter().take(sample).cloned().collect();

        let column_stats = if include_stats {
            Some(compute_column_stats(&sheet))
        } else {
            None
        };

        // Warn about empty tables (non-fatal for inspection).
        if sheet.rows.is_empty() {
            warnings.push(format!(
                "table '{}' loaded from '{}' contains no data rows",
                table_name,
                workbook_input.path.display()
            ));
        }

        summaries.push(TableSummary {
            table_name,
            source_path: workbook_input.path.display().to_string(),
            source_selector: workbook_input.sheet_name.map(str::to_owned),
            row_count: sheet.rows.len(),
            columns,
            sample_rows,
            warnings,
            column_stats,
        });
    }

    Ok(summaries)
}

/// Infer the SQL-compatible type and nullability for each column in `sheet`.
fn infer_column_infos(table_name: &str, sheet: &SheetData) -> Vec<ColumnInfo> {
    sheet
        .columns
        .iter()
        .enumerate()
        .map(|(col_idx, col_name)| {
            let mut has_integer = false;
            let mut has_real = false;
            let mut has_text = false;
            let mut has_null = false;

            for row in &sheet.rows {
                match row.get(col_idx).unwrap_or(&QueryValue::Null) {
                    QueryValue::Null => has_null = true,
                    QueryValue::Integer(_) => has_integer = true,
                    QueryValue::Real(_) => has_real = true,
                    QueryValue::Text(_) => has_text = true,
                }
            }

            let inferred_type = match (has_integer, has_real, has_text) {
                (true, false, false) => InferredType::Integer,
                (false, false, true) => InferredType::Text,
                (false, false, false) => InferredType::Text,
                // Integer+Real and Real-only both map to REAL: integers are
                // promotable to real without loss, so REAL is the wider type.
                (_, true, false) => InferredType::Real,
                _ => InferredType::Mixed,
            };

            ColumnInfo {
                table_name: table_name.to_owned(),
                column_name: col_name.clone(),
                inferred_type,
                nullable: has_null,
            }
        })
        .collect()
}

/// Collect non-fatal warnings for a loaded table.
fn collect_warnings(
    table_name: &str,
    sheet: &SheetData,
    columns: &[ColumnInfo],
) -> Vec<String> {
    let mut warnings = Vec::new();

    for col_info in columns {
        if col_info.inferred_type == InferredType::Mixed {
            warnings.push(format!(
                "column '{}' in table '{}' has mixed types (INTEGER/REAL and TEXT values)",
                col_info.column_name, table_name
            ));
        }
    }

    // Detect duplicate original column names that were de-duplicated by normalisation.
    let mut seen_names: std::collections::HashMap<String, usize> =
        std::collections::HashMap::new();
    for col in &sheet.columns {
        *seen_names.entry(col.clone()).or_insert(0) += 1;
    }
    for (name, count) in &seen_names {
        if *count > 1 {
            warnings.push(format!(
                "column name '{}' appears {} times in table '{}'; duplicates were renamed",
                name, count, table_name
            ));
        }
    }

    warnings
}

/// Compute per-column distinct counts and min/max values.
fn compute_column_stats(sheet: &SheetData) -> Vec<ColumnStats> {
    sheet
        .columns
        .iter()
        .enumerate()
        .map(|(col_idx, _)| {
            let mut distinct: std::collections::HashSet<String> =
                std::collections::HashSet::new();
            // Track min/max as f64 to handle both Integer and Real columns
            // uniformly without reparsing strings.
            let mut min_num: Option<f64> = None;
            let mut max_num: Option<f64> = None;
            // Remember whether the extremes came from an integer so we can
            // render them without a spurious decimal point.
            let mut min_as_int: Option<i64> = None;
            let mut max_as_int: Option<i64> = None;

            for row in &sheet.rows {
                let value = row.get(col_idx).unwrap_or(&QueryValue::Null);
                if matches!(value, QueryValue::Null) {
                    continue;
                }
                distinct.insert(display_value(value));

                let (as_f64, as_int) = match value {
                    QueryValue::Integer(n) => (*n as f64, Some(*n)),
                    QueryValue::Real(n) => (*n, None),
                    _ => continue,
                };

                if min_num.map_or(true, |m| as_f64 < m) {
                    min_num = Some(as_f64);
                    min_as_int = as_int;
                }
                if max_num.map_or(true, |m| as_f64 > m) {
                    max_num = Some(as_f64);
                    max_as_int = as_int;
                }
            }

            let min_value = min_num.map(|v| {
                min_as_int.map_or_else(|| v.to_string(), |i| i.to_string())
            });
            let max_value = max_num.map(|v| {
                max_as_int.map_or_else(|| v.to_string(), |i| i.to_string())
            });

            ColumnStats {
                distinct_count: distinct.len(),
                min_value,
                max_value,
            }
        })
        .collect()
}

pub fn run_query(
    workbook_path: &Path,
    sheet_name: Option<&str>,
    query: &str,
    has_headers: bool,
) -> Result<QueryResult> {
    run_query_with_params(workbook_path, sheet_name, query, &[], has_headers)
}

pub fn run_query_with_params(
    workbook_path: &Path,
    sheet_name: Option<&str>,
    query: &str,
    params: &[QueryParam],
    has_headers: bool,
) -> Result<QueryResult> {
    run_query_with_params_multi(&[workbook_path], sheet_name, query, params, has_headers)
}

pub fn run_query_with_params_multi(
    workbook_paths: &[&Path],
    sheet_name: Option<&str>,
    query: &str,
    params: &[QueryParam],
    has_headers: bool,
) -> Result<QueryResult> {
    let workbook_inputs = workbook_paths
        .iter()
        .map(|path| WorkbookInput {
            path,
            sheet_name,
            table_name: None,
        })
        .collect::<Vec<_>>();

    run_query_with_params_multi_inputs(&workbook_inputs, query, params, has_headers)
}

pub fn run_query_with_params_multi_inputs(
    workbook_inputs: &[WorkbookInput<'_>],
    query: &str,
    params: &[QueryParam],
    has_headers: bool,
) -> Result<QueryResult> {
    if workbook_inputs.is_empty() {
        bail!("at least one workbook input is required");
    }

    let connection =
        Connection::open_in_memory().context("failed to create in-memory SQLite database")?;
    let mut registered_sheet_views = HashSet::new();

    for (index, workbook_input) in workbook_inputs.iter().enumerate() {
        let sheet = load_input(
            workbook_input.path,
            workbook_input.sheet_name,
            has_headers,
        )?;
        let table_name = workbook_input.table_name.map(str::to_owned).unwrap_or_else(|| {
            if index == 0 {
                "table".to_owned()
            } else {
                format!("table{}", index + 1)
            }
        });

        register_sheet(
            &connection,
            &sheet,
            &table_name,
            &mut registered_sheet_views,
        )?;
    }

    execute_query(&connection, query, params)
}

pub fn render_text(result: &QueryResult) -> String {
    if result.columns.is_empty() {
        return String::new();
    }

    let mut column_widths = result.columns.iter().map(|column| column.len()).collect::<Vec<_>>();
    let mut rendered_rows = Vec::with_capacity(result.rows.len());

    for row in &result.rows {
        let mut rendered_row = Vec::with_capacity(result.columns.len());
        for index in 0..result.columns.len() {
            let rendered_value = row
                .get(index)
                .map(display_value)
                .unwrap_or_default();
            column_widths[index] = column_widths[index].max(rendered_value.len());
            rendered_row.push(rendered_value);
        }
        rendered_rows.push(rendered_row);
    }

    let mut output = String::new();
    write_aligned_row(&mut output, &result.columns, &column_widths);
    output.push('\n');
    output.push_str(
        &column_widths
            .iter()
            .map(|width| "-".repeat(*width))
            .collect::<Vec<_>>()
            .join("-+-"),
    );

    for row in rendered_rows {
        output.push('\n');
        write_aligned_row(&mut output, &row, &column_widths);
    }

    output
}

fn write_aligned_row(output: &mut String, values: &[String], column_widths: &[usize]) {
    for (index, value) in values.iter().enumerate() {
        if index > 0 {
            output.push_str(" | ");
        }

        let _ = write!(output, "{value:<width$}", width = column_widths[index]);
    }
}

pub fn render_csv(result: &QueryResult) -> String {
    let mut output = String::new();
    output.push_str(
        &result
            .columns
            .iter()
            .map(|column| escape_csv_field(column))
            .collect::<Vec<_>>()
            .join(","),
    );

    for row in &result.rows {
        output.push('\n');
        output.push_str(
            &row.iter()
                .map(display_value)
                .map(|value| escape_csv_field(&value))
                .collect::<Vec<_>>()
                .join(","),
        );
    }

    output
}

pub fn render_jsonl(result: &QueryResult) -> String {
    let mut output = String::new();

    for (row_index, row) in result.rows.iter().enumerate() {
        if row_index > 0 {
            output.push('\n');
        }
        output.push('{');
        for (column_index, column) in result.columns.iter().enumerate() {
            if column_index > 0 {
                output.push(',');
            }
            output.push_str(&escape_json_string(column));
            output.push(':');
            output.push_str(&to_json_value(
                row.get(column_index).unwrap_or(&QueryValue::Null),
            ));
        }
        output.push('}');
    }

    output
}

pub fn render_markdown(result: &QueryResult) -> String {
    let mut output = String::new();

    output.push('|');
    for column in &result.columns {
        output.push(' ');
        output.push_str(&escape_markdown_cell(column));
        output.push(' ');
        output.push('|');
    }
    output.push('\n');

    output.push('|');
    for _ in &result.columns {
        output.push_str(" --- |");
    }

    for row in &result.rows {
        output.push('\n');
        output.push('|');
        for column_index in 0..result.columns.len() {
            let value = row.get(column_index).unwrap_or(&QueryValue::Null);
            output.push(' ');
            output.push_str(&escape_markdown_cell(&display_value(value)));
            output.push(' ');
            output.push('|');
        }
    }

    output
}

pub fn render_xml(result: &QueryResult) -> String {
    let mut output = String::from("<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n<data>\n");

    for row in &result.rows {
        output.push_str("  <row>\n");
        for (column_index, column) in result.columns.iter().enumerate() {
            let value = row.get(column_index).unwrap_or(&QueryValue::Null);
            let xml_column = escape_xml_tag(column);
            let xml_value = escape_xml_text(&display_value(value));
            output.push_str(&format!("    <{}>{}</{}>
", xml_column, xml_value, xml_column));
        }
        output.push_str("  </row>\n");
    }

    output.push_str("</data>");
    output
}

pub fn write_xlsx(result: &QueryResult, output_path: &Path) -> Result<()> {
    let mut workbook = Workbook::new();
    let worksheet = workbook.add_worksheet();

    for (column, header) in result.columns.iter().enumerate() {
        worksheet.write_string(0, column as u16, header)?;
    }

    for (row_index, row) in result.rows.iter().enumerate() {
        for (column_index, value) in row.iter().enumerate() {
            let excel_row = (row_index + 1) as u32;
            let excel_column = column_index as u16;

            match value {
                QueryValue::Null => {}
                QueryValue::Integer(number) => {
                    worksheet.write_number(excel_row, excel_column, *number as f64)?;
                }
                QueryValue::Real(number) => {
                    worksheet.write_number(excel_row, excel_column, *number)?;
                }
                QueryValue::Text(text) => {
                    worksheet.write_string(excel_row, excel_column, text)?;
                }
            }
        }
    }

    workbook
        .save(output_path)
        .with_context(|| format!("failed to write {}", output_path.display()))
}

#[derive(Debug)]
struct SheetData {
    original_name: String,
    columns: Vec<String>,
    rows: Vec<Vec<QueryValue>>,
}

fn load_input(
    input_path: &Path,
    requested_sheet: Option<&str>,
    has_headers: bool,
) -> Result<SheetData> {
    if input_path
        .extension()
        .map(|extension| extension.to_string_lossy())
        .is_some_and(|extension| extension.eq_ignore_ascii_case("xml"))
    {
        return load_xml_sheet(input_path, requested_sheet);
    }

    if input_path
        .extension()
        .map(|extension| extension.to_string_lossy())
        .is_some_and(|extension| extension.eq_ignore_ascii_case("csv"))
    {
        return load_csv_sheet(input_path, requested_sheet, has_headers);
    }

    if input_path
        .extension()
        .map(|extension| extension.to_string_lossy())
        .is_some_and(|extension| extension.eq_ignore_ascii_case("jsonl"))
    {
        return load_jsonl_sheet(input_path, requested_sheet);
    }

    if input_path
        .extension()
        .map(|extension| extension.to_string_lossy())
        .is_some_and(|extension| extension.eq_ignore_ascii_case("json"))
    {
        return load_json_sheet(input_path, requested_sheet);
    }

    if input_path
        .extension()
        .map(|extension| extension.to_string_lossy())
        .is_some_and(|extension| {
            extension.eq_ignore_ascii_case("md") || extension.eq_ignore_ascii_case("markdown")
        })
    {
        return load_markdown_sheet(input_path, requested_sheet, has_headers);
    }

    load_xlsx_sheet(input_path, requested_sheet, has_headers)
}

fn load_csv_sheet(
    csv_path: &Path,
    requested_sheet: Option<&str>,
    has_headers: bool,
) -> Result<SheetData> {
    if let Some(selector) = requested_sheet {
        bail!(
            "CSV input {} does not support selector '{selector}'. Remove ':{selector}' from --input for CSV files.",
            csv_path.display()
        );
    }

    let mut reader = csv::ReaderBuilder::new()
        .has_headers(has_headers)
        .from_path(csv_path)
        .with_context(|| format!("failed to open {}", csv_path.display()))?;

    let mut records = Vec::new();
    for record in reader.records() {
        let record = record.with_context(|| {
            format!("failed to read CSV record from {}", csv_path.display())
        })?;
        records.push(record.iter().map(str::to_owned).collect::<Vec<_>>());
    }

    let columns = if has_headers {
        let headers = reader
            .headers()
            .with_context(|| format!("failed to read headers from {}", csv_path.display()))?
            .iter()
            .map(str::to_owned)
            .collect::<Vec<_>>();
        normalize_text_headers(&headers)
    } else {
        let width = records.iter().map(Vec::len).max().unwrap_or(0);
        (1..=width).map(|index| format!("column{index}")).collect()
    };

    if columns.is_empty() {
        bail!("CSV input {} is empty", csv_path.display());
    }

    let rows = records
        .into_iter()
        .map(|record| {
            (0..columns.len())
                .map(|index| {
                    let value = record.get(index).map(String::as_str).unwrap_or("");
                    parse_scalar_value(value)
                })
                .collect::<Vec<_>>()
        })
        .filter(|row| row.iter().any(|value| !matches!(value, QueryValue::Null)))
        .collect::<Vec<_>>();

    Ok(SheetData {
        original_name: "csv".to_owned(),
        columns,
        rows,
    })
}

fn load_jsonl_sheet(jsonl_path: &Path, requested_sheet: Option<&str>) -> Result<SheetData> {
    if let Some(selector) = requested_sheet {
        bail!(
            "JSONL input {} does not support selector '{selector}'. Remove ':{selector}' from --input for JSONL files.",
            jsonl_path.display()
        );
    }

    let content = fs::read_to_string(jsonl_path)
        .with_context(|| format!("failed to read {}", jsonl_path.display()))?;

    let mut rows_maps = Vec::<Vec<(String, QueryValue)>>::new();
    for (line_index, raw_line) in content.lines().enumerate() {
        let line = raw_line.trim();
        if line.is_empty() {
            continue;
        }

        let json_value = serde_json::from_str::<JsonValue>(line).with_context(|| {
            format!(
                "failed to parse JSONL line {} in {}",
                line_index + 1,
                jsonl_path.display()
            )
        })?;

        let JsonValue::Object(object) = json_value else {
            bail!(
                "JSONL line {} in {} is not an object",
                line_index + 1,
                jsonl_path.display()
            );
        };

        rows_maps.push(
            object
                .into_iter()
                .map(|(key, value)| (key, json_to_query_value(value)))
                .collect(),
        );
    }

    if rows_maps.is_empty() {
        bail!("JSONL input {} is empty", jsonl_path.display());
    }

    Ok(rows_maps_to_sheet_data(rows_maps, "jsonl".to_owned()))
}

fn load_json_sheet(json_path: &Path, requested_sheet: Option<&str>) -> Result<SheetData> {
    let content = fs::read_to_string(json_path)
        .with_context(|| format!("failed to read {}", json_path.display()))?;
    let root = serde_json::from_str::<JsonValue>(&content)
        .with_context(|| format!("failed to parse JSON {}", json_path.display()))?;

    let scope = if let Some(sheet_key) = requested_sheet {
        let JsonValue::Object(mut object) = root else {
            bail!(
                "JSON input {} uses selector '{sheet_key}', but key selection requires a top-level object. Remove the selector or provide a JSON object at the root.",
                json_path.display()
            );
        };

        let mut available_keys = object.keys().cloned().collect::<Vec<_>>();
        available_keys.sort();

        object
            .remove(sheet_key)
            .ok_or_else(|| {
                let available_suffix = if available_keys.is_empty() {
                    " The top-level object has no keys.".to_owned()
                } else {
                    format!(" Available keys: {}.", available_keys.join(", "))
                };
                anyhow!(
                    "JSON key '{sheet_key}' not found in {}.{available_suffix}",
                    json_path.display()
                )
            })?
    } else {
        root
    };

    let rows_maps = json_scope_to_rows(scope);
    if rows_maps.is_empty() {
        if let Some(sheet_key) = requested_sheet {
            bail!(
                "JSON selector '{sheet_key}' in {} resolved to an empty table (no rows)",
                json_path.display()
            );
        }
        bail!("JSON input {} is empty", json_path.display());
    }

    Ok(rows_maps_to_sheet_data(
        rows_maps,
        requested_sheet.unwrap_or("json").to_owned(),
    ))
}

fn load_markdown_sheet(
    markdown_path: &Path,
    requested_sheet: Option<&str>,
    has_headers: bool,
) -> Result<SheetData> {
    let content = fs::read_to_string(markdown_path)
        .with_context(|| format!("failed to read {}", markdown_path.display()))?;
    let tables = parse_markdown_tables(&content);
    if tables.is_empty() {
        bail!("Markdown input {} does not contain any table", markdown_path.display());
    }

    let table_index = if let Some(key) = requested_sheet {
        let index = key
            .trim()
            .parse::<usize>()
            .map_err(|_| {
                anyhow!(
                    "invalid Markdown table selector '{key}' for {}. Use a 1-based numeric index such as ':1' or ':2'.",
                    markdown_path.display()
                )
            })?;
        if index == 0 {
            bail!(
                "invalid Markdown table selector '{key}' for {}. Table indexes are 1-based (:1, :2, ...).",
                markdown_path.display()
            );
        }
        index
    } else {
        1
    };

    let table_count = tables.len();
    let Some(table) = tables.into_iter().nth(table_index - 1) else {
        bail!(
            "Markdown table {} not found in {}. Found {} table(s); choose an index between 1 and {}.",
            table_index,
            markdown_path.display(),
            table_count,
            table_count
        );
    };

    let mut rows = table.rows;
    let columns = if has_headers {
        normalize_text_headers(&table.headers)
    } else {
        rows.insert(0, table.headers);
        let width = rows.iter().map(Vec::len).max().unwrap_or(0);
        (1..=width).map(|index| format!("column{index}")).collect()
    };

    if columns.is_empty() {
        bail!("Markdown table {} is empty", table_index);
    }

    let data_rows = rows
        .into_iter()
        .map(|row| {
            (0..columns.len())
                .map(|index| {
                    let value = row.get(index).map(String::as_str).unwrap_or("");
                    parse_scalar_value(value)
                })
                .collect::<Vec<_>>()
        })
        .filter(|row| row.iter().any(|value| !matches!(value, QueryValue::Null)))
        .collect::<Vec<_>>();

    if data_rows.is_empty() {
        bail!(
            "Markdown table {} in {} is empty (no data rows)",
            table_index,
            markdown_path.display()
        );
    }

    Ok(SheetData {
        original_name: format!("table{table_index}"),
        columns,
        rows: data_rows,
    })
}

fn load_xlsx_sheet(
    workbook_path: &Path,
    requested_sheet: Option<&str>,
    has_headers: bool,
) -> Result<SheetData> {
    let mut workbook = open_workbook_auto(workbook_path)
        .with_context(|| format!("failed to open {}", workbook_path.display()))?;

    let sheet_name = match requested_sheet {
        Some(name) => name.to_owned(),
        None => workbook
            .sheet_names()
            .first()
            .cloned()
            .ok_or_else(|| anyhow!("workbook does not contain any sheets"))?,
    };

    let range = workbook
        .worksheet_range(&sheet_name)
        .with_context(|| format!("failed to read sheet {sheet_name}"))?;

    if range.width() == 0 {
        bail!("sheet {sheet_name} is empty");
    }

    let mut rows = range.rows();
    let columns = if has_headers {
        let header_row = rows
            .next()
            .ok_or_else(|| anyhow!("sheet {sheet_name} is empty"))?;
        normalize_headers(header_row, range.width())
    } else {
        (1..=range.width())
            .map(|index| format!("column{index}"))
            .collect()
    };

    let data_rows = rows
        .map(|row| {
            (0..columns.len())
                .map(|index| convert_cell(row.get(index).unwrap_or(&Data::Empty)))
                .collect::<Vec<_>>()
        })
        .filter(|row| row.iter().any(|value| !matches!(value, QueryValue::Null)))
        .collect();

    Ok(SheetData {
        original_name: sheet_name,
        columns,
        rows: data_rows,
    })
}

fn load_xml_sheet(xml_path: &Path, requested_sheet: Option<&str>) -> Result<SheetData> {
    let xml_content = fs::read_to_string(xml_path)
        .with_context(|| format!("failed to read {}", xml_path.display()))?;
    let document = Document::parse(&xml_content)
        .with_context(|| format!("failed to parse XML {}", xml_path.display()))?;

    let root = document.root_element();
    let scope = if let Some(sheet_tag) = requested_sheet {
        root.descendants()
            .find(|node| node.is_element() && node.tag_name().name() == sheet_tag)
            .ok_or_else(|| anyhow!("XML tag '{sheet_tag}' not found"))?
    } else {
        root
    };

    let mut records = collect_xml_records(scope);
    if records.is_empty() {
        let fallback = xml_row_from_children(scope);
        if fallback.is_empty() {
            let scope_name = requested_sheet.unwrap_or(scope.tag_name().name());
            bail!("XML scope '{scope_name}' does not contain tabular data");
        }
        records.push(fallback);
    }

    let mut columns = Vec::new();
    for row in &records {
        for (column, _) in row {
            if !columns.iter().any(|existing| existing == column) {
                columns.push(column.clone());
            }
        }
    }

    let rows = records
        .into_iter()
        .map(|row| {
            columns
                .iter()
                .map(|column| {
                    row.iter()
                        .find(|(key, _)| key == column)
                        .map(|(_, value)| value.clone())
                        .unwrap_or(QueryValue::Null)
                })
                .collect::<Vec<_>>()
        })
        .collect::<Vec<_>>();

    let original_name = requested_sheet
        .map(str::to_owned)
        .unwrap_or_else(|| scope.tag_name().name().to_owned());

    Ok(SheetData {
        original_name,
        columns,
        rows,
    })
}

fn collect_xml_records(scope: Node<'_, '_>) -> Vec<Vec<(String, QueryValue)>> {
    let direct_row_nodes = scope
        .children()
        .filter(|node| node.is_element() && node.tag_name().name().eq_ignore_ascii_case("row"))
        .collect::<Vec<_>>();
    if !direct_row_nodes.is_empty() {
        return direct_row_nodes
            .into_iter()
            .map(xml_row_from_children)
            .filter(|row| !row.is_empty())
            .collect();
    }

    scope
        .descendants()
        .filter(|node| node.is_element() && *node != scope)
        .filter(|node| is_xml_record_candidate(*node))
        .map(xml_row_from_children)
        .filter(|row| !row.is_empty())
        .collect()
}

fn is_xml_record_candidate(node: Node<'_, '_>) -> bool {
    let children = node
        .children()
        .filter(|child| child.is_element())
        .collect::<Vec<_>>();

    !children.is_empty()
        && children
            .iter()
            .all(|child| !child.children().any(|inner| inner.is_element()))
}

fn xml_row_from_children(node: Node<'_, '_>) -> Vec<(String, QueryValue)> {
    node.children()
        .filter(|child| child.is_element())
        .map(|child| {
            let column = child.tag_name().name().to_owned();
            let value = xml_text_to_query_value(&xml_text_content(child));
            (column, value)
        })
        .collect()
}

fn xml_text_content(node: Node<'_, '_>) -> String {
    node.text().unwrap_or_default().trim().to_owned()
}

fn xml_text_to_query_value(raw: &str) -> QueryValue {
    parse_scalar_value(raw)
}

fn parse_scalar_value(raw: &str) -> QueryValue {
    if raw.is_empty() {
        return QueryValue::Null;
    }

    let trimmed = raw.trim();
    if trimmed.is_empty() {
        return QueryValue::Null;
    }

    if trimmed.eq_ignore_ascii_case("true") {
        return QueryValue::Integer(1);
    }

    if trimmed.eq_ignore_ascii_case("false") {
        return QueryValue::Integer(0);
    }

    if let Ok(value) = trimmed.parse::<i64>() {
        return QueryValue::Integer(value);
    }

    if let Ok(value) = trimmed.parse::<f64>() {
        return QueryValue::Real(value);
    }

    QueryValue::Text(raw.to_owned())
}

fn normalize_text_headers(headers: &[String]) -> Vec<String> {
    let mut seen = std::collections::HashMap::new();

    headers
        .iter()
        .enumerate()
        .map(|(index, header)| {
            let base = if header.trim().is_empty() {
                format!("column{}", index + 1)
            } else {
                header.clone()
            };

            let count = seen
                .entry(base.clone())
                .and_modify(|count| *count += 1)
                .or_insert(1usize);

            if *count == 1 {
                base
            } else {
                format!("{base}_{count}")
            }
        })
        .collect()
}

fn rows_maps_to_sheet_data(rows_maps: Vec<Vec<(String, QueryValue)>>, original_name: String) -> SheetData {
    let mut columns = Vec::<String>::new();
    for row in &rows_maps {
        for (column, _) in row {
            if !columns.iter().any(|existing| existing == column) {
                columns.push(column.clone());
            }
        }
    }

    let rows = rows_maps
        .into_iter()
        .map(|row| {
            columns
                .iter()
                .map(|column| {
                    row.iter()
                        .find(|(key, _)| key == column)
                        .map(|(_, value)| value.clone())
                        .unwrap_or(QueryValue::Null)
                })
                .collect::<Vec<_>>()
        })
        .collect::<Vec<_>>();

    SheetData {
        original_name,
        columns,
        rows,
    }
}

#[derive(Debug)]
struct MarkdownTable {
    headers: Vec<String>,
    rows: Vec<Vec<String>>,
}

fn parse_markdown_tables(content: &str) -> Vec<MarkdownTable> {
    let lines = content.lines().collect::<Vec<_>>();
    let mut tables = Vec::new();
    let mut index = 0usize;

    while index + 1 < lines.len() {
        if !looks_like_markdown_row(lines[index]) || !is_markdown_separator(lines[index + 1]) {
            index += 1;
            continue;
        }

        let headers = parse_markdown_row(lines[index]);
        if headers.is_empty() {
            index += 1;
            continue;
        }

        let mut rows = Vec::new();
        index += 2;
        while index < lines.len() && looks_like_markdown_row(lines[index]) {
            if is_markdown_separator(lines[index]) {
                break;
            }
            let row = parse_markdown_row(lines[index]);
            if row.iter().any(|value| !value.trim().is_empty()) {
                rows.push(row);
            }
            index += 1;
        }

        tables.push(MarkdownTable { headers, rows });
    }

    tables
}

fn looks_like_markdown_row(line: &str) -> bool {
    line.contains('|')
}

fn is_markdown_separator(line: &str) -> bool {
    let parts = split_markdown_cells(line);
    if parts.is_empty() {
        return false;
    }

    parts.iter().all(|part| {
        let cell = part.trim();
        if cell.is_empty() {
            return false;
        }

        let inner = cell.trim_matches(':');
        !inner.is_empty() && inner.chars().all(|character| character == '-')
    })
}

fn parse_markdown_row(line: &str) -> Vec<String> {
    split_markdown_cells(line)
        .into_iter()
        .map(|cell| cell.replace("\\|", "|").trim().to_owned())
        .collect()
}

fn split_markdown_cells(line: &str) -> Vec<String> {
    let trimmed = line.trim();
    if trimmed.is_empty() {
        return Vec::new();
    }

    let without_prefix = trimmed.strip_prefix('|').unwrap_or(trimmed);
    let content = without_prefix
        .strip_suffix('|')
        .unwrap_or(without_prefix);

    content.split('|').map(str::to_owned).collect()
}

fn json_scope_to_rows(scope: JsonValue) -> Vec<Vec<(String, QueryValue)>> {
    match scope {
        JsonValue::Array(items) => items
            .into_iter()
            .map(json_item_to_row)
            .collect::<Vec<_>>(),
        JsonValue::Object(object) => vec![object
            .into_iter()
            .map(|(key, value)| (key, json_to_query_value(value)))
            .collect()],
        scalar => vec![vec![("value".to_owned(), json_to_query_value(scalar))]],
    }
}

fn json_item_to_row(item: JsonValue) -> Vec<(String, QueryValue)> {
    match item {
        JsonValue::Object(object) => object
            .into_iter()
            .map(|(key, value)| (key, json_to_query_value(value)))
            .collect(),
        scalar => vec![("value".to_owned(), json_to_query_value(scalar))],
    }
}

fn json_to_query_value(value: JsonValue) -> QueryValue {
    match value {
        JsonValue::Null => QueryValue::Null,
        JsonValue::Bool(flag) => QueryValue::Integer(i64::from(flag)),
        JsonValue::Number(number) => {
            if let Some(integer) = number.as_i64() {
                QueryValue::Integer(integer)
            } else if let Some(real) = number.as_f64() {
                QueryValue::Real(real)
            } else {
                QueryValue::Text(number.to_string())
            }
        }
        JsonValue::String(text) => parse_scalar_value(&text),
        JsonValue::Array(_) | JsonValue::Object(_) => QueryValue::Text(value.to_string()),
    }
}

fn normalize_headers(header_row: &[Data], width: usize) -> Vec<String> {
    let mut seen = std::collections::HashMap::new();

    (0..width)
        .map(|index| {
            let base = header_row
                .get(index)
                .map(cell_to_string)
                .filter(|value| !value.trim().is_empty())
                .unwrap_or_else(|| format!("column{}", index + 1));

            let count = seen
                .entry(base.clone())
                .and_modify(|count| *count += 1)
                .or_insert(1usize);

            if *count == 1 {
                base
            } else {
                format!("{base}_{count}")
            }
        })
        .collect()
}

fn convert_cell(cell: &Data) -> QueryValue {
    match cell {
        Data::Empty => QueryValue::Null,
        Data::Int(value) => QueryValue::Integer(*value),
        Data::Float(value) => QueryValue::Real(*value),
        Data::String(value) => QueryValue::Text(value.clone()),
        Data::Bool(value) => QueryValue::Integer(i64::from(*value)),
        Data::DateTime(value) => QueryValue::Text(value.to_string()),
        Data::DateTimeIso(value) => QueryValue::Text(value.clone()),
        Data::DurationIso(value) => QueryValue::Text(value.clone()),
        Data::Error(value) => QueryValue::Text(value.to_string()),
    }
}

fn cell_to_string(cell: &Data) -> String {
    match convert_cell(cell) {
        QueryValue::Null => String::new(),
        QueryValue::Integer(value) => value.to_string(),
        QueryValue::Real(value) => value.to_string(),
        QueryValue::Text(value) => value,
    }
}

fn register_sheet(
    connection: &Connection,
    sheet: &SheetData,
    table_name: &str,
    registered_views: &mut HashSet<String>,
) -> Result<()> {
    let columns = sheet
        .columns
        .iter()
        .map(|column| quote_identifier(column))
        .collect::<Vec<_>>()
        .join(", ");

    connection
        .execute(
            &format!("CREATE TABLE {} ({columns})", quote_identifier(table_name)),
            [],
        )
        .context("failed to create sheet table")?;

    let table1_key = normalize_view_key("table1");
    if table_name == "table" && !registered_views.contains(&table1_key) {
        connection
            .execute(
                &format!(
                    "CREATE VIEW {} AS SELECT * FROM {}",
                    quote_identifier("table1"),
                    quote_identifier("table")
                ),
                [],
            )
            .context("failed to register alias view table1 for first input")?;
        registered_views.insert(table1_key);
    }

    let sanitized_sheet_name = sanitize_table_name(&sheet.original_name);
    let sanitized_sheet_key = normalize_view_key(&sanitized_sheet_name);
    if sanitized_sheet_name != table_name && !registered_views.contains(&sanitized_sheet_key) {
        connection
            .execute(
                &format!(
                    "CREATE VIEW {} AS SELECT * FROM {}",
                    quote_identifier(&sanitized_sheet_name),
                    quote_identifier(table_name)
                ),
                [],
            )
            .with_context(|| {
                format!("failed to register view for sheet {}", sheet.original_name)
            })?;
        registered_views.insert(sanitized_sheet_key);
    }

    if sheet.rows.is_empty() {
        return Ok(());
    }

    let placeholders = vec!["?"; sheet.columns.len()].join(", ");
    let insert_sql = format!(
        "INSERT INTO {} VALUES ({placeholders})",
        quote_identifier(table_name)
    );
    let mut statement = connection
        .prepare(&insert_sql)
        .context("failed to prepare insert statement")?;

    for row in &sheet.rows {
        let values = row.iter().map(to_sql_value).collect::<Vec<_>>();
        statement
            .execute(params_from_iter(values))
            .context("failed to insert sheet row")?;
    }

    Ok(())
}

fn execute_query(connection: &Connection, query: &str, params: &[QueryParam]) -> Result<QueryResult> {
    let normalized_query = normalize_query_for_reserved_identifiers(query);
    let mut statement = connection
        .prepare(&normalized_query)
        .map_err(|error| enrich_query_prepare_error(connection, query, error))?;

    if statement.column_count() == 0 {
        bail!("query must return rows");
    }

    let columns = statement
        .column_names()
        .into_iter()
        .map(str::to_owned)
        .collect::<Vec<_>>();

    bind_query_params(&mut statement, params)?;

    let column_count = statement.column_count();
    let mut rows = statement.raw_query();
    let mut result_rows = Vec::new();

    while let Some(row) = rows.next().context("failed to fetch query row")? {
        let mut values = Vec::with_capacity(column_count);

        for index in 0..column_count {
            values.push(match row.get_ref(index)? {
                ValueRef::Null => QueryValue::Null,
                ValueRef::Integer(value) => QueryValue::Integer(value),
                ValueRef::Real(value) => QueryValue::Real(value),
                ValueRef::Text(value) => {
                    QueryValue::Text(String::from_utf8_lossy(value).into_owned())
                }
                ValueRef::Blob(value) => QueryValue::Text(format_blob(value)),
            });
        }

        result_rows.push(values);
    }

    Ok(QueryResult {
        columns,
        rows: result_rows,
    })
}

fn enrich_query_prepare_error(connection: &Connection, query: &str, error: rusqlite::Error) -> anyhow::Error {
    let raw_error = error.to_string();

    if let Some(table) = raw_error.strip_prefix("no such table: ").map(str::trim) {
        let table = simplify_sqlite_missing_identifier(table);
        let available_tables = list_loaded_table_names(connection);
        let available_suffix = if available_tables.is_empty() {
            String::new()
        } else {
            format!(" Available tables/views: {}.", available_tables.join(", "))
        };

        return anyhow!(
            "query references unknown table '{table}'.{available_suffix} Check your table names or run `qf tables --input ...` to inspect available tables.\nQuery: {query}"
        );
    }

    if let Some(column) = raw_error.strip_prefix("no such column: ").map(str::trim) {
        let column = simplify_sqlite_missing_identifier(column);
        return anyhow!(
            "query references unknown column '{column}'. Check your column names or run `qf schema --input ...` to inspect available columns.\nQuery: {query}"
        );
    }

    anyhow!(error).context(format!("failed to prepare query: {query}"))
}

fn simplify_sqlite_missing_identifier(identifier: &str) -> &str {
    identifier
        .split_once(" in ")
        .map(|(name, _)| name)
        .unwrap_or(identifier)
        .trim()
}

fn list_loaded_table_names(connection: &Connection) -> Vec<String> {
    let mut statement = match connection.prepare(
        "SELECT name FROM sqlite_master WHERE type IN ('table', 'view') AND name NOT LIKE 'sqlite_%' ORDER BY name",
    ) {
        Ok(statement) => statement,
        Err(_) => return Vec::new(),
    };

    let names = match statement.query_map([], |row| row.get::<_, String>(0)) {
        Ok(names) => names,
        Err(_) => return Vec::new(),
    };

    names.filter_map(|name| name.ok()).collect()
}

fn normalize_query_for_reserved_identifiers(query: &str) -> String {
    static RESERVED_TABLE_RE: OnceLock<Regex> = OnceLock::new();

    let re = RESERVED_TABLE_RE.get_or_init(|| {
        Regex::new(r"(?i)\b(from|join|update|into)\s+table\b")
            .expect("reserved table regex should compile")
    });

    re.replace_all(query, |captures: &regex::Captures<'_>| {
        format!("{} \"table\"", &captures[1])
    })
    .into_owned()
}

fn bind_query_params(statement: &mut rusqlite::Statement<'_>, params: &[QueryParam]) -> Result<()> {
    for param in params {
        let mut bound = false;

        for prefix in [":", "@", "$"] {
            let parameter_name = format!("{prefix}{}", param.name);
            if let Some(index) = statement
                .parameter_index(&parameter_name)
                .with_context(|| format!("failed to inspect parameter {parameter_name}"))?
            {
                statement
                    .raw_bind_parameter(index, to_sql_value(&param.value))
                    .with_context(|| format!("failed to bind parameter {parameter_name}"))?;
                bound = true;
                break;
            }
        }

        if !bound {
            bail!("query does not contain parameter :{}", param.name);
        }
    }

    Ok(())
}

fn to_sql_value(value: &QueryValue) -> Value {
    match value {
        QueryValue::Null => Value::Null,
        QueryValue::Integer(value) => Value::Integer(*value),
        QueryValue::Real(value) => Value::Real(*value),
        QueryValue::Text(value) => Value::Text(value.clone()),
    }
}

fn display_value(value: &QueryValue) -> String {
    match value {
        QueryValue::Null => String::new(),
        QueryValue::Integer(value) => value.to_string(),
        QueryValue::Real(value) => value.to_string(),
        QueryValue::Text(value) => value.clone(),
    }
}

fn escape_csv_field(value: &str) -> String {
    if value.contains([',', '"', '\n', '\r']) {
        format!("\"{}\"", value.replace('"', "\"\""))
    } else {
        value.to_owned()
    }
}

fn escape_json_string(value: &str) -> String {
    let mut escaped = String::from("\"");
    for character in value.chars() {
        match character {
            '"' => escaped.push_str("\\\""),
            '\\' => escaped.push_str("\\\\"),
            '\n' => escaped.push_str("\\n"),
            '\r' => escaped.push_str("\\r"),
            '\t' => escaped.push_str("\\t"),
            '\u{08}' => escaped.push_str("\\b"),
            '\u{0C}' => escaped.push_str("\\f"),
            c if c <= '\u{1F}' => {
                let _ = write!(&mut escaped, "\\u{:04x}", c as u32);
            }
            c => escaped.push(c),
        }
    }
    escaped.push('"');
    escaped
}

fn to_json_value(value: &QueryValue) -> String {
    match value {
        QueryValue::Null => "null".to_owned(),
        QueryValue::Integer(number) => number.to_string(),
        QueryValue::Real(number) if number.is_finite() => number.to_string(),
        QueryValue::Real(_) => "null".to_owned(),
        QueryValue::Text(text) => escape_json_string(text),
    }
}

fn escape_markdown_cell(value: &str) -> String {
    value.replace('|', "\\|").replace('\n', "<br>")
}

fn escape_xml_text(value: &str) -> String {
    value
        .replace('&', "&amp;")
        .replace('<', "&lt;")
        .replace('>', "&gt;")
        .replace('"', "&quot;")
        .replace('\'', "&apos;")
}

fn escape_xml_tag(value: &str) -> String {
    let sanitized = value
        .chars()
        .map(|character| {
            if character.is_alphanumeric() || character == '_' || character == '-' {
                character
            } else {
                '_'
            }
        })
        .collect::<String>();

    if sanitized.is_empty() {
        "element".to_owned()
    } else if sanitized.chars().next().unwrap_or('x').is_numeric() {
        format!("_{sanitized}")
    } else {
        sanitized
    }
}

fn format_blob(value: &[u8]) -> String {
    let mut formatted = String::from("0x");
    for byte in value {
        let _ = write!(&mut formatted, "{byte:02x}");
    }

    formatted
}

fn quote_identifier(identifier: &str) -> String {
    format!("\"{}\"", identifier.replace('"', "\"\""))
}

fn sanitize_table_name(name: &str) -> String {
    let sanitized = name
        .chars()
        .map(|character| {
            if character.is_alphanumeric() || character == '_' {
                character
            } else {
                '_'
            }
        })
        .collect::<String>()
        .trim_matches('_')
        .to_string();

    if sanitized.is_empty() {
        "table_view".to_owned()
    } else {
        sanitized
    }
}

fn normalize_view_key(name: &str) -> String {
    name.to_ascii_lowercase()
}

#[cfg(test)]
mod tests {
    use std::{
        fs,
        path::PathBuf,
        time::{SystemTime, UNIX_EPOCH},
    };

    use super::*;

    fn temp_path(name: &str) -> PathBuf {
        let unique = SystemTime::now()
            .duration_since(UNIX_EPOCH)
            .expect("time should move forward")
            .as_nanos();
        std::env::temp_dir().join(format!("query-forge-{name}-{unique}.xlsx"))
    }

    fn temp_xml_path(name: &str) -> PathBuf {
        let unique = SystemTime::now()
            .duration_since(UNIX_EPOCH)
            .expect("time should move forward")
            .as_nanos();
        std::env::temp_dir().join(format!("query-forge-{name}-{unique}.xml"))
    }

    fn temp_csv_path(name: &str) -> PathBuf {
        let unique = SystemTime::now()
            .duration_since(UNIX_EPOCH)
            .expect("time should move forward")
            .as_nanos();
        std::env::temp_dir().join(format!("query-forge-{name}-{unique}.csv"))
    }

    fn temp_jsonl_path(name: &str) -> PathBuf {
        let unique = SystemTime::now()
            .duration_since(UNIX_EPOCH)
            .expect("time should move forward")
            .as_nanos();
        std::env::temp_dir().join(format!("query-forge-{name}-{unique}.jsonl"))
    }

    fn temp_json_path(name: &str) -> PathBuf {
        let unique = SystemTime::now()
            .duration_since(UNIX_EPOCH)
            .expect("time should move forward")
            .as_nanos();
        std::env::temp_dir().join(format!("query-forge-{name}-{unique}.json"))
    }

    fn temp_markdown_path(name: &str) -> PathBuf {
        let unique = SystemTime::now()
            .duration_since(UNIX_EPOCH)
            .expect("time should move forward")
            .as_nanos();
        std::env::temp_dir().join(format!("query-forge-{name}-{unique}.md"))
    }

    fn write_test_workbook(path: &Path) -> Result<()> {
        let mut workbook = Workbook::new();
        let worksheet = workbook.add_worksheet();

        worksheet.write_string(0, 0, "name")?;
        worksheet.write_string(0, 1, "price")?;
        worksheet.write_string(0, 2, "active")?;
        worksheet.write_string(1, 0, "Keyboard")?;
        worksheet.write_number(1, 1, 12.5)?;
        worksheet.write_boolean(1, 2, true)?;
        worksheet.write_string(2, 0, "Cable")?;
        worksheet.write_number(2, 1, 5.0)?;
        worksheet.write_boolean(2, 2, false)?;

        workbook.save(path)?;
        Ok(())
    }

    fn write_test_workbook_on_sheet(path: &Path, sheet_name: &str) -> Result<()> {
        let mut workbook = Workbook::new();
        let worksheet = workbook.add_worksheet();
        worksheet.set_name(sheet_name)?;

        worksheet.write_string(0, 0, "name")?;
        worksheet.write_string(0, 1, "price")?;
        worksheet.write_string(1, 0, "Keyboard")?;
        worksheet.write_number(1, 1, 12.5)?;

        workbook.save(path)?;
        Ok(())
    }

    fn write_test_xml(path: &Path) -> Result<()> {
        fs::write(
            path,
            r#"<?xml version="1.0" encoding="UTF-8"?>
<data>
    <row>
        <name>Keyboard</name>
        <price>12.5</price>
        <active>true</active>
    </row>
    <row>
        <name>Cable</name>
        <price>5</price>
        <active>false</active>
    </row>
</data>
"#,
        )?;

        Ok(())
    }

    fn write_test_xml_with_sections(path: &Path) -> Result<()> {
        fs::write(
            path,
            r#"<?xml version="1.0" encoding="UTF-8"?>
<root>
    <Inventory>
        <row>
            <name>Keyboard</name>
            <price>12.5</price>
        </row>
    </Inventory>
    <Archive>
        <row>
            <name>Legacy Cable</name>
            <price>3.5</price>
        </row>
    </Archive>
</root>
"#,
        )?;

        Ok(())
    }

    fn write_test_csv(path: &Path) -> Result<()> {
        fs::write(
            path,
            "name,price,active\nKeyboard,12.5,true\nCable,5,false\n",
        )?;

        Ok(())
    }

    fn write_test_csv_no_headers(path: &Path) -> Result<()> {
        fs::write(path, "Keyboard,12.5,true\nCable,5,false\n")?;

        Ok(())
    }

    fn write_test_jsonl(path: &Path) -> Result<()> {
        fs::write(
            path,
            "{\"name\":\"Keyboard\",\"price\":12.5,\"active\":true}\n{\"name\":\"Cable\",\"price\":5,\"active\":false}\n",
        )?;

        Ok(())
    }

    fn write_test_json(path: &Path) -> Result<()> {
        fs::write(
            path,
            "[{\"name\":\"Keyboard\",\"price\":12.5,\"active\":true},{\"name\":\"Cable\",\"price\":5,\"active\":false}]",
        )?;

        Ok(())
    }

    fn write_test_json_with_sections(path: &Path) -> Result<()> {
        fs::write(
            path,
            "{\"Inventory\":[{\"name\":\"Keyboard\",\"price\":12.5}],\"Archive\":[{\"name\":\"Legacy Cable\",\"price\":3.5}]}",
        )?;

        Ok(())
    }

    fn write_test_markdown_with_tables(path: &Path) -> Result<()> {
        fs::write(
            path,
            r#"# Inventory Report

| name | price | active |
| --- | ---: | :---: |
| Keyboard | 12.5 | true |
| Cable | 5 | false |

## Archive

| name | price |
| --- | --- |
| Legacy Cable | 3.5 |
"#,
        )?;

        Ok(())
    }

    fn write_test_markdown_with_headers_only(path: &Path) -> Result<()> {
        fs::write(
            path,
            r#"| name | price |
| --- | --- |
"#,
        )?;

        Ok(())
    }

    #[test]
    fn normalizes_duplicate_and_blank_headers() {
        let headers = vec![
            Data::String("name".into()),
            Data::String(String::new()),
            Data::String("name".into()),
        ];

        assert_eq!(
            normalize_headers(&headers, 3),
            vec!["name", "column2", "name_2"]
        );
    }

    #[test]
    fn executes_sql_query_against_sheet() -> Result<()> {
        let workbook_path = temp_path("query");
        write_test_workbook(&workbook_path)?;

        let result = run_query(
            &workbook_path,
            Some("Sheet1"),
            "SELECT name, price FROM table WHERE price > 10 ORDER BY price DESC",
            true,
        )?;

        assert_eq!(result.columns, vec!["name", "price"]);
        assert_eq!(
            result.rows,
            vec![vec![
                QueryValue::Text("Keyboard".into()),
                QueryValue::Real(12.5)
            ]]
        );

        fs::remove_file(workbook_path)?;
        Ok(())
    }

    #[test]
    fn executes_sql_query_against_sheet1_alias() -> Result<()> {
        let workbook_path = temp_path("query-sheet1-alias");
        write_test_workbook(&workbook_path)?;

        let result = run_query(
            &workbook_path,
            Some("Sheet1"),
            "SELECT name, price FROM table1 WHERE price > 10 ORDER BY price DESC",
            true,
        )?;

        assert_eq!(result.columns, vec!["name", "price"]);
        assert_eq!(
            result.rows,
            vec![vec![
                QueryValue::Text("Keyboard".into()),
                QueryValue::Real(12.5)
            ]]
        );

        fs::remove_file(workbook_path)?;
        Ok(())
    }

    #[test]
    fn executes_sql_query_with_named_parameter() -> Result<()> {
        let workbook_path = temp_path("query-with-param");
        write_test_workbook(&workbook_path)?;

        let result = run_query_with_params(
            &workbook_path,
            Some("Sheet1"),
            "SELECT name, price FROM table WHERE price > :min_price ORDER BY price DESC",
            &[QueryParam {
                name: "min_price".to_owned(),
                value: QueryValue::Real(10.0),
            }],
            true,
        )?;

        assert_eq!(
            result,
            QueryResult {
                columns: vec!["name".to_owned(), "price".to_owned()],
                rows: vec![vec![
                    QueryValue::Text("Keyboard".to_owned()),
                    QueryValue::Real(12.5),
                ]],
            }
        );

        fs::remove_file(&workbook_path)?;
        Ok(())
    }

    #[test]
    fn executes_query_against_multiple_workbooks() -> Result<()> {
        let workbook_path_1 = temp_path("multi-1");
        let workbook_path_2 = temp_path("multi-2");
        write_test_workbook(&workbook_path_1)?;
        write_test_workbook(&workbook_path_2)?;

        let result = run_query_with_params_multi(
            &[workbook_path_1.as_path(), workbook_path_2.as_path()],
            Some("Sheet1"),
            "SELECT COUNT(*) AS total_rows FROM table UNION ALL SELECT COUNT(*) AS total_rows FROM table2",
            &[],
            true,
        )?;

        assert_eq!(result.columns, vec!["total_rows"]);
        assert_eq!(
            result.rows,
            vec![vec![QueryValue::Integer(2)], vec![QueryValue::Integer(2)]]
        );

        fs::remove_file(&workbook_path_1)?;
        fs::remove_file(&workbook_path_2)?;
        Ok(())
    }

    #[test]
    fn executes_query_against_multiple_workbooks_with_distinct_sheet_names() -> Result<()> {
        let workbook_path_1 = temp_path("multi-sheet-1");
        let workbook_path_2 = temp_path("multi-sheet-2");
        write_test_workbook_on_sheet(&workbook_path_1, "Consuntivo")?;
        write_test_workbook_on_sheet(&workbook_path_2, "WKL")?;

        let result = run_query_with_params_multi_inputs(
            &[
                WorkbookInput {
                    path: workbook_path_1.as_path(),
                    sheet_name: Some("Consuntivo"),
                    table_name: None,
                },
                WorkbookInput {
                    path: workbook_path_2.as_path(),
                    sheet_name: Some("WKL"),
                    table_name: None,
                },
            ],
            "SELECT COUNT(*) AS total_rows FROM table UNION ALL SELECT COUNT(*) AS total_rows FROM table2",
            &[],
            true,
        )?;

        assert_eq!(result.columns, vec!["total_rows"]);
        assert_eq!(
            result.rows,
            vec![vec![QueryValue::Integer(1)], vec![QueryValue::Integer(1)]]
        );

        fs::remove_file(&workbook_path_1)?;
        fs::remove_file(&workbook_path_2)?;
        Ok(())
    }

    #[test]
    fn executes_query_with_explicit_table_names() -> Result<()> {
        let sales_path = temp_path("explicit-name-sales");
        let costs_path = temp_path("explicit-name-costs");
        write_test_workbook_on_sheet(&sales_path, "Sheet1")?;
        write_test_workbook_on_sheet(&costs_path, "Sheet1")?;

        let result = run_query_with_params_multi_inputs(
            &[
                WorkbookInput {
                    path: sales_path.as_path(),
                    sheet_name: Some("Sheet1"),
                    table_name: Some("sales"),
                },
                WorkbookInput {
                    path: costs_path.as_path(),
                    sheet_name: Some("Sheet1"),
                    table_name: Some("costs"),
                },
            ],
            "SELECT COUNT(*) AS total_rows FROM sales UNION ALL SELECT COUNT(*) AS total_rows FROM costs",
            &[],
            true,
        )?;

        assert_eq!(result.columns, vec!["total_rows"]);
        assert_eq!(
            result.rows,
            vec![vec![QueryValue::Integer(1)], vec![QueryValue::Integer(1)]]
        );

        fs::remove_file(&sales_path)?;
        fs::remove_file(&costs_path)?;
        Ok(())
    }

    #[test]
    fn executes_sql_query_against_whole_xml_file() -> Result<()> {
        let xml_path = temp_xml_path("xml-whole");
        write_test_xml(&xml_path)?;

        let result = run_query(
            &xml_path,
            None,
            "SELECT name, price FROM table WHERE active = 1 ORDER BY price DESC",
            true,
        )?;

        assert_eq!(result.columns, vec!["name", "price"]);
        assert_eq!(
            result.rows,
            vec![vec![
                QueryValue::Text("Keyboard".into()),
                QueryValue::Real(12.5)
            ]]
        );

        fs::remove_file(xml_path)?;
        Ok(())
    }

    #[test]
    fn executes_sql_query_against_xml_sheet_tag() -> Result<()> {
        let xml_path = temp_xml_path("xml-sheet-tag");
        write_test_xml_with_sections(&xml_path)?;

        let result = run_query(
            &xml_path,
            Some("Archive"),
            "SELECT name, price FROM table",
            true,
        )?;

        assert_eq!(result.columns, vec!["name", "price"]);
        assert_eq!(
            result.rows,
            vec![vec![
                QueryValue::Text("Legacy Cable".into()),
                QueryValue::Real(3.5)
            ]]
        );

        fs::remove_file(xml_path)?;
        Ok(())
    }

    #[test]
    fn executes_query_against_heterogeneous_xlsx_and_xml_inputs() -> Result<()> {
        let workbook_path = temp_path("mixed-xlsx");
        let xml_path = temp_xml_path("mixed-xml");
        write_test_workbook(&workbook_path)?;
        write_test_xml(&xml_path)?;

        let result = run_query_with_params_multi_inputs(
            &[
                WorkbookInput {
                    path: workbook_path.as_path(),
                    sheet_name: Some("Sheet1"),
                    table_name: None,
                },
                WorkbookInput {
                    path: xml_path.as_path(),
                    sheet_name: None,
                    table_name: None,
                },
            ],
            "SELECT COUNT(*) AS total_rows FROM table UNION ALL SELECT COUNT(*) AS total_rows FROM table2",
            &[],
            true,
        )?;

        assert_eq!(result.columns, vec!["total_rows"]);
        assert_eq!(
            result.rows,
            vec![vec![QueryValue::Integer(2)], vec![QueryValue::Integer(2)]]
        );

        fs::remove_file(&workbook_path)?;
        fs::remove_file(&xml_path)?;
        Ok(())
    }

    #[test]
    fn executes_sql_query_against_csv_file() -> Result<()> {
        let csv_path = temp_csv_path("csv");
        write_test_csv(&csv_path)?;

        let result = run_query(
            &csv_path,
            None,
            "SELECT name, price FROM table WHERE active = 1 ORDER BY price DESC",
            true,
        )?;

        assert_eq!(result.columns, vec!["name", "price"]);
        assert_eq!(
            result.rows,
            vec![vec![
                QueryValue::Text("Keyboard".into()),
                QueryValue::Real(12.5)
            ]]
        );

        fs::remove_file(csv_path)?;
        Ok(())
    }

    #[test]
    fn executes_sql_query_against_csv_without_headers() -> Result<()> {
        let csv_path = temp_csv_path("csv-no-headers");
        write_test_csv_no_headers(&csv_path)?;

        let result = run_query(
            &csv_path,
            None,
            "SELECT column1, column2 FROM table WHERE column3 = 1 ORDER BY column2 DESC",
            false,
        )?;

        assert_eq!(result.columns, vec!["column1", "column2"]);
        assert_eq!(
            result.rows,
            vec![vec![
                QueryValue::Text("Keyboard".into()),
                QueryValue::Real(12.5)
            ]]
        );

        fs::remove_file(csv_path)?;
        Ok(())
    }

    #[test]
    fn executes_sql_query_against_jsonl_file() -> Result<()> {
        let jsonl_path = temp_jsonl_path("jsonl");
        write_test_jsonl(&jsonl_path)?;

        let result = run_query(
            &jsonl_path,
            None,
            "SELECT name, price FROM table WHERE active = 1 ORDER BY price DESC",
            true,
        )?;

        assert_eq!(result.columns, vec!["name", "price"]);
        assert_eq!(
            result.rows,
            vec![vec![
                QueryValue::Text("Keyboard".into()),
                QueryValue::Real(12.5)
            ]]
        );

        fs::remove_file(jsonl_path)?;
        Ok(())
    }

    #[test]
    fn executes_sql_query_against_json_file() -> Result<()> {
        let json_path = temp_json_path("json");
        write_test_json(&json_path)?;

        let result = run_query(
            &json_path,
            None,
            "SELECT name, price FROM table WHERE active = 1 ORDER BY price DESC",
            true,
        )?;

        assert_eq!(result.columns, vec!["name", "price"]);
        assert_eq!(
            result.rows,
            vec![vec![
                QueryValue::Text("Keyboard".into()),
                QueryValue::Real(12.5)
            ]]
        );

        fs::remove_file(json_path)?;
        Ok(())
    }

    #[test]
    fn executes_sql_query_against_json_sheet_key() -> Result<()> {
        let json_path = temp_json_path("json-sheet-key");
        write_test_json_with_sections(&json_path)?;

        let result = run_query(
            &json_path,
            Some("Archive"),
            "SELECT name, price FROM table",
            true,
        )?;

        assert_eq!(result.columns, vec!["name", "price"]);
        assert_eq!(
            result.rows,
            vec![vec![
                QueryValue::Text("Legacy Cable".into()),
                QueryValue::Real(3.5)
            ]]
        );

        fs::remove_file(json_path)?;
        Ok(())
    }

    #[test]
    fn executes_sql_query_against_first_markdown_table_by_default() -> Result<()> {
        let markdown_path = temp_markdown_path("markdown-default");
        write_test_markdown_with_tables(&markdown_path)?;

        let result = run_query(
            &markdown_path,
            None,
            "SELECT name, price FROM table WHERE active = 1",
            true,
        )?;

        assert_eq!(result.columns, vec!["name", "price"]);
        assert_eq!(
            result.rows,
            vec![vec![
                QueryValue::Text("Keyboard".into()),
                QueryValue::Real(12.5)
            ]]
        );

        fs::remove_file(markdown_path)?;
        Ok(())
    }

    #[test]
    fn reports_actionable_error_for_csv_selector() -> Result<()> {
        let csv_path = temp_csv_path("csv-selector-error");
        write_test_csv(&csv_path)?;

        let error = run_query(&csv_path, Some("Sheet1"), "SELECT name FROM table", true)
            .expect_err("CSV selector should fail");
        let message = error.to_string();
        assert!(message.contains("does not support selector 'Sheet1'"));
        assert!(message.contains("Remove ':Sheet1'"));

        fs::remove_file(csv_path)?;
        Ok(())
    }

    #[test]
    fn reports_actionable_error_for_jsonl_selector() -> Result<()> {
        let jsonl_path = temp_jsonl_path("jsonl-selector-error");
        write_test_jsonl(&jsonl_path)?;

        let error = run_query(
            &jsonl_path,
            Some("Records"),
            "SELECT name FROM table",
            true,
        )
        .expect_err("JSONL selector should fail");
        let message = error.to_string();
        assert!(message.contains("does not support selector 'Records'"));
        assert!(message.contains("Remove ':Records'"));

        fs::remove_file(jsonl_path)?;
        Ok(())
    }

    #[test]
    fn reports_json_key_error_with_available_keys() -> Result<()> {
        let json_path = temp_json_path("json-missing-key");
        write_test_json_with_sections(&json_path)?;

        let error = run_query(
            &json_path,
            Some("Missing"),
            "SELECT name FROM table",
            true,
        )
        .expect_err("missing JSON key should fail");
        let message = error.to_string();
        assert!(message.contains("JSON key 'Missing' not found"));
        assert!(message.contains("Available keys: Archive, Inventory"));

        fs::remove_file(json_path)?;
        Ok(())
    }

    #[test]
    fn reports_invalid_markdown_selector_with_guidance() -> Result<()> {
        let markdown_path = temp_markdown_path("markdown-invalid-selector");
        write_test_markdown_with_tables(&markdown_path)?;

        let error = run_query(
            &markdown_path,
            Some("abc"),
            "SELECT name FROM table",
            true,
        )
        .expect_err("non-numeric markdown selector should fail");
        let message = error.to_string();
        assert!(message.contains("invalid Markdown table selector 'abc'"));
        assert!(message.contains("':1'"));

        fs::remove_file(markdown_path)?;
        Ok(())
    }

    #[test]
    fn reports_empty_markdown_table_as_actionable_error() -> Result<()> {
        let markdown_path = temp_markdown_path("markdown-empty-table");
        write_test_markdown_with_headers_only(&markdown_path)?;

        let error = run_query(&markdown_path, None, "SELECT name FROM table", true)
            .expect_err("empty markdown table should fail");
        let message = error.to_string();
        assert!(message.contains("is empty (no data rows)"));

        fs::remove_file(markdown_path)?;
        Ok(())
    }

    #[test]
    fn reports_unknown_table_with_inspection_hint() -> Result<()> {
        let workbook_path = temp_path("unknown-table-query");
        write_test_workbook(&workbook_path)?;

        let error = run_query(&workbook_path, Some("Sheet1"), "SELECT * FROM missing_table", true)
            .expect_err("unknown table should fail");
        let message = error.to_string();
        assert!(message.contains("unknown table 'missing_table'"));
        assert!(message.contains("qf tables --input"));

        fs::remove_file(workbook_path)?;
        Ok(())
    }

    #[test]
    fn reports_unknown_column_with_schema_hint() -> Result<()> {
        let workbook_path = temp_path("unknown-column-query");
        write_test_workbook(&workbook_path)?;

        let error = run_query(
            &workbook_path,
            Some("Sheet1"),
            "SELECT missing_column FROM table",
            true,
        )
        .expect_err("unknown column should fail");
        let message = error.to_string();
        assert!(message.contains("unknown column 'missing_column'"));
        assert!(message.contains("qf schema --input"));

        fs::remove_file(workbook_path)?;
        Ok(())
    }

    #[test]
    fn executes_sql_query_against_markdown_table_by_numeric_key() -> Result<()> {
        let markdown_path = temp_markdown_path("markdown-key");
        write_test_markdown_with_tables(&markdown_path)?;

        let result = run_query(
            &markdown_path,
            Some("2"),
            "SELECT name, price FROM table",
            true,
        )?;

        assert_eq!(result.columns, vec!["name", "price"]);
        assert_eq!(
            result.rows,
            vec![vec![
                QueryValue::Text("Legacy Cable".into()),
                QueryValue::Real(3.5)
            ]]
        );

        fs::remove_file(markdown_path)?;
        Ok(())
    }

    #[test]
    fn executes_query_against_heterogeneous_xlsx_xml_csv_jsonl_json_markdown_inputs() -> Result<()> {
        let workbook_path = temp_path("mixed4-xlsx");
        let xml_path = temp_xml_path("mixed4-xml");
        let csv_path = temp_csv_path("mixed4-csv");
        let jsonl_path = temp_jsonl_path("mixed4-jsonl");
        let json_path = temp_json_path("mixed4-json");
        let markdown_path = temp_markdown_path("mixed4-markdown");
        write_test_workbook(&workbook_path)?;
        write_test_xml(&xml_path)?;
        write_test_csv(&csv_path)?;
        write_test_jsonl(&jsonl_path)?;
        write_test_json(&json_path)?;
        write_test_markdown_with_tables(&markdown_path)?;

        let result = run_query_with_params_multi_inputs(
            &[
                WorkbookInput {
                    path: workbook_path.as_path(),
                    sheet_name: Some("Sheet1"),
                    table_name: None,
                },
                WorkbookInput {
                    path: xml_path.as_path(),
                    sheet_name: None,
                    table_name: None,
                },
                WorkbookInput {
                    path: csv_path.as_path(),
                    sheet_name: None,
                    table_name: None,
                },
                WorkbookInput {
                    path: jsonl_path.as_path(),
                    sheet_name: None,
                    table_name: None,
                },
                WorkbookInput {
                    path: json_path.as_path(),
                    sheet_name: None,
                    table_name: None,
                },
                WorkbookInput {
                    path: markdown_path.as_path(),
                    sheet_name: None,
                    table_name: None,
                },
            ],
            "SELECT COUNT(*) AS total_rows FROM table UNION ALL SELECT COUNT(*) AS total_rows FROM table2 UNION ALL SELECT COUNT(*) AS total_rows FROM table3 UNION ALL SELECT COUNT(*) AS total_rows FROM table4 UNION ALL SELECT COUNT(*) AS total_rows FROM table5 UNION ALL SELECT COUNT(*) AS total_rows FROM table6",
            &[],
            true,
        )?;

        assert_eq!(result.columns, vec!["total_rows"]);
        assert_eq!(
            result.rows,
            vec![
                vec![QueryValue::Integer(2)],
                vec![QueryValue::Integer(2)],
                vec![QueryValue::Integer(2)],
                vec![QueryValue::Integer(2)],
                vec![QueryValue::Integer(2)],
                vec![QueryValue::Integer(2)]
            ]
        );

        fs::remove_file(&workbook_path)?;
        fs::remove_file(&xml_path)?;
        fs::remove_file(&csv_path)?;
        fs::remove_file(&jsonl_path)?;
        fs::remove_file(&json_path)?;
        fs::remove_file(&markdown_path)?;
        Ok(())
    }

    #[test]
    fn writes_query_result_to_xlsx() -> Result<()> {
        let output_path = temp_path("output");
        let result = QueryResult {
            columns: vec!["item".into(), "total".into()],
            rows: vec![vec![
                QueryValue::Text("Mouse".into()),
                QueryValue::Integer(3),
            ]],
        };

        write_xlsx(&result, &output_path)?;

        let written = run_query(
            &output_path,
            Some("Sheet1"),
            "SELECT item, total FROM table",
            true,
        )?;

        assert_eq!(written.columns, vec!["item", "total"]);
        assert_eq!(
            written.rows,
            vec![vec![
                QueryValue::Text("Mouse".into()),
                QueryValue::Real(3.0)
            ]]
        );

        fs::remove_file(output_path)?;
        Ok(())
    }

    #[test]
    fn renders_csv_with_escaping() {
        let result = QueryResult {
            columns: vec!["name".into(), "notes".into()],
            rows: vec![vec![
                QueryValue::Text("Mouse".into()),
                QueryValue::Text("line1,line2".into()),
            ]],
        };

        assert_eq!(render_csv(&result), "name,notes\nMouse,\"line1,line2\"");
    }

    #[test]
    fn renders_text_with_aligned_columns() {
        let result = QueryResult {
            columns: vec!["mese".into(), "totale_ore".into()],
            rows: vec![
                vec![QueryValue::Text("2026-01-01".into()), QueryValue::Real(10.5)],
                vec![QueryValue::Text("2026-12-01".into()), QueryValue::Integer(2)],
            ],
        };

        assert_eq!(
            render_text(&result),
            "mese       | totale_ore\n-----------+-----------\n2026-01-01 | 10.5      \n2026-12-01 | 2         "
        );
    }

    #[test]
    fn renders_jsonl() {
        let result = QueryResult {
            columns: vec!["item".into(), "stock".into(), "note".into()],
            rows: vec![vec![
                QueryValue::Text("Desk".into()),
                QueryValue::Integer(8),
                QueryValue::Null,
            ]],
        };

        assert_eq!(
            render_jsonl(&result),
            "{\"item\":\"Desk\",\"stock\":8,\"note\":null}"
        );
    }

    #[test]
    fn renders_markdown() {
        let result = QueryResult {
            columns: vec!["category".into(), "total".into()],
            rows: vec![vec![
                QueryValue::Text("electronics".into()),
                QueryValue::Integer(47),
            ]],
        };

        assert_eq!(
            render_markdown(&result),
            "| category | total |\n| --- | --- |\n| electronics | 47 |"
        );
    }
}