/// Runtime SQL value representation.
/// All Rust types that can be bound as SQL parameters are converted to `Value`.
#[derive(Debug, Clone, PartialEq)]
pub enum Value {
    Null,
    Bool(bool),
    Int(i64),
    Float(f64),
    Text(String),
    Bytes(Vec<u8>),
    Json(serde_json::Value),
    Uuid(uuid::Uuid),
    Timestamp(chrono::NaiveDateTime),
    Date(chrono::NaiveDate),
    IntArray(Vec<i64>),
    FloatArray(Vec<f64>),
    TextArray(Vec<String>),
    BoolArray(Vec<bool>),
    /// Generic array of values (for mixed-type IN clauses).
    ValueArray(Vec<Value>),
    /// Raw SQL expression (not parameterized).
    Raw(String),
    /// Immediate literal value embedded directly into SQL.
    Immediate(String),
    /// IS NOT NULL condition.
    NotNull,
    /// Dynamic SQL expression with bind parameters.
    Dynamic {
        sql: String,
        params: Vec<Value>,
    },
    /// Type-cast value: `?::sql_type` in PostgreSQL.
    Cast {
        value: Box<Value>,
        sql_type: String,
    },
    /// Type-cast array for IN clause: `IN (?::sql_type, ...)`.
    CastArray {
        values: Vec<Value>,
        sql_type: String,
    },
}

impl Value {
    pub fn is_null(&self) -> bool {
        matches!(self, Value::Null)
    }
}

// ============================================
// From implementations for common Rust types
// ============================================

impl From<bool> for Value {
    fn from(v: bool) -> Self {
        Value::Bool(v)
    }
}

impl From<i32> for Value {
    fn from(v: i32) -> Self {
        Value::Int(v as i64)
    }
}

impl From<i64> for Value {
    fn from(v: i64) -> Self {
        Value::Int(v)
    }
}

impl From<f64> for Value {
    fn from(v: f64) -> Self {
        Value::Float(v)
    }
}

impl From<f32> for Value {
    fn from(v: f32) -> Self {
        Value::Float(v as f64)
    }
}

impl From<String> for Value {
    fn from(v: String) -> Self {
        Value::Text(v)
    }
}

impl From<&str> for Value {
    fn from(v: &str) -> Self {
        Value::Text(v.to_string())
    }
}

impl From<&String> for Value {
    fn from(v: &String) -> Self {
        Value::Text(v.clone())
    }
}

impl From<serde_json::Value> for Value {
    fn from(v: serde_json::Value) -> Self {
        Value::Json(v)
    }
}

impl From<uuid::Uuid> for Value {
    fn from(v: uuid::Uuid) -> Self {
        Value::Uuid(v)
    }
}

impl From<chrono::NaiveDateTime> for Value {
    fn from(v: chrono::NaiveDateTime) -> Self {
        Value::Timestamp(v)
    }
}

impl From<chrono::NaiveDate> for Value {
    fn from(v: chrono::NaiveDate) -> Self {
        Value::Date(v)
    }
}

impl From<Vec<i64>> for Value {
    fn from(v: Vec<i64>) -> Self {
        Value::IntArray(v)
    }
}

impl From<Vec<f64>> for Value {
    fn from(v: Vec<f64>) -> Self {
        Value::FloatArray(v)
    }
}

impl From<Vec<String>> for Value {
    fn from(v: Vec<String>) -> Self {
        Value::TextArray(v)
    }
}

impl From<Vec<bool>> for Value {
    fn from(v: Vec<bool>) -> Self {
        Value::BoolArray(v)
    }
}

impl<T: Into<Value>> From<Option<T>> for Value {
    fn from(v: Option<T>) -> Self {
        match v {
            Some(val) => val.into(),
            None => Value::Null,
        }
    }
}

// ============================================
// FromValue trait (Value → Rust type conversion)
// ============================================

/// Convert a `Value` back into a Rust type.
/// Used by the `#[derive(Model)]` macro for `FromRow` implementation.
pub trait FromValue: Sized {
    fn from_value(v: Value) -> Option<Self>;
}

