at-container-registry/pkg/appview/db/queries_test.go

package db

import (
	"testing"
	"time"
)

func TestGetRepositoryMetadata(t *testing.T) {
	// Create in-memory test database
	db, err := InitDB(":memory:", true)
	if err != nil {
		t.Fatalf("Failed to init database: %v", err)
	}
	defer db.Close()

	// Insert test user
	testUser := &User{
		DID:         "did:plc:test123",
		Handle:      "testuser.bsky.social",
		PDSEndpoint: "https://test.pds.example.com",
		Avatar:      "",
		LastSeen:    time.Now(),
	}
	if err := UpsertUser(db, testUser); err != nil {
		t.Fatalf("Failed to insert user: %v", err)
	}

	// Test 1: No manifests - should return empty map
	metadata, err := GetRepositoryMetadata(db, testUser.DID, "nonexistent")
	if err != nil {
		t.Fatalf("Expected no error for nonexistent repo, got: %v", err)
	}
	if len(metadata) != 0 {
		t.Errorf("Expected empty map for nonexistent repository, got %d entries", len(metadata))
	}

	// Test 2: Insert manifest and annotations
	_, err = db.Exec(`
		INSERT INTO manifests (did, repository, digest, hold_endpoint, schema_version, media_type, created_at)
		VALUES (?, ?, ?, ?, ?, ?, ?)
	`, testUser.DID, "myapp", "sha256:abc123", "did:web:hold.example.com", 2, "application/vnd.oci.image.manifest.v1+json",
		time.Now().Add(-2*time.Hour))
	if err != nil {
		t.Fatalf("Failed to insert manifest: %v", err)
	}

	// Insert annotations separately
	err = UpsertRepositoryAnnotations(db, testUser.DID, "myapp", map[string]string{
		"org.opencontainers.image.title":         "My App",
		"org.opencontainers.image.description":   "A cool application",
		"org.opencontainers.image.source":        "https://github.com/user/myapp",
		"org.opencontainers.image.documentation": "https://docs.example.com",
		"org.opencontainers.image.licenses":      "MIT",
		"io.atcr.icon":                           "https://example.com/icon.png",
	})
	if err != nil {
		t.Fatalf("Failed to insert annotations: %v", err)
	}

	// Test 3: Retrieve metadata
	metadata, err = GetRepositoryMetadata(db, testUser.DID, "myapp")
	if err != nil {
		t.Fatalf("Failed to get repository metadata: %v", err)
	}

	if metadata["org.opencontainers.image.title"] != "My App" {
		t.Errorf("Expected title 'My App', got '%s'", metadata["org.opencontainers.image.title"])
	}
	if metadata["org.opencontainers.image.description"] != "A cool application" {
		t.Errorf("Expected description 'A cool application', got '%s'", metadata["org.opencontainers.image.description"])
	}
	if metadata["org.opencontainers.image.source"] != "https://github.com/user/myapp" {
		t.Errorf("Expected sourceURL 'https://github.com/user/myapp', got '%s'", metadata["org.opencontainers.image.source"])
	}
	if metadata["org.opencontainers.image.documentation"] != "https://docs.example.com" {
		t.Errorf("Expected documentationURL 'https://docs.example.com', got '%s'", metadata["org.opencontainers.image.documentation"])
	}
	if metadata["org.opencontainers.image.licenses"] != "MIT" {
		t.Errorf("Expected licenses 'MIT', got '%s'", metadata["org.opencontainers.image.licenses"])
	}
	if metadata["io.atcr.icon"] != "https://example.com/icon.png" {
		t.Errorf("Expected iconURL 'https://example.com/icon.png', got '%s'", metadata["io.atcr.icon"])
	}

	// Test 4: Insert newer manifest with different annotations
	_, err = db.Exec(`
		INSERT INTO manifests (did, repository, digest, hold_endpoint, schema_version, media_type, created_at)
		VALUES (?, ?, ?, ?, ?, ?, ?)
	`, testUser.DID, "myapp", "sha256:def456", "did:web:hold.example.com", 2, "application/vnd.oci.image.manifest.v1+json",
		time.Now()) // Most recent
	if err != nil {
		t.Fatalf("Failed to insert newer manifest: %v", err)
	}

	// Update annotations with new values (simulates latest manifest having different annotations)
	err = UpsertRepositoryAnnotations(db, testUser.DID, "myapp", map[string]string{
		"org.opencontainers.image.title":         "My App v2",
		"org.opencontainers.image.description":   "An even cooler application",
		"org.opencontainers.image.source":        "https://github.com/user/myapp-v2",
		"org.opencontainers.image.documentation": "https://v2.docs.example.com",
		"org.opencontainers.image.licenses":      "Apache-2.0",
		"io.atcr.icon":                           "https://example.com/icon-v2.png",
	})
	if err != nil {
		t.Fatalf("Failed to update annotations: %v", err)
	}

	// Test 5: Should return metadata from most recent manifest
	metadata, err = GetRepositoryMetadata(db, testUser.DID, "myapp")
	if err != nil {
		t.Fatalf("Failed to get repository metadata: %v", err)
	}

	if metadata["org.opencontainers.image.title"] != "My App v2" {
		t.Errorf("Expected title from newest manifest 'My App v2', got '%s'", metadata["org.opencontainers.image.title"])
	}
	if metadata["org.opencontainers.image.description"] != "An even cooler application" {
		t.Errorf("Expected description from newest manifest, got '%s'", metadata["org.opencontainers.image.description"])
	}
	if metadata["org.opencontainers.image.licenses"] != "Apache-2.0" {
		t.Errorf("Expected licenses 'Apache-2.0', got '%s'", metadata["org.opencontainers.image.licenses"])
	}

	// Test 6: Manifest with NULL metadata fields
	_, err = db.Exec(`
		INSERT INTO manifests (did, repository, digest, hold_endpoint, schema_version, media_type, created_at)
		VALUES (?, ?, ?, ?, ?, ?, ?)
	`, testUser.DID, "minimal-app", "sha256:minimal", "did:web:hold.example.com", 2, "application/vnd.oci.image.manifest.v1+json", time.Now())
	if err != nil {
		t.Fatalf("Failed to insert minimal manifest: %v", err)
	}

	// Test 7: Should handle missing annotations gracefully
	metadata, err = GetRepositoryMetadata(db, testUser.DID, "minimal-app")
	if err != nil {
		t.Fatalf("Failed to get repository metadata for minimal app: %v", err)
	}

	if len(metadata) != 0 {
		t.Error("Expected empty map for manifest with no annotations")
	}
}

