//! Models and collects [`Fields`](Field), which represent columns in a Postgres database,
//! and converts data types from Postgres to Toml.
//!
//! There are only a handful of Toml types, and a large number of Postgres types, so
//! in general, many Postgres types will be converted into one Toml type.

use std::error::Error;
use std::fmt::Display;
use std::str::FromStr;

use chrono::{DateTime, Local, NaiveDate, NaiveDateTime, NaiveTime};
use sqlx::{
    Column, Decode, PgPool, Postgres, Row, TypeInfo, Value, ValueRef,
    postgres::{PgRow, PgValueRef},
    prelude::FromRow,
    types::BigDecimal,
};
use toml::{Value as TomlValue, value::Array as TomlArray};

use crate::RESULTS_PER_PAGE;
use crate::relations::{Relation, RelationType};
use crate::ui::main_pane::SortOrder;
use crate::{errors::PtError, ui::main_pane::Sort};

static BOOL_TYPES: &[&str] = &["BOOL", "BOOLEAN", "bool", "boolean"];
static TEXT_TYPES: &[&str] = &[
    "character varying",
    "varchar",
    "character",
    "char",
    "bpchar",
    "text",
    "citext",
    "CHARACTER VARYING",
    "VARCHAR",
    "CHARACTER",
    "CHAR",
    "BPCHAR",
    "TEXT",
    "CITEXT",
];
static INT_TYPES: &[&str] = &[
    "smallint",
    "smallserial",
    "int2",
    "int",
    "serial",
    "int4",
    "bigint",
    "bigserial",
    "int8",
    "integer",
    "SMALLINT",
    "SMALLSERIAL",
    "INT2",
    "INT",
    "SERIAL",
    "INT4",
    "BIGINT",
    "BIGSERIAL",
    "INT8",
    "INTEGER",
];
static FLOAT32_TYPES: &[&str] = &["real", "float4", "REAL", "FLOAT4"];
static FLOAT64_TYPES: &[&str] = &[
    "double precision",
    "float",
    "float8",
    "DOUBLE PRECISION",
    "FLOAT",
    "FLOAT8",
];
static BIG_DECIMAL_TYPES: &[&str] = &["decimal", "numeric", "DECIMAL", "NUMERIC"];
static TIME_TYPES_NO_TZ: &[&str] = &[
    "time",
    "time without time zone",
    "TIME",
    "TIME WITHOUT TIME ZONE",
];
/* NOTE: there is no "TIME_TYPES_WITH_TZ" as Postgres recommenads against the type. See:
     - <https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-DATETIME>
     - <https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_timetz>
*/
static TIMESTAMP_TYPES_NO_TZ: &[&str] = &[
    "timestamp",
    "timestamp without time zone",
    "TIMESTAMP",
    "TIMESTAMP WITHOUT TIME ZONE",
];

static TIMESTAMP_TYPES_WITH_TZ: &[&str] = &[
    "timestamptz",
    "timestamp with time zone",
    "TIMESTAMPTZ",
    "TIMESTAMP WITH TIME ZONE",
];

/// The various types that we'll attempt to represent as Toml [`Values`][toml::Value].
#[derive(Clone, Debug, Default, PartialEq)]
pub enum DataType {
    #[default]
    Boolean,
    String,
    Integer,
    Float,
    Date,
    Time,
    TimestampNoTz,
    TimestampWithTz,
    Array,
    Other, // unknown, essentially, and will probably just be handled as String
}

/// Set Datatype from name of Postgres data type.
// NOTE: toml value must still be parsed, via impl Decode on NewToml below.
impl From<&str> for DataType {
    fn from(value: &str) -> Self {
        match value {
            val if BOOL_TYPES.contains(&val) => Self::Boolean,
            val if TEXT_TYPES.contains(&val) => Self::String,
            val if INT_TYPES.contains(&val) => Self::Integer,
            val if FLOAT32_TYPES.contains(&val) => Self::Float,
            val if FLOAT64_TYPES.contains(&val) => Self::Float,
            val if BIG_DECIMAL_TYPES.contains(&val) => Self::Float,
            "DATE" | "date" => Self::Date,
            val if TIME_TYPES_NO_TZ.contains(&val) => Self::Time,
            val if TIMESTAMP_TYPES_NO_TZ.contains(&val) => Self::TimestampNoTz,
            val if TIMESTAMP_TYPES_WITH_TZ.contains(&val) => Self::TimestampWithTz,
            "ARRAY" | "array" => Self::Array,
            "TEXT[]" | "text[]" => Self::Array,
            _ => Self::Other,
        }
    }
}

impl Display for DataType {
    fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
        match self {
            DataType::Boolean => write!(f, "Boolean"),
            DataType::String => write!(f, "String"),
            DataType::Integer => write!(f, "Integer"),
            DataType::Float => write!(f, "Float"),
            DataType::Date => write!(f, "Date"),
            DataType::Time => write!(f, "Time"),
            DataType::TimestampNoTz => write!(f, "Datetime (without timezone offset)"),
            DataType::TimestampWithTz => write!(f, "Datetime (with timezone offset)"),
            DataType::Array => write!(f, "Array"),
            DataType::Other => write!(f, "Other"),
        }
    }
}

/// Whethor or not the Postgres column represented is an identity.
#[derive(Debug, Clone)]
pub enum IsIdentity {
    Yes,
    No,
}

impl From<bool> for IsIdentity {
    fn from(value: bool) -> Self {
        if value { Self::Yes } else { Self::No }
    }
}

