lmrc-postgres 0.3.16

//! PostgreSQL Manager with builder pattern
//!
//! This module provides the main `PostgresManager` type for managing PostgreSQL
//! installations on remote servers.

use crate::config::PostgresConfig;
use crate::diff::ConfigDiff;
use crate::error::{Error, Result};
use crate::operations;
use lmrc_ssh::{AuthMethod, SshClient};
use tracing::{info, warn};

/// PostgreSQL Manager
///
/// Main entry point for managing PostgreSQL installations on remote servers.
///
/// Use [`PostgresManagerBuilder`] to construct instances.
///
/// # Example
///
/// ```rust,no_run
/// use lmrc_postgres::{PostgresConfig, PostgresManager};
///
/// # async fn example() -> Result<(), Box<dyn std::error::Error>> {
/// let config = PostgresConfig::builder()
///     .version("15")
///     .database_name("myapp")
///     .username("myuser")
///     .password("secure_password")
///     .build()?;
///
/// let manager = PostgresManager::builder()
///     .config(config)
///     .server_ip("192.168.1.100")
///     .ssh_user("root")
///     .build()?;
///
/// manager.install().await?;
/// # Ok(())
/// # }
/// ```
pub struct PostgresManager {
    config: PostgresConfig,
    server_ip: String,
    ssh_user: String,
    ssh_password: Option<String>,
    ssh_key_path: Option<String>,
    ssh_port: u16,
    private_ip: Option<String>,
}

impl PostgresManager {
    /// Create a new builder
    pub fn builder() -> PostgresManagerBuilder {
        PostgresManagerBuilder::default()
    }

    /// Get a mutable SSH client connection to the server
    fn connect(&self) -> Result<SshClient> {
        let auth = if let Some(ref password) = self.ssh_password {
            AuthMethod::Password {
                username: self.ssh_user.clone(),
                password: password.clone(),
            }
        } else if let Some(ref key_path) = self.ssh_key_path {
            AuthMethod::PublicKey {
                username: self.ssh_user.clone(),
                private_key_path: key_path.clone(),
                passphrase: None,
            }
        } else {
            // Default to project-local SSH key
            let ssh_key_path = std::env::var("SSH_KEY_PATH")
                .unwrap_or_else(|_| ".ssh/id_rsa".to_string());

            AuthMethod::PublicKey {
                username: self.ssh_user.clone(),
                private_key_path: ssh_key_path,
                passphrase: None,
            }
        };

        SshClient::new(&self.server_ip, self.ssh_port)?
            .with_auth(auth)
            .connect()
            .map_err(Error::Ssh)
    }

    /// Check if PostgreSQL is installed
    ///
    /// # Example
    ///
    /// ```rust,no_run
    /// # use lmrc_postgres::PostgresManager;
    /// # async fn example(manager: PostgresManager) -> Result<(), Box<dyn std::error::Error>> {
    /// if !manager.is_installed().await? {
    ///     println!("PostgreSQL is not installed");
    /// }
    /// # Ok(())
    /// # }
    /// ```
    pub async fn is_installed(&self) -> Result<bool> {
        let mut ssh = self.connect()?;
        operations::is_installed(&mut ssh).await
    }

    /// Get installed PostgreSQL version
    ///
    /// Returns `None` if PostgreSQL is not installed.
    pub async fn get_installed_version(&self) -> Result<Option<String>> {
        let mut ssh = self.connect()?;
        operations::get_installed_version(&mut ssh).await
    }

    /// Install PostgreSQL (idempotent)
    ///
    /// This operation is idempotent - it can be safely run multiple times.
    /// If PostgreSQL is already installed with the correct version, it will skip installation.
    ///
    /// # Example
    ///
    /// ```rust,no_run
    /// # use lmrc_postgres::PostgresManager;
    /// # async fn example(manager: PostgresManager) -> Result<(), Box<dyn std::error::Error>> {
    /// manager.install().await?;
    /// println!("PostgreSQL installed successfully");
    /// # Ok(())
    /// # }
    /// ```
    pub async fn install(&self) -> Result<()> {
        let mut ssh = self.connect()?;
        operations::install(&mut ssh, &self.config).await
    }

    /// Uninstall PostgreSQL
    ///
    /// # Arguments
    ///
    /// * `purge` - If true, remove all data and configuration files
    ///
    /// # Example
    ///
    /// ```rust,no_run
    /// # use lmrc_postgres::PostgresManager;
    /// # async fn example(manager: PostgresManager) -> Result<(), Box<dyn std::error::Error>> {
    /// // Remove PostgreSQL but keep data
    /// manager.uninstall(false).await?;
    ///
    /// // Remove PostgreSQL and all data
    /// manager.uninstall(true).await?;
    /// # Ok(())
    /// # }
    /// ```
    pub async fn uninstall(&self, purge: bool) -> Result<()> {
        let mut ssh = self.connect()?;
        operations::uninstall(&mut ssh, &self.config, purge).await
    }