func TestInsertManifest(t *testing.T) {
	// Create in-memory test database
	db, err := InitDB(":memory:", true)
	if err != nil {
		t.Fatalf("Failed to init database: %v", err)
	}
	defer db.Close()

	// Insert test user
	testUser := &User{
		DID:         "did:plc:test123",
		Handle:      "testuser.bsky.social",
		PDSEndpoint: "https://test.pds.example.com",
		Avatar:      "",
		LastSeen:    time.Now(),
	}
	if err := UpsertUser(db, testUser); err != nil {
		t.Fatalf("Failed to insert user: %v", err)
	}

	// Test 1: Insert new manifest with core fields
	manifest1 := &Manifest{
		DID:           testUser.DID,
		Repository:    "myapp",
		Digest:        "sha256:abc123",
		HoldEndpoint:  "did:web:hold.example.com",
		SchemaVersion: 2,
		MediaType:     "application/vnd.oci.image.manifest.v1+json",
		ConfigDigest:  "sha256:config123",
		ConfigSize:    1024,
		CreatedAt:     time.Now(),
	}

	id1, err := InsertManifest(db, manifest1)
	if err != nil {
		t.Fatalf("Failed to insert manifest: %v", err)
	}
	if id1 == 0 {
		t.Error("Expected non-zero manifest ID")
	}

	// Insert annotations separately
	annotations := map[string]string{
		"org.opencontainers.image.title":         "My App",
		"org.opencontainers.image.description":   "A cool application",
		"org.opencontainers.image.source":        "https://github.com/user/myapp",
		"org.opencontainers.image.documentation": "https://docs.example.com",
		"org.opencontainers.image.licenses":      "MIT",
		"io.atcr.icon":                           "https://example.com/icon.png",
		"io.atcr.readme":                         "https://github.com/user/myapp/blob/main/README.md",
	}
	err = UpsertRepositoryAnnotations(db, testUser.DID, "myapp", annotations)
	if err != nil {
		t.Fatalf("Failed to insert annotations: %v", err)
	}

	// Verify the manifest was inserted correctly
	retrieved, err := GetManifest(db, manifest1.Digest)
	if err != nil {
		t.Fatalf("Failed to retrieve manifest: %v", err)
	}
	if retrieved.ID != id1 {
		t.Errorf("Expected ID %d, got %d", id1, retrieved.ID)
	}

	// Verify annotations were inserted
	retrievedAnnotations, err := GetRepositoryAnnotations(db, testUser.DID, "myapp")
	if err != nil {
		t.Fatalf("Failed to retrieve annotations: %v", err)
	}
	if retrievedAnnotations["org.opencontainers.image.title"] != "My App" {
		t.Errorf("Expected title 'My App', got '%s'", retrievedAnnotations["org.opencontainers.image.title"])
	}
	if retrievedAnnotations["io.atcr.readme"] != "https://github.com/user/myapp/blob/main/README.md" {
		t.Errorf("Expected readme_url, got '%s'", retrievedAnnotations["io.atcr.readme"])
	}

	// Test 2: Insert manifest with minimal fields (NULLs for annotations)
	manifest2 := &Manifest{
		DID:           testUser.DID,
		Repository:    "minimal",
		Digest:        "sha256:minimal123",
		HoldEndpoint:  "did:web:hold.example.com",
		SchemaVersion: 2,
		MediaType:     "application/vnd.oci.image.manifest.v1+json",
		CreatedAt:     time.Now(),
	}

	id2, err := InsertManifest(db, manifest2)
	if err != nil {
		t.Fatalf("Failed to insert minimal manifest: %v", err)
	}
	if id2 == 0 {
		t.Error("Expected non-zero manifest ID for minimal manifest")
	}

	_, err = GetManifest(db, manifest2.Digest)
	if err != nil {
		t.Fatalf("Failed to retrieve minimal manifest: %v", err)
	}
	// Verify no annotations exist for minimal manifest
	minimalAnnotations, err := GetRepositoryAnnotations(db, testUser.DID, "minimal")
	if err != nil {
		t.Fatalf("Failed to get minimal annotations: %v", err)
	}
	if len(minimalAnnotations) != 0 {
		t.Errorf("Expected no annotations for minimal manifest, got %d", len(minimalAnnotations))
	}

	// Test 3: Upsert existing manifest (same DID+repo+digest) - verify UPDATE path
	manifest1Updated := &Manifest{
		DID:           testUser.DID,
		Repository:    "myapp",
		Digest:        "sha256:abc123", // Same digest - should trigger UPDATE
		HoldEndpoint:  "did:web:hold2.example.com",
		SchemaVersion: 2,
		MediaType:     "application/vnd.oci.image.manifest.v1+json",
		ConfigDigest:  "sha256:newconfig",
		ConfigSize:    2048,
		CreatedAt:     time.Now(),
	}

	id3, err := InsertManifest(db, manifest1Updated)
	if err != nil {
		t.Fatalf("Failed to upsert manifest: %v", err)
	}
	// ID should be the same as the original insert (UPDATE, not INSERT)
	if id3 != id1 {
		t.Errorf("Expected upsert to return same ID %d, got %d", id1, id3)
	}

	// Update annotations separately
	updatedAnnotations := map[string]string{
		"org.opencontainers.image.title":         "My App v2",
		"org.opencontainers.image.description":   "An updated application",
		"org.opencontainers.image.source":        "https://github.com/user/myapp-v2",
		"org.opencontainers.image.documentation": "https://v2.docs.example.com",
		"org.opencontainers.image.licenses":      "Apache-2.0",
		"io.atcr.icon":                           "https://example.com/icon-v2.png",
		"io.atcr.readme":                         "https://github.com/user/myapp/blob/v2/README.md",
	}
	err = UpsertRepositoryAnnotations(db, testUser.DID, "myapp", updatedAnnotations)
	if err != nil {
		t.Fatalf("Failed to update annotations: %v", err)
	}

	// Verify the manifest was updated
	retrievedUpdated, err := GetManifest(db, manifest1.Digest)
	if err != nil {
		t.Fatalf("Failed to retrieve updated manifest: %v", err)
	}
	if retrievedUpdated.HoldEndpoint != "did:web:hold2.example.com" {
		t.Errorf("Expected updated hold_endpoint, got '%s'", retrievedUpdated.HoldEndpoint)
	}

	// Verify annotations were updated
	retrievedUpdatedAnnotations, err := GetRepositoryAnnotations(db, testUser.DID, "myapp")
	if err != nil {
		t.Fatalf("Failed to retrieve updated annotations: %v", err)
	}
	if retrievedUpdatedAnnotations["org.opencontainers.image.title"] != "My App v2" {
		t.Errorf("Expected updated title 'My App v2', got '%s'", retrievedUpdatedAnnotations["org.opencontainers.image.title"])
	}
	if retrievedUpdatedAnnotations["io.atcr.readme"] != "https://github.com/user/myapp/blob/v2/README.md" {
		t.Errorf("Expected updated readme_url, got '%s'", retrievedUpdatedAnnotations["io.atcr.readme"])
	}

	// Test 4: Verify count - should have 2 manifests (not 3, because one was upserted)
	digests, err := GetManifestDigestsForDID(db, testUser.DID)
	if err != nil {
		t.Fatalf("Failed to get manifest digests: %v", err)
	}
	if len(digests) != 2 {
		t.Errorf("Expected 2 manifests after upsert, got %d", len(digests))
	}
}

