# Phase 6: Unified Query API

## Overview

This phase refactors the query system to provide a unified API for both entity queries and projection queries. The current `Query<T>` class will become an abstract base class, with `EntityQuery<T>` handling entity-specific logic and `ProjectionQuery<TProjection>` adapted to extend the same base.

## Goals

1. Create an abstract `Query<T>` base class with shared state and common methods
2. Extract current `Query<T>` functionality into `EntityQuery<T>`
3. Adapt `ProjectionQuery<TProjection>` to extend `Query<T>`
4. Update `OrmSession.query<T>()` to return the appropriate query type based on type registration
5. Standardize return types on `ImmutableLot<T>` (which extends `Lot<T>` extends `Enumerable<T>`)

## Current Architecture

```
┌─────────────────┐     ┌─────────────────────────┐
│    Query<T>     │     │ ProjectionQuery<TProj>  │
│   (concrete)    │     │      (concrete)         │
├─────────────────┤     ├─────────────────────────┤
│ _session        │     │ _query_session          │
│ _filter: Expr?  │     │ _query_definition       │
│ _orderings      │     │ _query_sql_builder      │
│ _limit: int?    │     │ _where_clauses          │
│ _offset: int?   │     │ _order_by_clauses       │
├─────────────────┤     │ _limit_value: int64?    │
│ where()         │     │ _offset_value: int64?   │
│ where_expr()    │     │ _use_or                 │
│ order_by()      │     ├─────────────────────────┤
│ order_by_desc() │     │ where()                 │
│ limit()         │     │ or_where()              │
│ offset()        │     │ order_by()              │
│ materialise()   │     │ order_by_desc()         │
│ materialise_    │     │ limit()                 │
│   async()       │     │ offset()                │
│ first()         │     │ materialise()           │
│ first_async()   │     │ materialise_async()     │
└─────────────────┘     │ first()                 │
                        │ first_async()           │
                        │ to_sql()                │
                        └─────────────────────────┘
```

## Proposed Architecture

```
                    ┌───────────────────────────┐
                    │     Query<T> (abstract)   │
                    ├───────────────────────────┤
                    │ #_session: OrmSession     │
                    │ #_orderings: Vector<...>  │
                    │ #_limit: int64?           │
                    │ #_offset: int64?          │
                    │ #_use_or: bool            │
                    ├───────────────────────────┤
                    │ where(expr): Query<T>     │
                    │ or_where(expr): Query<T>  │
                    │ order_by(expr): Query<T>  │
                    │ order_by_desc(): Query<T> │
                    │ limit(count): Query<T>    │
                    │ offset(count): Query<T>   │
                    │ materialise(): Immutable  │
                    │   Lot<T>                  │
                    │ materialise_async(): ...  │
                    │ first(): T?               │
                    │ first_async(): T?         │
                    │ to_sql(): string          │
                    │ where_expr(e): Query<T>   │
                    ├───────────────────────────┤
                    │ #build_sql(): string      │
                    │ #get_combined_where()     │
                    └─────────────┬─────────────┘
                                  │
                ┌─────────────────┴─────────────────┐
                │                                   │
┌───────────────┴───────────────┐   ┌───────────────┴───────────────┐
│   EntityQuery<T> (concrete)   │   │ ProjectionQuery<T> (concrete) │
├───────────────────────────────┤   ├───────────────────────────────┤
│ -_filter: Expression?         │   │ -_query_definition            │
│ -_where_clauses: Vector<...>  │   │ -_query_sql_builder           │
│                               │   │ -_where_clauses: Vector<...>  │
├───────────────────────────────┤   ├───────────────────────────────┤
│ #build_sql(): string          │   │ #build_sql(): string          │
│ #get_combined_where()         │   │ #get_combined_where()         │
│ +where_expr(e): EntityQuery<T>│   │ +where_expr(): throws error   │
└───────────────────────────────┘   └───────────────────────────────┘
```

## Implementation Details

### 1. Abstract Query<T> Base Class

