---
phase: 28-integration-testing-documentation
plan: 03
type: execute
wave: 2
depends_on: ["28-01"]
files_modified:
  - test/sql/phase28_e2e.test
  - test/sql/TEST_LIST
  - README.md
autonomous: true
requirements: ["DOC-01"]

must_haves:
  truths:
    - "A 3-table PK/FK semantic view can be created, queried, and produces correct results with known data"
    - "explain_semantic_view output contains expected FROM/JOIN/GROUP BY clauses"
    - "README shows only AS-body PK/FK syntax with e-commerce domain examples"
    - "README contains How it works, Quick start, Multi-table, DDL reference, and Building sections"
  artifacts:
    - path: "test/sql/phase28_e2e.test"
      provides: "3-table E2E integration test with exact result verification"
      contains: "p28_analytics"
    - path: "README.md"
      provides: "Clean-slate documentation with AS-body PK/FK syntax"
      contains: "CREATE SEMANTIC VIEW"
  key_links:
    - from: "test/sql/phase28_e2e.test"
      to: "extension pipeline"
      via: "CREATE SEMANTIC VIEW -> semantic_view() -> exact result rows"
      pattern: "semantic_view.*p28_analytics"
    - from: "test/sql/phase28_e2e.test"
      to: "explain function"
      via: "explain_semantic_view() output verification"
      pattern: "explain_semantic_view.*p28_analytics"
---

<objective>
Create a comprehensive end-to-end integration test with a 3-table PK/FK scenario and rewrite README.md to document only the new SQL DDL syntax.

Purpose: DOC-01 requires README updated with new SQL DDL syntax reference, PK/FK relationship examples, and qualified column usage. The E2E test validates the complete DDL-to-query pipeline with exact result verification.

Output: `phase28_e2e.test` proving the full pipeline, README.md with clean-slate documentation.
</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/phases/28-integration-testing-documentation/28-CONTEXT.md
@.planning/phases/28-integration-testing-documentation/28-RESEARCH.md
@.planning/phases/28-integration-testing-documentation/28-01-SUMMARY.md

<interfaces>
<!-- Query and explain function signatures (unchanged): -->
```sql
-- Query function (table function):
SELECT * FROM semantic_view('view_name',
    dimensions := ['dim1', 'dim2'],
    metrics := ['metric1', 'metric2']
);

-- Explain function (table function):
SELECT * FROM explain_semantic_view('view_name',
    dimensions := ['dim1'],
    metrics := ['metric1']
);
-- Returns rows of explain_output VARCHAR
```

<!-- Native DDL syntax for CREATE: -->
```sql
CREATE SEMANTIC VIEW name AS
TABLES (
    alias AS physical_table PRIMARY KEY (col1, col2)
)
RELATIONSHIPS (
    rel_name AS from_alias(fk_col) REFERENCES to_alias
)
DIMENSIONS (
    alias.dim_name AS sql_expression
)
METRICS (
    alias.metric_name AS aggregate_expression
);
```

<!-- All 7 DDL verbs: -->
```sql
CREATE SEMANTIC VIEW name AS ...;
CREATE OR REPLACE SEMANTIC VIEW name AS ...;
CREATE SEMANTIC VIEW IF NOT EXISTS name AS ...;
DROP SEMANTIC VIEW name;
DROP SEMANTIC VIEW IF EXISTS name;
DESCRIBE SEMANTIC VIEW name;
SHOW SEMANTIC VIEWS;
```

<!-- sqllogictest format reference: -->
```
# Comments with hash
statement ok
SQL STATEMENT

query TTI rowsort
SELECT ...
----
expected_row_1_col1	expected_row_1_col2	expected_row_1_col3
expected_row_2_col1	expected_row_2_col2	expected_row_2_col3

statement error
SQL THAT SHOULD FAIL
----
partial error message match
```
</interfaces>
</context>

<tasks>

<task type="auto">
  <name>Task 1: Create 3-table E2E integration test with exact result verification</name>
  <files>test/sql/phase28_e2e.test, test/sql/TEST_LIST</files>
  <action>
Create `test/sql/phase28_e2e.test` with the following structure. Use the orders/customers/products e-commerce domain per user decision. Use `p28_` prefix for all table names to avoid cross-test catalog pollution.

**Setup section:**
```sql
require semantic_views

# Create 3 related tables with known data
CREATE TABLE p28_customers (id INTEGER, name VARCHAR, tier VARCHAR);
INSERT INTO p28_customers VALUES (1, 'Alice', 'gold'), (2, 'Bob', 'silver'), (3, 'Charlie', 'gold');

CREATE TABLE p28_products (id INTEGER, name VARCHAR, category VARCHAR);
INSERT INTO p28_products VALUES (10, 'Widget', 'hardware'), (20, 'Gadget', 'electronics');

CREATE TABLE p28_orders (
    id INTEGER, customer_id INTEGER, product_id INTEGER,
    amount DECIMAL(10,2), region VARCHAR
);
INSERT INTO p28_orders VALUES
    (1, 1, 10, 100.00, 'East'),
    (2, 1, 20, 200.00, 'East'),
    (3, 2, 10, 50.00, 'West'),
    (4, 3, 10, 150.00, 'East'),
    (5, 3, 20, 75.00, 'West');
```