impl FromValue for bool {
    fn from_value(v: Value) -> Option<Self> {
        match v {
            Value::Bool(b) => Some(b),
            Value::Int(i) => Some(i != 0),
            _ => None,
        }
    }
}

impl FromValue for i32 {
    fn from_value(v: Value) -> Option<Self> {
        match v {
            Value::Int(i) => i32::try_from(i).ok(),
            _ => None,
        }
    }
}

impl FromValue for i64 {
    fn from_value(v: Value) -> Option<Self> {
        match v {
            Value::Int(i) => Some(i),
            _ => None,
        }
    }
}

impl FromValue for f64 {
    fn from_value(v: Value) -> Option<Self> {
        match v {
            Value::Float(f) => Some(f),
            Value::Int(i) => Some(i as f64),
            _ => None,
        }
    }
}

impl FromValue for String {
    fn from_value(v: Value) -> Option<Self> {
        match v {
            Value::Text(s) => Some(s),
            _ => None,
        }
    }
}

impl FromValue for Vec<u8> {
    fn from_value(v: Value) -> Option<Self> {
        match v {
            Value::Bytes(b) => Some(b),
            _ => None,
        }
    }
}

impl FromValue for serde_json::Value {
    fn from_value(v: Value) -> Option<Self> {
        match v {
            Value::Json(j) => Some(j),
            Value::Text(s) => serde_json::from_str(&s).ok(),
            _ => None,
        }
    }
}

impl FromValue for uuid::Uuid {
    fn from_value(v: Value) -> Option<Self> {
        match v {
            Value::Uuid(u) => Some(u),
            Value::Text(s) => s.parse().ok(),
            _ => None,
        }
    }
}

impl FromValue for chrono::NaiveDateTime {
    fn from_value(v: Value) -> Option<Self> {
        match v {
            Value::Timestamp(t) => Some(t),
            _ => None,
        }
    }
}

impl FromValue for chrono::NaiveDate {
    fn from_value(v: Value) -> Option<Self> {
        match v {
            Value::Date(d) => Some(d),
            _ => None,
        }
    }
}

impl FromValue for Vec<i64> {
    fn from_value(v: Value) -> Option<Self> {
        match v {
            Value::IntArray(a) => Some(a),
            _ => None,
        }
    }
}

impl FromValue for Vec<f64> {
    fn from_value(v: Value) -> Option<Self> {
        match v {
            Value::FloatArray(a) => Some(a),
            _ => None,
        }
    }
}

impl FromValue for Vec<String> {
    fn from_value(v: Value) -> Option<Self> {
        match v {
            Value::TextArray(a) => Some(a),
            _ => None,
        }
    }
}

impl FromValue for Vec<bool> {
    fn from_value(v: Value) -> Option<Self> {
        match v {
            Value::BoolArray(a) => Some(a),
            _ => None,
        }
    }
}

impl<T: FromValue> FromValue for Option<T> {
    fn from_value(v: Value) -> Option<Self> {
        if v.is_null() {
            Some(None)
        } else {
            Some(T::from_value(v))
        }
    }
}

// ============================================
// DbValue helpers (factory functions matching TS API)
// ============================================

pub fn db_null() -> Value {
    Value::Null
}

pub fn db_raw(expr: &str) -> Value {
    Value::Raw(expr.to_string())
}

/// Parent column reference for correlated subqueries.
/// Produces a `Value::Raw` with the qualified column name (e.g. `organizations.id`),
/// which is embedded directly into SQL without parameter binding.
///
/// Use in subqueries when the inner query needs to reference a column from the outer query:
///
/// ```ignore
/// // Find users in orgs that have posts
/// User.find(handler, [in_subquery(
///     "id",
///     "SELECT user_id FROM posts WHERE org_id = ?",
///     [parent_ref("organizations.id")],
/// )])
/// ```
///
/// Equivalent to `db_raw(column)` but documents the intent for correlated subqueries.
pub fn parent_ref(column: &str) -> Value {
    Value::Raw(column.to_string())
}

pub fn db_immediate(expr: &str) -> Value {
    Value::Immediate(expr.to_string())
}

pub fn db_now() -> Value {
    Value::Immediate("NOW()".to_string())
}

pub fn db_true() -> Value {
    Value::Bool(true)
}

