gochat/backend/internal/database/high_risk_migrations_test.go

package database

import (
	"os"
	"path/filepath"
	"testing"
	"time"

	"github.com/stretchr/testify/assert"
	"github.com/stretchr/testify/require"
	"gorm.io/driver/postgres"
	"gorm.io/gorm"
	"gorm.io/gorm/logger"
)

func TestHighRiskMigrationsPreserveRollbackData(t *testing.T) {
	if os.Getenv("GOCHAT_TEST_DB") == "sqlite" {
		t.Skip("requires PostgreSQL migration semantics")
	}
	dsn := os.Getenv("GOCHAT_TEST_DB_URL")
	if dsn == "" {
		dsn = "host=localhost port=5432 user=postgres password=postgres dbname=gochat_test sslmode=disable"
	}
	db, err := gorm.Open(postgres.Open(dsn), &gorm.Config{Logger: logger.Default.LogMode(logger.Silent)})
	require.NoError(t, err)
	sqlDB, err := db.DB()
	require.NoError(t, err)
	sqlDB.SetMaxOpenConns(1)
	t.Cleanup(func() { _ = sqlDB.Close() })

	schema := "high_risk_migrations_" + time.Now().Format("20060102150405000000000")
	require.NoError(t, db.Exec("CREATE SCHEMA "+schema).Error)
	t.Cleanup(func() { _ = db.Exec("DROP SCHEMA " + schema + " CASCADE").Error })
	require.NoError(t, db.Exec("SET search_path TO "+schema).Error)
	require.NoError(t, db.Exec(`
		CREATE TABLE reporting_events_rollups (
			id SERIAL PRIMARY KEY, account_id INTEGER NOT NULL, dimension VARCHAR(50) NOT NULL,
			dimension_value VARCHAR(255) NOT NULL, metric_name VARCHAR(50) NOT NULL,
			value DOUBLE PRECISION NOT NULL, value_in_business_hours DOUBLE PRECISION,
			period VARCHAR(50) NOT NULL, created_at TIMESTAMPTZ DEFAULT NOW(),
			updated_at TIMESTAMPTZ DEFAULT NOW(), deleted_at TIMESTAMPTZ,
			UNIQUE(account_id, dimension, dimension_value, metric_name, period)
		);
		INSERT INTO reporting_events_rollups
			(account_id, dimension, dimension_value, metric_name, value, value_in_business_hours, period)
		VALUES (1, 'inbox', '42', 'conversations_count', 7, 3, '2026-08-21');
		CREATE TABLE custom_attribute_definitions (attribute_model TEXT, attribute_name TEXT);
		CREATE TABLE conversations (id BIGINT PRIMARY KEY, custom_attributes JSONB, updated_at TIMESTAMPTZ);
		INSERT INTO custom_attribute_definitions VALUES ('conversation_attribute', 'swt_source_url');
		INSERT INTO conversations VALUES (1, '{"swt_source_url":"https://example.test"}', NOW());
	`).Error)

	up48, err := os.ReadFile(filepath.Join("..", "..", "migrations", "000048_fix_reporting_events_rollups_schema.up.sql"))
	require.NoError(t, err)
	require.NoError(t, db.Exec(string(up48)).Error)
	var rollup struct {
		DimensionID int64
		Count       int64
		SumValue    float64
	}
	require.NoError(t, db.Table("reporting_events_rollups").Select("dimension_id, count, sum_value").Scan(&rollup).Error)
	assert.Equal(t, int64(42), rollup.DimensionID)
	assert.Equal(t, int64(7), rollup.Count)
	assert.Equal(t, float64(7), rollup.SumValue)

	up76, err := os.ReadFile(filepath.Join("..", "..", "migrations", "000076_replace_shangwutong_source_attributes_with_messages.up.sql"))
	require.NoError(t, err)
	require.NoError(t, db.Exec(string(up76)).Error)
	var definitions, conversations int64
	require.NoError(t, db.Table("custom_attribute_definitions").Count(&definitions).Error)
	require.NoError(t, db.Table("conversations").Where("custom_attributes->>'swt_source_url' IS NOT NULL").Count(&conversations).Error)
	assert.Equal(t, int64(1), definitions)
	assert.Equal(t, int64(1), conversations)

	down48, err := os.ReadFile(filepath.Join("..", "..", "migrations", "000048_fix_reporting_events_rollups_schema.down.sql"))
	require.NoError(t, err)
	require.ErrorContains(t, db.Exec(string(down48)).Error, "irreversible")
}

