---
phase: 33-unique-constraints-cardinality-inference
plan: 02
type: execute
wave: 2
depends_on: [33-01]
files_modified:
  - src/graph.rs
  - src/expand.rs
  - src/ddl/define.rs
  - src/ddl/describe.rs
  - test/sql/phase31_fan_trap.test
  - test/sql/phase32_role_playing.test
  - test/sql/phase33_cardinality_inference.test
  - tests/expand_proptest.rs
autonomous: true
requirements: [CARD-03, CARD-08, CARD-09]

must_haves:
  truths:
    - "FK referenced columns must match a declared PK or UNIQUE on the target table -- error at define time if not"
    - "Composite FK referencing a subset of a composite PK is rejected (exact match required)"
    - "Fan trap detection works correctly with two-variant cardinality (ManyToOne reverse = fan-out, OneToOne = safe)"
    - "ON clause synthesis uses Join.ref_columns instead of hardcoded pk_columns lookup"
    - "Old-format JSON (v0.5.3) is rejected on load with clear error message"
    - "DESCRIBE shows UNIQUE constraints in tables JSON and inferred cardinality in joins JSON"
    - "Existing sqllogictests updated to use new syntax and pass"
    - "New phase33 sqllogictest covers all CARD requirements end-to-end"
    - "just test-all passes"
  artifacts:
    - path: "src/graph.rs"
      provides: "CARD-03/09 FK reference validation against PK/UNIQUE"
      contains: "validate_fk_references"
    - path: "src/expand.rs"
      provides: "Fan trap detection without OneToMany, ON clause using ref_columns"
    - path: "src/ddl/define.rs"
      provides: "Old-JSON detection guard"
      contains: "created with an older version"
    - path: "src/ddl/describe.rs"
      provides: "UNIQUE constraints and inferred cardinality in DESCRIBE output"
    - path: "test/sql/phase33_cardinality_inference.test"
      provides: "End-to-end sqllogictest for CARD-01 through CARD-09"
  key_links:
    - from: "src/graph.rs"
      to: "src/model.rs"
      via: "validate_fk_references reads TableRef.unique_constraints and Join.ref_columns"
      pattern: "unique_constraints"
    - from: "src/expand.rs"
      to: "src/model.rs"
      via: "synthesize_on_clause_scoped uses Join.ref_columns"
      pattern: "ref_columns"
    - from: "src/ddl/define.rs"
      to: "src/model.rs"
      via: "Old-JSON check reads Join.ref_columns emptiness"
      pattern: "ref_columns.is_empty"
---

<objective>
Implement CARD-03/CARD-09 validation (FK must match PK/UNIQUE on target), adapt fan trap detection and ON clause synthesis for the new model, add old-JSON rejection, update DESCRIBE output, and update all tests to use the new syntax.

Purpose: Completes the Snowflake-aligned cardinality inference feature. After this plan, `just test-all` passes with the new UNIQUE + inference model.

Output: Modified graph.rs, expand.rs, define.rs, describe.rs. Updated phase31/32 tests. New phase33 sqllogictest.
</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/33-unique-constraints-cardinality-inference/33-CONTEXT.md
@.planning/phases/33-unique-constraints-cardinality-inference/33-RESEARCH.md
@.planning/phases/33-unique-constraints-cardinality-inference/33-01-SUMMARY.md

<interfaces>
<!-- After Plan 01, these are the key contracts: -->

From src/model.rs (after Plan 01):
```rust
pub struct TableRef {
    pub alias: String,
    pub table: String,
    #[serde(default, skip_serializing_if = "Vec::is_empty")]
    pub pk_columns: Vec<String>,
    #[serde(default, skip_serializing_if = "Vec::is_empty")]
    pub unique_constraints: Vec<Vec<String>>,
}

pub enum Cardinality {
    #[default]
    ManyToOne,
    OneToOne,
    // OneToMany is GONE
}

pub struct Join {
    // ... existing fields ...
    pub fk_columns: Vec<String>,
    #[serde(default, skip_serializing_if = "Vec::is_empty")]
    pub ref_columns: Vec<String>,  // NEW: resolved target-side columns
    pub cardinality: Cardinality,
}
```

From src/graph.rs (current state, to be modified):
```rust
fn check_fk_pk_counts(def: &SemanticViewDefinition) -> Result<(), String>;
pub fn validate_graph(def: &SemanticViewDefinition) -> Result<RelationshipGraph, String>;
```

From src/expand.rs (current state, to be modified):
```rust
fn synthesize_on_clause_scoped(join: &Join, tables: &[TableRef], to_alias: &str) -> String;
fn check_fan_traps(...) -> Result<(), ExpandError>;
fn check_path_up(...) -> Option<ExpandError>;
fn check_path_down(...) -> Option<ExpandError>;
// FanTrap error message contains "(ONE TO MANY)" -- must change to inference language
```

