---
phase: 12-explain-typed-output
plan: 04
type: execute
wave: 3
depends_on:
  - 12-02
  - 12-03
files_modified:
  - test/sql/phase2_ddl.test
  - test/sql/phase4_query.test
  - test/sql/semantic_views.test
  - .planning/REQUIREMENTS.md
autonomous: true
requirements:
  - EXPL-01
  - OUT-01

must_haves:
  truths:
    - "`make test_debug` passes with zero failures after all test file renames and additions"
    - "All `define_semantic_view` / `define_or_replace_semantic_view` calls in test files are replaced with `create_semantic_view` / `create_or_replace_semantic_view`"
    - "`create_semantic_view_if_not_exists` is tested: duplicate call succeeds silently"
    - "`explain_semantic_view` test returns rows containing the expanded SQL (EXPL-01)"
    - "A BIGINT metric test uses `query I` not `query T`, confirming typed output (OUT-01)"
    - "A DATE time dimension test uses `query D` or asserts DATE value format, confirming typed output (OUT-01)"
  artifacts:
    - path: "test/sql/phase2_ddl.test"
      provides: "DDL rename tests + create_semantic_view_if_not_exists test"
      contains: "create_semantic_view"
    - path: "test/sql/phase4_query.test"
      provides: "Typed output column tests + explain_semantic_view test"
      contains: "explain_semantic_view"
  key_links:
    - from: "test/sql/phase4_query.test"
      to: "src/query/explain.rs"
      via: "FROM explain_semantic_view(...) returns expanded SQL rows"
      pattern: "explain_semantic_view"
    - from: "test/sql/phase4_query.test"
      to: "src/query/table_function.rs"
      via: "query I asserts BIGINT column type from semantic_view"
      pattern: "query I"
---

<objective>
Update all SQLLogicTest files to reflect the DDL rename (`define_semantic_view` → `create_semantic_view`), add tests for `create_semantic_view_if_not_exists`, verify `explain_semantic_view` returns expanded SQL (EXPL-01), and add typed output column assertions using SQLLogicTest type specifiers (EXPL-01, OUT-01).

Purpose: Integration tests are the final gate. Without updated tests, the implementation changes have no automated verification.
Output: All three `.test` files updated. `make test_debug` passes with zero failures.
</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-02-SUMMARY.md
@.planning/phases/12-explain-typed-output/12-03-SUMMARY.md

<interfaces>
<!-- SQLLogicTest format patterns used in this project -->

SQLLogicTest type specifiers:
- `query T` → VARCHAR/text column
- `query I` → INTEGER column (also matches BIGINT)
- `query R` → REAL/DOUBLE column
- `query D` → DATE column (returns YYYY-MM-DD format strings in assertions)
- `query TT` → two text columns
- `query TI` → text + integer columns

Pattern for `create_semantic_view` (6-arg):
```sql
SELECT create_semantic_view(
    'view_name',
    [{'alias': 'o', 'table': 'table_name'}],  -- tables
    [],                                          -- relationships
    [{'name': 'dim', 'expr': 'col', 'source_table': 'o'}],  -- dimensions
    [],                                          -- time_dimensions
    [{'name': 'metric', 'expr': 'sum(col)', 'source_table': 'o'}]  -- metrics
);
```

Pattern for `explain_semantic_view`:
```sql
-- Returns multiple rows of VARCHAR, each row is one line of EXPLAIN output
-- The expanded SQL appears in the output (look for SELECT or WITH keyword)
FROM explain_semantic_view('view_name', dimensions := ['dim'], metrics := ['metric'])
```

Pattern for typed output test (BIGINT metric):
```sql
-- Use query I when the metric is expected to be BIGINT
query I
SELECT revenue FROM semantic_view('view_name', metrics := ['revenue'])
----
<integer_value>
```

Pattern for DUCKDB typed output DATE dimension:
```sql
query D
SELECT order_date FROM semantic_view('view_name', dimensions := ['order_date'])
----
2024-01-01
```

The `require semantic_views` directive at the top of each test file loads the extension.

Tip comment format (add to phase4_query.test header or a dedicated comment block):
```
# TIP: EXPLAIN SELECT * FROM semantic_view('view', ...) shows DuckDB's physical
# plan (TABLE_FUNCTION node). Use explain_semantic_view('view', ...) to see
# the expanded SQL that semantic_view executes internally.
```
</interfaces>
</context>

<tasks>

