// Package writer contains the SyncWriter interface and implementations
package writer

import (
	"context"
	"encoding/json"
	"testing"
	"time"

	"github.com/aws/smithy-go/ptr"
	"github.com/google/uuid"
	"github.com/jackc/pgx/v5/pgxpool"
	upstreamv0 "github.com/modelcontextprotocol/registry/pkg/api/v0"
	"github.com/modelcontextprotocol/registry/pkg/model"
	toolhivetypes "github.com/stacklok/toolhive-core/registry/types"
	"github.com/stretchr/testify/assert"
	"github.com/stretchr/testify/require"

	"github.com/stacklok/toolhive-registry-server/database"
	"github.com/stacklok/toolhive-registry-server/internal/db/sqlc"
)

// Test constants for icon themes and MIME types
const (
	testThemeLight  = "light"
	testThemeDark   = "dark"
	testMimeTypePNG = "image/png"
	testMaxMetaSize = 65536

	testQuery = `
	SELECT COUNT(s.*) FROM mcp_server s
	  JOIN entry_version v ON s.version_id = v.id
	  JOIN registry_entry e ON v.entry_id = e.id
	 WHERE e.source_id = $1
	   AND e.entry_type = 'MCP'
	`
)

// setupTestDB creates a test database connection and returns cleanup function
func setupTestDB(t *testing.T) (*pgxpool.Pool, func()) {
	t.Helper()

	ctx := context.Background()
	db, cleanupFunc := database.SetupTestDB(t)
	t.Cleanup(cleanupFunc)

	// Get connection string from the db connection
	connStr := db.Config().ConnString()

	// Create a pgxpool from the connection string
	pool, err := pgxpool.New(ctx, connStr)
	require.NoError(t, err)

	poolCleanup := func() {
		pool.Close()
		cleanupFunc()
	}

	return pool, poolCleanup
}

// testRegistryIDs holds IDs returned by createTestRegistry
type testRegistryIDs struct {
	sourceID   uuid.UUID
	registryID uuid.UUID
}

// createTestRegistry creates a test source, registry entry, and links them.
// Returns both the source ID and registry ID.
func createTestRegistry(t *testing.T, pool *pgxpool.Pool, name string) testRegistryIDs {
	t.Helper()

	ctx := context.Background()
	queries := sqlc.New(pool)

	sourceID, err := queries.UpsertSource(ctx, sqlc.UpsertSourceParams{
		Name:         name,
		CreationType: sqlc.CreationTypeCONFIG,
		SourceType:   "git",
		Syncable:     true,
	})
	require.NoError(t, err)

	now := time.Now().UTC()
	reg, err := queries.UpsertRegistry(ctx, sqlc.UpsertRegistryParams{
		Name:         name,
		CreationType: sqlc.CreationTypeCONFIG,
		CreatedAt:    &now,
		UpdatedAt:    &now,
	})
	require.NoError(t, err)

	err = queries.LinkRegistrySource(ctx, sqlc.LinkRegistrySourceParams{
		RegistryID: reg.ID,
		SourceID:   sourceID,
		Position:   0,
	})
	require.NoError(t, err)

	return testRegistryIDs{
		sourceID:   sourceID,
		registryID: reg.ID,
	}
}

// getTestRegistryID looks up the registry UUID by name
func getTestRegistryID(t *testing.T, pool *pgxpool.Pool, name string) uuid.UUID {
	t.Helper()
	ctx := context.Background()
	queries := sqlc.New(pool)
	reg, err := queries.GetRegistryByName(ctx, name)
	require.NoError(t, err)
	return reg.ID
}

// createTestUpstreamRegistry creates a test UpstreamRegistry with the given servers
func createTestUpstreamRegistry(servers []upstreamv0.ServerJSON) *toolhivetypes.UpstreamRegistry {
	return &toolhivetypes.UpstreamRegistry{
		Schema:  "https://example.com/schema.json",
		Version: "1.0.0",
		Data: toolhivetypes.UpstreamData{
			Servers: servers,
		},
	}
}

// createTestServer creates a test ServerJSON with the given name and version
func createTestServer(name, version string) upstreamv0.ServerJSON {
	return upstreamv0.ServerJSON{
		Name:        name,
		Version:     version,
		Description: "Test server description",
		Title:       "Test Server",
		WebsiteURL:  "https://example.com",
	}
}

// createTestServerWithPackages creates a test ServerJSON with packages
func createTestServerWithPackages(name, version string) upstreamv0.ServerJSON {
	server := createTestServer(name, version)
	server.Packages = []model.Package{
		{
			RegistryType:    "npm",
			RegistryBaseURL: "https://registry.npmjs.org",
			Identifier:      "@test/package",
			Version:         "1.0.0",
			RunTimeHint:     "npx",
			FileSHA256:      "abc123def456789",
			Transport: model.Transport{
				Type: "stdio",
				URL:  "https://example.com/transport",
			},
			RuntimeArguments: []model.Argument{
				{Name: "--yes"},
			},
			PackageArguments: []model.Argument{
				{Name: "--verbose"},
			},
			EnvironmentVariables: []model.KeyValueInput{
				{Name: "NODE_ENV"},
			},
		},
	}
	return server
}

// createTestServerWithRemotes creates a test ServerJSON with remotes
func createTestServerWithRemotes(name, version string) upstreamv0.ServerJSON {
	server := createTestServer(name, version)
	server.Remotes = []model.Transport{
		{
			Type: "sse",
			URL:  "https://example.com/sse",
			Headers: []model.KeyValueInput{
				{Name: "Authorization"},
			},
		},
	}
	return server
}

// createTestServerWithIcons creates a test ServerJSON with icons
func createTestServerWithIcons(name, version string) upstreamv0.ServerJSON {
	server := createTestServer(name, version)
	lightTheme := testThemeLight
	darkTheme := testThemeDark
	mimeType := testMimeTypePNG
	server.Icons = []model.Icon{
		{
			Src:      "https://example.com/icon-light.png",
			MimeType: &mimeType,
			Theme:    &lightTheme,
		},
		{
			Src:      "https://example.com/icon-dark.png",
			MimeType: &mimeType,
			Theme:    &darkTheme,
		},
	}
	return server
}

// createTestServerWithRepository creates a test ServerJSON with repository info
func createTestServerWithRepository(name, version string) upstreamv0.ServerJSON {
	server := createTestServer(name, version)
	server.Repository = &model.Repository{
		URL:       "https://github.com/test/repo",
		Source:    "github",
		ID:        "repo-123",
		Subfolder: "src",
	}
	return server
}

// createTestServerWithMeta creates a test ServerJSON with metadata
func createTestServerWithMeta(name, version string) upstreamv0.ServerJSON {
	server := createTestServer(name, version)
	server.Meta = &upstreamv0.ServerMeta{
		PublisherProvided: map[string]any{
			"custom_field": "custom_value",
			"nested": map[string]any{
				"key": "value",
			},
		},
	}
	return server
}

// createFullTestServer creates a test ServerJSON with all fields populated
func createFullTestServer(name, version string) upstreamv0.ServerJSON {
	lightTheme := testThemeLight
	mimeType := testMimeTypePNG

	return upstreamv0.ServerJSON{
		Name:        name,
		Version:     version,
		Description: "Full test server description",
		Title:       "Full Test Server",
		WebsiteURL:  "https://example.com",
		Repository: &model.Repository{
			URL:       "https://github.com/test/repo",
			Source:    "github",
			ID:        "repo-123",
			Subfolder: "src",
		},
		Icons: []model.Icon{
			{
				Src:      "https://example.com/icon.png",
				MimeType: &mimeType,
				Theme:    &lightTheme,
			},
		},
		Packages: []model.Package{
			{
				RegistryType:    "npm",
				RegistryBaseURL: "https://registry.npmjs.org",
				Identifier:      "@test/package",
				Version:         "1.0.0",
				RunTimeHint:     "npx",
				Transport: model.Transport{
					Type: "stdio",
				},
				RuntimeArguments: []model.Argument{
					{Name: "--yes"},
				},
				PackageArguments: []model.Argument{
					{Name: "--verbose"},
				},
				EnvironmentVariables: []model.KeyValueInput{
					{Name: "NODE_ENV"},
				},
			},
		},
		Remotes: []model.Transport{
			{
				Type: "sse",
				URL:  "https://example.com/sse",
				Headers: []model.KeyValueInput{
					{Name: "Authorization"},
				},
			},
		},
		Meta: &upstreamv0.ServerMeta{
			PublisherProvided: map[string]any{
				"custom": "value",
			},
		},
	}
}

// TestNewDBSyncWriter tests the constructor
func TestNewDBSyncWriter(t *testing.T) {
	t.Parallel()

	tests := []struct {
		name          string
		pool          *pgxpool.Pool
		expectError   bool
		errorContains string
	}{
		{
			name:        "valid pool",
			pool:        &pgxpool.Pool{}, // Non-nil pool
			expectError: false,
		},
		{
			name:          "nil pool",
			pool:          nil,
			expectError:   true,
			errorContains: "pgx pool is required",
		},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			t.Parallel()

			writer, err := NewDBSyncWriter(tt.pool, 0)

			if tt.expectError {
				require.Error(t, err)
				assert.Contains(t, err.Error(), tt.errorContains)
				assert.Nil(t, writer)
			} else {
				require.NoError(t, err)
				assert.NotNil(t, writer)
			}
		})
	}
}

