Add database migrations

1 files changed, 152 insertions, 0 deletions

diff --git a/src/migrations.rs b/src/migrations.rs
new file mode 100644
index 0000000..c7cd6bf
--- /dev/null
+++ b/src/migrations.rs
@@ -0,0 +1,152 @@
+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> {
+        let version = self.conn.query_row(
+            "SELECT COALESCE(MAX(version), 0) FROM schema_migrations",
+            [],
+            |row| row.get::<_, i32>(0),
+        )?;
+        Ok(version)
+    }
+
+    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);
+    }
+}
\ No newline at end of file