use std::path::PathBuf;

use clap::{ArgAction, ArgGroup, Args, Parser, Subcommand, ValueEnum};

#[derive(Debug, Parser)]
#[command(
    author,
    version,
    about = "Query XLSX/XML/CSV/JSON/JSONL/Markdown/Parquet datasets with SQL"
)]
pub(crate) struct Cli {
    #[command(subcommand)]
    pub(crate) command: Commands,
}

#[derive(Debug, Subcommand)]
pub(crate) enum Commands {
    /// Execute a SQL query against one or more input files.
    #[command(after_long_help = "Examples:\n\
    qf query --input ./input.xlsx:Sheet1 --sql \"SELECT * FROM table\"\n\
    qf query --input ./inventory.xml:Inventory --sql \"SELECT product, price FROM table WHERE active = 1\"\n\
    qf query --input ./inventory.csv --sql \"SELECT product, price FROM table WHERE active = 1\"\n\
    qf query --input ./inventory.json:Inventory --sql \"SELECT product, price FROM table WHERE active = 1\"\n\
    qf query --input ./inventory.jsonl --sql \"SELECT product, price FROM table WHERE active = 1\"\n\
    qf query --input ./a.xlsx:Sheet1 --input ./b.csv --sql \"SELECT * FROM table UNION ALL SELECT * FROM table2\"\n\
    qf query --input sales=./sales.xlsx:Q1 --input costs=./costs.csv --sql \"SELECT * FROM sales JOIN costs ON sales.id = costs.id\"\n\
    qf query --input ./inventory.md --sql \"SELECT product, price FROM table WHERE active = 1\"\n\
    qf query --input ./inventory.md:2 --sql \"SELECT product, price FROM table\"\n\
    qf query --input ./input.xlsx:Sheet1 --sql-file ./query.sql --output ./result.csv --format csv\n\
    qf query --input ./summary.json --sql \"SELECT key, value FROM table\" --json-mode object\n\
    qf query --input ./record.json --sql \"SELECT \\\"user.name\\\", \\\"address.city\\\" FROM table\" --json-mode flatten\n\
    qf query --input ./config.xml --sql \"SELECT tag, value FROM table WHERE tag = 'timeout'\" --xml-mode descendants\n\
    qf query --input ./items.xml --sql \"SELECT id, type, value FROM table\" --xml-mode attributes\n\
\n\
Query source:\n\
    Provide SQL inline with --sql or load it from disk with --sql-file.\n\
\n\
Table naming:\n\
    Without explicit names, inputs become table, table2, table3, ...\n\
    Prefix inputs as NAME=PATH to use readable table names in SQL.\n\
\n\
JSON extraction modes (--json-mode):\n\
    array (default): each element of a top-level array becomes a row.\n\
    object: each key-value pair of a top-level object becomes a row with 'key' and 'value' columns.\n\
    flatten: recursively flattens nested objects/arrays into a single row using dot-separated column names.\n\
\n\
XML extraction modes (--xml-mode):\n\
    rows (default): detect and extract tabular rows from the XML structure.\n\
    descendants: collect every leaf text element as a row with 'tag' and 'value' columns.\n\
    attributes: collect elements with attributes; each attribute name becomes a column.")]
    Query(QueryCommand),

    /// List logical SQL tables generated from inputs.
    #[command(after_long_help = "Examples:\n\
    qf tables --input sales=./sales.xlsx:Q1 --input costs=./costs.csv\n\
    qf tables --input ./inventory.json:Inventory --format json\n\
    qf tables --input ./inventory.xml:Inventory --input ./inventory.csv --format markdown\n\
Output:\n\
    Lists the logical SQL table names created from your inputs, in input order.\n\
    Use this command before writing queries when you want to confirm automatic table names.")]
    Tables(TablesCommand),

    /// Show per-table column names and inferred SQL-compatible types.
    #[command(after_long_help = "Examples:\n\
    qf schema --input ./inventory.json:Inventory\n\
    qf schema --input sales=./sales.xlsx:Q1 --input costs=./costs.csv --format json\n\
    qf schema --input ./inventory.xml:Inventory --format markdown\n\
