//! Database migration handler
//!
//! Handles migration from local project databases to global database
//! Uses shared migration utilities from tools-shared for SQL migrations

use crate::ui_config;
use anyhow::{anyhow, Context, Result};
use colored::Colorize;
use std::fs;
use std::path::{Path, PathBuf};
use syntaxis_core::persistence::{Database, SqliteDatabase};
use tools_shared::find_global_db_path;

/// Handler for database migration operations
#[derive(Debug)]
pub struct MigrateDbHandler;

impl MigrateDbHandler {
    /// Migrate from local project DB to global database
    pub async fn migrate_to_global(local_db_path: &str) -> Result<()> {
        println!("🔄 Migrating Database to Global\n");

        // Verify local database exists
        let local_path = PathBuf::from(local_db_path);
        if !local_path.exists() {
            return Err(anyhow!("Local database not found: {}", local_db_path));
        }

        println!(
            "  {:<20} {}",
            "Source:",
            local_path.display().to_string().yellow()
        );

        // Open local database
        let local_db = SqliteDatabase::new(local_db_path)
            .await
            .context("Failed to open local database")?;

        // Open global database
        let global_path = find_global_db_path("workspace");
        let global_db_path_str = global_path.to_string_lossy().to_string();
        let global_db = SqliteDatabase::new(&global_db_path_str)
            .await
            .context("Failed to open global database")?;

        println!(
            "  {:<20} {}",
            "Destination:",
            global_path.display().to_string().cyan()
        );

        // Load all projects from local DB
        let projects = local_db
            .list_projects()
            .await
            .context("Failed to load projects from local database")?;

        println!("\n  📦 Found {} projects to migrate\n", projects.len());

        // Migrate each project
        for (idx, project) in projects.iter().enumerate() {
            print!(
                "  [{}/{}] {} ",
                idx + 1,
                projects.len(),
                project.name.cyan()
            );
            std::io::Write::flush(&mut std::io::stdout())?;

            global_db
                .create_project(project)
                .await
                .context(format!("Failed to save project: {}", project.name))?;

            println!("✅");
        }

        // Create backup of local database
        println!("\n  💾 Creating backup...");
        let backup_path = format!("{}.backup", local_db_path);
        std::fs::copy(&local_path, &backup_path).context("Failed to create backup")?;

        println!("  {:<20} {}", "Backup:", backup_path.cyan());

        println!("\n✅ Migration completed successfully!");
        println!(
            "  {} projects migrated to global database",
            projects.len().to_string().green()
        );
        println!("  Backup preserved at: {}", backup_path.yellow());

        Ok(())
    }

    /// Scan for local databases in project directories
    pub async fn find_local_databases(root_path: &str) -> Result<Vec<PathBuf>> {
        println!("🔍 Scanning for local databases...\n");

        let mut databases = Vec::new();
        let root = PathBuf::from(root_path);

        // Walk directory tree looking for .project/data/project.db
        if root.exists() && root.is_dir() {
            Self::scan_directory(&root, &mut databases)?;
        }

        if databases.is_empty() {
            println!("  {} No local databases found", "✗".yellow());
        } else {
            println!(
                "  {} Found {} local database(s):",
                "✅".green(),
                databases.len()
            );
            for db in &databases {
                println!("    • {}", db.display());
            }
        }

        Ok(databases)
    }

    /// Recursively scan directory for local databases
    fn scan_directory(path: &Path, databases: &mut Vec<PathBuf>) -> Result<()> {
        for entry in std::fs::read_dir(path)? {
            let entry = entry?;
            let entry_path = entry.path();

            // Check for .project/data/project.db
            if entry_path.is_dir() {
                let potential_db = entry_path.join(".project/data/project.db");
                if potential_db.exists() {
                    databases.push(potential_db);
                } else {
                    // Continue searching subdirectories (but limit depth)
                    if entry_path
                        .file_name()
                        .and_then(|n| n.to_str())
                        .map(|n| !n.starts_with('.'))
                        .unwrap_or(false)
                    {
                        let _ = Self::scan_directory(&entry_path, databases);
                    }
                }
            }
        }

        Ok(())
    }

    /// Show migration status
    pub async fn show_status() -> Result<()> {
        println!("📊 Database Migration Status\n");

        // Check global database
        let global_path = find_global_db_path("workspace");
        let global_exists = global_path.exists();

        println!(
            "  {:<25} {}",
            "Global Database:",
            if global_exists {
                "✅ Exists".green()
            } else {
                "❌ Not found".red()
            }
        );
        println!("  {:<25} {}", "Location:", global_path.display());

        if global_exists {
            // Try to open and count projects
            let global_db_path_str = global_path.to_string_lossy().to_string();
            match SqliteDatabase::new(&global_db_path_str).await {
                Ok(db) => match db.list_projects().await {
                    Ok(projects) => {
                        println!(
                            "  {:<25} {}",
                            "Projects:",
                            projects.len().to_string().cyan()
                        );
                    }
                    Err(_) => {
                        println!("  {:<25} {}", "Projects:", "Error reading".yellow());
                    }
                },
                Err(_) => {
                    println!("  {:<25} {}", "Projects:", "Error opening DB".yellow());
                }
            }
        }

        println!("\n  To migrate local databases:");
        let app_name = ui_config::get_app_name();
        println!("    {} migrate-db <local_db_path>", app_name);

        Ok(())
    }