pub fn db_false() -> Value {
    Value::Bool(false)
}

/// Create a type-casted value (e.g., `?::uuid` in PostgreSQL).
pub fn db_cast(value: impl Into<Value>, sql_type: &str) -> Value {
    Value::Cast {
        value: Box::new(value.into()),
        sql_type: sql_type.to_string(),
    }
}

/// Create an IN clause from a Vec of values.
pub fn db_in(values: Vec<Value>) -> Value {
    Value::ValueArray(values)
}

/// Create an IS NOT NULL condition.
pub fn db_not_null() -> Value {
    Value::NotNull
}

/// Create a dynamic SQL expression with bind parameters.
pub fn db_dynamic(sql: &str, params: Vec<Value>) -> Value {
    Value::Dynamic {
        sql: sql.to_string(),
        params,
    }
}

/// Create a UUID type-cast value (`?::uuid` in PostgreSQL).
pub fn db_uuid(value: &str) -> Value {
    Value::Cast {
        value: Box::new(Value::Text(value.to_string())),
        sql_type: "uuid".to_string(),
    }
}

/// Create an IN clause with type cast for each value.
pub fn db_cast_in(values: &[&str], sql_type: &str) -> Value {
    Value::CastArray {
        values: values.iter().map(|v| Value::Text(v.to_string())).collect(),
        sql_type: sql_type.to_string(),
    }
}

/// Create an IN clause with UUID type cast.
pub fn db_uuid_in(values: &[&str]) -> Value {
    db_cast_in(values, "uuid")
}

/// Create a tuple IN condition: `(col1, col2) IN ((v1, v2), (v3, v4))`.
///
/// For PostgreSQL, prefer `db_tuple_in_pg` which uses UNNEST for better performance
/// (constant parameter count regardless of row count).
pub fn db_tuple_in(columns: &[&str], rows: Vec<Vec<Value>>) -> crate::condition::Condition {
    let col_list = columns.join(", ");
    let mut params = Vec::new();
    let row_strs: Vec<String> = rows
        .iter()
        .map(|row| {
            let placeholders: Vec<&str> = row
                .iter()
                .map(|v| {
                    params.push(v.clone());
                    "?"
                })
                .collect();
            format!("({})", placeholders.join(", "))
        })
        .collect();

    crate::condition::Condition::Raw {
        sql: format!("({}) IN ({})", col_list, row_strs.join(", ")),
        params,
    }
}