    /// Configure database and user (idempotent)
    ///
    /// Creates the database and user if they don't exist, grants permissions.
    /// This operation is idempotent.
    ///
    /// # Example
    ///
    /// ```rust,no_run
    /// # use lmrc_postgres::PostgresManager;
    /// # async fn example(manager: PostgresManager) -> Result<(), Box<dyn std::error::Error>> {
    /// manager.configure_database().await?;
    /// # Ok(())
    /// # }
    /// ```
    pub async fn configure_database(&self) -> Result<()> {
        let mut ssh = self.connect()?;
        operations::configure_database(&mut ssh, &self.config).await
    }

    /// Configure PostgreSQL server settings
    ///
    /// Updates server configuration (listen addresses, port, memory settings, etc.)
    /// and restarts the service.
    ///
    /// # Example
    ///
    /// ```rust,no_run
    /// # use lmrc_postgres::PostgresManager;
    /// # async fn example(manager: PostgresManager) -> Result<(), Box<dyn std::error::Error>> {
    /// manager.configure_server().await?;
    /// # Ok(())
    /// # }
    /// ```
    pub async fn configure_server(&self) -> Result<()> {
        let mut ssh = self.connect()?;
        operations::configure_server(&mut ssh, &self.config).await
    }

    /// Configure both database and server
    ///
    /// Convenience method that calls both `configure_database()` and `configure_server()`.
    ///
    /// # Example
    ///
    /// ```rust,no_run
    /// # use lmrc_postgres::PostgresManager;
    /// # async fn example(manager: PostgresManager) -> Result<(), Box<dyn std::error::Error>> {
    /// manager.configure().await?;
    /// # Ok(())
    /// # }
    /// ```
    pub async fn configure(&self) -> Result<()> {
        self.configure_database().await?;
        self.configure_server().await?;
        Ok(())
    }

    /// Complete setup (install and configure)
    ///
    /// Installs PostgreSQL (if not already installed) and configures both database and server.
    /// All operations are idempotent.
    ///
    /// # Example
    ///
    /// ```rust,no_run
    /// # use lmrc_postgres::PostgresManager;
    /// # async fn example(manager: PostgresManager) -> Result<(), Box<dyn std::error::Error>> {
    /// manager.setup().await?;
    /// println!("PostgreSQL is ready!");
    /// # Ok(())
    /// # }
    /// ```
    pub async fn setup(&self) -> Result<()> {
        if !self.is_installed().await? {
            self.install().await?;
        } else {
            info!("PostgreSQL is already installed");
        }

        self.configure().await?;

        // Display connection info
        if let Some(ref priv_ip) = self.private_ip {
            info!(
                "Database is accessible at: postgresql://{}:***@{}:{}/{}",
                self.config.username, priv_ip, self.config.port, self.config.database_name
            );
        }

        Ok(())
    }

    /// Detect configuration differences
    ///
    /// Compares the desired configuration with the current server configuration
    /// and returns a diff of changes.
    ///
    /// # Example
    ///
    /// ```rust,no_run
    /// # use lmrc_postgres::PostgresManager;
    /// # async fn example(manager: PostgresManager) -> Result<(), Box<dyn std::error::Error>> {
    /// let diff = manager.diff().await?;
    ///
    /// if diff.has_changes() {
    ///     println!("Configuration changes:");
    ///     for change in diff.changes() {
    ///         println!("  {}", change);
    ///     }
    /// }
    /// # Ok(())
    /// # }
    /// ```
    pub async fn diff(&self) -> Result<ConfigDiff> {
        let mut ssh = self.connect()?;
        operations::detect_diff(&mut ssh, &self.config).await
    }

    /// Apply configuration diff
    ///
    /// Applies the changes detected by `diff()` to the server.
    ///
    /// # Arguments
    ///
    /// * `diff` - Configuration diff to apply
    ///
    /// # Example
    ///
    /// ```rust,no_run
    /// # use lmrc_postgres::PostgresManager;
    /// # async fn example(manager: PostgresManager) -> Result<(), Box<dyn std::error::Error>> {
    /// let diff = manager.diff().await?;
    ///
    /// if diff.has_changes() {
    ///     manager.apply_diff(&diff).await?;
    ///     println!("Configuration updated");
    /// }
    /// # Ok(())
    /// # }
    /// ```
    pub async fn apply_diff(&self, diff: &ConfigDiff) -> Result<()> {
        if !diff.has_changes() {
            info!("No changes to apply");
            return Ok(());
        }

        info!("Applying configuration changes: {}", diff.summary());

        // For now, we reconfigure the server to apply all changes
        // In a more sophisticated implementation, we could apply individual changes
        self.configure_server().await?;

        info!("Configuration changes applied successfully");
        Ok(())
    }

