---
phase: 12-explain-typed-output
plan: 02
type: execute
wave: 2
depends_on:
  - 12-01
files_modified:
  - src/lib.rs
  - src/ddl/define.rs
  - src/expand.rs
autonomous: true
requirements:
  - EXPL-01
  - OUT-01

must_haves:
  truths:
    - "`create_semantic_view`, `create_or_replace_semantic_view`, and `create_semantic_view_if_not_exists` are registered and callable from DuckDB"
    - "`define_semantic_view` and `define_or_replace_semantic_view` registrations are removed"
    - "`create_semantic_view_if_not_exists` silently succeeds (no error) when the view already exists"
    - "DDL-time LIMIT 0 runs via persist_conn for file-backed databases and populates both column_type_names and column_types_inferred (parallel vecs) on the stored definition"
    - "In-memory databases skip DDL-time inference (column_types_inferred remains empty vec)"
    - "`explain_semantic_view` remains registered (EXPL-01 — this is the EXPLAIN surface)"
    - "`output_type` on Dimension/Metric is emitted as `CAST(expr AS <type>)` in the expanded SQL when set"
  artifacts:
    - path: "src/lib.rs"
      provides: "create_semantic_view / create_or_replace_semantic_view / create_semantic_view_if_not_exists registrations"
      contains: "create_semantic_view"
    - path: "src/ddl/define.rs"
      provides: "DefineState.if_not_exists flag + DDL-time LIMIT 0 inference + output_type CAST codegen"
      contains: "if_not_exists"
  key_links:
    - from: "src/lib.rs"
      to: "src/ddl/define.rs"
      via: "DefineState with or_replace/if_not_exists flags"
      pattern: "if_not_exists"
    - from: "src/ddl/define.rs"
      to: "src/query/table_function.rs"
      via: "column_types_inferred stored in catalog JSON; read by bind() in Plan 03"
      pattern: "column_types_inferred"
---

<objective>
Rename the three `define_semantic_view*` function registrations to `create_semantic_view*`, add the new `create_semantic_view_if_not_exists` variant, implement DDL-time LIMIT 0 type inference via `persist_conn`, and wire `output_type` CAST codegen into `expand()`. `explain_semantic_view` stays registered (it is the EXPL-01 surface).

Purpose: Breaking DDL rename makes the interface match standard SQL conventions. DDL-time inference populates `column_types_inferred` so `bind()` in Plan 03 can declare typed output columns without per-query overhead.
Output: Updated `src/lib.rs` and `src/ddl/define.rs`. The `create_semantic_view` functions are callable from DuckDB; DDL-time inference fires for file-backed databases.
</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/12-explain-typed-output/12-RESEARCH.md
@.planning/phases/12-explain-typed-output/12-01-SUMMARY.md

<interfaces>
<!-- Key types and functions the executor needs. No codebase exploration required. -->

From src/ddl/define.rs (current state before Plan 02):
```rust
pub struct DefineState {
    pub catalog: CatalogState,
    pub persist_conn: Option<ffi::duckdb_connection>,
    pub or_replace: bool,
    // Plan 02: ADD if_not_exists: bool
}

// invoke() currently:
// 1. parse_define_args → parsed.def (SemanticViewDefinition)
// 2. serde_json::to_string → json
// 3. persist_define(persist_conn, name, json) if file-backed
// 4. catalog_insert or catalog_upsert based on or_replace
// 5. out.insert(i, parsed.name)
//
// Plan 02 changes:
// After step 2, BEFORE step 3:
//   2a. run try_infer_schema(persist_conn, "{expanded_sql} LIMIT 0") → column_types
//   2b. store types as Vec<u32> in parsed.def.column_types_inferred
//   2c. re-serialize to json (now includes column_types_inferred)
//
// For if_not_exists variant:
//   - catalog_insert returns Err containing "already exists" → swallow it, return Ok
```

From src/query/table_function.rs (reusable helpers):
```rust
// try_infer_schema is currently private (fn). Plan 02 needs to call it from define.rs.
// Solution: move try_infer_schema to pub(crate) visibility.
unsafe fn try_infer_schema(conn: ffi::duckdb_connection, sql: &str)
    -> Option<(Vec<String>, Vec<ffi::duckdb_type>)>
```

From src/lib.rs (current registrations to rename):
```rust
// RENAME:
con.register_scalar_function_with_state::<DefineSemanticView>("define_semantic_view", &define_state)?;
// →
con.register_scalar_function_with_state::<DefineSemanticView>("create_semantic_view", &define_state)?;

con.register_scalar_function_with_state::<DefineSemanticView>("define_or_replace_semantic_view", &define_or_replace_state)?;
// →
con.register_scalar_function_with_state::<DefineSemanticView>("create_or_replace_semantic_view", &define_or_replace_state)?;

// ADD (if_not_exists variant — new DefineState):
let define_if_not_exists_state = DefineState {
    catalog: catalog_state.clone(),
    persist_conn,
    or_replace: false,
    if_not_exists: true,
};
con.register_scalar_function_with_state::<DefineSemanticView>("create_semantic_view_if_not_exists", &define_if_not_exists_state)?;
```

