package portal

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

	"github.com/DATA-DOG/go-sqlmock"
	"github.com/stretchr/testify/assert"
	"github.com/stretchr/testify/require"
)

// --- AssetStore tests ---

func TestPostgresAssetStoreInsert(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close() //nolint:errcheck // test cleanup

	store := NewPostgresAssetStore(db)

	asset := Asset{
		ID:          "abc123",
		OwnerID:     "user1",
		Name:        "Test Dashboard",
		Description: "A test",
		ContentType: "text/html",
		S3Bucket:    "portal",
		S3Key:       "user1/abc123/content.html",
		SizeBytes:   1024,
		Tags:        []string{"dashboard"},
		Provenance:  Provenance{SessionID: "sess1"},
		SessionID:   "sess1",
	}

	mock.ExpectExec("INSERT INTO portal_assets").
		WithArgs(
			asset.ID, asset.OwnerID, asset.OwnerEmail, asset.Name, asset.Description,
			asset.ContentType, asset.S3Bucket, asset.S3Key, asset.SizeBytes,
			sqlmock.AnyArg(), sqlmock.AnyArg(), asset.SessionID, 0, nil,
		).
		WillReturnResult(sqlmock.NewResult(0, 1))

	err = store.Insert(context.Background(), asset)
	assert.NoError(t, err)
	assert.NoError(t, mock.ExpectationsWereMet())
}

// TestPostgresAssetStoreInsertNilTags pins the normalization that a nil
// Tags slice is persisted as JSON `[]` rather than `null`. Persisting
// `null` is what blanked the portal: the React asset list calls
// `asset.tags.slice(0, 3)` and one null tags row brings down the entire
// React tree with no error boundary.
func TestPostgresAssetStoreInsertNilTags(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close() //nolint:errcheck // test cleanup

	store := NewPostgresAssetStore(db)

	asset := Asset{
		ID:          "abc123",
		OwnerID:     "user1",
		Name:        "Untagged Export",
		ContentType: "application/json",
		S3Bucket:    "portal",
		S3Key:       "user1/abc123/content.json",
		SizeBytes:   42,
		Tags:        nil, // simulate api_export with no tags supplied
	}

	mock.ExpectExec("INSERT INTO portal_assets").
		WithArgs(
			asset.ID, asset.OwnerID, asset.OwnerEmail, asset.Name, asset.Description,
			asset.ContentType, asset.S3Bucket, asset.S3Key, asset.SizeBytes,
			[]byte("[]"), sqlmock.AnyArg(), asset.SessionID, 0, nil,
		).
		WillReturnResult(sqlmock.NewResult(0, 1))

	require.NoError(t, store.Insert(context.Background(), asset))
	assert.NoError(t, mock.ExpectationsWereMet())
}

// TestPostgresAssetStoreGetNullTags pins the read-side normalization:
// rows that were persisted before the Insert fix may still hold the
// literal JSON `null` for tags. The Get path must surface an empty
// slice so the portal API response is always `"tags": []`.
func TestPostgresAssetStoreGetNullTags(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close() //nolint:errcheck // test cleanup

	store := NewPostgresAssetStore(db)
	now := time.Now()
	prov, _ := json.Marshal(Provenance{})

	rows := sqlmock.NewRows([]string{
		"id", "owner_id", "owner_email", "name", "description", "content_type", "s3_bucket", "s3_key",
		"thumbnail_s3_key", "size_bytes", "tags", "provenance", "session_id", "current_version", "created_at", "updated_at", "deleted_at", "idempotency_key",
	}).AddRow(
		"abc123", "user1", "user1@example.com", "Untagged", "", "application/json", "portal", "key1",
		"", int64(42), []byte("null"), prov, "", 1, now, now, nil, "",
	)

	mock.ExpectQuery("SELECT .+ FROM portal_assets WHERE id").
		WithArgs("abc123").
		WillReturnRows(rows)

	asset, err := store.Get(context.Background(), "abc123")
	require.NoError(t, err)
	assert.NotNil(t, asset.Tags, "stored JSON null must surface as a non-nil slice")
	assert.Equal(t, []string{}, asset.Tags)
	assert.NoError(t, mock.ExpectationsWereMet())
}

func TestPostgresAssetStoreGet(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close() //nolint:errcheck // test cleanup

	store := NewPostgresAssetStore(db)
	now := time.Now()

	tags, _ := json.Marshal([]string{"report"})
	prov, _ := json.Marshal(Provenance{SessionID: "sess1"})

	rows := sqlmock.NewRows([]string{
		"id", "owner_id", "owner_email", "name", "description", "content_type", "s3_bucket", "s3_key",
		"thumbnail_s3_key", "size_bytes", "tags", "provenance", "session_id", "current_version", "created_at", "updated_at", "deleted_at", "idempotency_key",
	}).AddRow(
		"abc123", "user1", "user1@example.com", "Test", "desc", "text/html", "portal", "key1",
		"", int64(512), tags, prov, "sess1", 1, now, now, nil, "",
	)

	mock.ExpectQuery("SELECT .+ FROM portal_assets WHERE id").
		WithArgs("abc123").
		WillReturnRows(rows)

	asset, err := store.Get(context.Background(), "abc123")
	require.NoError(t, err)
	assert.Equal(t, "abc123", asset.ID)
	assert.Equal(t, "user1", asset.OwnerID)
	assert.Equal(t, "user1@example.com", asset.OwnerEmail)
	assert.Equal(t, []string{"report"}, asset.Tags)
	assert.Nil(t, asset.DeletedAt)
	assert.NoError(t, mock.ExpectationsWereMet())
}

func TestPostgresAssetStoreGetNotFound(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close() //nolint:errcheck // test cleanup

	store := NewPostgresAssetStore(db)

	mock.ExpectQuery("SELECT .+ FROM portal_assets WHERE id").
		WithArgs("missing").
		WillReturnError(fmt.Errorf("sql: no rows in result set"))

	_, err = store.Get(context.Background(), "missing")
	assert.Error(t, err)
	assert.NoError(t, mock.ExpectationsWereMet())
}

func TestPostgresAssetStoreGetByIdempotencyKey(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close() //nolint:errcheck // test cleanup

	store := NewPostgresAssetStore(db)
	now := time.Now()
	tags, _ := json.Marshal([]string{"export"})
	prov, _ := json.Marshal(Provenance{SessionID: "sess1"})

	rows := sqlmock.NewRows([]string{
		"id", "owner_id", "owner_email", "name", "description", "content_type", "s3_bucket", "s3_key",
		"thumbnail_s3_key", "size_bytes", "tags", "provenance", "session_id", "current_version", "created_at", "updated_at", "deleted_at", "idempotency_key",
	}).AddRow(
		"abc123", "user1", "user1@example.com", "Export", "desc", "text/csv", "portal", "key1",
		"", int64(1024), tags, prov, "sess1", 1, now, now, nil, "dedup-key-1",
	)

	mock.ExpectQuery("SELECT .+ FROM portal_assets WHERE owner_id").
		WithArgs("user1", "dedup-key-1").
		WillReturnRows(rows)

	asset, err := store.GetByIdempotencyKey(context.Background(), "user1", "dedup-key-1")
	require.NoError(t, err)
	assert.Equal(t, "abc123", asset.ID)
	assert.Equal(t, "dedup-key-1", asset.IdempotencyKey)
	assert.Equal(t, []string{"export"}, asset.Tags)
	assert.NoError(t, mock.ExpectationsWereMet())
}

