//! Value conversion traits for `SQLite` types
//!
//! This module provides the `FromSQLiteValue` trait for converting `SQLite` values
//! to Rust types, and row capability traits for unified access across drivers.

use crate::prelude::*;
use crate::values::{OwnedSQLiteValue, SQLiteValue, SQLiteValueRef};
use drizzle_core::conv::checked_float_to_int;
use drizzle_core::error::DrizzleError;

/// Trait for custom Rust types that map to a `SQLite` column.
///
/// Generated by `#[derive(SQLiteEnum)]`. Implement this trait manually for
/// custom wrappers that should be usable directly in table schemas.
///
/// # Associated Constants
///
/// - `SQLType`: The Drizzle SQL type marker used for typed expressions
/// - `SQL_TYPE`: The `SQLite` column affinity (`"TEXT"`, `"INTEGER"`, `"BLOB"`, etc.)
///
/// # Required Methods
///
/// - `decode`: Convert a borrowed `SQLite` value read from the database into `Self`
/// - `encode`: Convert self to a `SQLiteValue` for insertion/updates
///
/// The blanket `From<Self> for SQLiteValue` owns the encoded value because
/// insert/update models may store SQL fragments after the source value is
/// dropped. Call `encode()` directly when you need an immediate borrowed value.
/// Override `encode_owned()` when consuming `self` can avoid cloning owned data.
#[diagnostic::on_unimplemented(
    message = "`{Self}` cannot be used as a SQLite column type",
    note = "add #[derive(SQLiteEnum)] for enum types, or use a supported primitive type"
)]
pub trait DrizzleSQLiteColumn: Sized {
    /// Drizzle SQL type marker for this column.
    ///
    /// Use one of the built-in SQLite markers, such as `Text`, `Integer`,
    /// `Blob`, `Real`, `Numeric`, or `Any`.
    type SQLType: drizzle_core::types::DataType;

    /// `SQLite` column affinity: `"TEXT"` or `"INTEGER"`
    const SQL_TYPE: &'static str;

    /// Decode a borrowed `SQLite` value read from the database into `Self`.
    ///
    /// Implementations should reject unsupported storage classes with
    /// [`DrizzleError::ConversionError`].
    ///
    /// # Errors
    ///
    /// Returns [`DrizzleError::ConversionError`] if `value` cannot be decoded
    /// as this custom column type.
    fn decode(value: SQLiteValueRef<'_>) -> Result<Self, DrizzleError>;

    /// Convert self to a `SQLiteValue` for insertion/updates.
    fn encode(&self) -> SQLiteValue<'_>;

    /// Convert self to an owned `SQLite` value for stored bind parameters.
    ///
    /// The default implementation owns the borrowed result of [`encode`](Self::encode).
    /// Override this for wrappers that can move an internal string or byte buffer
    /// directly into the SQL parameter.
    fn encode_owned(self) -> OwnedSQLiteValue {
        self.encode().into_owned()
    }
}

impl<'a, T> From<T> for SQLiteValue<'a>
where
    T: DrizzleSQLiteColumn,
{
    fn from(value: T) -> Self {
        value.encode_owned().into()
    }
}

/// Trait for types that can be converted from `SQLite` values.
///
/// `SQLite` has 5 storage classes: NULL, INTEGER, REAL, TEXT, BLOB.
/// This trait provides conversion methods for each type.
///
/// # Implementation Notes
///
/// - Implement the methods that make sense for your type
/// - Return `Err` for unsupported conversions
/// - `SQLiteEnum` derive automatically implements this trait
pub trait FromSQLiteValue: Sized {
    /// Convert from a 64-bit integer value.
    ///
    /// # Errors
    ///
    /// Returns [`DrizzleError::ConversionError`] if `value` cannot be represented as `Self`.
    fn from_sqlite_integer(value: i64) -> Result<Self, DrizzleError>;

    /// Convert from a text/string value.
    ///
    /// # Errors
    ///
    /// Returns [`DrizzleError::ConversionError`] if `value` cannot be parsed or represented as `Self`.
    fn from_sqlite_text(value: &str) -> Result<Self, DrizzleError>;

    /// Convert from a real/float value.
    ///
    /// # Errors
    ///
    /// Returns [`DrizzleError::ConversionError`] if `value` cannot be represented as `Self`.
    fn from_sqlite_real(value: f64) -> Result<Self, DrizzleError>;

    /// Convert from a blob/binary value.
    ///
    /// # Errors
    ///
    /// Returns [`DrizzleError::ConversionError`] if `value` cannot be interpreted as `Self`.
    fn from_sqlite_blob(value: &[u8]) -> Result<Self, DrizzleError>;

    /// Convert from a borrowed SQLite storage value.
    ///
    /// # Errors
    ///
    /// Returns [`DrizzleError::ConversionError`] if `value` cannot be
    /// represented as `Self`.
    fn from_sqlite_ref(value: SQLiteValueRef<'_>) -> Result<Self, DrizzleError> {
        match value {
            SQLiteValueRef::Integer(value) => Self::from_sqlite_integer(value),
            SQLiteValueRef::Text(value) => Self::from_sqlite_text(value),
            SQLiteValueRef::Real(value) => Self::from_sqlite_real(value),
            SQLiteValueRef::Blob(value) => Self::from_sqlite_blob(value),
            SQLiteValueRef::Null => Self::from_sqlite_null(),
        }
    }

    /// Convert from a NULL value (default returns error).
    ///
    /// # Errors
    ///
    /// The default implementation always returns [`DrizzleError::ConversionError`];
    /// override for nullable-aware types (e.g. [`Option`]).
    fn from_sqlite_null() -> Result<Self, DrizzleError> {
        Err(DrizzleError::ConversionError(
            "unexpected NULL value".into(),
        ))
    }

