---
phase: 26-pk-fk-join-resolution
plan: 02
type: execute
wave: 2
depends_on:
  - 26-01
files_modified:
  - src/expand.rs
  - test/sql/phase26_join_resolution.test
  - test/sql/TEST_LIST
autonomous: true
requirements:
  - EXP-02
  - EXP-04

must_haves:
  truths:
    - "Given PK/FK declarations, the expansion engine generates correct LEFT JOIN ... ON a.fk = b.pk clauses"
    - "Composite PK/FK (multi-column) generates AND-joined ON clauses"
    - "Requesting dimensions from tables A and C connected through B automatically includes A-B and B-C joins"
    - "Only needed joins are included -- requesting dims from A only does not join B or C"
    - "Join ordering follows topological sort, not declaration order"
    - "All generated joins use LEFT JOIN (not bare JOIN/INNER JOIN)"
    - "Legacy definitions with join_columns or on fields still expand correctly (three-tier fallback)"
    - "A multi-table semantic view created with AS-body DDL can be queried end-to-end with correct results"
  artifacts:
    - path: "src/expand.rs"
      provides: "Graph-based resolve_joins_pkfk(), synthesize_on_clause(), LEFT JOIN emission"
      contains: "LEFT JOIN"
    - path: "test/sql/phase26_join_resolution.test"
      provides: "Integration tests for PK/FK join synthesis, transitive inclusion, error cases"
      contains: "phase26"
    - path: "test/sql/TEST_LIST"
      provides: "phase26_join_resolution.test registered"
      contains: "phase26_join_resolution"
  key_links:
    - from: "src/expand.rs"
      to: "src/graph.rs"
      via: "RelationshipGraph::from_definition + toposort for join ordering"
      pattern: "RelationshipGraph"
    - from: "src/expand.rs"
      to: "src/model.rs"
      via: "Join.fk_columns + TableRef.pk_columns for ON clause synthesis"
      pattern: "fk_columns.*pk_columns"
    - from: "test/sql/phase26_join_resolution.test"
      to: "src/expand.rs"
      via: "End-to-end CREATE + query validating generated SQL"
      pattern: "semantic_view"
---

<objective>
Update the expansion engine to use graph-based PK/FK join resolution, and validate end-to-end with sqllogictest.

Purpose: EXP-02 requires JOIN ON clauses synthesized from PK/FK declarations. EXP-04 requires transitive join inclusion. This plan replaces the legacy fixed-point ON-substring matching with proper graph traversal and adds integration tests covering the full CREATE-to-query pipeline.

Output: Updated `src/expand.rs` with graph-based join resolution, PK/FK ON clause synthesis, LEFT JOIN emission, and `phase26_join_resolution.test` integration tests.
</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/26-pk-fk-join-resolution/26-CONTEXT.md
@.planning/phases/26-pk-fk-join-resolution/26-RESEARCH.md
@.planning/phases/26-pk-fk-join-resolution/26-01-SUMMARY.md

<interfaces>
<!-- Interfaces from Plan 01 that this plan depends on -->

From src/graph.rs (created in Plan 01):
```rust
pub struct RelationshipGraph {
    pub edges: HashMap<String, Vec<String>>,
    pub reverse: HashMap<String, Vec<String>>,
    pub all_nodes: HashSet<String>,
    pub root: String,
}

impl RelationshipGraph {
    pub fn from_definition(def: &SemanticViewDefinition) -> Result<Self, String>;
    pub fn toposort(&self) -> Result<Vec<String>, String>;
}

pub fn validate_graph(def: &SemanticViewDefinition) -> Result<RelationshipGraph, String>;
```

From src/expand.rs (existing, to be modified):
```rust
// resolve_joins() at line 168 — REPLACE with graph-based resolution for PK/FK defs
// append_join_on_clause() at line 303 — ADD fk_columns tier before join_columns check
// expand() at line 348 — lines 421-437: change JOIN to LEFT JOIN, use graph ordering
fn resolve_joins<'a>(joins: &'a [Join], resolved_dims, resolved_mets, def) -> Vec<&'a Join>;
fn append_join_on_clause(sql: &mut String, join: &Join, def: &SemanticViewDefinition);
pub fn expand(view_name: &str, def: &SemanticViewDefinition, req: &QueryRequest) -> Result<String, ExpandError>;
```

