//! Model trait and utilities
//!
//! This module provides the core `Model` trait that all TideORM models implement,
//! inspired by Rails ActiveRecord, Laravel Eloquent, and Sequelize.
//!
//! ## Features
//!
//! - Static methods for querying (find, where_clause, paginate, etc.)
//! - Instance methods for record manipulation (save, update, delete, reload)
//! - Configuration methods for hidden attributes, translations, searchable fields
//! - Support for soft deletes, translations, and file attachments
//! - Index definitions for schema generation (like Sequelize TypeScript)

use async_trait::async_trait;
use std::collections::HashMap;

/// Index definition for database schema generation
/// 
/// Supports both regular and unique indexes with optional custom names.
/// 
/// # Example (in macro)
/// ```ignore
/// #[derive(Model)]
/// #[tide(
///     table = "users",
///     index = "email;name:first_name,last_name",
///     unique_index = "tenant_id,email"
/// )]
/// struct User { ... }
/// ```
#[derive(Debug, Clone, PartialEq)]
pub struct IndexDefinition {
    /// Index name (auto-generated if not specified)
    pub name: String,
    /// Column names in the index
    pub columns: Vec<String>,
    /// Whether this is a unique index
    pub unique: bool,
}

impl IndexDefinition {
    /// Create a new index definition
    pub fn new(name: impl Into<String>, columns: Vec<String>, unique: bool) -> Self {
        Self {
            name: name.into(),
            columns,
            unique,
        }
    }
    
    /// Parse index definitions from the macro format
    /// 
    /// Format: "field1", "field1,field2", or "name:field1,field2"
    /// Multiple indexes separated by semicolons: "email;name:first_name,last_name"
    pub fn parse(_table_name: &str, input: &str, unique: bool) -> Vec<Self> {
        if input.is_empty() {
            return vec![];
        }
        
        input
            .split(';')
            .filter(|s| !s.trim().is_empty())
            .map(|part| {
                let part = part.trim();
                let (name, columns) = if let Some((n, cols)) = part.split_once(':') {
                    (n.trim().to_string(), cols)
                } else {
                    // Auto-generate name: idx_tablename_columns or uidx_tablename_columns
                    let cols = part;
                    let prefix = if unique { "uidx" } else { "idx" };
                    let col_part = cols.replace(',', "_").replace(' ', "");
                    (format!("{}_{}", prefix, col_part), cols)
                };
                
                let columns: Vec<String> = columns
                    .split(',')
                    .map(|s| s.trim().to_string())
                    .filter(|s| !s.is_empty())
                    .collect();
                
                IndexDefinition::new(name, columns, unique)
            })
            .collect()
    }
}

use crate::error::{Error, Result};
use crate::query::QueryBuilder;

/// Metadata trait for model information
///
/// This trait provides compile-time information about a model.
/// It is automatically implemented by the `#[derive(Model)]` macro.
pub trait ModelMeta: Sized + Send + Sync + Clone + 'static {
    /// The type of the primary key
    type PrimaryKey: Send + Sync + Clone + std::fmt::Debug + std::fmt::Display + 'static;
    
    /// Returns the table name for this model
    fn table_name() -> &'static str;
    
    /// Returns the primary key column name
    fn primary_key_name() -> &'static str;
    
    /// Returns all column names in the database
    fn column_names() -> &'static [&'static str];
    
    /// Returns all field names in the struct
    fn field_names() -> &'static [&'static str];
    
    // =========================================================================
    // CONFIGURATION METHODS (can be overridden via macro attributes)
    // =========================================================================
    
    /// Returns hidden attributes that should not be exposed in API responses
    /// This applies to both model fields and file attachment fields
    fn hidden_attributes() -> Vec<&'static str> {
        vec!["deleted_at"]
    }
    
    /// Returns the default presenter name
    fn default_presenter() -> &'static str {
        "default"
    }
    
    /// Returns searchable fields for full-text search
    fn searchable_fields() -> Vec<&'static str> {
        vec![]
    }
    
    /// Returns the default order for queries
    fn default_order() -> Vec<(&'static str, &'static str)> {
        vec![("id", "DESC")]
    }
    
    /// Returns fields for option set label
    fn option_set_label() -> &'static str {
        "id"
    }
    
    /// Returns fields to search in option sets
    fn option_set_search_fields() -> Vec<&'static str> {
        vec![]
    }
    
    /// Returns translatable fields (for models with translations JSONB column)
    fn translatable_fields() -> Vec<&'static str> {
        vec![]
    }
    
    /// Returns allowed languages for translations
    /// 
    /// By default, returns languages from global TideConfig.
    /// Override in your model to specify model-specific languages.
    fn allowed_languages() -> Vec<String> {
        crate::config::Config::get_languages()
    }
    
    /// Returns the fallback language
    /// 
    /// By default, returns fallback from global TideConfig.
    /// Override in your model to specify a model-specific fallback.
    fn fallback_language() -> String {
        crate::config::Config::get_fallback_language()
    }
    
    /// Check if model has translations support
    fn has_translations() -> bool {
        !Self::translatable_fields().is_empty()
    }
    
    /// Returns single file attachment relations (hasOne)
    fn has_one_attached_file() -> Vec<&'static str> {
        vec![]
    }
    
    /// Returns multiple file attachment relations (hasMany)
    fn has_many_attached_files() -> Vec<&'static str> {
        vec![]
    }
    
    /// Returns all file relation names
    fn files_relations() -> Vec<&'static str> {
        let mut relations = Self::has_one_attached_file();
        relations.extend(Self::has_many_attached_files());
        relations
    }
    
    /// Check if model has file attachments support
    fn has_file_attachments() -> bool {
        !Self::files_relations().is_empty()
    }
    
    /// Check if model supports soft deletes
    fn soft_delete_enabled() -> bool {
        false
    }
    
    /// Check if model has timestamps (created_at, updated_at)
    /// 
    /// When true, TideORM automatically:
    /// - Sets `created_at` to now on save()
    /// - Sets `updated_at` to now on save() and update()
    fn has_timestamps() -> bool {
        false
    }
    
    // =========================================================================
    // INDEX DEFINITIONS (Sequelize-style)
    // =========================================================================
    
    /// Returns regular index definitions for this model
    /// 
    /// Override this in the macro to define indexes:
    /// ```ignore
    /// #[tide(index = "email;name:first_name,last_name")]
    /// ```
    fn indexes() -> Vec<IndexDefinition> {
        vec![]
    }
    
    /// Returns unique index definitions for this model
    /// 
    /// Override this in the macro to define unique indexes:
    /// ```ignore
    /// #[tide(unique_index = "tenant_id,email")]
    /// ```
    fn unique_indexes() -> Vec<IndexDefinition> {
        vec![]
    }
    
    /// Returns all index definitions (both regular and unique)
    fn all_indexes() -> Vec<IndexDefinition> {
        let mut all = Self::indexes();
        all.extend(Self::unique_indexes());
        all
    }
    
    /// Check if model has any index definitions
    fn has_indexes() -> bool {
        !Self::indexes().is_empty() || !Self::unique_indexes().is_empty()
    }
}