**Define section:**
```sql
# 3-table PK/FK semantic view
CREATE SEMANTIC VIEW p28_analytics AS
  TABLES (
    o AS p28_orders PRIMARY KEY (id),
    c AS p28_customers PRIMARY KEY (id),
    p AS p28_products PRIMARY KEY (id)
  )
  RELATIONSHIPS (
    order_to_customer AS o(customer_id) REFERENCES c,
    order_to_product AS o(product_id) REFERENCES p
  )
  DIMENSIONS (
    c.customer_name AS c.name,
    p.product_name AS p.name,
    o.region AS o.region,
    c.tier AS c.tier
  )
  METRICS (
    o.total_revenue AS sum(o.amount),
    o.order_count AS count(*)
  );
```

**Query tests with exact result verification:**

1. **Cross-table: customer_name -> total_revenue** (joins o->c):
   - Alice: 100+200=300, Bob: 50, Charlie: 150+75=225
   - Use `query TR rowsort` (T=customer_name VARCHAR, R=total_revenue DECIMAL via VARCHAR)

2. **Cross-table: product_name -> order_count** (joins o->p):
   - Widget: 3 orders (ids 1,3,4), Gadget: 2 orders (ids 2,5)
   - Use `query TI rowsort` (T=product_name VARCHAR, I=order_count BIGINT)

3. **3-table transitive: customer_name + product_name -> total_revenue** (joins o->c AND o->p):
   - Alice/Widget: 100, Alice/Gadget: 200, Bob/Widget: 50, Charlie/Widget: 150, Charlie/Gadget: 75
   - Use `query TTR rowsort`

4. **Single-table: region -> total_revenue** (no joins needed, just orders):
   - East: 100+200+150=450, West: 50+75=125
   - Use `query TR rowsort`

5. **Metrics-only: total_revenue** (grand total):
   - 100+200+50+150+75=575
   - Use `query T` (DECIMAL as VARCHAR)

6. **Dimensions-only: region** (distinct values):
   - East, West
   - Use `query T rowsort`

7. **Explain verification:**
   - Call `explain_semantic_view('p28_analytics', dimensions := ['customer_name'], metrics := ['total_revenue'])`
   - Verify output contains `LEFT JOIN` (since joins are needed)
   - Verify output contains `GROUP BY` (since metric present)
   - Verify output contains `p28_orders` and `p28_customers` (base tables)

8. **WHERE composition:**
   - Query with WHERE region = 'East', verify only East results

9. **DESCRIBE verification:**
   - `DESCRIBE SEMANTIC VIEW p28_analytics` returns expected metadata

10. **Error case: unknown dimension:**
    - `semantic_view('p28_analytics', dimensions := ['nonexistent'])` errors with "unknown dimension"

**Cleanup section:**
```sql
DROP SEMANTIC VIEW p28_analytics;
DROP TABLE p28_orders;
DROP TABLE p28_customers;
DROP TABLE p28_products;
```

**Update TEST_LIST:**
Add `test/sql/phase28_e2e.test` to the list. If Plan 02 has already removed the 2 deleted files, just add the new entry. If TEST_LIST still has the old entries (parallel execution), remove `phase2_ddl.test` and `semantic_views.test` here too.
  </action>
  <verify>
    <automated>cd /Users/paul/Documents/Dev/Personal/duckdb-semantic-views && just build && just test-sql 2>&1 | tail -10</automated>
  </verify>
  <done>phase28_e2e.test created with 10+ test scenarios, all passing with exact result verification against known data</done>
</task>

<task type="auto">
  <name>Task 2: Rewrite README.md with clean-slate AS-body PK/FK documentation</name>
  <files>README.md</files>
  <action>
Rewrite README.md from scratch per user decisions. Structure:

**Header:**
```markdown
# DuckDB Semantic Views

A DuckDB extension that lets you define dimensions and metrics once, then query them in any combination. The extension writes the GROUP BY and JOIN logic for you.

Inspired by [Snowflake Semantic Views](https://docs.snowflake.com/en/sql-reference/sql/create-semantic-view), adapted for DuckDB as a loadable extension.

v0.5.2 -- early-stage, not yet on the community registry.
```

**How it works:**
Brief explanation: define a semantic view over one or more tables declaring dimensions (columns/expressions to group by), metrics (aggregates), and relationships (PK/FK join paths). Query by picking which dims/metrics you want. Extension generates the SQL.

**Quick start (single table):**
Use orders table from e-commerce domain. Show CREATE TABLE, then CREATE SEMANTIC VIEW with AS-body syntax, then query examples (dims+metrics, dims-only, metrics-only, WHERE).