/// Whethor or not the Postgres column represented is nullable.
#[derive(Debug, Clone)]
pub enum IsNullable {
    Yes,
    No,
}

impl From<bool> for IsNullable {
    fn from(value: bool) -> Self {
        if value { Self::Yes } else { Self::No }
    }
}

/// Whethor or not the Postgres column represented has a default value.
#[derive(Debug, Clone)]
pub enum HasDefault {
    Yes,
    No,
}

impl From<bool> for HasDefault {
    fn from(value: bool) -> Self {
        if value { Self::Yes } else { Self::No }
    }
}

/// Information about a Postgres column, without the actual value or primary key status.
pub struct FieldMeta {
    pub name: String,
    pub kind: DataType,
    pub is_identity: IsIdentity,
    pub is_nullable: IsNullable,
    pub has_default: HasDefault,
}

impl FieldMeta {
    /// Collect the metadata about a table.
    pub async fn collect(pool: &PgPool, relation: &Relation) -> Result<Vec<Self>, PtError> {
        if relation.relation_type != RelationType::Table {
            return Err(PtError::MetadataAvailableForRelationOnly);
        }
        // Fetch some metadata about the columns of the table.
        let fields_meta: Vec<FieldMeta> = sqlx::query_as(
            "select column_name, data_type, is_identity, is_nullable, case when column_default is null then false else true end as has_default from information_schema.columns where table_schema = $1 and table_name = $2"
            ).bind(&relation.schema).bind(&relation.name)
            .fetch_all(pool)
            .await?;

        Ok(fields_meta)
    }
}

impl FromRow<'_, PgRow> for FieldMeta {
    fn from_row(row: &PgRow) -> sqlx::Result<Self> {
        Ok(Self {
            name: row.get("column_name"),
            kind: DataType::from(row.get::<&str, &str>("data_type")),
            is_identity: IsIdentity::from(!matches!(row.get("is_identity"), "NO")),
            is_nullable: IsNullable::from(!matches!(row.get("is_nullable"), "NO")),
            has_default: HasDefault::from(row.get::<bool, &str>("has_default")),
        })
    }
}

/// Simplified representation of a Postgres column, including its value as a Toml [Value][toml::Value].
#[derive(Clone, Debug)]
pub struct Field {
    pub name: String,
    pub kind: DataType,
    pub is_identity: IsIdentity,
    pub is_nullable: IsNullable,
    pub has_default: HasDefault,
    pub is_primary_key: Option<bool>,
    /// Was there an error in attempting to parse the type from Postgres to Toml?
    pub parse_error: bool,
    pub toml_value: Option<TomlValue>,
}

/// The results from collecting [`Fields`](Field) from a [`Relation`].
pub struct ResultSet {
    /// Total number of results in the query.
    pub total_count: usize,
    /// The paginated, current results.
    pub current_set: Vec<Vec<Field>>,
}

impl Field {
    pub fn new(
        name: String,
        kind: DataType,
        is_identity: IsIdentity,
        is_nullable: IsNullable,
        has_default: HasDefault,
        parse_error: bool,
        toml_value: Option<TomlValue>,
    ) -> Self {
        Self {
            name,
            kind,
            is_identity,
            is_nullable,
            has_default,
            parse_error,
            is_primary_key: None,
            toml_value,
        }
    }

    pub fn is_identity(&self) -> bool {
        match self.is_identity {
            IsIdentity::Yes => true,
            IsIdentity::No => false,
        }
    }

    pub fn is_nullable(&self) -> bool {
        match self.is_nullable {
            IsNullable::Yes => true,
            IsNullable::No => false,
        }
    }

    pub fn has_default(&self) -> bool {
        match self.has_default {
            HasDefault::Yes => true,
            HasDefault::No => false,
        }
    }