func TestPostgresAssetStoreGetByIdempotencyKeyNotFound(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close() //nolint:errcheck // test cleanup

	store := NewPostgresAssetStore(db)

	mock.ExpectQuery("SELECT .+ FROM portal_assets WHERE owner_id").
		WithArgs("user1", "missing-key").
		WillReturnError(fmt.Errorf("sql: no rows in result set"))

	_, err = store.GetByIdempotencyKey(context.Background(), "user1", "missing-key")
	assert.Error(t, err)
	assert.NoError(t, mock.ExpectationsWereMet())
}

func TestPostgresAssetStoreInsertWithIdempotencyKey(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close() //nolint:errcheck // test cleanup

	store := NewPostgresAssetStore(db)

	asset := Asset{
		ID:             "abc123",
		OwnerID:        "user1",
		Name:           "Export",
		ContentType:    "text/csv",
		S3Bucket:       "portal",
		S3Key:          "key1",
		Tags:           []string{},
		Provenance:     Provenance{},
		IdempotencyKey: "dedup-key-1",
	}

	mock.ExpectExec("INSERT INTO portal_assets").
		WithArgs(
			asset.ID, asset.OwnerID, asset.OwnerEmail, asset.Name, asset.Description,
			asset.ContentType, asset.S3Bucket, asset.S3Key, asset.SizeBytes,
			sqlmock.AnyArg(), sqlmock.AnyArg(), asset.SessionID, 0,
			sqlmock.AnyArg(), // idempotency_key (pointer)
		).
		WillReturnResult(sqlmock.NewResult(0, 1))

	err = store.Insert(context.Background(), asset)
	assert.NoError(t, err)
	assert.NoError(t, mock.ExpectationsWereMet())
}

func TestPostgresAssetStoreList(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close() //nolint:errcheck // test cleanup

	store := NewPostgresAssetStore(db)
	now := time.Now()

	tags, _ := json.Marshal([]string{})
	prov, _ := json.Marshal(Provenance{})

	// Count query
	mock.ExpectQuery("SELECT COUNT").WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(1))

	// Select query
	dataRows := sqlmock.NewRows([]string{
		"id", "owner_id", "owner_email", "name", "description", "content_type", "s3_bucket", "s3_key",
		"thumbnail_s3_key", "size_bytes", "tags", "provenance", "session_id", "current_version", "created_at", "updated_at", "deleted_at", "idempotency_key",
	}).AddRow(
		"abc123", "user1", "", "Test", "", "text/html", "portal", "key1",
		"", int64(100), tags, prov, "", 1, now, now, nil, "",
	)
	mock.ExpectQuery("SELECT .+ FROM portal_assets").WillReturnRows(dataRows)

	// populateCollections query
	mock.ExpectQuery("SELECT ci.asset_id").WillReturnRows(
		sqlmock.NewRows([]string{"asset_id", "id", "name"}),
	)

	assets, total, err := store.List(context.Background(), AssetFilter{OwnerID: "user1"})
	require.NoError(t, err)
	assert.Equal(t, 1, total)
	assert.Len(t, assets, 1)
	assert.Equal(t, "abc123", assets[0].ID)
	assert.NoError(t, mock.ExpectationsWereMet())
}

func TestPostgresAssetStoreUpdate(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close() //nolint:errcheck // test cleanup

	store := NewPostgresAssetStore(db)

	mock.ExpectExec("UPDATE portal_assets").
		WillReturnResult(sqlmock.NewResult(0, 1))

	name := "New Name"
	err = store.Update(context.Background(), "abc123", AssetUpdate{Name: &name})
	assert.NoError(t, err)
	assert.NoError(t, mock.ExpectationsWereMet())
}

func TestPostgresAssetStoreUpdateAllFields(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close() //nolint:errcheck // test cleanup

	store := NewPostgresAssetStore(db)

	mock.ExpectExec("UPDATE portal_assets").
		WillReturnResult(sqlmock.NewResult(0, 1))

	name := "New"
	desc := "Desc"
	err = store.Update(context.Background(), "abc123", AssetUpdate{
		Name:        &name,
		Description: &desc,
		Tags:        []string{"tag1"},
		ContentType: "text/csv",
		S3Key:       "new/key",
		SizeBytes:   2048,
		HasContent:  true,
	})
	assert.NoError(t, err)
	assert.NoError(t, mock.ExpectationsWereMet())
}

func TestPostgresAssetStoreUpdateNotFound(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close() //nolint:errcheck // test cleanup

	store := NewPostgresAssetStore(db)

	mock.ExpectExec("UPDATE portal_assets").
		WillReturnResult(sqlmock.NewResult(0, 0))

	name := "x"
	err = store.Update(context.Background(), "missing", AssetUpdate{Name: &name})
	assert.Error(t, err)
	assert.Contains(t, err.Error(), "not found or deleted")
	assert.NoError(t, mock.ExpectationsWereMet())
}

func TestPostgresAssetStoreUpdateClearDescription(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close() //nolint:errcheck // test cleanup

	store := NewPostgresAssetStore(db)

	mock.ExpectExec("UPDATE portal_assets").
		WillReturnResult(sqlmock.NewResult(0, 1))

	empty := ""
	err = store.Update(context.Background(), "abc123", AssetUpdate{Description: &empty})
	assert.NoError(t, err)
	assert.NoError(t, mock.ExpectationsWereMet())
}

func TestPostgresAssetStoreUpdateThumbnailKey(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close() //nolint:errcheck // test cleanup

	store := NewPostgresAssetStore(db)

	mock.ExpectExec("UPDATE portal_assets").
		WillReturnResult(sqlmock.NewResult(0, 1))

	thumbKey := "portal/u1/a1/thumbnail.png"
	err = store.Update(context.Background(), "abc123", AssetUpdate{ThumbnailS3Key: &thumbKey})
	assert.NoError(t, err)
	assert.NoError(t, mock.ExpectationsWereMet())
}

func TestPostgresAssetStoreUpdateClearThumbnail(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close() //nolint:errcheck // test cleanup

	store := NewPostgresAssetStore(db)

	mock.ExpectExec("UPDATE portal_assets").
		WillReturnResult(sqlmock.NewResult(0, 1))

	empty := ""
	err = store.Update(context.Background(), "abc123", AssetUpdate{ThumbnailS3Key: &empty})
	assert.NoError(t, err)
	assert.NoError(t, mock.ExpectationsWereMet())
}

func TestPostgresAssetStoreUpdateNoFields(t *testing.T) {
	db, _, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close() //nolint:errcheck // test cleanup

	store := NewPostgresAssetStore(db)
	err = store.Update(context.Background(), "abc123", AssetUpdate{})
	assert.Error(t, err)
	assert.Contains(t, err.Error(), "no fields to update")
}

func TestPostgresAssetStoreSoftDelete(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close() //nolint:errcheck // test cleanup

	store := NewPostgresAssetStore(db)

	mock.ExpectExec("UPDATE portal_assets SET deleted_at").
		WillReturnResult(sqlmock.NewResult(0, 1))

	err = store.SoftDelete(context.Background(), "abc123")
	assert.NoError(t, err)
	assert.NoError(t, mock.ExpectationsWereMet())
}