From src/ddl/define.rs (current state):
```rust
impl VTab for DefineFromJsonVTab {
    fn bind(bind: &BindInfo) -> Result<Self::BindData, Box<dyn std::error::Error>> {
        let mut def = SemanticViewDefinition::from_json(&name, &json)?;
        validate_graph(&def)?;   // <-- old-JSON check goes BEFORE this
        validate_facts(&def)?;
        // ...
    }
}
```
</interfaces>
</context>

<tasks>

<task type="auto" tdd="true">
  <name>Task 1: Add CARD-03/09 validation, adapt fan trap, ON clause, old-JSON guard, DESCRIBE</name>
  <files>src/graph.rs, src/expand.rs, src/ddl/define.rs, src/ddl/describe.rs</files>
  <read_first>src/graph.rs, src/expand.rs, src/ddl/define.rs, src/ddl/describe.rs, src/model.rs</read_first>
  <behavior>
    - Test: FK referencing PK on target passes validation (CARD-03 happy path)
    - Test: FK referencing UNIQUE on target passes validation (CARD-03 happy path)
    - Test: FK referencing columns that are NOT PK or UNIQUE on target fails with error listing available constraints (CARD-03)
    - Test: Composite FK referencing subset of composite PK fails (CARD-09)
    - Test: Fan trap with ManyToOne reverse still detected (no OneToMany branches)
    - Test: Fan trap error message uses inference language
    - Test: ON clause synthesis uses ref_columns from Join (not pk_columns from target table)
    - Test: Old JSON with fk_columns but no ref_columns triggers legacy error
    - Test: DESCRIBE output includes unique_constraints in tables JSON
    - Test: DESCRIBE output includes ref_columns and cardinality in joins JSON
  </behavior>
  <action>
**A. graph.rs -- Replace `check_fk_pk_counts` with `validate_fk_references`:**

1. Replace `check_fk_pk_counts` (lines ~218-243) with a new function `validate_fk_references` that:
   - For each join with non-empty `fk_columns` and non-empty `ref_columns`:
     - Find the target table by `join.table` alias (case-insensitive)
     - Collect the ref_columns as a lowercase `HashSet`
     - Check against target's `pk_columns` (as lowercase HashSet) -- if exact set match, valid
     - Check against each of target's `unique_constraints` (as lowercase HashSet) -- if any exact set match, valid
     - If NEITHER matches, build error message:
       ```
       "FK ({fk_cols_joined}) in relationship '{rel_name}' does not match any PRIMARY KEY or \
        UNIQUE constraint on table '{target_alias}'. Available: {available_list}."
       ```
       Where `available_list` formats as: `PK(id)` and/or `UNIQUE(email)`, `UNIQUE(code, region)`.
   - This covers CARD-03 (must match PK or UNIQUE) and CARD-09 (exact set match, not subset).

   ```rust
   fn validate_fk_references(def: &SemanticViewDefinition) -> Result<(), String> {
       for join in &def.joins {
           if join.fk_columns.is_empty() || join.ref_columns.is_empty() {
               continue;
           }
           let to_alias_lower = join.table.to_ascii_lowercase();
           let target = def.tables.iter()
               .find(|t| t.alias.to_ascii_lowercase() == to_alias_lower);
           let Some(target) = target else { continue };

           let ref_set: std::collections::HashSet<String> = join.ref_columns.iter()
               .map(|c| c.to_ascii_lowercase())
               .collect();

           // Check PK
           let pk_set: std::collections::HashSet<String> = target.pk_columns.iter()
               .map(|c| c.to_ascii_lowercase())
               .collect();
           if !pk_set.is_empty() && ref_set == pk_set {
               continue; // Valid: matches PK
           }

           // Check UNIQUE constraints
           let matches_unique = target.unique_constraints.iter().any(|uc| {
               let uc_set: std::collections::HashSet<String> = uc.iter()
                   .map(|c| c.to_ascii_lowercase())
                   .collect();
               ref_set == uc_set
           });
           if matches_unique {
               continue; // Valid: matches a UNIQUE constraint
           }

           // Neither matches -- build error
           let rel_name = join.name.as_deref().unwrap_or("?");
           let ref_cols = join.ref_columns.join(", ");
           let mut available = Vec::new();
           if !target.pk_columns.is_empty() {
               available.push(format!("PK({})", target.pk_columns.join(", ")));
           }
           for uc in &target.unique_constraints {
               available.push(format!("UNIQUE({})", uc.join(", ")));
           }
           let available_str = if available.is_empty() {
               "none declared".to_string()
           } else {
               available.join(", ")
           };
           return Err(format!(
               "FK ({ref_cols}) in relationship '{rel_name}' does not match any PRIMARY KEY or \
                UNIQUE constraint on table '{}'. Available: {available_str}.",
               target.alias
           ));
       }
       Ok(())
   }
   ```

