---
phase: 32-role-playing-using
plan: 02
type: execute
wave: 2
depends_on: ["32-01"]
files_modified:
  - src/expand.rs
  - test/sql/phase32_role_playing.test
  - test/sql/TEST_LIST
autonomous: true
requirements: [JOIN-03, JOIN-05, ROLE-01, ROLE-02, ROLE-03]

must_haves:
  truths:
    - "Metrics with USING expand with relationship-scoped aliases ({to_alias}__{rel_name}) in generated SQL"
    - "Same physical table joined via different named relationships produces separate LEFT JOINs with distinct aliases"
    - "Dimensions from a role-playing table resolve to the correct scoped alias based on co-queried metric's USING"
    - "Querying a dimension from an ambiguous multi-path table without USING produces an AmbiguousPath error"
    - "Classic flights/airports role-playing pattern works end-to-end through DDL -> query -> correct results"
    - "Fan trap detection still works correctly with USING-scoped paths"
  artifacts:
    - path: "src/expand.rs"
      provides: "AmbiguousPath error variant, USING-aware resolve_joins_pkfk, scoped alias generation, dimension resolution"
      contains: "AmbiguousPath"
    - path: "test/sql/phase32_role_playing.test"
      provides: "End-to-end sqllogictest for flights/airports role-playing pattern"
      min_lines: 50
  key_links:
    - from: "src/expand.rs"
      to: "src/model.rs"
      via: "reads metric.using_relationships to determine scoped aliases"
      pattern: "using_relationships"
    - from: "src/expand.rs"
      to: "src/graph.rs"
      via: "RelationshipGraph identifies multi-path tables for ambiguity detection"
      pattern: "reverse.*len.*>.*1"
    - from: "test/sql/phase32_role_playing.test"
      to: "src/expand.rs"
      via: "End-to-end test exercises full DDL -> query -> expansion pipeline"
      pattern: "semantic_view"
---

<objective>
Extend the expansion engine to generate relationship-scoped aliases for USING metrics, detect ambiguous multi-path dimensions, and verify the complete role-playing pattern end-to-end.

Purpose: This plan delivers the runtime behavior that makes role-playing dimensions work. After Plan 01 established the model and validation, this plan teaches the expansion engine to generate separate LEFT JOINs with scoped aliases (`a__dep_airport`, `a__arr_airport`), resolve dimensions to the correct scoped alias based on co-queried metrics' USING context, and block ambiguous queries with clear errors.

Output: USING-aware expansion in expand.rs, AmbiguousPath error variant, end-to-end sqllogictest for flights/airports pattern.
</objective>

<execution_context>
@/Users/paul/.claude/get-shit-done/workflows/execute-plan.md
@/Users/paul/.claude/get-shit-done/templates/summary.md
</execution_context>

<context>
@.planning/PROJECT.md
@.planning/ROADMAP.md
@.planning/STATE.md
@.planning/phases/32-role-playing-using/32-RESEARCH.md
@.planning/phases/32-role-playing-using/32-01-SUMMARY.md

<interfaces>
<!-- Key types and contracts from Plan 01 (to be created). -->

From src/model.rs (after Plan 01):
```rust
pub struct Metric {
    pub name: String,
    pub expr: String,
    pub source_table: Option<String>,
    pub output_type: Option<String>,
    #[serde(default, skip_serializing_if = "Vec::is_empty")]
    pub using_relationships: Vec<String>,
}
```

From src/expand.rs (existing):
```rust
pub enum ExpandError {
    EmptyRequest { view_name: String },
    UnknownDimension { view_name, name, available, suggestion },
    UnknownMetric { view_name, name, available, suggestion },
    DuplicateDimension { view_name, name },
    DuplicateMetric { view_name, name },
    FanTrap { view_name, metric_name, metric_table, dimension_name, dimension_table, relationship_name },
    // Phase 32 adds: AmbiguousPath { view_name, dimension_name, dimension_table, available_relationships },
}

fn resolve_joins_pkfk(
    def: &SemanticViewDefinition,
    resolved_dims: &[&Dimension],
    resolved_mets: &[&Metric],
) -> Vec<String>

fn synthesize_on_clause(join: &Join, tables: &[TableRef]) -> String

pub fn expand(
    view_name: &str,
    def: &SemanticViewDefinition,
    req: &QueryRequest,
) -> Result<String, ExpandError>
```

From src/graph.rs:
```rust
pub struct RelationshipGraph {
    pub edges: HashMap<String, Vec<String>>,
    pub reverse: HashMap<String, Vec<String>>,  // to_alias -> [from_alias, ...]
    pub all_nodes: HashSet<String>,
    pub root: String,
}
```
</interfaces>
</context>

