butterrobot/internal/migration/migrations.go

package migration

import (
	"database/sql"
	"golang.org/x/crypto/bcrypt"
)

func init() {
	// Register migrations
	Register(1, "Initial schema with bcrypt passwords", migrateInitialSchemaUp, migrateInitialSchemaDown)
	Register(2, "Add reminders table", migrateRemindersUp, migrateRemindersDown)
	Register(3, "Add cache table", migrateCacheUp, migrateCacheDown)
	Register(4, "Add enable_all_plugins column to channels", migrateEnableAllPluginsUp, migrateEnableAllPluginsDown)
}

// Initial schema creation with bcrypt passwords - version 1
func migrateInitialSchemaUp(db *sql.DB) error {
	// Create channels table
	_, err := db.Exec(`
		CREATE TABLE IF NOT EXISTS channels (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			platform TEXT NOT NULL,
			platform_channel_id TEXT NOT NULL,
			enabled BOOLEAN NOT NULL DEFAULT 0,
			channel_raw TEXT NOT NULL,
			UNIQUE(platform, platform_channel_id)
		)
	`)
	if err != nil {
		return err
	}

	// Create channel_plugin table
	_, err = db.Exec(`
		CREATE TABLE IF NOT EXISTS channel_plugin (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			channel_id INTEGER NOT NULL,
			plugin_id TEXT NOT NULL,
			enabled BOOLEAN NOT NULL DEFAULT 0,
			config TEXT NOT NULL DEFAULT '{}',
			UNIQUE(channel_id, plugin_id),
			FOREIGN KEY (channel_id) REFERENCES channels (id) ON DELETE CASCADE
		)
	`)
	if err != nil {
		return err
	}

	// Create users table with bcrypt passwords
	_, err = db.Exec(`
		CREATE TABLE IF NOT EXISTS users (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			username TEXT NOT NULL UNIQUE,
			password TEXT NOT NULL
		)
	`)
	if err != nil {
		return err
	}

	// Create default admin user with bcrypt password
	hashedPassword, err := bcrypt.GenerateFromPassword([]byte("admin"), 12)
	if err != nil {
		return err
	}

	// Check if users table is empty before inserting
	var count int
	err = db.QueryRow("SELECT COUNT(*) FROM users").Scan(&count)
	if err != nil {
		return err
	}

	if count == 0 {
		_, err = db.Exec(
			"INSERT INTO users (username, password) VALUES (?, ?)",
			"admin", string(hashedPassword),
		)
		if err != nil {
			return err
		}
	}

	return nil
}

func migrateInitialSchemaDown(db *sql.DB) error {
	// Drop tables in reverse order of dependencies
	_, err := db.Exec(`DROP TABLE IF EXISTS channel_plugin`)
	if err != nil {
		return err
	}

	_, err = db.Exec(`DROP TABLE IF EXISTS channels`)
	if err != nil {
		return err
	}

	_, err = db.Exec(`DROP TABLE IF EXISTS users`)
	if err != nil {
		return err
	}

	return nil
}

// Add reminders table - version 2
func migrateRemindersUp(db *sql.DB) error {
	_, err := db.Exec(`
		CREATE TABLE IF NOT EXISTS reminders (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			platform TEXT NOT NULL,
			channel_id TEXT NOT NULL,
			message_id TEXT NOT NULL,
			reply_to_id TEXT NOT NULL,
			user_id TEXT NOT NULL,
			username TEXT NOT NULL,
			created_at TIMESTAMP NOT NULL,
			trigger_at TIMESTAMP NOT NULL,
			content TEXT NOT NULL,
			processed BOOLEAN NOT NULL DEFAULT 0
		)
	`)
	return err
}

func migrateRemindersDown(db *sql.DB) error {
	_, err := db.Exec(`DROP TABLE IF EXISTS reminders`)
	return err
}

// Add cache table - version 3
func migrateCacheUp(db *sql.DB) error {
	_, err := db.Exec(`
		CREATE TABLE IF NOT EXISTS cache (
			key TEXT PRIMARY KEY,
			value TEXT NOT NULL,
			expires_at TIMESTAMP,
			created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
			updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
		)
	`)
	if err != nil {
		return err
	}

	// Create index on expires_at for efficient cleanup
	_, err = db.Exec(`
		CREATE INDEX IF NOT EXISTS idx_cache_expires_at ON cache(expires_at)
	`)
	return err
}

func migrateCacheDown(db *sql.DB) error {
	_, err := db.Exec(`DROP TABLE IF EXISTS cache`)
	return err
}

// Add enable_all_plugins column to channels table - version 4
func migrateEnableAllPluginsUp(db *sql.DB) error {
	_, err := db.Exec(`
		ALTER TABLE channels ADD COLUMN enable_all_plugins BOOLEAN NOT NULL DEFAULT 0
	`)
	return err
}

func migrateEnableAllPluginsDown(db *sql.DB) error {
	// SQLite doesn't support DROP COLUMN, so we need to recreate the table
	tx, err := db.Begin()
	if err != nil {
		return err
	}
	defer func() {
		_ = tx.Rollback() // Ignore rollback errors
	}()

	// Create backup table
	_, err = tx.Exec(`
		CREATE TABLE channels_backup (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			platform TEXT NOT NULL,
			platform_channel_id TEXT NOT NULL,
			enabled BOOLEAN NOT NULL DEFAULT 0,
			channel_raw TEXT NOT NULL,
			UNIQUE(platform, platform_channel_id)
		)
	`)
	if err != nil {
		return err
	}

	// Copy data excluding enable_all_plugins column
	_, err = tx.Exec(`
		INSERT INTO channels_backup (id, platform, platform_channel_id, enabled, channel_raw)
		SELECT id, platform, platform_channel_id, enabled, channel_raw FROM channels
	`)
	if err != nil {
		return err
	}

	// Drop original table
	_, err = tx.Exec(`DROP TABLE channels`)
	if err != nil {
		return err
	}

	// Rename backup table
	_, err = tx.Exec(`ALTER TABLE channels_backup RENAME TO channels`)
	if err != nil {
		return err
	}

	return tx.Commit()
}