// TestDbSyncWriter_Store tests the Store method
func TestDbSyncWriter_Store(t *testing.T) {
	t.Parallel()

	tests := []struct {
		name          string
		registryName  string
		setupFunc     func(t *testing.T, pool *pgxpool.Pool)
		registry      *toolhivetypes.UpstreamRegistry
		expectError   bool
		errorContains string
		validateFunc  func(t *testing.T, pool *pgxpool.Pool, registryName string)
	}{
		{
			name:         "successful sync with single server",
			registryName: "test-registry",
			//nolint:thelper // We want to see these lines in the test output
			setupFunc: func(t *testing.T, pool *pgxpool.Pool) {
				createTestRegistry(t, pool, "test-registry")
			},
			registry: createTestUpstreamRegistry([]upstreamv0.ServerJSON{
				createTestServer("test.org/server", "1.0.0"),
			}),
			expectError: false,
			//nolint:thelper // We want to see these lines in the test output
			validateFunc: func(t *testing.T, pool *pgxpool.Pool, registryName string) {
				ctx := context.Background()
				queries := sqlc.New(pool)
				regID := getTestRegistryID(t, pool, registryName)

				servers, err := queries.ListServers(ctx, sqlc.ListServersParams{RegistryID: regID, Size: 100})
				require.NoError(t, err)
				require.Len(t, servers, 1)
				assert.Equal(t, "test.org/server", servers[0].Name)
				assert.Equal(t, "1.0.0", servers[0].Version)
			},
		},
		{
			name:         "successful sync with multiple servers",
			registryName: "test-registry",
			//nolint:thelper // We want to see these lines in the test output
			setupFunc: func(t *testing.T, pool *pgxpool.Pool) {
				createTestRegistry(t, pool, "test-registry")
			},
			registry: createTestUpstreamRegistry([]upstreamv0.ServerJSON{
				createTestServer("test.org/server1", "1.0.0"),
				createTestServer("test.org/server2", "2.0.0"),
				createTestServer("test.org/server3", "3.0.0"),
			}),
			expectError: false,
			//nolint:thelper // We want to see these lines in the test output
			validateFunc: func(t *testing.T, pool *pgxpool.Pool, registryName string) {
				ctx := context.Background()
				queries := sqlc.New(pool)
				regID := getTestRegistryID(t, pool, registryName)

				servers, err := queries.ListServers(ctx, sqlc.ListServersParams{RegistryID: regID, Size: 100})
				require.NoError(t, err)
				require.Len(t, servers, 3)
			},
		},
		{
			name:         "successful sync with server packages",
			registryName: "test-registry",
			//nolint:thelper // We want to see these lines in the test output
			setupFunc: func(t *testing.T, pool *pgxpool.Pool) {
				createTestRegistry(t, pool, "test-registry")
			},
			registry: createTestUpstreamRegistry([]upstreamv0.ServerJSON{
				createTestServerWithPackages("test.org/server", "1.0.0"),
			}),
			expectError: false,
			//nolint:thelper // We want to see these lines in the test output
			validateFunc: func(t *testing.T, pool *pgxpool.Pool, registryName string) {
				ctx := context.Background()
				queries := sqlc.New(pool)
				regID := getTestRegistryID(t, pool, registryName)

				servers, err := queries.ListServers(ctx, sqlc.ListServersParams{RegistryID: regID, Size: 100})
				require.NoError(t, err)
				require.Len(t, servers, 1)

				packages, err := queries.ListServerPackages(ctx, []uuid.UUID{servers[0].ID})
				require.NoError(t, err)
				require.Len(t, packages, 1)
				assert.Equal(t, "npm", packages[0].RegistryType)
				assert.Equal(t, "@test/package", packages[0].PkgIdentifier)
			},
		},
		{
			name:         "successful sync with server remotes",
			registryName: "test-registry",
			//nolint:thelper // We want to see these lines in the test output
			setupFunc: func(t *testing.T, pool *pgxpool.Pool) {
				createTestRegistry(t, pool, "test-registry")
			},
			registry: createTestUpstreamRegistry([]upstreamv0.ServerJSON{
				createTestServerWithRemotes("test.org/server", "1.0.0"),
			}),
			expectError: false,
			//nolint:thelper // We want to see these lines in the test output
			validateFunc: func(t *testing.T, pool *pgxpool.Pool, registryName string) {
				ctx := context.Background()
				queries := sqlc.New(pool)
				regID := getTestRegistryID(t, pool, registryName)

				servers, err := queries.ListServers(ctx, sqlc.ListServersParams{RegistryID: regID, Size: 100})
				require.NoError(t, err)
				require.Len(t, servers, 1)

				remotes, err := queries.ListServerRemotes(ctx, []uuid.UUID{servers[0].ID})
				require.NoError(t, err)
				require.Len(t, remotes, 1)
				assert.Equal(t, "sse", remotes[0].Transport)
				assert.Equal(t, "https://example.com/sse", remotes[0].TransportUrl)
			},
		},
		{
			name:         "successful sync with server icons",
			registryName: "test-registry",
			//nolint:thelper // We want to see these lines in the test output
			setupFunc: func(t *testing.T, pool *pgxpool.Pool) {
				createTestRegistry(t, pool, "test-registry")
			},
			registry: createTestUpstreamRegistry([]upstreamv0.ServerJSON{
				createTestServerWithIcons("test.org/server", "1.0.0"),
			}),
			expectError: false,
			//nolint:thelper // We want to see these lines in the test output
			validateFunc: func(t *testing.T, pool *pgxpool.Pool, registryName string) {
				ctx := context.Background()
				queries := sqlc.New(pool)
				regID := getTestRegistryID(t, pool, registryName)

				servers, err := queries.ListServers(ctx, sqlc.ListServersParams{RegistryID: regID, Size: 100})
				require.NoError(t, err)
				require.Len(t, servers, 1)
				// Icons are stored but not retrieved by ListServers
			},
		},
		{
			name:         "successful sync with repository info",
			registryName: "test-registry",
			//nolint:thelper // We want to see these lines in the test output
			setupFunc: func(t *testing.T, pool *pgxpool.Pool) {
				createTestRegistry(t, pool, "test-registry")
			},
			registry: createTestUpstreamRegistry([]upstreamv0.ServerJSON{
				createTestServerWithRepository("test.org/server", "1.0.0"),
			}),
			expectError: false,
			//nolint:thelper // We want to see these lines in the test output
			validateFunc: func(t *testing.T, pool *pgxpool.Pool, registryName string) {
				ctx := context.Background()
				queries := sqlc.New(pool)
				regID := getTestRegistryID(t, pool, registryName)

				serverRows, err := queries.GetServerVersion(ctx, sqlc.GetServerVersionParams{
					RegistryID: regID,
					Name:       "test.org/server",
					Version:    "1.0.0",
					Size:       100,
				})
				require.NoError(t, err)
				require.NotEmpty(t, serverRows)
				server := serverRows[0]
				require.NotNil(t, server.RepositoryUrl)
				assert.Equal(t, "https://github.com/test/repo", *server.RepositoryUrl)
			},
		},
		{
			name:         "successful sync with metadata",
			registryName: "test-registry",
			//nolint:thelper // We want to see these lines in the test output
			setupFunc: func(t *testing.T, pool *pgxpool.Pool) {
				createTestRegistry(t, pool, "test-registry")
			},
			registry: createTestUpstreamRegistry([]upstreamv0.ServerJSON{
				createTestServerWithMeta("test.org/server", "1.0.0"),
			}),
			expectError: false,
			//nolint:thelper // We want to see these lines in the test output
			validateFunc: func(t *testing.T, pool *pgxpool.Pool, registryName string) {
				ctx := context.Background()
				queries := sqlc.New(pool)
				regID := getTestRegistryID(t, pool, registryName)

				serverRows, err := queries.GetServerVersion(ctx, sqlc.GetServerVersionParams{
					RegistryID: regID,
					Name:       "test.org/server",
					Version:    "1.0.0",
					Size:       100,
				})
				require.NoError(t, err)
				require.NotEmpty(t, serverRows)
				server := serverRows[0]
				require.NotNil(t, server.ServerMeta)
			},
		},
		{
			name:         "successful sync with full server",
			registryName: "test-registry",
			//nolint:thelper // We want to see these lines in the test output
			setupFunc: func(t *testing.T, pool *pgxpool.Pool) {
				createTestRegistry(t, pool, "test-registry")
			},
			registry: createTestUpstreamRegistry([]upstreamv0.ServerJSON{
				createFullTestServer("test.org/server", "1.0.0"),
			}),
			expectError: false,
			//nolint:thelper // We want to see these lines in the test output
			validateFunc: func(t *testing.T, pool *pgxpool.Pool, registryName string) {
				ctx := context.Background()
				queries := sqlc.New(pool)
				regID := getTestRegistryID(t, pool, registryName)

				servers, err := queries.ListServers(ctx, sqlc.ListServersParams{RegistryID: regID, Size: 100})
				require.NoError(t, err)
				require.Len(t, servers, 1)

				packages, err := queries.ListServerPackages(ctx, []uuid.UUID{servers[0].ID})
				require.NoError(t, err)
				require.Len(t, packages, 1)

				remotes, err := queries.ListServerRemotes(ctx, []uuid.UUID{servers[0].ID})
				require.NoError(t, err)
				require.Len(t, remotes, 1)
			},
		},
		{
			name:         "successful sync with empty servers list",
			registryName: "test-registry",
			//nolint:thelper // We want to see these lines in the test output
			setupFunc: func(t *testing.T, pool *pgxpool.Pool) {
				createTestRegistry(t, pool, "test-registry")
			},
			registry:    createTestUpstreamRegistry([]upstreamv0.ServerJSON{}),
			expectError: false,
			//nolint:thelper // We want to see these lines in the test output
			validateFunc: func(t *testing.T, pool *pgxpool.Pool, registryName string) {
				ctx := context.Background()
				queries := sqlc.New(pool)
				regID := getTestRegistryID(t, pool, registryName)

				servers, err := queries.ListServers(ctx, sqlc.ListServersParams{RegistryID: regID, Size: 100})
				require.NoError(t, err)
				require.Len(t, servers, 0)
			},
		},
		{
			name:         "successful sync replaces existing servers",
			registryName: "test-registry",
			//nolint:thelper // We want to see these lines in the test output
			setupFunc: func(t *testing.T, pool *pgxpool.Pool) {
				ctx := context.Background()
				ids := createTestRegistry(t, pool, "test-registry")
				queries := sqlc.New(pool)

				// Insert registry entry
				entryID, err := queries.InsertRegistryEntry(ctx, sqlc.InsertRegistryEntryParams{
					SourceID:  ids.sourceID,
					EntryType: sqlc.EntryTypeMCP,
					Name:      "test.org/old-server",
				})
				require.NoError(t, err)

				// Insert entry version
				versionID, err := queries.InsertEntryVersion(ctx, sqlc.InsertEntryVersionParams{
					EntryID: entryID,
					Version: "0.1.0",
				})
				require.NoError(t, err)

				// Insert existing server that should be deleted
				_, err = queries.InsertServerVersion(ctx, sqlc.InsertServerVersionParams{
					VersionID: versionID,
				})
				require.NoError(t, err)
			},
			registry: createTestUpstreamRegistry([]upstreamv0.ServerJSON{
				createTestServer("test.org/new-server", "1.0.0"),
			}),
			expectError: false,
			//nolint:thelper // We want to see these lines in the test output
			validateFunc: func(t *testing.T, pool *pgxpool.Pool, registryName string) {
				ctx := context.Background()
				queries := sqlc.New(pool)
				regID := getTestRegistryID(t, pool, registryName)

				servers, err := queries.ListServers(ctx, sqlc.ListServersParams{RegistryID: regID, Size: 100})
				require.NoError(t, err)
				require.Len(t, servers, 1)
				assert.Equal(t, "test.org/new-server", servers[0].Name)
			},
		},
		{
			name:         "successful sync updates latest version - single version",
			registryName: "test-registry",
			//nolint:thelper // We want to see these lines in the test output
			setupFunc: func(t *testing.T, pool *pgxpool.Pool) {
				createTestRegistry(t, pool, "test-registry")
			},
			registry: createTestUpstreamRegistry([]upstreamv0.ServerJSON{
				createTestServer("test.org/server", "1.0.0"),
			}),
			expectError: false,
			//nolint:thelper // We want to see these lines in the test output
			validateFunc: func(t *testing.T, pool *pgxpool.Pool, registryName string) {
				ctx := context.Background()
				queries := sqlc.New(pool)
				regID := getTestRegistryID(t, pool, registryName)

				serverRows, err := queries.GetServerVersion(ctx, sqlc.GetServerVersionParams{
					RegistryID: regID,
					Name:       "test.org/server",
					Version:    "1.0.0",
					Size:       100,
				})
				require.NoError(t, err)
				require.NotEmpty(t, serverRows)
				server := serverRows[0]
				// Single version should be marked as latest
				assert.True(t, server.IsLatest)
			},
		},
		{
			name:         "successful sync updates latest version - multiple versions",
			registryName: "test-registry",
			//nolint:thelper // We want to see these lines in the test output
			setupFunc: func(t *testing.T, pool *pgxpool.Pool) {
				createTestRegistry(t, pool, "test-registry")
			},
			registry: createTestUpstreamRegistry([]upstreamv0.ServerJSON{
				createTestServer("test.org/server", "1.0.0"),
				createTestServer("test.org/server", "2.0.0"),
				createTestServer("test.org/server", "1.5.0"),
			}),
			expectError: false,
			//nolint:thelper // We want to see these lines in the test output
			validateFunc: func(t *testing.T, pool *pgxpool.Pool, registryName string) {
				ctx := context.Background()
				queries := sqlc.New(pool)
				regID := getTestRegistryID(t, pool, registryName)

				// Version 2.0.0 should be latest
				serverRows, err := queries.GetServerVersion(ctx, sqlc.GetServerVersionParams{
					RegistryID: regID,
					Name:       "test.org/server",
					Version:    "2.0.0",
					Size:       100,
				})
				require.NoError(t, err)
				require.NotEmpty(t, serverRows)
				server := serverRows[0]
				assert.True(t, server.IsLatest)

				// Version 1.0.0 should not be latest
				serverRows, err = queries.GetServerVersion(ctx, sqlc.GetServerVersionParams{
					RegistryID: regID,
					Name:       "test.org/server",
					Version:    "1.0.0",
					Size:       100,
				})
				require.NoError(t, err)
				require.NotEmpty(t, serverRows)
				server = serverRows[0]
				assert.False(t, server.IsLatest)

				// Version 1.5.0 should not be latest
				serverRows, err = queries.GetServerVersion(ctx, sqlc.GetServerVersionParams{
					RegistryID: regID,
					Name:       "test.org/server",
					Version:    "1.5.0",
					Size:       100,
				})
				require.NoError(t, err)
				require.NotEmpty(t, serverRows)
				server = serverRows[0]
				assert.False(t, server.IsLatest)
			},
		},
		{
			name:          "registry not found error",
			registryName:  "non-existent-registry",
			setupFunc:     func(_ *testing.T, _ *pgxpool.Pool) {},
			registry:      createTestUpstreamRegistry([]upstreamv0.ServerJSON{}),
			expectError:   true,
			errorContains: "registry not found",
		},
		{
			name:          "nil registry error",
			registryName:  "test-registry",
			setupFunc:     func(_ *testing.T, _ *pgxpool.Pool) {},
			registry:      nil,
			expectError:   true,
			errorContains: "registry data is required",
		},
		{
			name:         "multiple servers same name different versions",
			registryName: "test-registry",
			//nolint:thelper // We want to see these lines in the test output
			setupFunc: func(t *testing.T, pool *pgxpool.Pool) {
				createTestRegistry(t, pool, "test-registry")
			},
			registry: createTestUpstreamRegistry([]upstreamv0.ServerJSON{
				createTestServer("test.org/server", "1.0.0"),
				createTestServer("test.org/server", "1.1.0"),
				createTestServer("test.org/server", "2.0.0"),
			}),
			expectError: false,
			//nolint:thelper // We want to see these lines in the test output
			validateFunc: func(t *testing.T, pool *pgxpool.Pool, registryName string) {
				ctx := context.Background()
				queries := sqlc.New(pool)
				regID := getTestRegistryID(t, pool, registryName)

				versions, err := queries.ListServers(ctx, sqlc.ListServersParams{
					RegistryID: regID,
					Name:       ptr.String("test.org/server"),
					Size:       100,
				})
				require.NoError(t, err)
				require.Len(t, versions, 3)
			},
		},
		{
			name:         "server with empty optional fields",
			registryName: "test-registry",
			//nolint:thelper // We want to see these lines in the test output
			setupFunc: func(t *testing.T, pool *pgxpool.Pool) {
				createTestRegistry(t, pool, "test-registry")
			},
			registry: createTestUpstreamRegistry([]upstreamv0.ServerJSON{
				{
					Name:        "test.org/minimal",
					Version:     "1.0.0",
					Description: "",
					Title:       "",
					WebsiteURL:  "",
				},
			}),
			expectError: false,
			//nolint:thelper // We want to see these lines in the test output
			validateFunc: func(t *testing.T, pool *pgxpool.Pool, registryName string) {
				ctx := context.Background()
				queries := sqlc.New(pool)
				regID := getTestRegistryID(t, pool, registryName)

				serverRows, err := queries.GetServerVersion(ctx, sqlc.GetServerVersionParams{
					RegistryID: regID,
					Name:       "test.org/minimal",
					Version:    "1.0.0",
					Size:       100,
				})
				require.NoError(t, err)
				require.NotEmpty(t, serverRows)
				server := serverRows[0]
				assert.Nil(t, server.Description)
				assert.Nil(t, server.Title)
				assert.Nil(t, server.Website)
			},
		},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			t.Parallel()

			pool, cleanup := setupTestDB(t)
			defer cleanup()

			tt.setupFunc(t, pool)

			writer, err := NewDBSyncWriter(pool, testMaxMetaSize)
			require.NoError(t, err)

			err = writer.Store(context.Background(), tt.registryName, tt.registry)

			if tt.expectError {
				require.Error(t, err)
				if tt.errorContains != "" {
					assert.Contains(t, err.Error(), tt.errorContains)
				}
			} else {
				require.NoError(t, err)
				if tt.validateFunc != nil {
					tt.validateFunc(t, pool, tt.registryName)
				}
			}
		})
	}
}

// TestServerKey tests the server key generation function
func TestServerKey(t *testing.T) {
	t.Parallel()

	tests := []struct {
		name     string
		server   string
		version  string
		expected string
	}{
		{
			name:     "simple name and version",
			server:   "test-server",
			version:  "1.0.0",
			expected: "test-server@1.0.0",
		},
		{
			name:     "namespaced server",
			server:   "test.org/my-server",
			version:  "2.0.0",
			expected: "test.org/my-server@2.0.0",
		},
		{
			name:     "complex version",
			server:   "server",
			version:  "1.0.0-alpha+build.123",
			expected: "server@1.0.0-alpha+build.123",
		},
		{
			name:     "empty name",
			server:   "",
			version:  "1.0.0",
			expected: "@1.0.0",
		},
		{
			name:     "empty version",
			server:   "server",
			version:  "",
			expected: "server@",
		},
		{
			name:     "both empty",
			server:   "",
			version:  "",
			expected: "@",
		},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			t.Parallel()

			result := serverKey(tt.server, tt.version)
			assert.Equal(t, tt.expected, result)
		})
	}
}

// TestNilIfEmpty tests the nilIfEmpty helper function
func TestNilIfEmpty(t *testing.T) {
	t.Parallel()

	tests := []struct {
		name     string
		input    string
		expected *string
	}{
		{
			name:     "non-empty string",
			input:    "test",
			expected: strPtr("test"),
		},
		{
			name:     "empty string",
			input:    "",
			expected: nil,
		},
		{
			name:     "whitespace only",
			input:    "   ",
			expected: strPtr("   "),
		},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			t.Parallel()

			result := nilIfEmpty(tt.input)
			if tt.expected == nil {
				assert.Nil(t, result)
			} else {
				require.NotNil(t, result)
				assert.Equal(t, *tt.expected, *result)
			}
		})
	}
}

// strPtr returns a pointer to the given string
func strPtr(s string) *string {
	return &s
}

// TestExtractArgumentValues tests the extractArgumentValues helper function
func TestExtractArgumentValues(t *testing.T) {
	t.Parallel()

	tests := []struct {
		name      string
		arguments []model.Argument
		expected  []string
	}{
		{
			name:      "empty arguments",
			arguments: []model.Argument{},
			expected:  []string{},
		},
		{
			name: "single argument",
			arguments: []model.Argument{
				{Name: "--verbose"},
			},
			expected: []string{"--verbose"},
		},
		{
			name: "multiple arguments",
			arguments: []model.Argument{
				{Name: "--verbose"},
				{Name: "--output"},
				{Name: "-f"},
			},
			expected: []string{"--verbose", "--output", "-f"},
		},
		{
			name:      "nil arguments",
			arguments: nil,
			expected:  []string{},
		},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			t.Parallel()

			result := extractArgumentValues(tt.arguments)
			assert.Equal(t, tt.expected, result)
		})
	}
}

