# Phase 1: ORM Core Design

## Overview

This document describes the design for the core ORM functionality in Invercargill-Sql, leveraging `Invercargill.Mapping` for entity-to-properties mapping and `Invercargill.Expressions` for type-safe query filtering.

## Design Goals

- **Implementation agnostic**: Database engines provide translation layers via interfaces
- **Leverage Invercargill.Mapping**: Use `PropertyMapper<T>` for entity materialization
- **Leverage Invercargill.Expressions**: Use expression parsing and visitor pattern for query translation
- **Fluent API**: Consistent with Invercargill's builder patterns

## Architecture Diagram

```mermaid
classDiagram
    direction TB
    
    namespace ORM_Core {
        class OrmSession {
            -Connection _connection
            -Dictionary~Type_EntityMapper~ _mappers
            +register_entity~T~(EntityMapper~T~ mapper) void
            +query~T~() Query~T~
            +insert~T~(T entity) void
            +update~T~(T entity) void
            +delete~T~(T entity) void
        }
        
        class Query~T~ {
            -OrmSession _session
            -Expression? _filter
            -OrderByClause[] _orderings
            -int? _limit
            -int? _offset
            +where(string expression) Query~T~
            +order_by(string expression) Query~T~
            +limit(int count) Query~T~
            +offset(int count) Query~T~
            +materialise() Enumerable~T~
            +materialise_async() async Enumerable~T~
        }
        
        class EntityMapper~T~ {
            +string table_name
            +PropertyMapper~T~ property_mapper
            +ColumnDefinition[] columns
            +IndexDefinition[] indexes
            +string primary_key_column
        }
        
        class EntityMapperBuilder~T~ {
            +table(string name) EntityMapperBuilder~T~
            +column~TProp~(string name, getter, setter) ColumnBuilder~T~
            +composite_index(string name, string[] columns) EntityMapperBuilder~T~
            +composite_unique(string name, string[] columns) EntityMapperBuilder~T~
            +build() EntityMapper~T~
        }
        
        class ColumnBuilder~T~ {
            -EntityMapperBuilder~T~ _parent
            +primary_key() EntityMapperBuilder~T~
            +required() EntityMapperBuilder~T~
            +unique() EntityMapperBuilder~T~
            +index() EntityMapperBuilder~T~
            +default_value(T value) EntityMapperBuilder~T~
            +default_now() EntityMapperBuilder~T~
        }
    }
    
    namespace SQL_Translation {
        class SqlDialect {
            <<interface>>
            +translate_type(ColumnType type) string
            +translate_expression(Expression expr) string
            +build_select(SelectQuery query) string
            +build_insert(InsertQuery query) string
            +build_update(UpdateQuery query) string
            +build_delete(DeleteQuery query) string
        }
        
        class SqliteDialect {
            +translate_type(ColumnType type) string
            +translate_expression(Expression expr) string
            +build_select(SelectQuery query) string
            +build_insert(InsertQuery query) string
            +build_update(UpdateQuery query) string
            +build_delete(DeleteQuery query) string
        }
        
        class ExpressionToSqlVisitor {
            -StringBuilder _sql
            -SqlDialect _dialect
            +visit_binary(BinaryExpression expr) void
            +visit_property(PropertyExpression expr) void
            +visit_literal(LiteralExpression expr) void
            +get_sql() string
        }
    }
    
    namespace Type_System {
        class ColumnType {
            <<enumeration>>
            INT_32
            INT_64
            TEXT
            BOOLEAN
            DECIMAL
            DATETIME
            BINARY
            UUID
        }
        
        class ColumnDefinition {
            +string name
            +ColumnType type
            +bool is_primary_key
            +bool is_required
            +bool is_unique
            +bool has_index
            +Element? default_value
        }
    }
    
    OrmSession --> EntityMapper~T~ : manages
    OrmSession --> Query~T~ : creates
    Query~T~ --> SqlDialect : uses
    EntityMapperBuilder~T~ --> ColumnBuilder~T~ : creates
    ColumnBuilder~T~ --> EntityMapperBuilder~T~ : returns to parent
    SqlDialect <|.. SqliteDialect : implements
    SqliteDialect --> ExpressionToSqlVisitor : uses
    ExpressionToSqlVisitor ..> Expression : visits
```

## Component Details

### 1. EntityMapper and Builder Pattern