    /// Helper function to convert from rusqlite's `ValueRef` using `FromSQLiteValue`.
    ///
    /// # Errors
    ///
    /// Returns [`DrizzleError::ConversionError`] if the underlying conversion fails,
    /// including UTF-8 decoding failures for TEXT values.
    #[cfg(feature = "rusqlite")]
    fn from_value_ref(value: ::rusqlite::types::ValueRef<'_>) -> Result<Self, DrizzleError> {
        let value = SQLiteValueRef::try_from_rusqlite_value_ref(value)?;
        Self::from_sqlite_ref(value)
    }
}

impl<T> FromSQLiteValue for T
where
    T: DrizzleSQLiteColumn,
{
    fn from_sqlite_ref(value: SQLiteValueRef<'_>) -> Result<Self, DrizzleError> {
        T::decode(value)
    }

    fn from_sqlite_integer(value: i64) -> Result<Self, DrizzleError> {
        T::decode(SQLiteValueRef::Integer(value))
    }

    fn from_sqlite_text(value: &str) -> Result<Self, DrizzleError> {
        T::decode(SQLiteValueRef::Text(value))
    }

    fn from_sqlite_real(value: f64) -> Result<Self, DrizzleError> {
        T::decode(SQLiteValueRef::Real(value))
    }

    fn from_sqlite_blob(value: &[u8]) -> Result<Self, DrizzleError> {
        T::decode(SQLiteValueRef::Blob(value))
    }

    fn from_sqlite_null() -> Result<Self, DrizzleError> {
        T::decode(SQLiteValueRef::Null)
    }
}

/// Row capability for index-based extraction.
pub trait DrizzleRowByIndex {
    /// Get a column value by index.
    ///
    /// # Errors
    ///
    /// Returns [`DrizzleError`] if the index is out of range or the column value
    /// cannot be converted to `T`.
    fn get_column<T: FromSQLiteValue>(&self, idx: usize) -> Result<T, DrizzleError>;
}

/// Optional row capability for name-based extraction.
pub trait DrizzleRowByName: DrizzleRowByIndex {
    /// Get a column value by name.
    ///
    /// # Errors
    ///
    /// Returns [`DrizzleError`] if the name does not resolve to a column or the
    /// column value cannot be converted to `T`.
    fn get_column_by_name<T: FromSQLiteValue>(&self, name: &str) -> Result<T, DrizzleError>;
}

// =============================================================================
// Primitive implementations
// =============================================================================

/// Macro to implement `FromSQLiteValue` for integer types (handles narrowing conversion from i64)
macro_rules! impl_from_sqlite_value_int {
    // Special case for i64 - no conversion needed
    (i64) => {
        impl FromSQLiteValue for i64 {
            fn from_sqlite_integer(value: i64) -> Result<Self, DrizzleError> {
                Ok(value)
            }

            fn from_sqlite_text(value: &str) -> Result<Self, DrizzleError> {
                value.parse().map_err(|e| {
                    DrizzleError::ConversionError(format!("cannot parse '{}' as i64: {}", value, e).into())
                })
            }

            fn from_sqlite_real(value: f64) -> Result<Self, DrizzleError> {
                checked_float_to_int(value, "i64")
            }

            fn from_sqlite_blob(_value: &[u8]) -> Result<Self, DrizzleError> {
                Err(DrizzleError::ConversionError("cannot convert BLOB to i64".into()))
            }
        }
    };
    // General case for other integer types - uses try_into for narrowing
    ($($ty:ty),+ $(,)?) => {
        $(
            impl FromSQLiteValue for $ty {
                fn from_sqlite_integer(value: i64) -> Result<Self, DrizzleError> {
                    value.try_into().map_err(|e| {
                        DrizzleError::ConversionError(
                            format!("i64 {} out of range for {}: {}", value, stringify!($ty), e).into(),
                        )
                    })
                }

                fn from_sqlite_text(value: &str) -> Result<Self, DrizzleError> {
                    value.parse().map_err(|e| {
                        DrizzleError::ConversionError(
                            format!("cannot parse '{}' as {}: {}", value, stringify!($ty), e).into()
                        )
                    })
                }

                fn from_sqlite_real(value: f64) -> Result<Self, DrizzleError> {
                    checked_float_to_int(value, stringify!($ty))
                }

                fn from_sqlite_blob(_value: &[u8]) -> Result<Self, DrizzleError> {
                    Err(DrizzleError::ConversionError(
                        concat!("cannot convert BLOB to ", stringify!($ty)).into()
                    ))
                }
            }
        )+
    };
}

/// IEEE 754 `f64` representation of an `i64` built from two exact halves via
/// [`From`], matching the semantics of `i as f64` without an `as`-cast.
#[inline]
fn i64_to_f64(i: i64) -> f64 {
    let high = i32::try_from(i >> 32).expect("sign-extended high word fits in i32");
    let low = u32::try_from(i & 0xFFFF_FFFF).expect("masked low word fits in u32");
    f64::from(high) * 4_294_967_296.0_f64 + f64::from(low)
}

// Integer types
impl_from_sqlite_value_int!(i64);
impl_from_sqlite_value_int!(i8, i16, i32, isize, u8, u16, u32, u64, usize);

// f64 — `i64` widening via split-halves is exact in the mantissa range and
// matches direct `as` cast beyond it; no cast is needed for f64→f64.
impl FromSQLiteValue for f64 {
    fn from_sqlite_integer(value: i64) -> Result<Self, DrizzleError> {
        Ok(i64_to_f64(value))
    }

    fn from_sqlite_text(value: &str) -> Result<Self, DrizzleError> {
        value.parse().map_err(|e| {
            DrizzleError::ConversionError(format!("cannot parse '{value}' as f64: {e}").into())
        })
    }

    fn from_sqlite_real(value: f64) -> Result<Self, DrizzleError> {
        Ok(value)
    }

