//! Helpers for mapping JSON values to PostgreSQL data types in generated SQL.
//!
//! Used by the SQLx driver when building insert/update placeholders and bind lists.

use serde_json::Value;
use std::collections::HashMap;

/// Returns true if `value` parses as RFC 3339 (suitable for `timestamptz` parameter binding).
pub fn is_rfc3339_timestamp(value: &str) -> bool {
    chrono::DateTime::parse_from_rfc3339(value).is_ok()
}

/// Whether to emit `$n::timestamptz` for this column/value pair.
///
/// JSON string values that parse as RFC 3339 are bound as PostgreSQL `text` unless the
/// placeholder is cast. We cast when the column looks like a timestamp field: common
/// names (audit columns, session expiry, validity windows) or any name ending in `_at`
/// (e.g. `expires_at`, `last_seen_at`).
pub fn timestamptz_cast_for_column(column: &str, value: &Value) -> bool {
    if !value.as_str().map(is_rfc3339_timestamp).unwrap_or(false) {
        return false;
    }
    let normalized: String = column.trim_matches('"').to_ascii_lowercase();
    matches!(
        normalized.as_str(),
        "created_at"
            | "updated_at"
            | "deleted_at"
            | "expires_at"
            | "started_at"
            | "ended_at"
            | "revoked_at"
            | "valid_until"
            | "valid_from"
            | "not_before"
            | "not_after"
            | "published_at"
            | "scheduled_at"
            | "locked_at"
            | "last_seen_at"
    ) || normalized.ends_with("_at")
}

fn normalized_column_name(column: &str) -> String {
    column.trim_matches('"').to_ascii_lowercase()
}

fn normalized_type_tokens(data_type: &str) -> Vec<String> {
    data_type
        .split(|ch| ch == '|' || ch == ',' || ch == ';')
        .map(|part| part.trim().to_ascii_lowercase())
        .filter(|part| !part.is_empty())
        .collect()
}

fn is_exact_type_token(token: &str, expected: &str) -> bool {
    token == expected || token.ends_with(&format!(".{}", expected))
}

pub fn lookup_column_data_type<'a>(
    column: &str,
    column_types: Option<&'a HashMap<String, String>>,
) -> Option<&'a str> {
    let map = column_types?;
    let key = normalized_column_name(column);
    map.get(&key).map(|value| value.as_str())
}

pub fn is_json_data_type(data_type: &str) -> bool {
    normalized_type_tokens(data_type)
        .iter()
        .any(|token| is_exact_type_token(token, "json"))
}

pub fn is_jsonb_data_type(data_type: &str) -> bool {
    normalized_type_tokens(data_type)
        .iter()
        .any(|token| is_exact_type_token(token, "jsonb"))
}

pub fn is_json_like_data_type(data_type: &str) -> bool {
    is_json_data_type(data_type) || is_jsonb_data_type(data_type)
}

pub fn jsonb_cast_for_column(column: &str, column_types: Option<&HashMap<String, String>>) -> bool {
    lookup_column_data_type(column, column_types)
        .map(is_jsonb_data_type)
        .unwrap_or(false)
}

pub fn json_like_column(column: &str, column_types: Option<&HashMap<String, String>>) -> bool {
    lookup_column_data_type(column, column_types)
        .map(is_json_like_data_type)
        .unwrap_or(false)
}

/// Returns the short Postgres cast name (`float8`, `int8`, `boolean`, ...) to apply to a
/// `$n` placeholder when comparing a JSON string value against a column of the given
/// `information_schema.columns` descriptor (`data_type|udt_name`).
///
/// Used by the WHERE builder so `"0.75" = cache_hit_ratio` (a `double precision` column)
/// becomes `$1::float8`, letting Postgres parse the literal into the target type instead
/// of failing with `operator does not exist: double precision = text`.
///
/// Returns `None` for textual types (no cast needed), for `uuid` (handled separately by
/// the LHS `::text` path), and for unknown descriptors.
pub fn where_cast_for_column_type(descriptor: &str) -> Option<&'static str> {
    let tokens: Vec<String> = normalized_type_tokens(descriptor);
    if tokens.is_empty() {
        return None;
    }

    for token in &tokens {
        if let Some(cast) = where_cast_for_token(token) {
            return Some(cast);
        }
    }

    None
}