func TestUserManagement(t *testing.T) {
	// Create in-memory test database
	db, err := InitDB(":memory:", true)
	if err != nil {
		t.Fatalf("Failed to init database: %v", err)
	}
	defer db.Close()

	// Test 1: Upsert new user
	user1 := &User{
		DID:         "did:plc:alice123",
		Handle:      "alice.bsky.social",
		PDSEndpoint: "https://bsky.social",
		Avatar:      "https://example.com/avatar.jpg",
		LastSeen:    time.Now(),
	}

	err = UpsertUser(db, user1)
	if err != nil {
		t.Fatalf("Failed to upsert new user: %v", err)
	}

	// Test 2: GetUserByDID - found
	retrieved, err := GetUserByDID(db, user1.DID)
	if err != nil {
		t.Fatalf("Failed to get user by DID: %v", err)
	}
	if retrieved == nil {
		t.Fatal("Expected user to be found, got nil")
	}
	if retrieved.Handle != "alice.bsky.social" {
		t.Errorf("Expected handle 'alice.bsky.social', got '%s'", retrieved.Handle)
	}
	if retrieved.Avatar != "https://example.com/avatar.jpg" {
		t.Errorf("Expected avatar URL, got '%s'", retrieved.Avatar)
	}

	// Test 3: GetUserByHandle - found
	retrievedByHandle, err := GetUserByHandle(db, user1.Handle)
	if err != nil {
		t.Fatalf("Failed to get user by handle: %v", err)
	}
	if retrievedByHandle == nil {
		t.Fatal("Expected user to be found by handle, got nil")
	}
	if retrievedByHandle.DID != user1.DID {
		t.Errorf("Expected DID '%s', got '%s'", user1.DID, retrievedByHandle.DID)
	}

	// Test 4: GetUserByDID - not found
	notFound, err := GetUserByDID(db, "did:plc:nonexistent")
	if err != nil {
		t.Fatalf("Expected no error for nonexistent user, got: %v", err)
	}
	if notFound != nil {
		t.Error("Expected nil for nonexistent user")
	}

	// Test 5: GetUserByHandle - not found
	notFoundByHandle, err := GetUserByHandle(db, "nonexistent.bsky.social")
	if err != nil {
		t.Fatalf("Expected no error for nonexistent handle, got: %v", err)
	}
	if notFoundByHandle != nil {
		t.Error("Expected nil for nonexistent handle")
	}

	// Test 6: Upsert existing user (update)
	user1.Handle = "alice-new.bsky.social" // Change handle
	user1.Avatar = ""                      // Remove avatar
	user1.LastSeen = time.Now().Add(1 * time.Hour)

	err = UpsertUser(db, user1)
	if err != nil {
		t.Fatalf("Failed to upsert existing user: %v", err)
	}

	// Verify update
	updated, err := GetUserByDID(db, user1.DID)
	if err != nil {
		t.Fatalf("Failed to get updated user: %v", err)
	}
	if updated.Handle != "alice-new.bsky.social" {
		t.Errorf("Expected updated handle 'alice-new.bsky.social', got '%s'", updated.Handle)
	}
	if updated.Avatar != "" {
		t.Errorf("Expected empty avatar after update, got '%s'", updated.Avatar)
	}

	// Test 7: User with empty avatar (NULL)
	user2 := &User{
		DID:         "did:plc:bob456",
		Handle:      "bob.bsky.social",
		PDSEndpoint: "https://bsky.social",
		Avatar:      "", // Empty avatar
		LastSeen:    time.Now(),
	}

	err = UpsertUser(db, user2)
	if err != nil {
		t.Fatalf("Failed to upsert user with empty avatar: %v", err)
	}

	retrieved2, err := GetUserByDID(db, user2.DID)
	if err != nil {
		t.Fatalf("Failed to get user with empty avatar: %v", err)
	}
	if retrieved2.Avatar != "" {
		t.Errorf("Expected empty avatar, got '%s'", retrieved2.Avatar)
	}
}