    fn from_sqlite_blob(_value: &[u8]) -> Result<Self, DrizzleError> {
        Err(DrizzleError::ConversionError(
            "cannot convert BLOB to f64".into(),
        ))
    }
}

fn narrow_to_f32(value: f64) -> Result<f32, DrizzleError> {
    // Round-trip through a decimal string so IEEE 754 round-to-nearest is
    // applied explicitly (and clippy doesn't flag the conversion as a lossy
    // numeric cast).
    format!("{value}").parse::<f32>().map_err(|e| {
        DrizzleError::ConversionError(format!("cannot convert REAL {value} to f32: {e}").into())
    })
}

// f32 — narrowing from both `i64` and `f64` is intentionally lossy; SQLite
// round-trips the value through `f64` storage so we defer to the standard
// IEEE 754 narrowing conversion. These casts are the only correct semantics.
impl FromSQLiteValue for f32 {
    fn from_sqlite_integer(value: i64) -> Result<Self, DrizzleError> {
        // Go via f64 (exact) then narrow to f32.
        narrow_to_f32(i64_to_f64(value))
    }

    fn from_sqlite_text(value: &str) -> Result<Self, DrizzleError> {
        value.parse().map_err(|e| {
            DrizzleError::ConversionError(format!("cannot parse '{value}' as f32: {e}").into())
        })
    }

    fn from_sqlite_real(value: f64) -> Result<Self, DrizzleError> {
        narrow_to_f32(value)
    }

    fn from_sqlite_blob(_value: &[u8]) -> Result<Self, DrizzleError> {
        Err(DrizzleError::ConversionError(
            "cannot convert BLOB to f32".into(),
        ))
    }
}

impl FromSQLiteValue for bool {
    fn from_sqlite_integer(value: i64) -> Result<Self, DrizzleError> {
        Ok(value != 0)
    }

    fn from_sqlite_text(value: &str) -> Result<Self, DrizzleError> {
        match value.to_lowercase().as_str() {
            "true" | "1" | "yes" | "on" => Ok(true),
            "false" | "0" | "no" | "off" => Ok(false),
            _ => Err(DrizzleError::ConversionError(
                format!("cannot parse '{value}' as bool").into(),
            )),
        }
    }

    fn from_sqlite_real(value: f64) -> Result<Self, DrizzleError> {
        Ok(value != 0.0)
    }

    fn from_sqlite_blob(_value: &[u8]) -> Result<Self, DrizzleError> {
        Err(DrizzleError::ConversionError(
            "cannot convert BLOB to bool".into(),
        ))
    }
}

impl FromSQLiteValue for String {
    fn from_sqlite_integer(value: i64) -> Result<Self, DrizzleError> {
        Ok(value.to_string())
    }

    fn from_sqlite_text(value: &str) -> Result<Self, DrizzleError> {
        Ok(value.to_string())
    }

    fn from_sqlite_real(value: f64) -> Result<Self, DrizzleError> {
        Ok(value.to_string())
    }

    fn from_sqlite_blob(value: &[u8]) -> Result<Self, DrizzleError> {
        Self::from_utf8(value.to_vec()).map_err(|e| {
            DrizzleError::ConversionError(format!("invalid UTF-8 in BLOB: {e}").into())
        })
    }
}

impl FromSQLiteValue for compact_str::CompactString {
    fn from_sqlite_integer(value: i64) -> Result<Self, DrizzleError> {
        Ok(Self::new(value.to_string()))
    }

    fn from_sqlite_text(value: &str) -> Result<Self, DrizzleError> {
        Ok(Self::new(value))
    }

    fn from_sqlite_real(value: f64) -> Result<Self, DrizzleError> {
        Ok(Self::new(value.to_string()))
    }

    fn from_sqlite_blob(value: &[u8]) -> Result<Self, DrizzleError> {
        let s = String::from_utf8(value.to_vec()).map_err(|e| {
            DrizzleError::ConversionError(format!("invalid UTF-8 in BLOB: {e}").into())
        })?;
        Ok(Self::new(s))
    }
}

impl FromSQLiteValue for Box<String> {
    fn from_sqlite_integer(value: i64) -> Result<Self, DrizzleError> {
        String::from_sqlite_integer(value).map(Self::new)
    }

    fn from_sqlite_text(value: &str) -> Result<Self, DrizzleError> {
        String::from_sqlite_text(value).map(Self::new)
    }

    fn from_sqlite_real(value: f64) -> Result<Self, DrizzleError> {
        String::from_sqlite_real(value).map(Self::new)
    }

    fn from_sqlite_blob(value: &[u8]) -> Result<Self, DrizzleError> {
        String::from_sqlite_blob(value).map(Self::new)
    }
}

impl FromSQLiteValue for Rc<String> {
    fn from_sqlite_integer(value: i64) -> Result<Self, DrizzleError> {
        String::from_sqlite_integer(value).map(Self::new)
    }

    fn from_sqlite_text(value: &str) -> Result<Self, DrizzleError> {
        String::from_sqlite_text(value).map(Self::new)
    }

    fn from_sqlite_real(value: f64) -> Result<Self, DrizzleError> {
        String::from_sqlite_real(value).map(Self::new)
    }

    fn from_sqlite_blob(value: &[u8]) -> Result<Self, DrizzleError> {
        String::from_sqlite_blob(value).map(Self::new)
    }
}

impl FromSQLiteValue for Arc<String> {
    fn from_sqlite_integer(value: i64) -> Result<Self, DrizzleError> {
        String::from_sqlite_integer(value).map(Self::new)
    }

    fn from_sqlite_text(value: &str) -> Result<Self, DrizzleError> {
        String::from_sqlite_text(value).map(Self::new)
    }

    fn from_sqlite_real(value: f64) -> Result<Self, DrizzleError> {
        String::from_sqlite_real(value).map(Self::new)
    }

    fn from_sqlite_blob(value: &[u8]) -> Result<Self, DrizzleError> {
        String::from_sqlite_blob(value).map(Self::new)
    }
}

impl FromSQLiteValue for Box<str> {
    fn from_sqlite_integer(value: i64) -> Result<Self, DrizzleError> {
        String::from_sqlite_integer(value).map(String::into_boxed_str)
    }