func TestPostgresAssetStoreSoftDeleteNotFound(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close() //nolint:errcheck // test cleanup

	store := NewPostgresAssetStore(db)

	mock.ExpectExec("UPDATE portal_assets SET deleted_at").
		WillReturnResult(sqlmock.NewResult(0, 0))

	err = store.SoftDelete(context.Background(), "missing")
	assert.Error(t, err)
	assert.Contains(t, err.Error(), "not found or already deleted")
	assert.NoError(t, mock.ExpectationsWereMet())
}

// --- ShareStore tests ---

func TestPostgresShareStoreInsert(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close() //nolint:errcheck // test cleanup

	store := NewPostgresShareStore(db)
	expires := time.Now().Add(24 * time.Hour)

	share := Share{
		ID:        "share1",
		AssetID:   "abc123",
		Token:     "tok123",
		CreatedBy: "user1",
		ExpiresAt: &expires,
	}

	mock.ExpectExec("INSERT INTO portal_shares").
		WithArgs(share.ID, sqlmock.AnyArg(), sqlmock.AnyArg(), share.Token, share.CreatedBy, sqlmock.AnyArg(), sqlmock.AnyArg(), sqlmock.AnyArg(), share.HideExpiration, share.NoticeText, "viewer").
		WillReturnResult(sqlmock.NewResult(0, 1))

	err = store.Insert(context.Background(), share)
	assert.NoError(t, err)
	assert.NoError(t, mock.ExpectationsWereMet())
}

func TestPostgresShareStoreGetByToken(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close() //nolint:errcheck // test cleanup

	store := NewPostgresShareStore(db)
	now := time.Now()

	rows := sqlmock.NewRows([]string{
		"id", "asset_id", "collection_id", "token", "created_by", "shared_with_user_id", "shared_with_email",
		"expires_at", "revoked", "hide_expiration", "notice_text", "access_count", "last_accessed_at", "created_at", "permission",
	}).AddRow("share1", "abc123", nil, "tok123", "user1", nil, nil, nil, false, false, defaultNoticeText, 5, now, now, "viewer")

	mock.ExpectQuery("SELECT .+ FROM portal_shares WHERE token").
		WithArgs("tok123").
		WillReturnRows(rows)

	share, err := store.GetByToken(context.Background(), "tok123")
	require.NoError(t, err)
	assert.Equal(t, "share1", share.ID)
	assert.Equal(t, 5, share.AccessCount)
	assert.NoError(t, mock.ExpectationsWereMet())
}

func TestPostgresShareStoreListByAsset(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close() //nolint:errcheck // test cleanup

	store := NewPostgresShareStore(db)
	now := time.Now()

	rows := sqlmock.NewRows([]string{
		"id", "asset_id", "collection_id", "token", "created_by", "shared_with_user_id", "shared_with_email",
		"expires_at", "revoked", "hide_expiration", "notice_text", "access_count", "last_accessed_at", "created_at", "permission",
	}).AddRow("share1", "abc123", nil, "tok1", "user1", nil, nil, nil, false, false, defaultNoticeText, 0, nil, now, "viewer")

	mock.ExpectQuery("SELECT .+ FROM portal_shares WHERE asset_id").
		WithArgs("abc123").
		WillReturnRows(rows)

	shares, err := store.ListByAsset(context.Background(), "abc123")
	require.NoError(t, err)
	assert.Len(t, shares, 1)
	assert.NoError(t, mock.ExpectationsWereMet())
}

func TestPostgresShareStoreListByAssetAllFields(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close() //nolint:errcheck // test cleanup

	store := NewPostgresShareStore(db)
	now := time.Now()
	expires := now.Add(24 * time.Hour)

	rows := sqlmock.NewRows([]string{
		"id", "asset_id", "collection_id", "token", "created_by", "shared_with_user_id", "shared_with_email",
		"expires_at", "revoked", "hide_expiration", "notice_text", "access_count", "last_accessed_at", "created_at", "permission",
	}).AddRow("share1", "abc123", nil, "tok1", "user1", "user2", "user2@example.com", expires, false, true, "Custom notice", 3, now, now, "editor")

	mock.ExpectQuery("SELECT .+ FROM portal_shares WHERE asset_id").
		WithArgs("abc123").
		WillReturnRows(rows)

	shares, err := store.ListByAsset(context.Background(), "abc123")
	require.NoError(t, err)
	require.Len(t, shares, 1)
	assert.Equal(t, "user2", shares[0].SharedWithUserID)
	assert.Equal(t, "user2@example.com", shares[0].SharedWithEmail)
	assert.NotNil(t, shares[0].ExpiresAt)
	assert.NotNil(t, shares[0].LastAccessedAt)
	assert.True(t, shares[0].HideExpiration)
	assert.Equal(t, 3, shares[0].AccessCount)
	assert.NoError(t, mock.ExpectationsWereMet())
}

func TestPostgresShareStoreRevoke(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close() //nolint:errcheck // test cleanup

	store := NewPostgresShareStore(db)

	mock.ExpectExec("UPDATE portal_shares SET revoked").
		WithArgs("share1").
		WillReturnResult(sqlmock.NewResult(0, 1))

	err = store.Revoke(context.Background(), "share1")
	assert.NoError(t, err)
	assert.NoError(t, mock.ExpectationsWereMet())
}

func TestPostgresShareStoreRevokeNotFound(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close() //nolint:errcheck // test cleanup

	store := NewPostgresShareStore(db)

	mock.ExpectExec("UPDATE portal_shares SET revoked").
		WithArgs("missing").
		WillReturnResult(sqlmock.NewResult(0, 0))

	err = store.Revoke(context.Background(), "missing")
	assert.Error(t, err)
	assert.Contains(t, err.Error(), "not found or already revoked")
	assert.NoError(t, mock.ExpectationsWereMet())
}

func TestPostgresShareStoreIncrementAccess(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close() //nolint:errcheck // test cleanup

	store := NewPostgresShareStore(db)

	mock.ExpectExec("UPDATE portal_shares SET access_count").
		WillReturnResult(sqlmock.NewResult(0, 1))

	err = store.IncrementAccess(context.Background(), "share1")
	assert.NoError(t, err)
	assert.NoError(t, mock.ExpectationsWereMet())
}

// --- Noop store tests ---

func TestNoopAssetStore(t *testing.T) {
	store := NewNoopAssetStore()
	ctx := context.Background()

	assert.NoError(t, store.Insert(ctx, Asset{}))

	_, err := store.Get(ctx, "any")
	assert.Error(t, err)

	assets, total, err := store.List(ctx, AssetFilter{})
	assert.NoError(t, err)
	assert.Nil(t, assets)
	assert.Equal(t, 0, total)

	assert.NoError(t, store.Update(ctx, "any", AssetUpdate{}))
	assert.NoError(t, store.SoftDelete(ctx, "any"))
}

func TestNoopShareStore(t *testing.T) {
	store := NewNoopShareStore()
	ctx := context.Background()

	assert.NoError(t, store.Insert(ctx, Share{}))

	_, err := store.GetByID(ctx, "any")
	assert.Error(t, err)

	_, err = store.GetByToken(ctx, "any")
	assert.Error(t, err)

	shares, err := store.ListByAsset(ctx, "any")
	assert.NoError(t, err)
	assert.Nil(t, shares)

	shared, total, err := store.ListSharedWithUser(ctx, "any", "", 10, 0)
	assert.NoError(t, err)
	assert.Nil(t, shared)
	assert.Equal(t, 0, total)

	assert.NoError(t, store.Revoke(ctx, "any"))
	assert.NoError(t, store.IncrementAccess(ctx, "any"))

	summaries, err := store.ListActiveShareSummaries(ctx, []string{"a1"})
	assert.NoError(t, err)
	assert.Empty(t, summaries)
}