func TestManifestOperations(t *testing.T) {
	// Create in-memory test database
	db, err := InitDB(":memory:", true)
	if err != nil {
		t.Fatalf("Failed to init database: %v", err)
	}
	defer db.Close()

	// Setup: Create test user
	testUser := &User{
		DID:         "did:plc:test123",
		Handle:      "test.bsky.social",
		PDSEndpoint: "https://test.pds.example.com",
		LastSeen:    time.Now(),
	}
	if err := UpsertUser(db, testUser); err != nil {
		t.Fatalf("Failed to create test user: %v", err)
	}

	// Insert test manifests
	manifests := []*Manifest{
		{
			DID:           testUser.DID,
			Repository:    "app1",
			Digest:        "sha256:aaa",
			HoldEndpoint:  "did:web:hold.example.com",
			SchemaVersion: 2,
			MediaType:     "application/vnd.oci.image.manifest.v1+json",
			CreatedAt:     time.Now(),
		},
		{
			DID:           testUser.DID,
			Repository:    "app1",
			Digest:        "sha256:bbb",
			HoldEndpoint:  "did:web:hold.example.com",
			SchemaVersion: 2,
			MediaType:     "application/vnd.oci.image.manifest.v1+json",
			CreatedAt:     time.Now(),
		},
		{
			DID:           testUser.DID,
			Repository:    "app2",
			Digest:        "sha256:ccc",
			HoldEndpoint:  "did:web:hold.example.com",
			SchemaVersion: 2,
			MediaType:     "application/vnd.oci.image.manifest.v1+json",
			CreatedAt:     time.Now(),
		},
	}

	for _, m := range manifests {
		_, err := InsertManifest(db, m)
		if err != nil {
			t.Fatalf("Failed to insert manifest: %v", err)
		}
	}

	// Insert annotations for test manifests
	err = UpsertRepositoryAnnotations(db, testUser.DID, "app1", map[string]string{
		"org.opencontainers.image.title": "App 1",
	})
	if err != nil {
		t.Fatalf("Failed to insert app1 annotations: %v", err)
	}
	err = UpsertRepositoryAnnotations(db, testUser.DID, "app2", map[string]string{
		"org.opencontainers.image.title": "App 2",
	})
	if err != nil {
		t.Fatalf("Failed to insert app2 annotations: %v", err)
	}

	// Test 1: GetManifest - found
	retrieved, err := GetManifest(db, "sha256:aaa")
	if err != nil {
		t.Fatalf("Failed to get manifest: %v", err)
	}
	if retrieved.Digest != "sha256:aaa" {
		t.Errorf("Expected digest 'sha256:aaa', got '%s'", retrieved.Digest)
	}

	// Test 2: GetManifest - not found
	notFound, err := GetManifest(db, "sha256:nonexistent")
	if err == nil {
		t.Error("Expected error for nonexistent manifest")
	}
	if notFound != nil {
		t.Error("Expected nil for nonexistent manifest")
	}

	// Test 3: GetManifestDigestsForDID - multiple manifests
	digests, err := GetManifestDigestsForDID(db, testUser.DID)
	if err != nil {
		t.Fatalf("Failed to get manifest digests: %v", err)
	}
	if len(digests) != 3 {
		t.Errorf("Expected 3 manifests, got %d", len(digests))
	}

	// Test 4: GetManifestDigestsForDID - no manifests
	noDigests, err := GetManifestDigestsForDID(db, "did:plc:nonexistent")
	if err != nil {
		t.Fatalf("Expected no error for user with no manifests, got: %v", err)
	}
	if len(noDigests) != 0 {
		t.Errorf("Expected 0 manifests for nonexistent user, got %d", len(noDigests))
	}

	// Test 5: DeleteManifestsNotInList - keep some, delete others
	keepDigests := []string{"sha256:aaa", "sha256:ccc"}
	err = DeleteManifestsNotInList(db, testUser.DID, keepDigests)
	if err != nil {
		t.Fatalf("Failed to delete manifests not in list: %v", err)
	}

	// Verify only sha256:aaa and sha256:ccc remain
	remaining, err := GetManifestDigestsForDID(db, testUser.DID)
	if err != nil {
		t.Fatalf("Failed to get remaining manifests: %v", err)
	}
	if len(remaining) != 2 {
		t.Errorf("Expected 2 remaining manifests, got %d", len(remaining))
	}

	// Verify sha256:bbb was deleted
	deleted, err := GetManifest(db, "sha256:bbb")
	if err == nil {
		t.Error("Expected error for deleted manifest")
	}
	if deleted != nil {
		t.Error("Expected nil for deleted manifest")
	}

	// Test 6: DeleteManifestsNotInList - empty list (delete all)
	err = DeleteManifestsNotInList(db, testUser.DID, []string{})
	if err != nil {
		t.Fatalf("Failed to delete all manifests: %v", err)
	}

	allGone, err := GetManifestDigestsForDID(db, testUser.DID)
	if err != nil {
		t.Fatalf("Failed to get manifests after delete all: %v", err)
	}
	if len(allGone) != 0 {
		t.Errorf("Expected 0 manifests after delete all, got %d", len(allGone))
	}

	// Test 7: DeleteManifest - specific deletion (re-insert for this test)
	manifest := &Manifest{
		DID:           testUser.DID,
		Repository:    "app3",
		Digest:        "sha256:ddd",
		HoldEndpoint:  "did:web:hold.example.com",
		SchemaVersion: 2,
		MediaType:     "application/vnd.oci.image.manifest.v1+json",
		CreatedAt:     time.Now(),
	}
	_, err = InsertManifest(db, manifest)
	if err != nil {
		t.Fatalf("Failed to insert manifest for delete test: %v", err)
	}

	// Delete by DID+repo+digest
	err = DeleteManifest(db, testUser.DID, "app3", "sha256:ddd")
	if err != nil {
		t.Fatalf("Failed to delete manifest: %v", err)
	}

	// Verify deletion
	afterDelete, err := GetManifest(db, "sha256:ddd")
	if err == nil {
		t.Error("Expected error for deleted manifest")
	}
	if afterDelete != nil {
		t.Error("Expected nil after deletion")
	}
}