From src/expand.rs (for output_type CAST codegen):
```rust
// expand() generates SQL expressions for each dimension/metric.
// When dim.output_type is Some(t), wrap the expression: CAST({expr} AS {t}) AS {name}
// When met.output_type is Some(t), wrap the aggregate: CAST({expr} AS {t}) AS {name}
// The existing quote_ident() helper is used for the alias.
// Use format!("CAST({} AS {}) AS {}", expr_or_quoted, type_str, quote_ident(&dim.name))
```

From src/model.rs (Plan 01 additions):
```rust
// Dimension.output_type: Option<String>   — new field from Plan 01
// Metric.output_type: Option<String>       — new field from Plan 01
// SemanticViewDefinition.column_types_inferred: Vec<u32>  — new field from Plan 01
```
</interfaces>
</context>

<tasks>

<task type="auto" tdd="true">
  <name>Task 1: DDL-time LIMIT 0 inference + output_type CAST in define.rs + expand.rs</name>
  <files>src/ddl/define.rs, src/query/table_function.rs, src/expand.rs</files>
  <behavior>
    - `DefineState` gains `if_not_exists: bool` field
    - `invoke()` calls `try_infer_schema` via `persist_conn` after `expand()` generates the SQL, stores types as `Vec<u32>` in `parsed.def.column_types_inferred`, then re-serializes `parsed.def` to JSON
    - When `persist_conn` is None (in-memory DB): `column_types_inferred` stays `vec![]`
    - When `if_not_exists` is true and `catalog_insert` returns an error whose message contains "already exists": swallow the error and return Ok (not an error)
    - `try_infer_schema` in `table_function.rs` becomes `pub(crate)` so `define.rs` can call it
    - `expand.rs`: when `dim.output_type` is `Some(type_str)`, emit `CAST({expr} AS {type_str}) AS {quoted_name}` instead of bare expr for the SELECT column. Same for metrics. The `date_trunc` wrapper for time dimensions takes precedence (applied after output_type check, i.e., if dim_type is "time", use the date_trunc expression, then if output_type is also set, wrap it in CAST)
    - Unit test (in model.rs or expand.rs test module): SemanticViewDefinition with `output_type: Some("BIGINT")` on a metric produces SQL containing `CAST(sum(amount) AS BIGINT)`
  </behavior>
  <action>
    **Step 1: Make `try_infer_schema` pub(crate) in `src/query/table_function.rs`**

    Change `unsafe fn try_infer_schema(...)` to `pub(crate) unsafe fn try_infer_schema(...)`.

    **Step 2: Add `if_not_exists` to `DefineState` in `src/ddl/define.rs`**

    Add field to `DefineState`:
    ```rust
    pub if_not_exists: bool,
    ```
    Update the derive/unsafe impl blocks (already have Send/Sync). Update the `invoke()` catalog section:
    ```rust
    if state.or_replace {
        catalog_upsert(&state.catalog, &parsed.name, &json)?;
    } else if state.if_not_exists {
        match catalog_insert(&state.catalog, &parsed.name, &json) {
            Ok(()) => {}
            Err(e) if e.to_string().contains("already exists") => {
                // Silently succeed — view exists, no replacement needed.
            }
            Err(e) => return Err(e),
        }
    } else {
        catalog_insert(&state.catalog, &parsed.name, &json)?;
    }
    ```

    **Step 3: DDL-time type inference in invoke() in `src/ddl/define.rs`**

    After `parse_define_args(input, i)` and the `expand()` call (which is needed for LIMIT 0 SQL), insert inference:
    ```rust
    // DDL-time type inference (file-backed databases only).
    // Runs LIMIT 0 on the expanded SQL via the persist connection.
    // The persist connection has its own context — safe from invoke's execution lock.
    if let Some(conn) = state.persist_conn {
        let req_all = /* build a QueryRequest with all dimensions and metrics from parsed.def */;
        if let Ok(expanded_for_inference) = expand(&parsed.name, &parsed.def, &req_all) {
            let limit0_sql = format!("{expanded_for_inference} LIMIT 0");
            if let Some((names, types)) = unsafe {
                crate::query::table_function::try_infer_schema(conn, &limit0_sql)
            } {
                // Store BOTH names and types so bind() can look up by name (not by position).
                // This is essential because query time requests a subset of dims+metrics, so
                // positional indexing would not match DDL-time column ordering.
                parsed.def.column_type_names = names;
                parsed.def.column_types_inferred = types.iter().map(|t| *t as u32).collect();
            }
        }
    }
    // (In-memory: both vecs stay empty — VARCHAR fallback in bind())
    ```

    The `QueryRequest` for inference must include ALL dimensions and ALL metrics from the definition:
    ```rust
    use crate::expand::{expand, QueryRequest};
    let req_all = QueryRequest {
        dimensions: parsed.def.dimensions.iter().map(|d| d.name.clone()).collect(),
        metrics: parsed.def.metrics.iter().map(|m| m.name.clone()).collect(),
        granularity_overrides: std::collections::HashMap::new(),
    };
    ```

    After inference, re-serialize `parsed.def` to JSON (it now has `column_types_inferred` populated):
    ```rust
    let json = serde_json::to_string(&parsed.def)
        .map_err(|e| Box::<dyn std::error::Error>::from(e.to_string()))?;
    ```
    (Move the `serde_json::to_string` call to AFTER the inference step.)

    **Step 4: output_type CAST in `src/expand.rs`**

    In the SELECT clause generation for dimensions, modify the expression builder:
    - For each dimension in the SELECT, if `dim.output_type` is Some, wrap the final expression: `CAST({final_expr} AS {type_str}) AS {quoted_name}`. The `final_expr` is already the time-truncated expression if applicable.
    - For each metric in the SELECT, if `met.output_type` is Some, wrap: `CAST({agg_expr} AS {type_str}) AS {quoted_name}`.
    - When `output_type` is None: no CAST wrapper (existing behavior).

    Run `cargo test` to verify all existing tests still pass. The new CAST emission is tested by the unit test in the behavior section.
  </action>
  <verify>
    <automated>cd /Users/paul/Documents/Dev/Personal/duckdb-semantic-views && cargo test 2>&1 | tail -20</automated>
  </verify>
  <done>
    `cargo test` passes. `DefineState` has `if_not_exists: bool`. `try_infer_schema` is `pub(crate)`. `invoke()` runs DDL-time LIMIT 0 via `persist_conn` and stores types in `parsed.def.column_types_inferred` before serialization. `expand.rs` emits `CAST(...)` for `output_type`-declared columns.
  </done>