2. In `validate_graph`, replace `check_fk_pk_counts(def)?;` (line ~319) with `validate_fk_references(def)?;`.

3. Add `use std::collections::HashSet;` at the top if not already imported (it IS imported on line 10).

4. Update unit tests in `graph.rs` `#[cfg(test)]` module:
   - Add `mod phase33_fk_reference_tests` with tests for:
     - `fk_matches_pk_passes` -- standard PK reference validation
     - `fk_matches_unique_passes` -- UNIQUE reference validation
     - `fk_no_match_errors_with_available` -- neither PK nor UNIQUE match -> error
     - `composite_fk_subset_rejected` -- FK refs (a) but PK is (a, b) -> rejected (CARD-09)
     - `case_insensitive_matching` -- columns differ in case but match
   - Update all existing `TableRef` struct literals to include `unique_constraints: vec![]`.

**B. expand.rs -- Remove OneToMany branches, update ON clause and fan trap message:**

1. In `check_path_up` (line ~1130-1186):
   - Remove the `if *card == Cardinality::OneToMany` branch (lines 1155-1166). With OneToMany gone, the only fan-out check in the forward direction is... none (ManyToOne forward is safe, OneToOne forward is safe). So the first `if let` block (forward edge, current->parent) needs no fan-out check -- it is always safe.
   - The second `if let` block (reverse edge, parent->current traversed as current->parent) keeps `if *card == Cardinality::ManyToOne` as the fan-out check. This is correct.

2. In `check_path_down` (lines ~1193-1240):
   - Same pattern: remove `if *card == Cardinality::OneToMany` (lines 1207-1218).
   - Keep `if *card == Cardinality::ManyToOne` in the reverse block.

3. Update the `FanTrap` error message (line ~147-152) to use inference language:
   ```rust
   Self::FanTrap { view_name, metric_name, metric_table, dimension_name, dimension_table, relationship_name } => {
       write!(
           f,
           "semantic view '{view_name}': fan trap detected -- metric '{metric_name}' \
            (table '{metric_table}') would be duplicated when joined to dimension \
            '{dimension_name}' (table '{dimension_table}') via relationship \
            '{relationship_name}' (many-to-one cardinality, inferred: FK is not PK/UNIQUE). \
            This would inflate aggregation results. \
            Remove the dimension, use a metric from the same table, or restructure the \
            relationship."
       )
   }
   ```

4. Update `synthesize_on_clause_scoped` (lines ~286-308) to use `join.ref_columns` instead of looking up `pk_columns` from tables:
   ```rust
   fn synthesize_on_clause_scoped(join: &Join, tables: &[TableRef], to_alias: &str) -> String {
       // Phase 33: Prefer ref_columns (resolved during inference).
       // Fall back to target PK for backward compat (legacy joins without ref_columns).
       let ref_cols: &[String] = if !join.ref_columns.is_empty() {
           &join.ref_columns
       } else {
           let to_alias_lower = join.table.to_ascii_lowercase();
           tables
               .iter()
               .find(|t| t.alias.to_ascii_lowercase() == to_alias_lower)
               .map_or(&[] as &[String], |t| &t.pk_columns)
       };

       let pairs: Vec<String> = join
           .fk_columns
           .iter()
           .zip(ref_cols.iter())
           .map(|(fk, pk)| {
               format!(
                   "{}.{} = {}.{}",
                   quote_ident(&join.from_alias),
                   quote_ident(fk),
                   quote_ident(to_alias),
                   quote_ident(pk),
               )
           })
           .collect();
       pairs.join(" AND ")
   }
   ```

5. Update `fan_trap_error_message_format` test: change assertion from `msg.contains("ONE TO MANY")` to `msg.contains("many-to-one cardinality")`.

6. Remove the `fan_trap_one_to_many_explicit` test (lines ~4253-4308) which constructs `Cardinality::OneToMany`. Replace with a test that verifies ManyToOne reverse fan trap detection (which is the actual mechanism now).

7. Update ALL `TableRef` struct literals in expand.rs tests to include `unique_constraints: vec![]` where not using `..Default::default()`. The fan trap test helpers `fan_trap_three_table_def` and `flights_airports_def` construct TableRef with explicit fields -- add `unique_constraints: vec![]` to each.

8. Update ALL `Join` struct literals in expand.rs tests to include `ref_columns` where appropriate. For joins that reference PK, set `ref_columns: vec!["id".to_string()]` (matching the target PK). For joins that use `..Default::default()`, the field will default to empty vec.