    /// Collect the data for the [`Relation`] selected in the sidebar.
    pub async fn collect(
        pool: &PgPool,
        relation: &Relation,
        where_clause: Option<String>,
        sort: &Sort,
        current_page: usize,
    ) -> Result<ResultSet, PtError> {
        let where_clause = if let Some(mut v) = where_clause {
            v = v.trim_end_matches(';').to_string();
            v = v
                .trim_start_matches("WHERE")
                .trim_start_matches("where")
                .to_string();
            Some(v)
        } else {
            None
        };

        let mut rows: Vec<Vec<Field>> = vec![];
        if relation.relation_type != RelationType::Table {
            return Err(PtError::MetadataAvailableForRelationOnly);
        }

        // Fetch metadata about the columns of the table.
        let field_meta = FieldMeta::collect(pool, relation).await?;

        // Determine how many results overall.
        let count_query = if let Some(v) = where_clause.clone() {
            format!(
                "select count(*) from {}.{} where {}",
                relation.schema, relation.name, v
            )
        } else {
            format!("select count(*) from {}.{}", relation.schema, relation.name)
        };
        let count: i64 = match sqlx::query(&count_query).fetch_one(pool).await {
            Ok(v) => v.get_unchecked(0),
            Err(_) => {
                return Err(PtError::CannotFetchRecordCount);
            }
        };

        // Set up query, possibly adding an order by clause.
        let order = match sort.order {
            SortOrder::Ascending => "asc",
            SortOrder::Descending => "desc",
        };

        // For now, filtering will take precedence over sorting
        let query = if let Some(v) = where_clause {
            match sort.field.clone() {
                Some(w) => format!(
                    "select * from {}.{} where {} order by {} {} limit {} offset {}",
                    relation.schema,
                    relation.name,
                    v,
                    w,
                    order,
                    RESULTS_PER_PAGE,
                    current_page * RESULTS_PER_PAGE
                ),
                None => format!(
                    "select * from {}.{} where {} limit {} offset {}",
                    relation.schema,
                    relation.name,
                    v,
                    RESULTS_PER_PAGE,
                    current_page * RESULTS_PER_PAGE
                ),
            }
        } else {
            match sort.field.clone() {
                Some(v) => format!(
                    "select * from {}.{} order by {} {} limit {} offset {}",
                    relation.schema,
                    relation.name,
                    v,
                    order,
                    RESULTS_PER_PAGE,
                    current_page * RESULTS_PER_PAGE
                ),
                None => format!(
                    "select * from {}.{} limit {} offset {}",
                    relation.schema,
                    relation.name,
                    RESULTS_PER_PAGE,
                    current_page * RESULTS_PER_PAGE
                ),
            }
        };

        // Fetch the column data.
        match sqlx::query(&query).fetch_all(pool).await {
            Ok(v) => {
                for pg_row in v {
                    let mut fields = vec![];
                    for pg_col in pg_row.columns() {
                        let mut parse_error = false;
                        let column_name = pg_col.name();
                        let pg_value = pg_row.try_get_raw(pg_col.ordinal())?;
                        let data = if pg_value.is_null() {
                            None
                        } else {
                            match NewToml::decode(pg_value) {
                                Ok(v) => Some(v.0),
                                Err(_) => {
                                    parse_error = true;
                                    None
                                }
                            }
                        };
                        let meta = field_meta.iter().find(|x| x.name == column_name).unwrap();

                        fields.push(Field::new(
                            column_name.to_string(),
                            meta.kind.clone(),
                            meta.is_identity.clone(),
                            meta.is_nullable.clone(),
                            meta.has_default.clone(),
                            parse_error,
                            data,
                        ));
                    }
                    rows.push(fields);
                }
                Ok(ResultSet {
                    total_count: count as usize,
                    current_set: rows,
                })
            }
            Err(_) => Err(PtError::CannotFetchTableData),
        }
    }

    /// Examples to show user in text editor.
    pub fn example(&self) -> Option<&'static str> {
        match self.kind {
            DataType::Boolean => Some("true"),
            DataType::String | DataType::Other => None,
            DataType::Integer => Some("4"),
            DataType::Float => Some("-0.1"),
            DataType::Date => Some("2025-07-15"),
            DataType::Time => Some("13:00:00"),
            DataType::TimestampNoTz => Some("2025-07-15T13:00:00"),
            DataType::TimestampWithTz => Some("2025-07-15T13:00:00-05:00"),
            DataType::Array => Some("[ \"red\", \"yellow\", \"green\" ]"),
        }
    }

    /// The line(s) in a text editor where the user will (possibly) enter data.
    ///
    /// If the field is nullable and doesn't already have a value, it will be commented out.
    /// If the field has a value, it will be included and the line won't be commented out even
    /// if it is nullable.
    pub fn template(&self) -> String {
        let mut line = format!("{} = ", self.name);

        line = match self.kind {
            DataType::String => {
                if let Some(v) = self.toml_value.clone() {
                    format!("{line}{v}\n")
                } else {
                    format!("{line}\"\"\n")
                }
            }
            DataType::Boolean
            | DataType::Integer
            | DataType::Float
            | DataType::Date
            | DataType::Time
            | DataType::TimestampNoTz
            | DataType::TimestampWithTz
            | DataType::Other => {
                if let Some(v) = self.toml_value.clone() {
                    format!("{line}{v}\n")
                } else {
                    format!("{line}\n")
                }
            }
            DataType::Array => {
                if let Some(v) = self.toml_value.clone() {
                    format!("{line}{v}\n")
                } else {
                    format!("{line}[]\n")
                }
            }
        };
        if ((self.is_nullable() || self.has_default()) && self.toml_value.is_none())
            || self.kind == DataType::Other
            || self.parse_error
        {
            line = format!("#{line}");
        }

        line
    }
}

impl From<FieldMeta> for Field {
    fn from(f: FieldMeta) -> Self {
        Self {
            name: f.name,
            kind: f.kind,
            is_identity: f.is_identity,
            is_nullable: f.is_nullable,
            has_default: f.has_default,
            is_primary_key: None,
            parse_error: false,
            toml_value: None,
        }
    }
}

impl FromRow<'_, PgRow> for Field {
    fn from_row(row: &PgRow) -> sqlx::Result<Self> {
        Ok(Self {
            name: row.get("column_name"),
            kind: DataType::from(row.get::<&str, &str>("data_type")),
            is_identity: IsIdentity::from(!matches!(row.get("is_identity"), "NO")),
            is_nullable: IsNullable::from(!matches!(row.get("is_nullable"), "NO")),
            has_default: HasDefault::from(row.get::<bool, &str>("has_default")),
            parse_error: false,
            is_primary_key: None,
            toml_value: None,
        })
    }
}

/// Validate that each item in a Toml Array is of the same type. Toml's Arrays are Values,
/// which can be of various types, but in Postgres they have to be the same.
pub fn array_is_same_type(toml_array: &TomlArray) -> bool {
    if let Some(v) = toml_array.first() {
        let mut prev = v;
        for each in toml_array.iter().skip(1) {
            if !each.same_type(prev) {
                return false;
            }
            prev = each;
        }
        true
    } else {
        true
    }
}

