# Requirements Document

## Introduction

This feature addresses a MongoDB compatibility issue where the current implementation uses the `$unionWith` aggregation operator, which is only available in MongoDB 4.4+. The system needs to support MongoDB 3.x environments by replacing the `$unionWith` approach with a compatible solution that queries collections separately and performs deduplication in application code.

## Glossary

- **Inspection_Result_System**: The secondary circuit inspection result management system
- **MongoDB_Driver**: The C# MongoDB driver used to interact with MongoDB
- **Collection_Sharding**: The practice of splitting data across multiple collections based on time periods (year-month)
- **Deduplication**: The process of removing duplicate inspection results based on day, inspection item ID, and status
- **Aggregation_Pipeline**: MongoDB's framework for data aggregation operations

## Requirements

### Requirement 1: Replace $unionWith with Compatible Approach

**User Story:** As a system administrator, I want the inspection result query to work on MongoDB 3.x, so that I don't need to upgrade the database server.

#### Acceptance Criteria

1. WHEN the system queries inspection results across multiple collections, THE Inspection_Result_System SHALL NOT use the $unionWith operator
2. WHEN querying multiple collections, THE Inspection_Result_System SHALL query each collection separately using standard MongoDB operations
3. WHEN multiple collection queries complete, THE Inspection_Result_System SHALL merge the results in application memory
4. THE Inspection_Result_System SHALL maintain the same deduplication logic (by Year, Month, Day, SecondaryCircuitInspectionItemId, and Status)
5. THE Inspection_Result_System SHALL maintain the same sorting and pagination behavior as the current implementation

### Requirement 2: Preserve Query Performance

**User Story:** As a system user, I want query performance to remain acceptable, so that the application remains responsive.

#### Acceptance Criteria

1. WHEN querying inspection results, THE Inspection_Result_System SHALL complete queries within 5 seconds for typical datasets (up to 12 months of data)
2. WHEN performance degrades, THE Inspection_Result_System SHALL log performance warnings with timing details
3. THE Inspection_Result_System SHALL use projection to limit fields retrieved from MongoDB
4. THE Inspection_Result_System SHALL apply filters at the MongoDB level before retrieving data
5. WHEN sorting results, THE Inspection_Result_System SHALL perform sorting in-memory after deduplication

### Requirement 3: Maintain Functional Equivalence

**User Story:** As a developer, I want the new implementation to produce identical results to the old implementation, so that existing functionality is not broken.

#### Acceptance Criteria

1. WHEN deduplicating results, THE Inspection_Result_System SHALL group by the same fields (Year, Month, Day, SecondaryCircuitInspectionItemId, Status)
2. WHEN multiple records exist for the same group, THE Inspection_Result_System SHALL select the first record after sorting
3. WHEN paginating results, THE Inspection_Result_System SHALL return the correct page of results based on skip and limit parameters
4. THE Inspection_Result_System SHALL return both the paginated results and the total count
5. WHEN handling null Status values, THE Inspection_Result_System SHALL treat them consistently with the original implementation

### Requirement 4: Error Handling and Logging

**User Story:** As a system administrator, I want clear error messages and logging, so that I can troubleshoot issues effectively.

#### Acceptance Criteria

1. WHEN a collection query fails, THE Inspection_Result_System SHALL log the error with collection name and continue with other collections
2. WHEN all collection queries fail, THE Inspection_Result_System SHALL return an appropriate error message
3. THE Inspection_Result_System SHALL log the number of collections queried and the number of results from each collection
4. THE Inspection_Result_System SHALL log performance metrics including query time and deduplication time
5. WHEN query time exceeds thresholds, THE Inspection_Result_System SHALL log performance warnings

### Requirement 5: Backward Compatibility

**User Story:** As a developer, I want the method signature to remain unchanged, so that calling code does not need to be modified.

#### Acceptance Criteria

1. THE Inspection_Result_System SHALL maintain the same method signature for QueryPagedDeduplicatedResultsAcrossCollectionsAsync
2. THE Inspection_Result_System SHALL accept the same parameters (collectionNames, filter, sortField, isDescending, skipCount, pageSize, cancellationToken)
3. THE Inspection_Result_System SHALL return the same result type (tuple of List<SecondaryCircuitInspectionResult> and long totalCount)
4. THE Inspection_Result_System SHALL support cancellation via CancellationToken
5. THE Inspection_Result_System SHALL handle empty collection lists by returning empty results