    /// Test database connection
    ///
    /// Attempts to connect to the database and verify credentials.
    ///
    /// # Example
    ///
    /// ```rust,no_run
    /// # use lmrc_postgres::PostgresManager;
    /// # async fn example(manager: PostgresManager) -> Result<(), Box<dyn std::error::Error>> {
    /// manager.test_connection().await?;
    /// println!("Connection successful!");
    /// # Ok(())
    /// # }
    /// ```
    pub async fn test_connection(&self) -> Result<()> {
        let mut ssh = self.connect()?;
        operations::test_connection(&mut ssh, &self.config).await
    }

    /// Get the PostgreSQL configuration
    pub fn config(&self) -> &PostgresConfig {
        &self.config
    }

    /// Get the server IP address
    pub fn server_ip(&self) -> &str {
        &self.server_ip
    }

    /// Get the SSH username
    pub fn ssh_user(&self) -> &str {
        &self.ssh_user
    }

    /// Get the private IP (if set)
    pub fn private_ip(&self) -> Option<&str> {
        self.private_ip.as_deref()
    }

    // === Advanced Installation Features ===

    /// Detect the server platform
    ///
    /// # Example
    ///
    /// ```rust,no_run
    /// # use lmrc_postgres::PostgresManager;
    /// # async fn example(manager: PostgresManager) -> Result<(), Box<dyn std::error::Error>> {
    /// let platform = manager.detect_platform().await?;
    /// println!("Platform: {:?}", platform);
    /// # Ok(())
    /// # }
    /// ```
    pub async fn detect_platform(&self) -> Result<crate::install::Platform> {
        let mut ssh = self.connect()?;
        crate::install::detect_platform(&mut ssh).await
    }

    /// Get system information
    ///
    /// # Example
    ///
    /// ```rust,no_run
    /// # use lmrc_postgres::PostgresManager;
    /// # async fn example(manager: PostgresManager) -> Result<(), Box<dyn std::error::Error>> {
    /// let info = manager.get_system_info().await?;
    /// println!("RAM: {}MB, Disk: {}MB, CPUs: {}",
    ///          info.total_ram_mb, info.free_disk_mb, info.cpu_cores);
    /// # Ok(())
    /// # }
    /// ```
    pub async fn get_system_info(&self) -> Result<crate::install::SystemInfo> {
        let mut ssh = self.connect()?;
        crate::install::get_system_info(&mut ssh).await
    }

    /// Check system requirements before installation
    ///
    /// # Arguments
    ///
    /// * `requirements` - Optional custom requirements (uses defaults if None)
    ///
    /// # Example
    ///
    /// ```rust,no_run
    /// # use lmrc_postgres::{PostgresManager, SystemRequirements};
    /// # async fn example(manager: PostgresManager) -> Result<(), Box<dyn std::error::Error>> {
    /// // Use default requirements
    /// manager.check_requirements(None).await?;
    ///
    /// // Or use custom requirements
    /// let requirements = SystemRequirements {
    ///     min_ram_mb: 2048,
    ///     min_disk_mb: 10240,
    ///     min_cpu_cores: 2,
    /// };
    /// manager.check_requirements(Some(requirements)).await?;
    /// # Ok(())
    /// # }
    /// ```
    pub async fn check_requirements(
        &self,
        requirements: Option<crate::install::SystemRequirements>,
    ) -> Result<crate::install::SystemInfo> {
        let mut ssh = self.connect()?;
        let req = requirements.unwrap_or_default();
        crate::install::check_requirements(&mut ssh, &req).await
    }

    /// Check if a PostgreSQL version is available for installation
    ///
    /// # Example
    ///
    /// ```rust,no_run
    /// # use lmrc_postgres::PostgresManager;
    /// # async fn example(manager: PostgresManager) -> Result<(), Box<dyn std::error::Error>> {
    /// if manager.check_version_available("16").await? {
    ///     println!("PostgreSQL 16 is available");
    /// }
    /// # Ok(())
    /// # }
    /// ```
    pub async fn check_version_available(&self, version: &str) -> Result<bool> {
        let mut ssh = self.connect()?;
        crate::install::check_version_available(&mut ssh, version).await
    }

    /// Verify installation comprehensively
    ///
    /// Performs extensive verification including:
    /// - Binary existence
    /// - Version check
    /// - Service status
    /// - Configuration files
    /// - Network listening
    ///
    /// # Example
    ///
    /// ```rust,no_run
    /// # use lmrc_postgres::PostgresManager;
    /// # async fn example(manager: PostgresManager) -> Result<(), Box<dyn std::error::Error>> {
    /// manager.verify_installation().await?;
    /// println!("Installation verified!");
    /// # Ok(())
    /// # }
    /// ```
    pub async fn verify_installation(&self) -> Result<()> {
        let mut ssh = self.connect()?;
        crate::install::verify_installation(&mut ssh, &self.config).await
    }