/// Core trait for TideORM models
///
/// This trait provides all the CRUD operations and query capabilities,
/// inspired by Rails ActiveRecord and Laravel Eloquent.
/// It is automatically implemented by the `#[derive(Model)]` macro.
///
/// ## Global Database Connection
///
/// TideORM uses a global database connection pattern. Initialize once at startup:
///
/// ```ignore
/// TideConfig::init()
///     .database("postgres://localhost/myapp")
///     .connect()
///     .await?;
/// ```
///
/// After initialization, all model methods work without passing a database reference:
///
/// ```ignore
/// let users = User::all().await?;
/// let user = user.save().await?;
/// user.delete().await?;
/// ```
#[async_trait]
pub trait Model: ModelMeta + crate::internal::InternalModel + serde::Serialize + for<'de> serde::Deserialize<'de> {
    /// Get the primary key value of this instance
    fn primary_key(&self) -> Self::PrimaryKey;
    
    /// Get the global database connection
    ///
    /// This is a convenience method to access the database from within model code.
    ///
    /// # Example
    /// ```ignore
    /// let db = User::db();
    /// // or from an instance
    /// let db = user.database();
    /// ```
    fn db() -> &'static crate::database::Database {
        crate::database::db()
    }
    
    /// Get the global database connection from an instance
    ///
    /// # Example
    /// ```ignore
    /// let user = User::find(1).await?;
    /// let db = user.database();
    /// ```
    fn database(&self) -> &'static crate::database::Database {
        crate::database::db()
    }
    
    // =========================================================================
    // STATIC METHODS (Class-level operations) - Use global connection
    // =========================================================================
    
    /// Get all records from the table
    ///
    /// # Example
    /// ```ignore
    /// let users = User::all().await?;
    /// ```
    async fn all() -> Result<Vec<Self>>
    where
        Self: Sized,
    {
        crate::internal::QueryExecutor::find_all::<Self>(crate::database::db().__internal_connection()).await
    }
    
    /// Start a query builder for this model
    ///
    /// # Example
    /// ```ignore
    /// let active_users = User::query()
    ///     .where_eq("status", "active")
    ///     .order_by("name", Order::Asc)
    ///     .get()
    ///     .await?;
    /// ```
    fn query() -> QueryBuilder<Self>
    where
        Self: Sized,
    {
        QueryBuilder::new()
    }
    
    /// Count all records (uses efficient SQL COUNT)
    ///
    /// # Example
    /// ```ignore
    /// let total = User::count().await?;
    /// ```
    async fn count() -> Result<u64>
    where
        Self: Sized,
    {
        crate::internal::QueryExecutor::count::<Self>(crate::database::db().__internal_connection(), None).await
    }
    
    /// Check if any records exist
    ///
    /// # Example
    /// ```ignore
    /// if User::exists_any().await? {
    ///     println!("We have users!");
    /// }
    /// ```
    async fn exists_any() -> Result<bool>
    where
        Self: Sized,
    {
        Ok(Self::count().await? > 0)
    }
    
    // =========================================================================
    // BATCH OPERATIONS
    // =========================================================================
    
    /// Insert multiple records at once (efficient batch insert)
    ///
    /// # Example
    /// ```ignore
    /// let users = vec![
    ///     User { id: 0, name: "John".into(), email: "john@example.com".into() },
    ///     User { id: 0, name: "Jane".into(), email: "jane@example.com".into() },
    /// ];
    /// let inserted = User::insert_all(users).await?;
    /// println!("Inserted {} users", inserted.len());
    /// ```
    async fn insert_all(models: Vec<Self>) -> Result<Vec<Self>>
    where
        Self: Sized,
    {
        let mut results = Vec::with_capacity(models.len());
        for model in models {
            results.push(model.save().await?);
        }
        Ok(results)
    }
    
    /// Insert or update a record (upsert)
    ///
    /// If a conflict occurs on the specified column(s), the record will be updated.
    /// This is useful for "insert or update" logic where you want to ensure a record
    /// exists with certain values.
    ///
    /// # Arguments
    /// * `conflict_columns` - Column name(s) to check for conflicts (e.g., vec!["email"] or vec!["tenant_id", "email"])
    ///
    /// # Example
    /// ```ignore
    /// // Insert or update user by email
    /// let user = User { id: 0, email: "john@example.com".into(), name: "John".into() };
    /// let user = User::insert_or_update(user, vec!["email"]).await?;
    ///
    /// // Insert or update by composite key
    /// let config = Config { tenant_id: 1, key: "theme".into(), value: "dark".into() };
    /// let config = Config::insert_or_update(config, vec!["tenant_id", "key"]).await?;
    /// ```
    async fn insert_or_update(
        model: Self,
        conflict_columns: Vec<&str>,
    ) -> Result<Self>
    where
        Self: Sized;
    
    /// Create an on-conflict builder for advanced upsert scenarios
    ///
    /// This provides more control over the upsert behavior, allowing you to
    /// specify which columns to update on conflict.
    ///
    /// # Example
    /// ```ignore
    /// // Update only specific columns on conflict
    /// let user = User::on_conflict(vec!["email"])
    ///     .update_columns(vec!["name", "updated_at"])
    ///     .insert(user)
    ///     .await?;
    ///
    /// // Or update all columns except the conflict column
    /// let user = User::on_conflict(vec!["email"])
    ///     .update_all_except(vec!["id", "email", "created_at"])
    ///     .insert(user)
    ///     .await?;
    /// ```
    fn on_conflict(conflict_columns: Vec<&str>) -> OnConflictBuilder<Self>
    where
        Self: Sized,
    {
        OnConflictBuilder::new(conflict_columns.into_iter().map(|s| s.to_string()).collect())
    }
    
    /// Update multiple records matching conditions
    ///
    /// Returns the number of rows affected.
    ///
    /// # Example
    /// ```ignore
    /// // Deactivate all users who haven't logged in for 1 year
    /// let affected = User::update_all()
    ///     .set("active", false)
    ///     .where_eq("last_login_before", "2024-01-01")
    ///     .execute()
    ///     .await?;
    /// ```
    fn update_all() -> BatchUpdateBuilder<Self>
    where
        Self: Sized,
    {
        BatchUpdateBuilder::new()
    }
    
    /// Execute a closure within a database transaction
    ///
    /// This provides a model-centric way to run transactions.
    /// The transaction is automatically committed if the closure returns `Ok`,
    /// and rolled back if it returns `Err` or panics.
    ///
    /// # Example
    /// ```ignore
    /// User::transaction(|tx| async move {
    ///     // All operations in here are in a transaction
    ///     // Use tx for manual queries if needed
    ///     let user = User::create(User { ... }).await?;
    ///     let profile = Profile::create(Profile { user_id: user.id, ... }).await?;
    ///     Ok((user, profile))
    /// }).await?;
    /// ```
    async fn transaction<F, Fut, T>(f: F) -> Result<T>
    where
        Self: Sized,
        F: FnOnce(crate::database::Transaction) -> Fut + Send,
        Fut: std::future::Future<Output = Result<T>> + Send,
        T: Send,
    {
        crate::database::db().transaction(f).await
    }
    
    /// Get the first record
    ///
    /// # Example
    /// ```ignore
    /// if let Some(user) = User::first().await? {
    ///     println!("First user: {}", user.name);
    /// }
    /// ```
    async fn first() -> Result<Option<Self>>
    where
        Self: Sized,
    {
        crate::internal::QueryExecutor::first::<Self>(crate::database::db().__internal_connection()).await
    }
    
    /// Get the last record (by primary key descending)
    ///
    /// # Example
    /// ```ignore
    /// let newest = User::last().await?;
    /// ```
    async fn last() -> Result<Option<Self>>
    where
        Self: Sized,
    {
        crate::internal::QueryExecutor::last::<Self>(crate::database::db().__internal_connection()).await
    }
    
    /// Paginate records
    ///
    /// # Example
    /// ```ignore
    /// let page_2 = User::paginate(2, 10).await?; // Page 2, 10 per page
    /// ```
    async fn paginate(page: u64, per_page: u64) -> Result<Vec<Self>>
    where
        Self: Sized,
    {
        let offset = (page.saturating_sub(1)) * per_page;
        crate::internal::QueryExecutor::paginate::<Self>(crate::database::db().__internal_connection(), per_page, offset).await
    }
    
    // =========================================================================
    // METHODS IMPLEMENTED BY MACRO (need access to primary key type)
    // These are declared here but implemented by the derive macro
    // =========================================================================
    
    /// Find record by primary key
    /// Implemented by the derive macro
    async fn find(id: Self::PrimaryKey) -> Result<Option<Self>>
    where
        Self: Sized;
    
    /// Find record by primary key or fail with NotFound error
    async fn find_or_fail(id: Self::PrimaryKey) -> Result<Self>
    where
        Self: Sized,
    {
        let id_display = format!("{}", id);
        Self::find(id).await?.ok_or_else(|| {
            Error::not_found(format!("{} with {} = {} not found", Self::table_name(), Self::primary_key_name(), id_display))
        })
    }
    
    /// Check if record exists by ID
    async fn exists(id: Self::PrimaryKey) -> Result<bool>
    where
        Self: Sized,
    {
        Ok(Self::find(id).await?.is_some())
    }
    
    /// Create a new record from model instance
    /// Implemented by the derive macro
    async fn create(model: Self) -> Result<Self>
    where
        Self: Sized;
    
    /// Delete record by ID
    /// Implemented by the derive macro
    async fn destroy(id: Self::PrimaryKey) -> Result<u64>
    where
        Self: Sized;
    
    // =========================================================================
    // INSTANCE METHODS (Record-level operations) - Use global connection
    // =========================================================================
    
    /// Save this record to the database (insert)
    ///
    /// # Example
    /// ```ignore
    /// let user = User { id: 0, name: "John".into(), email: "john@example.com".into() };
    /// let user = user.save().await?;
    /// ```
    async fn save(self) -> Result<Self>
    where
        Self: Sized;
    
    /// Update this record in the database
    ///
    /// # Example
    /// ```ignore
    /// user.name = "Jane".to_string();
    /// let user = user.update().await?;
    /// ```
    async fn update(self) -> Result<Self>
    where
        Self: Sized;
    
    /// Delete this instance from the database
    ///
    /// # Example
    /// ```ignore
    /// let user = User::find_or_fail(1).await?;
    /// user.delete().await?;
    /// ```
    async fn delete(self) -> Result<u64>
    where
        Self: Sized;
    
    /// Internal method for insert with conflict handling
    /// This is implemented by the derive macro and should not be called directly
    #[doc(hidden)]
    async fn __insert_with_conflict(model: Self, builder: OnConflictBuilder<Self>) -> Result<Self>
    where
        Self: Sized;
    
    /// Reload the instance from database
    ///
    /// # Example
    /// ```ignore
    /// let user = user.reload().await?;
    /// ```
    async fn reload(&self) -> Result<Self>
    where
        Self: Sized,
    {
        Self::find_or_fail(self.primary_key()).await
    }
    
    /// Check if this record is new (not yet saved)
    fn is_new(&self) -> bool {
        false
    }
    
    // =========================================================================
    // JSON SERIALIZATION
    // =========================================================================
    
    /// Convert model to JSON with options
    ///
    /// This method provides a comprehensive JSON representation that:
    /// - Excludes hidden attributes
    /// - Applies translations based on language option
    /// - Flattens file attachments to root level
    /// - Excludes hidden file attributes
    ///
    /// # Arguments
    /// * `options` - Optional HashMap with keys:
    ///   - "language": Language code for translations (e.g., "en", "fr", "ar")
    ///   - "presenter": Presenter name (e.g., "default", "minimal", "full")
    ///
    /// # Example
    /// ```ignore
    /// // Without options (uses defaults)
    /// let json = user.to_json(None);
    ///
    /// // With language option
    /// let mut opts = HashMap::new();
    /// opts.insert("language".to_string(), "fr".to_string());
    /// let json = user.to_json(Some(opts));
    /// ```
    fn to_json(&self, options: Option<HashMap<String, String>>) -> serde_json::Value
    where
        Self: serde::Serialize,
    {
        let opts = options.unwrap_or_default();
        let fallback = Self::fallback_language();
        let current_language = opts.get("language")
            .map(|s| s.as_str())
            .unwrap_or(&fallback);
        let _current_presenter = opts.get("presenter")
            .map(|s| s.as_str())
            .unwrap_or(Self::default_presenter());
        
        let hidden = Self::hidden_attributes();
        let translatable = Self::translatable_fields();
        let file_relations = Self::files_relations();
        
        // Serialize the model to JSON
        let mut json = match serde_json::to_value(self) {
            Ok(serde_json::Value::Object(map)) => map,
            _ => return serde_json::json!({}),
        };
        
        // Remove hidden attributes
        for attr in &hidden {
            json.remove(*attr);
        }
        
        // Apply translations if model has translations support
        if Self::has_translations() {
            if let Some(translations) = json.get("translations").cloned() {
                if let Some(trans_obj) = translations.as_object() {
                    for field in &translatable {
                        if let Some(field_trans) = trans_obj.get(*field) {
                            if let Some(field_obj) = field_trans.as_object() {
                                // Try to get the current language, fallback if not found
                                if let Some(value) = field_obj.get(current_language)
                                    .or_else(|| field_obj.get(&fallback))
                                {
                                    json.insert(field.to_string(), value.clone());
                                }
                            }
                        }
                    }
                }
                // Remove the raw translations object from output
                json.remove("translations");
            }
        }
        
        // Flatten file attachments to root level
        if Self::has_file_attachments() {
            if let Some(files) = json.remove("files") {
                if let Some(files_obj) = files.as_object() {
                    for relation in &file_relations {
                        if let Some(file_data) = files_obj.get(*relation) {
                            // Process file data to remove hidden attributes
                            let processed = Self::process_file_for_json(file_data, &hidden);
                            json.insert(relation.to_string(), processed);
                        }
                    }
                }
            }
        }
        
        serde_json::Value::Object(json)
    }
    
    /// Process file data for JSON output, removing hidden attributes
    fn process_file_for_json(
        file_data: &serde_json::Value,
        hidden_attrs: &[&str],
    ) -> serde_json::Value {
        match file_data {
            serde_json::Value::Object(obj) => {
                let mut cleaned = serde_json::Map::new();
                for (key, value) in obj {
                    if !hidden_attrs.contains(&key.as_str()) {
                        cleaned.insert(key.clone(), value.clone());
                    }
                }
                serde_json::Value::Object(cleaned)
            }
            serde_json::Value::Array(arr) => {
                let cleaned: Vec<serde_json::Value> = arr
                    .iter()
                    .map(|item| Self::process_file_for_json(item, hidden_attrs))
                    .collect();
                serde_json::Value::Array(cleaned)
            }
            other => other.clone(),
        }
    }
    
    /// Convert a collection of models to JSON array
    ///
    /// # Arguments
    /// * `models` - Vector of models to convert
    /// * `options` - Optional HashMap with "language" and "presenter" keys
    fn collection_to_json(models: Vec<Self>, options: Option<HashMap<String, String>>) -> serde_json::Value
    where
        Self: serde::Serialize,
    {
        let items: Vec<serde_json::Value> = models
            .iter()
            .map(|model| model.to_json(options.clone()))
            .collect();
        serde_json::Value::Array(items)
    }
    
    /// Convert model to HashMap (legacy support)
    fn to_hash_map(&self) -> HashMap<String, String>
    where
        Self: serde::Serialize,
    {
        let json = self.to_json(None);
        let mut map = HashMap::new();
        
        if let Some(obj) = json.as_object() {
            for (key, value) in obj {
                if key != "params" {
                    let str_val = match value {
                        serde_json::Value::String(s) => s.clone(),
                        serde_json::Value::Number(n) => n.to_string(),
                        serde_json::Value::Bool(b) => b.to_string(),
                        serde_json::Value::Null => "null".to_string(),
                        _ => value.to_string(),
                    };
                    map.insert(key.clone(), str_val);
                }
            }
        }
        
        map
    }
    
    // =========================================================================
    // TRANSLATION METHODS (for models with 'translations' JSONB column)
    // =========================================================================
    
    /// Load translations for a specific language into model fields
    fn load_language_translations(&mut self, _language: &str) -> std::result::Result<(), String> {
        if !Self::has_translations() {
            return Ok(()); // Skip if model doesn't have translations
        }
        // Default implementation - override in models with translations
        Ok(())
    }
    
    /// Load all translations for all languages into model fields
    fn load_all_translations(&mut self) -> std::result::Result<(), String> {
        if !Self::has_translations() {
            return Ok(()); // Skip if model doesn't have translations
        }
        // Default implementation - override in models with translations
        Ok(())
    }
    
    /// Extract translations from data HashMap for saving
    fn extract_translations(data: &mut HashMap<String, serde_json::Value>) -> std::result::Result<serde_json::Value, String> {
        if !Self::has_translations() {
            return Ok(serde_json::json!({}));
        }
        
        let mut translations = serde_json::Map::new();
        let translatable = Self::translatable_fields();
        
        for field in translatable {
            if let Some(value) = data.get(field) {
                if value.is_object() {
                    translations.insert(field.to_string(), value.clone());
                    data.remove(field);
                }
            }
        }
        
        Ok(serde_json::Value::Object(translations))
    }
    
    // =========================================================================
    // FILE ATTACHMENT METHODS (for models with 'files' JSONB column)
    // =========================================================================
    
    /// Get files from the JSONB 'files' column
    fn get_files_attribute(&self) -> std::result::Result<HashMap<String, serde_json::Value>, String> {
        if !Self::has_file_attachments() {
            return Ok(HashMap::new());
        }
        // Default implementation - override in models with files JSONB column
        Ok(HashMap::new())
    }
    
    /// Set files to the JSONB 'files' column
    fn set_files_attribute(&mut self, _files: HashMap<String, serde_json::Value>) -> std::result::Result<(), String> {
        if !Self::has_file_attachments() {
            return Ok(());
        }
        // Default implementation - override in models with files JSONB column
        Ok(())
    }
    
    /// Attach a file to a specific relation type
    ///
    /// # Arguments
    /// * `relation_type` - The relation type (e.g., "thumbnail", "images")
    /// * `file_key` - The file key/path to attach
    fn attach_file(&mut self, relation_type: &str, file_key: &str) -> std::result::Result<(), String> {
        if !Self::has_file_attachments() {
            return Err("Model does not support file attachments".to_string());
        }
        
        let mut files = self.get_files_attribute()?;
        
        let file_metadata = serde_json::json!({
            "key": file_key,
            "filename": file_key.split('/').next_back().unwrap_or(file_key),
            "created_at": chrono::Utc::now().to_rfc3339(),
        });
        
        if Self::has_one_attached_file().contains(&relation_type) {
            files.insert(relation_type.to_string(), file_metadata);
        } else if Self::has_many_attached_files().contains(&relation_type) {
            let mut array = files.get(relation_type)
                .and_then(|v| v.as_array().cloned())
                .unwrap_or_default();
            array.push(file_metadata);
            files.insert(relation_type.to_string(), serde_json::Value::Array(array));
        } else {
            return Err(format!("Unknown file relation: {}", relation_type));
        }
        
        self.set_files_attribute(files)?;
        Ok(())
    }
    
    /// Attach multiple files to a relation type (hasMany only)
    fn attach_files(&mut self, relation_type: &str, file_keys: Vec<&str>) -> std::result::Result<(), String> {
        if !Self::has_file_attachments() {
            return Err("Model does not support file attachments".to_string());
        }
        
        if !Self::has_many_attached_files().contains(&relation_type) {
            return Err(format!("Relation '{}' is not a hasMany relation", relation_type));
        }
        
        for file_key in file_keys {
            self.attach_file(relation_type, file_key)?;
        }
        
        Ok(())
    }
    
    /// Detach a file from a relation type
    fn detach_file(&mut self, relation_type: &str, file_key: Option<&str>) -> std::result::Result<(), String> {
        if !Self::has_file_attachments() {
            return Err("Model does not support file attachments".to_string());
        }
        
        let mut files = self.get_files_attribute()?;
        
        if let Some(key) = file_key {
            if Self::has_one_attached_file().contains(&relation_type) {
                if let Some(current) = files.get(relation_type) {
                    if current.get("key").and_then(|k| k.as_str()) == Some(key) {
                        files.insert(relation_type.to_string(), serde_json::Value::Null);
                    }
                }
            } else if Self::has_many_attached_files().contains(&relation_type) {
                if let Some(array) = files.get(relation_type).and_then(|v| v.as_array()) {
                    let filtered: Vec<serde_json::Value> = array.iter()
                        .filter(|item| item.get("key").and_then(|k| k.as_str()) != Some(key))
                        .cloned()
                        .collect();
                    files.insert(relation_type.to_string(), serde_json::Value::Array(filtered));
                }
            }
        } else {
            if Self::has_one_attached_file().contains(&relation_type) {
                files.insert(relation_type.to_string(), serde_json::Value::Null);
            } else if Self::has_many_attached_files().contains(&relation_type) {
                files.insert(relation_type.to_string(), serde_json::Value::Array(vec![]));
            }
        }
        
        self.set_files_attribute(files)?;
        Ok(())
    }
    
    /// Sync files for a relation type (replaces all existing files)
    fn sync_files(&mut self, relation_type: &str, file_keys: Vec<&str>) -> std::result::Result<(), String> {
        if !Self::has_file_attachments() {
            return Err("Model does not support file attachments".to_string());
        }
        
        let mut files = self.get_files_attribute()?;
        
        if file_keys.is_empty() {
            if Self::has_one_attached_file().contains(&relation_type) {
                files.insert(relation_type.to_string(), serde_json::Value::Null);
            } else if Self::has_many_attached_files().contains(&relation_type) {
                files.insert(relation_type.to_string(), serde_json::Value::Array(vec![]));
            }
        } else {
            if Self::has_one_attached_file().contains(&relation_type) {
                let file_metadata = serde_json::json!({
                    "key": file_keys[0],
                    "filename": file_keys[0].split('/').next_back().unwrap_or(file_keys[0]),
                    "created_at": chrono::Utc::now().to_rfc3339(),
                });
                files.insert(relation_type.to_string(), file_metadata);
            } else if Self::has_many_attached_files().contains(&relation_type) {
                let file_array: Vec<serde_json::Value> = file_keys.iter().map(|key| {
                    serde_json::json!({
                        "key": key,
                        "filename": key.split('/').next_back().unwrap_or(key),
                        "created_at": chrono::Utc::now().to_rfc3339(),
                    })
                }).collect();
                files.insert(relation_type.to_string(), serde_json::Value::Array(file_array));
            } else {
                return Err(format!("Unknown file relation: {}", relation_type));
            }
        }
        
        self.set_files_attribute(files)?;
        Ok(())
    }
    
    /// Extract files from data HashMap for saving
    fn extract_files(data: &mut HashMap<String, serde_json::Value>) -> std::result::Result<serde_json::Value, String> {
        if !Self::has_file_attachments() {
            return Ok(serde_json::json!({}));
        }
        
        let mut files = serde_json::Map::new();
        let file_relations = Self::files_relations();
        
        for relation in file_relations {
            if let Some(value) = data.remove(relation) {
                files.insert(relation.to_string(), value);
            }
        }
        
        Ok(serde_json::Value::Object(files))
    }
}