**File:** `src/orm/query.vala`

```vala
using Invercargill.DataStructures;
using Invercargill.Expressions;

namespace InvercargillSql.Orm {
    
    /**
     * Abstract base class for all queries.
     * 
     * Query<T> provides a fluent interface for building database queries.
     * Subclasses implement specific query execution strategies for entities
     * and projections.
     */
    public abstract class Query<T> : Object {
        
        // Shared state - protected for subclass access
        protected OrmSession _session;
        protected Vector<OrderByClause> _orderings;
        protected int64? _limit;
        protected int64? _offset;
        protected bool _use_or;
        protected Vector<string> _where_clauses;
        
        /**
         * Creates a new Query for the given session.
         */
        protected Query(OrmSession session) {
            _session = session;
            _orderings = new Vector<OrderByClause>();
            _where_clauses = new Vector<string>();
            _limit = null;
            _offset = null;
            _use_or = false;
        }
        
        // === Fluent query builders ===
        
        public virtual Query<T> where(string expression) {
            _where_clauses.add(expression);
            return this;
        }
        
        public virtual Query<T> or_where(string expression) {
            _use_or = true;
            _where_clauses.add(expression);
            return this;
        }
        
        public abstract Query<T> where_expr(Expression expression);
        
        public Query<T> order_by(string expression) {
            _orderings.add(new OrderByClause(expression, false));
            return this;
        }
        
        public Query<T> order_by_desc(string expression) {
            _orderings.add(new OrderByClause(expression, true));
            return this;
        }
        
        public Query<T> limit(int64 count) {
            _limit = count;
            return this;
        }
        
        public Query<T> offset(int64 count) {
            _offset = count;
            return this;
        }
        
        // === Execution methods ===
        
        /**
         * Executes the query and returns results.
         */
        public abstract Invercargill.ImmutableLot<T> materialise() throws SqlError;
        
        /**
         * Executes the query asynchronously.
         */
        public abstract async Invercargill.ImmutableLot<T> materialise_async() throws SqlError;
        
        /**
         * Returns the first result or null.
         */
        public virtual T? first() throws SqlError {
            _limit = 1;
            var results = materialise();
            if (results.length > 0) {
                return results.first();
            }
            return null;
        }
        
        /**
         * Returns the first result asynchronously.
         */
        public virtual async T? first_async() throws SqlError {
            _limit = 1;
            var results = yield materialise_async();
            if (results.length > 0) {
                return results.first();
            }
            return null;
        }
        
        /**
         * Returns the SQL for this query.
         */
        public abstract string to_sql();
        
        // === Protected helpers ===
        
        protected string? get_combined_where() {
            if (_where_clauses.length == 0) {
                return null;
            }
            
            if (_where_clauses.length == 1) {
                return _where_clauses.get(0);
            }
            
            var combined = new StringBuilder();
            string connector = _use_or ? " || " : " && ";
            
            bool first = true;
            foreach (var clause in _where_clauses) {
                if (!first) {
                    combined.append(connector);
                }
                combined.append("(");
                combined.append(clause);
                combined.append(")");
                first = false;
            }
            
            return combined.str;
        }
    }
}
```

### 2. EntityQuery<T> Implementation

**File:** `src/orm/entity-query.vala` (new file)

