---
phase: 11-create-semantic-view-parser-hook
plan: 04
type: execute
wave: 3
depends_on: [11-02, 11-03]
files_modified:
  - test/sql/phase2_ddl.test
  - test/sql/phase11_ddl.test
autonomous: true
requirements: [DDL-01, DDL-02, DDL-03, DDL-04, DDL-05, DDL-06]

must_haves:
  truths:
    - "CREATE SEMANTIC VIEW with TABLES + DIMENSIONS + METRICS succeeds and the view is queryable via semantic_query"
    - "DROP SEMANTIC VIEW removes a view; subsequent semantic_query returns an error"
    - "CREATE OR REPLACE SEMANTIC VIEW overwrites an existing definition without error"
    - "DROP SEMANTIC VIEW IF EXISTS succeeds silently when view does not exist"
    - "Native DDL with RELATIONSHIPS + FACTS clauses succeeds and definitions round-trip correctly"
    - "SELECT 1 and CREATE TABLE and other non-semantic SQL works identically before and after LOAD (DDL-06)"
    - "define_semantic_view() and drop_semantic_view() scalar functions are absent — calling them errors"
    - "make test_debug passes (all .test files pass)"
  artifacts:
    - path: "test/sql/phase2_ddl.test"
      provides: "Rewritten DDL test using native CREATE/DROP SEMANTIC VIEW syntax; covers DDL-01, DDL-02, DDL-03"
    - path: "test/sql/phase11_ddl.test"
      provides: "New DDL test covering RELATIONSHIPS, FACTS, IF NOT EXISTS, OR REPLACE, pass-through SQL, and absence of scalar DDL functions"
  key_links:
    - from: "test/sql/phase11_ddl.test"
      to: "src/shim/shim.cpp SemanticViewsParseFunction"
      via: "LOAD 'semantic_views' then CREATE SEMANTIC VIEW DDL statement"
      pattern: "CREATE.*SEMANTIC.*VIEW"
    - from: "test/sql/phase11_ddl.test SELECT 1 test"
      to: "src/shim/shim.cpp SemanticViewsParseFunction DISPLAY_ORIGINAL_ERROR path"
      via: "Extension loaded but SELECT passes through parser hook without error"
      pattern: "SELECT 1"
---

<objective>
Rewrite the DDL integration tests to use native CREATE/DROP SEMANTIC VIEW syntax and add comprehensive coverage for all Phase 11 DDL requirements.

Purpose: Validates that the full parser hook path works end-to-end through the SQLLogicTest runner (LOAD mechanism), covering DDL-01–06 and the removal of the scalar functions (DDL-05).

Output: Updated phase2_ddl.test (native DDL syntax); new phase11_ddl.test (comprehensive Phase 11 coverage).
</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/phases/11-create-semantic-view-parser-hook/11-CONTEXT.md
@.planning/phases/11-create-semantic-view-parser-hook/11-02-SUMMARY.md
@.planning/phases/11-create-semantic-view-parser-hook/11-03-SUMMARY.md

<interfaces>
<!-- Native DDL syntax the tests must exercise -->

CREATE SEMANTIC VIEW grammar:
```sql
CREATE [OR REPLACE] SEMANTIC VIEW [IF NOT EXISTS] <name>
  TABLES (
    <alias> AS <physical_table> [PRIMARY KEY (<col> [, ...])]
    [, ...]
  )
  [RELATIONSHIPS (
    <from_alias>(<fk_col> [, ...]) REFERENCES <ref_alias>
    [, ...]
  )]
  [FACTS (
    <alias>.<name> AS <sql_expr>
    [, ...]
  )]
  [DIMENSIONS (
    <alias>.<name> AS <sql_expr>
    [, ...]
  )]
  [METRICS (
    <alias>.<name> AS <sql_expr>
    [, ...]
  )]
```

DROP SEMANTIC VIEW grammar:
```sql
DROP SEMANTIC VIEW [IF EXISTS] <name>
```

Helper functions still available:
- list_semantic_views() — table function (not removed)
- describe_semantic_view('name') — table function (not removed)
- semantic_query('name', dimensions := [...], metrics := [...]) — table function

SQLLogicTest format patterns (from existing test files):
```
require semantic_views

statement ok
CREATE SEMANTIC VIEW ...

statement error
CREATE SEMANTIC VIEW already_exists ...
----
already exists

query TT rowsort
SELECT name, base_table FROM list_semantic_views();
----
my_view	my_table
```
</interfaces>
</context>

<tasks>

