query.go

package thing

import (
	"context"
	"errors"
	"fmt"
	"strconv"

	log "github.com/burugo/thing/internal/logging"

	"github.com/burugo/thing/common"
	"github.com/burugo/thing/internal/cache"
	"github.com/burugo/thing/internal/types"
)

const (
	// Max number of IDs to cache per query list
	cacheListCountLimit = 200
)

// QueryParams is the public query parameter type for Thing ORM queries.
type QueryParams struct {
	Where          string
	Args           []interface{}
	Order          string
	Preloads       []string
	IncludeDeleted bool
}

// toInternalQueryParams converts a public QueryParams to internal/types.QueryParams.
func toInternalQueryParams(p QueryParams) types.QueryParams {
	return types.QueryParams{
		Where:          p.Where,
		Args:           p.Args,
		Order:          p.Order,
		Preloads:       p.Preloads,
		IncludeDeleted: p.IncludeDeleted,
	}
}

// CachedResult represents a cached query result with lazy loading capabilities.
// It allows for efficient querying with pagination and caching.
type CachedResult[T Model] struct {
	thing          *Thing[T]
	params         QueryParams
	cachedIDs      []int64
	cachedCount    int64
	hasLoadedIDs   bool
	hasLoadedCount bool
	hasLoadedAll   bool
	all            []T
	Err            error // New: holds error if Query failed to initialize

	// Memoized cache keys for this query's immutable params. Generating a key
	// hashes the params (NormalizeValue + json.Marshal + FNV), and a single
	// Fetch/Count triggers several key lookups, so we compute each once per
	// CachedResult. Cleared whenever params change (see the chainable methods).
	listKey         string
	countKey        string
	preciseCountKey string
}

// resetKeyCache clears memoized cache keys after a params change so they are
// regenerated from the new params on next use.
func (cr *CachedResult[T]) resetKeyCache() {
	cr.listKey = ""
	cr.countKey = ""
	cr.preciseCountKey = ""
}

// --- Thing Method for Querying ---

// Query prepares a query based on QueryParams and returns a *CachedResult[T] for lazy execution.
// The actual database query happens when Count() or Fetch() is called on the result.
// Error handling for query execution is done within CachedResult methods.
func (t *Thing[T]) Query(params QueryParams) *CachedResult[T] {
	// TODO: Add validation for params if necessary?
	return &CachedResult[T]{
		thing:  t,
		params: params,
		// cachedIDs, cachedCount, hasLoadedIDs, hasLoadedCount, hasLoadedAll, all initialized to zero values
		Err: nil,
	}
}

// All is a convenience method to query and fetch all records matching the default QueryParams.
// It's equivalent to calling thingInstance.Query(QueryParams{}).All().
func (t *Thing[T]) All() ([]T, error) {
	// Simply call Query with empty params and then All() on the result.
	// The CachedResult.All() method handles initialization checks and error propagation.
	return t.Query(QueryParams{}).All()
}

// --- CachedResult Methods ---

// Helper function to generate cache key for count queries.
// Similar to generateQueryCacheKey but with a different prefix.
func (cr *CachedResult[T]) generateCountCacheKey() string {
	if cr.countKey == "" {
		cr.countKey = GenerateCacheKey("count", cr.thing.info.TableName, cr.params)
	}
	return cr.countKey
}

// Helper function to generate cache key for precise count queries.
// Precise counts apply KeepItem filtering and therefore use a distinct prefix
// so they never overwrite the approximate (SQL-only) count cache.
func (cr *CachedResult[T]) generatePreciseCountCacheKey() string {
	if cr.preciseCountKey == "" {
		cr.preciseCountKey = GenerateCacheKey("count_precise", cr.thing.info.TableName, cr.params)
	}
	return cr.preciseCountKey
}

// Helper function to generate cache key for list queries.
func (cr *CachedResult[T]) generateListCacheKey() string {
	if cr.listKey == "" {
		cr.listKey = GenerateCacheKey("list", cr.thing.info.TableName, cr.params)
	}
	return cr.listKey
}