Output:\n\
    Shows one row per column with the logical table name and inferred SQL-compatible type.\n\
    Use this command to verify names and types before writing filters, joins, or casts.")]
    Schema(SchemaCommand),

    /// Return a compact diagnostic summary (tables, row counts, column counts, null density, and sample rows).
    #[command(after_long_help = "Examples:\n\
    qf inspect --input ./inventory.md:2 --sample 5\n\
    qf inspect --input sales=./sales.xlsx:Q1 --input costs=./costs.csv --format json\n\
    qf inspect --input ./inventory.csv --stats --format markdown\n\
Output:\n\
    Includes a compact summary for each table, sampled rows, and optional extra metrics.\n\
    Use this command when you need fast diagnostics before or after adjusting normalization flags.")]
    Inspect(InspectCommand),
}

#[derive(Debug, Args)]
#[command(group(
    ArgGroup::new("query_source")
        .required(true)
        .args(["sql", "sql_file"])
))]
pub(crate) struct QueryCommand {
    #[arg(
        short,
        long,
        required = true,
        action = ArgAction::Append,
        value_name = "[NAME=]PATH[:SHEET|KEY]",
        long_help = "Input dataset path. Supported formats: .xlsx, .xml, .csv, .json, .jsonl, .md, .markdown, .parquet.\n\
Optionally prefix with a table name: NAME=PATH (e.g. sales=./sales.xlsx:Q1).\n\
Without a name prefix, tables are named 'table', 'table2', 'table3', ...\n\
For XLSX use PATH:SheetName (or PATH#SheetName) to select a worksheet.\n\
For XML use PATH:TagName (or PATH#TagName) to select a subtree; without sheet/tag the whole file is used.\n\
For JSON use PATH:Key (or PATH#Key) to select a top-level key; without key the whole JSON root is used.\n\
For Markdown use PATH:N (or PATH#N), where N is the 1-based table number; without key the first table is used.\n\
    CSV, JSONL and Parquet do not support sheet/key selection."
    )]
    pub(crate) input: Vec<String>,
    #[arg(
        long,
        alias = "query",
        short = 'q',
        conflicts_with = "sql_file",
        value_name = "SQL",
        long_help = "SQL query to execute. Use 'table', 'table2', 'table3', ... or explicit table names.\n\
The flag --query is also accepted as a legacy alias."
    )]
    pub(crate) sql: Option<String>,
    #[arg(
        long = "sql-file",
        value_name = "PATH",
        conflicts_with = "sql",
        long_help = "Path to a file containing the SQL query to execute.\n\
The file is read as UTF-8 text and trimmed before execution."
    )]
    pub(crate) sql_file: Option<PathBuf>,
    #[arg(
        long = "infer-types",
        conflicts_with = "all_text",
        long_help = "Infer typed SQLite values from input data when possible.\n\
This is useful for numeric comparisons, boolean filtering, and date-aware ingestion."
    )]
    pub(crate) infer_types: bool,
    #[arg(
        long = "all-text",
        conflicts_with = "infer_types",
        long_help = "Load all incoming values as text instead of inferring typed SQLite values.\n\
Use this when you want exact string preservation or need to avoid automatic conversions."
    )]
    pub(crate) all_text: bool,
    #[arg(
        long = "decimal-comma",
        long_help = "Interpret commas as decimal separators during type inference.\n\
Useful for locale-formatted values such as 12,50."
    )]
    pub(crate) decimal_comma: bool,
    #[arg(
        long = "date-format",
        value_name = "STRFTIME",
        long_help = "Expected date format used during type inference, for example %d/%m/%Y."
    )]
    pub(crate) date_format: Option<String>,
    #[arg(
        long = "null-values",
        value_delimiter = ',',
        action = ArgAction::Append,
        value_name = "VALUE[,VALUE...]",
        long_help = "Additional strings to interpret as NULL during type inference.\n\
Repeat the flag or pass comma-separated values."
    )]
    pub(crate) null_values: Vec<String>,
    #[arg(
        long = "true-values",
        value_delimiter = ',',
        action = ArgAction::Append,
        value_name = "VALUE[,VALUE...]",
        long_help = "Additional strings to interpret as boolean true during type inference.\n\
Repeat the flag or pass comma-separated values."
    )]
    pub(crate) true_values: Vec<String>,
    #[arg(
        long = "false-values",
        value_delimiter = ',',
        action = ArgAction::Append,
        value_name = "VALUE[,VALUE...]",
        long_help = "Additional strings to interpret as boolean false during type inference.\n\
Repeat the flag or pass comma-separated values."
    )]
    pub(crate) false_values: Vec<String>,
    #[arg(
        long = "trim",
        long_help = "Trim surrounding whitespace from every cell before loading it."
    )]
    pub(crate) trim: bool,
    #[arg(
        long = "skip-empty-rows",
        long_help = "Discard rows whose values are entirely empty after normalization."
    )]
    pub(crate) skip_empty_rows: bool,
    #[arg(
        long = "normalize-headers",
        long_help = "Normalize header names before loading them into SQLite.\n\
Combine with --header-case and --dedupe-headers for more stable schemas."
    )]
    pub(crate) normalize_headers: bool,
    #[arg(
        long = "header-case",
        value_enum,
        requires = "normalize_headers",
        long_help = "Header naming style to apply after normalization.\n\
Requires --normalize-headers."
    )]
    pub(crate) header_case: Option<HeaderCase>,
    #[arg(
        long = "dedupe-headers",
        long_help = "Make duplicate normalized headers unique by appending numeric suffixes."
    )]
    pub(crate) dedupe_headers: bool,
    #[arg(
        long = "param",
        value_name = "NAME=VALUE",
        action = ArgAction::Append,
        long_help = "Bind a named SQL parameter.\n\
Repeat the flag for multiple parameters; values are parsed as null, booleans, integers, reals, or text."
    )]
    pub(crate) params: Vec<String>,
    #[arg(
        short,
        long,
        value_name = "PATH",
        long_help = "Write query results to a file instead of stdout.\n\
Pair with --format to choose the serialization format when the extension is ambiguous."
    )]
    pub(crate) output: Option<PathBuf>,
    #[arg(
        long,
        value_enum,
        long_help = "Output format for query results.\n\
Supported values: text, csv, json, jsonl, markdown, xlsx, xml, parquet."
    )]
    pub(crate) format: Option<OutputFormat>,
    #[arg(
        long = "no-headers",
        long_help = "Treat the first row as data instead of column headers.\n\
Generated column names follow the pattern column1, column2, ..."
    )]
    pub(crate) no_headers: bool,
    #[arg(
        long = "json-mode",
        value_enum,
        long_help = "JSON extraction strategy.\n\
'array' (default): each element of a top-level JSON array becomes a row.\n\
'object': each key-value pair of a JSON object becomes a row with 'key' and 'value' columns.\n\
'flatten': recursively flatten nested JSON objects and arrays using dotted key paths."
    )]
    pub(crate) json_mode: Option<JsonMode>,
    #[arg(
        long = "xml-mode",
        value_enum,
        long_help = "XML extraction strategy.\n\
'rows' (default): detect and extract tabular rows from the XML structure.\n\
'descendants': collect every leaf text element as a row with 'tag' and 'value' columns.\n\
'attributes': extract element attributes as columns; each element with attributes becomes a row."
    )]
    pub(crate) xml_mode: Option<XmlMode>,
    #[arg(
        long,
        long_help = "Print query execution metadata to stderr after running the query.\n\
Includes row count, output column names, input tables loaded, and execution time.\n\
Use --meta-format to choose between text (default) and json output."
    )]
    pub(crate) meta: bool,
    #[arg(
        long = "meta-format",
        value_enum,
        requires = "meta",
        long_help = "Format for metadata output printed to stderr.\n\
Supported values: text (default), json.\n\
Requires --meta."
    )]
    pub(crate) meta_format: Option<MetadataFormat>,
}