```vala
using Invercargill.DataStructures;
using Invercargill.Expressions;
using InvercargillSql.Dialects;
using InvercargillSql.Expressions;

namespace InvercargillSql.Orm {
    
    /**
     * Query implementation for entity types.
     * 
     * EntityQuery<T> handles queries for registered entity types,
     * using EntityMapper for materialization and ExpressionToSqlVisitor
     * for WHERE clause generation.
     */
    public class EntityQuery<T> : Query<T> {
        
        private Expression? _filter;
        
        internal EntityQuery(OrmSession session) {
            base(session);
            _filter = null;
        }
        
        public override Query<T> where(string expression) {
            // Parse string to Expression and store
            _filter = ExpressionParser.parse(expression);
            _where_clauses.add(expression);
            return this;
        }
        
        public override Query<T> or_where(string expression) {
            // For EntityQuery, combine with existing filter using OR
            var new_filter = ExpressionParser.parse(expression);
            if (_filter != null) {
                _filter = new BinaryExpression(
                    _filter, 
                    new_filter, 
                    BinaryOperator.OR
                );
            } else {
                _filter = new_filter;
            }
            _use_or = true;
            _where_clauses.add(expression);
            return this;
        }
        
        public override Query<T> where_expr(Expression expression) {
            _filter = expression;
            return this;
        }
        
        public override Invercargill.ImmutableLot<T> materialise() throws SqlError {
            var mapper = _session.get_mapper<T>();
            var dialect = _session.get_dialect();
            
            // Build SQL
            var sql = build_select_sql(mapper, dialect);
            var command = _session.get_connection().create_command(sql);
            
            // Add parameters if filter exists
            if (_filter != null) {
                var visitor = new ExpressionToSqlVisitor(dialect, mapper);
                _filter.accept(visitor);
                
                var parameters = visitor.get_parameters();
                var param_names = visitor.get_parameter_names();
                var param_array = parameters.to_array();
                var name_array = param_names.to_array();
                
                for (int i = 0; i < param_array.length && i < name_array.length; i++) {
                    command.with_parameter<Invercargill.Element>(name_array[i], param_array[i]);
                }
            }
            
            // Execute and materialize
            var results = command.execute_query();
            var entities = new Vector<T>();
            
            foreach (var row in results) {
                try {
                    var entity = mapper.materialise(row);
                    entities.add(entity);
                } catch (Error e) {
                    throw new SqlError.GENERAL_ERROR(
                        "Failed to materialize entity: %s".printf(e.message)
                    );
                }
            }
            
            return entities.to_immutable_buffer();
        }
        
        public override async Invercargill.ImmutableLot<T> materialise_async() throws SqlError {
            var mapper = _session.get_mapper<T>();
            var dialect = _session.get_dialect();
            
            var sql = build_select_sql(mapper, dialect);
            var command = _session.get_connection().create_command(sql);
            
            if (_filter != null) {
                var visitor = new ExpressionToSqlVisitor(dialect, mapper);
                _filter.accept(visitor);
                
                var parameters = visitor.get_parameters();
                var param_names = visitor.get_parameter_names();
                var param_array = parameters.to_array();
                var name_array = param_names.to_array();
                
                for (int i = 0; i < param_array.length && i < name_array.length; i++) {
                    command.with_parameter<Invercargill.Element>(name_array[i], param_array[i]);
                }
            }
            
            var results = yield command.execute_query_async();
            var entities = new Vector<T>();
            
            foreach (var row in results) {
                try {
                    var entity = mapper.materialise(row);
                    entities.add(entity);
                } catch (Error e) {
                    throw new SqlError.GENERAL_ERROR(
                        "Failed to materialize entity: %s".printf(e.message)
                    );
                }
            }
            
            return entities.to_immutable_buffer();
        }
        
        public override string to_sql() {
            try {
                var mapper = _session.get_mapper<T>();
                var dialect = _session.get_dialect();
                return build_select_sql(mapper, dialect);
            } catch (SqlError e) {
                return "-- Error building SQL: %s".printf(e.message);
            }
        }
        
        private string build_select_sql(EntityMapper<T> mapper, SqlDialect dialect) {
            var sql = new StringBuilder();
            sql.append("SELECT * FROM ");
            sql.append(mapper.table_name);
            
            // WHERE clause
            if (_filter != null) {
                var visitor = new ExpressionToSqlVisitor(dialect, mapper);
                _filter.accept(visitor);
                sql.append(" WHERE ");
                sql.append(visitor.get_sql());
            }
            
            // ORDER BY
            if (_orderings.length > 0) {
                sql.append(" ORDER BY ");
                bool first = true;
                foreach (var ordering in _orderings) {
                    if (!first) {
                        sql.append(", ");
                    }
                    sql.append(ordering.expression);
                    if (ordering.descending) {
                        sql.append(" DESC");
                    }
                    first = false;
                }
            }
            
            // LIMIT/OFFSET
            if (_limit != null) {
                sql.append_printf(" LIMIT %lld", _limit);
            }
            if (_offset != null) {
                sql.append_printf(" OFFSET %lld", _offset);
            }
            
            return sql.str;
        }
    }
}
```