<task type="auto">
  <name>Task 1: Rewrite phase2_ddl.test with native DDL syntax</name>
  <files>test/sql/phase2_ddl.test</files>
  <action>
  Rewrite `test/sql/phase2_ddl.test` entirely. The old test used `define_semantic_view()` / `drop_semantic_view()` scalar functions. Replace all DDL calls with native CREATE/DROP SEMANTIC VIEW syntax.

  Test scenarios to cover (matching old test structure so regression coverage is preserved):

  1. **Create a semantic view** (DDL-01): `CREATE SEMANTIC VIEW orders TABLES (o AS orders) DIMENSIONS (o.region AS region) METRICS (o.revenue AS sum(amount))`
  2. **Create a second view** (DDL-01): customers view
  3. **list_semantic_views returns both** (DDL-03-helper: list function still works)
  4. **describe_semantic_view returns fields** (DDL-04-helper: describe function still works)
  5. **Duplicate CREATE is an error** (DDL-01): `CREATE SEMANTIC VIEW orders ...` again → error containing "already exists"
  6. **CREATE OR REPLACE overwrites** (DDL-03): `CREATE OR REPLACE SEMANTIC VIEW orders ...` → ok
  7. **DROP removes a view** (DDL-02): `DROP SEMANTIC VIEW orders` → ok; list no longer has orders
  8. **DROP on nonexistent is error** (DDL-02): `DROP SEMANTIC VIEW orders` (already dropped) → error containing "does not exist"
  9. **DROP IF EXISTS is silent** (DDL-02): `DROP SEMANTIC VIEW IF EXISTS nonexistent_view` → ok (statement ok)
  10. **Scalar functions are gone** (DDL-05): `SELECT define_semantic_view('x', '{}')` → error (function not found)
  11. **describe on dropped view errors**: `describe_semantic_view('orders')` → error "does not exist"
  12. **Cleanup**: DROP remaining views; `count(*) FROM list_semantic_views()` = 0

  Update the file header comment to reflect Phase 11 requirements. The test file header should reference DDL-01, DDL-02, DDL-03, DDL-05 requirements.

  Note on DDL syntax for basic views without joins: a single-table view needs only the TABLES clause + optional DIMENSIONS/METRICS. No RELATIONSHIPS or FACTS required. Example:
  ```sql
  CREATE SEMANTIC VIEW orders
    TABLES (o AS orders)
    DIMENSIONS (o.region AS region)
    METRICS (o.revenue AS sum(amount))
  ```

  The `describe_semantic_view` output format will have changed — FACTS is now a new column. Update expected query output to match whatever the describe function now returns. If describe_semantic_view does not yet show facts, omit facts from the query.
  </action>
  <verify>
    <automated>cargo build --features extension 2>&1 | grep "^error" | wc -l</automated>
  </verify>
  <done>phase2_ddl.test rewritten with native DDL syntax covering DDL-01, DDL-02, DDL-03, DDL-05; no references to define_semantic_view() or drop_semantic_view() scalar functions</done>
</task>