9. Update `tests/expand_proptest.rs`: If any `TableRef` or `Join` struct literals exist without `unique_constraints`/`ref_columns`, add them. Check for `Cardinality::OneToMany` references and remove.

**C. define.rs -- Add old-JSON detection:**

1. In `DefineFromJsonVTab::bind()`, after `SemanticViewDefinition::from_json(&name, &json)` (line ~112) and BEFORE `validate_graph(&def)` (line ~117), add the old-JSON check:
   ```rust
   // Phase 33: Reject old-format JSON (pre-v0.5.4) that has FK columns but no ref_columns.
   for join in &def.joins {
       if !join.fk_columns.is_empty() && join.ref_columns.is_empty() {
           return Err(Box::<dyn std::error::Error>::from(
               "This semantic view was created with an older version. \
                Please recreate it with the new DDL syntax."
           ));
       }
   }
   ```

**D. describe.rs -- Show UNIQUE constraints and inferred cardinality:**

The DESCRIBE output serializes the `joins` and `tables` sections as JSON. Since the model now includes `unique_constraints` on `TableRef` and `ref_columns`/`cardinality` on `Join`, and the DESCRIBE function uses `serde_json::to_string(&def["tables"])` etc., the new fields will automatically appear in the JSON output when present (and be omitted when empty due to `skip_serializing_if`).

No code changes needed in describe.rs itself -- the JSON content automatically reflects the model. Verify with a test in the sqllogictest (plan 02 task 2).

However, review describe.rs to confirm it serializes from the raw JSON value (it does -- lines 88-107 use `serde_json::Value` from stored JSON, so whatever is stored will be emitted). This means unique_constraints and ref_columns will show in DESCRIBE output automatically.

No code change in describe.rs. The new fields flow through automatically.
  </action>
  <verify>
    <automated>cd /Users/paul/Documents/Dev/Personal/duckdb-semantic-views && cargo test 2>&1 | tail -10</automated>
  </verify>
  <acceptance_criteria>
    - src/graph.rs contains `fn validate_fk_references(`
    - src/graph.rs does NOT contain `fn check_fk_pk_counts(`
    - src/expand.rs does NOT contain `Cardinality::OneToMany`
    - src/expand.rs `synthesize_on_clause_scoped` reads `join.ref_columns` before falling back to pk_columns
    - src/expand.rs FanTrap message contains "many-to-one cardinality, inferred"
    - src/ddl/define.rs contains `"created with an older version"` check before validate_graph
    - `cargo test` exits 0
  </acceptance_criteria>
  <done>CARD-03/09 validation enforces FK-PK/UNIQUE matching. Fan trap works with 2-variant enum. ON clause uses ref_columns. Old JSON rejected. All cargo tests pass.</done>
</task>

<task type="auto">
  <name>Task 2: Update existing sqllogictests and create phase33 end-to-end test</name>
  <files>test/sql/phase31_fan_trap.test, test/sql/phase32_role_playing.test, test/sql/phase33_cardinality_inference.test</files>
  <read_first>test/sql/phase31_fan_trap.test, test/sql/phase32_role_playing.test, test/sql/phase26_join_resolution.test</read_first>
  <action>
**A. Update `test/sql/phase31_fan_trap.test`:**

The following DDL statements use explicit cardinality keywords that no longer parse:

1. **Test 1 (line 37-55):** Remove `MANY TO ONE` from the two relationships. The DDL becomes:
   ```sql
   CREATE SEMANTIC VIEW p31_sales AS
     TABLES (
       o AS p31_orders PRIMARY KEY (id),
       li AS p31_line_items PRIMARY KEY (id),
       c AS p31_customers PRIMARY KEY (id)
     )
     RELATIONSHIPS (
       li_to_order AS li(order_id) REFERENCES o,
       order_to_customer AS o(customer_id) REFERENCES c
     )
     ...
   ```
   This will infer ManyToOne for both (FK columns are bare on from-side tables). Behavior unchanged.

2. **Test 5 (lines 93-119):** This test used `ONE TO MANY` to create a fan-out in the forward direction. Since `ONE TO MANY` is gone, this entire test concept no longer applies. **Delete Test 5 entirely** (lines 92-135, including the DROP on line 101 and the CREATE on lines 103-119, and the two query tests). The fan trap behavior is now always detected via ManyToOne reverse (tested in Test 3).