From src/model.rs:
```rust
pub struct Join {
    pub table: String,       // to_alias (for PK/FK) or physical table (legacy)
    pub on: String,          // legacy Phase 10
    pub join_columns: Vec<JoinColumn>, // Phase 11.1
    pub from_alias: String,  // Phase 24 — FK source
    pub fk_columns: Vec<String>,  // Phase 24 — FK columns
}
pub struct TableRef {
    pub alias: String,
    pub table: String,       // physical table name
    pub pk_columns: Vec<String>,
}
```
</interfaces>
</context>

<tasks>

<task type="auto" tdd="true">
  <name>Task 1: Update expand.rs with graph-based PK/FK join resolution and LEFT JOIN emission</name>
  <files>src/expand.rs</files>
  <behavior>
    - PK/FK ON synthesis: def with join(from_alias="o", fk_columns=["customer_id"], table="c") and table "c" pk_columns=["id"] generates "o.customer_id = c.id" ON clause
    - Composite ON: join with fk_columns=["fk1","fk2"] against pk_columns=["pk1","pk2"] generates "a.fk1 = b.pk1 AND a.fk2 = b.pk2"
    - LEFT JOIN: all joins in expanded SQL use "LEFT JOIN" not bare "JOIN"
    - Transitive inclusion: dims from A and C (connected A->B->C) include both B and C joins
    - Pruning: dims only from A (base) include no joins at all
    - Topological order: joins emitted in toposort order (root-outward), not declaration order
    - Three-tier fallback: legacy def with join_columns still works (expand still produces correct SQL)
    - Three-tier fallback: legacy def with on string still works
    - PK/FK path uses `from_alias` for the FROM side and `join.table` for the TO side (matching graph edge direction)
    - Alias lookup for join emission uses alias from def.tables (not physical table name) for AS clause
  </behavior>
  <action>
Modify `src/expand.rs`:

1. Add import: `use crate::graph::RelationshipGraph;`

2. Add `synthesize_on_clause(join: &Join, tables: &[TableRef]) -> String`:
   - Find referenced table via `join.table` (case-insensitive match against `tables[].alias`)
   - Zip `join.fk_columns` with `referenced_table.pk_columns`
   - Generate `quote_ident(join.from_alias).quote_ident(fk) = quote_ident(join.table).quote_ident(pk)` pairs
   - Join with " AND "

3. Add `resolve_joins_pkfk(def: &SemanticViewDefinition, resolved_dims, resolved_mets) -> Vec<String>`:
   - Build RelationshipGraph from def (if from_definition fails, fall back to empty — graph was validated at define time)
   - Collect needed aliases from source_table fields of resolved dims/metrics (lowercased)
   - For each needed alias, walk reverse edges back to root, collecting all intermediate aliases
   - Return needed aliases in topological order (filter toposort output to only needed aliases)
   - If no source_table fields reference non-base tables, return empty vec

4. Update `expand()` function (around line 407-437):
   - Detect PK/FK mode: `let has_pkfk = def.joins.iter().any(|j| !j.fk_columns.is_empty());`
   - If has_pkfk:
     - Call `resolve_joins_pkfk()` to get ordered alias list
     - For each alias in order, find the Join and TableRef
     - Emit `LEFT JOIN quote_table_ref(table_ref.table) AS quote_ident(alias) ON synthesize_on_clause(...)`
   - If NOT has_pkfk (legacy path):
     - Use existing `resolve_joins()` and `append_join_on_clause()`
     - BUT change `JOIN` to `LEFT JOIN` per user decision (LEFT JOIN is global for ALL definitions)

5. Update the legacy join emission block (lines 421-422):
   - Change `sql.push_str("\n    JOIN ");` to `sql.push_str("\n    LEFT JOIN ");`
   - This applies LEFT JOIN to ALL definitions per the locked decision

6. Keep `resolve_joins` and `append_join_on_clause` for legacy fallback. Do NOT remove them.

