lodviz_core 0.3.0

//! Column type inference for CSV data
//!
//! Automatically detects column types (Quantitative, Temporal, Nominal, Ordinal)
//! from sample data using pattern matching and heuristics.

use std::collections::HashSet;

use super::data::DataType;
use super::field_value::{DataRow, DataTable, FieldValue};

// ── Public API ────────────────────────────────────────────────────────────────

/// Metadata inferito per una singola colonna
#[derive(Debug, Clone, serde::Serialize, serde::Deserialize)]
pub struct InferredColumnType {
    /// Come è memorizzato (Numeric, Text, Timestamp, Boolean)
    pub storage_type: FieldValueType,

    /// Cosa rappresenta (Quantitative, Temporal, Nominal, Ordinal)
    pub semantic_type: DataType,

    /// Confidence 0.0-1.0 (basato su % match pattern)
    pub confidence: f64,

    /// Numero valori distinti nel sample
    pub cardinality: usize,

    /// Numero valori null nel sample
    pub null_count: usize,

    /// Righe ispezionate
    pub sample_size: usize,

    /// Metadata aggiuntivo (is_integer, date_format, ecc.)
    pub metadata: TypeMetadata,
}

/// Storage type (come è memorizzato il valore)
#[derive(Debug, Clone, Copy, PartialEq, Eq, serde::Serialize, serde::Deserialize)]
pub enum FieldValueType {
    Numeric,
    Text,
    Timestamp,
    Boolean,
}

/// Metadata aggiuntivo per il tipo inferito
#[derive(Debug, Clone, Default, serde::Serialize, serde::Deserialize)]
pub struct TypeMetadata {
    /// Tutti i valori numerici sono interi (no frazioni)
    pub is_integer: bool,

    /// Ha simboli di valuta ($, €, £) nei valori text
    pub has_currency_symbols: bool,

    /// Formato data rilevato (se Temporal)
    pub date_format: Option<DateFormat>,

    /// Cardinality bassa (<= categorical_threshold)
    pub is_low_cardinality: bool,
}

/// Formato data rilevato
#[derive(Debug, Clone, Copy, PartialEq, Eq, serde::Serialize, serde::Deserialize)]
pub enum DateFormat {
    /// YYYY-MM-DD (ISO 8601)
    ISO8601,
    /// DD/MM/YYYY (European)
    DMY,
    /// MM/DD/YYYY (US)
    MDY,
    /// YYYY/MM/DD (Asian)
    YMD,
    /// Non determinabile con certezza
    Ambiguous,
}

/// Configurazione per l'inferenza dei tipi
#[derive(Debug, Clone)]
pub struct InferenceConfig {
    /// Sample size (default 500 righe)
    pub sample_size: usize,

    /// Confidence threshold (default 0.9 = 90%)
    pub confidence_threshold: f64,

    /// Cardinality threshold per categorical (default 100)
    pub categorical_threshold: usize,
}

impl Default for InferenceConfig {
    fn default() -> Self {
        Self {
            sample_size: 500,
            confidence_threshold: 0.9,
            categorical_threshold: 100,
        }
    }
}

/// Infer types for all columns in a DataTable
///
/// # Arguments
/// - `table`: DataTable da analizzare
/// - `columns`: Nomi colonne in ordine schema
/// - `config`: Configurazione inferenza
///
/// # Returns
/// Vec<InferredColumnType> parallelo a `columns` (stesso ordine)
pub fn infer_column_types(
    table: &DataTable,
    columns: &[String],
    config: InferenceConfig,
) -> Vec<InferredColumnType> {
    columns
        .iter()
        .map(|col_name| infer_column_type(table, col_name, &config))
        .collect()
}

// ── Internal Helpers ──────────────────────────────────────────────────────────

/// Pattern statistics per una singola colonna
#[derive(Debug, Default)]
struct PatternStats {
    numeric_count: usize,
    text_count: usize,
    timestamp_count: usize,
    bool_count: usize,
    null_count: usize,
    integer_count: usize,
    datetime_pattern_count: usize,
    date_pattern_count: usize,
    distinct_values: HashSet<String>,
    has_currency: bool,
}