pub trait FromToml {
    fn from_toml(value: TomlValue) -> Self;
}

impl FromToml for NaiveDate {
    fn from_toml(value: TomlValue) -> Self {
        NaiveDate::from_str(&format!("{value}")).unwrap()
    }
}

impl FromToml for NaiveDateTime {
    fn from_toml(value: TomlValue) -> Self {
        NaiveDateTime::from_str(&format!("{value}")).unwrap()
    }
}

/// A newtype that wraps a Toml [`Value`][toml::Value] in order to implement sqlx's [`Decode`] trait on it to convert from Postgres to Toml.
pub struct NewToml(pub TomlValue);

// This is based on sqlx documentation and uses their suggestion to make the
// implementatin simpler, while also wrapping the decoded value in a NewType and decoding
// according to what type we're getting out of Postgres.
// `'r` is the lifetime of the `Row` being decoded
// <https://docs.rs/sqlx/latest/sqlx/postgres/types/index.html>
// <https://docs.rs/sqlx/latest/sqlx/trait.Decode.html>
impl<'r> Decode<'r, Postgres> for NewToml
where
    // We want to delegate some of the work to string decoding so let's make sure strings
    // are supported by the database.
    &'r str: Decode<'r, Postgres>,
{
    fn decode(value: PgValueRef<'r>) -> Result<Self, Box<dyn Error + 'static + Send + Sync>> {
        let pg_value = sqlx::ValueRef::to_owned(&value);

        match pg_value.type_info().name() {
            val if BOOL_TYPES.contains(&val) => {
                let decoded_value = <bool as Decode<Postgres>>::decode(value)?;
                Ok(NewToml(TomlValue::Boolean(decoded_value)))
            }
            val if TEXT_TYPES.contains(&val) => {
                let decoded_value = <String as Decode<Postgres>>::decode(value)?;
                // For whatever reason, using Value::String properly handles escaping of characters
                // and .parse::<Value>() does not.
                Ok(NewToml(TomlValue::String(decoded_value)))
            }
            val if INT_TYPES.contains(&val) => {
                let decoded_value = <i64 as Decode<Postgres>>::decode(value)?;
                Ok(NewToml(TomlValue::Integer(decoded_value)))
            }
            val if FLOAT32_TYPES.contains(&val) => {
                let decoded_value = <f32 as Decode<Postgres>>::decode(value)?;
                Ok(NewToml(TomlValue::Float(f64::from(decoded_value))))
            }
            val if FLOAT64_TYPES.contains(&val) => {
                let decoded_value = <f64 as Decode<Postgres>>::decode(value)?;
                Ok(NewToml(TomlValue::Float(decoded_value)))
            }
            val if BIG_DECIMAL_TYPES.contains(&val) => {
                let decoded_value = <BigDecimal as Decode<Postgres>>::decode(value)?;
                Ok(NewToml(TomlValue::Float(<f64>::from_str(
                    decoded_value.to_plain_string().as_str(),
                )?)))
            }
            "DATE" | "date" => {
                let decoded_value = <NaiveDate as Decode<Postgres>>::decode(value)?.to_string();
                Ok(NewToml(decoded_value.parse::<TomlValue>()?))
            }
            val if TIME_TYPES_NO_TZ.contains(&val) => {
                let decoded_value = <NaiveTime as Decode<Postgres>>::decode(value)?.to_string();
                Ok(NewToml(decoded_value.parse::<TomlValue>()?))
            }
            val if TIMESTAMP_TYPES_NO_TZ.contains(&val) => {
                let decoded_value = <NaiveDateTime as Decode<Postgres>>::decode(value)?.to_string();
                Ok(NewToml(decoded_value.parse::<TomlValue>()?))
            }
            // NOTE: Timestamp with time zone will be shown as local time. To show as UTC instead,
            // <DateTime<Utc> as Decode<Postgres>> would need to be used. This could be made
            // to be configurable in the future.
            val if TIMESTAMP_TYPES_WITH_TZ.contains(&val) => {
                let decoded_value =
                    <DateTime<Local> as Decode<Postgres>>::decode(value)?.to_rfc3339();
                Ok(NewToml(decoded_value.parse::<TomlValue>()?))
            }
            // TODO: arrays of other types
            "TEXT[]" | "text[]" => {
                let decoded_value = <Vec<String> as Decode<Postgres>>::decode(value)?;
                let mut toml_vec = vec![];
                for each in decoded_value {
                    toml_vec.push(TomlValue::String(each));
                }
                Ok(NewToml(TomlValue::Array(toml_vec)))
            }
            // For everything else, try to decode as String, and if that doesn't work, note that
            // it's unhandled.
            _ => {
                if let Ok(v) = <String as Decode<Postgres>>::decode(value.clone()) {
                    Ok(NewToml(TomlValue::String(v)))
                } else {
                    Ok(NewToml(
                        "'unhandled type'".to_string().parse::<TomlValue>()?,
                    ))
                }
            }
        }
    }
}

#[cfg(test)]
mod tests {
    use super::*;
    use std::fs;
    use std::io::Write;
    use std::path::PathBuf;

    use tempfile::NamedTempFile;
    use toml::value::{Date as TomlDate, Datetime as TomlDatetime, Table, Time as TomlTime};

    use crate::db_tests::{make_relation, test_db};

    const TOML_DATE1: &str = r"
        date = 2025-07-15
    ";

    fn str_to_path(toml: &'static str) -> PathBuf {
        let mut file = NamedTempFile::with_suffix(".toml").unwrap();
        writeln!(file, "{toml}").unwrap();
        let path = file.into_temp_path();
        path.keep().unwrap()
    }

    #[test]
    fn array_is_same_type_works() {
        let array = vec![TomlValue::Integer(1), TomlValue::Float(0.1)];
        assert!(!array_is_same_type(&array));
        let array = vec![
            TomlValue::Integer(1),
            TomlValue::Float(0.1),
            TomlValue::Integer(3),
        ];
        assert!(!array_is_same_type(&array));
        let array = vec![TomlValue::Integer(1), TomlValue::Integer(2)];
        assert!(array_is_same_type(&array));
        let array = vec![];
        assert!(array_is_same_type(&array));
        let array = vec![TomlValue::Integer(1)];
        assert!(array_is_same_type(&array));
    }

    #[test]
    fn string_to_path_works() {
        let path = str_to_path(TOML_DATE1);
        let contents = fs::read_to_string(path).unwrap();
        assert!(contents.contains("date"));
    }

    #[test]
    fn toml_date1_parsed() {
        let contents = fs::read_to_string(str_to_path(TOML_DATE1)).unwrap();
        let table = contents.parse::<Table>().unwrap();

        assert!(table.contains_key("date"));
        let date = table.get("date").unwrap().as_datetime().unwrap();
        assert_eq!(format!("{}", date.date.unwrap()), "2025-07-15");
    }

    #[test]
    fn toml_date_to_chrono_succeeds() {
        let toml_dt_without_time = TomlValue::Datetime(TomlDatetime {
            date: Some(TomlDate {
                year: 2025,
                month: 7,
                day: 16,
            }),
            time: None,
            offset: None,
        });
        let chrono_date = NaiveDate::from_toml(toml_dt_without_time);

        assert_eq!(format!("{chrono_date}"), "2025-07-16".to_string());
    }
    #[test]
    fn toml_datetime_to_chrono_succeeds() {
        let toml_dt = TomlValue::Datetime(TomlDatetime {
            date: Some(TomlDate {
                year: 2025,
                month: 7,
                day: 16,
            }),
            time: Some(TomlTime {
                hour: 22,
                minute: 19,
                second: 15,
                nanosecond: 0,
            }),
            offset: None,
        });

        let chrono_dt = NaiveDateTime::from_toml(toml_dt);

        assert_eq!(format!("{chrono_dt}"), "2025-07-16 22:19:15".to_string());
    }

    #[test]
    fn toml_parse_works_as_i_expect() {
        let value_to_parse = "wildlife";
        let value_quoted = format!("'{value_to_parse}'");
        let parsed = value_quoted.parse::<TomlValue>().unwrap();
        assert_eq!(parsed, TomlValue::String("wildlife".to_string()));

        let value_to_parse = "2025-07-18";
        let parsed = value_to_parse.parse::<TomlValue>().unwrap();
        assert_eq!(
            parsed,
            TomlValue::Datetime(TomlDatetime {
                date: Some(TomlDate {
                    year: 2025,
                    month: 7,
                    day: 18
                }),
                time: None,
                offset: None
            })
        );
    }

    #[tokio::test]
    async fn fieldmeta_collect() {
        let pool = test_db().await;
        let blog_relation = make_relation("blog".to_string(), RelationType::Table).await;
        let field_meta = FieldMeta::collect(&pool, &blog_relation).await.unwrap();
        assert_eq!(field_meta.len(), 7);
        assert_eq!(field_meta[0].name, "id".to_string());
        assert_eq!(field_meta[1].name, "title".to_string());
        assert_eq!(field_meta[2].name, "date".to_string());
        assert_eq!(field_meta[3].name, "content".to_string());
        assert_eq!(field_meta[4].name, "temperature".to_string());
        assert_eq!(field_meta[5].name, "tags".to_string());
        assert_eq!(field_meta[6].name, "published".to_string());
        assert_eq!(field_meta[0].kind, DataType::Integer);
        assert_eq!(field_meta[1].kind, DataType::String);
        assert_eq!(field_meta[2].kind, DataType::Date);
        assert_eq!(field_meta[3].kind, DataType::String);
        assert_eq!(field_meta[4].kind, DataType::Float);
        assert_eq!(field_meta[5].kind, DataType::Array);
        assert_eq!(field_meta[6].kind, DataType::Boolean);
        assert!(matches!(field_meta[0].is_identity, IsIdentity::Yes));
        assert!(matches!(field_meta[1].is_identity, IsIdentity::No));
        assert!(matches!(field_meta[2].is_identity, IsIdentity::No));
        assert!(matches!(field_meta[3].is_identity, IsIdentity::No));
        assert!(matches!(field_meta[4].is_identity, IsIdentity::No));
        assert!(matches!(field_meta[5].is_identity, IsIdentity::No));
        assert!(matches!(field_meta[6].is_identity, IsIdentity::No));
        assert!(matches!(field_meta[0].is_nullable, IsNullable::No));
        assert!(matches!(field_meta[1].is_nullable, IsNullable::No));
        assert!(matches!(field_meta[2].is_nullable, IsNullable::No));
        assert!(matches!(field_meta[3].is_nullable, IsNullable::No));
        assert!(matches!(field_meta[4].is_nullable, IsNullable::Yes));
        assert!(matches!(field_meta[5].is_nullable, IsNullable::Yes));
        assert!(matches!(field_meta[6].is_nullable, IsNullable::No));
        assert!(matches!(field_meta[0].has_default, HasDefault::No));
        assert!(matches!(field_meta[1].has_default, HasDefault::No));
        assert!(matches!(field_meta[2].has_default, HasDefault::No));
        assert!(matches!(field_meta[3].has_default, HasDefault::No));
        assert!(matches!(field_meta[4].has_default, HasDefault::No));
        assert!(matches!(field_meta[5].has_default, HasDefault::No));
        assert!(matches!(field_meta[6].has_default, HasDefault::Yes));
    }

    #[tokio::test]
    async fn field_collect() {
        let pool = test_db().await;
        let relation = make_relation("blog".to_string(), RelationType::Table).await;
        let sort = Sort {
            relation: Some(relation.clone()),
            field: Some("id".to_string()),
            order: SortOrder::Ascending,
        };
        let fields = Field::collect(&pool, &relation, None, &sort, 0)
            .await
            .unwrap();

        assert_eq!(fields.total_count, 3);

        // Get the first result.
        let fields = fields.current_set.first().unwrap();

        assert_eq!(fields[0].name, "id".to_string());
        assert_eq!(fields[1].name, "title".to_string());
        assert_eq!(fields[2].name, "date".to_string());
        assert_eq!(fields[3].name, "content".to_string());
        assert_eq!(fields[4].name, "temperature".to_string());
        assert_eq!(fields[5].name, "tags".to_string());
        assert_eq!(fields[6].name, "published".to_string());
        assert_eq!(fields[0].kind, DataType::Integer);
        assert_eq!(fields[1].kind, DataType::String);
        assert_eq!(fields[2].kind, DataType::Date);
        assert_eq!(fields[3].kind, DataType::String);
        assert_eq!(fields[4].kind, DataType::Float);
        assert_eq!(fields[5].kind, DataType::Array);
        assert_eq!(fields[6].kind, DataType::Boolean);
        assert!(matches!(fields[0].is_identity, IsIdentity::Yes));
        assert!(matches!(fields[1].is_identity, IsIdentity::No));
        assert!(matches!(fields[2].is_identity, IsIdentity::No));
        assert!(matches!(fields[3].is_identity, IsIdentity::No));
        assert!(matches!(fields[4].is_identity, IsIdentity::No));
        assert!(matches!(fields[5].is_identity, IsIdentity::No));
        assert!(matches!(fields[6].is_identity, IsIdentity::No));
        assert!(matches!(fields[0].is_nullable, IsNullable::No));
        assert!(matches!(fields[1].is_nullable, IsNullable::No));
        assert!(matches!(fields[2].is_nullable, IsNullable::No));
        assert!(matches!(fields[3].is_nullable, IsNullable::No));
        assert!(matches!(fields[4].is_nullable, IsNullable::Yes));
        assert!(matches!(fields[5].is_nullable, IsNullable::Yes));
        assert!(matches!(fields[6].is_nullable, IsNullable::No));
        assert!(matches!(fields[0].has_default, HasDefault::No));
        assert!(matches!(fields[1].has_default, HasDefault::No));
        assert!(matches!(fields[2].has_default, HasDefault::No));
        assert!(matches!(fields[3].has_default, HasDefault::No));
        assert!(matches!(fields[4].has_default, HasDefault::No));
        assert!(matches!(fields[5].has_default, HasDefault::No));
        assert!(matches!(fields[6].has_default, HasDefault::Yes));
        assert_eq!(fields[0].toml_value.clone().unwrap(), TomlValue::Integer(1));
        assert_eq!(
            fields[1].toml_value.clone().unwrap(),
            TomlValue::String("one".to_string())
        );
        assert_eq!(
            fields[2].toml_value.clone().unwrap(),
            TomlValue::Datetime(TomlDatetime {
                date: Some(TomlDate {
                    year: 2026,
                    month: 1,
                    day: 1,
                }),
                time: None,
                offset: None,
            })
        );
        assert_eq!(
            fields[3].toml_value.clone().unwrap(),
            TomlValue::String("Some content here".to_string())
        );
        assert_eq!(
            fields[4].toml_value.clone().unwrap(),
            TomlValue::Float(32.0)
        );
        assert_eq!(
            fields[5].toml_value.clone().unwrap(),
            TomlValue::Array(vec![
                TomlValue::String("tag1".to_string()),
                TomlValue::String("tag2".to_string())
            ])
        );
        assert_eq!(
            fields[6].toml_value.clone().unwrap(),
            TomlValue::Boolean(true)
        );
    }

    #[tokio::test]
    async fn verify_char_types() {
        let pool = test_db().await;
        let relation = make_relation("character_types".to_string(), RelationType::Table).await;
        let sort = Sort {
            relation: Some(relation.clone()),
            field: Some("one".to_string()),
            order: SortOrder::Ascending,
        };
        let fields = Field::collect(&pool, &relation, None, &sort, 0)
            .await
            .unwrap();

        // Get the first result.
        let fields = fields.current_set.first().unwrap();

        assert_eq!(fields[0].kind, DataType::String);
        assert_eq!(fields[1].kind, DataType::String);
        assert_eq!(fields[2].kind, DataType::String);
        assert_eq!(fields[3].kind, DataType::String);
        assert_eq!(fields[4].kind, DataType::String);
        assert_eq!(fields[5].kind, DataType::String);
        assert_eq!(fields[6].kind, DataType::String);
    }

    #[tokio::test]
    async fn verify_integer_types() {
        let pool = test_db().await;
        let relation = make_relation("integer_types".to_string(), RelationType::Table).await;
        let sort = Sort {
            relation: Some(relation.clone()),
            field: Some("one".to_string()),
            order: SortOrder::Ascending,
        };
        let fields = Field::collect(&pool, &relation, None, &sort, 0)
            .await
            .unwrap();

        // Get the first result.
        let fields = fields.current_set.first().unwrap();

        assert_eq!(fields[0].kind, DataType::Integer);
        assert_eq!(fields[1].kind, DataType::Integer);
        assert_eq!(fields[2].kind, DataType::Integer);
        assert_eq!(fields[3].kind, DataType::Integer);
        assert_eq!(fields[4].kind, DataType::Integer);
        assert_eq!(fields[5].kind, DataType::Integer);
        assert_eq!(fields[6].kind, DataType::Integer);
        assert_eq!(fields[7].kind, DataType::Integer);
        assert_eq!(fields[8].kind, DataType::Integer);
        assert_eq!(fields[9].kind, DataType::Integer);
    }

    #[tokio::test]
    async fn verify_float32_types() {
        let pool = test_db().await;
        let relation = make_relation("float32_types".to_string(), RelationType::Table).await;
        let sort = Sort {
            relation: Some(relation.clone()),
            field: Some("one".to_string()),
            order: SortOrder::Ascending,
        };
        let fields = Field::collect(&pool, &relation, None, &sort, 0)
            .await
            .unwrap();

        // Get the first result.
        let fields = fields.current_set.first().unwrap();

        assert_eq!(fields[0].kind, DataType::Float);
        assert_eq!(fields[1].kind, DataType::Float);
    }

    #[tokio::test]
    async fn verify_float64_types() {
        let pool = test_db().await;
        let relation = make_relation("float64_types".to_string(), RelationType::Table).await;
        let sort = Sort {
            relation: Some(relation.clone()),
            field: Some("one".to_string()),
            order: SortOrder::Ascending,
        };
        let fields = Field::collect(&pool, &relation, None, &sort, 0)
            .await
            .unwrap();

        // Get the first result.
        let fields = fields.current_set.first().unwrap();

        assert_eq!(fields[0].kind, DataType::Float);
        assert_eq!(fields[1].kind, DataType::Float);
    }

    #[tokio::test]
    async fn verify_big_decimal_types() {
        let pool = test_db().await;
        let relation = make_relation("big_decimal_types".to_string(), RelationType::Table).await;
        let sort = Sort {
            relation: Some(relation.clone()),
            field: Some("one".to_string()),
            order: SortOrder::Ascending,
        };
        let fields = Field::collect(&pool, &relation, None, &sort, 0)
            .await
            .unwrap();

        // Get the first result.
        let fields = fields.current_set.first().unwrap();

        assert_eq!(fields[0].kind, DataType::Float);
        assert_eq!(fields[1].kind, DataType::Float);
        assert_eq!(fields[0].clone().toml_value.unwrap().as_float(), Some(3.1));
        assert_eq!(fields[1].clone().toml_value.unwrap().as_float(), Some(4.1));
    }

    #[tokio::test]
    async fn verify_date_types() {
        let pool = test_db().await;
        let relation = make_relation("date_types".to_string(), RelationType::Table).await;
        let sort = Sort {
            relation: Some(relation.clone()),
            field: Some("one".to_string()),
            order: SortOrder::Ascending,
        };
        let fields = Field::collect(&pool, &relation, None, &sort, 0)
            .await
            .unwrap();

        // Get the first result.
        let fields = fields.current_set.first().unwrap();

        assert_eq!(fields[0].kind, DataType::Date);
    }

    #[tokio::test]
    async fn verify_time_no_tz_types() {
        let pool = test_db().await;
        let relation = make_relation("time_types_no_tz".to_string(), RelationType::Table).await;
        let sort = Sort {
            relation: Some(relation.clone()),
            field: Some("one".to_string()),
            order: SortOrder::Ascending,
        };
        let fields = Field::collect(&pool, &relation, None, &sort, 0)
            .await
            .unwrap();

        // Get the first result.
        let fields = fields.current_set.first().unwrap();

        assert_eq!(fields[0].kind, DataType::Time);
        assert_eq!(fields[1].kind, DataType::Time);
        assert_eq!(fields[2].kind, DataType::Time);
    }

    #[tokio::test]
    async fn verify_time_no_tz_values() {
        let pool = test_db().await;
        let relation = make_relation("time_types_no_tz".to_string(), RelationType::Table).await;
        let sort = Sort {
            relation: Some(relation.clone()),
            field: Some("one".to_string()),
            order: SortOrder::Ascending,
        };
        let fields = Field::collect(&pool, &relation, None, &sort, 0)
            .await
            .unwrap();

        // Get the first result.
        let fields = fields.current_set.first().unwrap().clone();

        let time1 = fields[0].clone().toml_value.unwrap();
        let time2 = fields[1].clone().toml_value.unwrap();
        let time3 = fields[2].clone().toml_value.unwrap();

        assert!(time1.is_datetime());
        assert!(time2.is_datetime());
        assert!(time3.is_datetime());

        let time1 = time1.as_datetime().unwrap().time.unwrap();
        let time2 = time2.as_datetime().unwrap().time.unwrap();
        let time3 = time3.as_datetime().unwrap().time.unwrap();

        assert_eq!(time1.hour, 4);
        assert_eq!(time1.minute, 5);
        assert_eq!(time2.hour, 16);
        assert_eq!(time2.minute, 59);
        assert_eq!(time3.hour, 23);
        assert_eq!(time3.minute, 10);
        assert_eq!(time3.second, 10);
        assert_eq!(time3.nanosecond, 789_000_000);
    }

    #[tokio::test]
    async fn verify_time_with_tz_types() {
        let pool = test_db().await;
        let relation = make_relation("time_types_with_tz".to_string(), RelationType::Table).await;
        let sort = Sort {
            relation: Some(relation.clone()),
            field: Some("one".to_string()),
            order: SortOrder::Ascending,
        };
        let fields = Field::collect(&pool, &relation, None, &sort, 0)
            .await
            .unwrap();

        // Get the first result.
        let fields = fields.current_set.first().unwrap();

        assert_eq!(fields[0].kind, DataType::Other);
        assert_eq!(fields[1].kind, DataType::Other);
    }

    #[tokio::test]
    async fn verify_datetime_types() {
        let pool = test_db().await;
        let relation = make_relation("datetime_types".to_string(), RelationType::Table).await;
        let sort = Sort {
            relation: Some(relation.clone()),
            field: Some("one".to_string()),
            order: SortOrder::Ascending,
        };
        let fields = Field::collect(&pool, &relation, None, &sort, 0)
            .await
            .unwrap();

        // Get the first result.
        let fields = fields.current_set.first().unwrap();

        assert_eq!(fields[0].kind, DataType::TimestampNoTz);
        assert_eq!(fields[1].kind, DataType::TimestampNoTz);
        assert_eq!(fields[2].kind, DataType::TimestampWithTz);
        assert_eq!(fields[3].kind, DataType::TimestampWithTz);
    }

    #[tokio::test]
    async fn verify_datetime_values() {
        // Helper functions.
        fn get_date(f: &Field) -> toml::value::Date {
            f.toml_value
                .clone()
                .unwrap()
                .as_datetime()
                .unwrap()
                .date
                .unwrap()
        }
        fn get_time(f: &Field) -> toml::value::Time {
            f.toml_value
                .clone()
                .unwrap()
                .as_datetime()
                .unwrap()
                .time
                .unwrap()
        }
        fn get_offset(f: &Field) -> Option<toml::value::Offset> {
            f.toml_value.clone().unwrap().as_datetime().unwrap().offset
        }

        let pool = test_db().await;
        let relation = make_relation("datetime_types".to_string(), RelationType::Table).await;
        let sort = Sort {
            relation: Some(relation.clone()),
            field: Some("one".to_string()),
            order: SortOrder::Ascending,
        };
        let fields = Field::collect(&pool, &relation, None, &sort, 0)
            .await
            .unwrap();

        // Get the first result.
        let fields = fields.current_set.first().unwrap().clone();

        let dt1 = fields[0].clone();
        let dt2 = fields[1].clone();
        let dt3 = fields[2].clone();
        let dt4 = fields[3].clone();

        assert!(!dt1.parse_error);
        assert!(!dt2.parse_error);
        assert!(!dt3.parse_error);
        assert!(!dt4.parse_error);

        assert!(dt1.toml_value.is_some());
        assert!(dt2.toml_value.is_some());
        assert!(dt3.toml_value.is_some());
        assert!(dt4.toml_value.is_some());

        assert!(dt1.toml_value.clone().unwrap().is_datetime());
        assert!(dt2.toml_value.clone().unwrap().is_datetime());
        assert!(dt3.toml_value.clone().unwrap().is_datetime());
        assert!(dt4.toml_value.clone().unwrap().is_datetime());

        assert_eq!(
            get_date(&dt1),
            toml::value::Date {
                year: 1999,
                month: 1,
                day: 8
            }
        );
        assert_eq!(
            get_date(&dt2),
            toml::value::Date {
                year: 1999,
                month: 1,
                day: 9
            }
        );
        assert_eq!(
            get_date(&dt3),
            toml::value::Date {
                year: 1999,
                month: 1,
                day: 10
            }
        );
        assert_eq!(
            get_date(&dt4),
            toml::value::Date {
                year: 1999,
                month: 1,
                day: 10
            }
        );
        assert_eq!(get_offset(&dt1), None);
        assert_eq!(get_offset(&dt2), None);
        assert_eq!(
            get_offset(&dt3),
            Some(toml::value::Offset::Custom { minutes: -300 })
        );
        assert_eq!(
            get_offset(&dt4),
            Some(toml::value::Offset::Custom { minutes: -300 })
        );
        assert_eq!(
            get_time(&dt1),
            toml::value::Time {
                hour: 4,
                minute: 5,
                second: 6,
                nanosecond: 0
            }
        );
        assert_eq!(
            get_time(&dt2),
            toml::value::Time {
                hour: 4,
                minute: 5,
                second: 6,
                nanosecond: 0
            }
        );
        // Is this going to fail depnding on run during DST or ST?
        assert_eq!(
            get_time(&dt3),
            toml::value::Time {
                hour: 4,
                minute: 5,
                second: 6,
                nanosecond: 0
            }
        );
        // Is this going to fail depending on run during DST or ST?
        assert_eq!(
            get_time(&dt4),
            toml::value::Time {
                hour: 17,
                minute: 5,
                second: 6,
                nanosecond: 0
            }
        );
    }
}
