
use serde::{Deserialize, Deserializer, Serialize, Serializer};
use std::str::FromStr;
use strum_macros::{AsRefStr, Display};

#[cfg(feature = "sqlite")]
use sqlx::sqlite::SqliteTypeInfo;
#[cfg(feature = "mysql")]
use sqlx::{mysql::MySqlTypeInfo, Type};
#[cfg(feature = "sqlite")]
use sqlx::Type as SqliteType;


#[derive(Debug, PartialEq, Eq, Clone, AsRefStr, Display)]
pub enum FieldType {
    // 自定义类型
    JsonArray, // JSON
    JsonObject,
    Files,

    // 通用类型（适用于 SQLite 和 MySQL）
    Text,     // TEXT
    Real,     // REAL, FLOAT, DOUBLE
    Blob,     // BLOB
    Integer,  // INT, INTEGER, TINYINT, BIGINT, etc.
    Boolean,  // BOOLEAN or TINYINT(1)
    Date,     // DATE
    DateTime, // DATETIME
    Json,     // JSON

    // MySql 字符串类型
    Varchar,    // VARCHAR
    Char,       // CHAR
    Tinytext,   // TINYTEXT
    TextLarge,  // TEXT or large TEXT variants
    Mediumtext, // MEDIUMTEXT
    LongText,   // LONGTEXT

    // MySql 整数类型
    Tinyint,     // TINYINT
    Smallint,    // SMALLINT
    Mediumint,   // MEDIUMINT
    Bigint,      // BIGINT
    IntUnsigned, //  INT UNSIGNED

    // MySql 精确数值类型
    Decimal, // DECIMAL
    Numeric, // NUMERIC

    // MySql 浮点类型
    Float,  // FLOAT
    Double, // DOUBLE

    // MySql 日期时间类型
    Timestamp, // TIMESTAMP
    Time,      // TIME
    Year,      // YEAR

    // MySql 二进制类型
    Binary,     // BINARY
    Varbinary,  // VARBINARY
    Tinyblob,   // TINYBLOB
    Mediumblob, // MEDIUMBLOB
    Longblob,   // LONGBLOB

    // MySql 特殊类型
    Enum, // ENUM
    Set,  // SET

    // Access (Access) 字段类型
    Memo,       // Memo (large text, similar to LONGTEXT in MySql)
    OleObject,  // OLE Object (BLOB equivalent, stores binary data)
    Hyperlink,  // Hyperlink (stores URL or email address)
    Attachment, // Attachment (similar to Blob, stores file attachments)
    Currency,   // Currency (decimal type with 4 digits of precision, exact for money values)
    AutoNumber, // AutoNumber (auto-incremented LongInt)
    ShortText,  // Short Text (like VARCHAR, max 255 characters)
    // LongText,     // Long Text (similar to Memo, but newer in .accdb formats)
    Number,        // Number (generic number type)
    ReplicationID, // Replication ID (GUID)
    YesNo,         // Yes/No (Boolean, true/false)

    Unknown, // 未知类型作为默认处理
}

// 实现 FromStr：从字符串解析为 FieldType
// 实现 FromStr，支持带前缀的类型解析（如 field_type.）
impl FromStr for FieldType {
    type Err = String;