// TestSerializeKeyValueInputs tests the serializeKeyValueInputs helper function
func TestSerializeKeyValueInputs(t *testing.T) {
	t.Parallel()

	// Create test inputs with full metadata using proper nested struct syntax
	inputWithMetadata := model.KeyValueInput{
		InputWithVariables: model.InputWithVariables{
			Input: model.Input{
				Default:     "default_value",
				Description: "API key for authentication",
				IsSecret:    true,
				IsRequired:  true,
			},
		},
		Name: "API_KEY",
	}

	inputWithSecret := model.KeyValueInput{Name: "API_KEY"}
	inputWithSecret.IsSecret = true

	inputWithDefault := model.KeyValueInput{Name: "DEBUG"}
	inputWithDefault.Default = "false"

	tests := []struct {
		name     string
		kvInputs []model.KeyValueInput
		expected string
	}{
		{
			name:     "empty inputs",
			kvInputs: []model.KeyValueInput{},
			expected: "[]",
		},
		{
			name: "single input with name only",
			kvInputs: []model.KeyValueInput{
				{Name: "NODE_ENV"},
			},
			expected: `[{"name":"NODE_ENV"}]`,
		},
		{
			name:     "input with full metadata",
			kvInputs: []model.KeyValueInput{inputWithMetadata},
			expected: `[{"name":"API_KEY","default":"default_value","description":"API key for authentication","isSecret":true,"isRequired":true}]`,
		},
		{
			name:     "multiple inputs",
			kvInputs: []model.KeyValueInput{{Name: "NODE_ENV"}, inputWithSecret, inputWithDefault},
			expected: `[{"name":"NODE_ENV"},{"name":"API_KEY","isSecret":true},{"name":"DEBUG","default":"false"}]`,
		},
		{
			name:     "nil inputs",
			kvInputs: nil,
			expected: "[]",
		},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			t.Parallel()

			result := serializeKeyValueInputs(tt.kvInputs)
			assert.JSONEq(t, tt.expected, string(result))
		})
	}
}

// TestSerializeServerMeta tests the serializeServerMeta helper function
func TestSerializeServerMeta(t *testing.T) {
	t.Parallel()

	tests := []struct {
		name        string
		meta        *upstreamv0.ServerMeta
		maxMetaSize int
		expectNil   bool
		expectError bool
	}{
		{
			name:      "nil meta",
			meta:      nil,
			expectNil: true,
		},
		{
			name: "nil publisher provided",
			meta: &upstreamv0.ServerMeta{
				PublisherProvided: nil,
			},
			expectNil: true,
		},
		{
			name: "empty publisher provided",
			meta: &upstreamv0.ServerMeta{
				PublisherProvided: map[string]any{},
			},
			expectNil: true,
		},
		{
			name: "with publisher provided data",
			meta: &upstreamv0.ServerMeta{
				PublisherProvided: map[string]any{
					"key": "value",
				},
			},
			maxMetaSize: testMaxMetaSize,
			expectNil:   false,
		},
		{
			name: "with nested data",
			meta: &upstreamv0.ServerMeta{
				PublisherProvided: map[string]any{
					"nested": map[string]any{
						"key": "value",
					},
				},
			},
			maxMetaSize: testMaxMetaSize,
			expectNil:   false,
		},
		{
			name: "with data within size limit",
			meta: &upstreamv0.ServerMeta{
				PublisherProvided: map[string]any{
					"key": "value",
				},
			},
			maxMetaSize: 1000,
			expectNil:   false,
		},
		{
			name: "with data exceeding size limit",
			meta: &upstreamv0.ServerMeta{
				PublisherProvided: map[string]any{
					"key": "value",
				},
			},
			maxMetaSize: 5,
			expectError: true,
		},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			t.Parallel()

			result, err := serializeServerMeta(tt.meta, tt.maxMetaSize)

			if tt.expectError {
				require.Error(t, err)
			} else {
				require.NoError(t, err)
				if tt.expectNil {
					assert.Nil(t, result)
				} else {
					assert.NotNil(t, result)
					assert.Greater(t, len(result), 0)
				}
			}
		})
	}
}

// TestDbSyncWriter_Store_ContextCancellation tests context cancellation behavior
func TestDbSyncWriter_Store_ContextCancellation(t *testing.T) {
	t.Parallel()

	pool, cleanup := setupTestDB(t)
	defer cleanup()

	createTestRegistry(t, pool, "test-registry")

	writer, err := NewDBSyncWriter(pool, testMaxMetaSize)
	require.NoError(t, err)

	// Create a cancelled context
	ctx, cancel := context.WithCancel(context.Background())
	cancel()

	registry := createTestUpstreamRegistry([]upstreamv0.ServerJSON{
		createTestServer("test.org/server", "1.0.0"),
	})

	err = writer.Store(ctx, "test-registry", registry)
	require.Error(t, err)
}

// TestDbSyncWriter_Store_IconThemes tests icon theme handling
func TestDbSyncWriter_Store_IconThemes(t *testing.T) {
	t.Parallel()

	pool, cleanup := setupTestDB(t)
	defer cleanup()

	createTestRegistry(t, pool, "test-registry")

	writer, err := NewDBSyncWriter(pool, testMaxMetaSize)
	require.NoError(t, err)

	// Create server with various icon themes
	mimeType := testMimeTypePNG
	lightTheme := testThemeLight
	darkTheme := testThemeDark
	unknownTheme := "custom"

	server := createTestServer("test.org/server", "1.0.0")
	server.Icons = []model.Icon{
		{
			Src:      "https://example.com/light.png",
			MimeType: &mimeType,
			Theme:    &lightTheme,
		},
		{
			Src:      "https://example.com/dark.png",
			MimeType: &mimeType,
			Theme:    &darkTheme,
		},
		{
			Src:      "https://example.com/unknown.png",
			MimeType: &mimeType,
			Theme:    &unknownTheme, // Should default to light
		},
		{
			Src:      "https://example.com/nil-theme.png",
			MimeType: &mimeType,
			Theme:    nil, // Should default to light
		},
	}

	registry := createTestUpstreamRegistry([]upstreamv0.ServerJSON{server})

	err = writer.Store(context.Background(), "test-registry", registry)
	require.NoError(t, err)

	// Verify servers were created
	ctx := context.Background()
	queries := sqlc.New(pool)
	regID := getTestRegistryID(t, pool, "test-registry")
	servers, err := queries.ListServers(ctx, sqlc.ListServersParams{RegistryID: regID, Size: 100})
	require.NoError(t, err)
	require.Len(t, servers, 1)
}

// TestDbSyncWriter_Store_PackageAcrossServers tests packages across multiple server versions
// Note: The database schema only allows one package per server version (server_id is PRIMARY KEY)
func TestDbSyncWriter_Store_PackageAcrossServers(t *testing.T) {
	t.Parallel()

	pool, cleanup := setupTestDB(t)
	defer cleanup()

	createTestRegistry(t, pool, "test-registry")

	writer, err := NewDBSyncWriter(pool, testMaxMetaSize)
	require.NoError(t, err)

	// Create multiple server versions, each with their own package
	servers := []upstreamv0.ServerJSON{
		{
			Name:        "test.org/server",
			Version:     "1.0.0",
			Description: "Test server v1",
			Packages: []model.Package{
				{
					RegistryType:    "npm",
					RegistryBaseURL: "https://registry.npmjs.org",
					Identifier:      "@test/package",
					Version:         "1.0.0",
					Transport:       model.Transport{Type: "stdio"},
				},
			},
		},
		{
			Name:        "test.org/server",
			Version:     "2.0.0",
			Description: "Test server v2",
			Packages: []model.Package{
				{
					RegistryType:    "pypi",
					RegistryBaseURL: "https://pypi.org/simple",
					Identifier:      "test-package",
					Version:         "2.0.0",
					Transport:       model.Transport{Type: "stdio"},
				},
			},
		},
		{
			Name:        "test.org/other-server",
			Version:     "1.0.0",
			Description: "Other test server",
			Packages: []model.Package{
				{
					RegistryType: "oci",
					Identifier:   "ghcr.io/test/package:v1.0.0",
					Transport:    model.Transport{Type: "stdio"},
				},
			},
		},
	}

	registry := createTestUpstreamRegistry(servers)

	err = writer.Store(context.Background(), "test-registry", registry)
	require.NoError(t, err)

	ctx := context.Background()
	queries := sqlc.New(pool)
	regID := getTestRegistryID(t, pool, "test-registry")

	serverRows, err := queries.ListServers(ctx, sqlc.ListServersParams{RegistryID: regID, Size: 100})
	require.NoError(t, err)
	require.Len(t, serverRows, 3)

	// Extract server IDs
	serverIDs := make([]uuid.UUID, len(serverRows))
	for i, s := range serverRows {
		serverIDs[i] = s.ID
	}

	packages, err := queries.ListServerPackages(ctx, serverIDs)
	require.NoError(t, err)
	require.Len(t, packages, 3)
}

// TestDbSyncWriter_Store_MultipleRemotes tests multiple remotes per server
func TestDbSyncWriter_Store_MultipleRemotes(t *testing.T) {
	t.Parallel()

	pool, cleanup := setupTestDB(t)
	defer cleanup()

	createTestRegistry(t, pool, "test-registry")

	writer, err := NewDBSyncWriter(pool, testMaxMetaSize)
	require.NoError(t, err)

	server := createTestServer("test.org/server", "1.0.0")
	server.Remotes = []model.Transport{
		{
			Type: "sse",
			URL:  "https://example.com/sse1",
		},
		{
			Type: "sse",
			URL:  "https://example.com/sse2",
		},
		{
			Type: "streamable-http",
			URL:  "https://example.com/http",
		},
	}

	registry := createTestUpstreamRegistry([]upstreamv0.ServerJSON{server})

	err = writer.Store(context.Background(), "test-registry", registry)
	require.NoError(t, err)

	ctx := context.Background()
	queries := sqlc.New(pool)
	regID := getTestRegistryID(t, pool, "test-registry")

	servers, err := queries.ListServers(ctx, sqlc.ListServersParams{RegistryID: regID, Size: 100})
	require.NoError(t, err)
	require.Len(t, servers, 1)

	remotes, err := queries.ListServerRemotes(ctx, []uuid.UUID{servers[0].ID})
	require.NoError(t, err)
	require.Len(t, remotes, 3)
}

// TestDbSyncWriter_Store_LatestVersionDetermination tests latest version logic
func TestDbSyncWriter_Store_LatestVersionDetermination(t *testing.T) {
	t.Parallel()

	tests := []struct {
		name           string
		versions       []string
		expectedLatest string
		serverName     string
		registryName   string
	}{
		{
			name:           "standard semver versions",
			versions:       []string{"1.0.0", "2.0.0", "1.5.0"},
			expectedLatest: "2.0.0",
			serverName:     "test.org/server1",
			registryName:   "test-registry-1",
		},
		{
			name:           "with prerelease versions",
			versions:       []string{"1.0.0", "1.0.1-alpha", "1.0.1"},
			expectedLatest: "1.0.1",
			serverName:     "test.org/server2",
			registryName:   "test-registry-2",
		},
		{
			name:           "all prerelease versions",
			versions:       []string{"1.0.0-alpha", "1.0.0-beta", "1.0.0-rc"},
			expectedLatest: "1.0.0-rc",
			serverName:     "test.org/server3",
			registryName:   "test-registry-3",
		},
		{
			name:           "single version",
			versions:       []string{"1.0.0"},
			expectedLatest: "1.0.0",
			serverName:     "test.org/server4",
			registryName:   "test-registry-4",
		},
		{
			name:           "with v prefix",
			versions:       []string{"v1.0.0", "v2.0.0", "v1.5.0"},
			expectedLatest: "v2.0.0",
			serverName:     "test.org/server5",
			registryName:   "test-registry-5",
		},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			t.Parallel()

			pool, cleanup := setupTestDB(t)
			defer cleanup()

			createTestRegistry(t, pool, tt.registryName)

			writer, err := NewDBSyncWriter(pool, testMaxMetaSize)
			require.NoError(t, err)

			servers := make([]upstreamv0.ServerJSON, len(tt.versions))
			for i, version := range tt.versions {
				servers[i] = createTestServer(tt.serverName, version)
			}

			registry := createTestUpstreamRegistry(servers)
			err = writer.Store(context.Background(), tt.registryName, registry)
			require.NoError(t, err)

			ctx := context.Background()
			queries := sqlc.New(pool)
			regID := getTestRegistryID(t, pool, tt.registryName)

			for _, version := range tt.versions {
				serverRows, err := queries.GetServerVersion(ctx, sqlc.GetServerVersionParams{
					RegistryID: regID,
					Name:       tt.serverName,
					Version:    version,
					Size:       100,
				})
				require.NoError(t, err)
				require.NotEmpty(t, serverRows)
				server := serverRows[0]

				if version == tt.expectedLatest {
					assert.True(t, server.IsLatest, "Expected %s to be latest", version)
				} else {
					assert.False(t, server.IsLatest, "Expected %s to not be latest", version)
				}
			}
		})
	}
}

// TestDbSyncWriter_Store_UUIDStability verifies that when the same server is synced twice
// without changes, it keeps the same UUID.
func TestDbSyncWriter_Store_UUIDStability(t *testing.T) {
	t.Parallel()

	pool, cleanup := setupTestDB(t)
	defer cleanup()

	createTestRegistry(t, pool, "test-registry")

	writer, err := NewDBSyncWriter(pool, testMaxMetaSize)
	require.NoError(t, err)

	ctx := context.Background()
	queries := sqlc.New(pool)
	regID := getTestRegistryID(t, pool, "test-registry")

	// Create UpstreamRegistry with 2 servers
	registry := createTestUpstreamRegistry([]upstreamv0.ServerJSON{
		createTestServer("test.org/server-a", "1.0.0"),
		createTestServer("test.org/server-b", "2.0.0"),
	})

	// First sync
	err = writer.Store(ctx, "test-registry", registry)
	require.NoError(t, err)

	// Query DB and record server UUIDs and created_at timestamps
	serverA1Rows, err := queries.GetServerVersion(ctx, sqlc.GetServerVersionParams{
		RegistryID: regID,
		Name:       "test.org/server-a",
		Version:    "1.0.0",
		Size:       100,
	})
	require.NoError(t, err)
	require.NotEmpty(t, serverA1Rows)
	serverA1 := serverA1Rows[0]

	serverB1Rows, err := queries.GetServerVersion(ctx, sqlc.GetServerVersionParams{
		RegistryID: regID,
		Name:       "test.org/server-b",
		Version:    "2.0.0",
		Size:       100,
	})
	require.NoError(t, err)
	require.NotEmpty(t, serverB1Rows)
	serverB1 := serverB1Rows[0]

	// Store original UUIDs and timestamps
	originalUUIDA := serverA1.ID
	originalUUIDB := serverB1.ID
	originalCreatedAtA := serverA1.CreatedAt
	originalCreatedAtB := serverB1.CreatedAt

	// Second sync with exact same data
	err = writer.Store(ctx, "test-registry", registry)
	require.NoError(t, err)

	// Query DB again and verify UUIDs are identical
	serverA2Rows, err := queries.GetServerVersion(ctx, sqlc.GetServerVersionParams{
		RegistryID: regID,
		Name:       "test.org/server-a",
		Version:    "1.0.0",
		Size:       100,
	})
	require.NoError(t, err)
	require.NotEmpty(t, serverA2Rows)
	serverA2 := serverA2Rows[0]

	serverB2Rows, err := queries.GetServerVersion(ctx, sqlc.GetServerVersionParams{
		RegistryID: regID,
		Name:       "test.org/server-b",
		Version:    "2.0.0",
		Size:       100,
	})
	require.NoError(t, err)
	require.NotEmpty(t, serverB2Rows)
	serverB2 := serverB2Rows[0]

	// Verify UUIDs are the same (not new UUIDs)
	assert.Equal(t, originalUUIDA, serverA2.ID, "Server A UUID should be preserved after re-sync")
	assert.Equal(t, originalUUIDB, serverB2.ID, "Server B UUID should be preserved after re-sync")

	// Verify created_at is preserved
	assert.Equal(t, originalCreatedAtA, serverA2.CreatedAt, "Server A created_at should be preserved")
	assert.Equal(t, originalCreatedAtB, serverB2.CreatedAt, "Server B created_at should be preserved")

	// Verify updated_at changed (or at least is not nil)
	assert.NotNil(t, serverA2.UpdatedAt, "Server A updated_at should be set")
	assert.NotNil(t, serverB2.UpdatedAt, "Server B updated_at should be set")
}