The `EntityMapper<T>` wraps `PropertyMapper<T>` and adds ORM-specific metadata (table name, column constraints, indexes).

**Key Design Decision:** Use sub-builders that return to the parent builder for fluent chaining, similar to `PropertyMappingBuilder` in Invercargill.Mapping.

```vala
// Example usage
var mapper = EntityMapper.build_for<User>(b => b
    .table("users")
    .column<int64>("id", u => u.id, (u, v) => u.id = v)
        .primary_key()
    .column<string>("name", u => u.name, (u, v) => u.name = v)
        .required()
    .column<string>("email", u => u.email, (u, v) => u.email = v)
        .unique()
        .index()
    .column<bool>("is_active", u => u.is_active, (u, v) => u.is_active = v)
    .column<double>("balance", u => u.balance, (u, v) => u.balance = v)
        .default_value(0.0)
    .column<DateTime>("created_at", u => u.created_at, (u, v) => u.created_at = v)
        .default_now()
    .composite_index("idx_name_email", {"name", "email"})
    .composite_unique("uk_name_email", {"name", "email"}));

orm.register_entity(mapper);
```

### 2. GType to ColumnType Mapping

Automatic type inference from generic type parameter:

| Vala Type | GType | ColumnType | SQLite Storage | PostgreSQL Storage |
|-----------|-------|------------|----------------|-------------------|
| `int` | `G_TYPE_INT` | `INT_32` | INTEGER | INT |
| `int64` | `G_TYPE_INT64` | `INT_64` | INTEGER | BIGINT |
| `string` | `G_TYPE_STRING` | `TEXT` | TEXT | TEXT |
| `bool` | `G_TYPE_BOOLEAN` | `BOOLEAN` | INTEGER (0/1) | BOOLEAN |
| `double` | `G_TYPE_DOUBLE` | `DECIMAL` | REAL | DOUBLE PRECISION |
| `float` | `G_TYPE_FLOAT` | `DECIMAL` | REAL | REAL |
| `DateTime` | `G_TYPE_DATE_TIME` | `DATETIME` | INTEGER (epoch) | TIMESTAMP |
| `uint8[]` | `G_TYPE_BYTE_ARRAY` | `BINARY` | BLOB | BYTEA |

### 3. Query API

Queries use `Invercargill.Expressions` string syntax for filtering. The query object does NOT extend `Enumerable<T>` - instead it provides `materialise()` methods that return `Enumerable<T>`.

```vala
// Create query
var query = orm.query<User>();

// Build query with fluent API
var active_users = orm.query<User>()
    .where("u => u.is_active == true")
    .order_by("u => u.created_at")
    .limit(10)
    .materialise();  // Returns Enumerable<User>

// Async materialization
var users_async = yield orm.query<User>()
    .where("u => u.age > 18")
    .materialise_async();

// Complex filter with AND/OR
var results = orm.query<User>()
    .where("u => (u.age > 18 && u.is_active == true) || u.is_admin == true")
    .order_by("u => u.name")
    .materialise();
```

### 4. Expression to SQL Translation

The `ExpressionToSqlVisitor` implements `Invercargill.Expressions.ExpressionVisitor` to walk expression trees and generate SQL WHERE clauses.

```vala
public class ExpressionToSqlVisitor : Object, ExpressionVisitor {
    private StringBuilder _sql;
    private SqlDialect _dialect;
    private EntityMapper _entity_mapper;
    
    public void visit_binary(BinaryExpression expr) {
        _sql.append("(");
        expr.left.accept(this);
        
        switch (expr.op) {
            case BinaryOperator.AND:
                _sql.append(" AND ");
                break;
            case BinaryOperator.OR:
                _sql.append(" OR ");
                break;
            case BinaryOperator.EQUAL:
                _sql.append(" = ");
                break;
            // ... other operators
        }
        
        expr.right.accept(this);
        _sql.append(")");
    }
    
    public void visit_property(PropertyExpression expr) {
        // Translate: u.name -> "name" (column name)
        var column_name = _entity_mapper.get_column_name(expr.property_name);
        _sql.append(column_name);
    }
    
    public void visit_literal(LiteralExpression expr) {
        // Generate parameter placeholder
        _sql.append("?");
        // Store value for parameter binding
    }
}
```

### 5. SqlDialect Interface

Database-specific SQL generation is abstracted behind `SqlDialect`:

```vala
public interface SqlDialect : Object {
    // Type translation
    public abstract string translate_type(ColumnType type);
    
    // Expression translation
    public abstract string translate_expression(Expression expr, EntityMapper mapper);
    
    // CRUD SQL generation
    public abstract string build_select(SelectQuery query);
    public abstract string build_insert(InsertQuery query);
    public abstract string build_update(UpdateQuery query);
    public abstract string build_delete(DeleteQuery query);
}
```

### 6. CRUD Operations

```vala
// Insert
var user = new User() {
    name = "John Doe",
    email = "john@example.com",
    is_active = true
};
orm.insert(user);
print("Inserted with ID: %lld\n", user.id);  // ID populated after insert

// Update
user.name = "Jane Doe";
orm.update(user);

// Delete
orm.delete(user);

// Batch operations
var users = new Series<User>();
// ... add users
orm.insert_all(users);
```

## File Structure

```
src/
├── orm/
│   ├── orm-session.vala              # Main ORM entry point
│   ├── entity-mapper.vala            # EntityMapper class
│   ├── entity-mapper-builder.vala    # Fluent builder for EntityMapper
│   ├── column-builder.vala           # Sub-builder for column constraints
│   ├── query.vala                    # Query class
│   ├── column-type.vala              # ColumnType enum
│   ├── column-definition.vala        # Column metadata
│   └── index-definition.vala         # Index metadata
├── dialects/
│   ├── sql-dialect.vala              # Interface for SQL dialects
│   └── sqlite-dialect.vala           # SQLite implementation
└── expressions/
    └── expression-to-sql-visitor.vala # Expression tree to SQL translator
```

## Dependencies

- **Invercargill.Mapping** - `PropertyMapper<T>`, `Mapper<TNative, TElement>`
- **Invercargill.Expressions** - `Expression`, `ExpressionParser`, `ExpressionVisitor`
- **Invercargill.DataStructures** - `Dictionary`, `Vector`, `Series`
- **Invercargill** - `Enumerable`, `Properties`, `Element`

## Implementation Checklist

- [ ] Create `ColumnType` enum with GType mapping
- [ ] Create `ColumnDefinition` and `IndexDefinition` classes
- [ ] Implement `ColumnBuilder` with fluent return to parent
- [ ] Implement `EntityMapperBuilder` with generic type parameter
- [ ] Implement `EntityMapper` wrapping `PropertyMapper`
- [ ] Create `SqlDialect` interface
- [ ] Implement `SqliteDialect` with type translation
- [ ] Implement `ExpressionToSqlVisitor` for WHERE clause generation
- [ ] Create `Query` class with `where()`, `order_by()`, `limit()`, `offset()`
- [ ] Implement `Query.materialise()` using expression translation
- [ ] Implement `OrmSession` with entity registration
- [ ] Implement CRUD operations (insert, update, delete)
- [ ] Add async variants for all operations
- [ ] Write unit tests for entity mapping
- [ ] Write unit tests for query translation
- [ ] Write integration tests with SQLite

## Usage Example

```vala
using InvercargillSql;
using InvercargillSql.Orm;

void main() {
    try {
        // Create connection
        var conn = ConnectionFactory.create_and_open("sqlite::memory:");
        
        // Create ORM session
        var orm = new OrmSession(conn);
        
        // Register entities
        orm.register_entity(EntityMapper.build_for<User>(b => b
            .table("users")
            .column<int64>("id", u => u.id, (u, v) => u.id = v)
                .primary_key()
            .column<string>("name", u => u.name, (u, v) => u.name = v)
                .required()
            .column<string>("email", u => u.email, (u, v) => u.email = v)
                .unique()
            .column<DateTime>("created_at", u => u.created_at, (u, v) => u.created_at = v)
                .default_now()));
        
        // Insert
        var user = new User() { name = "John", email = "john@example.com" };
        orm.insert(user);
        
        // Query
        var active = orm.query<User>()
            .where("u => u.name == 'John'")
            .materialise();
        
        foreach (var u in active) {
            print("User: %s\n", u.name);
        }
        
        // Update
        user.name = "Jane";
        orm.update(user);
        
        // Delete
        orm.delete(user);
        
        conn.close();
    } catch (Error e) {
        stderr.printf("Error: %s\n", e.message);
    }
}
```