// --- Type tests ---

func TestAssetFilterEffectiveLimit(t *testing.T) {
	tests := []struct {
		name     string
		limit    int
		expected int
	}{
		{"default", 0, defaultLimit},
		{"negative", -1, defaultLimit},
		{"small", 10, 10},
		{"max", maxLimit, maxLimit},
		{"over_max", maxLimit + 1, maxLimit},
	}
	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			f := AssetFilter{Limit: tt.limit}
			assert.Equal(t, tt.expected, f.EffectiveLimit())
		})
	}
}

// --- Validation tests ---

func TestValidateAssetName(t *testing.T) {
	assert.Error(t, ValidateAssetName(""))
	assert.NoError(t, ValidateAssetName("valid name"))

	longName := make([]byte, maxNameLength+1)
	for i := range longName {
		longName[i] = 'a'
	}
	assert.Error(t, ValidateAssetName(string(longName)))
}

func TestValidateContentType(t *testing.T) {
	assert.Error(t, ValidateContentType(""))
	assert.NoError(t, ValidateContentType("text/html"))
}

func TestValidateTags(t *testing.T) {
	assert.NoError(t, ValidateTags(nil))
	assert.NoError(t, ValidateTags([]string{"a", "b"}))

	tooMany := make([]string, maxTags+1)
	assert.Error(t, ValidateTags(tooMany))

	longTag := make([]byte, maxTagLength+1)
	for i := range longTag {
		longTag[i] = 'a'
	}
	assert.Error(t, ValidateTags([]string{string(longTag)}))
}

func TestValidateDescription(t *testing.T) {
	assert.NoError(t, ValidateDescription(""))
	assert.NoError(t, ValidateDescription("valid"))

	longDesc := make([]byte, maxDescriptionLength+1)
	for i := range longDesc {
		longDesc[i] = 'a'
	}
	assert.Error(t, ValidateDescription(string(longDesc)))
}

func TestValidateChangeSummary(t *testing.T) {
	assert.NoError(t, ValidateChangeSummary(""))
	assert.NoError(t, ValidateChangeSummary("Fixed typo"))
	assert.NoError(t, ValidateChangeSummary(strings.Repeat("a", MaxChangeSummaryLength)))

	longSummary := strings.Repeat("a", MaxChangeSummaryLength+1)
	assert.Error(t, ValidateChangeSummary(longSummary))
}

func TestValidateNoticeText(t *testing.T) {
	assert.NoError(t, ValidateNoticeText(""))
	assert.NoError(t, ValidateNoticeText("Custom notice"))
	assert.NoError(t, ValidateNoticeText(strings.Repeat("a", maxNoticeTextLength)))

	longText := strings.Repeat("a", maxNoticeTextLength+1)
	assert.Error(t, ValidateNoticeText(longText))
}

func TestValidateEmail(t *testing.T) {
	assert.NoError(t, ValidateEmail("user@example.com"))
	assert.NoError(t, ValidateEmail("a@b.co"))
	assert.Error(t, ValidateEmail(""))               // empty
	assert.Error(t, ValidateEmail("noatsign"))       // no @
	assert.Error(t, ValidateEmail("@example.com"))   // no local part
	assert.Error(t, ValidateEmail("user@"))          // no domain
	assert.Error(t, ValidateEmail("user@localhost")) // no dot in domain

	longEmail := strings.Repeat("a", 250) + "@b.co"
	assert.Error(t, ValidateEmail(longEmail)) // exceeds 254
}

// --- Error path tests ---

func TestPostgresAssetStoreInsertError(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close() //nolint:errcheck // test cleanup

	store := NewPostgresAssetStore(db)

	mock.ExpectExec("INSERT INTO portal_assets").
		WillReturnError(fmt.Errorf("db error"))

	err = store.Insert(context.Background(), Asset{
		Tags: []string{}, Provenance: Provenance{},
	})
	assert.Error(t, err)
	assert.Contains(t, err.Error(), "inserting asset")
	assert.NoError(t, mock.ExpectationsWereMet())
}

func TestPostgresAssetStoreListCountError(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close() //nolint:errcheck // test cleanup

	store := NewPostgresAssetStore(db)

	mock.ExpectQuery("SELECT COUNT").WillReturnError(fmt.Errorf("db error"))

	_, _, err = store.List(context.Background(), AssetFilter{OwnerID: "user1"})
	assert.Error(t, err)
	assert.Contains(t, err.Error(), "counting assets")
	assert.NoError(t, mock.ExpectationsWereMet())
}

func TestPostgresAssetStoreListQueryError(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close() //nolint:errcheck // test cleanup

	store := NewPostgresAssetStore(db)

	mock.ExpectQuery("SELECT COUNT").WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(1))
	mock.ExpectQuery("SELECT .+ FROM portal_assets").WillReturnError(fmt.Errorf("db error"))

	_, _, err = store.List(context.Background(), AssetFilter{OwnerID: "user1"})
	assert.Error(t, err)
	assert.Contains(t, err.Error(), "querying assets")
	assert.NoError(t, mock.ExpectationsWereMet())
}

func TestPostgresAssetStoreListWithOffset(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close() //nolint:errcheck // test cleanup

	store := NewPostgresAssetStore(db)

	mock.ExpectQuery("SELECT COUNT").WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(0))

	tags, _ := json.Marshal([]string{})
	prov, _ := json.Marshal(Provenance{})

	dataRows := sqlmock.NewRows([]string{
		"id", "owner_id", "owner_email", "name", "description", "content_type", "s3_bucket", "s3_key",
		"thumbnail_s3_key", "size_bytes", "tags", "provenance", "session_id", "current_version", "created_at", "updated_at", "deleted_at", "idempotency_key",
	}).AddRow(
		"abc123", "user1", "", "Test", "", "text/html", "portal", "key1",
		"", int64(100), tags, prov, "", 1, time.Now(), time.Now(), nil, "",
	)
	mock.ExpectQuery("SELECT .+ FROM portal_assets").WillReturnRows(dataRows)

	// populateCollections query
	mock.ExpectQuery("SELECT ci.asset_id").WillReturnRows(
		sqlmock.NewRows([]string{"asset_id", "id", "name"}),
	)

	assets, _, err := store.List(context.Background(), AssetFilter{
		OwnerID: "user1", Offset: 10, Limit: 5,
	})
	require.NoError(t, err)
	assert.Len(t, assets, 1)
	assert.NoError(t, mock.ExpectationsWereMet())
}

func TestPostgresAssetStoreListFilterByTag(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close() //nolint:errcheck // test cleanup

	store := NewPostgresAssetStore(db)

	mock.ExpectQuery("SELECT COUNT").WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(0))
	mock.ExpectQuery("SELECT .+ FROM portal_assets").WillReturnRows(
		sqlmock.NewRows([]string{
			"id", "owner_id", "owner_email", "name", "description", "content_type", "s3_bucket", "s3_key",
			"thumbnail_s3_key", "size_bytes", "tags", "provenance", "session_id", "current_version", "created_at", "updated_at", "deleted_at", "idempotency_key",
		}),
	)

	assets, _, err := store.List(context.Background(), AssetFilter{Tag: "dashboard"})
	require.NoError(t, err)
	assert.Empty(t, assets)
	assert.NoError(t, mock.ExpectationsWereMet())
}