    fn from_sqlite_text(value: &str) -> Result<Self, DrizzleError> {
        String::from_sqlite_text(value).map(String::into_boxed_str)
    }

    fn from_sqlite_real(value: f64) -> Result<Self, DrizzleError> {
        String::from_sqlite_real(value).map(String::into_boxed_str)
    }

    fn from_sqlite_blob(value: &[u8]) -> Result<Self, DrizzleError> {
        String::from_sqlite_blob(value).map(String::into_boxed_str)
    }
}

impl FromSQLiteValue for Rc<str> {
    fn from_sqlite_integer(value: i64) -> Result<Self, DrizzleError> {
        String::from_sqlite_integer(value).map(Self::from)
    }

    fn from_sqlite_text(value: &str) -> Result<Self, DrizzleError> {
        String::from_sqlite_text(value).map(Self::from)
    }

    fn from_sqlite_real(value: f64) -> Result<Self, DrizzleError> {
        String::from_sqlite_real(value).map(Self::from)
    }

    fn from_sqlite_blob(value: &[u8]) -> Result<Self, DrizzleError> {
        String::from_sqlite_blob(value).map(Self::from)
    }
}

impl FromSQLiteValue for Arc<str> {
    fn from_sqlite_integer(value: i64) -> Result<Self, DrizzleError> {
        String::from_sqlite_integer(value).map(Self::from)
    }

    fn from_sqlite_text(value: &str) -> Result<Self, DrizzleError> {
        String::from_sqlite_text(value).map(Self::from)
    }

    fn from_sqlite_real(value: f64) -> Result<Self, DrizzleError> {
        String::from_sqlite_real(value).map(Self::from)
    }

    fn from_sqlite_blob(value: &[u8]) -> Result<Self, DrizzleError> {
        String::from_sqlite_blob(value).map(Self::from)
    }
}

impl FromSQLiteValue for Vec<u8> {
    fn from_sqlite_integer(value: i64) -> Result<Self, DrizzleError> {
        Ok(value.to_le_bytes().to_vec())
    }

    fn from_sqlite_text(value: &str) -> Result<Self, DrizzleError> {
        Ok(value.as_bytes().to_vec())
    }

    fn from_sqlite_real(value: f64) -> Result<Self, DrizzleError> {
        Ok(value.to_le_bytes().to_vec())
    }

    fn from_sqlite_blob(value: &[u8]) -> Result<Self, DrizzleError> {
        Ok(value.to_vec())
    }
}

#[cfg(feature = "bytes")]
impl FromSQLiteValue for bytes::Bytes {
    fn from_sqlite_integer(value: i64) -> Result<Self, DrizzleError> {
        Vec::<u8>::from_sqlite_integer(value).map(Self::from)
    }

    fn from_sqlite_text(value: &str) -> Result<Self, DrizzleError> {
        Vec::<u8>::from_sqlite_text(value).map(Self::from)
    }

    fn from_sqlite_real(value: f64) -> Result<Self, DrizzleError> {
        Vec::<u8>::from_sqlite_real(value).map(Self::from)
    }

    fn from_sqlite_blob(value: &[u8]) -> Result<Self, DrizzleError> {
        Ok(Self::copy_from_slice(value))
    }
}

#[cfg(feature = "bytes")]
impl FromSQLiteValue for bytes::BytesMut {
    fn from_sqlite_integer(value: i64) -> Result<Self, DrizzleError> {
        Vec::<u8>::from_sqlite_integer(value).map(|v| Self::from(v.as_slice()))
    }

    fn from_sqlite_text(value: &str) -> Result<Self, DrizzleError> {
        Vec::<u8>::from_sqlite_text(value).map(|v| Self::from(v.as_slice()))
    }

    fn from_sqlite_real(value: f64) -> Result<Self, DrizzleError> {
        Vec::<u8>::from_sqlite_real(value).map(|v| Self::from(v.as_slice()))
    }

    fn from_sqlite_blob(value: &[u8]) -> Result<Self, DrizzleError> {
        Ok(Self::from(value))
    }
}

#[cfg(feature = "smallvec")]
impl<const N: usize> FromSQLiteValue for smallvec::SmallVec<[u8; N]> {
    fn from_sqlite_integer(_value: i64) -> Result<Self, DrizzleError> {
        Err(DrizzleError::ConversionError(
            "cannot convert INTEGER to SmallVec<u8>, use BLOB".into(),
        ))
    }

    fn from_sqlite_text(_value: &str) -> Result<Self, DrizzleError> {
        Err(DrizzleError::ConversionError(
            "cannot convert TEXT to SmallVec<u8>, use BLOB".into(),
        ))
    }

    fn from_sqlite_real(_value: f64) -> Result<Self, DrizzleError> {
        Err(DrizzleError::ConversionError(
            "cannot convert REAL to SmallVec<u8>, use BLOB".into(),
        ))
    }

    fn from_sqlite_blob(value: &[u8]) -> Result<Self, DrizzleError> {
        let mut out = Self::new();
        out.extend_from_slice(value);
        Ok(out)
    }
}

impl FromSQLiteValue for Box<Vec<u8>> {
    fn from_sqlite_integer(value: i64) -> Result<Self, DrizzleError> {
        Vec::<u8>::from_sqlite_integer(value).map(Self::new)
    }

    fn from_sqlite_text(value: &str) -> Result<Self, DrizzleError> {
        Vec::<u8>::from_sqlite_text(value).map(Self::new)
    }

    fn from_sqlite_real(value: f64) -> Result<Self, DrizzleError> {
        Vec::<u8>::from_sqlite_real(value).map(Self::new)
    }

    fn from_sqlite_blob(value: &[u8]) -> Result<Self, DrizzleError> {
        Vec::<u8>::from_sqlite_blob(value).map(Self::new)
    }
}

impl FromSQLiteValue for Rc<Vec<u8>> {
    fn from_sqlite_integer(value: i64) -> Result<Self, DrizzleError> {
        Vec::<u8>::from_sqlite_integer(value).map(Self::new)
    }

