tapir/internal/adapters/store/migrate_test.go

package store_test

import (
	"context"
	"database/sql"
	"errors"
	"os"
	"testing"

	"github.com/golang-migrate/migrate/v4"
	migratepgx "github.com/golang-migrate/migrate/v4/database/pgx/v5"
	"github.com/golang-migrate/migrate/v4/source/iofs"
	"github.com/stretchr/testify/require"

	_ "github.com/jackc/pgx/v5/stdlib" // register the "pgx" database/sql driver
)

// fileMigrator builds a golang-migrate instance from the on-disk migration files
// (not the embedded FS the production Migrate uses), so a test can step the schema
// up and down. os.DirFS(".") is rooted at the package dir; the SQL lives under
// "migrations". Mirrors store.Migrate's construction otherwise.
func fileMigrator(t *testing.T) *migrate.Migrate {
	t.Helper()
	db, err := sql.Open("pgx", dsn)
	require.NoError(t, err)
	t.Cleanup(func() { _ = db.Close() })

	drv, err := migratepgx.WithInstance(db, &migratepgx.Config{})
	require.NoError(t, err)
	src, err := iofs.New(os.DirFS("."), "migrations")
	require.NoError(t, err)
	m, err := migrate.NewWithInstance("iofs", src, "pgx", drv)
	require.NoError(t, err)
	t.Cleanup(func() { _, _ = m.Close() })
	return m
}

// headVersion reports the current (HEAD) schema version so a test can restore
// to it after stepping down, without hard-coding what HEAD is. Adding a
// migration on top changes HEAD but no test that uses this needs editing.
func headVersion(t *testing.T, m *migrate.Migrate) uint {
	t.Helper()
	v, dirty, err := m.Version()
	require.NoError(t, err)
	require.False(t, dirty, "schema must not be dirty")
	return v
}

// migrateTo drives the schema to an exact version *by version number*, not by
// step count. This is the whole point of the migrate-test design: a migration
// added above the target does not shift any count here, so unrelated tests stay
// green (see issue #8). ErrNoChange (already at that version) is not a failure.
func migrateTo(t *testing.T, m *migrate.Migrate, version uint) {
	t.Helper()
	err := m.Migrate(version)
	if errors.Is(err, migrate.ErrNoChange) {
		return
	}
	require.NoError(t, err)
}

// loginEventsExists reports whether the login_events relation is present.
func loginEventsExists(t *testing.T) bool {
	t.Helper()
	var reg *string
	require.NoError(t, rawPool(t).QueryRow(context.Background(),
		`SELECT to_regclass('public.login_events')::text`).Scan(&reg))
	return reg != nil
}

// TestMigration010LoginEventsUpDown proves migration 010 is reversible: the down
// migration drops login_events cleanly and the up migration recreates it. A rotten
// down migration (forgotten DROP, dangling policy) would fail here rather than in
// production during a rollback. The test restores the schema to latest before
// returning so the shared embedded-postgres stays at HEAD for sibling tests.
func TestMigration010LoginEventsUpDown(t *testing.T) {
	newStore(t) // ensure the schema is migrated to latest (011 applied)
	require.True(t, loginEventsExists(t), "login_events must exist at latest migration")

	m := fileMigrator(t)
	head := headVersion(t, m)

	migrateTo(t, m, 9) // just below 010 — everything above steps down
	require.False(t, loginEventsExists(t), "login_events must be gone after the down migration")

	migrateTo(t, m, 10) // up 010
	require.True(t, loginEventsExists(t), "login_events must be restored after the up migration")

	migrateTo(t, m, head) // restore to HEAD for sibling tests
}

// autoSummarizeDefault reads the users.auto_summarize column default as text
// ("true"/"false"), so the migration's default flip is verifiable directly.
func autoSummarizeDefault(t *testing.T) string {
	t.Helper()
	var def string
	require.NoError(t, rawPool(t).QueryRow(context.Background(),
		`SELECT column_default FROM information_schema.columns
		  WHERE table_name = 'users' AND column_name = 'auto_summarize'`).Scan(&def))
	return def
}

// TestMigration011AutoSummarizeDefaultUpDown proves migration 011 is reversible:
// up sets the auto_summarize column default to TRUE (ADR-018), down restores
// FALSE. The down intentionally does not revert existing rows — only the default.
func TestMigration011AutoSummarizeDefaultUpDown(t *testing.T) {
	newStore(t) // latest (013 applied)
	require.Equal(t, "true", autoSummarizeDefault(t), "011 sets the default to TRUE")

	m := fileMigrator(t)
	head := headVersion(t, m)

	migrateTo(t, m, 10) // just below 011 — reverts the column default
	require.Equal(t, "false", autoSummarizeDefault(t), "default is FALSE after the down migration")

	migrateTo(t, m, 11) // up 011 re-applies the TRUE default
	require.Equal(t, "true", autoSummarizeDefault(t))

	migrateTo(t, m, head) // restore to HEAD for sibling tests
}

// channelTitleExists reports whether videos.channel_title is present.
func channelTitleExists(t *testing.T) bool {
	t.Helper()
	var exists bool
	require.NoError(t, rawPool(t).QueryRow(context.Background(),
		`SELECT EXISTS (SELECT 1 FROM information_schema.columns
		  WHERE table_name = 'videos' AND column_name = 'channel_title')`).Scan(&exists))
	return exists
}

// TestMigration014VideoChannelTitleUpDown proves 014 is reversible: down drops
// videos.channel_title, up recreates it.
func TestMigration014VideoChannelTitleUpDown(t *testing.T) {
	newStore(t) // latest (014 applied)
	require.True(t, channelTitleExists(t), "channel_title exists at latest migration")

	m := fileMigrator(t)
	head := headVersion(t, m)

	migrateTo(t, m, 13) // just below 014 — drops channel_title
	require.False(t, channelTitleExists(t), "channel_title must be gone after the down migration")

	migrateTo(t, m, 14) // up 014 recreates channel_title
	require.True(t, channelTitleExists(t), "channel_title must be restored after the up migration")

	migrateTo(t, m, head) // restore to HEAD for sibling tests
}

// TestMigration012FixAutoSummarizeRLS proves 012 runs cleanly and flips any
// remaining auto_summarize=FALSE rows to TRUE (the back-fill blocked by RLS in 011).
func TestMigration012FixAutoSummarizeRLS(t *testing.T) {
	newStore(t) // apply all migrations including 012
	require.Equal(t, "true", autoSummarizeDefault(t), "column default is TRUE after 012")

	// Round-trip: down 012, then up 012 — must be idempotent.
	m := fileMigrator(t)
	head := headVersion(t, m)

	migrateTo(t, m, 11) // down 012 must not error
	migrateTo(t, m, 12) // up 012 must re-apply cleanly
	require.Equal(t, "true", autoSummarizeDefault(t), "default still TRUE after 012 re-applied")

	migrateTo(t, m, head) // restore to HEAD for sibling tests
}