fn where_cast_for_token(token: &str) -> Option<&'static str> {
    let trimmed: &str = token.trim();
    let bare: &str = trimmed.rsplit('.').next().unwrap_or(trimmed);
    match bare {
        "double precision" | "float8" => Some("float8"),
        "real" | "float4" => Some("float4"),
        "numeric" | "decimal" => Some("numeric"),
        "bigint" | "int8" => Some("int8"),
        "integer" | "int" | "int4" => Some("int4"),
        "smallint" | "int2" => Some("int2"),
        "boolean" | "bool" => Some("bool"),
        "timestamp with time zone" | "timestamptz" => Some("timestamptz"),
        "timestamp without time zone" | "timestamp" => Some("timestamp"),
        "date" => Some("date"),
        "time with time zone" | "timetz" => Some("timetz"),
        "time without time zone" | "time" => Some("time"),
        "jsonb" => Some("jsonb"),
        "json" => Some("json"),
        "inet" => Some("inet"),
        "cidr" => Some("cidr"),
        "macaddr" => Some("macaddr"),
        "macaddr8" => Some("macaddr8"),
        _ => None,
    }
}

/// Returns the short Postgres cast keyword for the given column name when its descriptor
/// is known. Convenience wrapper over [`lookup_column_data_type`] and
/// [`where_cast_for_column_type`].
pub fn where_cast_for_column(
    column: &str,
    column_types: Option<&HashMap<String, String>>,
) -> Option<&'static str> {
    lookup_column_data_type(column, column_types).and_then(where_cast_for_column_type)
}

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

    #[test]
    fn rfc3339_accepts_z_suffix() {
        assert!(is_rfc3339_timestamp("2024-01-15T12:00:00Z"));
    }

    #[test]
    fn rfc3339_rejects_plain_date() {
        assert!(!is_rfc3339_timestamp("2024-01-15"));
    }

    #[test]
    fn timestamptz_cast_only_for_known_columns_and_rfc3339_strings() {
        assert!(timestamptz_cast_for_column(
            "created_at",
            &json!("2024-01-15T12:00:00.000Z")
        ));
        assert!(timestamptz_cast_for_column(
            "expires_at",
            &json!("2026-03-25T15:43:18.618+00:00")
        ));
        assert!(timestamptz_cast_for_column(
            "custom_expires_at",
            &json!("2024-01-15T12:00:00.000Z")
        ));
        assert!(!timestamptz_cast_for_column(
            "created_at",
            &json!("not-a-date")
        ));
        assert!(!timestamptz_cast_for_column(
            "title",
            &json!("2024-01-15T12:00:00.000Z")
        ));
        assert!(!timestamptz_cast_for_column(
            "custom_at",
            &json!("not rfc3339")
        ));
        assert!(timestamptz_cast_for_column(
            "\"UPDATED_AT\"",
            &json!("2024-01-15T12:00:00+00:00")
        ));
    }

    #[test]
    fn lookup_column_data_type_is_quote_and_case_insensitive() {
        let column_types = HashMap::from([
            ("payload".to_string(), "jsonb".to_string()),
            ("metadata".to_string(), "json".to_string()),
        ]);

        assert_eq!(
            lookup_column_data_type("\"PAYLOAD\"", Some(&column_types)),
            Some("jsonb")
        );
        assert_eq!(
            lookup_column_data_type("metadata", Some(&column_types)),
            Some("json")
        );
        assert_eq!(
            lookup_column_data_type("missing", Some(&column_types)),
            None
        );
    }

    #[test]
    fn jsonb_cast_for_column_only_targets_jsonb() {
        let column_types = HashMap::from([
            ("payload".to_string(), "jsonb".to_string()),
            ("metadata".to_string(), "json".to_string()),
        ]);

        assert!(jsonb_cast_for_column("payload", Some(&column_types)));
        assert!(!jsonb_cast_for_column("metadata", Some(&column_types)));
        assert!(!jsonb_cast_for_column("created_at", Some(&column_types)));
    }

    #[test]
    fn json_like_column_detects_json_and_jsonb() {
        let column_types = HashMap::from([
            ("payload".to_string(), "jsonb|jsonb".to_string()),
            ("metadata".to_string(), "user-defined|json".to_string()),
            ("title".to_string(), "text".to_string()),
            ("events".to_string(), "array|_jsonb".to_string()),
        ]);

        assert!(json_like_column("payload", Some(&column_types)));
        assert!(json_like_column("metadata", Some(&column_types)));
        assert!(!json_like_column("title", Some(&column_types)));
        assert!(!json_like_column("events", Some(&column_types)));
    }

    #[test]
    fn where_cast_for_column_type_maps_common_numeric_and_bool_descriptors() {
        assert_eq!(
            where_cast_for_column_type("double precision|float8"),
            Some("float8")
        );
        assert_eq!(where_cast_for_column_type("real|float4"), Some("float4"));
        assert_eq!(
            where_cast_for_column_type("numeric|numeric"),
            Some("numeric")
        );
        assert_eq!(where_cast_for_column_type("bigint|int8"), Some("int8"));
        assert_eq!(where_cast_for_column_type("integer|int4"), Some("int4"));
        assert_eq!(where_cast_for_column_type("smallint|int2"), Some("int2"));
        assert_eq!(where_cast_for_column_type("boolean|bool"), Some("bool"));
    }

    #[test]
    fn where_cast_for_column_type_maps_date_time_and_network_descriptors() {
        assert_eq!(
            where_cast_for_column_type("timestamp with time zone|timestamptz"),
            Some("timestamptz")
        );
        assert_eq!(
            where_cast_for_column_type("timestamp without time zone|timestamp"),
            Some("timestamp")
        );
        assert_eq!(where_cast_for_column_type("date|date"), Some("date"));
        assert_eq!(where_cast_for_column_type("jsonb|jsonb"), Some("jsonb"));
        assert_eq!(where_cast_for_column_type("inet|inet"), Some("inet"));
        assert_eq!(
            where_cast_for_column_type("USER-DEFINED|pg_catalog.jsonb"),
            Some("jsonb")
        );
    }

    #[test]
    fn where_cast_for_column_type_returns_none_for_text_and_uuid_and_unknown() {
        assert_eq!(where_cast_for_column_type("text|text"), None);
        assert_eq!(
            where_cast_for_column_type("character varying|varchar"),
            None
        );
        assert_eq!(where_cast_for_column_type("uuid|uuid"), None);
        assert_eq!(where_cast_for_column_type("something weird|weird"), None);
        assert_eq!(where_cast_for_column_type(""), None);
    }

    #[test]
    fn where_cast_for_column_looks_up_via_column_map() {
        let column_types: HashMap<String, String> = HashMap::from([
            (
                "cache_hit_ratio".to_string(),
                "double precision|float8".to_string(),
            ),
            ("title".to_string(), "text|text".to_string()),
        ]);

        assert_eq!(
            where_cast_for_column("cache_hit_ratio", Some(&column_types)),
            Some("float8")
        );
        assert_eq!(where_cast_for_column("title", Some(&column_types)), None);
        assert_eq!(where_cast_for_column("missing", Some(&column_types)), None);
        assert_eq!(where_cast_for_column("cache_hit_ratio", None), None);
    }

    #[test]
    fn json_data_type_detection_handles_composite_type_metadata() {
        assert!(is_json_data_type("json"));
        assert!(is_json_data_type("domain|json"));
        assert!(is_jsonb_data_type("jsonb|jsonb"));
        assert!(is_jsonb_data_type("USER-DEFINED|pg_catalog.jsonb"));
        assert!(!is_jsonb_data_type("array|_jsonb"));
        assert!(!is_json_data_type("jsonpath"));
    }
}