// TestDbSyncWriter_Store_UpdatePreservesUUID verifies that when a server's fields change,
// the UUID stays the same but fields are updated.
func TestDbSyncWriter_Store_UpdatePreservesUUID(t *testing.T) {
	t.Parallel()

	pool, cleanup := setupTestDB(t)
	defer cleanup()

	createTestRegistry(t, pool, "test-registry")

	writer, err := NewDBSyncWriter(pool, testMaxMetaSize)
	require.NoError(t, err)

	ctx := context.Background()
	queries := sqlc.New(pool)
	regID := getTestRegistryID(t, pool, "test-registry")

	// First sync with original description
	server := createTestServer("test.org/server", "1.0.0")
	server.Description = "Old description"
	server.Title = "Old Title"
	registry := createTestUpstreamRegistry([]upstreamv0.ServerJSON{server})

	err = writer.Store(ctx, "test-registry", registry)
	require.NoError(t, err)

	// Query and record original UUID and description
	serverV1Rows, err := queries.GetServerVersion(ctx, sqlc.GetServerVersionParams{
		RegistryID: regID,
		Name:       "test.org/server",
		Version:    "1.0.0",
		Size:       100,
	})
	require.NoError(t, err)
	require.NotEmpty(t, serverV1Rows)
	serverV1 := serverV1Rows[0]

	originalUUID := serverV1.ID
	originalCreatedAt := serverV1.CreatedAt
	require.NotNil(t, serverV1.Description)
	assert.Equal(t, "Old description", *serverV1.Description)
	require.NotNil(t, serverV1.Title)
	assert.Equal(t, "Old Title", *serverV1.Title)

	// Second sync with updated description
	serverUpdated := createTestServer("test.org/server", "1.0.0")
	serverUpdated.Description = "New description"
	serverUpdated.Title = "New Title"
	registryUpdated := createTestUpstreamRegistry([]upstreamv0.ServerJSON{serverUpdated})

	err = writer.Store(ctx, "test-registry", registryUpdated)
	require.NoError(t, err)

	// Query and verify UUID is the same but description is updated
	serverV2Rows, err := queries.GetServerVersion(ctx, sqlc.GetServerVersionParams{
		RegistryID: regID,
		Name:       "test.org/server",
		Version:    "1.0.0",
		Size:       100,
	})
	require.NoError(t, err)
	require.NotEmpty(t, serverV2Rows)
	serverV2 := serverV2Rows[0]

	// Verify UUID is preserved
	assert.Equal(t, originalUUID, serverV2.ID, "Server UUID should be preserved after update")

	// Verify created_at is preserved
	assert.Equal(t, originalCreatedAt, serverV2.CreatedAt, "Server created_at should be preserved")

	// Verify fields are updated
	require.NotNil(t, serverV2.Description)
	assert.Equal(t, "New description", *serverV2.Description, "Description should be updated")
	require.NotNil(t, serverV2.Title)
	assert.Equal(t, "New Title", *serverV2.Title, "Title should be updated")

	// Verify updated_at changed
	assert.NotNil(t, serverV2.UpdatedAt, "updated_at should be set")
}

// TestDbSyncWriter_Store_OrphanedServerCleanup verifies that servers removed from upstream
// are deleted from the database.
func TestDbSyncWriter_Store_OrphanedServerCleanup(t *testing.T) {
	t.Parallel()

	pool, cleanup := setupTestDB(t)
	defer cleanup()

	createTestRegistry(t, pool, "test-registry")

	writer, err := NewDBSyncWriter(pool, testMaxMetaSize)
	require.NoError(t, err)

	ctx := context.Background()
	queries := sqlc.New(pool)
	regID := getTestRegistryID(t, pool, "test-registry")

	// First sync with 3 servers
	registry := createTestUpstreamRegistry([]upstreamv0.ServerJSON{
		createTestServer("test.org/server-a", "1.0.0"),
		createTestServer("test.org/server-b", "1.0.0"),
		createTestServer("test.org/server-c", "1.0.0"),
	})

	err = writer.Store(ctx, "test-registry", registry)
	require.NoError(t, err)

	// Verify all 3 servers exist
	servers, err := queries.ListServers(ctx, sqlc.ListServersParams{RegistryID: regID, Size: 100})
	require.NoError(t, err)
	require.Len(t, servers, 3, "Should have 3 servers after first sync")

	// Record UUID for server-a to verify it persists
	serverARows, err := queries.GetServerVersion(ctx, sqlc.GetServerVersionParams{
		RegistryID: regID,
		Name:       "test.org/server-a",
		Version:    "1.0.0",
		Size:       100,
	})
	require.NoError(t, err)
	require.NotEmpty(t, serverARows)
	serverA := serverARows[0]
	originalUUIDA := serverA.ID

	// Second sync with only 2 servers (server-b removed)
	registryUpdated := createTestUpstreamRegistry([]upstreamv0.ServerJSON{
		createTestServer("test.org/server-a", "1.0.0"),
		createTestServer("test.org/server-c", "1.0.0"),
	})

	err = writer.Store(ctx, "test-registry", registryUpdated)
	require.NoError(t, err)

	// Verify only server-a and server-c exist
	servers, err = queries.ListServers(ctx, sqlc.ListServersParams{RegistryID: regID, Size: 100})
	require.NoError(t, err)
	require.Len(t, servers, 2, "Should have 2 servers after second sync")

	// Verify server-a still exists with same UUID
	serverAUpdatedRows, err := queries.GetServerVersion(ctx, sqlc.GetServerVersionParams{
		RegistryID: regID,
		Name:       "test.org/server-a",
		Version:    "1.0.0",
		Size:       100,
	})
	require.NoError(t, err)
	require.NotEmpty(t, serverAUpdatedRows)
	serverAUpdated := serverAUpdatedRows[0]
	assert.Equal(t, originalUUIDA, serverAUpdated.ID, "Server A UUID should be preserved")

	// Verify server-c exists
	discardRows, err := queries.GetServerVersion(ctx, sqlc.GetServerVersionParams{
		RegistryID: regID,
		Name:       "test.org/server-c",
		Version:    "1.0.0",
		Size:       100,
	})
	require.NoError(t, err)
	require.NotEmpty(t, discardRows)

	// Verify server-b was deleted (should return error)
	discardRows, err = queries.GetServerVersion(ctx, sqlc.GetServerVersionParams{
		RegistryID: regID,
		Name:       "test.org/server-b",
		Version:    "1.0.0",
		Size:       100,
	})
	require.NoError(t, err)
	require.Empty(t, discardRows, "Server B should have been deleted")
}

// TestDbSyncWriter_Store_PackageCleanup verifies that when a package is changed or removed
// from a server, it is properly updated.
// Note: The database schema only allows ONE package per server version (server_id is PRIMARY KEY).
func TestDbSyncWriter_Store_PackageCleanup(t *testing.T) {
	t.Parallel()

	pool, cleanup := setupTestDB(t)
	defer cleanup()

	createTestRegistry(t, pool, "test-registry")

	writer, err := NewDBSyncWriter(pool, testMaxMetaSize)
	require.NoError(t, err)

	ctx := context.Background()
	queries := sqlc.New(pool)
	regID := getTestRegistryID(t, pool, "test-registry")

	// Create server with a package
	server := createTestServer("test.org/server", "1.0.0")
	server.Packages = []model.Package{
		{
			RegistryType:    "npm",
			RegistryBaseURL: "https://registry.npmjs.org",
			Identifier:      "@test/package-1",
			Version:         "1.0.0",
			Transport:       model.Transport{Type: "stdio"},
		},
	}
	registry := createTestUpstreamRegistry([]upstreamv0.ServerJSON{server})

	// First sync
	err = writer.Store(ctx, "test-registry", registry)
	require.NoError(t, err)

	// Verify package exists
	servers, err := queries.ListServers(ctx, sqlc.ListServersParams{RegistryID: regID, Size: 100})
	require.NoError(t, err)
	require.Len(t, servers, 1)

	packages, err := queries.ListServerPackages(ctx, []uuid.UUID{servers[0].ID})
	require.NoError(t, err)
	require.Len(t, packages, 1, "Should have 1 package after first sync")
	assert.Equal(t, "@test/package-1", packages[0].PkgIdentifier)

	originalUUID := servers[0].ID

	// Second sync with different package (package changed)
	serverUpdated := createTestServer("test.org/server", "1.0.0")
	serverUpdated.Packages = []model.Package{
		{
			RegistryType:    "pypi",
			RegistryBaseURL: "https://pypi.org/simple",
			Identifier:      "new-test-package",
			Version:         "2.0.0",
			Transport:       model.Transport{Type: "stdio"},
		},
	}
	registryUpdated := createTestUpstreamRegistry([]upstreamv0.ServerJSON{serverUpdated})

	err = writer.Store(ctx, "test-registry", registryUpdated)
	require.NoError(t, err)

	// Verify server UUID is preserved
	serverAfterUpdateRows, err := queries.GetServerVersion(ctx, sqlc.GetServerVersionParams{
		RegistryID: regID,
		Name:       "test.org/server",
		Version:    "1.0.0",
		Size:       100,
	})
	require.NoError(t, err)
	require.NotEmpty(t, serverAfterUpdateRows)
	serverAfterUpdate := serverAfterUpdateRows[0]
	assert.Equal(t, originalUUID, serverAfterUpdate.ID, "Server UUID should be preserved")

	// Verify package was replaced with new package
	packages, err = queries.ListServerPackages(ctx, []uuid.UUID{originalUUID})
	require.NoError(t, err)
	require.Len(t, packages, 1, "Should have 1 package after second sync")
	assert.Equal(t, "new-test-package", packages[0].PkgIdentifier, "Package identifier should be updated")
	assert.Equal(t, "pypi", packages[0].RegistryType, "Package registry type should be updated")

	// Third sync with no packages (package removed)
	serverNoPackages := createTestServer("test.org/server", "1.0.0")
	serverNoPackages.Packages = nil
	registryNoPackages := createTestUpstreamRegistry([]upstreamv0.ServerJSON{serverNoPackages})

	err = writer.Store(ctx, "test-registry", registryNoPackages)
	require.NoError(t, err)

	// Verify server UUID is still preserved
	serverAfterRemovalRows, err := queries.GetServerVersion(ctx, sqlc.GetServerVersionParams{
		RegistryID: regID,
		Name:       "test.org/server",
		Version:    "1.0.0",
		Size:       100,
	})
	require.NoError(t, err)
	require.NotEmpty(t, serverAfterRemovalRows)
	serverAfterRemoval := serverAfterRemovalRows[0]
	assert.Equal(t, originalUUID, serverAfterRemoval.ID, "Server UUID should be preserved after package removal")

	// Verify no packages exist
	packages, err = queries.ListServerPackages(ctx, []uuid.UUID{originalUUID})
	require.NoError(t, err)
	require.Len(t, packages, 0, "Should have 0 packages after third sync")
}

// TestDbSyncWriter_Store_RemoteCleanup verifies that when remotes are removed from a server,
// they are cleaned up.
func TestDbSyncWriter_Store_RemoteCleanup(t *testing.T) {
	t.Parallel()

	pool, cleanup := setupTestDB(t)
	defer cleanup()

	createTestRegistry(t, pool, "test-registry")

	writer, err := NewDBSyncWriter(pool, testMaxMetaSize)
	require.NoError(t, err)

	ctx := context.Background()
	queries := sqlc.New(pool)
	regID := getTestRegistryID(t, pool, "test-registry")

	// Create server with 2 remotes
	server := createTestServer("test.org/server", "1.0.0")
	server.Remotes = []model.Transport{
		{
			Type: "sse",
			URL:  "https://example.com/sse1",
		},
		{
			Type: "sse",
			URL:  "https://example.com/sse2",
		},
	}
	registry := createTestUpstreamRegistry([]upstreamv0.ServerJSON{server})

	// First sync
	err = writer.Store(ctx, "test-registry", registry)
	require.NoError(t, err)

	// Verify 2 remotes exist
	servers, err := queries.ListServers(ctx, sqlc.ListServersParams{RegistryID: regID, Size: 100})
	require.NoError(t, err)
	require.Len(t, servers, 1)

	remotes, err := queries.ListServerRemotes(ctx, []uuid.UUID{servers[0].ID})
	require.NoError(t, err)
	require.Len(t, remotes, 2, "Should have 2 remotes after first sync")

	originalUUID := servers[0].ID

	// Second sync with only 1 remote
	serverUpdated := createTestServer("test.org/server", "1.0.0")
	serverUpdated.Remotes = []model.Transport{
		{
			Type: "sse",
			URL:  "https://example.com/sse1",
		},
	}
	registryUpdated := createTestUpstreamRegistry([]upstreamv0.ServerJSON{serverUpdated})

	err = writer.Store(ctx, "test-registry", registryUpdated)
	require.NoError(t, err)

	// Verify server UUID is preserved
	serverAfterUpdateRows, err := queries.GetServerVersion(ctx, sqlc.GetServerVersionParams{
		RegistryID: regID,
		Name:       "test.org/server",
		Version:    "1.0.0",
		Size:       100,
	})
	require.NoError(t, err)
	require.NotEmpty(t, serverAfterUpdateRows)
	serverAfterUpdate := serverAfterUpdateRows[0]
	assert.Equal(t, originalUUID, serverAfterUpdate.ID, "Server UUID should be preserved")

	// Verify only 1 remote exists
	remotes, err = queries.ListServerRemotes(ctx, []uuid.UUID{originalUUID})
	require.NoError(t, err)
	require.Len(t, remotes, 1, "Should have 1 remote after second sync")
	assert.Equal(t, "https://example.com/sse1", remotes[0].TransportUrl)
}