#[derive(Debug, Args)]
pub(crate) struct TablesCommand {
    #[arg(
        short,
        long,
        required = true,
        action = ArgAction::Append,
        value_name = "[NAME=]PATH[:SHEET|KEY]",
        long_help = "Input dataset path. Supported formats: .xlsx, .xml, .csv, .json, .jsonl, .md, .markdown, .parquet.\n\
Optionally prefix with a table name: NAME=PATH (e.g. sales=./sales.xlsx:Q1).\n\
Without a name prefix, tables are named 'table', 'table2', 'table3', ...\n\
For XLSX use PATH:SheetName to select a worksheet.\n\
For XML use PATH:TagName to select a subtree.\n\
For JSON use PATH:Key to select a top-level key.\n\
For Markdown use PATH:N for the 1-based table index."
    )]
    pub(crate) input: Vec<String>,
    #[arg(
        short,
        long,
        value_name = "SELECTOR",
        long_help = "Default sheet/tag/key applied to every --input that does not already specify one."
    )]
    pub(crate) sheet: Option<String>,
    #[arg(
        long,
        value_enum,
        default_value = "text",
        long_help = "Output format for table discovery results. Supported values: text, json, markdown."
    )]
    pub(crate) format: InspectionFormat,
}

#[derive(Debug, Args)]
pub(crate) struct SchemaCommand {
    #[arg(
        short,
        long,
        required = true,
        action = ArgAction::Append,
        value_name = "[NAME=]PATH[:SHEET|KEY]",
        long_help = "Input dataset path. Supported formats: .xlsx, .xml, .csv, .json, .jsonl, .md, .markdown, .parquet.\n\
Optionally prefix with a table name: NAME=PATH (e.g. sales=./sales.xlsx:Q1).\n\
Without a name prefix, tables are named 'table', 'table2', 'table3', ...\n\
For XLSX use PATH:SheetName to select a worksheet.\n\
For XML use PATH:TagName to select a subtree.\n\
For JSON use PATH:Key to select a top-level key.\n\
For Markdown use PATH:N for the 1-based table index."
    )]
    pub(crate) input: Vec<String>,
    #[arg(
        short,
        long,
        value_name = "SELECTOR",
        long_help = "Default sheet/tag/key applied to every --input that does not already specify one."
    )]
    pub(crate) sheet: Option<String>,
    #[arg(
        long,
        value_enum,
        default_value = "text",
        long_help = "Output format for schema results. Supported values: text, json, markdown."
    )]
    pub(crate) format: InspectionFormat,
}