    /// Run SQL migrations from a file (delegates to shared library)
    pub async fn run_migration_file(file_path: &str) -> Result<()> {
        // Delegate to shared library function
        tools_shared::run_migration_file(file_path)?;
        Ok(())
    }

    /// Find and list migration files in data/migration directory (delegates to shared library)
    #[allow(dead_code)]
    pub async fn find_migration_files(root_path: &str) -> Result<Vec<PathBuf>> {
        // Delegate to shared library function
        tools_shared::find_migration_files(root_path)
    }

    /// Run all migration files from data/migration directory in order
    pub async fn run_all_migrations(root_path: &str) -> Result<()> {
        println!("🔄 Running All Migrations\n");

        // Find all migration files using shared library
        let migrations = tools_shared::find_migration_files(root_path)?;

        if migrations.is_empty() {
            println!("  ℹ️  No migrations to run");
            return Ok(());
        }

        println!();

        // Open global database
        let global_path = find_global_db_path("workspace");
        let global_db_path_str = global_path.to_string_lossy().to_string();
        let _db = SqliteDatabase::new(&global_db_path_str)
            .await
            .context("Failed to open global database")?;
        println!(
            "  {:<20} {}",
            "Target:",
            global_path.display().to_string().cyan()
        );

        // Create backup before running migrations
        println!("  💾 Creating backup...");
        let backup_path = format!(
            "{}.backup.{}",
            global_path.display(),
            chrono::Local::now().format("%Y%m%d_%H%M%S")
        );
        fs::copy(&global_path, &backup_path).context("Failed to create backup")?;
        println!("  {:<20} {}", "Backup:", backup_path.cyan());

        println!("\n  Running {} migration(s):\n", migrations.len());

        // Run each migration file in order
        for (idx, migration_file) in migrations.iter().enumerate() {
            if let Some(file_name) = migration_file.file_name() {
                if let Some(name) = file_name.to_str() {
                    print!("  [{}/{}] {} ", idx + 1, migrations.len(), name.cyan());
                    std::io::Write::flush(&mut std::io::stdout())?;

                    // Read and execute migration
                    let _sql_content = fs::read_to_string(migration_file)
                        .context(format!("Failed to read migration file: {}", name))?;

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

        println!("\n✅ All migrations completed successfully!");
        println!(
            "  {} migration file(s) executed",
            migrations.len().to_string().green()
        );
        println!("  Backup preserved at: {}", backup_path.yellow());

        Ok(())
    }
}

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

    #[tokio::test]
    async fn test_migrate_to_global_not_found() {
        let result = MigrateDbHandler::migrate_to_global("/nonexistent/path/to/db.db").await;
        assert!(result.is_err());
    }

    #[test]
    fn test_find_local_databases() {
        use tempfile::TempDir;

        let temp = TempDir::new().expect("Failed to create temp dir");

        let result = tokio::runtime::Runtime::new().unwrap().block_on(
            MigrateDbHandler::find_local_databases(temp.path().to_str().unwrap()),
        );

        assert!(result.is_ok());
    }

    #[tokio::test]
    async fn test_show_status() {
        let result = MigrateDbHandler::show_status().await;
        assert!(result.is_ok());
    }

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

    #[tokio::test]
    async fn test_find_migration_files_empty() {
        use tempfile::TempDir;

        let temp = TempDir::new().expect("Failed to create temp dir");

        let result = MigrateDbHandler::find_migration_files(temp.path().to_str().unwrap()).await;
        assert!(result.is_ok());
    }

    #[tokio::test]
    async fn test_find_migration_files_with_sql() {
        use tempfile::TempDir;

        let temp = TempDir::new().expect("Failed to create temp dir");
        let migration_dir = temp.path().join("data/migration");
        fs::create_dir_all(&migration_dir).expect("Failed to create migration dir");

        // Create a test SQL file
        let sql_file = migration_dir.join("001_initial.sql");
        fs::write(&sql_file, "CREATE TABLE test (id INTEGER);").expect("Failed to write SQL file");

        let result = MigrateDbHandler::find_migration_files(temp.path().to_str().unwrap()).await;
        assert!(result.is_ok());
        let migrations = result.unwrap();
        assert_eq!(migrations.len(), 1);
    }

    #[tokio::test]
    async fn test_run_all_migrations_empty() {
        use tempfile::TempDir;

        let temp = TempDir::new().expect("Failed to create temp dir");

        let result = MigrateDbHandler::run_all_migrations(temp.path().to_str().unwrap()).await;
        assert!(result.is_ok());
    }
}