// Count returns the total number of records matching the query.
// It utilizes caching to avoid redundant database calls.
func (cr *CachedResult[T]) Count() (int64, error) {
	if cr.thing != nil && cr.thing.inTransaction() {
		return cr.countFromExecutor(cr.thing.ctx, cr.thing.txExecutor())
	}
	if cr.thing == nil || cr.thing.cache == nil || cr.thing.db == nil {
		return 0, errors.New("Count: CachedResult not properly initialized")
	}

	// 1. Check if count is already loaded in memory
	if cr.hasLoadedCount {
		return cr.cachedCount, nil
	}

	// 2. Generate cache key
	cacheKey := cr.generateCountCacheKey()

	// 3. Check cache (using a generic Get method, assuming it returns string)
	cacheValStr, cacheErr := cr.thing.cache.Get(cr.thing.ctx, cacheKey)
	if cacheErr == nil {
		count, convErr := strconv.ParseInt(cacheValStr, 10, 64)
		if convErr == nil {
			cr.cachedCount = count
			cr.hasLoadedCount = true
			return count, nil
		} else {
			log.Warnf("invalid count value found in cache for key %s: %s: %v", cacheKey, cacheValStr, convErr)
			_ = cr.thing.cache.Delete(cr.thing.ctx, cacheKey)
		}
	}
	// Cache miss or error — fall through to DB fetch

	listCacheKey := cr.generateListCacheKey()
	if cachedIDs, idsErr := cr.thing.cache.GetQueryIDs(cr.thing.ctx, listCacheKey); idsErr == nil && len(cachedIDs) < cacheListCountLimit {
		count := int64(len(cachedIDs))
		cr.cachedCount = count
		cr.hasLoadedCount = true
		cacheSetErr := cr.thing.cache.Set(cr.thing.ctx, cacheKey, strconv.FormatInt(count, 10), getGlobalCacheTTL())
		if cacheSetErr != nil {
			log.Warnf("failed to cache count from list cache for key %s: %v", cacheKey, cacheSetErr)
		}
		cache.GlobalCacheIndex.RegisterQuery(cr.thing.info.TableName, cacheKey, toInternalQueryParams(cr.params))
		return count, nil
	} else if idsErr != nil && !errors.Is(idsErr, common.ErrNotFound) && !errors.Is(idsErr, common.ErrQueryCacheNoneResult) {
		log.Warnf("cache GetQueryIDs error for list key %s while deriving count: %v", listCacheKey, idsErr)
	}

	// 4. Cache miss or error, query database
	// Assuming DBAdapter has a GetCount method
	// Add the soft delete condition implicitly here, *unless* IncludeDeleted is true
	countParams := cr.params
	if !countParams.IncludeDeleted { // Check the flag
		if countParams.Where != "" {
			countParams.Where = fmt.Sprintf("(%s) AND \"deleted\" = false", countParams.Where)
		} else {
			countParams.Where = "\"deleted\" = false"
		}
	}
	dbCount, dbErr := cr.thing.db.GetCount(cr.thing.ctx, cr.thing.info.TableName, countParams.Where, countParams.Args)
	if dbErr != nil {
		log.Errorf("count query failed: %v", dbErr)
		return 0, fmt.Errorf("database count query failed: %w", dbErr)
	}

	// 5. Store result in memory and cache
	cr.cachedCount = dbCount
	cr.hasLoadedCount = true

	cacheSetErr := cr.thing.cache.Set(cr.thing.ctx, cacheKey, strconv.FormatInt(dbCount, 10), getGlobalCacheTTL())
	if cacheSetErr != nil {
		log.Warnf("failed to cache count for key %s: %v", cacheKey, cacheSetErr)
	}

	// Register the count key
	cache.GlobalCacheIndex.RegisterQuery(cr.thing.info.TableName, cacheKey, toInternalQueryParams(cr.params))

	return cr.cachedCount, nil
}