// TestDbSyncWriter_Store_IconCleanup verifies that when icons are removed from a server,
// they are cleaned up.
func TestDbSyncWriter_Store_IconCleanup(t *testing.T) {
	t.Parallel()

	pool, cleanup := setupTestDB(t)
	defer cleanup()

	createTestRegistry(t, pool, "test-registry")

	writer, err := NewDBSyncWriter(pool, testMaxMetaSize)
	require.NoError(t, err)

	ctx := context.Background()
	queries := sqlc.New(pool)
	regID := getTestRegistryID(t, pool, "test-registry")

	// Create server with 2 icons
	lightTheme := testThemeLight
	darkTheme := testThemeDark
	mimeType := testMimeTypePNG

	server := createTestServer("test.org/server", "1.0.0")
	server.Icons = []model.Icon{
		{
			Src:      "https://example.com/icon-light.png",
			MimeType: &mimeType,
			Theme:    &lightTheme,
		},
		{
			Src:      "https://example.com/icon-dark.png",
			MimeType: &mimeType,
			Theme:    &darkTheme,
		},
	}
	registry := createTestUpstreamRegistry([]upstreamv0.ServerJSON{server})

	// First sync
	err = writer.Store(ctx, "test-registry", registry)
	require.NoError(t, err)

	// Verify server created
	servers, err := queries.ListServers(ctx, sqlc.ListServersParams{RegistryID: regID, Size: 100})
	require.NoError(t, err)
	require.Len(t, servers, 1)

	originalUUID := servers[0].ID

	// Count icons using raw query
	var iconCount int
	err = pool.QueryRow(ctx, "SELECT COUNT(*) FROM mcp_server_icon WHERE server_id = $1", originalUUID).Scan(&iconCount)
	require.NoError(t, err)
	require.Equal(t, 2, iconCount, "Should have 2 icons after first sync")

	// Second sync with only 1 icon
	serverUpdated := createTestServer("test.org/server", "1.0.0")
	serverUpdated.Icons = []model.Icon{
		{
			Src:      "https://example.com/icon-light.png",
			MimeType: &mimeType,
			Theme:    &lightTheme,
		},
	}
	registryUpdated := createTestUpstreamRegistry([]upstreamv0.ServerJSON{serverUpdated})

	err = writer.Store(ctx, "test-registry", registryUpdated)
	require.NoError(t, err)

	// Verify server UUID is preserved
	serverAfterUpdateRows, err := queries.GetServerVersion(ctx, sqlc.GetServerVersionParams{
		RegistryID: regID,
		Name:       "test.org/server",
		Version:    "1.0.0",
		Size:       100,
	})
	require.NoError(t, err)
	require.NotEmpty(t, serverAfterUpdateRows)
	serverAfterUpdate := serverAfterUpdateRows[0]
	assert.Equal(t, originalUUID, serverAfterUpdate.ID, "Server UUID should be preserved")

	// Verify only 1 icon exists
	err = pool.QueryRow(ctx, "SELECT COUNT(*) FROM mcp_server_icon WHERE server_id = $1", originalUUID).Scan(&iconCount)
	require.NoError(t, err)
	require.Equal(t, 1, iconCount, "Should have 1 icon after second sync")
}

// TestDbSyncWriter_Store_RegistryIsolation verifies that orphan cleanup for one registry
// does NOT affect servers from other registries.
func TestDbSyncWriter_Store_RegistryIsolation(t *testing.T) {
	t.Parallel()

	pool, cleanup := setupTestDB(t)
	defer cleanup()

	// Step 1: Create two test registries
	idsA := createTestRegistry(t, pool, "registry-A")
	idsB := createTestRegistry(t, pool, "registry-B")
	registryAID := idsA.sourceID
	registryBID := idsB.sourceID
	regIDA := idsA.registryID
	regIDB := idsB.registryID

	writer, err := NewDBSyncWriter(pool, testMaxMetaSize)
	require.NoError(t, err)

	ctx := context.Background()
	queries := sqlc.New(pool)

	// Step 2-3: Create UpstreamRegistry for registry-A with 2 servers
	registryA := createTestUpstreamRegistry([]upstreamv0.ServerJSON{
		createTestServer("test.org/server-1", "1.0.0"),
		createTestServer("test.org/server-2", "1.0.0"),
	})

	// Create UpstreamRegistry for registry-B with 2 servers
	registryB := createTestUpstreamRegistry([]upstreamv0.ServerJSON{
		createTestServer("test.org/server-3", "1.0.0"),
		createTestServer("test.org/server-4", "1.0.0"),
	})

	// Step 4-5: Call Store() for both registries
	err = writer.Store(ctx, "registry-A", registryA)
	require.NoError(t, err)

	err = writer.Store(ctx, "registry-B", registryB)
	require.NoError(t, err)

	// Step 6: Query DB and verify all 4 servers exist (2 per registry)
	serversA, err := queries.ListServers(ctx, sqlc.ListServersParams{RegistryID: regIDA, Size: 100})
	require.NoError(t, err)
	serversB, err := queries.ListServers(ctx, sqlc.ListServersParams{RegistryID: regIDB, Size: 100})
	require.NoError(t, err)
	require.Equal(t, 4, len(serversA)+len(serversB), "Should have 4 servers total after initial sync")

	// Count servers per registry
	var countA, countB int
	err = pool.QueryRow(ctx, testQuery, registryAID).Scan(&countA)
	require.NoError(t, err)
	assert.Equal(t, 2, countA, "Registry A should have 2 servers")

	err = pool.QueryRow(ctx, testQuery, registryBID).Scan(&countB)
	require.NoError(t, err)
	assert.Equal(t, 2, countB, "Registry B should have 2 servers")

	// Step 7: Record server UUIDs for all 4 servers
	server1Rows, err := queries.GetServerVersion(ctx, sqlc.GetServerVersionParams{
		RegistryID: regIDA,
		Name:       "test.org/server-1",
		Version:    "1.0.0",
		Size:       100,
	})
	require.NoError(t, err)
	require.NotEmpty(t, server1Rows)
	server1 := server1Rows[0]
	uuidServer1 := server1.ID

	server2Rows, err := queries.GetServerVersion(ctx, sqlc.GetServerVersionParams{
		RegistryID: regIDA,
		Name:       "test.org/server-2",
		Version:    "1.0.0",
		Size:       100,
	})
	require.NoError(t, err)
	require.NotEmpty(t, server2Rows)
	server2 := server2Rows[0]
	uuidServer2 := server2.ID

	server3Rows, err := queries.GetServerVersion(ctx, sqlc.GetServerVersionParams{
		RegistryID: regIDB,
		Name:       "test.org/server-3",
		Version:    "1.0.0",
		Size:       100,
	})
	require.NoError(t, err)
	require.NotEmpty(t, server3Rows)
	server3 := server3Rows[0]
	uuidServer3 := server3.ID

	server4Rows, err := queries.GetServerVersion(ctx, sqlc.GetServerVersionParams{
		RegistryID: regIDB,
		Name:       "test.org/server-4",
		Version:    "1.0.0",
		Size:       100,
	})
	require.NoError(t, err)
	require.NotEmpty(t, server4Rows)
	server4 := server4Rows[0]
	uuidServer4 := server4.ID

	// Step 8: Create new UpstreamRegistry for registry-A with only 1 server (server-2 removed)
	registryAUpdated := createTestUpstreamRegistry([]upstreamv0.ServerJSON{
		createTestServer("test.org/server-1", "1.0.0"),
	})

	// Step 9: Call Store() for registry-A with the new data
	err = writer.Store(ctx, "registry-A", registryAUpdated)
	require.NoError(t, err)

	// Step 10: Query DB and verify registry isolation
	// 10a: registry-A: only "server-1" exists (server-2 deleted)
	err = pool.QueryRow(ctx, testQuery, registryAID).Scan(&countA)
	require.NoError(t, err)
	assert.Equal(t, 1, countA, "Registry A should have 1 server after update")

	// Verify server-1 still exists with same UUID
	server1AfterRows, err := queries.GetServerVersion(ctx, sqlc.GetServerVersionParams{
		RegistryID: regIDA,
		Name:       "test.org/server-1",
		Version:    "1.0.0",
		Size:       100,
	})
	require.NoError(t, err)
	require.NotEmpty(t, server1AfterRows)
	server1After := server1AfterRows[0]
	assert.Equal(t, uuidServer1, server1After.ID, "Server 1 UUID should be preserved")

	// Verify server-2 was deleted
	discardRows, err := queries.GetServerVersion(ctx, sqlc.GetServerVersionParams{
		RegistryID: regIDA,
		Name:       "test.org/server-2",
		Version:    "1.0.0",
		Size:       100,
	})
	require.NoError(t, err)
	require.Empty(t, discardRows, "Server 2 should have been deleted from registry A")
	_ = uuidServer2 // Server 2 was deleted, UUID no longer in DB

	// 10b: registry-B: both "server-3" and "server-4" still exist (unchanged!)
	err = pool.QueryRow(ctx, testQuery, registryBID).Scan(&countB)
	require.NoError(t, err)
	assert.Equal(t, 2, countB, "Registry B should still have 2 servers (unaffected by registry A changes)")

	// Verify server-3 still exists with same UUID
	server3AfterRows, err := queries.GetServerVersion(ctx, sqlc.GetServerVersionParams{
		RegistryID: regIDB,
		Name:       "test.org/server-3",
		Version:    "1.0.0",
		Size:       100,
	})
	require.NoError(t, err)
	require.NotEmpty(t, server3AfterRows)
	server3After := server3AfterRows[0]
	assert.Equal(t, uuidServer3, server3After.ID, "Server 3 UUID should be preserved (registry B unaffected)")

	// Verify server-4 still exists with same UUID
	server4AfterRows, err := queries.GetServerVersion(ctx, sqlc.GetServerVersionParams{
		RegistryID: regIDB,
		Name:       "test.org/server-4",
		Version:    "1.0.0",
		Size:       100,
	})
	require.NoError(t, err)
	require.NotEmpty(t, server4AfterRows)
	server4After := server4AfterRows[0]
	assert.Equal(t, uuidServer4, server4After.ID, "Server 4 UUID should be preserved (registry B unaffected)")

	// 10c: Verify total server count is 3
	serversA, err = queries.ListServers(ctx, sqlc.ListServersParams{RegistryID: regIDA, Size: 100})
	require.NoError(t, err)
	serversB, err = queries.ListServers(ctx, sqlc.ListServersParams{RegistryID: regIDB, Size: 100})
	require.NoError(t, err)
	require.Equal(t, 3, len(serversA)+len(serversB), "Should have 3 servers total (1 in registry A, 2 in registry B)")

	// Step 11: Do the reverse - remove a server from registry-B and verify registry-A is unaffected
	registryBUpdated := createTestUpstreamRegistry([]upstreamv0.ServerJSON{
		createTestServer("test.org/server-3", "1.0.0"),
		// server-4 removed
	})

	err = writer.Store(ctx, "registry-B", registryBUpdated)
	require.NoError(t, err)

	// Verify registry-A is unaffected
	err = pool.QueryRow(ctx, testQuery, registryAID).Scan(&countA)
	require.NoError(t, err)
	assert.Equal(t, 1, countA, "Registry A should still have 1 server (unaffected by registry B changes)")

	// Verify server-1 in registry-A still has same UUID
	server1FinalRows, err := queries.GetServerVersion(ctx, sqlc.GetServerVersionParams{
		RegistryID: regIDA,
		Name:       "test.org/server-1",
		Version:    "1.0.0",
		Size:       100,
	})
	require.NoError(t, err)
	require.NotEmpty(t, server1FinalRows)
	server1Final := server1FinalRows[0]
	assert.Equal(t, uuidServer1, server1Final.ID, "Server 1 UUID should still be preserved")

	// Verify registry-B now has 1 server
	err = pool.QueryRow(ctx, testQuery, registryBID).Scan(&countB)
	require.NoError(t, err)
	assert.Equal(t, 1, countB, "Registry B should have 1 server after update")

	// Verify server-3 still exists with same UUID
	server3FinalRows, err := queries.GetServerVersion(ctx, sqlc.GetServerVersionParams{
		RegistryID: regIDB,
		Name:       "test.org/server-3",
		Version:    "1.0.0",
		Size:       100,
	})
	require.NoError(t, err)
	require.NotEmpty(t, server3FinalRows)
	server3Final := server3FinalRows[0]
	assert.Equal(t, uuidServer3, server3Final.ID, "Server 3 UUID should be preserved")

	// Verify server-4 was deleted
	discardRows, err = queries.GetServerVersion(ctx, sqlc.GetServerVersionParams{
		RegistryID: regIDB,
		Name:       "test.org/server-4",
		Version:    "1.0.0",
		Size:       100,
	})
	require.NoError(t, err)
	require.Empty(t, discardRows, "Server 4 should have been deleted from registry B")

	// Final verification: total server count is 2
	serversA, err = queries.ListServers(ctx, sqlc.ListServersParams{RegistryID: regIDA, Size: 100})
	require.NoError(t, err)
	serversB, err = queries.ListServers(ctx, sqlc.ListServersParams{RegistryID: regIDB, Size: 100})
	require.NoError(t, err)
	require.Equal(t, 2, len(serversA)+len(serversB), "Should have 2 servers total (1 in each registry)")
}

// TestDbSyncWriter_Store_ServerWithMultiplePackages tests that a server can have multiple packages.
func TestDbSyncWriter_Store_ServerWithMultiplePackages(t *testing.T) {
	t.Parallel()

	pool, cleanup := setupTestDB(t)
	defer cleanup()

	createTestRegistry(t, pool, "test-registry")

	writer, err := NewDBSyncWriter(pool, testMaxMetaSize)
	require.NoError(t, err)

	ctx := context.Background()
	queries := sqlc.New(pool)
	regID := getTestRegistryID(t, pool, "test-registry")

	// Create a server with 3 different packages (different registry types and identifiers)
	server := createTestServer("test.org/server", "1.0.0")
	server.Packages = []model.Package{
		{
			RegistryType:    "npm",
			RegistryBaseURL: "https://registry.npmjs.org",
			Identifier:      "@test/npm-package",
			Version:         "1.0.0",
			Transport:       model.Transport{Type: "stdio"},
		},
		{
			RegistryType:    "pypi",
			RegistryBaseURL: "https://pypi.org/simple",
			Identifier:      "test-pypi-package",
			Version:         "2.0.0",
			Transport:       model.Transport{Type: "stdio"},
		},
		{
			RegistryType:    "oci",
			RegistryBaseURL: "",
			Identifier:      "ghcr.io/test/oci-package:v1.0.0",
			Version:         "",
			Transport:       model.Transport{Type: "stdio"},
		},
		{
			RegistryType:    "pypi",
			RegistryBaseURL: "fubar",
			Identifier:      "test-package",
			Version:         "2.0.0",
			Transport:       model.Transport{Type: "stdio"},
		},
		{
			RegistryType:    "npm",
			RegistryBaseURL: "fubar",
			Identifier:      "test-package",
			Version:         "2.0.0",
			Transport:       model.Transport{Type: "stdio"},
		},
	}

	registry := createTestUpstreamRegistry([]upstreamv0.ServerJSON{server})

	// First sync
	err = writer.Store(ctx, "test-registry", registry)
	require.NoError(t, err)

	// Verify all 3 packages exist
	servers, err := queries.ListServers(ctx, sqlc.ListServersParams{RegistryID: regID, Size: 100})
	require.NoError(t, err)
	require.Len(t, servers, 1)

	packages, err := queries.ListServerPackages(ctx, []uuid.UUID{servers[0].ID})
	require.NoError(t, err)
	require.Len(t, packages, 5, "Server should have 5 packages")

	// Verify each package
	pkgIdentifiers := make(map[string]bool)
	for _, pkg := range packages {
		pkgIdentifiers[pkg.PkgIdentifier] = true
	}
	assert.True(t, pkgIdentifiers["@test/npm-package"], "NPM package should exist")
	assert.True(t, pkgIdentifiers["test-pypi-package"], "PyPI package should exist")
	assert.True(t, pkgIdentifiers["ghcr.io/test/oci-package:v1.0.0"], "OCI package should exist")
}

