# Database Migration System

This package provides a simple database migration system for Butterfliu. It allows you to:

- Create new migrations
- Apply pending migrations
- Roll back migrations
- Check migration status

## Usage

### Running Migrations

To run migrations, use the following command:

```bash
go run cmd/migrate/main.go up
```

This will apply all pending migrations.

**Important:** Migrations must be run manually before starting the application. The application no longer automatically applies migrations on startup.

### Rolling Back Migrations

To roll back migrations to a specific version, use:

```bash
go run cmd/migrate/main.go down <version>
```

For example, to roll back to version 1:

```bash
go run cmd/migrate/main.go down 1
```

To roll back all migrations:

```bash
go run cmd/migrate/main.go reset
```

### Checking Migration Status

To check the status of all migrations:

```bash
go run cmd/migrate/main.go status
```

### Refreshing Migrations

To roll back all migrations and then apply them again:

```bash
go run cmd/migrate/main.go refresh
```

### Creating a New Migration

To create a new migration:

```bash
go run cmd/migrate/main.go create <name>
```

For example:

```bash
go run cmd/migrate/main.go create add_user_table
```

This will create a new migration file in the `migrations` directory.

## Auto-Migration

By default, auto-migration is **enabled**. The application will automatically apply all pending migrations on startup.

To disable auto-migration, set the `AUTO_MIGRATE` environment variable to `false`:

```bash
# Disable auto-migration
export AUTO_MIGRATE=false

# Then run the application
go run main.go
```

**Recommended usage:**
- **Development:** Auto-migration is enabled by default for convenience
- **Production:** Set `AUTO_MIGRATE=false` and run migrations manually using `go run cmd/migrate/main.go up` before deploying

## Migration File Structure

Each migration file should implement two functions:

- `Up`: Applies the migration
- `Down`: Rolls back the migration

Example:

```go
package migrations

import (
    "database/sql"
)

func init() {
    RegisterMigration(
        3,
        "Add user table",
        migrateAddUserTableUp,
        migrateAddUserTableDown,
    )
}

func migrateAddUserTableUp(tx *sql.Tx) error {
    _, err := tx.Exec(`
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT NOT NULL UNIQUE,
            password TEXT NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    `)
    return err
}

func migrateAddUserTableDown(tx *sql.Tx) error {
    _, err := tx.Exec(`DROP TABLE IF EXISTS users`)
    return err
}
```

## Best Practices

1. Always provide both `Up` and `Down` functions
2. Make migrations idempotent when possible
3. Use transactions to ensure atomicity
4. Keep migrations small and focused
5. Test migrations before applying them to production