3. **Test 6 (lines 137-179):** Uses `ONE TO ONE`. Replace by declaring UNIQUE constraint on the from-side table to make the inference produce OneToOne:
   ```sql
   CREATE SEMANTIC VIEW p31_oto AS
     TABLES (
       o AS p31_orders PRIMARY KEY (id) UNIQUE (id),
       d AS p31_order_details PRIMARY KEY (id)
     )
     RELATIONSHIPS (
       order_detail AS o(id) REFERENCES d
     )
     ...
   ```
   Here `o(id) REFERENCES d` -- `id` is both PK and UNIQUE on `o`, so inference yields OneToOne. (Note: PK match alone is sufficient for OneToOne. UNIQUE is redundant here but demonstrates the syntax.)
   Actually, simpler: `o` has `PRIMARY KEY (id)`, and the FK column is `id` which matches `o`'s PK. So inference gives OneToOne without needing UNIQUE. No UNIQUE needed here.

   Result:
   ```sql
   CREATE SEMANTIC VIEW p31_oto AS
     TABLES (
       o AS p31_orders PRIMARY KEY (id),
       d AS p31_order_details PRIMARY KEY (id)
     )
     RELATIONSHIPS (
       order_detail AS o(id) REFERENCES d
     )
     ...
   ```

4. **Test 7 (lines 182-217):** Already has no cardinality keyword (`li_to_order AS li(order_id) REFERENCES o`). No change needed.

5. **Test 8 (lines 219-261):** Uses `MANY TO ONE`. Remove the keyword:
   ```sql
   li_to_order AS li(order_id) REFERENCES o
   ```

6. **Test 9 (lines 263-297):** Tests case-insensitive cardinality keywords (`many to one`). **Delete this test entirely** -- cardinality keywords are gone. Update cleanup to remove the corresponding DROP/DROP TABLE statements.

7. **Update comment headers** to reflect Phase 33 changes. Remove references to "MANY TO ONE cardinality keywords" and replace with "inferred cardinality".

8. **Renumber tests** after deleting Test 5 and Test 9 for clarity.

**B. Update `test/sql/phase32_role_playing.test`:**

This file does NOT use explicit cardinality keywords. Relationships are:
- `dep_airport AS f(departure_code) REFERENCES a` -- no keyword, fine
- `arr_airport AS f(arrival_code) REFERENCES a` -- no keyword, fine

**No changes needed** to phase32. Verify by reading the file -- all REFERENCES statements have no trailing keywords.

**C. Check `test/sql/phase26_join_resolution.test`:**

Read the file and check for cardinality keywords. If any exist, remove them. (Based on the grep earlier, this file was NOT in the results, so likely no changes needed.)

**D. Create `test/sql/phase33_cardinality_inference.test`:**

Create a comprehensive end-to-end sqllogictest covering CARD-01 through CARD-09:

```sql
# Phase 33: End-to-end test for UNIQUE constraints and cardinality inference
# Tests CARD-01 through CARD-09: UNIQUE parsing, cardinality inference,
# FK/PK/UNIQUE validation, fan trap with inferred cardinality, DESCRIBE output.

require semantic_views

# ============================================================
# Setup: physical tables
# ============================================================

statement ok
CREATE TABLE p33_customers (id INTEGER, email VARCHAR, name VARCHAR);

statement ok
INSERT INTO p33_customers VALUES (1, 'alice@test.com', 'Alice'), (2, 'bob@test.com', 'Bob');

statement ok
CREATE TABLE p33_orders (id INTEGER, customer_id INTEGER, region VARCHAR, amount DOUBLE);

statement ok
INSERT INTO p33_orders VALUES (1, 1, 'East', 100.0), (2, 2, 'West', 200.0), (3, 1, 'East', 50.0);

statement ok
CREATE TABLE p33_line_items (id INTEGER, order_id INTEGER, price DOUBLE, status VARCHAR);

statement ok
INSERT INTO p33_line_items VALUES (1, 1, 40.0, 'shipped'), (2, 1, 60.0, 'pending'), (3, 2, 200.0, 'shipped'), (4, 3, 50.0, 'shipped');

statement ok
CREATE TABLE p33_profiles (user_id INTEGER, bio VARCHAR);

statement ok
INSERT INTO p33_profiles VALUES (1, 'Engineer'), (2, 'Designer');

# ============================================================
# Test 1 (CARD-01, CARD-02): UNIQUE constraints in TABLES clause
# ============================================================

statement ok
CREATE SEMANTIC VIEW p33_basic AS
  TABLES (
    c AS p33_customers PRIMARY KEY (id) UNIQUE (email),
    o AS p33_orders PRIMARY KEY (id)
  )
  RELATIONSHIPS (
    order_to_customer AS o(customer_id) REFERENCES c
  )
  DIMENSIONS (
    c.name AS c.name
  )
  METRICS (
    o.revenue AS SUM(o.amount)
  );

query TR rowsort
SELECT * FROM semantic_view('p33_basic', dimensions := ['name'], metrics := ['revenue']);
----
Alice	150.0
Bob	200.0

statement ok
DROP SEMANTIC VIEW p33_basic;

# ============================================================
# Test 2 (CARD-02): Multiple UNIQUE constraints on one table
# ============================================================

statement ok
CREATE SEMANTIC VIEW p33_multi_unique AS
  TABLES (
    c AS p33_customers PRIMARY KEY (id) UNIQUE (email) UNIQUE (name),
    o AS p33_orders PRIMARY KEY (id)
  )
  RELATIONSHIPS (
    order_to_customer AS o(customer_id) REFERENCES c
  )
  DIMENSIONS (
    c.name AS c.name
  )
  METRICS (
    o.revenue AS SUM(o.amount)
  );

query TR rowsort
SELECT * FROM semantic_view('p33_multi_unique', dimensions := ['name'], metrics := ['revenue']);
----
Alice	150.0
Bob	200.0

statement ok
DROP SEMANTIC VIEW p33_multi_unique;

# ============================================================
# Test 3 (CARD-03): FK must reference PK or UNIQUE on target -- error
# ============================================================

statement error
CREATE SEMANTIC VIEW p33_bad_ref AS
  TABLES (
    c AS p33_customers PRIMARY KEY (id) UNIQUE (email),
    o AS p33_orders PRIMARY KEY (id)
  )
  RELATIONSHIPS (
    order_to_customer AS o(customer_id) REFERENCES c(name)
  )
  DIMENSIONS (
    c.name AS c.name
  )
  METRICS (
    o.revenue AS SUM(o.amount)
  );
----
does not match any PRIMARY KEY or UNIQUE constraint

# ============================================================
# Test 4 (CARD-04): Cardinality inferred -- ManyToOne (bare FK)
# Fan trap: metric from o, dim from li -- o->li is reverse ManyToOne = fan-out
# ============================================================

statement ok
CREATE SEMANTIC VIEW p33_infer_many AS
  TABLES (
    o AS p33_orders PRIMARY KEY (id),
    li AS p33_line_items PRIMARY KEY (id)
  )
  RELATIONSHIPS (
    li_to_order AS li(order_id) REFERENCES o
  )
  DIMENSIONS (
    o.region AS o.region,
    li.status AS li.status
  )
  METRICS (
    li.revenue AS SUM(li.price),
    o.order_count AS COUNT(*)
  );

# Safe: li->o forward ManyToOne
query TR rowsort
SELECT * FROM semantic_view('p33_infer_many', dimensions := ['region'], metrics := ['revenue']);
----
East	150.0
West	200.0

# Fan trap: o->li reverse ManyToOne = fan-out
statement error
SELECT * FROM semantic_view('p33_infer_many', dimensions := ['status'], metrics := ['order_count']);
----
fan trap detected

statement ok
DROP SEMANTIC VIEW p33_infer_many;

# ============================================================
# Test 5 (CARD-04): OneToOne inferred -- FK matches PK on from-side
# ============================================================

statement ok
CREATE SEMANTIC VIEW p33_infer_oto AS
  TABLES (
    c AS p33_customers PRIMARY KEY (id),
    p AS p33_profiles PRIMARY KEY (user_id)
  )
  RELATIONSHIPS (
    customer_profile AS c(id) REFERENCES p(user_id)
  )
  DIMENSIONS (
    c.name AS c.name,
    p.bio AS p.bio
  )
  METRICS (
    c.customer_count AS COUNT(*),
    p.profile_count AS COUNT(*)
  );

# OneToOne: both directions safe
query TI rowsort
SELECT * FROM semantic_view('p33_infer_oto', dimensions := ['bio'], metrics := ['customer_count']);
----
Designer	1
Engineer	1

query TI rowsort
SELECT * FROM semantic_view('p33_infer_oto', dimensions := ['name'], metrics := ['profile_count']);
----
Alice	1
Bob	1

statement ok
DROP SEMANTIC VIEW p33_infer_oto;

# ============================================================
# Test 6 (CARD-04): OneToOne from UNIQUE match on from-side
# ============================================================

statement ok
CREATE SEMANTIC VIEW p33_infer_oto_unique AS
  TABLES (
    c AS p33_customers PRIMARY KEY (id) UNIQUE (email),
    p AS p33_profiles PRIMARY KEY (user_id)
  )
  RELATIONSHIPS (
    customer_profile AS c(email) REFERENCES p(user_id)
  )
  DIMENSIONS (
    c.name AS c.name,
    p.bio AS p.bio
  )
  METRICS (
    c.customer_count AS COUNT(*)
  );

# email is UNIQUE on c -> OneToOne, both directions safe
query TI rowsort
SELECT * FROM semantic_view('p33_infer_oto_unique', dimensions := ['bio'], metrics := ['customer_count']);
----
Designer	1
Engineer	1

statement ok
DROP SEMANTIC VIEW p33_infer_oto_unique;

# ============================================================
# Test 7 (CARD-05): Explicit cardinality keywords rejected
# ============================================================

statement error
CREATE SEMANTIC VIEW p33_explicit_card AS
  TABLES (
    o AS p33_orders PRIMARY KEY (id),
    li AS p33_line_items PRIMARY KEY (id)
  )
  RELATIONSHIPS (
    li_to_order AS li(order_id) REFERENCES o MANY TO ONE
  )
  DIMENSIONS (
    o.region AS o.region
  )
  METRICS (
    li.revenue AS SUM(li.price)
  );
----
no longer supported

# ============================================================
# Test 8 (CARD-07): REFERENCES target (no col) -> resolves to PK
# ============================================================

statement ok
CREATE SEMANTIC VIEW p33_ref_pk AS
  TABLES (
    c AS p33_customers PRIMARY KEY (id),
    o AS p33_orders PRIMARY KEY (id)
  )
  RELATIONSHIPS (
    order_to_customer AS o(customer_id) REFERENCES c
  )
  DIMENSIONS (
    c.name AS c.name
  )
  METRICS (
    o.revenue AS SUM(o.amount)
  );

query TR rowsort
SELECT * FROM semantic_view('p33_ref_pk', dimensions := ['name'], metrics := ['revenue']);
----
Alice	150.0
Bob	200.0

statement ok
DROP SEMANTIC VIEW p33_ref_pk;

# ============================================================
# Test 9 (CARD-07): REFERENCES target(col) -> explicit column
# ============================================================

statement ok
CREATE SEMANTIC VIEW p33_ref_explicit AS
  TABLES (
    c AS p33_customers PRIMARY KEY (id) UNIQUE (email),
    o AS p33_orders PRIMARY KEY (id)
  )
  RELATIONSHIPS (
    order_to_customer AS o(customer_id) REFERENCES c(id)
  )
  DIMENSIONS (
    c.name AS c.name
  )
  METRICS (
    o.revenue AS SUM(o.amount)
  );

query TR rowsort
SELECT * FROM semantic_view('p33_ref_explicit', dimensions := ['name'], metrics := ['revenue']);
----
Alice	150.0
Bob	200.0

statement ok
DROP SEMANTIC VIEW p33_ref_explicit;

# ============================================================
# Test 10 (CARD-07): REFERENCES target with no PK on target -> error
# ============================================================

statement error
CREATE SEMANTIC VIEW p33_no_pk_target AS
  TABLES (
    f AS p33_line_items,
    o AS p33_orders
  )
  RELATIONSHIPS (
    li_to_order AS f(order_id) REFERENCES o
  )
  DIMENSIONS (
    f.status AS f.status
  )
  METRICS (
    f.revenue AS SUM(f.price)
  );
----
has no PRIMARY KEY

# ============================================================
# Test 11 (CARD-08): Fan trap with inferred cardinality -- chain
# li->o->c all ManyToOne. Metric from c, dim from li via o reverse = fan-out
# ============================================================

statement ok
CREATE SEMANTIC VIEW p33_chain AS
  TABLES (
    o AS p33_orders PRIMARY KEY (id),
    li AS p33_line_items PRIMARY KEY (id),
    c AS p33_customers PRIMARY KEY (id)
  )
  RELATIONSHIPS (
    li_to_order AS li(order_id) REFERENCES o,
    order_to_customer AS o(customer_id) REFERENCES c
  )
  DIMENSIONS (
    c.name AS c.name,
    li.status AS li.status
  )
  METRICS (
    li.revenue AS SUM(li.price),
    c.cust_count AS COUNT(*)
  );

# Safe: li->o->c is ManyToOne chain
query TR rowsort
SELECT * FROM semantic_view('p33_chain', dimensions := ['name'], metrics := ['revenue']);
----
Alice	150.0
Bob	200.0

# Fan trap: c->o->li requires reverse traversal of ManyToOne edges
statement error
SELECT * FROM semantic_view('p33_chain', dimensions := ['status'], metrics := ['cust_count']);
----
fan trap detected

statement ok
DROP SEMANTIC VIEW p33_chain;

# ============================================================
# Test 12 (CARD-09): Composite FK subset rejected
# ============================================================

statement error
CREATE SEMANTIC VIEW p33_subset AS
  TABLES (
    c AS p33_customers PRIMARY KEY (id, email),
    o AS p33_orders PRIMARY KEY (id)
  )
  RELATIONSHIPS (
    order_to_customer AS o(customer_id) REFERENCES c(id)
  )
  DIMENSIONS (
    c.name AS c.name
  )
  METRICS (
    o.revenue AS SUM(o.amount)
  );
----
does not match any PRIMARY KEY or UNIQUE constraint

# ============================================================
# Test 13: DESCRIBE shows UNIQUE and inferred cardinality
# ============================================================

statement ok
CREATE SEMANTIC VIEW p33_describe AS
  TABLES (
    c AS p33_customers PRIMARY KEY (id) UNIQUE (email),
    o AS p33_orders PRIMARY KEY (id)
  )
  RELATIONSHIPS (
    order_to_customer AS o(customer_id) REFERENCES c
  )
  DIMENSIONS (
    c.name AS c.name
  )
  METRICS (
    o.revenue AS SUM(o.amount)
  );

# DESCRIBE should include unique_constraints in tables and ref_columns in joins
query TT
SELECT name, joins FROM describe_semantic_view('p33_describe');
----
p33_describe	(results will contain ref_columns and cardinality in JSON)

statement ok
DROP SEMANTIC VIEW p33_describe;

# ============================================================
# Test 14: Fact table without PRIMARY KEY
# ============================================================

statement ok
CREATE SEMANTIC VIEW p33_fact AS
  TABLES (
    f AS p33_line_items,
    o AS p33_orders PRIMARY KEY (id)
  )
  RELATIONSHIPS (
    li_to_order AS f(order_id) REFERENCES o
  )
  DIMENSIONS (
    o.region AS o.region
  )
  METRICS (
    f.revenue AS SUM(f.price)
  );

query TR rowsort
SELECT * FROM semantic_view('p33_fact', dimensions := ['region'], metrics := ['revenue']);
----
East	150.0
West	200.0

statement ok
DROP SEMANTIC VIEW p33_fact;

# ============================================================
# Cleanup
# ============================================================

statement ok
DROP TABLE p33_profiles;

statement ok
DROP TABLE p33_line_items;

statement ok
DROP TABLE p33_orders;

statement ok
DROP TABLE p33_customers;
```

