SOMS/.kiro/specs/mongodb-compatibility-fix/tasks.md

# Implementation Plan: MongoDB Compatibility Fix

## Overview

This implementation replaces the MongoDB 4.4+ `$unionWith` operator with a MongoDB 3.x compatible approach by querying collections separately and performing deduplication in application memory using LINQ.

## Tasks

- [x] 1. Add helper method for dynamic property access
  - Create `GetPropertyValue` method to retrieve property values by name for sorting
  - Handle cases where property doesn't exist (log warning, return null)
  - _Requirements: 1.5, 3.2_

- [ ]* 1.1 Write property test for GetPropertyValue helper
  - **Property: For any valid property name on SecondaryCircuitInspectionResult, GetPropertyValue should return the correct value**
  - **Validates: Requirements 1.5**

- [x] 2. Rewrite QueryPagedDeduplicatedResultsAcrossCollectionsAsync method
  - [x] 2.1 Replace aggregation pipeline with separate collection queries
    - Remove all BsonDocument pipeline construction code
    - Query each collection separately using Find with filter
    - Apply projection to limit fields
    - Apply initial sort at MongoDB level
    - Use Task.WhenAll for parallel execution
    - Wrap each query in try-catch for resilience
    - _Requirements: 1.1, 1.2, 1.3, 4.1_

  - [ ]* 2.2 Write property test for parallel collection querying
    - **Property 1: Deduplication Removes Duplicates by Key Fields**
    - **Validates: Requirements 1.4, 3.1**

  - [x] 2.3 Implement in-memory result merging
    - Use SelectMany to flatten collection results
    - Log number of results from each collection
    - _Requirements: 1.3_

  - [x] 2.4 Implement in-memory sorting before deduplication
    - Use LINQ OrderBy/OrderByDescending with GetPropertyValue
    - Handle null sort field (skip sorting)
    - _Requirements: 1.5_

  - [ ]* 2.5 Write property test for sorting
    - **Property 2: Sorting Produces Correct Order**
    - **Validates: Requirements 1.5**

  - [x] 2.6 Implement deduplication logic
    - Use LINQ GroupBy with anonymous type (Year, Month, Day, ItemId, Status)
    - Handle null Status by converting to empty string
    - Select first from each group
    - _Requirements: 1.4, 3.1, 3.5_

  - [ ]* 2.7 Write property test for first record selection
    - **Property 3: First Record Selection After Sorting**
    - **Validates: Requirements 3.2**

  - [x] 2.8 Implement final sorting after deduplication
    - Apply same sort logic as before deduplication
    - Maintain user-requested sort order
    - _Requirements: 1.5_

  - [x] 2.9 Implement counting and pagination
    - Count deduplicated results before pagination
    - Apply Skip and Take for pagination
    - Return tuple of (results, totalCount)
    - _Requirements: 3.3, 3.4_

  - [ ]* 2.10 Write property test for pagination
    - **Property 4: Pagination Returns Correct Slice**
    - **Validates: Requirements 3.3**

  - [ ]* 2.11 Write property test for total count
    - **Property 5: Total Count Matches Deduplicated Count**
    - **Validates: Requirements 3.4**

- [x] 3. Update logging and performance monitoring
  - Log collection query start and completion
  - Log number of results from each collection
  - Log deduplication metrics (before/after counts)
  - Maintain existing performance warning logic (2000ms threshold)
  - _Requirements: 2.2, 4.3, 4.4, 4.5_

- [ ]* 3.1 Write unit tests for logging behavior
  - Test that performance warnings are logged when threshold exceeded
  - Test that collection query failures are logged
  - _Requirements: 4.1, 4.2, 4.5_

- [x] 4. Handle edge cases and error conditions
  - [x] 4.1 Add early return for empty collection list
    - Check if collectionNames is null or empty
    - Return (empty list, 0) immediately
    - _Requirements: 5.5_

  - [x] 4.2 Ensure CancellationToken is passed through all async operations
    - Pass to all ToListAsync calls
    - Pass to Task.WhenAll
    - _Requirements: 5.4_

  - [ ]* 4.3 Write unit test for cancellation support
    - Test that cancellation is properly handled
    - _Requirements: 5.4_

  - [ ]* 4.4 Write unit test for empty collection list
    - Test that empty list returns empty results
    - _Requirements: 5.5_

  - [ ]* 4.5 Write unit test for all collections failing
    - Test that appropriate error handling occurs
    - _Requirements: 4.2_

- [ ] 5. Checkpoint - Ensure all tests pass
  - Ensure all tests pass, ask the user if questions arise.

- [ ] 6. Integration testing and verification
  - [ ] 6.1 Test with actual MongoDB 3.x instance
    - Verify queries execute without $unionWith errors
    - Compare results with MongoDB 4.4+ version (if available)
    - _Requirements: 1.1, 3.1, 3.2, 3.3, 3.4_

  - [ ] 6.2 Performance testing with realistic data volumes
    - Test with 1, 6, and 12 collections
    - Verify performance is acceptable (< 5 seconds)
    - Verify performance warnings are logged appropriately
    - _Requirements: 2.1, 2.2_

- [ ] 7. Final checkpoint - Ensure all tests pass
  - Ensure all tests pass, ask the user if questions arise.

## Notes

- Tasks marked with `*` are optional and can be skipped for faster MVP
- Each task references specific requirements for traceability
- Checkpoints ensure incremental validation
- Property tests validate universal correctness properties
- Unit tests validate specific examples and edge cases
- The implementation maintains the same method signature for backward compatibility
- Performance may be slightly different from $unionWith but should remain acceptable