//! Database connection pool management via `dao!` macro.
//!
//! Provides declarative database pool creation — declare a name and relative path,
//! get a Clone-able struct with automatic pool management.
//!
//! SQLite best practices are applied automatically:
//! - `PRAGMA busy_timeout = 5000` (wait up to 5s instead of failing immediately on lock)
//! - `PRAGMA journal_mode = WAL` (concurrent reads + single writer)
//!
//! These are set via `SqliteConnectOptions::pragma()`, so every connection
//! from the pool gets the correct PRAGMAs automatically.
//!
//! # Example
//!
//! ```ignore
//! use mad_hatter::dao;
//!
//! // Dynamic data source with resolver (recommended)
//! dao!(MyDb, schema = "schema.json", resolver = |id: &str| {
//!     DsConfig::sqlite(format!("data/{}/my.db", id))
//! });
//!
//! // Access DB through scope.db_for::<MyDb>(param) in transaction handlers.
//! ```

/// SQLite connection pool configuration with best-practice defaults.
///
/// Controls pool size and SQLite PRAGMAs applied via `SqliteConnectOptions`.
#[doc(hidden)]
#[derive(Debug, Clone)]
pub struct DaoConfig {
    /// Connection pool max size. Default: 2.
    pub pool_size: u32,
    /// PRAGMA busy_timeout in milliseconds. Default: 5000.
    pub busy_timeout_ms: u32,
    /// Whether to enable WAL journal mode. Default: true.
    pub journal_mode_wal: bool,
}

impl Default for DaoConfig {
    fn default() -> Self {
        Self {
            pool_size: 2,
            busy_timeout_ms: 5000,
            journal_mode_wal: true,
        }
    }
}

impl DaoConfig {
    /// Override pool max size.
    pub fn pool_size(mut self, size: u32) -> Self {
        self.pool_size = size;
        self
    }

    /// Override busy_timeout (milliseconds).
    pub fn busy_timeout(mut self, ms: u32) -> Self {
        self.busy_timeout_ms = ms;
        self
    }

    /// Override WAL mode (true = WAL, false = default journal mode).
    pub fn wal(mut self, enabled: bool) -> Self {
        self.journal_mode_wal = enabled;
        self
    }
}