/// Builder for creating new model instances
pub struct CreateBuilder<M: ModelMeta> {
    _marker: std::marker::PhantomData<M>,
    values: std::collections::HashMap<String, serde_json::Value>,
}

impl<M: ModelMeta> CreateBuilder<M> {
    /// Create a new builder
    pub fn new() -> Self {
        Self {
            _marker: std::marker::PhantomData,
            values: std::collections::HashMap::new(),
        }
    }
    
    /// Set a field value
    pub fn set(mut self, field: &str, value: impl Into<serde_json::Value>) -> Self {
        self.values.insert(field.to_string(), value.into());
        self
    }
}

impl<M: ModelMeta> Default for CreateBuilder<M> {
    fn default() -> Self {
        Self::new()
    }
}

/// Builder for updating model instances
pub struct UpdateBuilder<M: Model> {
    model: M,
    changes: std::collections::HashMap<String, serde_json::Value>,
}

impl<M: Model> UpdateBuilder<M> {
    /// Create a new update builder
    pub fn new(model: M) -> Self {
        Self {
            model,
            changes: std::collections::HashMap::new(),
        }
    }
    
    /// Set a field to a new value
    pub fn set(mut self, field: &str, value: impl Into<serde_json::Value>) -> Self {
        self.changes.insert(field.to_string(), value.into());
        self
    }
    