func TestIsManifestTagged(t *testing.T) {
	// Create in-memory test database
	db, err := InitDB(":memory:", true)
	if err != nil {
		t.Fatalf("Failed to init database: %v", err)
	}
	defer db.Close()

	// Setup: Create test user
	testUser := &User{
		DID:         "did:plc:test123",
		Handle:      "test.bsky.social",
		PDSEndpoint: "https://test.pds.example.com",
		LastSeen:    time.Now(),
	}
	if err := UpsertUser(db, testUser); err != nil {
		t.Fatalf("Failed to create test user: %v", err)
	}

	// Insert manifest
	manifest := &Manifest{
		DID:           testUser.DID,
		Repository:    "myapp",
		Digest:        "sha256:abc123",
		HoldEndpoint:  "did:web:hold.example.com",
		SchemaVersion: 2,
		MediaType:     "application/vnd.oci.image.manifest.v1+json",
		CreatedAt:     time.Now(),
	}
	_, err = InsertManifest(db, manifest)
	if err != nil {
		t.Fatalf("Failed to insert manifest: %v", err)
	}

	// Test 1: Manifest without tags
	tagged, err := IsManifestTagged(db, testUser.DID, "myapp", "sha256:abc123")
	if err != nil {
		t.Fatalf("Failed to check if manifest is tagged: %v", err)
	}
	if tagged {
		t.Error("Expected manifest to not be tagged")
	}

	// Test 2: Add a tag
	tag := &Tag{
		DID:        testUser.DID,
		Repository: "myapp",
		Tag:        "latest",
		Digest:     "sha256:abc123",
		CreatedAt:  time.Now(),
	}
	err = UpsertTag(db, tag)
	if err != nil {
		t.Fatalf("Failed to insert tag: %v", err)
	}

	// Test 3: Manifest with tag
	taggedNow, err := IsManifestTagged(db, testUser.DID, "myapp", "sha256:abc123")
	if err != nil {
		t.Fatalf("Failed to check if manifest is tagged: %v", err)
	}
	if !taggedNow {
		t.Error("Expected manifest to be tagged")
	}
}