    /// Upgrade PostgreSQL to a new version
    ///
    /// # Arguments
    ///
    /// * `new_version` - Target PostgreSQL version
    ///
    /// # Example
    ///
    /// ```rust,no_run
    /// # use lmrc_postgres::PostgresManager;
    /// # async fn example(manager: PostgresManager) -> Result<(), Box<dyn std::error::Error>> {
    /// // Upgrade from current version to version 16
    /// manager.upgrade("16").await?;
    /// # Ok(())
    /// # }
    /// ```
    ///
    /// # Warning
    ///
    /// This is a simplified upgrade. For production systems, you should:
    /// - Backup your data first
    /// - Review migration notes for the new version
    /// - Test in a staging environment
    /// - Plan for data migration with pg_upgrade
    pub async fn upgrade(&self, new_version: &str) -> Result<()> {
        let mut ssh = self.connect()?;

        // Get current version
        let current_version = operations::get_installed_version(&mut ssh)
            .await?
            .ok_or(Error::NotInstalled)?;

        crate::install::upgrade(&mut ssh, &current_version, new_version, &self.config).await
    }

    /// Create a backup of the current PostgreSQL configuration
    ///
    /// Backs up:
    /// - postgresql.conf
    /// - pg_hba.conf
    /// - pg_ident.conf (if exists)
    ///
    /// Returns backup metadata including backup directory and timestamp.
    ///
    /// # Example
    ///
    /// ```rust,no_run
    /// # use lmrc_postgres::PostgresManager;
    /// # async fn example(manager: PostgresManager) -> Result<(), Box<dyn std::error::Error>> {
    /// let backup = manager.backup_config().await?;
    /// println!("Backup created: {}", backup.backup_dir);
    /// # Ok(())
    /// # }
    /// ```
    pub async fn backup_config(&self) -> Result<crate::backup::ConfigBackup> {
        let mut ssh = self.connect()?;
        crate::backup::backup_config(&mut ssh, &self.config).await
    }

    /// List available configuration backups
    ///
    /// # Example
    ///
    /// ```rust,no_run
    /// # use lmrc_postgres::PostgresManager;
    /// # async fn example(manager: PostgresManager) -> Result<(), Box<dyn std::error::Error>> {
    /// let backups = manager.list_backups().await?;
    /// for backup in backups {
    ///     println!("Backup: {} ({} files)", backup.timestamp, backup.files.len());
    /// }
    /// # Ok(())
    /// # }
    /// ```
    pub async fn list_backups(&self) -> Result<Vec<crate::backup::ConfigBackup>> {
        let mut ssh = self.connect()?;
        crate::backup::list_backups(&mut ssh).await
    }

    /// Restore configuration from a specific backup
    ///
    /// # Arguments
    ///
    /// * `backup` - The backup to restore
    ///
    /// # Example
    ///
    /// ```rust,no_run
    /// # use lmrc_postgres::PostgresManager;
    /// # async fn example(manager: PostgresManager) -> Result<(), Box<dyn std::error::Error>> {
    /// let backups = manager.list_backups().await?;
    /// if let Some(backup) = backups.first() {
    ///     manager.restore_backup(backup).await?;
    ///     println!("Configuration restored from {}", backup.timestamp);
    /// }
    /// # Ok(())
    /// # }
    /// ```
    pub async fn restore_backup(&self, backup: &crate::backup::ConfigBackup) -> Result<()> {
        let mut ssh = self.connect()?;
        crate::backup::restore_backup(&mut ssh, &self.config, backup).await
    }

    /// Rollback to the most recent configuration backup
    ///
    /// # Example
    ///
    /// ```rust,no_run
    /// # use lmrc_postgres::PostgresManager;
    /// # async fn example(manager: PostgresManager) -> Result<(), Box<dyn std::error::Error>> {
    /// // Make some changes that you want to undo
    /// // ...
    ///
    /// // Rollback to previous configuration
    /// manager.rollback_config().await?;
    /// println!("Configuration rolled back successfully");
    /// # Ok(())
    /// # }
    /// ```
    pub async fn rollback_config(&self) -> Result<()> {
        let mut ssh = self.connect()?;
        crate::backup::rollback_config(&mut ssh, &self.config).await
    }

    /// Clean up old configuration backups
    ///
    /// Keeps only the most recent N backups and deletes the rest.
    ///
    /// # Arguments
    ///
    /// * `keep_count` - Number of backups to keep
    ///
    /// # Example
    ///
    /// ```rust,no_run
    /// # use lmrc_postgres::PostgresManager;
    /// # async fn example(manager: PostgresManager) -> Result<(), Box<dyn std::error::Error>> {
    /// // Keep only the 5 most recent backups
    /// let deleted = manager.cleanup_old_backups(5).await?;
    /// println!("Deleted {} old backup(s)", deleted);
    /// # Ok(())
    /// # }
    /// ```
    pub async fn cleanup_old_backups(&self, keep_count: usize) -> Result<usize> {
        let mut ssh = self.connect()?;
        crate::backup::cleanup_old_backups(&mut ssh, keep_count).await
    }

