use crate::migration_discovery::{Migration, RuntimeMigrationDiscovery};
use anyhow::Result;
use rusqlite::Connection;
use std::path::Path;

pub struct MigrationRunner<'a> {
    conn: &'a Connection,
    discovery: RuntimeMigrationDiscovery,
}

impl<'a> MigrationRunner<'a> {
    pub fn new(conn: &'a Connection) -> Result<Self> {
        // Default to migrations directory relative to project root
        let migrations_dir = Path::new("migrations");
        let discovery = RuntimeMigrationDiscovery::new(migrations_dir)?;
        Ok(Self { conn, discovery })
    }

    pub fn new_with_path<P: AsRef<Path>>(conn: &'a Connection, migrations_dir: P) -> Result<Self> {
        let discovery = RuntimeMigrationDiscovery::new(migrations_dir)?;
        Ok(Self { conn, discovery })
    }

    pub fn run_migrations(&self) -> Result<()> {
        // Create migrations table if it doesn't exist
        self.conn.execute(
            "CREATE TABLE IF NOT EXISTS schema_migrations (
                version BIGINT PRIMARY KEY,
                name TEXT NOT NULL,
                applied_at TEXT NOT NULL
            )",
            [],
        )?;

        // Get current migration version
        let current_version = self.get_current_version()?;

        println!("Current database version: {}", current_version);

        // Get pending migrations from discovery system
        let pending_migrations = self.discovery.get_pending_migrations(current_version);

        // Run each pending migration
        for migration in pending_migrations {
            println!(
                "Running migration {}: {}",
                migration.version, migration.name
            );
            self.run_migration(migration)?;
        }

        println!("All migrations completed successfully");
        Ok(())
    }

    fn get_current_version(&self) -> Result<i64> {
        // Check if schema_migrations table exists first
        let table_exists = self.conn.query_row(
            "SELECT name FROM sqlite_master WHERE type='table' AND name='schema_migrations'",
            [],
            |_| Ok(()),
        );

        match table_exists {
            Ok(_) => {
                // Table exists, get the current version
                let version = self.conn.query_row(
                    "SELECT COALESCE(MAX(version), 0) FROM schema_migrations",
                    [],
                    |row| row.get::<_, i64>(0),
                )?;
                Ok(version)
            }
            Err(_) => {
                // Table doesn't exist, we're at version 0
                Ok(0)
            }
        }
    }

    fn run_migration(&self, migration: &Migration) -> Result<()> {
        // Execute the migration SQL
        self.conn.execute_batch(&migration.sql)?;

        // Record the migration as applied
        self.conn.execute(
            "INSERT INTO schema_migrations (version, name, applied_at) VALUES (?1, ?2, ?3)",
            [
                &migration.version.to_string(),
                &migration.name,
                &chrono::Utc::now().to_rfc3339(),
            ],
        )?;

        Ok(())
    }

    pub fn rollback_to_version(&self, target_version: i64) -> Result<()> {
        println!("Rolling back to version {}", target_version);

        // This is a simplified rollback - in practice you'd need down migrations
        // For now, just remove migration records
        self.conn.execute(
            "DELETE FROM schema_migrations WHERE version > ?1",
            [target_version],
        )?;

        println!("Rollback completed (Note: This doesn't actually undo schema changes)");
        Ok(())
    }

    pub fn show_migration_status(&self) -> Result<()> {
        println!("Migration Status:");
        println!("================");

        let mut stmt = self
            .conn
            .prepare("SELECT version, name, applied_at FROM schema_migrations ORDER BY version")?;

        let migrations = stmt.query_map([], |row| {
            Ok((
                row.get::<_, i64>(0)?,
                row.get::<_, String>(1)?,
                row.get::<_, String>(2)?,
            ))
        })?;

        for migration in migrations {
            let (version, name, applied_at) = migration?;
            println!(
                "✅ Migration {}: {} (applied: {})",
                version, name, applied_at
            );
        }

        // Show pending migrations
        let current_version = self.get_current_version()?;
        let pending_migrations = self.discovery.get_pending_migrations(current_version);
        for migration in pending_migrations {
            println!(
                "⏳ Migration {}: {} (pending)",
                migration.version, migration.name
            );
        }

        Ok(())
    }
}

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

    #[test]
    fn test_migration_runner() {
        use std::fs;
        use tempfile::TempDir;

        // Create a temporary directory with a test migration
        let temp_dir = TempDir::new().unwrap();
        let migrations_dir = temp_dir.path();

        fs::write(
            migrations_dir.join("20231201120000_test_migration.sql"),
            "CREATE TABLE test_table (id INTEGER PRIMARY KEY);",
        )
        .unwrap();

        let conn = Connection::open_in_memory().unwrap();
        let runner = MigrationRunner::new_with_path(&conn, migrations_dir).unwrap();

        // Should start with version 0
        assert_eq!(runner.get_current_version().unwrap(), 0);

        // Run migrations
        runner.run_migrations().unwrap();

        // Should now be at latest version (timestamp of our migration)
        assert_eq!(runner.get_current_version().unwrap(), 20231201120000);
    }
}