func TestTagOperations(t *testing.T) {
	// Create in-memory test database
	db, err := InitDB(":memory:", true)
	if err != nil {
		t.Fatalf("Failed to init database: %v", err)
	}
	defer db.Close()

	// Setup: Create test user and manifests
	testUser := &User{
		DID:         "did:plc:test123",
		Handle:      "test.bsky.social",
		PDSEndpoint: "https://test.pds.example.com",
		LastSeen:    time.Now(),
	}
	if err := UpsertUser(db, testUser); err != nil {
		t.Fatalf("Failed to create test user: %v", err)
	}

	manifest := &Manifest{
		DID:           testUser.DID,
		Repository:    "myapp",
		Digest:        "sha256:abc123",
		HoldEndpoint:  "did:web:hold.example.com",
		SchemaVersion: 2,
		MediaType:     "application/vnd.oci.image.manifest.v1+json",
		CreatedAt:     time.Now(),
	}
	_, err = InsertManifest(db, manifest)
	if err != nil {
		t.Fatalf("Failed to insert manifest: %v", err)
	}

	// Test 1: UpsertTag - insert new tag
	tag1 := &Tag{
		DID:        testUser.DID,
		Repository: "myapp",
		Tag:        "latest",
		Digest:     "sha256:abc123",
		CreatedAt:  time.Now(),
	}
	err = UpsertTag(db, tag1)
	if err != nil {
		t.Fatalf("Failed to upsert tag: %v", err)
	}

	// Test 2: GetTagsForDID - should have 1 tag
	tags, err := GetTagsForDID(db, testUser.DID)
	if err != nil {
		t.Fatalf("Failed to get tags: %v", err)
	}
	if len(tags) != 1 {
		t.Errorf("Expected 1 tag, got %d", len(tags))
	}
	if tags[0].Repository != "myapp" || tags[0].Tag != "latest" {
		t.Errorf("Expected myapp:latest, got %s:%s", tags[0].Repository, tags[0].Tag)
	}

	// Test 3: UpsertTag - update existing tag (point to new digest)
	tag1Updated := &Tag{
		DID:        testUser.DID,
		Repository: "myapp",
		Tag:        "latest", // Same tag
		Digest:     "sha256:new456",
		CreatedAt:  time.Now(),
	}
	err = UpsertTag(db, tag1Updated)
	if err != nil {
		t.Fatalf("Failed to update tag: %v", err)
	}

	// Verify update - should still have 1 tag but with new digest
	tagsAfterUpdate, err := GetTagsForDID(db, testUser.DID)
	if err != nil {
		t.Fatalf("Failed to get tags after update: %v", err)
	}
	if len(tagsAfterUpdate) != 1 {
		t.Errorf("Expected 1 tag after update, got %d", len(tagsAfterUpdate))
	}
	if tagsAfterUpdate[0].Tag != "latest" {
		t.Errorf("Expected tag 'latest', got '%s'", tagsAfterUpdate[0].Tag)
	}

	// Test 4: Add more tags
	tag2 := &Tag{
		DID:        testUser.DID,
		Repository: "myapp",
		Tag:        "v1.0.0",
		Digest:     "sha256:abc123",
		CreatedAt:  time.Now(),
	}
	tag3 := &Tag{
		DID:        testUser.DID,
		Repository: "otherapp",
		Tag:        "latest",
		Digest:     "sha256:xyz789",
		CreatedAt:  time.Now(),
	}
	err = UpsertTag(db, tag2)
	if err != nil {
		t.Fatalf("Failed to insert tag2: %v", err)
	}
	err = UpsertTag(db, tag3)
	if err != nil {
		t.Fatalf("Failed to insert tag3: %v", err)
	}

	// Verify count
	allTags, err := GetTagsForDID(db, testUser.DID)
	if err != nil {
		t.Fatalf("Failed to get all tags: %v", err)
	}
	if len(allTags) != 3 {
		t.Errorf("Expected 3 tags, got %d", len(allTags))
	}

	// Test 5: DeleteTagsNotInList - keep some, delete others
	keepTags := []struct{ Repository, Tag string }{
		{Repository: "myapp", Tag: "latest"},
		{Repository: "otherapp", Tag: "latest"},
	}
	err = DeleteTagsNotInList(db, testUser.DID, keepTags)
	if err != nil {
		t.Fatalf("Failed to delete tags not in list: %v", err)
	}

	// Verify v1.0.0 was deleted
	remaining, err := GetTagsForDID(db, testUser.DID)
	if err != nil {
		t.Fatalf("Failed to get remaining tags: %v", err)
	}
	if len(remaining) != 2 {
		t.Errorf("Expected 2 remaining tags, got %d", len(remaining))
	}

	// Test 6: DeleteTag - specific deletion
	err = DeleteTag(db, testUser.DID, "myapp", "latest")
	if err != nil {
		t.Fatalf("Failed to delete tag: %v", err)
	}

	// Verify deletion
	afterDelete, err := GetTagsForDID(db, testUser.DID)
	if err != nil {
		t.Fatalf("Failed to get tags after delete: %v", err)
	}
	if len(afterDelete) != 1 {
		t.Errorf("Expected 1 tag after delete, got %d", len(afterDelete))
	}
	if afterDelete[0].Repository != "otherapp" {
		t.Errorf("Wrong tag remained: %s:%s", afterDelete[0].Repository, afterDelete[0].Tag)
	}

	// Test 7: GetTagsForDID - no tags
	noTags, err := GetTagsForDID(db, "did:plc:nonexistent")
	if err != nil {
		t.Fatalf("Expected no error for user with no tags, got: %v", err)
	}
	if len(noTags) != 0 {
		t.Errorf("Expected 0 tags for nonexistent user, got %d", len(noTags))
	}
}