impl PatternStats {
    fn total(&self) -> usize {
        self.numeric_count + self.text_count + self.timestamp_count + self.bool_count
    }

    fn cardinality(&self) -> usize {
        self.distinct_values.len()
    }

    fn all_integers(&self) -> bool {
        self.numeric_count > 0 && self.integer_count == self.numeric_count
    }

    fn is_mixed(&self) -> bool {
        let types_present = [
            self.numeric_count > 0,
            self.text_count > 0,
            self.timestamp_count > 0,
            self.bool_count > 0,
        ]
        .iter()
        .filter(|&&x| x)
        .count();
        types_present > 1
    }
}

/// Infer type for a single column
fn infer_column_type(
    table: &DataTable,
    col_name: &str,
    config: &InferenceConfig,
) -> InferredColumnType {
    // 1. Sample rows (max config.sample_size)
    let sample_rows = sample_rows(table, config.sample_size);

    // 2. Analyze patterns in sample
    let mut stats = PatternStats::default();
    for row in &sample_rows {
        if let Some(value) = row.get(col_name) {
            analyze_value(value, &mut stats);
        }
    }

    // 3. Determine storage type (prevalenza)
    let storage_type = detect_storage_type(&stats);

    // 4. Determine semantic type
    let semantic_type = detect_semantic_type(&stats, &storage_type, config);

    // 5. Calculate confidence
    let confidence = calculate_confidence(&stats, &storage_type);

    // 6. Build metadata
    let metadata = TypeMetadata {
        is_integer: stats.all_integers(),
        has_currency_symbols: stats.has_currency,
        date_format: detect_date_format(&stats, &sample_rows, col_name),
        is_low_cardinality: stats.cardinality() <= config.categorical_threshold,
    };

    InferredColumnType {
        storage_type,
        semantic_type,
        confidence,
        cardinality: stats.cardinality(),
        null_count: stats.null_count,
        sample_size: sample_rows.len(),
        metadata,
    }
}

/// Sample rows from table (max sample_size)
fn sample_rows(table: &DataTable, sample_size: usize) -> Vec<&DataRow> {
    table.rows().iter().take(sample_size).collect()
}

/// Analyze a single value and update pattern stats
fn analyze_value(value: &FieldValue, stats: &mut PatternStats) {
    match value {
        FieldValue::Null => {
            stats.null_count += 1;
        }
        FieldValue::Numeric(n) => {
            stats.numeric_count += 1;
            if n.fract() == 0.0 {
                stats.integer_count += 1;
            }
            stats.distinct_values.insert(value_to_key(value));
        }
        FieldValue::Timestamp(ts) => {
            stats.timestamp_count += 1;
            stats.distinct_values.insert(ts.to_string());
        }
        FieldValue::Bool(b) => {
            stats.bool_count += 1;
            stats.distinct_values.insert(b.to_string());
        }
        FieldValue::Text(s) => {
            stats.text_count += 1;

            // Check patterns
            if is_datetime_pattern(s) {
                stats.datetime_pattern_count += 1;
            } else if is_date_pattern(s) {
                stats.date_pattern_count += 1;
            }

            if has_currency_symbol(s) {
                stats.has_currency = true;
            }

            stats.distinct_values.insert(s.clone());
        }
    }
}

/// Convert FieldValue to String key for cardinality tracking
fn value_to_key(value: &FieldValue) -> String {
    match value {
        FieldValue::Numeric(n) => n.to_string(),
        FieldValue::Text(s) => s.clone(),
        FieldValue::Timestamp(ts) => ts.to_string(),
        FieldValue::Bool(b) => b.to_string(),
        FieldValue::Null => "null".to_string(),
    }
}

// ── Date Format Detection ─────────────────────────────────────────────────────

/// Parsed components of a date string
#[derive(Debug, Clone, Copy)]
struct DateParts {
    part1: u32,
    part2: u32,
    _part3: u32,
    separator: char,
}

