
---

### **Core Syntax Specification**

#### 1. **Basic Column Access**
```python
@{column}  # Current table's column value
```
**Examples:**  
- `@project_name` - Current project's name  
- `@budget` - Current record's budget value  

---

#### 2. **Direct Relationship Access**
```python
@{table}.{column}        # Direct parent/child single record
@{table}.{column}[0]     # First record in one-to-many
```
**Examples:**  
- `@author.name` - Name of directly linked author  
- `@order_items.quantity[0]` - Quantity from first order item  

---

#### 3. **Collection Operations**
```python
@{table}.{column}.list   # All values from relationship
@{table}.{column}.all    # All values from table
```
**Examples:**  
- `@products.price.list` - List of prices from linked products  
- `@customers.email.all` - All customer emails in table  

---

#### 4. **Deep Relationship Navigation**
```python
@{target_table}.{column}       # Column from any linked table (automatically resolves path)
@{target_table}.{column}[n]    # Indexed access to a specific row in a one-to-many relationship
```
**Examples:**  
- `@customer.address` - Customer address (resolves through any path, e.g., `order → customer`)  
- `@products[2].sku` - Third product's SKU (resolves through any path, e.g., `order → products`)  

---

#### 5. **SQL Integration**
```python
@sql.@{column}(query)          # Custom SQL for a specific column
@sql.@{table}.{column}(query)  # Custom SQL for a linked table's column
@sql(query)                    # Raw SQL execution
```
**Examples:**  
- `@sql.@budget("SELECT AVG(budget) FROM current_table")`  
- `@sql.@products.price("SELECT MAX(price) FROM {linked_products}")`  
- `@sql("SELECT COUNT(*) FROM archived_orders")`  

---

### **Key Changes and Clarifications**

1. **Deep Relationship Navigation**  
   - Only the **target table name** is specified.  
   - The system **automatically resolves the path** using the relationships defined in `table_definition_links`.  
   - Example:  
     ```python
     @customer.address  # Resolves through any path (e.g., order → customer)
     @products[0].sku   # Resolves through any path (e.g., order → products)
     ```

2. **Indexed Access**  
   - Use `[n]` to access a specific row in a one-to-many relationship.  
   - Example:  
     ```python
     @products[2].price  # Price of the third linked product
     ```

3. **Collection Operations**  
   - `.list` and `.all` are used to differentiate between:  
     - **Relationship-specific collections** (`.list`)  
     - **Entire table collections** (`.all`)  

4. **SQL Integration**  
   - Allows custom SQL for specific columns or tables.  
   - Ensures SQL queries are scoped to the current context.  

---

### **Examples**

#### Basic Column Access
```python
@project_name  # "Project Alpha"
@budget        # "10000"
```

#### Direct Relationship Access
```python
@author.name           # "John Doe"
@order_items[0].price  # "49.99"
```

#### Collection Operations
```python
@products.price.list  # ["49.99", "99.99", "149.99"]
@customers.email.all  # ["john@example.com", "jane@example.com"]
```

#### Deep Relationship Navigation
```python
@customer.address  # "123 Main St" (resolves through order → customer)
@products[1].sku   # "SKU12345" (resolves through order → products)
```

#### SQL Integration
```python
@sql.@budget("SELECT AVG(budget) FROM current_table")  # "7500"
@sql.@products.price("SELECT MAX(price) FROM {linked_products}")  # "149.99"
@sql("SELECT COUNT(*) FROM archived_orders")  # "42"
```

---