#[derive(Debug, Args)]
pub(crate) struct InspectCommand {
    #[arg(
        short,
        long,
        required = true,
        action = ArgAction::Append,
        value_name = "[NAME=]PATH[:SHEET|KEY]",
        long_help = "Input dataset path. Supported formats: .xlsx, .xml, .csv, .json, .jsonl, .md, .markdown, .parquet.\n\
Optionally prefix with a table name: NAME=PATH (e.g. sales=./sales.xlsx:Q1).\n\
Without a name prefix, tables are named 'table', 'table2', 'table3', ...\n\
For XLSX use PATH:SheetName to select a worksheet.\n\
For XML use PATH:TagName to select a subtree.\n\
For JSON use PATH:Key to select a top-level key.\n\
For Markdown use PATH:N for the 1-based table index."
    )]
    pub(crate) input: Vec<String>,
    #[arg(
        short,
        long,
        value_name = "SELECTOR",
        long_help = "Default sheet/tag/key applied to every --input that does not already specify one."
    )]
    pub(crate) sheet: Option<String>,
    #[arg(
        long,
        value_enum,
        default_value = "text",
        long_help = "Output format for inspection results. Supported values: text, json, markdown."
    )]
    pub(crate) format: InspectionFormat,
    /// Number of sample rows to include per table (default: 5).
    #[arg(
        long,
        default_value = "5",
        long_help = "Number of sample rows to include per table in the inspection output."
    )]
    pub(crate) sample: usize,
    /// Enable additional metrics (distinct count, min/max for numeric columns).
    #[arg(
        long,
        long_help = "Include additional metrics such as distinct counts and numeric min/max values when available."
    )]
    pub(crate) stats: bool,
}