/// Parse date string into numeric parts
///
/// Extracts three numeric parts and separator from date strings like:
/// - "2024-01-15" → DateParts { part1: 2024, part2: 1, part3: 15, separator: '-' }
/// - "15/03/2024" → DateParts { part1: 15, part2: 3, part3: 2024, separator: '/' }
fn parse_date_parts(date_str: &str) -> Option<DateParts> {
    let s = date_str.trim();

    // Detect separator
    let separator = if s.contains('-') {
        '-'
    } else if s.contains('/') {
        '/'
    } else {
        return None;
    };

    // Split by separator
    let parts: Vec<&str> = s.split(separator).collect();
    if parts.len() != 3 {
        return None;
    }

    // Parse each part as u32
    let part1 = parts[0].parse::<u32>().ok()?;
    let part2 = parts[1].parse::<u32>().ok()?;
    let part3 = parts[2].parse::<u32>().ok()?;

    Some(DateParts {
        part1,
        part2,
        _part3: part3,
        separator,
    })
}

/// Extract date string samples from column
///
/// Collects up to 50 date/datetime strings from the sample rows:
/// - Filters Text values matching date or datetime patterns
/// - Strips time portion from datetime values (splits on ' ' or 'T')
/// - Limits to 50 samples for performance (probability of all-ambiguous <0.01%)
fn extract_date_samples(sample_rows: &[&DataRow], col_name: &str) -> Vec<String> {
    let mut date_strings = Vec::new();

    for row in sample_rows {
        if let Some(FieldValue::Text(s)) = row.get(col_name) {
            // Check if matches date or datetime pattern
            if is_date_pattern(s) {
                date_strings.push(s.clone());
            } else if is_datetime_pattern(s) {
                // Strip time portion: split on ' ' or 'T', take date part
                let date_part = if s.contains('T') {
                    s.split('T').next().unwrap_or(s)
                } else {
                    s.split(' ').next().unwrap_or(s)
                };
                date_strings.push(date_part.to_string());
            }

            // Limit to 50 samples for performance
            if date_strings.len() >= 50 {
                break;
            }
        }
    }

    date_strings
}

/// Detect date format from sample data
///
/// Implements "unambiguous dates" algorithm (>90% accuracy, arXiv 2025):
/// 1. Searches for dates with day >12 to disambiguate DMY vs MDY
/// 2. Prioritizes ISO8601 (YYYY-MM-DD) as always unambiguous
/// 3. Falls back to Ambiguous when confidence is low
///
/// Returns:
/// - `None` if column is not temporal
/// - `Some(DateFormat)` with detected format or Ambiguous
fn detect_date_format(
    stats: &PatternStats,
    sample_rows: &[&DataRow],
    col_name: &str,
) -> Option<DateFormat> {
    // 1. Early exit: column is not temporal
    let date_count = stats.date_pattern_count + stats.datetime_pattern_count;
    if date_count == 0 {
        return None;
    }

    // 2. Extract date samples
    let date_samples = extract_date_samples(sample_rows, col_name);
    if date_samples.is_empty() {
        return None;
    }

    // 3. Parse date parts
    let parsed: Vec<DateParts> = date_samples
        .iter()
        .filter_map(|s| parse_date_parts(s))
        .collect();

    if parsed.is_empty() {
        return Some(DateFormat::Ambiguous);
    }

    let total = parsed.len();

    // 4. Detect ISO8601: YYYY-MM-DD (4 digits first, dash separator)
    let iso8601_count = parsed
        .iter()
        .filter(|p| p.part1 >= 1000 && p.separator == '-')
        .count();

    if (iso8601_count as f64 / total as f64) > 0.8 {
        return Some(DateFormat::ISO8601);
    }

    // 5. Detect YMD: YYYY/MM/DD (4 digits first, slash separator)
    let ymd_count = parsed
        .iter()
        .filter(|p| p.part1 >= 1000 && p.separator == '/')
        .count();

    if (ymd_count as f64 / total as f64) > 0.8 {
        return Some(DateFormat::YMD);
    }

    // 6. Disambiguate DMY vs MDY using unambiguous dates
    let mut dmy_votes = 0;
    let mut mdy_votes = 0;

    for parts in &parsed {
        // Skip dates with 4-digit year at start (ISO8601/YMD already handled above)
        if parts.part1 >= 1000 {
            continue;
        }

        // Unambiguous: first part > 12 → must be DMY (day first)
        if parts.part1 > 12 {
            dmy_votes += 1;
        }
        // Unambiguous: second part > 12 → must be MDY (month first, day second)
        else if parts.part2 > 12 {
            mdy_votes += 1;
        }
    }

    // Require 3:1 evidence ratio for confidence
    if dmy_votes >= 3 * mdy_votes && dmy_votes > 0 {
        return Some(DateFormat::DMY);
    }
    if mdy_votes >= 3 * dmy_votes && mdy_votes > 0 {
        return Some(DateFormat::MDY);
    }

    // 7. If no unambiguous dates found → Ambiguous (don't guess)
    if dmy_votes == 0 && mdy_votes == 0 {
        return Some(DateFormat::Ambiguous);
    }

    // 8. Fallback: separator-based heuristic (only if some votes exist but ratio insufficient)
    // European dates (DMY) more common globally with '/' separator
    let slash_count = parsed.iter().filter(|p| p.separator == '/').count();
    if (slash_count as f64 / total as f64) > 0.7 {
        return Some(DateFormat::DMY);
    }

    // No clear winner → Ambiguous
    Some(DateFormat::Ambiguous)
}