// TestDbSyncWriter_Store_MultiplePackagesOrphanedCleanup tests that when packages are added/removed,
// orphaned packages are properly deleted.
func TestDbSyncWriter_Store_MultiplePackagesOrphanedCleanup(t *testing.T) {
	t.Parallel()

	pool, cleanup := setupTestDB(t)
	defer cleanup()

	createTestRegistry(t, pool, "test-registry")

	writer, err := NewDBSyncWriter(pool, testMaxMetaSize)
	require.NoError(t, err)

	ctx := context.Background()
	queries := sqlc.New(pool)
	regID := getTestRegistryID(t, pool, "test-registry")

	// First sync: server with 3 packages
	server := createTestServer("test.org/server", "1.0.0")
	server.Packages = []model.Package{
		{
			RegistryType:    "npm",
			RegistryBaseURL: "https://registry.npmjs.org",
			Identifier:      "@test/package-1",
			Version:         "1.0.0",
			Transport:       model.Transport{Type: "stdio"},
		},
		{
			RegistryType:    "npm",
			RegistryBaseURL: "https://registry.npmjs.org",
			Identifier:      "@test/package-2",
			Version:         "1.0.0",
			Transport:       model.Transport{Type: "stdio"},
		},
		{
			RegistryType:    "pypi",
			RegistryBaseURL: "https://pypi.org/simple",
			Identifier:      "test-package-3",
			Version:         "1.0.0",
			Transport:       model.Transport{Type: "stdio"},
		},
	}
	registry := createTestUpstreamRegistry([]upstreamv0.ServerJSON{server})

	err = writer.Store(ctx, "test-registry", registry)
	require.NoError(t, err)

	// Verify all 3 packages exist
	servers, err := queries.ListServers(ctx, sqlc.ListServersParams{RegistryID: regID, Size: 100})
	require.NoError(t, err)
	require.Len(t, servers, 1)
	serverID := servers[0].ID

	packages, err := queries.ListServerPackages(ctx, []uuid.UUID{serverID})
	require.NoError(t, err)
	require.Len(t, packages, 3, "Should have 3 packages after first sync")

	// Second sync: Drop package-2, keep package-1 and package-3, add package-4
	serverUpdated := createTestServer("test.org/server", "1.0.0")
	serverUpdated.Packages = []model.Package{
		{
			RegistryType:    "npm",
			RegistryBaseURL: "https://registry.npmjs.org",
			Identifier:      "@test/package-1",
			Version:         "1.0.0",
			Transport:       model.Transport{Type: "stdio"},
		},
		{
			RegistryType:    "pypi",
			RegistryBaseURL: "https://pypi.org/simple",
			Identifier:      "test-package-3",
			Version:         "1.0.0",
			Transport:       model.Transport{Type: "stdio"},
		},
		{
			RegistryType:    "npm",
			RegistryBaseURL: "https://registry.npmjs.org",
			Identifier:      "@test/package-4",
			Version:         "1.0.0",
			Transport:       model.Transport{Type: "stdio"},
		},
	}
	registryUpdated := createTestUpstreamRegistry([]upstreamv0.ServerJSON{serverUpdated})

	err = writer.Store(ctx, "test-registry", registryUpdated)
	require.NoError(t, err)

	// Verify server UUID is preserved
	serverAfterUpdateRows, err := queries.GetServerVersion(ctx, sqlc.GetServerVersionParams{
		RegistryID: regID,
		Name:       "test.org/server",
		Version:    "1.0.0",
		Size:       100,
	})
	require.NoError(t, err)
	require.NotEmpty(t, serverAfterUpdateRows)
	serverAfterUpdate := serverAfterUpdateRows[0]
	assert.Equal(t, serverID, serverAfterUpdate.ID, "Server UUID should be preserved")

	// Verify only 3 packages exist (package-1, package-3, package-4)
	packages, err = queries.ListServerPackages(ctx, []uuid.UUID{serverID})
	require.NoError(t, err)
	require.Len(t, packages, 3, "Should have 3 packages after second sync")

	// Verify package identifiers
	pkgIdentifiers := make(map[string]bool)
	for _, pkg := range packages {
		pkgIdentifiers[pkg.PkgIdentifier] = true
	}
	assert.True(t, pkgIdentifiers["@test/package-1"], "Package-1 should exist")
	assert.False(t, pkgIdentifiers["@test/package-2"], "Package-2 should have been deleted")
	assert.True(t, pkgIdentifiers["test-package-3"], "Package-3 should exist")
	assert.True(t, pkgIdentifiers["@test/package-4"], "Package-4 should exist")

	// Third sync: Remove all packages
	serverNoPackages := createTestServer("test.org/server", "1.0.0")
	serverNoPackages.Packages = nil
	registryNoPackages := createTestUpstreamRegistry([]upstreamv0.ServerJSON{serverNoPackages})

	err = writer.Store(ctx, "test-registry", registryNoPackages)
	require.NoError(t, err)

	// Verify no packages exist
	packages, err = queries.ListServerPackages(ctx, []uuid.UUID{serverID})
	require.NoError(t, err)
	require.Len(t, packages, 0, "Should have 0 packages after third sync")
}

// TestDbSyncWriter_Store_PackageUpdate tests that when a package is updated (same identifier but different attributes),
// it is properly updated in place.
func TestDbSyncWriter_Store_PackageUpdate(t *testing.T) {
	t.Parallel()

	pool, cleanup := setupTestDB(t)
	defer cleanup()

	createTestRegistry(t, pool, "test-registry")

	writer, err := NewDBSyncWriter(pool, testMaxMetaSize)
	require.NoError(t, err)

	ctx := context.Background()
	queries := sqlc.New(pool)
	regID := getTestRegistryID(t, pool, "test-registry")

	// First sync: server with 1 package
	server := createTestServer("test.org/server", "1.0.0")
	server.Packages = []model.Package{
		{
			RegistryType:    "npm",
			RegistryBaseURL: "https://registry.npmjs.org",
			Identifier:      "@test/my-package",
			Version:         "1.0.0",
			RunTimeHint:     "npx",
			Transport:       model.Transport{Type: "stdio"},
		},
	}
	registry := createTestUpstreamRegistry([]upstreamv0.ServerJSON{server})

	err = writer.Store(ctx, "test-registry", registry)
	require.NoError(t, err)

	// Verify package exists with version 1.0.0
	servers, err := queries.ListServers(ctx, sqlc.ListServersParams{RegistryID: regID, Size: 100})
	require.NoError(t, err)
	require.Len(t, servers, 1)
	serverID := servers[0].ID

	packages, err := queries.ListServerPackages(ctx, []uuid.UUID{serverID})
	require.NoError(t, err)
	require.Len(t, packages, 1)
	assert.Equal(t, "@test/my-package", packages[0].PkgIdentifier)
	assert.Equal(t, "1.0.0", packages[0].PkgVersion)
	assert.Equal(t, "npx", *packages[0].RuntimeHint)

	// Second sync: Update the package version and runtime hint
	serverUpdated := createTestServer("test.org/server", "1.0.0")
	serverUpdated.Packages = []model.Package{
		{
			RegistryType:    "npm",
			RegistryBaseURL: "https://registry.npmjs.org",
			Identifier:      "@test/my-package",
			Version:         "2.0.0", // Updated version
			RunTimeHint:     "node",  // Updated hint
			Transport:       model.Transport{Type: "stdio"},
		},
	}
	registryUpdated := createTestUpstreamRegistry([]upstreamv0.ServerJSON{serverUpdated})

	err = writer.Store(ctx, "test-registry", registryUpdated)
	require.NoError(t, err)

	// Verify package was updated in place
	packages, err = queries.ListServerPackages(ctx, []uuid.UUID{serverID})
	require.NoError(t, err)
	require.Len(t, packages, 1, "Should still have 1 package")
	assert.Equal(t, "@test/my-package", packages[0].PkgIdentifier)
	assert.Equal(t, "2.0.0", packages[0].PkgVersion, "Package version should be updated")
	assert.Equal(t, "node", *packages[0].RuntimeHint, "Runtime hint should be updated")
}

// TestDbSyncWriter_Store_ComplexSyncScenario tests a realistic scenario with multiple changes at once.
func TestDbSyncWriter_Store_ComplexSyncScenario(t *testing.T) {
	t.Parallel()

	pool, cleanup := setupTestDB(t)
	defer cleanup()

	createTestRegistry(t, pool, "test-registry")

	writer, err := NewDBSyncWriter(pool, testMaxMetaSize)
	require.NoError(t, err)

	ctx := context.Background()
	queries := sqlc.New(pool)
	regID := getTestRegistryID(t, pool, "test-registry")

	// First sync: 3 servers (A v1.0, B v1.0, C v1.0) each with packages/remotes
	serverA := createTestServer("test.org/server-a", "1.0.0")
	serverA.Description = "Server A original"
	serverA.Packages = []model.Package{
		{
			RegistryType:    "npm",
			RegistryBaseURL: "https://registry.npmjs.org",
			Identifier:      "@test/a-package",
			Version:         "1.0.0",
			Transport:       model.Transport{Type: "stdio"},
		},
	}
	serverA.Remotes = []model.Transport{
		{
			Type: "sse",
			URL:  "https://example.com/a-sse",
		},
	}

	serverB := createTestServer("test.org/server-b", "1.0.0")
	serverB.Description = "Server B (will be removed)"
	serverB.Packages = []model.Package{
		{
			RegistryType:    "npm",
			RegistryBaseURL: "https://registry.npmjs.org",
			Identifier:      "@test/b-package",
			Version:         "1.0.0",
			Transport:       model.Transport{Type: "stdio"},
		},
	}

	serverC := createTestServer("test.org/server-c", "1.0.0")
	serverC.Description = "Server C unchanged"

	registry := createTestUpstreamRegistry([]upstreamv0.ServerJSON{serverA, serverB, serverC})

	err = writer.Store(ctx, "test-registry", registry)
	require.NoError(t, err)

	// Record all UUIDs
	serverAV1Rows, err := queries.GetServerVersion(ctx, sqlc.GetServerVersionParams{
		RegistryID: regID,
		Name:       "test.org/server-a",
		Version:    "1.0.0",
		Size:       100,
	})
	require.NoError(t, err)
	require.NotEmpty(t, serverAV1Rows)
	serverAV1 := serverAV1Rows[0]
	uuidA := serverAV1.ID

	serverBV1Rows, err := queries.GetServerVersion(ctx, sqlc.GetServerVersionParams{
		RegistryID: regID,
		Name:       "test.org/server-b",
		Version:    "1.0.0",
		Size:       100,
	})
	require.NoError(t, err)
	require.NotEmpty(t, serverBV1Rows)
	serverBV1 := serverBV1Rows[0]
	_ = serverBV1.ID // Server B will be deleted

	serverCV1Rows, err := queries.GetServerVersion(ctx, sqlc.GetServerVersionParams{
		RegistryID: regID,
		Name:       "test.org/server-c",
		Version:    "1.0.0",
		Size:       100,
	})
	require.NoError(t, err)
	require.NotEmpty(t, serverCV1Rows)
	serverCV1 := serverCV1Rows[0]
	uuidC := serverCV1.ID

	// Second sync:
	// - Server A v1.0: updated description
	// - Server B v1.0: removed
	// - Server C v1.0: no changes
	// - Server D v1.0: new server added
	serverAUpdated := createTestServer("test.org/server-a", "1.0.0")
	serverAUpdated.Description = "Server A updated description"
	serverAUpdated.Packages = []model.Package{
		{
			RegistryType:    "npm",
			RegistryBaseURL: "https://registry.npmjs.org",
			Identifier:      "@test/a-package",
			Version:         "1.0.0",
			Transport:       model.Transport{Type: "stdio"},
		},
	}
	serverAUpdated.Remotes = []model.Transport{
		{
			Type: "sse",
			URL:  "https://example.com/a-sse",
		},
	}

	serverCUnchanged := createTestServer("test.org/server-c", "1.0.0")
	serverCUnchanged.Description = "Server C unchanged"

	serverD := createTestServer("test.org/server-d", "1.0.0")
	serverD.Description = "Server D new"

	registryUpdated := createTestUpstreamRegistry([]upstreamv0.ServerJSON{serverAUpdated, serverCUnchanged, serverD})

	err = writer.Store(ctx, "test-registry", registryUpdated)
	require.NoError(t, err)

	// Verify results
	// Server A: same UUID, updated description
	serverAV2Rows, err := queries.GetServerVersion(ctx, sqlc.GetServerVersionParams{
		RegistryID: regID,
		Name:       "test.org/server-a",
		Version:    "1.0.0",
		Size:       100,
	})
	require.NoError(t, err)
	require.NotEmpty(t, serverAV2Rows)
	serverAV2 := serverAV2Rows[0]
	assert.Equal(t, uuidA, serverAV2.ID, "Server A UUID should be preserved")
	require.NotNil(t, serverAV2.Description)
	assert.Equal(t, "Server A updated description", *serverAV2.Description)

	// Verify Server A still has its package and remote
	packagesA, err := queries.ListServerPackages(ctx, []uuid.UUID{uuidA})
	require.NoError(t, err)
	require.Len(t, packagesA, 1, "Server A should still have 1 package")

	remotesA, err := queries.ListServerRemotes(ctx, []uuid.UUID{uuidA})
	require.NoError(t, err)
	require.Len(t, remotesA, 1, "Server A should still have 1 remote")

	// Server B: deleted
	discardRows, err := queries.GetServerVersion(ctx, sqlc.GetServerVersionParams{
		RegistryID: regID,
		Name:       "test.org/server-b",
		Version:    "1.0.0",
		Size:       100,
	})
	require.NoError(t, err)
	require.Empty(t, discardRows, "Server B should have been deleted")

	// Server C: same UUID, no changes
	serverCV2Rows, err := queries.GetServerVersion(ctx, sqlc.GetServerVersionParams{
		RegistryID: regID,
		Name:       "test.org/server-c",
		Version:    "1.0.0",
		Size:       100,
	})
	require.NoError(t, err)
	require.NotEmpty(t, serverCV2Rows)
	serverCV2 := serverCV2Rows[0]
	assert.Equal(t, uuidC, serverCV2.ID, "Server C UUID should be preserved")
	require.NotNil(t, serverCV2.Description)
	assert.Equal(t, "Server C unchanged", *serverCV2.Description)

	// Server D: new UUID, inserted
	serverDV1Rows, err := queries.GetServerVersion(ctx, sqlc.GetServerVersionParams{
		RegistryID: regID,
		Name:       "test.org/server-d",
		Version:    "1.0.0",
		Size:       100,
	})
	require.NoError(t, err)
	require.NotEmpty(t, serverDV1Rows)
	serverDV1 := serverDV1Rows[0]
	assert.NotEqual(t, uuid.Nil, serverDV1.ID, "Server D should have a valid UUID")
	require.NotNil(t, serverDV1.Description)
	assert.Equal(t, "Server D new", *serverDV1.Description)

	// Verify total server count
	servers, err := queries.ListServers(ctx, sqlc.ListServersParams{RegistryID: regID, Size: 100})
	require.NoError(t, err)
	require.Len(t, servers, 3, "Should have 3 servers (A, C, D)")
}