func TestGetTagsWithPlatforms(t *testing.T) {
	// Create in-memory test database
	db, err := InitDB(":memory:", true)
	if err != nil {
		t.Fatalf("Failed to init database: %v", err)
	}
	defer db.Close()

	// Setup: Create test user
	testUser := &User{
		DID:         "did:plc:test123",
		Handle:      "test.bsky.social",
		PDSEndpoint: "https://test.pds.example.com",
		LastSeen:    time.Now(),
	}
	if err := UpsertUser(db, testUser); err != nil {
		t.Fatalf("Failed to create test user: %v", err)
	}

	// Test 1: Single-arch manifest (no platform info)
	singleArchManifest := &Manifest{
		DID:           testUser.DID,
		Repository:    "myapp",
		Digest:        "sha256:single",
		HoldEndpoint:  "did:web:hold.example.com",
		SchemaVersion: 2,
		MediaType:     "application/vnd.oci.image.manifest.v1+json",
		CreatedAt:     time.Now(),
	}
	manifestID1, err := InsertManifest(db, singleArchManifest)
	if err != nil {
		t.Fatalf("Failed to insert single-arch manifest: %v", err)
	}

	singleTag := &Tag{
		DID:        testUser.DID,
		Repository: "myapp",
		Tag:        "latest",
		Digest:     "sha256:single",
		CreatedAt:  time.Now(),
	}
	err = UpsertTag(db, singleTag)
	if err != nil {
		t.Fatalf("Failed to insert single-arch tag: %v", err)
	}

	tagsWithPlatforms, err := GetTagsWithPlatforms(db, testUser.DID, "myapp")
	if err != nil {
		t.Fatalf("Failed to get tags with platforms: %v", err)
	}
	if len(tagsWithPlatforms) != 1 {
		t.Fatalf("Expected 1 tag, got %d", len(tagsWithPlatforms))
	}
	if tagsWithPlatforms[0].IsMultiArch {
		t.Error("Expected single-arch tag to not be multi-arch")
	}
	if len(tagsWithPlatforms[0].Platforms) != 0 {
		t.Errorf("Expected 0 platforms for single-arch, got %d", len(tagsWithPlatforms[0].Platforms))
	}

	// Test 2: Multi-arch manifest (manifest list with platform info)
	multiArchManifest := &Manifest{
		DID:           testUser.DID,
		Repository:    "multiapp",
		Digest:        "sha256:multi",
		HoldEndpoint:  "did:web:hold.example.com",
		SchemaVersion: 2,
		MediaType:     "application/vnd.oci.image.index.v1+json", // Manifest list
		CreatedAt:     time.Now(),
	}
	manifestID2, err := InsertManifest(db, multiArchManifest)
	if err != nil {
		t.Fatalf("Failed to insert multi-arch manifest: %v", err)
	}

	// Add manifest references with platform info
	ref1 := &ManifestReference{
		ManifestID:           manifestID2,
		Digest:               "sha256:amd64",
		Size:                 1000,
		MediaType:            "application/vnd.oci.image.manifest.v1+json",
		PlatformOS:           "linux",
		PlatformArchitecture: "amd64",
		ReferenceIndex:       0,
	}
	ref2 := &ManifestReference{
		ManifestID:           manifestID2,
		Digest:               "sha256:arm64",
		Size:                 1000,
		MediaType:            "application/vnd.oci.image.manifest.v1+json",
		PlatformOS:           "linux",
		PlatformArchitecture: "arm64",
		ReferenceIndex:       1,
	}
	err = InsertManifestReference(db, ref1)
	if err != nil {
		t.Fatalf("Failed to insert manifest reference 1: %v", err)
	}
	err = InsertManifestReference(db, ref2)
	if err != nil {
		t.Fatalf("Failed to insert manifest reference 2: %v", err)
	}

	multiTag := &Tag{
		DID:        testUser.DID,
		Repository: "multiapp",
		Tag:        "latest",
		Digest:     "sha256:multi",
		CreatedAt:  time.Now(),
	}
	err = UpsertTag(db, multiTag)
	if err != nil {
		t.Fatalf("Failed to insert multi-arch tag: %v", err)
	}

	multiTagsWithPlatforms, err := GetTagsWithPlatforms(db, testUser.DID, "multiapp")
	if err != nil {
		t.Fatalf("Failed to get multi-arch tags with platforms: %v", err)
	}
	if len(multiTagsWithPlatforms) != 1 {
		t.Fatalf("Expected 1 tag, got %d", len(multiTagsWithPlatforms))
	}
	if !multiTagsWithPlatforms[0].IsMultiArch {
		t.Error("Expected multi-arch tag to be marked as multi-arch")
	}
	if len(multiTagsWithPlatforms[0].Platforms) != 2 {
		t.Errorf("Expected 2 platforms for multi-arch, got %d", len(multiTagsWithPlatforms[0].Platforms))
	}

	// Verify platform details
	platforms := multiTagsWithPlatforms[0].Platforms
	if platforms[0].OS != "linux" || platforms[0].Architecture != "amd64" {
		t.Errorf("Expected linux/amd64, got %s/%s", platforms[0].OS, platforms[0].Architecture)
	}
	if platforms[1].OS != "linux" || platforms[1].Architecture != "arm64" {
		t.Errorf("Expected linux/arm64, got %s/%s", platforms[1].OS, platforms[1].Architecture)
	}

	// Don't use manifestID1 since it's not accessed after assignment
	_ = manifestID1
}