// CountPrecise returns the exact number of records matching the query after
// applying the model's KeepItem filter.
//
// For models that do not override KeepItem, this is identical to Count() and
// reuses the same fast path. For models with custom KeepItem logic that SQL
// cannot express, CountPrecise loads matching IDs and filters them with
// KeepItem, which is more expensive on large result sets. Its result is cached
// under a dedicated key so it never overwrites the approximate Count() cache.
func (cr *CachedResult[T]) CountPrecise() (int64, error) {
	if cr.thing == nil || cr.thing.cache == nil || cr.thing.db == nil {
		return 0, errors.New("CountPrecise: CachedResult not properly initialized")
	}

	// Default models: precise == approximate. Reuse the cheaper Count() path.
	if !cr.thing.info.HasCustomKeepItem {
		return cr.Count()
	}

	preciseKey := cr.generatePreciseCountCacheKey()

	// 1. Check the precise count cache.
	if valStr, err := cr.thing.cache.Get(cr.thing.ctx, preciseKey); err == nil {
		if count, convErr := strconv.ParseInt(valStr, 10, 64); convErr == nil {
			return count, nil
		}
		log.Warnf("invalid precise count value in cache for key %s: %s", preciseKey, valStr)
		_ = cr.thing.cache.Delete(cr.thing.ctx, preciseKey)
	} else if !errors.Is(err, common.ErrNotFound) {
		log.Warnf("cache Get error for precise count key %s: %v", preciseKey, err)
	}

	// 2. If the list cache holds the complete (KeepItem-filtered) result set, its
	//    length is the exact count.
	listCacheKey := cr.generateListCacheKey()
	if cachedIDs, idsErr := cr.thing.cache.GetQueryIDs(cr.thing.ctx, listCacheKey); idsErr == nil && len(cachedIDs) < cacheListCountLimit {
		return cr.cachePreciseCount(preciseKey, int64(len(cachedIDs)))
	} else if idsErr != nil && !errors.Is(idsErr, common.ErrNotFound) && !errors.Is(idsErr, common.ErrQueryCacheNoneResult) {
		log.Warnf("cache GetQueryIDs error for list key %s while deriving precise count: %v", listCacheKey, idsErr)
	}

	// 3. Full scan: page through all matching IDs and count those kept by KeepItem.
	count, err := cr.countByFullScan()
	if err != nil {
		return 0, err
	}
	return cr.cachePreciseCount(preciseKey, count)
}

// cachePreciseCount stores the precise count under its dedicated key and
// registers it for invalidation.
func (cr *CachedResult[T]) cachePreciseCount(preciseKey string, count int64) (int64, error) {
	if err := cr.thing.cache.Set(cr.thing.ctx, preciseKey, strconv.FormatInt(count, 10), getGlobalCacheTTL()); err != nil {
		log.Warnf("failed to cache precise count for key %s: %v", preciseKey, err)
	}
	cache.GlobalCacheIndex.RegisterQuery(cr.thing.info.TableName, preciseKey, toInternalQueryParams(cr.params))
	return count, nil
}

// countByFullScan pages through all matching IDs and counts those retained by
// KeepItem. Unlike _fetch_data it does not stop at cacheListCountLimit, so the
// count stays exact for large result sets.
func (cr *CachedResult[T]) countByFullScan() (int64, error) {
	var count int64
	currentOffset := 0
	const batchSize = 500

	for {
		batchIDs, dbErr := cr._fetch_ids_from_db(currentOffset, batchSize)
		if dbErr != nil {
			return 0, fmt.Errorf("CountPrecise: failed to fetch IDs from database: %w", dbErr)
		}
		if len(batchIDs) == 0 {
			break
		}

		models, modelsErr := cr.thing.ByIDs(batchIDs)
		if modelsErr != nil {
			return 0, fmt.Errorf("CountPrecise: failed to fetch models for IDs: %w", modelsErr)
		}

		for _, id := range batchIDs {
			model, found := models[id]
			if !found {
				continue
			}
			if cr.params.IncludeDeleted || model.KeepItem() {
				count++
			}
		}

		currentOffset += len(batchIDs)
		if len(batchIDs) < batchSize {
			break
		}
	}
	return count, nil
}