// --- Skill sync test helpers ---

// createTestSkill creates a test Skill
func createTestSkill(namespace, name, version string) toolhivetypes.Skill {
	return toolhivetypes.Skill{
		Namespace:   namespace,
		Name:        name,
		Version:     version,
		Description: "Test skill description",
		Title:       "Test Skill",
		Status:      "active",
		License:     "MIT",
	}
}

// createTestSkillWithPackages creates a test Skill with OCI and Git packages
func createTestSkillWithPackages(namespace, name, version string) toolhivetypes.Skill {
	skill := createTestSkill(namespace, name, version)
	skill.Packages = []toolhivetypes.SkillPackage{
		{
			RegistryType: "oci",
			Identifier:   "ghcr.io/test/skills/" + name + ":" + version,
			Digest:       "sha256:abc123",
			MediaType:    "application/vnd.test.skill.v1",
		},
		{
			RegistryType: "git",
			URL:          "https://github.com/test/skills.git",
			Ref:          "v" + version,
			Commit:       "abc123def456",
			Subfolder:    "skills/" + name,
		},
	}
	return skill
}

// createTestUpstreamRegistryWithSkills creates a test UpstreamRegistry with both servers and skills
func createTestUpstreamRegistryWithSkills(servers []upstreamv0.ServerJSON, skills []toolhivetypes.Skill) *toolhivetypes.UpstreamRegistry {
	return &toolhivetypes.UpstreamRegistry{
		Schema:  "https://example.com/schema.json",
		Version: "1.0.0",
		Data: toolhivetypes.UpstreamData{
			Servers: servers,
			Skills:  skills,
		},
	}
}

// Skill count query constants for test validation
const (
	testSkillCountQuery = `
	SELECT COUNT(s.*) FROM skill s
	  JOIN entry_version v ON s.version_id = v.id
	  JOIN registry_entry e ON v.entry_id = e.id
	 WHERE e.source_id = $1
	   AND e.entry_type = 'SKILL'
	`
	testSkillDetailQuery = `
	SELECT s.namespace, s.status, s.license, v.version, v.title, v.description
	  FROM skill s
	  JOIN entry_version v ON s.version_id = v.id
	  JOIN registry_entry e ON v.entry_id = e.id
	 WHERE e.source_id = $1
	   AND e.entry_type = 'SKILL'
	   AND e.name = $2
	`
	testSkillOCIPkgCountQuery = `
	SELECT COUNT(*) FROM skill_oci_package p
	  JOIN skill s ON p.skill_id = s.version_id
	  JOIN entry_version v ON s.version_id = v.id
	  JOIN registry_entry e ON v.entry_id = e.id
	 WHERE e.source_id = $1
	   AND e.name = $2
	`
	testSkillGitPkgCountQuery = `
	SELECT COUNT(*) FROM skill_git_package p
	  JOIN skill s ON p.skill_id = s.version_id
	  JOIN entry_version v ON s.version_id = v.id
	  JOIN registry_entry e ON v.entry_id = e.id
	 WHERE e.source_id = $1
	   AND e.name = $2
	`
)

// TestDbSyncWriter_Store_Skills tests skill storage through the Store method
func TestDbSyncWriter_Store_Skills(t *testing.T) {
	t.Parallel()

	tests := []struct {
		name         string
		registryName string
		setupFunc    func(t *testing.T, pool *pgxpool.Pool)
		registry     *toolhivetypes.UpstreamRegistry
		expectError  bool
		validateFunc func(t *testing.T, pool *pgxpool.Pool, registryName string)
	}{
		{
			name:         "successful sync with single skill",
			registryName: "test-registry",
			//nolint:thelper // We want to see these lines in the test output
			setupFunc: func(t *testing.T, pool *pgxpool.Pool) {
				createTestRegistry(t, pool, "test-registry")
			},
			registry: createTestUpstreamRegistryWithSkills(
				nil,
				[]toolhivetypes.Skill{
					createTestSkill("io.github.test", "my-skill", "1.0.0"),
				},
			),
			expectError: false,
			//nolint:thelper // We want to see these lines in the test output
			validateFunc: func(t *testing.T, pool *pgxpool.Pool, registryName string) {
				ctx := context.Background()
				queries := sqlc.New(pool)
				source, err := queries.GetSourceByName(ctx, registryName)
				require.NoError(t, err)

				var count int
				err = pool.QueryRow(ctx, testSkillCountQuery, source.ID).Scan(&count)
				require.NoError(t, err)
				assert.Equal(t, 1, count, "Should have 1 skill")

				var namespace, status string
				var license, version, title, description *string
				err = pool.QueryRow(ctx, testSkillDetailQuery, source.ID, "my-skill").
					Scan(&namespace, &status, &license, &version, &title, &description)
				require.NoError(t, err)
				assert.Equal(t, "io.github.test", namespace)
				assert.Equal(t, "ACTIVE", status)
				require.NotNil(t, license)
				assert.Equal(t, "MIT", *license)
				require.NotNil(t, version)
				assert.Equal(t, "1.0.0", *version)
				require.NotNil(t, title)
				assert.Equal(t, "Test Skill", *title)
				require.NotNil(t, description)
				assert.Equal(t, "Test skill description", *description)
			},
		},
		{
			name:         "successful sync with skill packages",
			registryName: "test-registry",
			//nolint:thelper // We want to see these lines in the test output
			setupFunc: func(t *testing.T, pool *pgxpool.Pool) {
				createTestRegistry(t, pool, "test-registry")
			},
			registry: createTestUpstreamRegistryWithSkills(
				nil,
				[]toolhivetypes.Skill{
					createTestSkillWithPackages("io.github.test", "pkg-skill", "1.0.0"),
				},
			),
			expectError: false,
			//nolint:thelper // We want to see these lines in the test output
			validateFunc: func(t *testing.T, pool *pgxpool.Pool, registryName string) {
				ctx := context.Background()
				queries := sqlc.New(pool)
				source, err := queries.GetSourceByName(ctx, registryName)
				require.NoError(t, err)

				// Verify the skill itself exists
				var count int
				err = pool.QueryRow(ctx, testSkillCountQuery, source.ID).Scan(&count)
				require.NoError(t, err)
				assert.Equal(t, 1, count, "Should have 1 skill")

				// Verify OCI package
				var ociCount int
				err = pool.QueryRow(ctx, testSkillOCIPkgCountQuery, source.ID, "pkg-skill").Scan(&ociCount)
				require.NoError(t, err)
				assert.Equal(t, 1, ociCount, "Should have 1 OCI package")

				// Verify Git package
				var gitCount int
				err = pool.QueryRow(ctx, testSkillGitPkgCountQuery, source.ID, "pkg-skill").Scan(&gitCount)
				require.NoError(t, err)
				assert.Equal(t, 1, gitCount, "Should have 1 Git package")
			},
		},
		{
			name:         "skill orphan cleanup",
			registryName: "test-registry",
			//nolint:thelper // We want to see these lines in the test output
			setupFunc: func(t *testing.T, pool *pgxpool.Pool) {
				ids := createTestRegistry(t, pool, "test-registry")

				// Pre-populate with 2 skills via an initial Store call
				w, err := NewDBSyncWriter(pool, testMaxMetaSize)
				require.NoError(t, err)
				initialRegistry := createTestUpstreamRegistryWithSkills(
					nil,
					[]toolhivetypes.Skill{
						createTestSkill("io.github.test", "skill-keep", "1.0.0"),
						createTestSkill("io.github.test", "skill-remove", "1.0.0"),
					},
				)
				err = w.Store(context.Background(), "test-registry", initialRegistry)
				require.NoError(t, err)

				// Verify both skills exist before the test's Store call
				var count int
				err = pool.QueryRow(context.Background(), testSkillCountQuery, ids.sourceID).Scan(&count)
				require.NoError(t, err)
				require.Equal(t, 2, count, "Setup should have created 2 skills")
			},
			// Now store with only 1 skill -- the other should be orphaned and cleaned up
			registry: createTestUpstreamRegistryWithSkills(
				nil,
				[]toolhivetypes.Skill{
					createTestSkill("io.github.test", "skill-keep", "1.0.0"),
				},
			),
			expectError: false,
			//nolint:thelper // We want to see these lines in the test output
			validateFunc: func(t *testing.T, pool *pgxpool.Pool, registryName string) {
				ctx := context.Background()
				queries := sqlc.New(pool)
				source, err := queries.GetSourceByName(ctx, registryName)
				require.NoError(t, err)

				var count int
				err = pool.QueryRow(ctx, testSkillCountQuery, source.ID).Scan(&count)
				require.NoError(t, err)
				assert.Equal(t, 1, count, "Should have 1 skill after orphan cleanup")

				// Verify the kept skill is the correct one
				var namespace string
				var version *string
				err = pool.QueryRow(ctx, testSkillDetailQuery, source.ID, "skill-keep").
					Scan(&namespace, new(string), new(*string), &version, new(*string), new(*string))
				require.NoError(t, err)
				assert.Equal(t, "io.github.test", namespace)
				require.NotNil(t, version)
				assert.Equal(t, "1.0.0", *version)
			},
		},
		{
			name:         "skill update preserves and updates data",
			registryName: "test-registry",
			//nolint:thelper // We want to see these lines in the test output
			setupFunc: func(t *testing.T, pool *pgxpool.Pool) {
				createTestRegistry(t, pool, "test-registry")

				// Pre-populate with original skill
				w, err := NewDBSyncWriter(pool, testMaxMetaSize)
				require.NoError(t, err)

				originalSkill := createTestSkill("io.github.test", "update-skill", "1.0.0")
				originalSkill.Title = "Old Title"
				originalSkill.Description = "Old description"
				originalSkill.License = "Apache-2.0"

				initialRegistry := createTestUpstreamRegistryWithSkills(
					nil,
					[]toolhivetypes.Skill{originalSkill},
				)
				err = w.Store(context.Background(), "test-registry", initialRegistry)
				require.NoError(t, err)
			},
			// Store again with updated fields
			registry: func() *toolhivetypes.UpstreamRegistry {
				updatedSkill := createTestSkill("io.github.test", "update-skill", "1.0.0")
				updatedSkill.Title = "New Title"
				updatedSkill.Description = "New description"
				updatedSkill.License = "MIT"
				return createTestUpstreamRegistryWithSkills(nil, []toolhivetypes.Skill{updatedSkill})
			}(),
			expectError: false,
			//nolint:thelper // We want to see these lines in the test output
			validateFunc: func(t *testing.T, pool *pgxpool.Pool, registryName string) {
				ctx := context.Background()
				queries := sqlc.New(pool)
				source, err := queries.GetSourceByName(ctx, registryName)
				require.NoError(t, err)

				var namespace, status string
				var license, version, title, description *string
				err = pool.QueryRow(ctx, testSkillDetailQuery, source.ID, "update-skill").
					Scan(&namespace, &status, &license, &version, &title, &description)
				require.NoError(t, err)

				assert.Equal(t, "io.github.test", namespace)
				require.NotNil(t, title)
				assert.Equal(t, "New Title", *title, "Title should be updated")
				require.NotNil(t, description)
				assert.Equal(t, "New description", *description, "Description should be updated")
				require.NotNil(t, license)
				assert.Equal(t, "MIT", *license, "License should be updated")
			},
		},
		{
			name:         "mixed servers and skills",
			registryName: "test-registry",
			//nolint:thelper // We want to see these lines in the test output
			setupFunc: func(t *testing.T, pool *pgxpool.Pool) {
				createTestRegistry(t, pool, "test-registry")
			},
			registry: createTestUpstreamRegistryWithSkills(
				[]upstreamv0.ServerJSON{
					createTestServer("test.org/server", "1.0.0"),
					createTestServer("test.org/server2", "2.0.0"),
				},
				[]toolhivetypes.Skill{
					createTestSkill("io.github.test", "mixed-skill-a", "1.0.0"),
					createTestSkillWithPackages("io.github.test", "mixed-skill-b", "2.0.0"),
				},
			),
			expectError: false,
			//nolint:thelper // We want to see these lines in the test output
			validateFunc: func(t *testing.T, pool *pgxpool.Pool, registryName string) {
				ctx := context.Background()
				queries := sqlc.New(pool)
				source, err := queries.GetSourceByName(ctx, registryName)
				require.NoError(t, err)
				regID := getTestRegistryID(t, pool, registryName)

				// Verify servers
				servers, err := queries.ListServers(ctx, sqlc.ListServersParams{RegistryID: regID, Size: 100})
				require.NoError(t, err)
				assert.Len(t, servers, 2, "Should have 2 servers")

				// Verify skills
				var skillCount int
				err = pool.QueryRow(ctx, testSkillCountQuery, source.ID).Scan(&skillCount)
				require.NoError(t, err)
				assert.Equal(t, 2, skillCount, "Should have 2 skills")

				// Verify skill-b has OCI and Git packages
				var ociCount, gitCount int
				err = pool.QueryRow(ctx, testSkillOCIPkgCountQuery, source.ID, "mixed-skill-b").Scan(&ociCount)
				require.NoError(t, err)
				assert.Equal(t, 1, ociCount, "Skill B should have 1 OCI package")

				err = pool.QueryRow(ctx, testSkillGitPkgCountQuery, source.ID, "mixed-skill-b").Scan(&gitCount)
				require.NoError(t, err)
				assert.Equal(t, 1, gitCount, "Skill B should have 1 Git package")
			},
		},
		{
			name:         "empty skills does not affect servers",
			registryName: "test-registry",
			//nolint:thelper // We want to see these lines in the test output
			setupFunc: func(t *testing.T, pool *pgxpool.Pool) {
				createTestRegistry(t, pool, "test-registry")
			},
			registry: createTestUpstreamRegistryWithSkills(
				[]upstreamv0.ServerJSON{
					createTestServer("test.org/server-x", "1.0.0"),
					createTestServer("test.org/server-y", "2.0.0"),
				},
				nil, // no skills
			),
			expectError: false,
			//nolint:thelper // We want to see these lines in the test output
			validateFunc: func(t *testing.T, pool *pgxpool.Pool, registryName string) {
				ctx := context.Background()
				queries := sqlc.New(pool)
				source, err := queries.GetSourceByName(ctx, registryName)
				require.NoError(t, err)
				regID := getTestRegistryID(t, pool, registryName)

				// Verify servers are stored correctly
				servers, err := queries.ListServers(ctx, sqlc.ListServersParams{RegistryID: regID, Size: 100})
				require.NoError(t, err)
				assert.Len(t, servers, 2, "Should have 2 servers")

				// Verify no skills exist
				var skillCount int
				err = pool.QueryRow(ctx, testSkillCountQuery, source.ID).Scan(&skillCount)
				require.NoError(t, err)
				assert.Equal(t, 0, skillCount, "Should have 0 skills")
			},
		},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			t.Parallel()

			pool, cleanup := setupTestDB(t)
			defer cleanup()

			if tt.setupFunc != nil {
				tt.setupFunc(t, pool)
			}

			w, err := NewDBSyncWriter(pool, testMaxMetaSize)
			require.NoError(t, err)

			err = w.Store(context.Background(), tt.registryName, tt.registry)

			if tt.expectError {
				require.Error(t, err)
			} else {
				require.NoError(t, err)
				if tt.validateFunc != nil {
					tt.validateFunc(t, pool, tt.registryName)
				}
			}
		})
	}
}