<task type="auto">
  <name>Task 2: Write phase11_ddl.test — comprehensive DDL coverage + pass-through test</name>
  <files>test/sql/phase11_ddl.test</files>
  <action>
  Create `test/sql/phase11_ddl.test` covering the Phase 11 specific requirements not covered in phase2_ddl.test:

  ```
  # Phase 11 DDL integration test.
  # Exercises CREATE SEMANTIC VIEW native DDL with full grammar:
  # TABLES, RELATIONSHIPS, FACTS, DIMENSIONS, METRICS, IF NOT EXISTS, OR REPLACE.
  # Also verifies DDL-06: non-semantic SQL is unaffected by parser hook.
  #
  # Requirements covered:
  #   DDL-01: CREATE SEMANTIC VIEW succeeds
  #   DDL-02: DROP SEMANTIC VIEW succeeds; IF EXISTS variant
  #   DDL-03: CREATE OR REPLACE SEMANTIC VIEW overwrites
  #   DDL-04: All capabilities (RELATIONSHIPS, FACTS, DIMENSIONS, METRICS)
  #   DDL-06: Non-semantic SQL is unaffected

  require semantic_views
  ```

  Test cases:

  **Section 1: Pass-through SQL (DDL-06)** — MUST be first to prove the hook doesn't break basic SQL:
  ```
  statement ok
  SELECT 1

  query I
  SELECT 42
  ----
  42

  statement ok
  CREATE TABLE test_orders (id INTEGER, region VARCHAR, amount DECIMAL(10,2), customer_id INTEGER)

  statement ok
  INSERT INTO test_orders VALUES (1, 'West', 100.0, 10), (2, 'East', 200.0, 20), (3, 'West', 150.0, 10)

  statement ok
  CREATE TABLE test_customers (id INTEGER, country VARCHAR)

  statement ok
  INSERT INTO test_customers VALUES (10, 'US'), (20, 'UK')
  ```

  **Section 2: Basic CREATE SEMANTIC VIEW (DDL-01)**:
  ```sql
  CREATE SEMANTIC VIEW order_summary
    TABLES (o AS test_orders)
    DIMENSIONS (o.region AS region)
    METRICS (o.revenue AS sum(amount))
  ```
  Verify with list_semantic_views().

  **Section 3: semantic_query works after native DDL (DDL-01 end-to-end)**:
  ```sql
  SELECT * FROM semantic_query('order_summary', dimensions := ['region'], metrics := ['revenue'])
  ORDER BY region
  ```
  Expected: East 200.0, West 250.0 (as VARCHAR since output is VARCHAR).

  **Section 4: CREATE OR REPLACE (DDL-03)**:
  ```sql
  CREATE OR REPLACE SEMANTIC VIEW order_summary
    TABLES (o AS test_orders)
    DIMENSIONS (o.region AS region)
    METRICS (o.revenue AS sum(amount), o.order_count AS count(*))
  ```
  Verify: `SELECT count(*) FROM list_semantic_views()` = 1 (not 2). Verify updated view has 2 metrics.

  **Section 5: IF NOT EXISTS (DDL-01)**:
  ```sql
  CREATE SEMANTIC VIEW IF NOT EXISTS order_summary
    TABLES (o AS test_orders)
    DIMENSIONS (o.region AS region)
    METRICS (o.revenue AS sum(amount))
  ```
  Should be `statement ok` (IF NOT EXISTS: no error, silently does nothing when exists).

  **Section 6: RELATIONSHIPS clause (DDL-04)**:
  ```sql
  CREATE SEMANTIC VIEW order_customer_view
    TABLES (
      o AS test_orders,
      c AS test_customers
    )
    RELATIONSHIPS (
      o(customer_id) REFERENCES c
    )
    DIMENSIONS (o.region AS region, c.country AS country)
    METRICS (o.revenue AS sum(o.amount))
  ```
  Verify with describe_semantic_view: confirm base_table is determined correctly.

  **Section 7: FACTS clause (DDL-04)**:
  ```sql
  CREATE SEMANTIC VIEW order_with_facts
    TABLES (o AS test_orders)
    FACTS (o.unit_price AS amount / 1.0)
    DIMENSIONS (o.region AS region)
    METRICS (o.revenue AS sum(amount))
  ```
  Verify view is created successfully (list_semantic_views returns it).

  **Section 8: DROP SEMANTIC VIEW (DDL-02)**:
  ```sql
  DROP SEMANTIC VIEW order_summary
  ```
  Verify removed from list.

  **Section 9: DROP IF EXISTS (DDL-02)**:
  ```sql
  DROP SEMANTIC VIEW IF EXISTS order_summary
  ```
  Should be `statement ok` (already dropped, IF EXISTS = silent).
  ```sql
  DROP SEMANTIC VIEW IF EXISTS completely_nonexistent_view
  ```
  Should be `statement ok`.

  **Section 10: DROP without IF EXISTS on missing view is error (DDL-02)**:
  ```sql
  DROP SEMANTIC VIEW nonexistent_view
  ```
  Should be `statement error` containing "does not exist".

  **Section 11: Cleanup**:
  Drop remaining views; verify list is empty.

  Add `statement ok` / `query` / `statement error` markers and expected outputs for all cases. For query results involving aggregation of the test data, compute the expected values from the INSERT statements in Section 1.
  </action>
  <verify>
    <automated>make test_debug 2>&1 | tail -30</automated>
  </verify>
  <done>make test_debug passes with all .test files green; phase11_ddl.test covers DDL-01, DDL-02, DDL-03, DDL-04, DDL-06 scenarios</done>
</task>

</tasks>

<verification>
Full SQL logic test suite:
```bash
make test_debug 2>&1 | grep -E "FAILED|passed|error" | tail -20
```
Expected: all test files pass, zero failures.

DDL-06 pass-through check (can run after make test_debug builds the extension):
```bash
grep -c "SELECT 1" test/sql/phase11_ddl.test
```
Expected: at least 1 (pass-through test exists).

DDL-05 check — no scalar function calls in test files:
```bash
grep -rn "define_semantic_view\b\|drop_semantic_view\b" test/sql/ 2>&1
```
Expected: zero matches.
</verification>

<success_criteria>
- `make test_debug` passes all .test files with zero failures
- `test/sql/phase2_ddl.test` uses native CREATE/DROP SEMANTIC VIEW syntax exclusively
- `test/sql/phase11_ddl.test` exists and covers DDL-01 through DDL-06
- No references to `define_semantic_view()` or `drop_semantic_view()` in any .test file
- `SELECT 1` pass-through test is present in phase11_ddl.test (DDL-06)
- CREATE OR REPLACE, DROP IF EXISTS, RELATIONSHIPS, and FACTS are each tested
</success_criteria>

<output>
After completion, create `.planning/phases/11-create-semantic-view-parser-hook/11-04-SUMMARY.md`
</output>