// WithDeleted returns a new CachedResult instance that will include
// soft-deleted records in its results.
func (cr *CachedResult[T]) WithDeleted() *CachedResult[T] {
	// Create a shallow copy of the original CachedResult
	newCr := *cr
	// Copy the params to avoid modifying the original
	newParams := cr.params
	newParams.IncludeDeleted = true
	// Set the modified params on the new CachedResult
	newCr.params = newParams
	// Reset loaded state flags, as the query parameters have changed
	newCr.hasLoadedCount = false
	newCr.hasLoadedIDs = false
	newCr.cachedIDs = nil
	newCr.cachedCount = 0
	newCr.hasLoadedAll = false
	newCr.all = nil
	newCr.resetKeyCache()

	return &newCr
}

// _fetch ensures that the list of IDs matching the query is loaded, either from cache or DB.
func (cr *CachedResult[T]) _fetch() error {
	if cr.hasLoadedIDs {
		return nil // Already loaded
	}

	ids, err := cr._fetch_data()
	if err != nil {
		return err // Propagate error from data fetching
	}

	cr.cachedIDs = ids
	cr.hasLoadedIDs = true
	return nil
}

// _fetch_ids_from_db fetches IDs from the database with pagination support.
// It accepts offset and limit parameters to enable proper pagination.
func (cr *CachedResult[T]) _fetch_ids_from_db(offset, limit int) ([]int64, error) {
	if cr.thing == nil || cr.thing.db == nil || cr.thing.info == nil {
		return nil, errors.New("_fetch_ids_from_db: CachedResult not properly initialized")
	}
	return cr.fetchIDsFromExecutor(cr.thing.ctx, cr.thing.db, offset, limit)
}

func (cr *CachedResult[T]) fetchIDsFromExecutor(ctx context.Context, executor interface {
	Select(context.Context, interface{}, string, ...interface{}) error
}, offset, limit int,
) ([]int64, error) {
	if cr.thing == nil || cr.thing.db == nil || cr.thing.info == nil {
		return nil, errors.New("fetchIDsFromExecutor: CachedResult not properly initialized")
	}
	if executor == nil {
		return nil, errors.New("fetchIDsFromExecutor: executor is nil")
	}

	// Always handle soft delete at the query layer
	where := cr.params.Where
	if !cr.params.IncludeDeleted {
		if where != "" {
			where = fmt.Sprintf("(%s) AND \"deleted\" = false", where)
		} else {
			where = "\"deleted\" = false"
		}
	}
	query, args := cr.thing.db.Builder().BuildSelectIDsSQL(cr.thing.info.TableName, cr.thing.info.PkName, where, cr.params.Args, cr.params.Order)
	queryWithPagination := fmt.Sprintf("%s LIMIT %d OFFSET %d", query, limit, offset)

	// Execute the query
	var fetchedIDs []int64
	dbErr := executor.Select(ctx, &fetchedIDs, queryWithPagination, args...)
	if dbErr != nil {
		log.Errorf("fetching IDs with offset %d, limit %d failed: %v", offset, limit, dbErr)
		return nil, fmt.Errorf("database query for IDs with offset %d, limit %d failed: %w", offset, limit, dbErr)
	}

	log.Debugf("fetched %d IDs with offset %d, limit %d", len(fetchedIDs), offset, limit)
	return fetchedIDs, nil
}