    /// Save the changes to the database
    pub async fn save(self) -> Result<M> {
        self.model.update().await
    }
}

/// Builder for on-conflict (upsert) operations
/// 
/// Provides fine-grained control over what happens when a conflict occurs during insert.
///
/// # Example
/// ```ignore
/// // Update specific columns on conflict
/// let user = User::on_conflict("email")
///     .update_columns(vec!["name", "updated_at"])
///     .insert(user)
///     .await?;
///
/// // Update all columns except specified ones
/// let user = User::on_conflict(vec!["tenant_id", "key"])
///     .update_all_except(vec!["id", "created_at"])
///     .insert(config)
///     .await?;
/// ```
#[doc(hidden)]
pub struct OnConflictBuilder<M: Model> {
    pub _marker: std::marker::PhantomData<M>,
    pub conflict_columns: Vec<String>,
    pub update_columns: Option<Vec<String>>,
    pub exclude_columns: Option<Vec<String>>,
}

impl<M: Model> OnConflictBuilder<M> {
    /// Create a new on-conflict builder
    pub fn new(conflict_columns: Vec<String>) -> Self {
        Self {
            _marker: std::marker::PhantomData,
            conflict_columns,
            update_columns: None,
            exclude_columns: None,
        }
    }
    
    /// Specify which columns to update on conflict
    /// 
    /// Only these columns will be updated when a conflict occurs.
    pub fn update_columns(mut self, columns: Vec<&str>) -> Self {
        self.update_columns = Some(columns.into_iter().map(|s| s.to_string()).collect());
        self
    }
    