<tasks>

<task type="auto" tdd="true">
  <name>Task 1: USING-aware expansion with scoped aliases and ambiguity detection</name>
  <files>src/expand.rs</files>
  <behavior>
    - Test: Metric with USING (dep_airport) generates JOIN with alias "a__dep_airport" (scoped)
    - Test: Two metrics with different USING generate two separate LEFT JOINs to same physical table with distinct scoped aliases
    - Test: Dimension expression for role-playing table is rewritten from "a.city" to "a__dep_airport.city" when co-queried metric uses dep_airport
    - Test: Dimension from ambiguous table (multiple relationships) without any metric USING produces AmbiguousPath error
    - Test: AmbiguousPath error message lists available relationships
    - Test: Dimension from non-ambiguous table (single relationship) works without USING
    - Test: Dimension from base table (no relationships needed) works unchanged
    - Test: Fan trap detection still works with USING-scoped paths
    - Test: Derived metric referencing two base metrics with different USING paths resolves both join paths
    - Test: Metric with USING from base table (no join needed) does not emit unnecessary JOIN
  </behavior>
  <action>
1. Add `AmbiguousPath` variant to `ExpandError`:
   ```rust
   AmbiguousPath {
       view_name: String,
       dimension_name: String,
       dimension_table: String,
       available_relationships: Vec<String>,
   }
   ```
   Add its `Display` implementation: "semantic view '{view_name}': dimension '{dimension_name}' is ambiguous -- table '{dimension_table}' is reached via multiple relationships: [{rels}]. Specify a metric with USING to disambiguate, or use a dimension from a non-ambiguous table."

2. Create a helper function `find_using_context()` that, given resolved metrics and a dimension's source_table, determines which relationship-scoped alias to use:
   - Build a set of relationships that point to the dimension's source table (from the graph's reverse map or from def.joins).
   - If only one relationship points to that table -> unambiguous, return the bare alias (no scoping needed, existing behavior).
   - If multiple relationships point to that table -> look through co-queried metrics' `using_relationships` for one that targets this table. If exactly one metric provides USING context for this table -> return scoped alias `{to_alias}__{rel_name}`. If zero metrics provide context -> return AmbiguousPath error. If multiple metrics use different relationships to the same table -> each metric gets its own scoped alias; dimensions must be queried with a single unambiguous path.

3. Modify `resolve_joins_pkfk()` to be USING-aware:
   - When a metric has `using_relationships`, instead of adding the bare `to_alias` to `needed`, add scoped alias entries `{to_alias}__{rel_name}` for each relationship in the USING list.
   - For derived metrics, walk dependency graph transitively via `collect_derived_metric_source_tables` -- but now also collect the USING relationships from referenced base metrics to know which scoped aliases are needed.
   - The returned Vec should include scoped alias strings where applicable.

4. Modify the JOIN generation section of `expand()` (around line 1104-1125):
   - When a scoped alias (containing `__`) is in `ordered_aliases`, find the relationship by name (the part after `__`), look up the Join entry for that relationship, and generate: `LEFT JOIN "physical_table" AS "scoped_alias" ON synthesize_on_clause_scoped(...)`.
   - Create a `synthesize_on_clause_scoped()` helper (or extend `synthesize_on_clause`) that uses the scoped alias in the ON clause instead of the bare alias. The FK side uses `join.from_alias`, the PK side uses the scoped alias.

5. Modify dimension expression rendering in `expand()` (around line 1068-1077):
   - Before rendering each dimension's expression, check if the dimension's source_table has multiple relationship paths (is a role-playing table).
   - If so, use `find_using_context()` to determine the scoped alias, and rewrite the dimension expression: replace occurrences of the bare alias with the scoped alias (e.g., `a.city` becomes `a__dep_airport.city`). Use the `replace_word_boundary` helper for safe replacement of the alias prefix.
   - If `find_using_context()` returns an error (ambiguous), return that error.

6. Ensure `check_fan_traps()` still works with USING-scoped paths. The cardinality map keys use `(from_alias, to_alias)` bare aliases, so fan trap detection should still function correctly since it checks the logical relationship structure, not the physical alias. If the scoped aliases cause issues, adjust the fan trap check to normalize scoped aliases back to bare aliases before lookup.