    fn from_sqlite_text(value: &str) -> Result<Self, DrizzleError> {
        Vec::<u8>::from_sqlite_text(value).map(Self::new)
    }

    fn from_sqlite_real(value: f64) -> Result<Self, DrizzleError> {
        Vec::<u8>::from_sqlite_real(value).map(Self::new)
    }

    fn from_sqlite_blob(value: &[u8]) -> Result<Self, DrizzleError> {
        Vec::<u8>::from_sqlite_blob(value).map(Self::new)
    }
}

impl FromSQLiteValue for Arc<Vec<u8>> {
    fn from_sqlite_integer(value: i64) -> Result<Self, DrizzleError> {
        Vec::<u8>::from_sqlite_integer(value).map(Self::new)
    }

    fn from_sqlite_text(value: &str) -> Result<Self, DrizzleError> {
        Vec::<u8>::from_sqlite_text(value).map(Self::new)
    }

    fn from_sqlite_real(value: f64) -> Result<Self, DrizzleError> {
        Vec::<u8>::from_sqlite_real(value).map(Self::new)
    }

    fn from_sqlite_blob(value: &[u8]) -> Result<Self, DrizzleError> {
        Vec::<u8>::from_sqlite_blob(value).map(Self::new)
    }
}

// Option<T> implementation - handles NULL values
impl<T: FromSQLiteValue> FromSQLiteValue for Option<T> {
    fn from_sqlite_integer(value: i64) -> Result<Self, DrizzleError> {
        T::from_sqlite_integer(value).map(Some)
    }

    fn from_sqlite_text(value: &str) -> Result<Self, DrizzleError> {
        T::from_sqlite_text(value).map(Some)
    }

    fn from_sqlite_real(value: f64) -> Result<Self, DrizzleError> {
        T::from_sqlite_real(value).map(Some)
    }

    fn from_sqlite_blob(value: &[u8]) -> Result<Self, DrizzleError> {
        T::from_sqlite_blob(value).map(Some)
    }

    fn from_sqlite_null() -> Result<Self, DrizzleError> {
        Ok(None)
    }
}

// =============================================================================
// Driver-specific DrizzleRow implementations
// =============================================================================

#[cfg(feature = "rusqlite")]
impl DrizzleRowByIndex for rusqlite::Row<'_> {
    fn get_column<T: FromSQLiteValue>(&self, idx: usize) -> Result<T, DrizzleError> {
        let value_ref = self.get_ref(idx)?;
        match value_ref {
            rusqlite::types::ValueRef::Integer(i) => T::from_sqlite_integer(i),
            rusqlite::types::ValueRef::Text(s) => {
                let s = core::str::from_utf8(s).map_err(|e| {
                    DrizzleError::ConversionError(format!("invalid UTF-8: {e}").into())
                })?;
                T::from_sqlite_text(s)
            }
            rusqlite::types::ValueRef::Real(r) => T::from_sqlite_real(r),
            rusqlite::types::ValueRef::Blob(b) => T::from_sqlite_blob(b),
            rusqlite::types::ValueRef::Null => T::from_sqlite_null(),
        }
    }
}

#[cfg(feature = "rusqlite")]
impl DrizzleRowByName for rusqlite::Row<'_> {
    fn get_column_by_name<T: FromSQLiteValue>(&self, name: &str) -> Result<T, DrizzleError> {
        let idx = self.as_ref().column_index(name)?;
        DrizzleRowByIndex::get_column(self, idx)
    }
}

#[cfg(feature = "libsql")]
impl DrizzleRowByIndex for libsql::Row {
    fn get_column<T: FromSQLiteValue>(&self, idx: usize) -> Result<T, DrizzleError> {
        let idx_i32 = i32::try_from(idx).map_err(|e| {
            DrizzleError::ConversionError(format!("column index {idx} out of range: {e}").into())
        })?;
        let value = self.get_value(idx_i32)?;
        match value {
            libsql::Value::Integer(i) => T::from_sqlite_integer(i),
            libsql::Value::Text(ref s) => T::from_sqlite_text(s),
            libsql::Value::Real(r) => T::from_sqlite_real(r),
            libsql::Value::Blob(ref b) => T::from_sqlite_blob(b),
            libsql::Value::Null => T::from_sqlite_null(),
        }
    }
}

#[cfg(feature = "libsql")]
impl DrizzleRowByName for libsql::Row {
    fn get_column_by_name<T: FromSQLiteValue>(&self, name: &str) -> Result<T, DrizzleError> {
        let idx = (0..self.column_count())
            .find(|&i| self.column_name(i) == Some(name))
            .ok_or_else(|| {
                DrizzleError::ConversionError(format!("column '{name}' not found").into())
            })?;

        let idx_usize = usize::try_from(idx).map_err(|e| {
            DrizzleError::ConversionError(format!("column index {idx} negative: {e}").into())
        })?;
        DrizzleRowByIndex::get_column(self, idx_usize)
    }
}

#[cfg(feature = "turso")]
impl DrizzleRowByIndex for turso::Row {
    fn get_column<T: FromSQLiteValue>(&self, idx: usize) -> Result<T, DrizzleError> {
        let value = self.get_value(idx)?;
        if value.is_null() {
            T::from_sqlite_null()
        } else if let Some(&i) = value.as_integer() {
            T::from_sqlite_integer(i)
        } else if let Some(s) = value.as_text() {
            T::from_sqlite_text(s)
        } else if let Some(&r) = value.as_real() {
            T::from_sqlite_real(r)
        } else if let Some(b) = value.as_blob() {
            T::from_sqlite_blob(b)
        } else {
            Err(DrizzleError::ConversionError(
                "unknown SQLite value type".into(),
            ))
        }
    }
}

// =============================================================================
// UUID support (when feature enabled)
// =============================================================================