func TestUpdateUserHandle(t *testing.T) {
	// Create in-memory test database
	db, err := InitDB(":memory:", true)
	if err != nil {
		t.Fatalf("Failed to init database: %v", err)
	}
	defer db.Close()

	// Setup: Create test user
	testUser := &User{
		DID:         "did:plc:alice123",
		Handle:      "alice.bsky.social",
		PDSEndpoint: "https://bsky.social",
		Avatar:      "https://example.com/avatar.jpg",
		LastSeen:    time.Now(),
	}
	err = UpsertUser(db, testUser)
	if err != nil {
		t.Fatalf("Failed to create test user: %v", err)
	}

	// Test 1: Update handle for existing user
	newHandle := "alice-new.bsky.social"
	err = UpdateUserHandle(db, testUser.DID, newHandle)
	if err != nil {
		t.Fatalf("Failed to update user handle: %v", err)
	}

	// Verify handle was updated
	retrieved, err := GetUserByDID(db, testUser.DID)
	if err != nil {
		t.Fatalf("Failed to get user after handle update: %v", err)
	}
	if retrieved == nil {
		t.Fatal("Expected user to be found, got nil")
	}
	if retrieved.Handle != newHandle {
		t.Errorf("Expected handle '%s', got '%s'", newHandle, retrieved.Handle)
	}

	// Verify other fields unchanged
	if retrieved.DID != testUser.DID {
		t.Errorf("DID changed unexpectedly: %s -> %s", testUser.DID, retrieved.DID)
	}
	if retrieved.PDSEndpoint != testUser.PDSEndpoint {
		t.Errorf("PDS endpoint changed unexpectedly")
	}
	if retrieved.Avatar != testUser.Avatar {
		t.Errorf("Avatar changed unexpectedly")
	}

	// Test 2: Update handle for non-existent user (should not error, but no rows affected)
	err = UpdateUserHandle(db, "did:plc:nonexistent", "new.handle.social")
	if err != nil {
		t.Errorf("Expected no error for non-existent user, got: %v", err)
	}

	// Test 3: Update handle multiple times
	handles := []string{"alice1.bsky.social", "alice2.bsky.social", "alice3.bsky.social"}
	for _, handle := range handles {
		err = UpdateUserHandle(db, testUser.DID, handle)
		if err != nil {
			t.Fatalf("Failed to update handle to '%s': %v", handle, err)
		}

		retrieved, err = GetUserByDID(db, testUser.DID)
		if err != nil {
			t.Fatalf("Failed to retrieve user: %v", err)
		}
		if retrieved.Handle != handle {
			t.Errorf("Expected handle '%s', got '%s'", handle, retrieved.Handle)
		}
	}
}

// TestEscapeLikePattern tests the SQL LIKE pattern escaping function
func TestEscapeLikePattern(t *testing.T) {
	tests := []struct {
		name     string
		input    string
		expected string
	}{
		{
			name:     "plain text",
			input:    "hello",
			expected: "hello",
		},
		{
			name:     "with percent wildcard",
			input:    "hello%world",
			expected: "hello\\%world",
		},
		{
			name:     "with underscore wildcard",
			input:    "hello_world",
			expected: "hello\\_world",
		},
		{
			name:     "with backslash",
			input:    "hello\\world",
			expected: "hello\\\\world",
		},
		{
			name:     "with null byte",
			input:    "test\x00null",
			expected: "testnull",
		},
		{
			name:     "with control characters",
			input:    "test\x01\x02control",
			expected: "testcontrol",
		},
		{
			name:     "keep tabs and newlines",
			input:    "test\t\n\rwhitespace",
			expected: "test\t\n\rwhitespace",
		},
		{
			name:     "with leading/trailing spaces",
			input:    "  padded  ",
			expected: "padded",
		},
		{
			name:     "multiple wildcards",
			input:    "test%_value\\here",
			expected: "test\\%\\_value\\\\here",
		},
		{
			name:     "empty string",
			input:    "",
			expected: "",
		},
		{
			name:     "only spaces",
			input:    "   ",
			expected: "",
		},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			result := escapeLikePattern(tt.input)
			if result != tt.expected {
				t.Errorf("escapeLikePattern(%q) = %q, want %q", tt.input, result, tt.expected)
			}
		})
	}
}

// TestParseTimestamp tests the timestamp parsing function with multiple formats
func TestParseTimestamp(t *testing.T) {
	tests := []struct {
		name      string
		input     string
		shouldErr bool
	}{
		{
			name:      "RFC3339",
			input:     "2024-01-01T12:00:00Z",
			shouldErr: false,
		},
		{
			name:      "RFC3339Nano",
			input:     "2024-01-01T12:00:00.123456789Z",
			shouldErr: false,
		},
		{
			name:      "SQLite format",
			input:     "2024-01-01 12:00:00",
			shouldErr: false,
		},
		{
			name:      "SQLite with nanos",
			input:     "2024-01-01 12:00:00.123456789",
			shouldErr: false,
		},
		{
			name:      "SQLite with timezone",
			input:     "2024-01-01 12:00:00.123456789-07:00",
			shouldErr: false,
		},
		{
			name:      "RFC3339 with timezone",
			input:     "2024-01-01T12:00:00-07:00",
			shouldErr: false,
		},
		{
			name:      "invalid format",
			input:     "not-a-date",
			shouldErr: true,
		},
		{
			name:      "empty string",
			input:     "",
			shouldErr: true,
		},
		{
			name:      "partial date",
			input:     "2024-01-01",
			shouldErr: true,
		},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			result, err := parseTimestamp(tt.input)
			if tt.shouldErr {
				if err == nil {
					t.Errorf("parseTimestamp(%q) expected error, got nil (result: %v)", tt.input, result)
				}
			} else {
				if err != nil {
					t.Errorf("parseTimestamp(%q) unexpected error: %v", tt.input, err)
				}
				if result.IsZero() {
					t.Errorf("parseTimestamp(%q) returned zero time", tt.input)
				}
			}
		})
	}
}