/// Detect storage type based on pattern prevalence (threshold 60%)
fn detect_storage_type(stats: &PatternStats) -> FieldValueType {
    let total = stats.total();
    if total == 0 {
        return FieldValueType::Text; // Fallback per colonne vuote
    }

    let threshold = 0.6;

    // Priority order: Timestamp > Numeric > Boolean > Text

    // Check if native Timestamp values (from Arrow/Parquet)
    if (stats.timestamp_count as f64 / total as f64) > threshold {
        return FieldValueType::Timestamp;
    }

    // Check if Text values with date/datetime patterns (from CSV)
    // Promote Text → Timestamp if majority have date patterns
    let date_like_count = stats.datetime_pattern_count + stats.date_pattern_count;
    if stats.text_count > 0 && (date_like_count as f64 / stats.text_count as f64) > threshold {
        return FieldValueType::Timestamp;
    }

    if (stats.numeric_count as f64 / total as f64) > threshold {
        FieldValueType::Numeric
    } else if (stats.bool_count as f64 / total as f64) > threshold {
        FieldValueType::Boolean
    } else {
        FieldValueType::Text
    }
}

/// Detect semantic type from storage type + heuristics
fn detect_semantic_type(
    stats: &PatternStats,
    storage: &FieldValueType,
    config: &InferenceConfig,
) -> DataType {
    match storage {
        FieldValueType::Timestamp => DataType::Temporal,

        FieldValueType::Numeric => {
            // Heuristic: cardinality bassa + interi → Ordinal (ZIP codes, anni, mesi)
            if stats.cardinality() <= config.categorical_threshold && stats.all_integers() {
                DataType::Ordinal
            } else {
                DataType::Quantitative
            }
        }

        FieldValueType::Text => {
            // Low cardinality → Nominal (categorie)
            if stats.cardinality() <= config.categorical_threshold {
                DataType::Nominal
            } else {
                // High cardinality → potrebbe essere ID/free text, ma default Nominal
                DataType::Nominal
            }
        }

        FieldValueType::Boolean => DataType::Nominal, // Trattato come categoria binaria
    }
}

/// Calculate confidence based on pattern consistency
fn calculate_confidence(stats: &PatternStats, inferred_storage: &FieldValueType) -> f64 {
    let total = stats.total();
    if total == 0 {
        return 0.0;
    }

    // Count matching values for the inferred type
    let matching_count = match inferred_storage {
        FieldValueType::Numeric => stats.numeric_count,
        FieldValueType::Text => stats.text_count,
        FieldValueType::Timestamp => stats.timestamp_count,
        FieldValueType::Boolean => stats.bool_count,
    };

    let match_rate = matching_count as f64 / total as f64;

    // Penalty for mixed types
    if stats.is_mixed() {
        match_rate * 0.8 // -20% confidence
    } else {
        match_rate
    }
}

// ── Pattern Detection ─────────────────────────────────────────────────────────