### 3. Updated ProjectionQuery<TProjection>

**File:** `src/orm/projections/projection-query.vala`

Key changes:
- Extend `Query<TProjection>` instead of `Object`
- Change `_limit_value`/`_offset_value` to use base class `_limit`/`_offset`
- Change `_order_by_clauses` to use base class `_orderings`
- Change `_where_clauses` to use base class `_where_clauses`
- Change `_use_or` to use base class `_use_or`
- Implement `where_expr()` to throw an error (projections use string-based where)
- Change return types to `ImmutableLot<TProjection>`

```vala
public class ProjectionQuery<TProjection> : Query<TProjection> {
    
    private ProjectionDefinition _query_definition;
    private ProjectionSqlBuilder _query_sql_builder;
    
    internal ProjectionQuery(
        OrmSession session, 
        ProjectionDefinition definition,
        ProjectionSqlBuilder sql_builder
    ) {
        base(session);  // Initialize base class
        _query_definition = definition;
        _query_sql_builder = sql_builder;
    }
    
    // where() and or_where() use base class implementation
    
    public override Query<TProjection> where_expr(Expression expression) {
        // Projections don't support Expression-based where clauses
        // because they need to translate through the projection definition
        throw new SqlError.GENERAL_ERROR(
            "ProjectionQuery does not support where_expr(). Use where() with a string expression instead."
        );
    }
    
    public override Invercargill.ImmutableLot<TProjection> materialise() throws SqlError {
        string? where_expr = get_combined_where();
        
        BuiltQuery built = _query_sql_builder.build_with_split(
            where_expr,
            _orderings,  // Use base class field
            _limit,      // Use base class field
            _offset      // Use base class field
        );
        
        string sql = built.sql;
        var connection = _session.get_connection();  // Use base class session
        var command = connection.create_command(sql);
        var results = command.execute_query();
        
        var mapper = new ProjectionMapper<TProjection>(_query_definition);
        
        try {
            var vector = mapper.map_all(results);
            return vector.to_immutable_buffer();
        } catch (ProjectionError e) {
            throw new SqlError.GENERAL_ERROR(
                @"Failed to materialize projection: $(e.message)"
            );
        }
    }
    
    // Similar changes for materialise_async(), first(), first_async()
    
    public override string to_sql() {
        string? where_expr = get_combined_where();
        
        BuiltQuery built = _query_sql_builder.build_with_split(
            where_expr,
            _orderings,
            _limit,
            _offset
        );
        
        return built.sql;
    }
}
```

### 4. Updated OrmSession

**File:** `src/orm/orm-session.vala`

```vala
/**
 * Creates a new query for type T.
 * 
 * Returns EntityQuery<T> if T is a registered entity type.
 * Returns ProjectionQuery<T> if T is a registered projection type.
 * 
 * @return A new Query<T> instance appropriate for type T
 * @throws SqlError if T is neither a registered entity nor projection
 */
public Query<T> query<T>() throws SqlError {
    var type = typeof(T);
    
    // Check if it's a registered entity
    if (_mappers.contains(type)) {
        return new EntityQuery<T>(this);
    }
    
    // Check if it's a registered projection
    var projection_def = projection_registry.get(type);
    if (projection_def != null) {
        var sql_builder = new ProjectionSqlBuilder(projection_def, _dialect);
        return new ProjectionQuery<T>(this, projection_def, sql_builder);
    }
    
    throw new SqlError.GENERAL_ERROR(
        "Type %s is not registered as an entity or projection".printf(type.name())
    );
}

/**
 * Gets the SQL dialect for internal use.
 */
internal SqlDialect get_dialect() {
    return _dialect;
}
```