Add unit tests in the existing `mod tests` block:
- `test_pkfk_on_clause_simple`: single FK->PK generates correct ON
- `test_pkfk_on_clause_composite`: multi-column FK->PK generates AND-joined ON
- `test_pkfk_left_join_emitted`: expanded SQL contains "LEFT JOIN" not bare "JOIN"
- `test_pkfk_transitive_join_inclusion`: A->B->C, request dim from C includes B join
- `test_pkfk_pruning`: A->B->C, request dim from A only includes no joins
- `test_pkfk_topological_order`: joins emitted root-outward regardless of declaration order
- `test_legacy_join_columns_still_works`: existing Phase 11.1 def expands correctly with LEFT JOIN
- `test_legacy_on_string_still_works`: existing Phase 10 def expands correctly with LEFT JOIN
  </action>
  <verify>
    <automated>cd /Users/paul/Documents/Dev/Personal/duckdb-semantic-views && cargo test --lib expand</automated>
  </verify>
  <done>
    - PK/FK ON clause synthesis works for single and composite keys
    - Graph-based transitive join resolution replaces fixed-point loop for PK/FK definitions
    - All joins emit LEFT JOIN (both PK/FK and legacy paths)
    - Topological ordering determines join emission order
    - Legacy join_columns and on-string fallbacks preserved and working
    - All expand unit tests pass
  </done>
</task>

<task type="auto">
  <name>Task 2: Add sqllogictest integration tests for PK/FK join resolution</name>
  <files>test/sql/phase26_join_resolution.test, test/sql/TEST_LIST</files>
  <action>
Create `test/sql/phase26_join_resolution.test`:

```
# Phase 26: PK/FK Join Resolution
# Tests: PK/FK ON clause synthesis, transitive join inclusion, LEFT JOIN,
#        graph validation errors (cycle, diamond, orphan, self-ref)

require semantic_views

# --- Setup: create test tables with p26_ prefix ---
statement ok
CREATE TABLE p26_customers (id INTEGER, name VARCHAR);

statement ok
INSERT INTO p26_customers VALUES (1, 'Alice'), (2, 'Bob');

statement ok
CREATE TABLE p26_orders (id INTEGER, customer_id INTEGER, amount DECIMAL(10,2));

statement ok
INSERT INTO p26_orders VALUES (1, 1, 100.00), (2, 1, 200.00), (3, 2, 50.00);

statement ok
CREATE TABLE p26_line_items (id INTEGER, order_id INTEGER, product VARCHAR, qty INTEGER);

statement ok
INSERT INTO p26_line_items VALUES (1, 1, 'Widget', 2), (2, 2, 'Gadget', 1), (3, 3, 'Widget', 3);

# --- Test 1: Basic PK/FK join (EXP-02) ---
statement ok
CREATE SEMANTIC VIEW p26_sales AS
  TABLES (
    o AS p26_orders PRIMARY KEY (id),
    c AS p26_customers PRIMARY KEY (id)
  )
  RELATIONSHIPS (
    order_to_customer AS o(customer_id) REFERENCES c
  )
  DIMENSIONS (
    c.customer_name AS c.name
  )
  METRICS (
    o.total_amount AS sum(o.amount)
  );

# Query crossing join boundary (customer_name from c, total_amount from o)
query TR
SELECT * FROM semantic_view('p26_sales', dimensions := ['customer_name'], metrics := ['total_amount']) ORDER BY customer_name;
----
Alice	300.00
Bob	50.00

# --- Test 2: Transitive join inclusion through 3 tables (EXP-04) ---
statement ok
CREATE SEMANTIC VIEW p26_detailed AS
  TABLES (
    o AS p26_orders PRIMARY KEY (id),
    c AS p26_customers PRIMARY KEY (id),
    li AS p26_line_items PRIMARY KEY (id)
  )
  RELATIONSHIPS (
    order_to_customer AS o(customer_id) REFERENCES c,
    item_to_order AS li(order_id) REFERENCES o
  )
  DIMENSIONS (
    c.customer_name AS c.name,
    li.product AS li.product
  )
  METRICS (
    li.total_qty AS sum(li.qty)
  );

# Query from li to c: should auto-join through o
query TI
SELECT * FROM semantic_view('p26_detailed', dimensions := ['customer_name'], metrics := ['total_qty']) ORDER BY customer_name;
----
Alice	3
Bob	3

# --- Test 3: Pruning — query only base table dims needs no joins ---
query T
SELECT * FROM semantic_view('p26_detailed', dimensions := ['product']) ORDER BY product;
----
Gadget
Widget

# --- Test 4: LEFT JOIN preserves base table rows ---
# Add an order with no matching customer
statement ok
INSERT INTO p26_orders VALUES (4, 999, 75.00);

query TR
SELECT * FROM semantic_view('p26_sales', dimensions := ['customer_name'], metrics := ['total_amount']) ORDER BY total_amount;
----
NULL	75.00
Bob	50.00
Alice	300.00

# --- Test 5: Graph validation errors (EXP-06) ---

# Cycle: a->b->a
statement error
CREATE SEMANTIC VIEW p26_cycle AS
  TABLES (
    a AS p26_orders PRIMARY KEY (id),
    b AS p26_customers PRIMARY KEY (id)
  )
  RELATIONSHIPS (
    a_to_b AS a(customer_id) REFERENCES b,
    b_to_a AS b(id) REFERENCES a
  )
  DIMENSIONS (a.dim1 AS a.amount)
  METRICS (a.met1 AS sum(a.amount));
----
cycle detected

# Self-reference
statement error
CREATE SEMANTIC VIEW p26_selfref AS
  TABLES (
    o AS p26_orders PRIMARY KEY (id)
  )
  RELATIONSHIPS (
    self_ref AS o(customer_id) REFERENCES o
  )
  DIMENSIONS (o.dim1 AS o.amount)
  METRICS (o.met1 AS sum(o.amount));
----
cannot reference itself

# --- Cleanup ---
statement ok
DROP SEMANTIC VIEW p26_sales;

statement ok
DROP SEMANTIC VIEW p26_detailed;

statement ok
DROP TABLE p26_line_items;

statement ok
DROP TABLE p26_orders;

statement ok
DROP TABLE p26_customers;
```