#[cfg(feature = "uuid")]
impl FromSQLiteValue for uuid::Uuid {
    fn from_sqlite_integer(_value: i64) -> Result<Self, DrizzleError> {
        Err(DrizzleError::ConversionError(
            "cannot convert INTEGER to UUID".into(),
        ))
    }

    fn from_sqlite_text(value: &str) -> Result<Self, DrizzleError> {
        Self::parse_str(value).map_err(|e| {
            DrizzleError::ConversionError(format!("invalid UUID string '{value}': {e}").into())
        })
    }

    fn from_sqlite_real(_value: f64) -> Result<Self, DrizzleError> {
        Err(DrizzleError::ConversionError(
            "cannot convert REAL to UUID".into(),
        ))
    }

    fn from_sqlite_blob(value: &[u8]) -> Result<Self, DrizzleError> {
        Self::from_slice(value)
            .map_err(|e| DrizzleError::ConversionError(format!("invalid UUID bytes: {e}").into()))
    }
}

// =============================================================================
// Chrono date/time types (parse from ISO-8601 text)
// =============================================================================

#[cfg(feature = "chrono")]
impl FromSQLiteValue for chrono::NaiveDate {
    fn from_sqlite_integer(_value: i64) -> Result<Self, DrizzleError> {
        Err(DrizzleError::ConversionError(
            "cannot convert INTEGER to NaiveDate".into(),
        ))
    }

    fn from_sqlite_text(value: &str) -> Result<Self, DrizzleError> {
        value.parse().map_err(|e| {
            DrizzleError::ConversionError(
                format!("cannot parse '{value}' as NaiveDate: {e}").into(),
            )
        })
    }

    fn from_sqlite_real(_value: f64) -> Result<Self, DrizzleError> {
        Err(DrizzleError::ConversionError(
            "cannot convert REAL to NaiveDate".into(),
        ))
    }

    fn from_sqlite_blob(_value: &[u8]) -> Result<Self, DrizzleError> {
        Err(DrizzleError::ConversionError(
            "cannot convert BLOB to NaiveDate".into(),
        ))
    }
}

#[cfg(feature = "chrono")]
impl FromSQLiteValue for chrono::NaiveTime {
    fn from_sqlite_integer(_value: i64) -> Result<Self, DrizzleError> {
        Err(DrizzleError::ConversionError(
            "cannot convert INTEGER to NaiveTime".into(),
        ))
    }

    fn from_sqlite_text(value: &str) -> Result<Self, DrizzleError> {
        value.parse().map_err(|e| {
            DrizzleError::ConversionError(
                format!("cannot parse '{value}' as NaiveTime: {e}").into(),
            )
        })
    }

    fn from_sqlite_real(_value: f64) -> Result<Self, DrizzleError> {
        Err(DrizzleError::ConversionError(
            "cannot convert REAL to NaiveTime".into(),
        ))
    }

    fn from_sqlite_blob(_value: &[u8]) -> Result<Self, DrizzleError> {
        Err(DrizzleError::ConversionError(
            "cannot convert BLOB to NaiveTime".into(),
        ))
    }
}

#[cfg(feature = "chrono")]
impl FromSQLiteValue for chrono::NaiveDateTime {
    fn from_sqlite_integer(_value: i64) -> Result<Self, DrizzleError> {
        Err(DrizzleError::ConversionError(
            "cannot convert INTEGER to NaiveDateTime".into(),
        ))
    }

    fn from_sqlite_text(value: &str) -> Result<Self, DrizzleError> {
        value.parse().map_err(|e| {
            DrizzleError::ConversionError(
                format!("cannot parse '{value}' as NaiveDateTime: {e}").into(),
            )
        })
    }

    fn from_sqlite_real(_value: f64) -> Result<Self, DrizzleError> {
        Err(DrizzleError::ConversionError(
            "cannot convert REAL to NaiveDateTime".into(),
        ))
    }

    fn from_sqlite_blob(_value: &[u8]) -> Result<Self, DrizzleError> {
        Err(DrizzleError::ConversionError(
            "cannot convert BLOB to NaiveDateTime".into(),
        ))
    }
}

#[cfg(feature = "chrono")]
impl FromSQLiteValue for chrono::DateTime<chrono::FixedOffset> {
    fn from_sqlite_integer(_value: i64) -> Result<Self, DrizzleError> {
        Err(DrizzleError::ConversionError(
            "cannot convert INTEGER to DateTime<FixedOffset>".into(),
        ))
    }

    fn from_sqlite_text(value: &str) -> Result<Self, DrizzleError> {
        Self::parse_from_rfc3339(value).map_err(|e| {
            DrizzleError::ConversionError(
                format!("cannot parse '{value}' as DateTime<FixedOffset>: {e}").into(),
            )
        })
    }

    fn from_sqlite_real(_value: f64) -> Result<Self, DrizzleError> {
        Err(DrizzleError::ConversionError(
            "cannot convert REAL to DateTime<FixedOffset>".into(),
        ))
    }

    fn from_sqlite_blob(_value: &[u8]) -> Result<Self, DrizzleError> {
        Err(DrizzleError::ConversionError(
            "cannot convert BLOB to DateTime<FixedOffset>".into(),
        ))
    }
}

#[cfg(feature = "chrono")]
impl FromSQLiteValue for chrono::DateTime<chrono::Utc> {
    fn from_sqlite_integer(_value: i64) -> Result<Self, DrizzleError> {
        Err(DrizzleError::ConversionError(
            "cannot convert INTEGER to DateTime<Utc>".into(),
        ))
    }

    fn from_sqlite_text(value: &str) -> Result<Self, DrizzleError> {
        value.parse().map_err(|e| {
            DrizzleError::ConversionError(
                format!("cannot parse '{value}' as DateTime<Utc>: {e}").into(),
            )
        })
    }

    fn from_sqlite_real(_value: f64) -> Result<Self, DrizzleError> {
        Err(DrizzleError::ConversionError(
            "cannot convert REAL to DateTime<Utc>".into(),
        ))
    }