// _fetch_data attempts to load up to `cacheListCountLimit` valid IDs from cache or database.
// It filters out soft-deleted items before caching.
func (cr *CachedResult[T]) _fetch_data() ([]int64, error) {
	if cr.thing == nil || cr.thing.cache == nil || cr.thing.db == nil {
		return nil, errors.New("_fetch_data: CachedResult not properly initialized")
	}

	// 1. Generate List Cache Key
	listCacheKey := cr.generateListCacheKey()

	// 2. Check Cache directly using GetQueryIDs
	cachedIDs, idsCacheErr := cr.thing.cache.GetQueryIDs(cr.thing.ctx, listCacheKey)
	if idsCacheErr == nil {
		return cachedIDs, nil // Cache hit with actual IDs or empty slice
	}

	// 3. Handle Cache Miss or Error
	if errors.Is(idsCacheErr, common.ErrNotFound) || errors.Is(idsCacheErr, common.ErrQueryCacheNoneResult) {
		// Normal cache miss or explicit none result found.
	} else { // Handle unexpected errors
		// Log unexpected errors but treat as cache miss
		log.Warnf("cache GetQueryIDs error for list key %s: %v", listCacheKey, idsCacheErr)
	}

	// 4. Cache Miss: Query Database and filter results
	// 4a. Prepare to collect valid, non-deleted IDs
	validIDs := make([]int64, 0, cacheListCountLimit)
	currentOffset := 0
	batchSize := int(float64(cacheListCountLimit) * 1.5) // Larger batch for efficiency

	// Models without a custom KeepItem() are fully filtered by the SQL
	// "deleted" = false clause already applied in _fetch_ids_from_db, so the
	// per-row KeepItem() check is redundant. In that case we can collect IDs
	// directly and skip loading every model just to filter.
	skipKeepItemFilter := !cr.thing.info.HasCustomKeepItem && !cr.params.IncludeDeleted

	// 4b. Loop until we have enough IDs or no more results, with max iteration protection
	const maxIterations = 20 // Prevent excessive looping when many records are soft-deleted
	iterationCount := 0
	scanComplete := false

	for len(validIDs) < cacheListCountLimit {
		iterationCount++
		if iterationCount > maxIterations {
			log.Warnf("reached maximum number of iterations (%d) in _fetch_data, returning %d valid IDs found so far",
				maxIterations, len(validIDs))
			break
		}

		// Fetch a batch of IDs from DB
		batchIDs, dbErr := cr._fetch_ids_from_db(currentOffset, batchSize)
		if dbErr != nil {
			return nil, fmt.Errorf("failed to fetch IDs from database: %w", dbErr)
		}

		// If no more IDs, break
		if len(batchIDs) == 0 {
			scanComplete = true
			break
		}

		if skipKeepItemFilter {
			// No custom KeepItem: SQL already excluded soft-deleted rows, so
			// every fetched ID is valid. Avoid loading the models here.
			for _, id := range batchIDs {
				if len(validIDs) >= cacheListCountLimit {
					break
				}
				validIDs = append(validIDs, id)
			}
		} else {
			// Get models for these IDs to check KeepItem()
			models, modelsErr := cr.thing.ByIDs(batchIDs)
			if modelsErr != nil {
				log.Warnf("failed to fetch models for IDs: %v", modelsErr)
				// Continue with next batch
				currentOffset += len(batchIDs)
				continue
			}

			// Filter models based on KeepItem()
			for _, id := range batchIDs {
				if len(validIDs) >= cacheListCountLimit {
					break
				}
				model, found := models[id]
				if !found {
					continue
				}
				// Only filter out soft-deleted items if !IncludeDeleted
				if cr.params.IncludeDeleted || model.KeepItem() {
					validIDs = append(validIDs, id)
				}
			}
		}

		// Advance offset for next batch
		currentOffset += len(batchIDs)

		// If this batch returned fewer than expected, no more results
		if len(batchIDs) < batchSize {
			scanComplete = true
			break
		}
	}

	if !scanComplete && len(validIDs) < cacheListCountLimit {
		return validIDs, nil
	}

	// 5. Handle filtered DB results and cache appropriately
	cacheSetErr := cr.thing.cache.SetQueryIDs(cr.thing.ctx, listCacheKey, validIDs, getGlobalCacheTTL())
	if cacheSetErr != nil {
		log.Warnf("failed to cache list IDs for key %s: %v", listCacheKey, cacheSetErr)
	}
	// Register the list key
	cache.GlobalCacheIndex.RegisterQuery(cr.thing.info.TableName, listCacheKey, toInternalQueryParams(cr.params))

	// If fetched count < limit, update Count cache as well (handles count=0 correctly)
	if len(validIDs) < cacheListCountLimit { // Changed to < for clarity
		countCacheKey := cr.generateCountCacheKey()
		countStr := strconv.FormatInt(int64(len(validIDs)), 10) // Correctly gets "0" if len is 0
		countSetErr := cr.thing.cache.Set(cr.thing.ctx, countCacheKey, countStr, getGlobalCacheTTL())
		if countSetErr != nil {
			log.Warnf("failed to update count cache (key: %s) after list fetch: %v", countCacheKey, countSetErr)
		}
		// Register the count key
		cache.GlobalCacheIndex.RegisterQuery(cr.thing.info.TableName, countCacheKey, toInternalQueryParams(cr.params))
	}
	return validIDs, nil // Return filtered valid IDs (or empty slice)
}