func TestPostgresAssetStoreListFilterByContentType(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close() //nolint:errcheck // test cleanup

	store := NewPostgresAssetStore(db)

	mock.ExpectQuery("SELECT COUNT").WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(0))
	mock.ExpectQuery("SELECT .+ FROM portal_assets").WillReturnRows(
		sqlmock.NewRows([]string{
			"id", "owner_id", "owner_email", "name", "description", "content_type", "s3_bucket", "s3_key",
			"thumbnail_s3_key", "size_bytes", "tags", "provenance", "session_id", "current_version", "created_at", "updated_at", "deleted_at", "idempotency_key",
		}),
	)

	assets, _, err := store.List(context.Background(), AssetFilter{ContentType: "text/html"})
	require.NoError(t, err)
	assert.Empty(t, assets)
	assert.NoError(t, mock.ExpectationsWereMet())
}

func TestPostgresAssetStoreListFilterBySearch(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close() //nolint:errcheck // test cleanup

	store := NewPostgresAssetStore(db)

	mock.ExpectQuery("SELECT COUNT").WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(0))
	mock.ExpectQuery("SELECT .+ FROM portal_assets").WillReturnRows(
		sqlmock.NewRows([]string{
			"id", "owner_id", "owner_email", "name", "description", "content_type", "s3_bucket", "s3_key",
			"thumbnail_s3_key", "size_bytes", "tags", "provenance", "session_id", "current_version", "created_at", "updated_at", "deleted_at", "idempotency_key",
		}),
	)

	assets, _, err := store.List(context.Background(), AssetFilter{Search: "dashboard"})
	require.NoError(t, err)
	assert.Empty(t, assets)
	assert.NoError(t, mock.ExpectationsWereMet())
}

func TestPostgresAssetStoreUpdateExecError(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close() //nolint:errcheck // test cleanup

	store := NewPostgresAssetStore(db)

	mock.ExpectExec("UPDATE portal_assets").
		WillReturnError(fmt.Errorf("db error"))

	name := "x"
	err = store.Update(context.Background(), "abc123", AssetUpdate{Name: &name})
	assert.Error(t, err)
	assert.Contains(t, err.Error(), "updating asset")
	assert.NoError(t, mock.ExpectationsWereMet())
}

func TestPostgresAssetStoreSoftDeleteExecError(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close() //nolint:errcheck // test cleanup

	store := NewPostgresAssetStore(db)

	mock.ExpectExec("UPDATE portal_assets SET deleted_at").
		WillReturnError(fmt.Errorf("db error"))

	err = store.SoftDelete(context.Background(), "abc123")
	assert.Error(t, err)
	assert.Contains(t, err.Error(), "soft-deleting asset")
	assert.NoError(t, mock.ExpectationsWereMet())
}

func TestPostgresShareStoreInsertError(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close() //nolint:errcheck // test cleanup

	store := NewPostgresShareStore(db)

	mock.ExpectExec("INSERT INTO portal_shares").
		WillReturnError(fmt.Errorf("db error"))

	err = store.Insert(context.Background(), Share{ID: "s1", AssetID: "a1", Token: "t1"})
	assert.Error(t, err)
	assert.Contains(t, err.Error(), "inserting share")
	assert.NoError(t, mock.ExpectationsWereMet())
}

func TestPostgresShareStoreListByAssetError(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close() //nolint:errcheck // test cleanup

	store := NewPostgresShareStore(db)

	mock.ExpectQuery("SELECT .+ FROM portal_shares WHERE asset_id").
		WillReturnError(fmt.Errorf("db error"))

	_, err = store.ListByAsset(context.Background(), "abc123")
	assert.Error(t, err)
	assert.Contains(t, err.Error(), "querying shares")
	assert.NoError(t, mock.ExpectationsWereMet())
}

func TestPostgresShareStoreRevokeExecError(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close() //nolint:errcheck // test cleanup

	store := NewPostgresShareStore(db)

	mock.ExpectExec("UPDATE portal_shares SET revoked").
		WillReturnError(fmt.Errorf("db error"))

	err = store.Revoke(context.Background(), "share1")
	assert.Error(t, err)
	assert.Contains(t, err.Error(), "revoking share")
	assert.NoError(t, mock.ExpectationsWereMet())
}

func TestPostgresShareStoreIncrementAccessError(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close() //nolint:errcheck // test cleanup

	store := NewPostgresShareStore(db)

	mock.ExpectExec("UPDATE portal_shares SET access_count").
		WillReturnError(fmt.Errorf("db error"))

	err = store.IncrementAccess(context.Background(), "share1")
	assert.Error(t, err)
	assert.Contains(t, err.Error(), "incrementing access count")
	assert.NoError(t, mock.ExpectationsWereMet())
}

func TestPostgresShareStoreGetByID(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close() //nolint:errcheck // test cleanup

	store := NewPostgresShareStore(db)
	now := time.Now()

	rows := sqlmock.NewRows([]string{
		"id", "asset_id", "collection_id", "token", "created_by", "shared_with_user_id", "shared_with_email",
		"expires_at", "revoked", "hide_expiration", "notice_text", "access_count", "last_accessed_at", "created_at", "permission",
	}).AddRow("share1", "abc123", nil, "tok123", "user1", "shareduser", nil, nil, false, false, defaultNoticeText, 0, nil, now, "viewer")

	mock.ExpectQuery("SELECT .+ FROM portal_shares WHERE id").
		WithArgs("share1").
		WillReturnRows(rows)

	share, err := store.GetByID(context.Background(), "share1")
	require.NoError(t, err)
	assert.Equal(t, "share1", share.ID)
	assert.Equal(t, "shareduser", share.SharedWithUserID)
	assert.NoError(t, mock.ExpectationsWereMet())
}

func TestPostgresShareStoreGetByIDNotFound(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close() //nolint:errcheck // test cleanup

	store := NewPostgresShareStore(db)

	mock.ExpectQuery("SELECT .+ FROM portal_shares WHERE id").
		WithArgs("missing").
		WillReturnError(fmt.Errorf("sql: no rows in result set"))

	_, err = store.GetByID(context.Background(), "missing")
	assert.Error(t, err)
	assert.NoError(t, mock.ExpectationsWereMet())
}