/// Reconcile database schema from a JSON declaration.
///
/// Parses the JSON schema and ensures all declared tables and columns exist.
/// Missing tables are created; missing columns are added via ALTER TABLE.
/// Index statements under the `_indexes` key are executed as-is.
///
/// JSON format (column order preserved via `serde_json` `preserve_order` feature):
/// ```json
/// {
///   "my_table": {
///     "id": "INTEGER PRIMARY KEY AUTOINCREMENT",
///     "name": "TEXT NOT NULL",
///     "value": "TEXT NOT NULL DEFAULT ''"
///   },
///   "_indexes": [
///     "CREATE INDEX IF NOT EXISTS idx_my_table_name ON my_table(name)"
///   ]
/// }
/// ```
pub async fn reconcile_schema(
    db: &crate::db::Db,
    schema_json: &str,
) -> Result<(), crate::critical::CriticalError> {
    use std::collections::HashSet;

    let schema: serde_json::Map<String, serde_json::Value> =
        serde_json::from_str(schema_json).map_err(|e| crate::critical::CriticalError {
            operation: "reconcile_schema",
            message: format!("failed to parse schema JSON: {}", e),
        })?;

    for (key, value) in &schema {
        if key == "_indexes" {
            let indexes: Vec<String> = serde_json::from_value(value.clone()).map_err(|e| {
                crate::critical::CriticalError {
                    operation: "reconcile_schema",
                    message: format!("invalid _indexes format: {}", e),
                }
            })?;
            for idx_sql in &indexes {
                db.execute_raw(idx_sql).await.map_err(|e| {
                    crate::critical::CriticalError {
                        operation: "reconcile_schema",
                        message: format!("index creation failed: {}", e),
                    }
                })?;
            }
            continue;
        }

        // Table definition: key = table name, value = { col_name: col_def, ... }
        let columns: serde_json::Map<String, serde_json::Value> =
            serde_json::from_value(value.clone()).map_err(|e| crate::critical::CriticalError {
                operation: "reconcile_schema",
                message: format!("invalid table definition for '{}': {}", key, e),
            })?;

        // BUILD: CREATE TABLE IF NOT EXISTS
        let col_defs: Vec<String> = columns
            .iter()
            .map(|(name, def)| {
                let def_str = def.as_str().unwrap_or_default();
                format!("{} {}", name, def_str)
            })
            .collect();
        let create_sql = format!(
            "CREATE TABLE IF NOT EXISTS {} ({})",
            key,
            col_defs.join(", ")
        );
        db.execute_raw(&create_sql).await.map_err(|e| {
            crate::critical::CriticalError {
                operation: "reconcile_schema",
                message: format!("CREATE TABLE '{}' failed: {}", key, e),
            }
        })?;

        // PRAGMA: get existing column names
        let pragma_sql = format!("SELECT name FROM pragma_table_info('{}')", key);
        let existing_cols: HashSet<String> = db
            .fetch_scalars(&pragma_sql)
            .await
            .map_err(|e| crate::critical::CriticalError {
                operation: "reconcile_schema",
                message: format!("PRAGMA table_info('{}') failed: {}", key, e),
            })?
            .into_iter()
            .collect();

        // DIFF: add missing columns
        for (col_name, col_def) in &columns {
            if !existing_cols.contains(col_name) {
                let def_str = col_def.as_str().unwrap_or_default();
                let alter_sql = format!(
                    "ALTER TABLE {} ADD COLUMN {} {}",
                    key, col_name, def_str
                );
                let result = db.execute_raw(&alter_sql).await;
                match result {
                    Ok(_) => {
                        eprintln!("[DAO] Auto-migration: added column {}.{}", key, col_name);
                    }
                    Err(e) => {
                        let msg = e.to_string();
                        if msg.contains("duplicate column name") {
                            // Column already exists (race condition or repeated migration) — idempotent
                            eprintln!("[DAO] Column {}.{} already exists, skipping", key, col_name);
                        } else {
                            return Err(crate::critical::CriticalError {
                                operation: "reconcile_schema",
                                message: format!(
                                    "ALTER TABLE '{}' ADD COLUMN '{}' failed: {}",
                                    key, col_name, msg
                                ),
                            });
                        }
                    }
                }
            }
        }
    }

    Ok(())
}