    fn from_sqlite_blob(_value: &[u8]) -> Result<Self, DrizzleError> {
        Err(DrizzleError::ConversionError(
            "cannot convert BLOB to DateTime<Utc>".into(),
        ))
    }
}

// =============================================================================
// Time crate date/time types (parse from ISO-8601 text)
// =============================================================================

#[cfg(feature = "time")]
impl FromSQLiteValue for time::Date {
    fn from_sqlite_integer(_value: i64) -> Result<Self, DrizzleError> {
        Err(DrizzleError::ConversionError(
            "cannot convert INTEGER to time::Date".into(),
        ))
    }

    fn from_sqlite_text(value: &str) -> Result<Self, DrizzleError> {
        Self::parse(value, &time::format_description::well_known::Iso8601::DATE).map_err(|e| {
            DrizzleError::ConversionError(
                format!("cannot parse '{value}' as time::Date: {e}").into(),
            )
        })
    }

    fn from_sqlite_real(_value: f64) -> Result<Self, DrizzleError> {
        Err(DrizzleError::ConversionError(
            "cannot convert REAL to time::Date".into(),
        ))
    }

    fn from_sqlite_blob(_value: &[u8]) -> Result<Self, DrizzleError> {
        Err(DrizzleError::ConversionError(
            "cannot convert BLOB to time::Date".into(),
        ))
    }
}

#[cfg(feature = "time")]
impl FromSQLiteValue for time::Time {
    fn from_sqlite_integer(_value: i64) -> Result<Self, DrizzleError> {
        Err(DrizzleError::ConversionError(
            "cannot convert INTEGER to time::Time".into(),
        ))
    }

    fn from_sqlite_text(value: &str) -> Result<Self, DrizzleError> {
        Self::parse(value, &time::format_description::well_known::Iso8601::TIME).map_err(|e| {
            DrizzleError::ConversionError(
                format!("cannot parse '{value}' as time::Time: {e}").into(),
            )
        })
    }

    fn from_sqlite_real(_value: f64) -> Result<Self, DrizzleError> {
        Err(DrizzleError::ConversionError(
            "cannot convert REAL to time::Time".into(),
        ))
    }

    fn from_sqlite_blob(_value: &[u8]) -> Result<Self, DrizzleError> {
        Err(DrizzleError::ConversionError(
            "cannot convert BLOB to time::Time".into(),
        ))
    }
}

#[cfg(feature = "time")]
impl FromSQLiteValue for time::PrimitiveDateTime {
    fn from_sqlite_integer(_value: i64) -> Result<Self, DrizzleError> {
        Err(DrizzleError::ConversionError(
            "cannot convert INTEGER to time::PrimitiveDateTime".into(),
        ))
    }

    fn from_sqlite_text(value: &str) -> Result<Self, DrizzleError> {
        Self::parse(
            value,
            &time::format_description::well_known::Iso8601::DATE_TIME,
        )
        .map_err(|e| {
            DrizzleError::ConversionError(
                format!("cannot parse '{value}' as time::PrimitiveDateTime: {e}").into(),
            )
        })
    }

    fn from_sqlite_real(_value: f64) -> Result<Self, DrizzleError> {
        Err(DrizzleError::ConversionError(
            "cannot convert REAL to time::PrimitiveDateTime".into(),
        ))
    }

    fn from_sqlite_blob(_value: &[u8]) -> Result<Self, DrizzleError> {
        Err(DrizzleError::ConversionError(
            "cannot convert BLOB to time::PrimitiveDateTime".into(),
        ))
    }
}

#[cfg(feature = "time")]
impl FromSQLiteValue for time::OffsetDateTime {
    fn from_sqlite_integer(_value: i64) -> Result<Self, DrizzleError> {
        Err(DrizzleError::ConversionError(
            "cannot convert INTEGER to time::OffsetDateTime".into(),
        ))
    }

    fn from_sqlite_text(value: &str) -> Result<Self, DrizzleError> {
        Self::parse(value, &time::format_description::well_known::Rfc3339).map_err(|e| {
            DrizzleError::ConversionError(
                format!("cannot parse '{value}' as time::OffsetDateTime: {e}").into(),
            )
        })
    }

    fn from_sqlite_real(_value: f64) -> Result<Self, DrizzleError> {
        Err(DrizzleError::ConversionError(
            "cannot convert REAL to time::OffsetDateTime".into(),
        ))
    }

    fn from_sqlite_blob(_value: &[u8]) -> Result<Self, DrizzleError> {
        Err(DrizzleError::ConversionError(
            "cannot convert BLOB to time::OffsetDateTime".into(),
        ))
    }
}

// =============================================================================
// Decimal (parse from text)
// =============================================================================

#[cfg(feature = "rust-decimal")]
impl FromSQLiteValue for rust_decimal::Decimal {
    fn from_sqlite_integer(value: i64) -> Result<Self, DrizzleError> {
        Ok(Self::from(value))
    }

    fn from_sqlite_text(value: &str) -> Result<Self, DrizzleError> {
        value.parse().map_err(|e| {
            DrizzleError::ConversionError(format!("cannot parse '{value}' as Decimal: {e}").into())
        })
    }

    fn from_sqlite_real(value: f64) -> Result<Self, DrizzleError> {
        Self::try_from(value).map_err(|e| {
            DrizzleError::ConversionError(
                format!("cannot convert REAL {value} to Decimal: {e}").into(),
            )
        })
    }

    fn from_sqlite_blob(_value: &[u8]) -> Result<Self, DrizzleError> {
        Err(DrizzleError::ConversionError(
            "cannot convert BLOB to Decimal".into(),
        ))
    }
}

// =============================================================================
// Duration types
// =============================================================================

#[cfg(feature = "chrono")]
impl FromSQLiteValue for chrono::Duration {
    fn from_sqlite_integer(value: i64) -> Result<Self, DrizzleError> {
        Ok(Self::seconds(value))
    }

