use anyhow::Result;
use rusqlite::Connection;

pub struct Migration {
    pub version: i32,
    pub name: &'static str,
    pub sql: &'static str,
}

const MIGRATIONS: &[Migration] = &[
    Migration {
        version: 1,
        name: "initial_schema",
        sql: include_str!("../migrations/001_initial_schema.sql"),
    },
    // Add more migrations here as needed
    // Migration {
    //     version: 2,
    //     name: "add_user_table",
    //     sql: include_str!("../migrations/002_add_user_table.sql"),
    // },
];

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

impl<'a> MigrationRunner<'a> {
    pub fn new(conn: &'a Connection) -> Self {
        Self { conn }
    }

    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 INTEGER 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);

        // Run pending migrations
        for migration in MIGRATIONS {
            if migration.version > current_version {
                println!(
                    "Running migration {}: {}",
                    migration.version, migration.name
                );
                self.run_migration(migration)?;
            }
        }

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

    fn get_current_version(&self) -> Result<i32> {
        // 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::<_, i32>(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: i32) -> 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::<_, i32>(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()?;
        for migration in MIGRATIONS {
            if migration.version > current_version {
                println!(
                    "⏳ Migration {}: {} (pending)",
                    migration.version, migration.name
                );
            }
        }

        Ok(())
    }
}

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

    #[test]
    fn test_migration_runner() {
        let conn = Connection::open_in_memory().unwrap();
        let runner = MigrationRunner::new(&conn);

        // 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
        assert_eq!(
            runner.get_current_version().unwrap(),
            MIGRATIONS.len() as i32
        );
    }
}