    fn from_str(s: &str) -> Result<Self, Self::Err> {
        match s.to_uppercase().as_str() {
            // 匹配已知类型
            "FILES" => Ok(FieldType::Files),

            "TEXT" => Ok(FieldType::Text),
            "REAL" => Ok(FieldType::Real),
            "BLOB" => Ok(FieldType::Blob),
            "INTEGER" => Ok(FieldType::Integer),
            "BOOLEAN" => Ok(FieldType::Boolean),
            "DATE" => Ok(FieldType::Date),
            "DATETIME" => Ok(FieldType::DateTime),
            "JSON" => Ok(FieldType::Json),
            "JSONARRAY" => Ok(FieldType::JsonArray),
            "JSONOBJECT" => Ok(FieldType::JsonObject),

            "VARCHAR" => Ok(FieldType::Varchar),
            "CHAR" => Ok(FieldType::Char),
            "TINYTEXT" => Ok(FieldType::Tinytext),
            "TEXT LARGE" => Ok(FieldType::TextLarge),
            "MEDIUMTEXT" => Ok(FieldType::Mediumtext),
            "LONGTEXT" => Ok(FieldType::LongText),
            "INT UNSIGNED" => Ok(FieldType::IntUnsigned),
            "TINYINT" => Ok(FieldType::Tinyint),
            "SMALLINT" => Ok(FieldType::Smallint),
            "MEDIUMINT" => Ok(FieldType::Mediumint),
            "BIGINT" => Ok(FieldType::Bigint),

            "FLOAT" => Ok(FieldType::Float),
            "DOUBLE" => Ok(FieldType::Double),
            "DECIMAL" => Ok(FieldType::Decimal),
            "NUMERIC" => Ok(FieldType::Numeric), // 添加 NUMERIC

            "TIMESTAMP" => Ok(FieldType::Timestamp),
            "TIME" => Ok(FieldType::Time),
            "YEAR" => Ok(FieldType::Year),
            "BINARY" => Ok(FieldType::Binary),
            "VARBINARY" => Ok(FieldType::Varbinary),
            "TINYBLOB" => Ok(FieldType::Tinyblob),
            "MEDIUMBLOB" => Ok(FieldType::Mediumblob),
            "LONGBLOB" => Ok(FieldType::Longblob),
            "ENUM" => Ok(FieldType::Enum),
            "SET" => Ok(FieldType::Set),

            // Access (Access) 字段类型
            "MEMO" => Ok(FieldType::Memo), // Memo (large text, similar to LONGTEXT in MySql)
            "OLEOBJECT" => Ok(FieldType::OleObject), // OLE Object (BLOB equivalent, stores binary data)
            "HYPERLINK" => Ok(FieldType::Hyperlink), // Hyperlink (stores URL or email address)
            "ATTACHMENT" => Ok(FieldType::Attachment), // Attachment (similar to Blob, stores file attachments)
            "CURRENCY" => Ok(FieldType::Currency), // Currency (decimal type with 4 digits of precision, exact for money values)
            "AUTONUMBER" => Ok(FieldType::AutoNumber), // AutoNumber (auto-incremented LongInt)
            "SHORTTEXT" => Ok(FieldType::ShortText), // Short Text (like VARCHAR, max 255 characters)
            // "LONGTEXT"=>Ok(FieldType::LongText),      // Long Text (similar to Memo, but newer in .accdb formats)
            "NUMBER" => Ok(FieldType::Number), // Number (generic number type)
            "REPLICATIONID" => Ok(FieldType::ReplicationID), // Replication ID (GUID)
            "YESNO" => Ok(FieldType::YesNo),   // Yes/No (Boolean, true/false)

            // 对于未知的类型，改为返回 FieldType::Unknown
            _ => Ok(FieldType::Unknown),
        }
    }
}
// 实现 Serialize：序列化为符合格式的字符串