<task type="auto">
  <name>Task 1: Rename define_semantic_view → create_semantic_view in all test files</name>
  <files>test/sql/phase2_ddl.test, test/sql/phase4_query.test, test/sql/semantic_views.test</files>
  <action>
    Perform a mechanical rename across all three test files:

    **In `test/sql/phase2_ddl.test`:**
    - Replace ALL occurrences of `define_semantic_view(` with `create_semantic_view(`
    - Replace ALL occurrences of `define_or_replace_semantic_view(` with `create_or_replace_semantic_view(`
    - Update the comment header to say "create_semantic_view" instead of "define_semantic_view"
    - Update the Requirements covered section: DDL-01 → "create_semantic_view() registers a view", DDL-03 → "create_or_replace_semantic_view() overwrites"
    - After the existing DDL-03 (or_replace) test section, add a new test section for `create_semantic_view_if_not_exists`:
      ```
      # ============================================================
      # N. create_semantic_view_if_not_exists — silent no-op on duplicate
      # ============================================================

      # First call creates the view (normal behavior)
      statement ok
      SELECT create_semantic_view_if_not_exists(
          'idempotent_test',
          [{'alias': 't', 'table': 'orders'}],
          [],
          [{'name': 'region', 'expr': 'region', 'source_table': 't'}],
          [],
          []
      );

      # Second call on same view name must NOT error (if_not_exists behavior)
      statement ok
      SELECT create_semantic_view_if_not_exists(
          'idempotent_test',
          [{'alias': 't', 'table': 'orders'}],
          [],
          [{'name': 'region', 'expr': 'region', 'source_table': 't'}],
          [],
          []
      );

      # Clean up
      statement ok
      SELECT drop_semantic_view('idempotent_test');
      ```

    **In `test/sql/phase4_query.test`:**
    - Replace ALL occurrences of `define_semantic_view(` with `create_semantic_view(`
    - Replace ALL occurrences of `define_or_replace_semantic_view(` with `create_or_replace_semantic_view(`
    - Update comment header (line 9 note about VARCHAR) — change to note that typed output is now supported; update "all columns as VARCHAR" note to "typed columns (BIGINT, DATE, DOUBLE, etc.)"
    - Update Requirements covered comment to include OUT-01 and EXPL-01
    - Update all `query T` assertions for metric columns that are now typed:
      - `count(*)` → returns BIGINT (INTEGER in sqllogictest) → change `query T` to `query I` for count metric results
      - IMPORTANT: Check each `query T` block and determine if the result column is now typed. If a metric uses `sum()` or `count()` on integer data, it will return BIGINT or INTEGER. Use `query I` for those. VARCHAR dimensions stay as `query T`. Mixed: use `query TI` or `query IT` as appropriate.
      - Do NOT blindly change all `query T` — only change type specifiers where the column is now typed (integer/float/date).

    **In `test/sql/semantic_views.test`:**
    - If this file contains any `define_semantic_view` calls, replace them.
    - This file is the LOAD smoke test — likely just does `LOAD` and basic sanity; update any DDL calls found.

    After all renames, run `make test_debug` to see which tests pass and which fail (expected some failures on typed columns before fixing the type specifiers).

    NOTE: At this stage, do NOT yet add the explain_semantic_view or new typed output tests — those are in Task 2. Task 1 is just the rename.
  </action>
  <verify>
    <automated>cd /Users/paul/Documents/Dev/Personal/duckdb-semantic-views && grep -c "define_semantic_view" test/sql/phase2_ddl.test test/sql/phase4_query.test test/sql/semantic_views.test 2>&1</automated>
  </verify>
  <done>
    `grep -c "define_semantic_view"` returns 0 for all test files. `create_semantic_view_if_not_exists` test section added to phase2_ddl.test.
  </done>
</task>