7. Add comprehensive unit tests in `src/expand.rs` (phase32 test module):
   - Test flights/airports definition with two relationships (dep_airport, arr_airport) and two metrics with USING. Verify:
     - Single metric + city dimension -> one scoped JOIN
     - Both metrics + carrier dimension -> two scoped JOINs
     - City dimension without any metric USING -> AmbiguousPath error
     - City dimension with one metric USING -> resolves to correct scoped alias
   - Test derived metric referencing two USING metrics -> both JOINs emitted
   - Test backward compat: definition without any USING expands as before
   - Test fan trap still detects fan-out with USING paths
  </action>
  <verify>
    <automated>cargo test -- --test-threads=1 2>&1 | tail -5</automated>
  </verify>
  <done>
    - AmbiguousPath error variant exists with descriptive Display
    - Metrics with USING generate relationship-scoped aliases in JOINs
    - Dimensions from role-playing tables resolve via co-queried metric USING context
    - Ambiguous dimensions without USING produce clear AmbiguousPath error
    - Fan trap detection works with USING-scoped paths
    - All existing unit tests pass (no regression)
  </done>
</task>

<task type="auto">
  <name>Task 2: End-to-end sqllogictest and full test suite verification</name>
  <files>test/sql/phase32_role_playing.test, test/sql/TEST_LIST</files>
  <action>
1. Create `test/sql/phase32_role_playing.test` with comprehensive end-to-end scenarios:

   **Setup:** Create flights and airports tables with test data.
   ```sql
   CREATE TABLE airports (airport_code VARCHAR, city VARCHAR, country VARCHAR);
   INSERT INTO airports VALUES ('SFO', 'San Francisco', 'US'), ('JFK', 'New York', 'US'), ('LHR', 'London', 'UK');
   CREATE TABLE flights (flight_id INT, departure_code VARCHAR, arrival_code VARCHAR, carrier VARCHAR);
   INSERT INTO flights VALUES (1, 'SFO', 'JFK', 'AA'), (2, 'JFK', 'LHR', 'BA'), (3, 'LHR', 'SFO', 'AA');
   ```

   **Test 1 (ROLE-03 core):** Define semantic view with two relationships and USING metrics. Query `dimensions := ['city'], metrics := ['departure_count']` -- verify scoped alias produces correct city grouping for departures.

   **Test 2:** Query `dimensions := ['carrier'], metrics := ['departure_count', 'arrival_count']` -- verify both scoped JOINs emitted and carrier dimension (from non-ambiguous base table) works alongside role-playing metrics.

   **Test 3 (JOIN-05):** Query `dimensions := ['city'], metrics := ['total_flights']` where total_flights is a derived metric referencing both USING metrics -- verify AmbiguousPath error for ambiguous dimension.

   **Test 4:** Query `dimensions := ['carrier'], metrics := ['total_flights']` -- verify derived metric with no ambiguous dimensions works (carrier is from base table flights, not airports).

   **Test 5 (JOIN-04):** Define semantic view with USING referencing non-existent relationship -- verify define-time error.

   **Test 6:** Query metrics only (no dimensions): `metrics := ['departure_count']` -- verify scoped JOIN still emitted (metric needs its relationship).

   **Test 7:** Define a standard semantic view (no role-playing) and verify it still works unchanged (backward compat).

   **Test 8:** Single relationship to airports (not role-playing) -- city dimension resolves without USING (no ambiguity).

2. Register `phase32_role_playing` in `test/sql/TEST_LIST`.

3. Run `just build && just test-sql` to verify all sqllogictests pass.

4. Run `just test-all` to verify the complete test suite (unit + proptest + sqllogictest + DuckLake CI).
  </action>
  <verify>
    <automated>just test-all 2>&1 | tail -10</automated>
  </verify>
  <done>
    - phase32_role_playing.test passes with 8+ scenarios covering all JOIN/ROLE requirements
    - Full test suite green: cargo test + sqllogictest + DuckLake CI
    - No regressions in any prior phase tests
    - Flights/airports role-playing pattern produces correct query results end-to-end
  </done>
</task>

</tasks>

<verification>
Run `just test-all` to verify the complete test suite:
- `cargo test` -- all unit tests, proptests, doc tests (including new Phase 32 tests)
- `just test-sql` -- all sqllogictests (including phase32_role_playing.test)
- `just test-ducklake-ci` -- DuckLake integration tests

This is the quality gate per CLAUDE.md.
</verification>

<success_criteria>
- Metrics with USING generate separate LEFT JOINs with scoped aliases (`{alias}__{rel_name}`)
- Same physical table appears multiple times with distinct aliases in expanded SQL
- Dimensions from role-playing tables resolve to correct alias via metric USING context
- Ambiguous dimensions produce clear AmbiguousPath error listing available relationships
- Flights/airports pattern works end-to-end: DDL defines, query expands, results are correct
- Fan trap detection works correctly with USING-scoped paths
- `just test-all` passes with zero failures
</success_criteria>

<output>
After completion, create `.planning/phases/32-role-playing-using/32-02-SUMMARY.md`
</output>