impl Serialize for FieldType {
    fn serialize<S>(&self, serializer: S) -> Result<S::Ok, S::Error>
    where
        S: Serializer,
    {
        macro_rules! to_field_type_str {
            ($self:ident, $($variant:ident => $str:expr),*) => {
                match $self {
                    $(FieldType::$variant => $str),*
                }
            };
        }

        let s = to_field_type_str!(
            self,
            // 自定义数据类型
            JsonArray => "JSONARRAY",
            JsonObject => "JSONOBJECT",
            Files => "FILES",

            Blob => "BLOB",
            Boolean => "BOOLEAN",

            Json => "JSON",

            Timestamp => "TIMESTAMP",
            Time => "TIME",
            Year => "YEAR",
            Date => "DATE",
            DateTime => "DATETIME",

            Text => "TEXT",
            Varchar => "VARCHAR",
            Char => "CHAR",
            Tinytext => "TINYTEXT",
            TextLarge => "TEXT LARGE",
            Mediumtext => "MEDIUMTEXT",
            LongText => "LONGTEXT",

            Integer => "INTEGER",
            IntUnsigned => "INT UNSIGNED",
            Tinyint => "TINYINT",
            Smallint => "SMALLINT",
            Mediumint => "MEDIUMINT",
            Bigint => "BIGINT",
            Number=> "NUMBER",      // Number (generic number type)

            Real => "REAL",
            Float => "FLOAT",
            Double => "DOUBLE",
            Decimal => "DECIMAL",
            Numeric => "NUMERIC",  // 确保 Numeric 定义正确

            Binary => "BINARY",
            Varbinary => "VARBINARY",
            Tinyblob => "TINYBLOB",
            Mediumblob => "MEDIUMBLOB",
            Longblob => "LONGBLOB",
            Enum => "ENUM",
            Set => "SET",

            Memo=> "MEMO",         // Memo (large text, similar to LONGTEXT in MySql)
            OleObject=> "OLEOBJECT",    // OLE Object (BLOB equivalent, stores binary data)
            Hyperlink=> "HYPERLINK",    // Hyperlink (stores URL or email address)
            Attachment=> "ATTACHMENT",   // Attachment (similar to Blob, stores file attachments)
            Currency=> "CURRENCY",     // Currency (decimal type with 4 digits of precision, exact for money values)
            AutoNumber=> "AUTONUMBER",   // AutoNumber (auto-incremented LongInt)
            ShortText=> "SHORTTEXT",   // Short Text (like VARCHAR, max 255 characters)
            // LongText=> "field_type.longtext",    // Long Text (similar to Memo, but newer in .accdb formats)

            ReplicationID=> "REPLICATIONID",// Replication ID (GUID)
            YesNo=> "YESNO",        // Yes/No (Boolean, true/false)

            Unknown => "UNKNOWN"
        );

        serializer.serialize_str(s)
    }
}

impl<'de> Deserialize<'de> for FieldType {
    fn deserialize<D>(deserializer: D) -> Result<Self, D::Error>
    where
        D: Deserializer<'de>,
    {
        let s: String = String::deserialize(deserializer)?;
        FieldType::from_str(&s).map_err(serde::de::Error::custom)
    }
}

#[cfg(feature = "mysql")]
impl<'r> sqlx::Decode<'r, sqlx::MySql> for FieldType {
    fn decode(value: sqlx::mysql::MySqlValueRef<'r>) -> Result<Self, sqlx::error::BoxDynError> {
        let value_str = <String as sqlx::Decode<sqlx::MySql>>::decode(value)?;
        FieldType::from_str(&value_str).map_err(|e| e.into())
    }
}

#[cfg(feature = "sqlite")]
impl<'r> sqlx::Decode<'r, sqlx::Sqlite> for FieldType {
    fn decode(value: sqlx::sqlite::SqliteValueRef<'r>) -> Result<Self, sqlx::error::BoxDynError> {
        let value_str = <String as sqlx::Decode<sqlx::Sqlite>>::decode(value)?;
        FieldType::from_str(&value_str).map_err(|e| e.into())
    }
}

#[cfg(feature = "mysql")]
impl Type<sqlx::MySql> for FieldType {
    fn type_info() -> MySqlTypeInfo {
        <String as Type<sqlx::MySql>>::type_info()
    }
    fn compatible(ty: &MySqlTypeInfo) -> bool {
        <String as Type<sqlx::MySql>>::compatible(ty)
    }
}

#[cfg(feature = "sqlite")]
impl SqliteType<sqlx::Sqlite> for FieldType {
    fn type_info() -> SqliteTypeInfo {
        <String as SqliteType<sqlx::Sqlite>>::type_info()
    }
    fn compatible(ty: &SqliteTypeInfo) -> bool {
        <String as SqliteType<sqlx::Sqlite>>::compatible(ty)
    }
}

#[cfg(feature = "postgres")]
impl<'r> sqlx::Decode<'r, sqlx::Postgres> for FieldType {
    fn decode(value: sqlx::postgres::PgValueRef<'r>) -> Result<Self, sqlx::error::BoxDynError> {
        let value_str = <String as sqlx::Decode<sqlx::Postgres>>::decode(value)?;
        FieldType::from_str(&value_str).map_err(|e| e.into())
    }
}