func TestMigration48RejectsUnsafeLegacyData(t *testing.T) {
	if os.Getenv("GOCHAT_TEST_DB") == "sqlite" {
		t.Skip("requires PostgreSQL migration semantics")
	}
	dsn := os.Getenv("GOCHAT_TEST_DB_URL")
	if dsn == "" {
		dsn = "host=localhost port=5432 user=postgres password=postgres dbname=gochat_test sslmode=disable"
	}
	db, err := gorm.Open(postgres.Open(dsn), &gorm.Config{Logger: logger.Default.LogMode(logger.Silent)})
	require.NoError(t, err)
	sqlDB, err := db.DB()
	require.NoError(t, err)
	sqlDB.SetMaxOpenConns(1)
	t.Cleanup(func() { _ = sqlDB.Close() })

	up48, err := os.ReadFile(filepath.Join("..", "..", "migrations", "000048_fix_reporting_events_rollups_schema.up.sql"))
	require.NoError(t, err)
	tests := []struct {
		name, values, want string
		rows               int64
	}{
		{"invalid_period", "(1, 'inbox', '42', 'conversations_count', 1, 'not-a-date')", "cannot convert to date", 1},
		{"invalid_dimension", "(1, 'inbox', 'vip', 'conversations_count', 1, '2026-08-21')", "cannot convert to bigint", 1},
		{"converted_key_collision", "(1, 'inbox', '042', 'conversations_count', 1, '2026-08-21'), (1, 'inbox', '42', 'conversations_count', 2, '2026-08-21')", "unique-key collision", 2},
	}
	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			schema := "migration_48_rejection_" + tt.name
			t.Cleanup(func() { _ = db.Exec("DROP SCHEMA IF EXISTS " + schema + " CASCADE").Error })
			require.NoError(t, db.Exec("DROP SCHEMA IF EXISTS "+schema+" CASCADE").Error)
			require.NoError(t, db.Exec("CREATE SCHEMA "+schema).Error)
			require.NoError(t, db.Exec("SET search_path TO "+schema).Error)
			require.NoError(t, db.Exec(`
				CREATE TABLE reporting_events_rollups (
					id SERIAL PRIMARY KEY, account_id INTEGER NOT NULL, dimension VARCHAR(50) NOT NULL,
					dimension_value VARCHAR(255) NOT NULL, metric_name VARCHAR(50) NOT NULL,
					value DOUBLE PRECISION NOT NULL, value_in_business_hours DOUBLE PRECISION,
					period VARCHAR(50) NOT NULL, created_at TIMESTAMPTZ DEFAULT NOW(),
					updated_at TIMESTAMPTZ DEFAULT NOW(), deleted_at TIMESTAMPTZ,
					UNIQUE(account_id, dimension, dimension_value, metric_name, period)
				);
				INSERT INTO reporting_events_rollups
					(account_id, dimension, dimension_value, metric_name, value, period)
				VALUES `+tt.values).Error)

			require.ErrorContains(t, db.Exec(string(up48)).Error, tt.want)
			require.NoError(t, db.Exec("ROLLBACK").Error)
			var rows int64
			require.NoError(t, db.Table(schema+".reporting_events_rollups").Count(&rows).Error)
			assert.Equal(t, tt.rows, rows)
			require.NoError(t, db.Exec("SET search_path TO public").Error)
			require.NoError(t, db.Exec("DROP SCHEMA "+schema+" CASCADE").Error)
		})
	}
}