    /// Detect pg_hba.conf configuration differences
    ///
    /// Compares the current pg_hba.conf rules with a desired state.
    ///
    /// # Example
    ///
    /// ```rust,no_run
    /// # use lmrc_postgres::PostgresManager;
    /// # async fn example(manager: PostgresManager) -> Result<(), Box<dyn std::error::Error>> {
    /// let current_content = manager.read_pg_hba().await?;
    /// println!("Current pg_hba.conf:\n{}", current_content);
    /// # Ok(())
    /// # }
    /// ```
    pub async fn read_pg_hba(&self) -> Result<String> {
        let mut ssh = self.connect()?;
        crate::backup::read_pg_hba(&mut ssh, &self.config).await
    }

    /// Preview configuration changes without applying them (dry-run)
    ///
    /// This method shows what changes would be made without actually applying them.
    /// Useful for validating changes before execution.
    ///
    /// # Example
    ///
    /// ```rust,no_run
    /// # use lmrc_postgres::PostgresManager;
    /// # async fn example(manager: PostgresManager) -> Result<(), Box<dyn std::error::Error>> {
    /// // Preview changes
    /// let diff = manager.dry_run_configure().await?;
    ///
    /// if diff.has_changes() {
    ///     println!("Would make the following changes:");
    ///     println!("{}", diff);
    ///
    ///     // If changes look good, apply them
    ///     manager.apply_diff(&diff).await?;
    /// }
    /// # Ok(())
    /// # }
    /// ```
    pub async fn dry_run_configure(&self) -> Result<ConfigDiff> {
        info!("Running dry-run configuration check (no changes will be applied)");
        self.diff().await
    }

    /// Apply configuration changes with automatic backup
    ///
    /// This method:
    /// 1. Creates a backup of the current configuration
    /// 2. Applies the changes
    /// 3. Verifies the changes
    /// 4. Rolls back if verification fails
    ///
    /// # Arguments
    ///
    /// * `diff` - Configuration diff to apply
    ///
    /// # Example
    ///
    /// ```rust,no_run
    /// # use lmrc_postgres::PostgresManager;
    /// # async fn example(manager: PostgresManager) -> Result<(), Box<dyn std::error::Error>> {
    /// let diff = manager.diff().await?;
    ///
    /// if diff.has_changes() {
    ///     // Apply with automatic backup and rollback on failure
    ///     manager.apply_diff_safe(&diff).await?;
    /// }
    /// # Ok(())
    /// # }
    /// ```
    pub async fn apply_diff_safe(&self, diff: &ConfigDiff) -> Result<()> {
        if !diff.has_changes() {
            info!("No changes to apply");
            return Ok(());
        }

        info!("Creating backup before applying changes...");
        let backup = self.backup_config().await?;
        info!("✓ Backup created: {}", backup.backup_dir);

        info!("Applying configuration changes...");
        match self.apply_diff(diff).await {
            Ok(_) => {
                info!("✓ Configuration changes applied successfully");
                Ok(())
            }
            Err(e) => {
                warn!("Configuration apply failed, rolling back to backup...");
                self.restore_backup(&backup).await?;
                info!("✓ Configuration rolled back successfully");
                Err(e)
            }
        }
    }

    // ==================== User & Database Management ====================

    /// List all PostgreSQL users
    ///
    /// # Example
    ///
    /// ```rust,no_run
    /// # use lmrc_postgres::PostgresManager;
    /// # async fn example(manager: PostgresManager) -> Result<(), Box<dyn std::error::Error>> {
    /// let users = manager.list_users().await?;
    /// for user in users {
    ///     println!("User: {} (superuser: {})", user.name, user.is_superuser);
    /// }
    /// # Ok(())
    /// # }
    /// ```
    pub async fn list_users(&self) -> Result<Vec<crate::user_db_management::UserInfo>> {
        let mut ssh = self.connect()?;
        crate::user_db_management::list_users(&mut ssh).await
    }

    /// List all PostgreSQL databases
    ///
    /// # Example
    ///
    /// ```rust,no_run
    /// # use lmrc_postgres::PostgresManager;
    /// # async fn example(manager: PostgresManager) -> Result<(), Box<dyn std::error::Error>> {
    /// let databases = manager.list_databases().await?;
    /// for db in databases {
    ///     println!("Database: {} (owner: {}, encoding: {})", db.name, db.owner, db.encoding);
    /// }
    /// # Ok(())
    /// # }
    /// ```
    pub async fn list_databases(&self) -> Result<Vec<crate::user_db_management::DatabaseInfo>> {
        let mut ssh = self.connect()?;
        crate::user_db_management::list_databases(&mut ssh).await
    }