/// Check if string matches datetime pattern (YYYY-MM-DD HH:MM:SS or ISO 8601)
///
/// Robust pattern that distinguishes dates from URLs, phone numbers, etc.
/// References: https://www.oreilly.com/library/view/regular-expressions-cookbook/9781449327453/ch04s07.html
fn is_datetime_pattern(s: &str) -> bool {
    let s = s.trim();

    // Exclude URLs (contain :// or www.)
    if s.contains("://") || s.contains("www.") {
        return false;
    }

    // Exclude phone numbers (contain 'x' extensions or parentheses without time structure)
    if (s.contains('x') || s.contains('(')) && !s.contains('T') {
        return false;
    }

    // Must contain both date and time separators
    let has_date_sep = s.contains('-') || s.contains('/');
    let has_time_sep = s.contains(':');

    if !has_date_sep || !has_time_sep {
        return false;
    }

    // Check for ISO 8601 format: YYYY-MM-DDTHH:MM:SS or YYYY-MM-DD HH:MM:SS
    // Pattern: digits-digits-digits separator digits:digits
    let parts: Vec<&str> = if s.contains('T') {
        s.split('T').collect()
    } else {
        s.split(' ').collect()
    };

    if parts.len() != 2 {
        return false;
    }

    // Validate date part (YYYY-MM-DD or YYYY/MM/DD)
    let date_part = parts[0];
    if !is_date_like_structure(date_part) {
        return false;
    }

    // Validate time part (HH:MM or HH:MM:SS)
    let time_part = parts[1];
    is_time_like_structure(time_part)
}

/// Check if string matches date pattern (YYYY-MM-DD, MM/DD/YYYY, DD-MM-YYYY)
///
/// Robust pattern that distinguishes dates from URLs, phone numbers, etc.
/// References: https://www.freecodecamp.org/news/regex-for-date-formats-what-is-the-regular-expression-for-matching-dates/
fn is_date_pattern(s: &str) -> bool {
    let s = s.trim();

    // Exclude URLs (contain :// or www. or common TLDs)
    if s.contains("://") || s.contains("www.") || s.contains(".com") || s.contains(".org") {
        return false;
    }

    // Exclude phone numbers (contain 'x' extensions or excessive dashes/parentheses)
    if s.contains('x') || s.contains('(') || s.contains(')') {
        return false;
    }

    // Exclude if contains time separator
    if s.contains(':') {
        return false;
    }

    // Must contain date separators
    let has_sep = s.contains('-') || s.contains('/');
    if !has_sep {
        return false;
    }

    is_date_like_structure(s)
}

/// Check if string has date-like structure with numbers in correct positions
fn is_date_like_structure(s: &str) -> bool {
    // Split by common separators
    let parts: Vec<&str> = if s.contains('-') {
        s.split('-').collect()
    } else if s.contains('/') {
        s.split('/').collect()
    } else {
        return false;
    };

    // Must have exactly 3 parts (year, month, day in some order)
    if parts.len() != 3 {
        return false;
    }

    // All parts must be numeric
    if !parts.iter().all(|p| p.chars().all(|c| c.is_ascii_digit())) {
        return false;
    }

    // Check part lengths: expect either YYYY-MM-DD or DD-MM-YYYY or MM-DD-YYYY
    let lens: Vec<usize> = parts.iter().map(|p| p.len()).collect();

    // Common patterns:
    // YYYY-MM-DD: [4, 2, 2]
    // DD-MM-YYYY: [2, 2, 4]
    // MM-DD-YYYY: [2, 2, 4]
    // D-M-YYYY: [1, 1, 4] or [1, 2, 4] etc.
    matches!(
        lens.as_slice(),
        [4, 2, 2]
            | [2, 2, 4]
            | [4, 1, 1]
            | [1, 1, 4]
            | [2, 1, 4]
            | [1, 2, 4]
            | [4, 2, 1]
            | [4, 1, 2]
    )
}

/// Check if string has time-like structure (HH:MM or HH:MM:SS)
fn is_time_like_structure(s: &str) -> bool {
    let parts: Vec<&str> = s.split(':').collect();

    // Must have 2 or 3 parts (HH:MM or HH:MM:SS)
    if parts.len() < 2 || parts.len() > 3 {
        return false;
    }

    // All parts must be numeric
    if !parts.iter().all(|p| p.chars().all(|c| c.is_ascii_digit())) {
        return false;
    }

    // Check part lengths: expect HH:MM:SS (2 digits each)
    parts.iter().all(|p| p.len() == 2 || p.len() == 1)
}