Add `phase26_join_resolution.test` to `test/sql/TEST_LIST`.

Note: The exact expected output (column types in query headers, NULL formatting) may need adjustment based on actual DuckDB behavior. Adjust the query result assertions as needed during execution. The key invariants are:
- Alice has total 300.00 (two orders: 100+200)
- Bob has total 50.00 (one order)
- The NULL row from the unmatched FK (customer_id=999) appears due to LEFT JOIN
- Transitive join through 3 tables produces correct aggregation
  </action>
  <verify>
    <automated>cd /Users/paul/Documents/Dev/Personal/duckdb-semantic-views && just build && just test-sql</automated>
  </verify>
  <done>
    - phase26_join_resolution.test exists and is registered in TEST_LIST
    - Basic PK/FK join produces correct query results
    - Transitive 3-table join works (A->B->C auto-join)
    - LEFT JOIN preserves unmatched base table rows (NULL customer for FK=999)
    - Graph validation errors (cycle, self-ref) produce expected error messages
    - just test-sql passes with all test files
    - just test-all passes (full quality gate)
  </done>
</task>

</tasks>

<verification>
- `cargo test --lib expand` runs all expand unit tests including new PK/FK tests
- `cargo test` passes (all Rust tests)
- `just build && just test-sql` passes (all sqllogictest files including phase26)
- `just test-all` passes (full quality gate per CLAUDE.md)
</verification>

<success_criteria>
- PK/FK ON clauses are synthesized from from_alias.fk_columns = to_alias.pk_columns
- Transitive join inclusion works: requesting dims from A and C auto-joins through B
- All joins use LEFT JOIN (global change per user decision)
- Join ordering follows topological sort from graph.rs
- Legacy definitions (join_columns, on string) continue to expand correctly
- End-to-end integration tests pass in sqllogictest
- just test-all green
</success_criteria>

<output>
After completion, create `.planning/phases/26-pk-fk-join-resolution/26-02-SUMMARY.md`
</output>