// TestDbSyncWriter_Store_RepeatedMixedSync verifies that a second Store call
// with both servers and skills does not corrupt data. This is a regression test
// for a bug where DeleteOrphanedServers would incorrectly delete skill
// entry_versions because it lacked an entry_type = 'MCP' filter.
func TestDbSyncWriter_Store_RepeatedMixedSync(t *testing.T) {
	t.Parallel()

	pool, cleanup := setupTestDB(t)
	defer cleanup()

	ctx := context.Background()
	createTestRegistry(t, pool, "test-registry")

	w, err := NewDBSyncWriter(pool, testMaxMetaSize)
	require.NoError(t, err)

	queries := sqlc.New(pool)
	regID := getTestRegistryID(t, pool, "test-registry")
	source, err := queries.GetSourceByName(ctx, "test-registry")
	require.NoError(t, err)

	// ── First sync: 2 servers + 2 skills ──────────────────────────────────
	firstRegistry := createTestUpstreamRegistryWithSkills(
		[]upstreamv0.ServerJSON{
			createTestServer("test.org/server-a", "1.0.0"),
			createTestServer("test.org/server-b", "1.0.0"),
		},
		[]toolhivetypes.Skill{
			createTestSkill("io.github.test", "skill-x", "1.0.0"),
			createTestSkillWithPackages("io.github.test", "skill-y", "1.0.0"),
		},
	)

	err = w.Store(ctx, "test-registry", firstRegistry)
	require.NoError(t, err)

	// Validate first sync: 2 servers
	servers, err := queries.ListServers(ctx, sqlc.ListServersParams{RegistryID: regID, Size: 100})
	require.NoError(t, err)
	require.Len(t, servers, 2, "Should have 2 servers after first sync")

	// Validate first sync: 2 skills
	var skillCount int
	err = pool.QueryRow(ctx, testSkillCountQuery, source.ID).Scan(&skillCount)
	require.NoError(t, err)
	assert.Equal(t, 2, skillCount, "Should have 2 skills after first sync")

	// Validate first sync: skill-y has OCI and Git packages
	var ociCount, gitCount int
	err = pool.QueryRow(ctx, testSkillOCIPkgCountQuery, source.ID, "skill-y").Scan(&ociCount)
	require.NoError(t, err)
	assert.Equal(t, 1, ociCount, "skill-y should have 1 OCI package after first sync")

	err = pool.QueryRow(ctx, testSkillGitPkgCountQuery, source.ID, "skill-y").Scan(&gitCount)
	require.NoError(t, err)
	assert.Equal(t, 1, gitCount, "skill-y should have 1 Git package after first sync")

	// ── Second sync: changed content ──────────────────────────────────────
	// Servers: server-a kept, server-b removed, server-c added
	// Skills: skill-x updated to v2.0.0, skill-y removed, skill-z added
	secondRegistry := createTestUpstreamRegistryWithSkills(
		[]upstreamv0.ServerJSON{
			createTestServer("test.org/server-a", "1.0.0"),
			createTestServer("test.org/server-c", "1.0.0"),
		},
		[]toolhivetypes.Skill{
			createTestSkill("io.github.test", "skill-x", "2.0.0"),
			createTestSkill("io.github.test", "skill-z", "1.0.0"),
		},
	)

	err = w.Store(ctx, "test-registry", secondRegistry)
	require.NoError(t, err)

	// Validate second sync: 2 servers (server-a, server-c; server-b orphaned)
	servers, err = queries.ListServers(ctx, sqlc.ListServersParams{RegistryID: regID, Size: 100})
	require.NoError(t, err)
	require.Len(t, servers, 2, "Should have 2 servers after second sync")

	serverNames := make(map[string]bool, len(servers))
	for _, s := range servers {
		serverNames[s.Name] = true
	}
	assert.True(t, serverNames["test.org/server-a"], "server-a should still exist")
	assert.True(t, serverNames["test.org/server-c"], "server-c should exist")
	assert.False(t, serverNames["test.org/server-b"], "server-b should be orphaned")

	// Validate second sync: 2 skills (skill-x updated, skill-z new; skill-y orphaned)
	err = pool.QueryRow(ctx, testSkillCountQuery, source.ID).Scan(&skillCount)
	require.NoError(t, err)
	assert.Equal(t, 2, skillCount, "Should have 2 skills after second sync")

	// skill-x should show version "2.0.0"
	var namespace, status string
	var license, version, title, description *string
	err = pool.QueryRow(ctx, testSkillDetailQuery, source.ID, "skill-x").
		Scan(&namespace, &status, &license, &version, &title, &description)
	require.NoError(t, err)
	assert.Equal(t, "io.github.test", namespace)
	require.NotNil(t, version)
	assert.Equal(t, "2.0.0", *version, "skill-x should be updated to v2.0.0")

	// skill-z should exist
	err = pool.QueryRow(ctx, testSkillDetailQuery, source.ID, "skill-z").
		Scan(&namespace, &status, &license, &version, &title, &description)
	require.NoError(t, err)
	assert.Equal(t, "io.github.test", namespace)
	require.NotNil(t, version)
	assert.Equal(t, "1.0.0", *version, "skill-z should be at v1.0.0")

	// skill-y should be gone (orphan cleanup); querying it should return no rows
	err = pool.QueryRow(ctx, testSkillDetailQuery, source.ID, "skill-y").
		Scan(new(string), new(string), new(*string), new(*string), new(*string), new(*string))
	assert.Error(t, err, "skill-y should have been removed by orphan cleanup")
}

// TestDbSyncWriter_Store_SkillMultiVersionLatest verifies that when multiple
// versions of the same skill are synced, the latest_entry_version table
// correctly points to the highest semantic version.
func TestDbSyncWriter_Store_SkillMultiVersionLatest(t *testing.T) {
	t.Parallel()

	pool, cleanup := setupTestDB(t)
	defer cleanup()

	ctx := context.Background()
	createTestRegistry(t, pool, "test-registry")

	w, err := NewDBSyncWriter(pool, testMaxMetaSize)
	require.NoError(t, err)

	queries := sqlc.New(pool)
	source, err := queries.GetSourceByName(ctx, "test-registry")
	require.NoError(t, err)

	// Store 3 versions of the same skill
	registry := createTestUpstreamRegistryWithSkills(
		nil,
		[]toolhivetypes.Skill{
			createTestSkill("io.github.test", "my-skill", "1.0.0"),
			createTestSkill("io.github.test", "my-skill", "2.0.0"),
			createTestSkill("io.github.test", "my-skill", "3.0.0"),
		},
	)

	err = w.Store(ctx, "test-registry", registry)
	require.NoError(t, err)

	// Validate: 3 skill rows exist (one per version)
	var skillCount int
	err = pool.QueryRow(ctx, testSkillCountQuery, source.ID).Scan(&skillCount)
	require.NoError(t, err)
	assert.Equal(t, 3, skillCount, "Should have 3 skill rows (one per version)")

	// Query latest_entry_version to verify it points to v3.0.0
	const latestVersionQuery = `
	SELECT lev.version FROM latest_entry_version lev
	  JOIN source s ON lev.source_id = s.id
	 WHERE s.name = $1
	   AND lev.name = $2
	`
	var latestVersion string
	err = pool.QueryRow(ctx, latestVersionQuery, "test-registry", "my-skill").Scan(&latestVersion)
	require.NoError(t, err)
	assert.Equal(t, "3.0.0", latestVersion, "latest_entry_version should point to v3.0.0")
}

// TestDbSyncWriter_Store_PerEntryClaims tests that WithPerEntryClaims overrides
// source-level claims for specific entries while other entries fall back to source claims.
func TestDbSyncWriter_Store_PerEntryClaims(t *testing.T) {
	t.Parallel()

	// Helper to marshal claims JSON for test inputs
	mustMarshal := func(t *testing.T, v any) []byte {
		t.Helper()
		b, err := json.Marshal(v)
		require.NoError(t, err)
		return b
	}

	type entryExpectation struct {
		name      string
		claimsNil bool
		claimsMap map[string]string
	}

	tests := []struct {
		name           string
		registryName   string
		servers        []upstreamv0.ServerJSON
		sourceClaims   []byte // nil means no source-level claims
		perEntryClaims map[string][]byte
		useOption      bool // whether to pass WithPerEntryClaims at all
		expectations   []entryExpectation
	}{
		{
			name:         "per-entry claims override source claims",
			registryName: "per-entry-override",
			servers: []upstreamv0.ServerJSON{
				createTestServer("test.org/server1", "1.0.0"),
				createTestServer("test.org/server2", "1.0.0"),
			},
			sourceClaims: mustMarshal(t, map[string]string{"org": "acme"}),
			perEntryClaims: map[string][]byte{
				"test.org/server1": mustMarshal(t, map[string]string{"org": "acme", "team": "platform"}),
			},
			useOption: true,
			expectations: []entryExpectation{
				{
					name:      "test.org/server1",
					claimsMap: map[string]string{"org": "acme", "team": "platform"},
				},
				{
					name:      "test.org/server2",
					claimsMap: map[string]string{"org": "acme"},
				},
			},
		},
		{
			name:         "per-entry claims with no source claims",
			registryName: "per-entry-no-source",
			servers: []upstreamv0.ServerJSON{
				createTestServer("test.org/server1", "1.0.0"),
				createTestServer("test.org/server2", "1.0.0"),
			},
			sourceClaims: nil,
			perEntryClaims: map[string][]byte{
				"test.org/server1": mustMarshal(t, map[string]string{"team": "data"}),
			},
			useOption: true,
			expectations: []entryExpectation{
				{
					name:      "test.org/server1",
					claimsMap: map[string]string{"team": "data"},
				},
				{
					name:      "test.org/server2",
					claimsNil: true,
				},
			},
		},
		{
			name:         "all entries have per-entry claims",
			registryName: "per-entry-all",
			servers: []upstreamv0.ServerJSON{
				createTestServer("test.org/server1", "1.0.0"),
				createTestServer("test.org/server2", "1.0.0"),
			},
			sourceClaims: mustMarshal(t, map[string]string{"org": "acme"}),
			perEntryClaims: map[string][]byte{
				"test.org/server1": mustMarshal(t, map[string]string{"team": "alpha"}),
				"test.org/server2": mustMarshal(t, map[string]string{"team": "beta"}),
			},
			useOption: true,
			expectations: []entryExpectation{
				{
					name:      "test.org/server1",
					claimsMap: map[string]string{"team": "alpha"},
				},
				{
					name:      "test.org/server2",
					claimsMap: map[string]string{"team": "beta"},
				},
			},
		},
		{
			name:         "empty per-entry claims map uses source claims",
			registryName: "per-entry-empty-map",
			servers: []upstreamv0.ServerJSON{
				createTestServer("test.org/server1", "1.0.0"),
				createTestServer("test.org/server2", "1.0.0"),
			},
			sourceClaims:   mustMarshal(t, map[string]string{"org": "acme"}),
			perEntryClaims: map[string][]byte{},
			useOption:      true,
			expectations: []entryExpectation{
				{
					name:      "test.org/server1",
					claimsMap: map[string]string{"org": "acme"},
				},
				{
					name:      "test.org/server2",
					claimsMap: map[string]string{"org": "acme"},
				},
			},
		},
		{
			name:         "no StoreOption uses source claims",
			registryName: "per-entry-no-option",
			servers: []upstreamv0.ServerJSON{
				createTestServer("test.org/server1", "1.0.0"),
				createTestServer("test.org/server2", "1.0.0"),
			},
			sourceClaims:   mustMarshal(t, map[string]string{"org": "acme"}),
			perEntryClaims: nil,
			useOption:      false,
			expectations: []entryExpectation{
				{
					name:      "test.org/server1",
					claimsMap: map[string]string{"org": "acme"},
				},
				{
					name:      "test.org/server2",
					claimsMap: map[string]string{"org": "acme"},
				},
			},
		},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			t.Parallel()

			pool, cleanup := setupTestDB(t)
			defer cleanup()

			ctx := context.Background()
			ids := createTestRegistry(t, pool, tt.registryName)

			// Set source-level claims if provided
			if tt.sourceClaims != nil {
				_, err := pool.Exec(ctx, "UPDATE source SET claims = $1 WHERE id = $2", tt.sourceClaims, ids.sourceID)
				require.NoError(t, err)
			}

			writer, err := NewDBSyncWriter(pool, testMaxMetaSize)
			require.NoError(t, err)

			reg := createTestUpstreamRegistry(tt.servers)

			// Call Store with or without the option
			var storeOpts []StoreOption
			if tt.useOption {
				storeOpts = append(storeOpts, WithPerEntryClaims(tt.perEntryClaims))
			}
			err = writer.Store(ctx, tt.registryName, reg, storeOpts...)
			require.NoError(t, err)

			// Read back entries and verify claims
			queries := sqlc.New(pool)
			entries, err := queries.ListEntriesBySource(ctx, ids.sourceID)
			require.NoError(t, err)

			// Build a map of entry name -> claims for easy lookup
			entryClaims := make(map[string][]byte, len(entries))
			for _, entry := range entries {
				entryClaims[entry.Name] = entry.Claims
			}

			for _, exp := range tt.expectations {
				claims, ok := entryClaims[exp.name]
				require.True(t, ok, "entry %q should exist", exp.name)

				if exp.claimsNil {
					assert.Nil(t, claims, "entry %q should have nil claims", exp.name)
				} else {
					require.NotNil(t, claims, "entry %q should have non-nil claims", exp.name)
					var got map[string]string
					err := json.Unmarshal(claims, &got)
					require.NoError(t, err, "entry %q claims should be valid JSON", exp.name)
					assert.Equal(t, exp.claimsMap, got, "entry %q claims mismatch", exp.name)
				}
			}
		})
	}
}