func TestPostgresShareStoreListSharedWithUser(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close() //nolint:errcheck // test cleanup

	store := NewPostgresShareStore(db)
	now := time.Now()

	tags, _ := json.Marshal([]string{"chart"})
	prov, _ := json.Marshal(Provenance{SessionID: "sess1"})

	// Count query
	mock.ExpectQuery("SELECT COUNT").
		WithArgs("user2", "").
		WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(1))

	// Select query
	dataRows := sqlmock.NewRows([]string{
		"id", "owner_id", "owner_email", "name", "description", "content_type", "s3_bucket", "s3_key",
		"thumbnail_s3_key", "size_bytes", "tags", "provenance", "session_id", "current_version", "created_at", "updated_at", "deleted_at", "idempotency_key",
		"share_id", "created_by", "share_created_at", "permission",
	}).AddRow(
		"abc123", "user1", "user1@example.com", "Shared Asset", "desc", "text/html", "portal", "key1",
		"", int64(512), tags, prov, "sess1", 1, now, now, nil, "",
		"share1", "user1", now, "viewer",
	)

	mock.ExpectQuery("SELECT .+ FROM portal_shares ps").
		WithArgs("user2", "", 10, 0).
		WillReturnRows(dataRows)

	results, total, err := store.ListSharedWithUser(context.Background(), "user2", "", 10, 0)
	require.NoError(t, err)
	assert.Equal(t, 1, total)
	require.Len(t, results, 1)
	assert.Equal(t, "abc123", results[0].Asset.ID)
	assert.Equal(t, "share1", results[0].ShareID)
	assert.Equal(t, "user1", results[0].SharedBy)
	assert.Equal(t, []string{"chart"}, results[0].Asset.Tags)
	assert.NoError(t, mock.ExpectationsWereMet())
}

func TestPostgresShareStoreListSharedWithUserCountError(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close() //nolint:errcheck // test cleanup

	store := NewPostgresShareStore(db)

	mock.ExpectQuery("SELECT COUNT").
		WithArgs("user2", "").
		WillReturnError(fmt.Errorf("db error"))

	_, _, err = store.ListSharedWithUser(context.Background(), "user2", "", 10, 0)
	assert.Error(t, err)
	assert.Contains(t, err.Error(), "counting shared assets")
	assert.NoError(t, mock.ExpectationsWereMet())
}

func TestPostgresShareStoreListSharedWithUserQueryError(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close() //nolint:errcheck // test cleanup

	store := NewPostgresShareStore(db)

	mock.ExpectQuery("SELECT COUNT").
		WithArgs("user2", "").
		WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(1))

	mock.ExpectQuery("SELECT .+ FROM portal_shares ps").
		WithArgs("user2", "", 10, 0).
		WillReturnError(fmt.Errorf("db error"))

	_, _, err = store.ListSharedWithUser(context.Background(), "user2", "", 10, 0)
	assert.Error(t, err)
	assert.Contains(t, err.Error(), "querying shared assets")
	assert.NoError(t, mock.ExpectationsWereMet())
}

func TestPostgresShareStoreListSharedWithUserDefaults(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close() //nolint:errcheck // test cleanup

	store := NewPostgresShareStore(db)

	// Count query
	mock.ExpectQuery("SELECT COUNT").
		WithArgs("user2", "").
		WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(0))

	// Select query — limit defaults to 50, offset 0
	mock.ExpectQuery("SELECT .+ FROM portal_shares ps").
		WithArgs("user2", "", defaultLimit, 0).
		WillReturnRows(sqlmock.NewRows([]string{
			"id", "owner_id", "owner_email", "name", "description", "content_type", "s3_bucket", "s3_key",
			"thumbnail_s3_key", "size_bytes", "tags", "provenance", "session_id", "current_version", "created_at", "updated_at", "deleted_at", "idempotency_key",
			"share_id", "created_by", "share_created_at", "permission",
		}))

	results, total, err := store.ListSharedWithUser(context.Background(), "user2", "", 0, 0)
	require.NoError(t, err)
	assert.Equal(t, 0, total)
	assert.Empty(t, results)
	assert.NoError(t, mock.ExpectationsWereMet())
}

func TestPostgresShareStoreListSharedWithUserMaxLimit(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close() //nolint:errcheck // test cleanup

	store := NewPostgresShareStore(db)

	mock.ExpectQuery("SELECT COUNT").
		WithArgs("user2", "").
		WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(0))

	mock.ExpectQuery("SELECT .+ FROM portal_shares ps").
		WithArgs("user2", "", maxLimit, 0).
		WillReturnRows(sqlmock.NewRows([]string{
			"id", "owner_id", "owner_email", "name", "description", "content_type", "s3_bucket", "s3_key",
			"thumbnail_s3_key", "size_bytes", "tags", "provenance", "session_id", "current_version", "created_at", "updated_at", "deleted_at", "idempotency_key",
			"share_id", "created_by", "share_created_at", "permission",
		}))

	results, _, err := store.ListSharedWithUser(context.Background(), "user2", "", 9999, 0)
	require.NoError(t, err)
	assert.Empty(t, results)
	assert.NoError(t, mock.ExpectationsWereMet())
}

func TestPostgresShareStoreInsertWithSharedWithUser(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close() //nolint:errcheck // test cleanup

	store := NewPostgresShareStore(db)

	share := Share{
		ID:               "share1",
		AssetID:          "abc123",
		Token:            "tok123",
		CreatedBy:        "user1",
		SharedWithUserID: "user2",
	}

	mock.ExpectExec("INSERT INTO portal_shares").
		WithArgs(share.ID, sqlmock.AnyArg(), sqlmock.AnyArg(), share.Token, share.CreatedBy, sqlmock.AnyArg(), sqlmock.AnyArg(), sqlmock.AnyArg(), share.HideExpiration, share.NoticeText, "viewer").
		WillReturnResult(sqlmock.NewResult(0, 1))

	err = store.Insert(context.Background(), share)
	assert.NoError(t, err)
	assert.NoError(t, mock.ExpectationsWereMet())
}

func TestPostgresShareStoreGetByTokenNotFound(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close() //nolint:errcheck // test cleanup

	store := NewPostgresShareStore(db)

	mock.ExpectQuery("SELECT .+ FROM portal_shares WHERE token").
		WithArgs("missing").
		WillReturnError(fmt.Errorf("sql: no rows in result set"))

	_, err = store.GetByToken(context.Background(), "missing")
	assert.Error(t, err)
	assert.NoError(t, mock.ExpectationsWereMet())
}

func TestPostgresShareStoreGetByTokenWithExpiration(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close() //nolint:errcheck // test cleanup

	store := NewPostgresShareStore(db)
	now := time.Now()
	expires := now.Add(24 * time.Hour)

	rows := sqlmock.NewRows([]string{
		"id", "asset_id", "collection_id", "token", "created_by", "shared_with_user_id", "shared_with_email",
		"expires_at", "revoked", "hide_expiration", "notice_text", "access_count", "last_accessed_at", "created_at", "permission",
	}).AddRow("share1", "abc123", nil, "tok123", "user1", nil, nil, expires, false, false, defaultNoticeText, 0, nil, now, "viewer")

	mock.ExpectQuery("SELECT .+ FROM portal_shares WHERE token").
		WithArgs("tok123").
		WillReturnRows(rows)

	share, err := store.GetByToken(context.Background(), "tok123")
	require.NoError(t, err)
	assert.NotNil(t, share.ExpiresAt)
	assert.Nil(t, share.LastAccessedAt)
	assert.NoError(t, mock.ExpectationsWereMet())
}