/// Check if string contains currency symbols
fn has_currency_symbol(s: &str) -> bool {
    s.contains('$') || s.contains('€') || s.contains('£') || s.contains('¥')
}

// ── Tests ─────────────────────────────────────────────────────────────────────

#[cfg(test)]
mod tests {
    use super::*;
    use std::collections::HashMap;

    fn create_test_table(rows: Vec<HashMap<String, FieldValue>>) -> DataTable {
        DataTable::new(rows)
    }

    #[test]
    fn test_infer_quantitative() {
        let rows = vec![
            HashMap::from([("revenue".to_string(), FieldValue::Numeric(1250.50))]),
            HashMap::from([("revenue".to_string(), FieldValue::Numeric(980.00))]),
            HashMap::from([("revenue".to_string(), FieldValue::Numeric(1450.75))]),
        ];
        let table = create_test_table(rows);
        let columns = vec!["revenue".to_string()];

        let inferred = infer_column_types(&table, &columns, InferenceConfig::default());

        assert_eq!(inferred.len(), 1);
        assert_eq!(inferred[0].storage_type, FieldValueType::Numeric);
        assert_eq!(inferred[0].semantic_type, DataType::Quantitative);
        assert!(inferred[0].confidence > 0.9);
    }

    #[test]
    fn test_infer_nominal_low_cardinality() {
        let rows = vec![
            HashMap::from([("region".to_string(), FieldValue::Text("North".to_string()))]),
            HashMap::from([("region".to_string(), FieldValue::Text("South".to_string()))]),
            HashMap::from([("region".to_string(), FieldValue::Text("North".to_string()))]),
        ];
        let table = create_test_table(rows);
        let columns = vec!["region".to_string()];

        let inferred = infer_column_types(&table, &columns, InferenceConfig::default());

        assert_eq!(inferred.len(), 1);
        assert_eq!(inferred[0].storage_type, FieldValueType::Text);
        assert_eq!(inferred[0].semantic_type, DataType::Nominal);
        assert_eq!(inferred[0].cardinality, 2);
        assert!(inferred[0].metadata.is_low_cardinality);
    }

    #[test]
    fn test_infer_ordinal_zip_codes() {
        let rows = vec![
            HashMap::from([("zip".to_string(), FieldValue::Numeric(10001.0))]),
            HashMap::from([("zip".to_string(), FieldValue::Numeric(10002.0))]),
            HashMap::from([("zip".to_string(), FieldValue::Numeric(90210.0))]),
        ];
        let table = create_test_table(rows);
        let columns = vec!["zip".to_string()];

        let inferred = infer_column_types(&table, &columns, InferenceConfig::default());

        assert_eq!(inferred.len(), 1);
        assert_eq!(inferred[0].storage_type, FieldValueType::Numeric);
        assert_eq!(inferred[0].semantic_type, DataType::Ordinal); // Low cardinality + integer
        assert!(inferred[0].metadata.is_integer);
    }

    #[test]
    fn test_infer_mixed_types_low_confidence() {
        let rows = vec![
            HashMap::from([("value".to_string(), FieldValue::Numeric(100.0))]),
            HashMap::from([("value".to_string(), FieldValue::Text("abc".to_string()))]),
            HashMap::from([("value".to_string(), FieldValue::Numeric(200.0))]),
            HashMap::from([("value".to_string(), FieldValue::Text("xyz".to_string()))]),
        ];
        let table = create_test_table(rows);
        let columns = vec!["value".to_string()];

        let inferred = infer_column_types(&table, &columns, InferenceConfig::default());

        assert_eq!(inferred.len(), 1);
        // Con 50% numeric, 50% text, nessuno raggiunge 60% → fallback Text
        assert_eq!(inferred[0].storage_type, FieldValueType::Text);
        // Confidence dovrebbe essere bassa (<0.7) per tipo misto
        assert!(inferred[0].confidence < 0.7);
    }