/// Declare a database connection pool struct.
///
/// Generates a typed database data source.
///
/// SQLite best practices (WAL mode, busy_timeout) are applied automatically.
/// Use `config { ... }` to override defaults.
///
/// # Variants
///
/// ```ignore
/// // Dynamic data source with resolver
/// dao!(MyDb, schema = "schema.json", resolver = |id: &str| {
///     DsConfig::sqlite(format!("data/{}/my.db", id))
/// });
///
/// // Resolver with custom config:
/// dao!(MyDb, schema = "schema.json", resolver = |id: &str| {
///     DsConfig::sqlite(format!("data/{}/my.db", id))
/// }, config { pool_size = 4 });
/// ```
#[macro_export]
macro_rules! dao {
    // === Public API branches (delegate to @internal) ===

    // Basic — no init, default config
    ($name:ident, $rel_path:literal) => {
        dao!(@internal $name, $rel_path, @no_init, $crate::dao::DaoConfig::default());
    };
    // Basic — no init, custom config
    ($name:ident, $rel_path:literal, config { $($key:ident = $val:expr),* $(,)? }) => {
        dao!(@internal $name, $rel_path, @no_init,
            $crate::dao::DaoConfig::default()$(.$key($val))*);
    };

    // Init SQL, default config
    ($name:ident, $rel_path:literal, init = $init_path:literal) => {
        dao!(@internal $name, $rel_path, @init $init_path, $crate::dao::DaoConfig::default());
    };
    // Init SQL, custom config
    ($name:ident, $rel_path:literal, init = $init_path:literal, config { $($key:ident = $val:expr),* $(,)? }) => {
        dao!(@internal $name, $rel_path, @init $init_path,
            $crate::dao::DaoConfig::default()$(.$key($val))*);
    };

    // Schema JSON, default config
    ($name:ident, $rel_path:literal, schema = $schema_path:literal) => {
        dao!(@internal $name, $rel_path, @schema $schema_path, $crate::dao::DaoConfig::default());
    };
    // Schema JSON, custom config
    ($name:ident, $rel_path:literal, schema = $schema_path:literal, config { $($key:ident = $val:expr),* $(,)? }) => {
        dao!(@internal $name, $rel_path, @schema $schema_path,
            $crate::dao::DaoConfig::default()$(.$key($val))*);
    };


    // === Public API branches with global singleton (`as GLOBAL`) ===

    // Basic — no init, default config, with global
    ($name:ident, $rel_path:literal, as $global:ident) => {
        dao!(@internal $name, $rel_path, @no_init, $crate::dao::DaoConfig::default());
        pub static $global: std::sync::OnceLock<$name> = std::sync::OnceLock::new();
    };
    // Basic — no init, custom config, with global
    ($name:ident, $rel_path:literal, as $global:ident, config { $($key:ident = $val:expr),* $(,)? }) => {
        dao!(@internal $name, $rel_path, @no_init,
            $crate::dao::DaoConfig::default()$(.$key($val))*);
        pub static $global: std::sync::OnceLock<$name> = std::sync::OnceLock::new();
    };

    // Init SQL, default config, with global
    ($name:ident, $rel_path:literal, init = $init_path:literal, as $global:ident) => {
        dao!(@internal $name, $rel_path, @init $init_path, $crate::dao::DaoConfig::default());
        pub static $global: std::sync::OnceLock<$name> = std::sync::OnceLock::new();
    };
    // Init SQL, custom config, with global
    ($name:ident, $rel_path:literal, init = $init_path:literal, as $global:ident, config { $($key:ident = $val:expr),* $(,)? }) => {
        dao!(@internal $name, $rel_path, @init $init_path,
            $crate::dao::DaoConfig::default()$(.$key($val))*);
        pub static $global: std::sync::OnceLock<$name> = std::sync::OnceLock::new();
    };

    // Schema JSON, default config, with global
    ($name:ident, $rel_path:literal, schema = $schema_path:literal, as $global:ident) => {
        dao!(@internal $name, $rel_path, @schema $schema_path, $crate::dao::DaoConfig::default());
        pub static $global: std::sync::OnceLock<$name> = std::sync::OnceLock::new();
    };
    // Schema JSON, custom config, with global
    ($name:ident, $rel_path:literal, schema = $schema_path:literal, as $global:ident, config { $($key:ident = $val:expr),* $(,)? }) => {
        dao!(@internal $name, $rel_path, @schema $schema_path,
            $crate::dao::DaoConfig::default()$(.$key($val))*);
        pub static $global: std::sync::OnceLock<$name> = std::sync::OnceLock::new();
    };

    // === Dynamic data source (resolver) branches ===

    // Resolver only (no schema)
    ($name:ident, resolver = $resolver:expr) => {
        pub struct $name;
        impl $crate::db::DynamicDao for $name {
            fn resolve(param: &str) -> $crate::db::DsConfig {
                #[allow(clippy::redundant_closure_call)]
                ($resolver)(param)
            }
        }
    };

    // Resolver with schema
    ($name:ident, schema = $schema_path:literal, resolver = $resolver:expr) => {
        pub struct $name;
        impl $crate::db::DynamicDao for $name {
            fn resolve(param: &str) -> $crate::db::DsConfig {
                #[allow(clippy::redundant_closure_call)]
                ($resolver)(param)
            }
            fn schema() -> Option<&'static str> {
                Some(include_str!($schema_path))
            }
        }
    };

    // Resolver with config
    ($name:ident, resolver = $resolver:expr, config { $($key:ident = $val:expr),* $(,)? }) => {
        pub struct $name;
        impl $crate::db::DynamicDao for $name {
            fn resolve(param: &str) -> $crate::db::DsConfig {
                #[allow(clippy::redundant_closure_call)]
                ($resolver)(param)
            }
            fn dao_config() -> $crate::dao::DaoConfig {
                $crate::dao::DaoConfig::default()$(.$key($val))*
            }
        }
    };

    // Resolver with schema and config
    ($name:ident, schema = $schema_path:literal, resolver = $resolver:expr, config { $($key:ident = $val:expr),* $(,)? }) => {
        pub struct $name;
        impl $crate::db::DynamicDao for $name {
            fn resolve(param: &str) -> $crate::db::DsConfig {
                #[allow(clippy::redundant_closure_call)]
                ($resolver)(param)
            }
            fn schema() -> Option<&'static str> {
                Some(include_str!($schema_path))
            }
            fn dao_config() -> $crate::dao::DaoConfig {
                $crate::dao::DaoConfig::default()$(.$key($val))*
            }
        }
    };

        // === Internal implementation branches ===

    (@internal $name:ident, $rel_path:literal, @no_init, $config:expr) => {
        #[derive(Clone)]
        pub struct $name {
            db: $crate::db::Db,
        }

        impl $name {
            /// Get the database handle (framework-internal).
            #[doc(hidden)]
            pub fn db(&self) -> &$crate::db::Db {
                &self.db
            }
        }
    };

    (@internal $name:ident, $rel_path:literal, @init $init_path:literal, $config:expr) => {
        #[derive(Clone)]
        pub struct $name {
            db: $crate::db::Db,
        }

        impl $name {
            /// Get the database handle (framework-internal).
            #[doc(hidden)]
            pub fn db(&self) -> &$crate::db::Db {
                &self.db
            }
        }
    };

    (@internal $name:ident, $rel_path:literal, @schema $schema_path:literal, $config:expr) => {
        #[derive(Clone)]
        pub struct $name {
            db: $crate::db::Db,
        }

        impl $name {
            /// Get the database handle (framework-internal).
            #[doc(hidden)]
            pub fn db(&self) -> &$crate::db::Db {
                &self.db
            }
        }
    };
}

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

    #[test]
    fn test_dao_config_defaults() {
        let config = DaoConfig::default();
        assert_eq!(config.pool_size, 2);
        assert_eq!(config.busy_timeout_ms, 5000);
        assert!(config.journal_mode_wal);
    }

    #[test]
    fn test_dao_config_builder_pool_size() {
        let config = DaoConfig::default().pool_size(4);
        assert_eq!(config.pool_size, 4);
        assert_eq!(config.busy_timeout_ms, 5000);
        assert!(config.journal_mode_wal);
    }

    #[test]
    fn test_dao_config_builder_busy_timeout() {
        let config = DaoConfig::default().busy_timeout(10000);
        assert_eq!(config.busy_timeout_ms, 10000);
    }

    #[test]
    fn test_dao_config_builder_wal_off() {
        let config = DaoConfig::default().wal(false);
        assert!(!config.journal_mode_wal);
    }

    #[test]
    fn test_dao_config_builder_chain() {
        let config = DaoConfig::default()
            .pool_size(8)
            .busy_timeout(3000)
            .wal(false);
        assert_eq!(config.pool_size, 8);
        assert_eq!(config.busy_timeout_ms, 3000);
        assert!(!config.journal_mode_wal);
    }
}