    /// Drop a database
    ///
    /// # Arguments
    ///
    /// * `database_name` - Name of the database to drop
    ///
    /// # Example
    ///
    /// ```rust,no_run
    /// # use lmrc_postgres::PostgresManager;
    /// # async fn example(manager: PostgresManager) -> Result<(), Box<dyn std::error::Error>> {
    /// manager.drop_database("old_database").await?;
    /// # Ok(())
    /// # }
    /// ```
    pub async fn drop_database(&self, database_name: &str) -> Result<()> {
        let mut ssh = self.connect()?;
        crate::user_db_management::drop_database(&mut ssh, database_name).await
    }

    /// Drop a user
    ///
    /// # Arguments
    ///
    /// * `username` - Name of the user to drop
    ///
    /// # Example
    ///
    /// ```rust,no_run
    /// # use lmrc_postgres::PostgresManager;
    /// # async fn example(manager: PostgresManager) -> Result<(), Box<dyn std::error::Error>> {
    /// manager.drop_user("old_user").await?;
    /// # Ok(())
    /// # }
    /// ```
    pub async fn drop_user(&self, username: &str) -> Result<()> {
        let mut ssh = self.connect()?;
        crate::user_db_management::drop_user(&mut ssh, username).await
    }

    /// Update a user's password
    ///
    /// # Arguments
    ///
    /// * `username` - Name of the user
    /// * `new_password` - New password for the user
    ///
    /// # Example
    ///
    /// ```rust,no_run
    /// # use lmrc_postgres::PostgresManager;
    /// # async fn example(manager: PostgresManager) -> Result<(), Box<dyn std::error::Error>> {
    /// manager.update_user_password("myuser", "new_secure_password").await?;
    /// # Ok(())
    /// # }
    /// ```
    pub async fn update_user_password(&self, username: &str, new_password: &str) -> Result<()> {
        let mut ssh = self.connect()?;
        crate::user_db_management::update_user_password(&mut ssh, username, new_password).await
    }

    /// Grant privileges to a user on a database
    ///
    /// # Arguments
    ///
    /// * `database` - Database name
    /// * `username` - User to grant privileges to
    /// * `privileges` - List of privileges to grant
    ///
    /// # Example
    ///
    /// ```rust,no_run
    /// # use lmrc_postgres::{PostgresManager, Privilege};
    /// # async fn example(manager: PostgresManager) -> Result<(), Box<dyn std::error::Error>> {
    /// manager.grant_privileges(
    ///     "myapp_db",
    ///     "app_user",
    ///     &[Privilege::Select, Privilege::Insert, Privilege::Update]
    /// ).await?;
    /// # Ok(())
    /// # }
    /// ```
    pub async fn grant_privileges(
        &self,
        database: &str,
        username: &str,
        privileges: &[crate::user_db_management::Privilege],
    ) -> Result<()> {
        let mut ssh = self.connect()?;
        crate::user_db_management::grant_privileges(&mut ssh, database, username, privileges).await
    }

    /// Revoke privileges from a user on a database
    ///
    /// # Arguments
    ///
    /// * `database` - Database name
    /// * `username` - User to revoke privileges from
    /// * `privileges` - List of privileges to revoke
    ///
    /// # Example
    ///
    /// ```rust,no_run
    /// # use lmrc_postgres::{PostgresManager, Privilege};
    /// # async fn example(manager: PostgresManager) -> Result<(), Box<dyn std::error::Error>> {
    /// manager.revoke_privileges(
    ///     "myapp_db",
    ///     "app_user",
    ///     &[Privilege::Delete, Privilege::Truncate]
    /// ).await?;
    /// # Ok(())
    /// # }
    /// ```
    pub async fn revoke_privileges(
        &self,
        database: &str,
        username: &str,
        privileges: &[crate::user_db_management::Privilege],
    ) -> Result<()> {
        let mut ssh = self.connect()?;
        crate::user_db_management::revoke_privileges(&mut ssh, database, username, privileges).await
    }

    /// Create a role
    ///
    /// # Arguments
    ///
    /// * `role_name` - Name of the role to create
    /// * `can_login` - Whether the role can login
    /// * `is_superuser` - Whether the role is a superuser
    ///
    /// # Example
    ///
    /// ```rust,no_run
    /// # use lmrc_postgres::PostgresManager;
    /// # async fn example(manager: PostgresManager) -> Result<(), Box<dyn std::error::Error>> {
    /// // Create a non-login role for grouping privileges
    /// manager.create_role("app_readonly", false, false).await?;
    /// # Ok(())
    /// # }
    /// ```
    pub async fn create_role(
        &self,
        role_name: &str,
        can_login: bool,
        is_superuser: bool,
    ) -> Result<()> {
        let mut ssh = self.connect()?;
        crate::user_db_management::create_role(&mut ssh, role_name, can_login, is_superuser).await
    }