#[derive(Clone, Debug, ValueEnum)]
pub(crate) enum OutputFormat {
    Text,
    Csv,
    Json,
    Jsonl,
    Markdown,
    Xlsx,
    Xml,
    Parquet,
}

#[derive(Clone, Debug, ValueEnum)]
pub(crate) enum InspectionFormat {
    Text,
    Json,
    Markdown,
}

#[derive(Clone, Debug, ValueEnum)]
pub(crate) enum MetadataFormat {
    /// Human-readable text output (default).
    Text,
    /// Machine-readable JSON output.
    Json,
}

#[derive(Clone, Debug, ValueEnum)]
pub(crate) enum HeaderCase {
    Snake,
}

/// JSON extraction mode for `--json-mode`.
#[derive(Clone, Debug, ValueEnum)]
pub(crate) enum JsonMode {
    /// Each element of a top-level JSON array becomes a row (default).
    Array,
    /// Each key-value pair of the JSON object becomes a row with "key" and "value" columns.
    Object,
    /// Recursively flatten nested JSON into dotted key paths (e.g. address.city).
    Flatten,
}

/// XML extraction mode for `--xml-mode`.
#[derive(Clone, Debug, ValueEnum)]
pub(crate) enum XmlMode {
    /// Detect and extract tabular rows from the XML structure (default).
    Rows,
    /// Collect every leaf text element as a row with "tag" and "value" columns.
    Descendants,
    /// Extract element attributes as columns; each element with attributes becomes a row.
    Attributes,
}

#[cfg(test)]
mod tests {
    use clap::Parser;

    use super::{Cli, Commands};

    #[test]
    fn parses_configurable_type_inference_flags() {
        let cli = Cli::parse_from([
            "qf",
            "query",
            "--input",
            "data.csv",
            "--sql",
            "SELECT * FROM table",
            "--all-text",
            "--decimal-comma",
            "--date-format",
            "%d/%m/%Y",
            "--null-values",
            "NULL,N/A",
            "--true-values",
            "YES,Y",
            "--false-values",
            "NO,N",
        ]);

        let Commands::Query(cmd) = cli.command else {
            panic!("expected query command");
        };

        assert!(cmd.all_text);
        assert!(cmd.decimal_comma);
        assert_eq!(cmd.date_format.as_deref(), Some("%d/%m/%Y"));
        assert_eq!(cmd.null_values, vec!["NULL", "N/A"]);
        assert_eq!(cmd.true_values, vec!["YES", "Y"]);
        assert_eq!(cmd.false_values, vec!["NO", "N"]);
    }

    #[test]
    fn parses_input_normalization_flags() {
        let cli = Cli::parse_from([
            "qf",
            "query",
            "--input",
            "data.csv",
            "--sql",
            "SELECT * FROM table",
            "--trim",
            "--skip-empty-rows",
            "--normalize-headers",
            "--header-case",
            "snake",
            "--dedupe-headers",
        ]);

        let Commands::Query(cmd) = cli.command else {
            panic!("expected query command");
        };

        assert!(cmd.trim);
        assert!(cmd.skip_empty_rows);
        assert!(cmd.normalize_headers);
        assert!(matches!(cmd.header_case, Some(super::HeaderCase::Snake)));
        assert!(cmd.dedupe_headers);
    }