func TestPostgresAssetStoreGetWithDeletedAt(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close() //nolint:errcheck // test cleanup

	store := NewPostgresAssetStore(db)
	now := time.Now()
	deletedAt := now.Add(-1 * time.Hour)

	tags, _ := json.Marshal([]string{})
	prov, _ := json.Marshal(Provenance{})

	rows := sqlmock.NewRows([]string{
		"id", "owner_id", "owner_email", "name", "description", "content_type", "s3_bucket", "s3_key",
		"thumbnail_s3_key", "size_bytes", "tags", "provenance", "session_id", "current_version", "created_at", "updated_at", "deleted_at", "idempotency_key",
	}).AddRow(
		"abc123", "user1", "", "Test", "desc", "text/html", "portal", "key1",
		"", int64(512), tags, prov, "sess1", 1, now, now, deletedAt, "",
	)

	mock.ExpectQuery("SELECT .+ FROM portal_assets WHERE id").
		WithArgs("abc123").
		WillReturnRows(rows)

	asset, err := store.Get(context.Background(), "abc123")
	require.NoError(t, err)
	assert.NotNil(t, asset.DeletedAt)
	assert.NoError(t, mock.ExpectationsWereMet())
}

// --- ListActiveShareSummaries tests ---

func TestPostgresShareStoreListActiveShareSummaries(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close() //nolint:errcheck // test cleanup

	store := NewPostgresShareStore(db)

	rows := sqlmock.NewRows([]string{"asset_id", "has_user_share", "has_public_link"}).
		AddRow("a1", true, false).
		AddRow("a2", false, true)

	mock.ExpectQuery("SELECT asset_id").
		WithArgs(sqlmock.AnyArg()).
		WillReturnRows(rows)

	result, err := store.ListActiveShareSummaries(context.Background(), []string{"a1", "a2", "a3"})
	require.NoError(t, err)
	assert.Len(t, result, 2)
	assert.True(t, result["a1"].HasUserShare)
	assert.False(t, result["a1"].HasPublicLink)
	assert.False(t, result["a2"].HasUserShare)
	assert.True(t, result["a2"].HasPublicLink)
	assert.NoError(t, mock.ExpectationsWereMet())
}

func TestPostgresShareStoreListActiveShareSummariesEmpty(t *testing.T) {
	db, _, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close() //nolint:errcheck // test cleanup

	store := NewPostgresShareStore(db)

	result, err := store.ListActiveShareSummaries(context.Background(), []string{})
	require.NoError(t, err)
	assert.Empty(t, result)
	// No query should be executed for empty input
}

func TestPostgresShareStoreListActiveShareSummariesQueryError(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close() //nolint:errcheck // test cleanup

	store := NewPostgresShareStore(db)

	mock.ExpectQuery("SELECT asset_id").
		WithArgs(sqlmock.AnyArg()).
		WillReturnError(fmt.Errorf("db error"))

	_, err = store.ListActiveShareSummaries(context.Background(), []string{"a1"})
	assert.Error(t, err)
	assert.Contains(t, err.Error(), "querying share summaries")
	assert.NoError(t, mock.ExpectationsWereMet())
}

func TestPostgresShareStoreListActiveShareSummariesScanError(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close() //nolint:errcheck // test cleanup

	store := NewPostgresShareStore(db)

	// Return a row with wrong column type to trigger scan error
	rows := sqlmock.NewRows([]string{"asset_id", "has_user_share", "has_public_link"}).
		AddRow("a1", "not-a-bool", false)

	mock.ExpectQuery("SELECT asset_id").
		WithArgs(sqlmock.AnyArg()).
		WillReturnRows(rows)

	_, err = store.ListActiveShareSummaries(context.Background(), []string{"a1"})
	assert.Error(t, err)
	assert.Contains(t, err.Error(), "scanning share summary row")
	assert.NoError(t, mock.ExpectationsWereMet())
}

func TestPostgresShareStoreListActiveShareSummariesRowsErr(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close() //nolint:errcheck // test cleanup

	store := NewPostgresShareStore(db)

	rows := sqlmock.NewRows([]string{"asset_id", "has_user_share", "has_public_link"}).
		AddRow("a1", true, false).
		RowError(0, fmt.Errorf("row iteration error"))

	mock.ExpectQuery("SELECT asset_id").
		WithArgs(sqlmock.AnyArg()).
		WillReturnRows(rows)

	_, err = store.ListActiveShareSummaries(context.Background(), []string{"a1"})
	assert.Error(t, err)
	assert.Contains(t, err.Error(), "iterating share summary rows")
	assert.NoError(t, mock.ExpectationsWereMet())
}

// --- VersionStore tests ---

func TestPostgresVersionStoreCreateVersion(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close() //nolint:errcheck // test cleanup

	store := NewPostgresVersionStore(db)

	version := AssetVersion{
		ID:            "v1",
		AssetID:       "abc123",
		Version:       2,
		S3Key:         "user1/abc123/v2/content.html",
		S3Bucket:      "portal",
		ContentType:   "text/html",
		SizeBytes:     2048,
		CreatedBy:     "user1",
		ChangeSummary: "Updated content",
	}

	mock.ExpectBegin()
	mock.ExpectQuery("SELECT current_version FROM portal_assets").
		WithArgs("abc123").
		WillReturnRows(sqlmock.NewRows([]string{"current_version"}).AddRow(1))
	mock.ExpectExec("INSERT INTO portal_asset_versions").
		WithArgs(version.ID, version.AssetID, version.Version, version.S3Key, version.S3Bucket,
			version.ContentType, version.SizeBytes, version.CreatedBy, version.ChangeSummary).
		WillReturnResult(sqlmock.NewResult(0, 1))
	mock.ExpectExec("UPDATE portal_assets").
		WithArgs(version.Version, version.S3Key, version.ContentType, version.SizeBytes, version.AssetID).
		WillReturnResult(sqlmock.NewResult(0, 1))
	mock.ExpectCommit()

	assignedVersion, err := store.CreateVersion(context.Background(), version)
	assert.NoError(t, err)
	assert.Equal(t, 2, assignedVersion)
	assert.NoError(t, mock.ExpectationsWereMet())
}

func TestPostgresVersionStoreCreateVersionInsertError(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close() //nolint:errcheck // test cleanup

	store := NewPostgresVersionStore(db)

	mock.ExpectBegin()
	mock.ExpectQuery("SELECT current_version FROM portal_assets").
		WithArgs("").
		WillReturnRows(sqlmock.NewRows([]string{"current_version"}).AddRow(0))
	mock.ExpectExec("INSERT INTO portal_asset_versions").
		WillReturnError(fmt.Errorf("db error"))
	mock.ExpectRollback()

	_, err = store.CreateVersion(context.Background(), AssetVersion{})
	assert.Error(t, err)
	assert.Contains(t, err.Error(), "inserting version")
	assert.NoError(t, mock.ExpectationsWereMet())
}

func TestPostgresVersionStoreCreateVersionUpdateError(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close() //nolint:errcheck // test cleanup

	store := NewPostgresVersionStore(db)

	mock.ExpectBegin()
	mock.ExpectQuery("SELECT current_version FROM portal_assets").
		WithArgs("").
		WillReturnRows(sqlmock.NewRows([]string{"current_version"}).AddRow(0))
	mock.ExpectExec("INSERT INTO portal_asset_versions").
		WillReturnResult(sqlmock.NewResult(0, 1))
	mock.ExpectExec("UPDATE portal_assets").
		WillReturnError(fmt.Errorf("db error"))
	mock.ExpectRollback()

	_, err = store.CreateVersion(context.Background(), AssetVersion{})
	assert.Error(t, err)
	assert.Contains(t, err.Error(), "updating asset version")
	assert.NoError(t, mock.ExpectationsWereMet())
}