    /// Grant a role to a user
    ///
    /// # Arguments
    ///
    /// * `role_name` - Name of the role to grant
    /// * `username` - User to grant the role to
    ///
    /// # Example
    ///
    /// ```rust,no_run
    /// # use lmrc_postgres::PostgresManager;
    /// # async fn example(manager: PostgresManager) -> Result<(), Box<dyn std::error::Error>> {
    /// manager.grant_role("app_readonly", "new_user").await?;
    /// # Ok(())
    /// # }
    /// ```
    pub async fn grant_role(&self, role_name: &str, username: &str) -> Result<()> {
        let mut ssh = self.connect()?;
        crate::user_db_management::grant_role(&mut ssh, role_name, username).await
    }

    /// Revoke a role from a user
    ///
    /// # Arguments
    ///
    /// * `role_name` - Name of the role to revoke
    /// * `username` - User to revoke the role from
    ///
    /// # Example
    ///
    /// ```rust,no_run
    /// # use lmrc_postgres::PostgresManager;
    /// # async fn example(manager: PostgresManager) -> Result<(), Box<dyn std::error::Error>> {
    /// manager.revoke_role("app_readonly", "old_user").await?;
    /// # Ok(())
    /// # }
    /// ```
    pub async fn revoke_role(&self, role_name: &str, username: &str) -> Result<()> {
        let mut ssh = self.connect()?;
        crate::user_db_management::revoke_role(&mut ssh, role_name, username).await
    }

    /// Check if a user exists
    ///
    /// # Arguments
    ///
    /// * `username` - Name of the user to check
    ///
    /// # Example
    ///
    /// ```rust,no_run
    /// # use lmrc_postgres::PostgresManager;
    /// # async fn example(manager: PostgresManager) -> Result<(), Box<dyn std::error::Error>> {
    /// if manager.user_exists("myuser").await? {
    ///     println!("User exists");
    /// }
    /// # Ok(())
    /// # }
    /// ```
    pub async fn user_exists(&self, username: &str) -> Result<bool> {
        let mut ssh = self.connect()?;
        crate::user_db_management::user_exists(&mut ssh, username).await
    }

    /// Check if a database exists
    ///
    /// # Arguments
    ///
    /// * `database` - Name of the database to check
    ///
    /// # Example
    ///
    /// ```rust,no_run
    /// # use lmrc_postgres::PostgresManager;
    /// # async fn example(manager: PostgresManager) -> Result<(), Box<dyn std::error::Error>> {
    /// if manager.database_exists("mydb").await? {
    ///     println!("Database exists");
    /// }
    /// # Ok(())
    /// # }
    /// ```
    pub async fn database_exists(&self, database: &str) -> Result<bool> {
        let mut ssh = self.connect()?;
        crate::user_db_management::database_exists(&mut ssh, database).await
    }

    /// Create a database with advanced options
    ///
    /// # Arguments
    ///
    /// * `database_name` - Name of the database to create
    /// * `owner` - Optional owner of the database
    /// * `encoding` - Optional character encoding (e.g., "UTF8")
    /// * `template` - Optional template database
    ///
    /// # Example
    ///
    /// ```rust,no_run
    /// # use lmrc_postgres::PostgresManager;
    /// # async fn example(manager: PostgresManager) -> Result<(), Box<dyn std::error::Error>> {
    /// manager.create_database_with_options(
    ///     "analytics_db",
    ///     Some("analytics_user"),
    ///     Some("UTF8"),
    ///     Some("template0")
    /// ).await?;
    /// # Ok(())
    /// # }
    /// ```
    pub async fn create_database_with_options(
        &self,
        database_name: &str,
        owner: Option<&str>,
        encoding: Option<&str>,
        template: Option<&str>,
    ) -> Result<()> {
        let mut ssh = self.connect()?;
        crate::user_db_management::create_database_with_options(
            &mut ssh,
            database_name,
            owner,
            encoding,
            template,
        )
        .await
    }

    /// Create a user with advanced options
    ///
    /// # Arguments
    ///
    /// * `username` - Name of the user to create
    /// * `password` - Password for the user
    /// * `is_superuser` - Whether the user should be a superuser
    /// * `can_create_db` - Whether the user can create databases
    /// * `can_create_role` - Whether the user can create roles
    /// * `connection_limit` - Optional connection limit (-1 for unlimited)
    ///
    /// # Example
    ///
    /// ```rust,no_run
    /// # use lmrc_postgres::PostgresManager;
    /// # async fn example(manager: PostgresManager) -> Result<(), Box<dyn std::error::Error>> {
    /// manager.create_user_with_options(
    ///     "limited_user",
    ///     "secure_password",
    ///     false,
    ///     false,
    ///     false,
    ///     Some(10)
    /// ).await?;
    /// # Ok(())
    /// # }
    /// ```
    pub async fn create_user_with_options(
        &self,
        username: &str,
        password: &str,
        is_superuser: bool,
        can_create_db: bool,
        can_create_role: bool,
        connection_limit: Option<i32>,
    ) -> Result<()> {
        let mut ssh = self.connect()?;
        crate::user_db_management::create_user_with_options(
            &mut ssh,
            username,
            password,
            is_superuser,
            can_create_db,
            can_create_role,
            connection_limit,
        )
        .await
    }
}