// invalidateCache invalidates both the list and count cache for the current query.
// This is used when we detect inconsistencies in the cached data.
func (cr *CachedResult[T]) invalidateCache() error {
	if cr.thing == nil || cr.thing.cache == nil {
		return errors.New("invalidateCache: CachedResult not properly initialized")
	}

	// 1. Invalidate list cache
	listCacheKey := cr.generateListCacheKey()

	deleteErr := cr.thing.cache.Delete(cr.thing.ctx, listCacheKey)
	if deleteErr != nil && !errors.Is(deleteErr, common.ErrNotFound) {
		log.Warnf("failed to invalidate list cache for key %s: %v", listCacheKey, deleteErr)
	}

	// 2. Invalidate count cache
	countCacheKey := cr.generateCountCacheKey()

	deleteErr = cr.thing.cache.Delete(cr.thing.ctx, countCacheKey)
	if deleteErr != nil && !errors.Is(deleteErr, common.ErrNotFound) {
		log.Warnf("failed to invalidate count cache for key %s: %v", countCacheKey, deleteErr)
	}

	// 3. Reset in-memory cache state to trigger reload on next access
	cr.hasLoadedIDs = false
	cr.hasLoadedCount = false
	cr.cachedIDs = nil
	cr.cachedCount = 0

	return nil
}

// Fetch returns a subset of records starting from the given offset with the specified limit.
// It filters out soft-deleted items and triggers cache updates if inconsistencies are found.
// This implementation closely follows the CachedResult.fetch() logic:
// - It iteratively fetches batches from cache or DB
// - It filters items using KeepItem()
// - It dynamically calculates how many more items to fetch based on filtering results
func (cr *CachedResult[T]) Fetch(offset, limit int) ([]T, error) {
	if cr.Err != nil {
		return nil, cr.Err
	}
	if cr.thing != nil && cr.thing.inTransaction() {
		return cr.fetchFromExecutor(cr.thing.ctx, cr.thing.txExecutor(), offset, limit)
	}
	if cr.thing == nil || cr.thing.cache == nil || cr.thing.db == nil {
		return nil, errors.New("Fetch: CachedResult not properly initialized")
	}

	// 1. Ensure initial IDs are loaded (from cache or DB first attempt)
	if err := cr._fetch(); err != nil {
		return nil, fmt.Errorf("failed to fetch underlying IDs: %w", err)
	}

	// Handle case where query yielded no results initially
	if len(cr.cachedIDs) == 0 {
		return []T{}, nil
	}

	// Get total count for this query to determine if there are more records to fetch
	totalCount, err := cr.Count()
	if err != nil {
		log.Warnf("failed to get total count for query: %v", err)
		// Even if count fails, we can still use cachedIDs
		totalCount = int64(len(cr.cachedIDs))
	}

	// --- Setup for iterative fetching ---
	finalResults := make([]T, 0, limit)
	nextFetchOffset := offset // Starting offset
	nextFetchLimit := limit   // Initial fetch limit
	remainingNeeded := limit  // How many more items we need
	cacheInvalidated := false // Flag to track if cache was invalidated

	// Main loop - keep fetching until we have enough results or run out of data
	for remainingNeeded > 0 {
		// Determine what IDs to check in this iteration
		var idsToCheck []int64
		var fetchSource string

		// --- get items from cache or DB ---
		switch {
		case nextFetchOffset < len(cr.cachedIDs):
			// Get slice from cached IDs
			fetchSource = "Cache"

			// Adjust limit if it would exceed cached IDs
			availableCachedCount := len(cr.cachedIDs) - nextFetchOffset
			actualFetchLimit := nextFetchLimit
			if actualFetchLimit > availableCachedCount {
				actualFetchLimit = availableCachedCount
			}

			endOffset := nextFetchOffset + actualFetchLimit
			idsToCheck = cr.cachedIDs[nextFetchOffset:endOffset]

		case int64(nextFetchOffset) < totalCount:
			// Still have more data in the database according to total count
			fetchSource = "Database"

			// Get IDs directly from DB with proper offset and limit
			var dbErr error
			idsToCheck, dbErr = cr._fetch_ids_from_db(nextFetchOffset, nextFetchLimit)
			if dbErr != nil {
				return nil, fmt.Errorf("failed to fetch additional IDs from database: %w", dbErr)
			}

		default:
			// Reached the end of total records
		}

		if len(idsToCheck) == 0 {
			break // Should not happen with above checks, but safety first
		}

		// --- Fetch models for IDs ---
		// Pass preloads from the query params to ByIDs to support relationship loading
		models, err := cr.thing.ByIDs(idsToCheck, cr.params.Preloads...)
		if err != nil {
			log.Warnf("fetch iteration ByIDs failed: %v", err)

			// If fetching from cache failed, invalidate cache
			if fetchSource == "Cache" && !cacheInvalidated {
				_ = cr.invalidateCache()
				cacheInvalidated = true

				// Skip this batch and continue
				nextFetchOffset += len(idsToCheck)
				continue
			}

			nextFetchOffset += len(idsToCheck) // Still advance offset.
			nextFetchLimit = remainingNeeded   // Set next limit to remaining need
			continue
		}

		// Flag to track if any issue was found with cached IDs
		anyIssueFound := false

		// --- Process and filter fetched models ---
		processedFromBatch := 0
		for _, id := range idsToCheck {
			processedFromBatch++
			model, found := models[id]
			if !found {
				if fetchSource == "Cache" {
					anyIssueFound = true
				}
				continue
			}
			// Only filter out soft-deleted items if !IncludeDeleted
			if cr.params.IncludeDeleted || model.KeepItem() {
				finalResults = append(finalResults, model)
				remainingNeeded--
				if remainingNeeded == 0 {
					break
				}
			} else if fetchSource == "Cache" {
				anyIssueFound = true
			}
		}

		// If any issues found with cached IDs and cache hasn't been invalidated yet
		if fetchSource == "Cache" && anyIssueFound && !cacheInvalidated {
			_ = cr.invalidateCache()
			cacheInvalidated = true

			// We continue with the results we have so far, and possibly fetch more
			// from the database in the next iteration
		}

		// --- Prepare for next iteration ---
		// Advance offset by how many we processed this iteration
		nextFetchOffset += processedFromBatch

		// Set next limit to how many more we need
		nextFetchLimit = remainingNeeded

		// checks if we need more and if there's anything left to fetch
		if remainingNeeded == 0 {
			break
		}
	}

	log.Debugf("Fetch returning %d/%d requested results", len(finalResults), limit)
	return finalResults, nil
}