```sql
CREATE TABLE orders (
    id INTEGER, region VARCHAR, category VARCHAR,
    amount DECIMAL(10,2)
);

CREATE SEMANTIC VIEW order_metrics AS
TABLES (
    o AS orders PRIMARY KEY (id)
)
DIMENSIONS (
    o.region AS o.region,
    o.category AS o.category
)
METRICS (
    o.revenue AS sum(o.amount),
    o.order_count AS count(*)
);

-- Pick any combination of dimensions and metrics
SELECT * FROM semantic_view('order_metrics',
    dimensions := ['region', 'category'],
    metrics := ['revenue', 'order_count']
);

-- Dimensions only (distinct values)
SELECT * FROM semantic_view('order_metrics',
    dimensions := ['region']
);

-- Metrics only (grand total)
SELECT * FROM semantic_view('order_metrics',
    metrics := ['revenue']
);

-- WHERE works on the result
SELECT * FROM semantic_view('order_metrics',
    dimensions := ['region'], metrics := ['revenue']
) WHERE region = 'East';
```

**Multi-table (PK/FK relationships):**
Show 3-table example with orders/customers/products. Demonstrate:
- TABLES with PRIMARY KEY on each
- RELATIONSHIPS with FK references
- Dimensions from different tables
- Metrics from the fact table
- Query that joins across tables (only needed tables get joined)

```sql
CREATE TABLE customers (id INTEGER, name VARCHAR, tier VARCHAR);
CREATE TABLE products (id INTEGER, name VARCHAR, category VARCHAR);
CREATE TABLE orders (
    id INTEGER, customer_id INTEGER, product_id INTEGER,
    amount DECIMAL(10,2), region VARCHAR
);

CREATE SEMANTIC VIEW analytics AS
TABLES (
    o AS orders PRIMARY KEY (id),
    c AS customers PRIMARY KEY (id),
    p AS products PRIMARY KEY (id)
)
RELATIONSHIPS (
    order_customer AS o(customer_id) REFERENCES c,
    order_product AS o(product_id) REFERENCES p
)
DIMENSIONS (
    c.customer_name AS c.name,
    p.product_name AS p.name,
    o.region AS o.region
)
METRICS (
    o.revenue AS sum(o.amount),
    o.order_count AS count(*)
);

-- Only customers table is joined (products not needed)
SELECT * FROM semantic_view('analytics',
    dimensions := ['customer_name'],
    metrics := ['revenue']
);

-- Both customers and products tables are joined
SELECT * FROM semantic_view('analytics',
    dimensions := ['customer_name', 'product_name'],
    metrics := ['revenue']
);
```

Add a brief note: "Only the tables needed for your requested dimensions and metrics get joined."

Fold explain into the multi-table section or add a brief subsection:
```sql
-- See the generated SQL
SELECT * FROM explain_semantic_view('analytics',
    dimensions := ['customer_name'],
    metrics := ['revenue']
);
```

**DDL reference:**
Show all 7 DDL verb forms with brief descriptions:
```sql
CREATE SEMANTIC VIEW name AS ...;
CREATE OR REPLACE SEMANTIC VIEW name AS ...;
CREATE SEMANTIC VIEW IF NOT EXISTS name AS ...;
DROP SEMANTIC VIEW name;
DROP SEMANTIC VIEW IF EXISTS name;
DESCRIBE SEMANTIC VIEW name;
SHOW SEMANTIC VIEWS;
```

**Building:**
Keep the existing building section with Rust/just/make/Python requirements and the command list:
```bash
just setup     # one-time: installs dev tools, configures build
just build     # debug build
cargo test     # unit + property-based tests
just test-sql  # SQL logic tests (needs just build first)
just test-all  # everything
just lint      # fmt + clippy + cargo-deny
```

**License:** MIT

**What NOT to include:**
- No mention of function DDL (`create_semantic_view()` etc.) -- it's been removed
- No "Function syntax" section
- No paren-body syntax examples
- No ON-clause join syntax
- Keep it concise -- this is a README, not a manual
  </action>
  <verify>
    <automated>cd /Users/paul/Documents/Dev/Personal/duckdb-semantic-views && grep -c "CREATE SEMANTIC VIEW" README.md && ! grep -q "create_semantic_view(" README.md && echo "README OK"</automated>
  </verify>
  <done>README.md rewritten with AS-body PK/FK syntax only, e-commerce examples, version updated to v0.5.2, no references to removed function DDL</done>
</task>

</tasks>

<verification>
After both tasks complete:
- `just build && just test-sql` passes with new E2E test
- `grep "CREATE SEMANTIC VIEW" README.md` shows multiple matches
- `grep "create_semantic_view(" README.md` returns no matches
- `grep "v0.5.2" README.md` returns a match
- README contains sections: How it works, Quick start, Multi-table, DDL reference, Building
</verification>

<success_criteria>
- phase28_e2e.test passes all assertions with exact result verification
- E2E test covers: 3-table PK/FK, cross-table joins, transitive joins, single-table, metrics-only, dims-only, explain, WHERE, DESCRIBE, error cases
- README shows only AS-body PK/FK syntax with e-commerce domain
- Version line reads "v0.5.2"
- DOC-01 requirement satisfied
</success_criteria>

<output>
After completion, create `.planning/phases/28-integration-testing-documentation/28-03-SUMMARY.md`
</output>