    #[test]
    fn rejects_infer_types_and_all_text_together() {
        let parsed = Cli::try_parse_from([
            "qf",
            "query",
            "--input",
            "data.csv",
            "--sql",
            "SELECT * FROM table",
            "--infer-types",
            "--all-text",
        ]);

        assert!(parsed.is_err());
    }

    #[test]
    fn parses_sql_file_flag() {
        let cli = Cli::parse_from([
            "qf",
            "query",
            "--input",
            "data.csv",
            "--sql-file",
            "query.sql",
        ]);

        let Commands::Query(cmd) = cli.command else {
            panic!("expected query command");
        };

        assert_eq!(cmd.sql_file.as_deref(), Some(std::path::Path::new("query.sql")));
    }

    #[test]
    fn parses_json_mode_flag() {
        let cli = Cli::parse_from([
            "qf",
            "query",
            "--input",
            "data.json",
            "--sql",
            "SELECT * FROM table",
            "--json-mode",
            "flatten",
        ]);

        let Commands::Query(cmd) = cli.command else {
            panic!("expected query command");
        };

        assert!(matches!(cmd.json_mode, Some(super::JsonMode::Flatten)));
    }

    #[test]
    fn parses_xml_mode_flag() {
        let cli = Cli::parse_from([
            "qf",
            "query",
            "--input",
            "data.xml",
            "--sql",
            "SELECT * FROM table",
            "--xml-mode",
            "attributes",
        ]);

        let Commands::Query(cmd) = cli.command else {
            panic!("expected query command");
        };

        assert!(matches!(cmd.xml_mode, Some(super::XmlMode::Attributes)));
    }

    #[test]
    fn json_mode_defaults_to_none_when_not_specified() {
        let cli = Cli::parse_from([
            "qf",
            "query",
            "--input",
            "data.json",
            "--sql",
            "SELECT * FROM table",
        ]);

        let Commands::Query(cmd) = cli.command else {
            panic!("expected query command");
        };

        assert!(cmd.json_mode.is_none());
        assert!(cmd.xml_mode.is_none());
    }

    #[test]
    fn parses_meta_flag() {
        let cli = Cli::parse_from([
            "qf",
            "query",
            "--input",
            "data.csv",
            "--sql",
            "SELECT * FROM table",
            "--meta",
        ]);

        let Commands::Query(cmd) = cli.command else {
            panic!("expected query command");
        };

        assert!(cmd.meta);
        assert!(cmd.meta_format.is_none());
    }

    #[test]
    fn parses_meta_with_json_format() {
        let cli = Cli::parse_from([
            "qf",
            "query",
            "--input",
            "data.csv",
            "--sql",
            "SELECT * FROM table",
            "--meta",
            "--meta-format",
            "json",
        ]);

        let Commands::Query(cmd) = cli.command else {
            panic!("expected query command");
        };

        assert!(cmd.meta);
        assert!(matches!(cmd.meta_format, Some(super::MetadataFormat::Json)));
    }

    #[test]
    fn parses_meta_with_text_format() {
        let cli = Cli::parse_from([
            "qf",
            "query",
            "--input",
            "data.csv",
            "--sql",
            "SELECT * FROM table",
            "--meta",
            "--meta-format",
            "text",
        ]);

        let Commands::Query(cmd) = cli.command else {
            panic!("expected query command");
        };

        assert!(cmd.meta);
        assert!(matches!(cmd.meta_format, Some(super::MetadataFormat::Text)));
    }

    #[test]
    fn meta_flag_is_false_when_not_specified() {
        let cli = Cli::parse_from([
            "qf",
            "query",
            "--input",
            "data.csv",
            "--sql",
            "SELECT * FROM table",
        ]);

        let Commands::Query(cmd) = cli.command else {
            panic!("expected query command");
        };

        assert!(!cmd.meta);
        assert!(cmd.meta_format.is_none());
    }
}