func (cr *CachedResult[T]) countFromExecutor(ctx context.Context, executor interface {
	Select(context.Context, interface{}, string, ...interface{}) error
},
) (int64, error) {
	if cr.thing == nil || cr.thing.db == nil || cr.thing.info == nil {
		return 0, errors.New("Count: CachedResult not properly initialized")
	}
	var count int64
	currentOffset := 0
	const batchSize = 500
	for {
		ids, err := cr.fetchIDsFromExecutor(ctx, executor, currentOffset, batchSize)
		if err != nil {
			return 0, err
		}
		count += int64(len(ids))
		if len(ids) < batchSize {
			break
		}
		currentOffset += len(ids)
	}
	return count, nil
}

func (cr *CachedResult[T]) fetchFromExecutor(ctx context.Context, executor interface {
	Select(context.Context, interface{}, string, ...interface{}) error
}, offset, limit int,
) ([]T, error) {
	if cr.thing == nil || cr.thing.db == nil || cr.thing.info == nil {
		return nil, errors.New("Fetch: CachedResult not properly initialized")
	}
	ids, err := cr.fetchIDsFromExecutor(ctx, executor, offset, limit)
	if err != nil {
		return nil, err
	}
	if len(ids) == 0 {
		return []T{}, nil
	}
	models, err := cr.thing.ByIDs(ids, cr.params.Preloads...)
	if err != nil {
		return nil, err
	}
	results := make([]T, 0, len(ids))
	for _, id := range ids {
		model, found := models[id]
		if found && (cr.params.IncludeDeleted || model.KeepItem()) {
			results = append(results, model)
		}
	}
	return results, nil
}