/// Builder for [`PostgresManager`]
///
/// # Example
///
/// ```rust,no_run
/// use lmrc_postgres::{PostgresConfig, PostgresManager};
///
/// # fn example() -> Result<(), Box<dyn std::error::Error>> {
/// let config = PostgresConfig::builder()
///     .version("15")
///     .database_name("myapp")
///     .username("myuser")
///     .password("secure_password")
///     .build()?;
///
/// let manager = PostgresManager::builder()
///     .config(config)
///     .server_ip("192.168.1.100")
///     .ssh_user("root")
///     .ssh_port(22)
///     .private_ip("10.0.1.100")
///     .build()?;
/// # Ok(())
/// # }
/// ```
#[derive(Debug, Default)]
pub struct PostgresManagerBuilder {
    config: Option<PostgresConfig>,
    server_ip: Option<String>,
    ssh_user: Option<String>,
    ssh_password: Option<String>,
    ssh_key_path: Option<String>,
    ssh_port: Option<u16>,
    private_ip: Option<String>,
}

impl PostgresManagerBuilder {
    /// Set PostgreSQL configuration
    pub fn config(mut self, config: PostgresConfig) -> Self {
        self.config = Some(config);
        self
    }

    /// Set server IP address
    pub fn server_ip(mut self, ip: impl Into<String>) -> Self {
        self.server_ip = Some(ip.into());
        self
    }

    /// Set SSH username (default: "root")
    pub fn ssh_user(mut self, user: impl Into<String>) -> Self {
        self.ssh_user = Some(user.into());
        self
    }

    /// Set SSH password for password authentication
    pub fn ssh_password(mut self, password: impl Into<String>) -> Self {
        self.ssh_password = Some(password.into());
        self
    }

    /// Set SSH private key path for key-based authentication
    pub fn ssh_key_path(mut self, path: impl Into<String>) -> Self {
        self.ssh_key_path = Some(path.into());
        self
    }

    /// Set SSH port (default: 22)
    pub fn ssh_port(mut self, port: u16) -> Self {
        self.ssh_port = Some(port);
        self
    }

    /// Set private IP address (for connection string display)
    pub fn private_ip(mut self, ip: impl Into<String>) -> Self {
        self.private_ip = Some(ip.into());
        self
    }

    /// Build the PostgresManager
    pub fn build(self) -> Result<PostgresManager> {
        Ok(PostgresManager {
            config: self
                .config
                .ok_or_else(|| Error::MissingConfig("config".to_string()))?,
            server_ip: self
                .server_ip
                .ok_or_else(|| Error::MissingConfig("server_ip".to_string()))?,
            ssh_user: self.ssh_user.unwrap_or_else(|| "root".to_string()),
            ssh_password: self.ssh_password,
            ssh_key_path: self.ssh_key_path,
            ssh_port: self.ssh_port.unwrap_or(22),
            private_ip: self.private_ip,
        })
    }
}

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

    fn create_test_config() -> PostgresConfig {
        PostgresConfig::builder()
            .version("15")
            .database_name("test_db")
            .username("test_user")
            .password("test_pass")
            .build()
            .unwrap()
    }

    #[test]
    fn test_builder_minimal() {
        let config = create_test_config();
        let manager = PostgresManager::builder()
            .config(config.clone())
            .server_ip("192.168.1.100")
            .build()
            .unwrap();

        assert_eq!(manager.server_ip(), "192.168.1.100");
        assert_eq!(manager.ssh_user(), "root");
        assert_eq!(manager.ssh_port, 22);
        assert_eq!(manager.private_ip(), None);
    }

    #[test]
    fn test_builder_full() {
        let config = create_test_config();
        let manager = PostgresManager::builder()
            .config(config.clone())
            .server_ip("192.168.1.100")
            .ssh_user("admin")
            .ssh_port(2222)
            .private_ip("10.0.1.100")
            .build()
            .unwrap();

        assert_eq!(manager.server_ip(), "192.168.1.100");
        assert_eq!(manager.ssh_user(), "admin");
        assert_eq!(manager.ssh_port, 2222);
        assert_eq!(manager.private_ip(), Some("10.0.1.100"));
    }

    #[test]
    fn test_builder_missing_config() {
        let result = PostgresManager::builder()
            .server_ip("192.168.1.100")
            .build();

        assert!(matches!(result, Err(Error::MissingConfig(_))));
    }

    #[test]
    fn test_builder_missing_server_ip() {
        let config = create_test_config();
        let result = PostgresManager::builder().config(config).build();

        assert!(matches!(result, Err(Error::MissingConfig(_))));
    }
}