    /// Update all columns except the specified ones
    /// 
    /// Useful when you want to update most fields but exclude primary keys, timestamps, etc.
    pub fn update_all_except(mut self, columns: Vec<&str>) -> Self {
        self.exclude_columns = Some(columns.into_iter().map(|s| s.to_string()).collect());
        self
    }
    
    /// Execute the insert with on-conflict handling
    pub async fn insert(self, model: M) -> Result<M>
    where
        M: Sized,
    {
        M::__insert_with_conflict(model, self).await
    }
}

/// Builder for batch update operations
/// 
/// Allows updating multiple records matching conditions.
///
/// # Example
/// ```ignore
/// let affected = User::update_all()
///     .set("active", false)
///     .where_eq("status", "banned")
///     .execute()
///     .await?;
/// ```
pub struct BatchUpdateBuilder<M: Model> {
    _marker: std::marker::PhantomData<M>,
    updates: std::collections::HashMap<String, serde_json::Value>,
    conditions: Vec<crate::query::WhereCondition>,
}

impl<M: Model> BatchUpdateBuilder<M> {
    /// Create a new batch update builder
    pub fn new() -> Self {
        Self {
            _marker: std::marker::PhantomData,
            updates: std::collections::HashMap::new(),
            conditions: Vec::new(),
        }
    }
    