Note: Test 13 (DESCRIBE) uses a loose match -- the exact JSON format depends on serde serialization. Replace the `query TT` result with the actual output after running `just build && just test-sql` the first time. If the test needs exact output, capture it. Alternatively, convert Test 13 to `statement ok` (just verify DESCRIBE does not error) and add a more precise check like `query T` with `LIKE '%unique_constraints%'` or just verify the query runs.

**E. Update `test/sql/TEST_LIST`** to include the new test file (if the TEST_LIST is manually maintained).

**F. Add the new test file to the list**: Check if `test/sql/TEST_LIST` needs updating.
  </action>
  <verify>
    <automated>cd /Users/paul/Documents/Dev/Personal/duckdb-semantic-views && just build && just test-all 2>&1 | tail -20</automated>
  </verify>
  <acceptance_criteria>
    - test/sql/phase31_fan_trap.test does NOT contain "MANY TO ONE" or "ONE TO ONE" or "ONE TO MANY" as DDL keywords
    - test/sql/phase33_cardinality_inference.test exists with tests for CARD-01 through CARD-09
    - `just test-all` exits 0 (full quality gate)
    - test/sql/phase33_cardinality_inference.test contains "does not match any PRIMARY KEY or UNIQUE constraint"
    - test/sql/phase33_cardinality_inference.test contains "has no PRIMARY KEY"
    - test/sql/phase33_cardinality_inference.test contains "no longer supported"
    - test/sql/phase33_cardinality_inference.test contains "fan trap detected"
  </acceptance_criteria>
  <done>All sqllogictests updated to new syntax. New phase33 test covers CARD-01 through CARD-09 end-to-end. `just test-all` passes -- full quality gate met.</done>
</task>

</tasks>

<verification>
```bash
# Full quality gate
cd /Users/paul/Documents/Dev/Personal/duckdb-semantic-views && just test-all
```

This runs: Rust unit tests, property-based tests, SQL logic tests, and DuckLake CI tests.
</verification>

<success_criteria>
- graph.rs: CARD-03/CARD-09 FK reference validation replaces old FK/PK count check
- expand.rs: No OneToMany branches, ON clause uses ref_columns, fan trap error uses inference language
- define.rs: Old-format JSON rejected with clear error
- All existing tests updated to new syntax (no cardinality keywords in DDL)
- New phase33 sqllogictest covers all CARD requirements
- `just test-all` passes (full quality gate from CLAUDE.md)
</success_criteria>

<output>
After completion, create `.planning/phases/33-unique-constraints-cardinality-inference/33-02-SUMMARY.md`
</output>