<task type="auto" tdd="true">
  <name>Task 2: Add explain_semantic_view test (EXPL-01) + typed output tests (OUT-01) + run full suite</name>
  <files>test/sql/phase4_query.test</files>
  <behavior>
    - EXPL-01 test: `FROM explain_semantic_view('simple_orders', dimensions := ['region'], metrics := ['revenue'])` returns at least one row containing "SELECT" or "WITH" (expanded SQL is present in output)
    - OUT-01 test (BIGINT): a `count(*)` metric over test_orders returns an integer column — `query I` asserts the count value as an integer
    - OUT-01 test (DATE): a time dimension (declare a new semantic view with a DATE column) returns a DATE column — `query D` or check the YYYY-MM-DD format
    - `make test_debug` succeeds (zero failures, zero errors)
  </behavior>
  <action>
    Add a new section at the end of `test/sql/phase4_query.test`:

    ```
    # ============================================================
    # N. explain_semantic_view — EXPL-01
    # TIP: 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 semantic_view executes internally.
    # ============================================================

    # explain_semantic_view returns the expanded SQL in its output
    # The output contains multiple rows; check that at least one row
    # contains SQL keywords from the expanded query
    query T
    SELECT count(*) > 0
    FROM explain_semantic_view('simple_orders', dimensions := ['region'], metrics := ['revenue'])
    WHERE explain_output ILIKE '%SELECT%' OR explain_output ILIKE '%WITH%'
    ----
    true
    ```

    Add a typed output section (OUT-01). First, set up a test view with a `count(*)` metric on test_orders (already created in setup):

    ```
    # ============================================================
    # N. Typed output columns — OUT-01
    # ============================================================

    # Define a view with a count metric (will be typed as INTEGER/BIGINT, not VARCHAR)
    statement ok
    SELECT create_semantic_view(
        'typed_output_test',
        [{'alias': 'o', 'table': 'test_orders'}],
        [],
        [{'name': 'region', 'expr': 'region', 'source_table': 'o'}],
        [],
        [{'name': 'order_count', 'expr': 'count(*)', 'source_table': 'o'}]
    );

    # query I confirms the metric column is typed as INTEGER (not VARCHAR)
    # count(*) returns 5 total rows, but queried by region: EMEA=3, APAC=2
    query TI rowsort
    FROM semantic_view('typed_output_test', dimensions := ['region'], metrics := ['order_count'])
    ----
    APAC	2
    EMEA	3

    statement ok
    SELECT drop_semantic_view('typed_output_test');
    ```

    For the DATE typed column test, add a test table with a DATE column and define a view:
    ```
    # DATE typed column test (OUT-01)
    statement ok
    CREATE TABLE test_events (
        event_date DATE,
        event_count INTEGER
    );

    statement ok
    INSERT INTO test_events VALUES
        ('2024-01-15', 5),
        ('2024-02-20', 3);

    statement ok
    SELECT create_semantic_view(
        'typed_date_test',
        [{'alias': 'e', 'table': 'test_events'}],
        [],
        [],
        [{'name': 'event_date', 'expr': 'event_date', 'granularity': 'month'}],
        [{'name': 'total_count', 'expr': 'sum(event_count)', 'source_table': 'e'}]
    );

    # The time dimension should return DATE values (not VARCHAR strings)
    # date_trunc('month', event_date) returns the first day of the month as DATE
    query DI rowsort
    FROM semantic_view('typed_date_test', dimensions := ['event_date'], metrics := ['total_count'])
    ----
    2024-01-01	5
    2024-02-01	3

    statement ok
    SELECT drop_semantic_view('typed_date_test');
    ```

    After adding both sections, run the full test suite:
    ```
    make test_debug
    ```

    If tests fail due to type specifier mismatches (e.g., the result is BIGINT but test expects I which is for INTEGER), adjust the type specifiers. The SQLLogicTest runner maps:
    - `I` → matches BIGINT, INTEGER, SMALLINT, TINYINT
    - `R` → matches DOUBLE, FLOAT, DECIMAL
    - `D` → matches DATE

    If the DATE test cannot use `query D` due to SQLLogicTest limitations (date values printed differently), fall back to `query T` with the string representation `2024-01-01`.

    Fix any remaining type specifier mismatches from Task 1 renames until `make test_debug` passes with zero failures.
  </action>
  <verify>
    <automated>cd /Users/paul/Documents/Dev/Personal/duckdb-semantic-views && make test_debug 2>&1 | tail -30</automated>
  </verify>
  <done>
    `make test_debug` passes with zero failures. `phase4_query.test` contains an `explain_semantic_view` test asserting it returns rows with SQL content. `phase4_query.test` contains a `query TI` test for a count metric confirming BIGINT/INTEGER typed output. All three test files use `create_semantic_view` (no `define_semantic_view` references).
  </done>
</task>

<task type="auto">
  <name>Task 3: Update REQUIREMENTS.md — mark EXPL-01 and OUT-01 complete, update DDL names</name>
  <files>.planning/REQUIREMENTS.md</files>
  <action>
    In `.planning/REQUIREMENTS.md`:
    - Find EXPL-01 and mark it complete: change `[ ]` to `[x]`
    - Find OUT-01 and mark it complete: change `[ ]` to `[x]`
    - Find all references to `define_semantic_view()` in DDL-04 and DDL-05 and update to `create_semantic_view()`
    - Find all references to `define_or_replace_semantic_view()` and update to `create_or_replace_semantic_view()`
    No other changes — do not rewrite or restructure the file.
  </action>
  <verify>
    <automated>cd /Users/paul/Documents/Dev/Personal/duckdb-semantic-views && grep -E "EXPL-01|OUT-01" .planning/REQUIREMENTS.md | head -10</automated>
  </verify>
  <done>
    EXPL-01 and OUT-01 show `[x]` in REQUIREMENTS.md. No `define_semantic_view` references remain in DDL-04/DDL-05.
  </done>
</task>

</tasks>

<verification>
Final checks:
1. `make test_debug` passes with zero failures
2. `grep -r "define_semantic_view" test/sql/` returns zero matches
3. `grep "create_semantic_view_if_not_exists" test/sql/phase2_ddl.test` returns at least two matches (the two `statement ok` calls)
4. `grep "explain_semantic_view" test/sql/phase4_query.test` returns at least one match
5. `grep "query TI\|query I\|query DI\|query D" test/sql/phase4_query.test` returns at least one match for typed output assertions
6. EXPL-01 and OUT-01 are marked `[x]` in `.planning/REQUIREMENTS.md`
</verification>

<success_criteria>
`make test_debug` passes with zero failures. All `define_semantic_view` references replaced with `create_semantic_view` in all test files. `create_semantic_view_if_not_exists` tested. `explain_semantic_view` test present and passing (EXPL-01). At least one typed output assertion uses `query I` or `query TI` for a numeric metric (OUT-01).
</success_criteria>

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