    fn from_sqlite_text(value: &str) -> Result<Self, DrizzleError> {
        // Parse seconds from the text representation
        let secs: i64 = value.trim_end_matches('s').parse().map_err(|e| {
            DrizzleError::ConversionError(
                format!("cannot parse '{value}' as chrono::Duration: {e}").into(),
            )
        })?;
        Ok(Self::seconds(secs))
    }

    fn from_sqlite_real(value: f64) -> Result<Self, DrizzleError> {
        let millis: i64 = format!("{:.0}", value * 1000.0).parse().map_err(|e| {
            DrizzleError::ConversionError(
                format!("cannot convert REAL {value} to chrono::Duration: {e}").into(),
            )
        })?;
        Ok(Self::milliseconds(millis))
    }

    fn from_sqlite_blob(_value: &[u8]) -> Result<Self, DrizzleError> {
        Err(DrizzleError::ConversionError(
            "cannot convert BLOB to chrono::Duration".into(),
        ))
    }
}

#[cfg(feature = "time")]
impl FromSQLiteValue for time::Duration {
    fn from_sqlite_integer(value: i64) -> Result<Self, DrizzleError> {
        Ok(Self::seconds(value))
    }

    fn from_sqlite_text(value: &str) -> Result<Self, DrizzleError> {
        // Parse seconds from the "Ns" text representation
        let secs: i64 = value.trim_end_matches('s').parse().map_err(|e| {
            DrizzleError::ConversionError(
                format!("cannot parse '{value}' as time::Duration: {e}").into(),
            )
        })?;
        Ok(Self::seconds(secs))
    }

    fn from_sqlite_real(value: f64) -> Result<Self, DrizzleError> {
        Ok(Self::seconds_f64(value))
    }

    fn from_sqlite_blob(_value: &[u8]) -> Result<Self, DrizzleError> {
        Err(DrizzleError::ConversionError(
            "cannot convert BLOB to time::Duration".into(),
        ))
    }
}

// =============================================================================
// JSON support (serde_json::Value)
// =============================================================================

#[cfg(feature = "serde")]
impl FromSQLiteValue for serde_json::Value {
    fn from_sqlite_integer(value: i64) -> Result<Self, DrizzleError> {
        Ok(Self::Number(value.into()))
    }

    fn from_sqlite_text(value: &str) -> Result<Self, DrizzleError> {
        serde_json::from_str(value).map_err(|e| {
            DrizzleError::ConversionError(format!("cannot parse '{value}' as JSON: {e}").into())
        })
    }

    fn from_sqlite_real(value: f64) -> Result<Self, DrizzleError> {
        serde_json::Number::from_f64(value)
            .map(serde_json::Value::Number)
            .ok_or_else(|| {
                DrizzleError::ConversionError(
                    format!("cannot convert non-finite REAL {value} to JSON").into(),
                )
            })
    }

    fn from_sqlite_blob(value: &[u8]) -> Result<Self, DrizzleError> {
        serde_json::from_slice(value).map_err(|e| {
            DrizzleError::ConversionError(format!("cannot parse BLOB as JSON: {e}").into())
        })
    }
}

// =============================================================================
// ArrayVec/ArrayString support
// =============================================================================

#[cfg(feature = "arrayvec")]
impl<const N: usize> FromSQLiteValue for arrayvec::ArrayString<N> {
    fn from_sqlite_integer(value: i64) -> Result<Self, DrizzleError> {
        let s = value.to_string();
        Self::from(&s).map_err(|_| {
            DrizzleError::ConversionError(
                format!(
                    "String length {} exceeds ArrayString capacity {}",
                    s.len(),
                    N
                )
                .into(),
            )
        })
    }

    fn from_sqlite_text(value: &str) -> Result<Self, DrizzleError> {
        Self::from(value).map_err(|_| {
            DrizzleError::ConversionError(
                format!(
                    "Text length {} exceeds ArrayString capacity {}",
                    value.len(),
                    N
                )
                .into(),
            )
        })
    }

    fn from_sqlite_real(value: f64) -> Result<Self, DrizzleError> {
        let s = value.to_string();
        Self::from(&s).map_err(|_| {
            DrizzleError::ConversionError(
                format!(
                    "String length {} exceeds ArrayString capacity {}",
                    s.len(),
                    N
                )
                .into(),
            )
        })
    }

    fn from_sqlite_blob(value: &[u8]) -> Result<Self, DrizzleError> {
        let s = String::from_utf8(value.to_vec())
            .map_err(|e| DrizzleError::ConversionError(format!("invalid UTF-8: {e}").into()))?;
        Self::from(&s).map_err(|_| {
            DrizzleError::ConversionError(
                format!(
                    "String length {} exceeds ArrayString capacity {}",
                    s.len(),
                    N
                )
                .into(),
            )
        })
    }
}

#[cfg(feature = "arrayvec")]
impl<const N: usize> FromSQLiteValue for arrayvec::ArrayVec<u8, N> {
    fn from_sqlite_integer(_value: i64) -> Result<Self, DrizzleError> {
        Err(DrizzleError::ConversionError(
            "cannot convert INTEGER to ArrayVec<u8>, use BLOB".into(),
        ))
    }

    fn from_sqlite_text(_value: &str) -> Result<Self, DrizzleError> {
        Err(DrizzleError::ConversionError(
            "cannot convert TEXT to ArrayVec<u8>, use BLOB".into(),
        ))
    }

    fn from_sqlite_real(_value: f64) -> Result<Self, DrizzleError> {
        Err(DrizzleError::ConversionError(
            "cannot convert REAL to ArrayVec<u8>, use BLOB".into(),
        ))
    }

    fn from_sqlite_blob(value: &[u8]) -> Result<Self, DrizzleError> {
        Self::try_from(value).map_err(|_| {
            DrizzleError::ConversionError(
                format!(
                    "Blob length {} exceeds ArrayVec capacity {}",
                    value.len(),
                    N
                )
                .into(),
            )
        })
    }
}