    #[test]
    fn test_pattern_detection_datetime() {
        // Valid datetime patterns
        assert!(is_datetime_pattern("2024-01-15 10:30:00"));
        assert!(is_datetime_pattern("2024/01/15 10:30:00"));
        assert!(is_datetime_pattern("2024-01-15T10:30:00")); // ISO 8601

        // Invalid: date only (no time)
        assert!(!is_datetime_pattern("2024-01-15"));

        // Invalid: time only (no date)
        assert!(!is_datetime_pattern("10:30:00"));

        // Invalid: URLs (even with colons)
        assert!(!is_datetime_pattern("http://www.example.com/"));
        assert!(!is_datetime_pattern("https://example.com:8080/path"));

        // Invalid: phone with extension
        assert!(!is_datetime_pattern("846-790-4623x4715"));
    }

    #[test]
    fn test_pattern_detection_date() {
        // Valid date patterns
        assert!(is_date_pattern("2024-01-15"));
        assert!(is_date_pattern("01/15/2024"));
        assert!(is_date_pattern("15-01-2024"));
        assert!(is_date_pattern("2024/1/5")); // Single digit month/day

        // Invalid: has time component
        assert!(!is_date_pattern("2024-01-15 10:30:00"));

        // Invalid: no separator
        assert!(!is_date_pattern("revenue"));
        assert!(!is_date_pattern("20240115"));

        // Invalid: URLs
        assert!(!is_date_pattern("http://www.shea.biz/"));
        assert!(!is_date_pattern("www.example.com/path"));
        assert!(!is_date_pattern("example.com"));

        // Invalid: phone numbers
        assert!(!is_date_pattern("846-790-4623x4715"));
        assert!(!is_date_pattern("(335)987-3085x3780"));
        assert!(!is_date_pattern("124-597-8652"));

        // Invalid: too many or too few parts
        assert!(!is_date_pattern("2024-01"));
        assert!(!is_date_pattern("2024-01-15-10"));
    }

    #[test]
    fn test_date_vs_phone_distinction() {
        // Phones should NOT be recognized as dates
        assert!(!is_date_pattern("846-790-4623x4715")); // Has 'x'
        assert!(!is_date_pattern("(335)987-3085x3780")); // Has '(' and 'x'
        assert!(!is_date_pattern("555-1234")); // Only 2 parts

        // Dates should be recognized
        assert!(is_date_pattern("2024-01-15")); // YYYY-MM-DD
        assert!(is_date_pattern("01-15-2024")); // MM-DD-YYYY
    }

    #[test]
    fn test_date_vs_url_distinction() {
        // URLs should NOT be recognized as dates
        assert!(!is_date_pattern("http://www.shea.biz/"));
        assert!(!is_date_pattern("https://example.com/path"));
        assert!(!is_date_pattern("www.example.com"));
        assert!(!is_date_pattern("example.com"));

        // Dates with slashes should still work
        assert!(is_date_pattern("2024/01/15"));
        assert!(is_date_pattern("01/15/2024"));
    }

    #[test]
    fn test_currency_detection() {
        assert!(has_currency_symbol("$100.00"));
        assert!(has_currency_symbol("€50"));
        assert!(has_currency_symbol("£25.99"));
        assert!(!has_currency_symbol("100"));
    }

    // ── Date Format Detection Tests ───────────────────────────────────────────

    #[test]
    fn test_date_format_iso8601() {
        let rows = vec![
            HashMap::from([(
                "date".to_string(),
                FieldValue::Text("2024-01-15".to_string()),
            )]),
            HashMap::from([(
                "date".to_string(),
                FieldValue::Text("2024-03-20".to_string()),
            )]),
            HashMap::from([(
                "date".to_string(),
                FieldValue::Text("2024-12-31".to_string()),
            )]),
        ];
        let table = create_test_table(rows);
        let columns = vec!["date".to_string()];

        let inferred = infer_column_types(&table, &columns, InferenceConfig::default());

        assert_eq!(inferred.len(), 1);
        assert_eq!(inferred[0].metadata.date_format, Some(DateFormat::ISO8601));
    }