func TestPostgresVersionStoreCreateVersionBeginError(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close() //nolint:errcheck // test cleanup

	store := NewPostgresVersionStore(db)

	mock.ExpectBegin().WillReturnError(fmt.Errorf("db error"))

	_, err = store.CreateVersion(context.Background(), AssetVersion{})
	assert.Error(t, err)
	assert.Contains(t, err.Error(), "beginning transaction")
	assert.NoError(t, mock.ExpectationsWereMet())
}

func TestPostgresVersionStoreListByAsset(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close() //nolint:errcheck // test cleanup

	store := NewPostgresVersionStore(db)
	now := time.Now()

	mock.ExpectQuery("SELECT COUNT").
		WithArgs("abc123").
		WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(2))

	dataRows := sqlmock.NewRows([]string{
		"id", "asset_id", "version", "s3_key", "s3_bucket", "content_type",
		"size_bytes", "created_by", "change_summary", "created_at",
	}).
		AddRow("v2", "abc123", 2, "key2", "portal", "text/html", int64(2048), "user1", "v2 changes", now).
		AddRow("v1", "abc123", 1, "key1", "portal", "text/html", int64(1024), "user1", "initial", now)

	mock.ExpectQuery("SELECT .+ FROM portal_asset_versions").
		WithArgs("abc123", 10, 0).
		WillReturnRows(dataRows)

	versions, total, err := store.ListByAsset(context.Background(), "abc123", 10, 0)
	require.NoError(t, err)
	assert.Equal(t, 2, total)
	require.Len(t, versions, 2)
	assert.Equal(t, 2, versions[0].Version)
	assert.Equal(t, 1, versions[1].Version)
	assert.NoError(t, mock.ExpectationsWereMet())
}

func TestPostgresVersionStoreListByAssetCountError(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close() //nolint:errcheck // test cleanup

	store := NewPostgresVersionStore(db)

	mock.ExpectQuery("SELECT COUNT").
		WithArgs("abc123").
		WillReturnError(fmt.Errorf("db error"))

	_, _, err = store.ListByAsset(context.Background(), "abc123", 10, 0)
	assert.Error(t, err)
	assert.Contains(t, err.Error(), "counting versions")
	assert.NoError(t, mock.ExpectationsWereMet())
}

func TestPostgresVersionStoreListByAssetQueryError(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close() //nolint:errcheck // test cleanup

	store := NewPostgresVersionStore(db)

	mock.ExpectQuery("SELECT COUNT").
		WithArgs("abc123").
		WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(1))

	mock.ExpectQuery("SELECT .+ FROM portal_asset_versions").
		WithArgs("abc123", 10, 0).
		WillReturnError(fmt.Errorf("db error"))

	_, _, err = store.ListByAsset(context.Background(), "abc123", 10, 0)
	assert.Error(t, err)
	assert.Contains(t, err.Error(), "querying versions")
	assert.NoError(t, mock.ExpectationsWereMet())
}

func TestPostgresVersionStoreListByAssetDefaults(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close() //nolint:errcheck // test cleanup

	store := NewPostgresVersionStore(db)

	mock.ExpectQuery("SELECT COUNT").
		WithArgs("abc123").
		WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(0))

	mock.ExpectQuery("SELECT .+ FROM portal_asset_versions").
		WithArgs("abc123", defaultLimit, 0).
		WillReturnRows(sqlmock.NewRows([]string{
			"id", "asset_id", "version", "s3_key", "s3_bucket", "content_type",
			"size_bytes", "created_by", "change_summary", "created_at",
		}))

	versions, total, err := store.ListByAsset(context.Background(), "abc123", 0, 0)
	require.NoError(t, err)
	assert.Equal(t, 0, total)
	assert.Empty(t, versions)
	assert.NoError(t, mock.ExpectationsWereMet())
}

func TestPostgresVersionStoreGetByVersion(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close() //nolint:errcheck // test cleanup

	store := NewPostgresVersionStore(db)
	now := time.Now()

	rows := sqlmock.NewRows([]string{
		"id", "asset_id", "version", "s3_key", "s3_bucket", "content_type",
		"size_bytes", "created_by", "change_summary", "created_at",
	}).AddRow("v1", "abc123", 1, "key1", "portal", "text/html", int64(1024), "user1", "initial", now)

	mock.ExpectQuery("SELECT .+ FROM portal_asset_versions").
		WithArgs("abc123", 1).
		WillReturnRows(rows)

	v, err := store.GetByVersion(context.Background(), "abc123", 1)
	require.NoError(t, err)
	assert.Equal(t, "v1", v.ID)
	assert.Equal(t, 1, v.Version)
	assert.Equal(t, "abc123", v.AssetID)
	assert.Equal(t, "initial", v.ChangeSummary)
	assert.NoError(t, mock.ExpectationsWereMet())
}

func TestPostgresVersionStoreGetByVersionNotFound(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close() //nolint:errcheck // test cleanup

	store := NewPostgresVersionStore(db)

	mock.ExpectQuery("SELECT .+ FROM portal_asset_versions").
		WithArgs("abc123", 99).
		WillReturnError(fmt.Errorf("sql: no rows in result set"))

	_, err = store.GetByVersion(context.Background(), "abc123", 99)
	assert.Error(t, err)
	assert.Contains(t, err.Error(), "querying version")
	assert.NoError(t, mock.ExpectationsWereMet())
}

func TestPostgresVersionStoreGetLatest(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close() //nolint:errcheck // test cleanup

	store := NewPostgresVersionStore(db)
	now := time.Now()

	rows := sqlmock.NewRows([]string{
		"id", "asset_id", "version", "s3_key", "s3_bucket", "content_type",
		"size_bytes", "created_by", "change_summary", "created_at",
	}).AddRow("v3", "abc123", 3, "key3", "portal", "text/html", int64(4096), "user1", "latest changes", now)

	mock.ExpectQuery("SELECT .+ FROM portal_asset_versions").
		WithArgs("abc123").
		WillReturnRows(rows)

	v, err := store.GetLatest(context.Background(), "abc123")
	require.NoError(t, err)
	assert.Equal(t, "v3", v.ID)
	assert.Equal(t, 3, v.Version)
	assert.Equal(t, "latest changes", v.ChangeSummary)
	assert.NoError(t, mock.ExpectationsWereMet())
}

func TestPostgresVersionStoreGetLatestNotFound(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close() //nolint:errcheck // test cleanup

	store := NewPostgresVersionStore(db)

	mock.ExpectQuery("SELECT .+ FROM portal_asset_versions").
		WithArgs("abc123").
		WillReturnError(fmt.Errorf("sql: no rows in result set"))

	_, err = store.GetLatest(context.Background(), "abc123")
	assert.Error(t, err)
	assert.Contains(t, err.Error(), "querying latest version")
	assert.NoError(t, mock.ExpectationsWereMet())
}

func TestNoopVersionStore(t *testing.T) {
	store := NewNoopVersionStore()
	ctx := context.Background()

	v, err := store.CreateVersion(ctx, AssetVersion{})
	assert.NoError(t, err)
	assert.Equal(t, 0, v)

	versions, total, err := store.ListByAsset(ctx, "any", 10, 0)
	assert.NoError(t, err)
	assert.Nil(t, versions)
	assert.Equal(t, 0, total)

	_, err = store.GetByVersion(ctx, "any", 1)
	assert.Error(t, err)

	_, err = store.GetLatest(ctx, "any")
	assert.Error(t, err)
}