    /// Set a field to a new value
    pub fn set(mut self, field: &str, value: impl Into<serde_json::Value>) -> Self {
        self.updates.insert(field.to_string(), value.into());
        self
    }
    
    /// Add a where equals condition
    pub fn where_eq(mut self, column: &str, value: impl Into<serde_json::Value>) -> Self {
        self.conditions.push(crate::query::WhereCondition {
            column: column.to_string(),
            operator: crate::query::Operator::Eq,
            value: crate::query::ConditionValue::Single(value.into()),
        });
        self
    }
    
    /// Add a where not equals condition
    pub fn where_not(mut self, column: &str, value: impl Into<serde_json::Value>) -> Self {
        self.conditions.push(crate::query::WhereCondition {
            column: column.to_string(),
            operator: crate::query::Operator::NotEq,
            value: crate::query::ConditionValue::Single(value.into()),
        });
        self
    }
    
    /// Add a where greater than condition
    pub fn where_gt(mut self, column: &str, value: impl Into<serde_json::Value>) -> Self {
        self.conditions.push(crate::query::WhereCondition {
            column: column.to_string(),
            operator: crate::query::Operator::Gt,
            value: crate::query::ConditionValue::Single(value.into()),
        });
        self
    }
    
    /// Add a where less than condition
    pub fn where_lt(mut self, column: &str, value: impl Into<serde_json::Value>) -> Self {
        self.conditions.push(crate::query::WhereCondition {
            column: column.to_string(),
            operator: crate::query::Operator::Lt,
            value: crate::query::ConditionValue::Single(value.into()),
        });
        self
    }
    