    #[test]
    fn test_date_format_ymd() {
        let rows = vec![
            HashMap::from([(
                "date".to_string(),
                FieldValue::Text("2024/01/15".to_string()),
            )]),
            HashMap::from([(
                "date".to_string(),
                FieldValue::Text("2024/03/20".to_string()),
            )]),
            HashMap::from([(
                "date".to_string(),
                FieldValue::Text("2024/12/31".to_string()),
            )]),
        ];
        let table = create_test_table(rows);
        let columns = vec!["date".to_string()];

        let inferred = infer_column_types(&table, &columns, InferenceConfig::default());

        assert_eq!(inferred.len(), 1);
        assert_eq!(inferred[0].metadata.date_format, Some(DateFormat::YMD));
    }

    #[test]
    fn test_date_format_dmy_unambiguous() {
        let rows = vec![
            HashMap::from([(
                "date".to_string(),
                FieldValue::Text("15/01/2024".to_string()),
            )]),
            HashMap::from([(
                "date".to_string(),
                FieldValue::Text("25/03/2024".to_string()),
            )]),
            HashMap::from([(
                "date".to_string(),
                FieldValue::Text("31/12/2024".to_string()),
            )]),
        ];
        let table = create_test_table(rows);
        let columns = vec!["date".to_string()];

        let inferred = infer_column_types(&table, &columns, InferenceConfig::default());

        assert_eq!(inferred.len(), 1);
        assert_eq!(inferred[0].metadata.date_format, Some(DateFormat::DMY));
    }

    #[test]
    fn test_date_format_mdy_unambiguous() {
        let rows = vec![
            HashMap::from([(
                "date".to_string(),
                FieldValue::Text("01/15/2024".to_string()),
            )]),
            HashMap::from([(
                "date".to_string(),
                FieldValue::Text("03/25/2024".to_string()),
            )]),
            HashMap::from([(
                "date".to_string(),
                FieldValue::Text("12/31/2024".to_string()),
            )]),
        ];
        let table = create_test_table(rows);
        let columns = vec!["date".to_string()];

        let inferred = infer_column_types(&table, &columns, InferenceConfig::default());

        assert_eq!(inferred.len(), 1);
        assert_eq!(inferred[0].metadata.date_format, Some(DateFormat::MDY));
    }

    #[test]
    fn test_date_format_ambiguous() {
        let rows = vec![
            HashMap::from([(
                "date".to_string(),
                FieldValue::Text("01/02/2024".to_string()),
            )]),
            HashMap::from([(
                "date".to_string(),
                FieldValue::Text("03/04/2024".to_string()),
            )]),
            HashMap::from([(
                "date".to_string(),
                FieldValue::Text("05/06/2024".to_string()),
            )]),
        ];
        let table = create_test_table(rows);
        let columns = vec!["date".to_string()];

        let inferred = infer_column_types(&table, &columns, InferenceConfig::default());

        assert_eq!(inferred.len(), 1);
        assert_eq!(
            inferred[0].metadata.date_format,
            Some(DateFormat::Ambiguous)
        );
    }

    #[test]
    fn test_date_format_datetime_strips_time() {
        let rows = vec![
            HashMap::from([(
                "datetime".to_string(),
                FieldValue::Text("2024-01-15 10:30:00".to_string()),
            )]),
            HashMap::from([(
                "datetime".to_string(),
                FieldValue::Text("2024-03-20T14:45:00".to_string()),
            )]),
        ];
        let table = create_test_table(rows);
        let columns = vec!["datetime".to_string()];

        let inferred = infer_column_types(&table, &columns, InferenceConfig::default());

        assert_eq!(inferred.len(), 1);
        assert_eq!(inferred[0].metadata.date_format, Some(DateFormat::ISO8601));
    }

    #[test]
    fn test_date_format_non_temporal_column() {
        let rows = vec![
            HashMap::from([("revenue".to_string(), FieldValue::Numeric(1250.50))]),
            HashMap::from([("revenue".to_string(), FieldValue::Numeric(980.00))]),
        ];
        let table = create_test_table(rows);
        let columns = vec!["revenue".to_string()];

        let inferred = infer_column_types(&table, &columns, InferenceConfig::default());

        assert_eq!(inferred.len(), 1);
        assert_eq!(inferred[0].metadata.date_format, None); // Non-temporal column
    }
}