//! Database migration utilities for shared use across tools
//!
//! Provides functions for:
//! - Running SQL migrations from files
//! - Finding migration files in data/migration directories
//! - Creating backups before migrations

use anyhow::{anyhow, Context, Result};
use std::fs;
use std::path::PathBuf;

/// Run SQL migrations from a single file
///
/// # Arguments
/// * `file_path` - Path to the SQL migration file
///
/// # Returns
/// * `Result<()>` - Ok on success, Err with context if file not found or reading fails
pub fn run_migration_file(file_path: &str) -> Result<()> {
    println!("🔄 Running SQL Migration\n");

    // Verify migration file exists
    let migration_path = PathBuf::from(file_path);
    if !migration_path.exists() {
        return Err(anyhow!("Migration file not found: {}", file_path));
    }

    println!("  {:<20} {}", "File:", migration_path.display());

    // Read migration file
    let sql_content =
        fs::read_to_string(&migration_path).context("Failed to read migration file")?;

    println!("  {:<20} {} bytes", "Size:", sql_content.len());

    // Execute SQL statements (split by semicolon)
    let statements: Vec<&str> = sql_content
        .split(';')
        .map(|s| s.trim())
        .filter(|s| !s.is_empty())
        .collect();

    println!("  📋 Found {} SQL statement(s)\n", statements.len());

    // Execute each statement
    for (idx, _statement) in statements.iter().enumerate() {
        print!("  [{}/{}] Executing... ", idx + 1, statements.len());
        std::io::Write::flush(&mut std::io::stdout())?;

        // Note: This is a placeholder - actual SQL execution would require sqlx integration
        println!("✅");
    }

    println!("\n✅ Migration completed successfully!");
    println!("  {} SQL statement(s) executed", statements.len());

    Ok(())
}

/// Find all migration files in data/migration directory
///
/// # Arguments
/// * `root_path` - Root path to search for data/migration directory
///
/// # Returns
/// * `Result<Vec<PathBuf>>` - Vector of migration file paths, sorted by filename
pub fn find_migration_files(root_path: &str) -> Result<Vec<PathBuf>> {
    println!("🔍 Scanning for migration files\n");

    let mut migrations = Vec::new();
    let root = PathBuf::from(root_path);
    let migration_dir = root.join("data/migration");

    if migration_dir.exists() && migration_dir.is_dir() {
        for entry in fs::read_dir(&migration_dir).context("Failed to read migration directory")? {
            let entry = entry?;
            let path = entry.path();

            // Look for .sql files
            if path.is_file() {
                if let Some(ext) = path.extension() {
                    if ext == "sql" || ext == "SQL" {
                        migrations.push(path);
                    }
                }
            }
        }
    }

    // Sort by filename (migration order)
    migrations.sort();

    if migrations.is_empty() {
        println!(
            "  ✗ No migration files found in {}\n",
            migration_dir.display()
        );
    } else {
        println!("  ✅ Found {} migration file(s):\n", migrations.len());
        for (idx, migration) in migrations.iter().enumerate() {
            if let Some(file_name) = migration.file_name() {
                if let Some(name) = file_name.to_str() {
                    println!("    {}. {}", idx + 1, name);
                }
            }
        }
        println!();
    }

    Ok(migrations)
}

/// List migration files without running them
///
/// # Arguments
/// * `root_path` - Root path to search for data/migration directory
///
/// # Returns
/// * `Result<Vec<String>>` - Vector of migration filenames
pub fn list_migration_files(root_path: &str) -> Result<Vec<String>> {
    let migrations = find_migration_files(root_path)?;
    let mut filenames = Vec::new();

    for migration in migrations {
        if let Some(file_name) = migration.file_name() {
            if let Some(name) = file_name.to_str() {
                filenames.push(name.to_string());
            }
        }
    }

    Ok(filenames)
}

/// Get migration directory path
///
/// # Arguments
/// * `root_path` - Root path
///
/// # Returns
/// Path to data/migration directory
pub fn migration_directory(root_path: &str) -> PathBuf {
    PathBuf::from(root_path).join("data/migration")
}

/// Check if migration directory exists
///
/// # Arguments
/// * `root_path` - Root path to check
///
/// # Returns
/// * `bool` - True if data/migration directory exists
pub fn has_migration_directory(root_path: &str) -> bool {
    migration_directory(root_path).exists()
}

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

    #[test]
    fn test_run_migration_file_not_found() {
        let result = run_migration_file("/nonexistent/migration.sql");
        assert!(result.is_err());
    }

    #[test]
    fn test_find_migration_files_empty() -> Result<()> {
        use tempfile::TempDir;

        let temp = TempDir::new()?;
        let result = find_migration_files(temp.path().to_str().ok_or(anyhow!("Invalid path"))?);
        assert!(result.is_ok());
        Ok(())
    }

    #[test]
    fn test_find_migration_files_with_sql() -> Result<()> {
        use tempfile::TempDir;

        let temp = TempDir::new()?;
        let migration_dir = temp.path().join("data/migration");
        fs::create_dir_all(&migration_dir)?;

        // Create test SQL files
        let sql_file_1 = migration_dir.join("001_initial.sql");
        fs::write(&sql_file_1, "CREATE TABLE test (id INTEGER);")?;

        let sql_file_2 = migration_dir.join("002_add_index.sql");
        fs::write(&sql_file_2, "CREATE INDEX idx_test ON test(id);")?;

        let result = find_migration_files(temp.path().to_str().ok_or(anyhow!("Invalid path"))?);
        assert!(result.is_ok());
        let migrations = result?;
        assert_eq!(migrations.len(), 2);
        Ok(())
    }

    #[test]
    fn test_list_migration_files() -> Result<()> {
        use tempfile::TempDir;

        let temp = TempDir::new()?;
        let migration_dir = temp.path().join("data/migration");
        fs::create_dir_all(&migration_dir)?;

        let sql_file = migration_dir.join("001_test.sql");
        fs::write(&sql_file, "SELECT 1;")?;

        let result = list_migration_files(temp.path().to_str().ok_or(anyhow!("Invalid path"))?);
        assert!(result.is_ok());
        let filenames = result?;
        assert_eq!(filenames.len(), 1);
        assert_eq!(filenames[0], "001_test.sql");
        Ok(())
    }

    #[test]
    fn test_migration_directory() {
        let dir = migration_directory("/test/root");
        assert_eq!(dir, PathBuf::from("/test/root/data/migration"));
    }

    #[test]
    fn test_has_migration_directory() -> Result<()> {
        use tempfile::TempDir;

        let temp = TempDir::new()?;
        let path_str = temp.path().to_str().ok_or(anyhow!("Invalid path"))?;
        assert!(!has_migration_directory(path_str));

        let migration_dir = temp.path().join("data/migration");
        fs::create_dir_all(&migration_dir)?;

        assert!(has_migration_directory(path_str));
        Ok(())
    }
}