    /// Add a where IN condition
    pub fn where_in<V: Into<serde_json::Value>>(mut self, column: &str, values: Vec<V>) -> Self {
        self.conditions.push(crate::query::WhereCondition {
            column: column.to_string(),
            operator: crate::query::Operator::In,
            value: crate::query::ConditionValue::List(values.into_iter().map(|v| v.into()).collect()),
        });
        self
    }
    
    /// Execute the batch update and return rows affected
    pub async fn execute(self) -> Result<u64> {
        if self.updates.is_empty() {
            return Ok(0);
        }
        
        // Build SET clause
        let set_parts: Vec<String> = self.updates.iter()
            .map(|(k, v)| {
                let value_str = match v {
                    serde_json::Value::Null => "NULL".to_string(),
                    serde_json::Value::Bool(b) => b.to_string(),
                    serde_json::Value::Number(n) => n.to_string(),
                    serde_json::Value::String(s) => format!("'{}'", s.replace('\'', "''")),
                    _ => format!("'{}'", v.to_string().replace('\'', "''")),
                };
                format!("\"{}\" = {}", k, value_str)
            })
            .collect();
        
        // Build WHERE clause
        let where_parts: Vec<String> = self.conditions.iter()
            .filter_map(|cond| {
                let op = match &cond.operator {
                    crate::query::Operator::Eq => "=",
                    crate::query::Operator::NotEq => "!=",
                    crate::query::Operator::Gt => ">",
                    crate::query::Operator::Lt => "<",
                    crate::query::Operator::Gte => ">=",
                    crate::query::Operator::Lte => "<=",
                    _ => return None,
                };
                
                let value = match &cond.value {
                    crate::query::ConditionValue::Single(v) => {
                        match v {
                            serde_json::Value::Null => "NULL".to_string(),
                            serde_json::Value::Bool(b) => b.to_string(),
                            serde_json::Value::Number(n) => n.to_string(),
                            serde_json::Value::String(s) => format!("'{}'", s.replace('\'', "''")),
                            _ => format!("'{}'", v.to_string().replace('\'', "''")),
                        }
                    },
                    _ => return None,
                };
                
                Some(format!("\"{}\" {} {}", cond.column, op, value))
            })
            .collect();
        
        let mut sql = format!(
            "UPDATE \"{}\" SET {}",
            M::table_name(),
            set_parts.join(", ")
        );
        
        if !where_parts.is_empty() {
            sql.push_str(" WHERE ");
            sql.push_str(&where_parts.join(" AND "));
        }
        
        crate::Database::execute(&sql).await
    }
}

impl<M: Model> Default for BatchUpdateBuilder<M> {
    fn default() -> Self {
        Self::new()
    }
}
