go-mad/internal/database/database.go

package database

import (
	"context"
	"database/sql"
	"fmt"
	"log"
	"os"
	"path/filepath"
	"strings"

	_ "github.com/jackc/pgx/v5/stdlib"
)

func Open(databaseURL string) (*sql.DB, error) {
	db, err := sql.Open("pgx", databaseURL)
	if err != nil {
		return nil, fmt.Errorf("opening database: %w", err)
	}

	db.SetMaxOpenConns(20)
	db.SetMaxIdleConns(5)
	db.SetConnMaxLifetime(30 * 60)

	if err := db.PingContext(context.Background()); err != nil {
		db.Close()
		return nil, fmt.Errorf("pinging database: %w", err)
	}

	log.Println("Database connection opened successfully")
	return db, nil
}

type Migration struct {
	Version     string
	Description string
	SQL         string
}

func FindMigrations(migrationPath string) ([]Migration, error) {
	var migrations []Migration

	entries, err := os.ReadDir(migrationPath)
	if err != nil {
		return nil, fmt.Errorf("reading migration directory: %w", err)
	}

	for _, entry := range entries {
		if entry.IsDir() || !strings.HasSuffix(entry.Name(), ".sql") {
			continue
		}

		path := filepath.Join(migrationPath, entry.Name())
		content, err := os.ReadFile(path)
		if err != nil {
			return nil, fmt.Errorf("reading migration file %s: %w", entry.Name(), err)
		}

		contentStr := string(content)
		migration := parseMigration(contentStr, entry.Name())
		migrations = append(migrations, migration)
	}

	return migrations, nil
}

func parseMigration(content string, filename string) Migration {
	// Parse goose-style migration: -- +goose Up / -- +goose Down
	lines := strings.Split(content, "\n")
	var upSQL strings.Builder
	inUp := false

	for _, line := range lines {
		trimmed := strings.TrimSpace(line)
		if strings.Contains(trimmed, "-- +goose Up") {
			inUp = true
			continue
		}
		if strings.Contains(trimmed, "-- +goose Down") {
			inUp = false
			continue
		}

		if inUp {
			upSQL.WriteString(line)
			upSQL.WriteString("\n")
		}
	}

	// Extract version from filename (first part before _)
	version := ""
	desc := ""
	if idx := strings.Index(filename, "_"); idx >= 0 {
		version = filename[:idx]
		remaining := filename[idx+1:]
		if idx2 := strings.Index(remaining, "_"); idx2 >= 0 {
			desc = remaining[:idx2]
		} else {
			desc = strings.TrimSuffix(remaining, ".sql")
		}
	}

	return Migration{
		Version:     version,
		Description: desc,
		SQL:         upSQL.String(),
	}
}

func Migrate(db *sql.DB, migrationPath string) error {
	migrations, err := FindMigrations(migrationPath)
	if err != nil {
		return fmt.Errorf("finding migrations: %w", err)
	}

	if len(migrations) == 0 {
		log.Println("No migration files found")
		return nil
	}

	log.Printf("Found %d migration(s), running up...", len(migrations))

	// Ensure schema_migrations table exists
	_, err = db.ExecContext(context.Background(), `
		CREATE TABLE IF NOT EXISTS schema_migrations (
			version VARCHAR(255) PRIMARY KEY,
			description VARCHAR(255),
			applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
		)
	`)
	if err != nil {
		return fmt.Errorf("creating schema_migrations table: %w", err)
	}

	for _, m := range migrations {
		// Check if already applied
		var count int
		err := db.QueryRowContext(context.Background(), "SELECT COUNT(*) FROM schema_migrations WHERE version = $1", m.Version).Scan(&count)
		if err != nil {
			return fmt.Errorf("checking migration %s: %w", m.Version, err)
		}

		if count > 0 {
			log.Printf("Migration %s already applied, skipping", m.Version)
			continue
		}

		// Execute the up SQL
		tx, err := db.BeginTx(context.Background(), nil)
		if err != nil {
			return fmt.Errorf("beginning transaction for migration %s: %w", m.Version, err)
		}

		_, err = tx.ExecContext(context.Background(), m.SQL)
		if err != nil {
			tx.Rollback()
			return fmt.Errorf("executing migration %s: %w", m.Version, err)
		}

		// Record the migration as applied
		_, err = tx.ExecContext(context.Background(), "INSERT INTO schema_migrations (version, description) VALUES ($1, $2)", m.Version, m.Description)
		if err != nil {
			tx.Rollback()
			return fmt.Errorf("recording migration %s: %w", m.Version, err)
		}

		if err := tx.Commit(); err != nil {
			return fmt.Errorf("committing migration %s: %w", m.Version, err)
		}

		log.Printf("Applied migration %s: %s", m.Version, m.Description)
	}

	log.Println("Migrations completed successfully")
	return nil
}