// All retrieves all records matching the query.
// It first gets the total count and then fetches all records using Fetch.
func (cr *CachedResult[T]) All() ([]T, error) {
	// 0. Check if already loaded
	if cr.hasLoadedAll {
		return cr.all, nil
	}

	// 1. Get the total count
	count, err := cr.Count()
	if err != nil {
		return nil, fmt.Errorf("failed to get count for All(): %w", err)
	}

	// 2. If count is zero, return empty slice
	if count == 0 {
		return []T{}, nil
	}

	// 3. Fetch all records using Fetch(0, count)
	results, err := cr.Fetch(0, int(count))
	if err != nil {
		return nil, fmt.Errorf("failed to fetch %d records for All(): %w", count, err)
	}

	// Store the results and mark as loaded
	cr.all = results
	cr.hasLoadedAll = true
	return results, nil
}

func (cr *CachedResult[T]) First() (T, error) {
	// 1. Try fetching just the first item using Fetch
	// This leverages the existing caching logic within Fetch
	results, err := cr.Fetch(0, 1)
	if err != nil {
		// Propagate errors from Fetch (e.g., DB connection issues)
		var zero T
		return zero, err
	}

	// 2. Check if any result was returned
	if len(results) == 0 {
		// No results found, return ErrNotFound
		// Check if Count is 0 first to potentially set NoneResult for count cache?
		// For simplicity now, just return ErrNotFound directly.
		// TODO: Consider integrating with NoneResult caching for the query itself?
		var zero T
		return zero, common.ErrNotFound // Use the existing ErrNotFound
	}

	// 3. Return the first result
	return results[0], nil
}

// --- Chainable Query Builder Methods ---

// Where on Thing: starts a new query chain
func (t *Thing[T]) Where(where string, args ...interface{}) *CachedResult[T] {
	return &CachedResult[T]{
		thing:  t,
		params: QueryParams{Where: where, Args: args},
	}
}

// Where on CachedResult: returns a new instance with updated Where/Args
func (cr *CachedResult[T]) Where(where string, args ...interface{}) *CachedResult[T] {
	newCr := *cr
	newCr.params.Where = where
	newCr.params.Args = args
	// Reset loaded state
	newCr.hasLoadedCount = false
	newCr.hasLoadedIDs = false
	newCr.cachedIDs = nil
	newCr.cachedCount = 0
	newCr.hasLoadedAll = false
	newCr.all = nil
	newCr.resetKeyCache()
	return &newCr
}

// Order on Thing: starts a new query chain
func (t *Thing[T]) Order(order string) *CachedResult[T] {
	return &CachedResult[T]{
		thing:  t,
		params: QueryParams{Order: order},
	}
}

// Order on CachedResult: returns a new instance with updated Order
func (cr *CachedResult[T]) Order(order string) *CachedResult[T] {
	newCr := *cr
	newCr.params.Order = order
	// Reset loaded state
	newCr.hasLoadedCount = false
	newCr.hasLoadedIDs = false
	newCr.cachedIDs = nil
	newCr.cachedCount = 0
	newCr.hasLoadedAll = false
	newCr.all = nil
	newCr.resetKeyCache()
	return &newCr
}

// Preload on Thing: starts a new query chain
func (t *Thing[T]) Preload(preloads ...string) *CachedResult[T] {
	return &CachedResult[T]{
		thing:  t,
		params: QueryParams{Preloads: preloads},
	}
}

// Preload on CachedResult: returns a new instance with updated Preloads
func (cr *CachedResult[T]) Preload(preloads ...string) *CachedResult[T] {
	newCr := *cr
	// Merge preloads
	newCr.params.Preloads = append([]string{}, cr.params.Preloads...)
	newCr.params.Preloads = append(newCr.params.Preloads, preloads...)
	// Reset loaded state
	newCr.hasLoadedCount = false
	newCr.hasLoadedIDs = false
	newCr.cachedIDs = nil
	newCr.cachedCount = 0
	newCr.hasLoadedAll = false
	newCr.all = nil
	newCr.resetKeyCache()
	return &newCr
}