</task>

<task type="auto">
  <name>Task 2: Rename DDL registrations in lib.rs + add create_semantic_view_if_not_exists</name>
  <files>src/lib.rs</files>
  <action>
    In `src/lib.rs`, inside `init_extension()`:

    **Rename two existing registrations:**
    - `"define_semantic_view"` → `"create_semantic_view"`
    - `"define_or_replace_semantic_view"` → `"create_or_replace_semantic_view"`

    **Add new `create_semantic_view_if_not_exists` registration:**
    After the `create_or_replace_semantic_view` registration block, add:
    ```rust
    // Register create_semantic_view_if_not_exists(name, tables, relationships, dimensions, time_dimensions, metrics).
    // Succeeds silently (no-op) if the view already exists; errors only on other failures.
    let define_if_not_exists_state = DefineState {
        catalog: catalog_state.clone(),
        persist_conn,
        or_replace: false,
        if_not_exists: true,
    };
    con.register_scalar_function_with_state::<DefineSemanticView>(
        "create_semantic_view_if_not_exists",
        &define_if_not_exists_state,
    )?;
    ```

    Update the comment above the old `define_semantic_view` registration to reflect the new name.

    **Keep `explain_semantic_view` registration unchanged** — it is the EXPL-01 surface. Do not rename or remove it.

    **Tip comment about EXPLAIN:** Add a code comment above the `explain_semantic_view` registration explaining how users can get DuckDB's physical plan:
    ```rust
    // Note: EXPLAIN SELECT * FROM semantic_view('name', ...) shows DuckDB's physical
    // plan (TABLE_FUNCTION node). Use explain_semantic_view('name', ...) to see the
    // expanded SQL that runs inside the table function.
    ```

    Run `cargo test` to confirm no compilation errors.
  </action>
  <verify>
    <automated>cd /Users/paul/Documents/Dev/Personal/duckdb-semantic-views && cargo test 2>&1 | tail -20</automated>
  </verify>
  <done>
    `cargo test` passes. `src/lib.rs` registers `create_semantic_view`, `create_or_replace_semantic_view`, `create_semantic_view_if_not_exists` (three DDL functions). `define_semantic_view` and `define_or_replace_semantic_view` are no longer registered. `explain_semantic_view` remains registered.
  </done>
</task>

</tasks>

<verification>
`cargo test` passes. Grep checks:
- `grep "create_semantic_view" src/lib.rs` — three hits (define, or_replace, if_not_exists)
- `grep "define_semantic_view" src/lib.rs` — zero hits (fully renamed)
- `grep "if_not_exists" src/ddl/define.rs` — at least two hits (field + condition)
- `grep "column_types_inferred" src/ddl/define.rs` — at least one hit
- `grep "pub(crate)" src/query/table_function.rs` — hits on `try_infer_schema`
</verification>

<success_criteria>
`cargo test` passes. `create_semantic_view*` functions are registered. `define_semantic_view*` functions are not. DDL-time LIMIT 0 inference populates `column_types_inferred` for file-backed databases. `create_semantic_view_if_not_exists` swallows duplicate-view errors. `output_type` generates CAST in expanded SQL.
</success_criteria>

<output>
After completion, create `.planning/phases/12-explain-typed-output/12-02-SUMMARY.md`
</output>