/// PostgreSQL-optimized tuple IN using UNNEST.
///
/// Generates: `(col1, col2) IN (SELECT * FROM UNNEST(?::type1[], ?::type2[]))`
///
/// Only 2 array parameters regardless of row count (vs 2*N individual parameters
/// with `db_tuple_in`). Requires `column_types` for PostgreSQL type annotations.
///
/// Falls back to `1 = 0` for empty rows.
pub fn db_tuple_in_pg(
    columns: &[&str],
    rows: Vec<Vec<Value>>,
    column_types: &[&str],
) -> crate::condition::Condition {
    if rows.is_empty() {
        return crate::condition::Condition::Raw {
            sql: "1 = 0".to_string(),
            params: vec![],
        };
    }

    let num_cols = columns.len();
    let mut col_arrays: Vec<Vec<Value>> = vec![Vec::with_capacity(rows.len()); num_cols];
    for row in &rows {
        for (i, val) in row.iter().enumerate() {
            col_arrays[i].push(val.clone());
        }
    }

    let col_list = columns.join(", ");
    let unnest_parts: Vec<String> = column_types
        .iter()
        .map(|ct| format!("?::{}[]", ct))
        .collect();
    let sql = format!(
        "({}) IN (SELECT * FROM UNNEST({}))",
        col_list,
        unnest_parts.join(", ")
    );

    let params: Vec<Value> = col_arrays
        .into_iter()
        .map(|arr| match arr.first() {
            Some(Value::Int(_)) => Value::IntArray(
                arr.into_iter()
                    .map(|v| match v {
                        Value::Int(i) => i,
                        _ => 0,
                    })
                    .collect(),
            ),
            Some(Value::Float(_)) => Value::FloatArray(
                arr.into_iter()
                    .map(|v| match v {
                        Value::Float(f) => f,
                        _ => 0.0,
                    })
                    .collect(),
            ),
            Some(Value::Bool(_)) => Value::BoolArray(
                arr.into_iter()
                    .map(|v| match v {
                        Value::Bool(b) => b,
                        _ => false,
                    })
                    .collect(),
            ),
            _ => Value::TextArray(
                arr.into_iter()
                    .map(|v| match v {
                        Value::Text(s) => s,
                        _ => format!("{:?}", v),
                    })
                    .collect(),
            ),
        })
        .collect();

    crate::condition::Condition::Raw { sql, params }
}

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

    // ========== FromValue: Bool ==========
    #[test]
    fn test_from_value_bool_true() {
        assert_eq!(bool::from_value(Value::Bool(true)), Some(true));
        assert_eq!(bool::from_value(Value::Bool(false)), Some(false));
    }

    #[test]
    fn test_from_value_bool_from_int() {
        assert_eq!(bool::from_value(Value::Int(1)), Some(true));
        assert_eq!(bool::from_value(Value::Int(0)), Some(false));
    }

    #[test]
    fn test_from_value_option_bool_null() {
        assert_eq!(Option::<bool>::from_value(Value::Null), Some(None));
    }

    // ========== FromValue: Int ==========
    #[test]
    fn test_from_value_int() {
        assert_eq!(i32::from_value(Value::Int(42)), Some(42));
        assert_eq!(i64::from_value(Value::Int(42)), Some(42));
    }

    #[test]
    fn test_from_value_int_overflow_i32() {
        assert_eq!(i32::from_value(Value::Int(i64::MAX)), None);
    }

    // ========== FromValue: Float ==========
    #[test]
    fn test_from_value_float() {
        assert_eq!(f64::from_value(Value::Float(1.23)), Some(1.23));
    }

    #[test]
    fn test_from_value_float_from_int() {
        assert_eq!(f64::from_value(Value::Int(42)), Some(42.0));
    }

    // ========== FromValue: String ==========
    #[test]
    fn test_from_value_string() {
        assert_eq!(
            String::from_value(Value::Text("hello".to_string())),
            Some("hello".to_string())
        );
    }

    // ========== FromValue: Bytes ==========
    #[test]
    fn test_from_value_bytes() {
        let bytes = vec![1u8, 2, 3];
        assert_eq!(
            Vec::<u8>::from_value(Value::Bytes(bytes.clone())),
            Some(bytes)
        );
    }

    // ========== FromValue: Option<T> ==========
    #[test]
    fn test_from_value_option_string_null() {
        assert_eq!(Option::<String>::from_value(Value::Null), Some(None));
    }

    #[test]
    fn test_from_value_option_string_some() {
        assert_eq!(
            Option::<String>::from_value(Value::Text("x".to_string())),
            Some(Some("x".to_string()))
        );
    }

    // ========== FromValue: Error cases ==========
    #[test]
    fn test_from_value_bool_to_string_err() {
        assert_eq!(String::from_value(Value::Bool(true)), None);
    }

    #[test]
    fn test_from_value_int_to_string_err() {
        assert_eq!(String::from_value(Value::Int(42)), None);
    }

    // ========== FromValue: Uuid ==========
    #[test]
    fn test_from_value_uuid() {
        let uuid_str = "550e8400-e29b-41d4-a716-446655440000";
        let u = uuid::Uuid::parse_str(uuid_str).unwrap();
        assert_eq!(uuid::Uuid::from_value(Value::Uuid(u)), Some(u));
    }

    #[test]
    fn test_from_value_uuid_from_text() {
        let uuid_str = "550e8400-e29b-41d4-a716-446655440000";
        let u = uuid::Uuid::parse_str(uuid_str).unwrap();
        assert_eq!(
            uuid::Uuid::from_value(Value::Text(uuid_str.to_string())),
            Some(u)
        );
    }

    // ========== FromValue: Chrono ==========
    #[test]
    fn test_from_value_naive_datetime() {
        let dt = chrono::NaiveDate::from_ymd_opt(2024, 1, 15)
            .unwrap()
            .and_hms_opt(10, 30, 0)
            .unwrap();
        assert_eq!(
            chrono::NaiveDateTime::from_value(Value::Timestamp(dt)),
            Some(dt)
        );
    }

    #[test]
    fn test_from_value_naive_date() {
        let d = chrono::NaiveDate::from_ymd_opt(2024, 1, 15).unwrap();
        assert_eq!(chrono::NaiveDate::from_value(Value::Date(d)), Some(d));
    }

    // ========== From<T> for Value ==========
    #[test]
    fn test_value_from_types() {
        assert_eq!(Value::from(42i32), Value::Int(42));
        assert_eq!(Value::from(42i64), Value::Int(42));
        assert_eq!(Value::from(1.23f64), Value::Float(1.23));
        match Value::from(1.23f32) {
            Value::Float(f) => assert!((f - 1.23).abs() < 1e-6),
            _ => panic!("Expected Float"),
        }
        assert_eq!(Value::from("hello"), Value::Text("hello".to_string()));
        assert_eq!(
            Value::from("hello".to_string()),
            Value::Text("hello".to_string())
        );
        assert_eq!(
            Value::from(&"hello".to_string()),
            Value::Text("hello".to_string())
        );
        assert_eq!(Value::from(true), Value::Bool(true));
        assert_eq!(Value::from(None::<i64>), Value::Null);
        assert_eq!(Value::from(Some(42i64)), Value::Int(42));
    }

    #[test]
    fn test_value_from_vec_types() {
        assert_eq!(
            Value::from(vec![1i64, 2, 3]),
            Value::IntArray(vec![1, 2, 3])
        );
        assert_eq!(
            Value::from(vec!["a".to_string(), "b".to_string()]),
            Value::TextArray(vec!["a".to_string(), "b".to_string()])
        );
        assert_eq!(
            Value::from(vec![true, false]),
            Value::BoolArray(vec![true, false])
        );
    }

    #[test]
    fn test_value_is_null() {
        assert!(Value::Null.is_null());
        assert!(!Value::Int(1).is_null());
        assert!(!Value::NotNull.is_null());
    }

    // ========== Helper functions ==========
    #[test]
    fn test_db_null() {
        assert_eq!(db_null(), Value::Null);
    }

    #[test]
    fn test_db_raw() {
        assert_eq!(db_raw("NOW()"), Value::Raw("NOW()".to_string()));
    }

    #[test]
    fn test_parent_ref() {
        assert_eq!(
            parent_ref("organizations.id"),
            Value::Raw("organizations.id".to_string())
        );
        assert_eq!(parent_ref("users.id"), Value::Raw("users.id".to_string()));
    }

    #[test]
    fn test_db_immediate() {
        assert_eq!(db_immediate("42"), Value::Immediate("42".to_string()));
    }

    #[test]
    fn test_db_now() {
        assert_eq!(db_now(), Value::Immediate("NOW()".to_string()));
    }

    #[test]
    fn test_db_true() {
        assert_eq!(db_true(), Value::Bool(true));
    }

    #[test]
    fn test_db_false() {
        assert_eq!(db_false(), Value::Bool(false));
    }

    #[test]
    fn test_db_in_mixed_types() {
        let val = db_in(vec![
            Value::Int(1),
            Value::Text("hello".into()),
            Value::Bool(true),
        ]);
        match val {
            Value::ValueArray(arr) => {
                assert_eq!(arr.len(), 3);
                assert_eq!(arr[0], Value::Int(1));
                assert_eq!(arr[1], Value::Text("hello".into()));
                assert_eq!(arr[2], Value::Bool(true));
            }
            _ => panic!("Expected ValueArray"),
        }
    }

    #[test]
    fn test_db_in_empty() {
        let val = db_in(vec![]);
        match val {
            Value::ValueArray(arr) => assert!(arr.is_empty()),
            _ => panic!("Expected ValueArray"),
        }
    }

    #[test]
    fn test_db_not_null() {
        assert_eq!(db_not_null(), Value::NotNull);
    }

    #[test]
    fn test_db_dynamic() {
        let val = db_dynamic("LOWER(?)", vec![Value::Text("HELLO".into())]);
        match val {
            Value::Dynamic { sql, params } => {
                assert_eq!(sql, "LOWER(?)");
                assert_eq!(params, vec![Value::Text("HELLO".into())]);
            }
            _ => panic!("Expected Dynamic"),
        }
    }

    #[test]
    fn test_db_uuid() {
        let val = db_uuid("550e8400-e29b-41d4-a716-446655440000");
        match val {
            Value::Cast { value, sql_type } => {
                assert_eq!(
                    *value,
                    Value::Text("550e8400-e29b-41d4-a716-446655440000".into())
                );
                assert_eq!(sql_type, "uuid");
            }
            _ => panic!("Expected Cast"),
        }
    }

    #[test]
    fn test_db_cast_in() {
        let val = db_cast_in(&["a", "b", "c"], "uuid");
        match val {
            Value::CastArray { values, sql_type } => {
                assert_eq!(values.len(), 3);
                assert_eq!(sql_type, "uuid");
            }
            _ => panic!("Expected CastArray"),
        }
    }

    #[test]
    fn test_db_uuid_in() {
        let val = db_uuid_in(&["id1", "id2"]);
        match val {
            Value::CastArray { values, sql_type } => {
                assert_eq!(values.len(), 2);
                assert_eq!(sql_type, "uuid");
            }
            _ => panic!("Expected CastArray"),
        }
    }

    #[test]
    fn test_db_cast_fixed() {
        let val = db_cast("test", "text");
        match val {
            Value::Cast { value, sql_type } => {
                assert_eq!(*value, Value::Text("test".into()));
                assert_eq!(sql_type, "text");
            }
            _ => panic!("Expected Cast"),
        }
    }

    #[test]
    fn test_db_tuple_in() {
        let cond = db_tuple_in(
            &["col1", "col2"],
            vec![
                vec![Value::Int(1), Value::Text("a".into())],
                vec![Value::Int(2), Value::Text("b".into())],
            ],
        );
        match cond {
            crate::condition::Condition::Raw { sql, params } => {
                assert!(sql.contains("(col1, col2) IN"));
                assert_eq!(params.len(), 4);
            }
            _ => panic!("Expected Raw condition"),
        }
    }

    #[test]
    fn test_db_tuple_in_pg_basic() {
        let cond = db_tuple_in_pg(
            &["tenant_id", "user_id"],
            vec![
                vec![Value::Int(1), Value::Int(10)],
                vec![Value::Int(2), Value::Int(20)],
                vec![Value::Int(3), Value::Int(30)],
            ],
            &["int8", "int8"],
        );
        match cond {
            crate::condition::Condition::Raw { sql, params } => {
                assert_eq!(
                    sql,
                    "(tenant_id, user_id) IN (SELECT * FROM UNNEST(?::int8[], ?::int8[]))"
                );
                assert_eq!(params.len(), 2);
                assert_eq!(params[0], Value::IntArray(vec![1, 2, 3]));
                assert_eq!(params[1], Value::IntArray(vec![10, 20, 30]));
            }
            _ => panic!("Expected Raw condition"),
        }
    }

    #[test]
    fn test_db_tuple_in_pg_mixed_types() {
        let cond = db_tuple_in_pg(
            &["id", "name"],
            vec![
                vec![Value::Int(1), Value::Text("alice".into())],
                vec![Value::Int(2), Value::Text("bob".into())],
            ],
            &["int8", "text"],
        );
        match cond {
            crate::condition::Condition::Raw { sql, params } => {
                assert_eq!(
                    sql,
                    "(id, name) IN (SELECT * FROM UNNEST(?::int8[], ?::text[]))"
                );
                assert_eq!(params.len(), 2);
                assert_eq!(params[0], Value::IntArray(vec![1, 2]));
                assert_eq!(
                    params[1],
                    Value::TextArray(vec!["alice".into(), "bob".into()])
                );
            }
            _ => panic!("Expected Raw condition"),
        }
    }

    #[test]
    fn test_db_tuple_in_pg_empty() {
        let cond = db_tuple_in_pg(&["col1", "col2"], vec![], &["int8", "int8"]);
        match cond {
            crate::condition::Condition::Raw { sql, params } => {
                assert_eq!(sql, "1 = 0");
                assert!(params.is_empty());
            }
            _ => panic!("Expected Raw condition"),
        }
    }
}