### 5. OrmSession API Changes

The following methods become internal or are removed:

| Method | Change |
|--------|--------|
| `execute_query<T>(Query<T>)` | Remove - logic moved to EntityQuery |
| `execute_query_async<T>(Query<T>)` | Remove - logic moved to EntityQuery |
| `query_projection<T>()` | Deprecate - use `query<T>()` instead |
| `get_connection()` | Keep as internal |
| `get_dialect()` | Add as internal |

## Migration Guide

### For Entity Queries

Before:
```vala
var users = session.query<User>()
    .where("age > 18")
    .materialise();
```

After:
```vala
// Same API, but now returns ImmutableLot<User>
var users = session.query<User>()
    .where("age > 18")
    .materialise();
```

### For Projection Queries

Before:
```vala
var stats = session.query_projection<UserOrderStats>()
    .where("user_id > 100")
    .materialise();
```

After:
```vala
// Use query<T>() instead of query_projection<T>()
var stats = session.query<UserOrderStats>()
    .where("user_id > 100")
    .materialise();
```

### New Unified Features

Both query types now support:
```vala
// or_where() now available on both
var results = session.query<User>()
    .where("age > 18")
    .or_where("name == 'Admin'")
    .materialise();

// to_sql() now available on both
string sql = session.query<User>()
    .where("age > 18")
    .to_sql();
```

## Implementation Checklist

- [ ] Create abstract `Query<T>` base class in `src/orm/query.vala`
- [ ] Create `EntityQuery<T>` in `src/orm/entity-query.vala`
- [ ] Update `ProjectionQuery<TProjection>` to extend `Query<T>`
- [ ] Update `OrmSession.query<T>()` to dispatch to correct type
- [ ] Add `get_dialect()` method to OrmSession
- [ ] Remove `execute_query<T>()` and `execute_query_async<T>()` from OrmSession
- [ ] Deprecate `query_projection<T>()` (keep for backward compatibility)
- [ ] Update `src/meson.build` to include new `entity-query.vala`
- [ ] Update tests to use unified API
- [ ] Update documentation

## Testing Strategy

1. **Unit Tests for EntityQuery**
   - Test `where()`, `or_where()`, `where_expr()`
   - Test `order_by()`, `order_by_desc()`
   - Test `limit()`, `offset()`
   - Test `materialise()`, `materialise_async()`
   - Test `first()`, `first_async()`
   - Test `to_sql()`

2. **Unit Tests for ProjectionQuery**
   - Same tests as EntityQuery
   - Test that `where_expr()` throws appropriate error

3. **Integration Tests for OrmSession.query<T>()**
   - Test dispatch to EntityQuery for registered entities
   - Test dispatch to ProjectionQuery for registered projections
   - Test error for unregistered types

4. **Backward Compatibility Tests**
   - Ensure existing entity query tests pass
   - Ensure existing projection query tests pass

## Risks and Mitigations

| Risk | Mitigation |
|------|------------|
| Breaking existing code | Keep `query_projection<T>()` as deprecated method |
| Type inference issues | Ensure return type is `Query<T>` not concrete type |
| ImmutableLot conversion | Use `to_immutable_buffer()` extension method |
| Expression handling in EntityQuery | Reuse existing ExpressionToSqlVisitor |

## Files Changed

| File | Change |
|------|--------|
| `src/orm/query.vala` | Convert to abstract class |
| `src/orm/entity-query.vala` | New file |
| `src/orm/projections/projection-query.vala` | Extend Query<T> |
| `src/orm/orm-session.vala` | Update query<T>() dispatch |
| `src/meson.build` | Add entity-query.vala |
| `src/tests/orm-test.vala` | Update tests |
| `src/tests/projection-test.vala` | Update tests |