-- Pure BigQuery SQL Query (no CREATE VIEW statement).
-- Demonstrates multi-level nesting with 2 rows and items/attributes at each level.

WITH orders AS (
  SELECT 
    1001 AS order_id,
    "STORE-US-01" AS store_id,
    "e-commerce" AS channel,
    FALSE AS is_test_order,
    STRUCT(
      "iOS App" AS device_type,
      "192.168.1.55" AS ip_address,
      345 AS session_duration_sec
    ) AS metadata,
    STRUCT(
      "Alice Smith" AS name,
      "alice@example.com" AS email,
      STRUCT(
        "123 Main St" AS street,
        "San Francisco" AS city,
        "CA" AS state,
        "94105" AS zip_code
      ) AS address
    ) AS customer,
    [
      STRUCT(
        "SKU-A1" AS product_id,
        "Laptop" AS product_name,
        2 AS quantity,
        1200.00 AS unit_price,
        [
          STRUCT("electronics" AS category_name, "high-margin" AS tag),
          STRUCT("hardware" AS category_name, "flagship" AS tag)
        ] AS attributes
      ),
      STRUCT(
        "SKU-B2" AS product_id,
        "Mouse" AS product_name,
        1 AS quantity,
        25.00 AS unit_price,
        [
          STRUCT("accessories" AS category_name, "low-margin" AS tag),
          STRUCT("peripherals" AS category_name, "standard" AS tag)
        ] AS attributes
      )
    ] AS items,
    TIMESTAMP("2026-07-23T10:15:00Z") AS order_timestamp

  UNION ALL

  SELECT 
    1002 AS order_id,
    "STORE-EU-02" AS store_id,
    "retail" AS channel,
    FALSE AS is_test_order,
    STRUCT(
      "Android Web" AS device_type,
      "10.0.42.12" AS ip_address,
      512 AS session_duration_sec
    ) AS metadata,
    STRUCT(
      "Bob Jones" AS name,
      "bob@example.com" AS email,
      STRUCT(
        "456 Rue de Rivoli" AS street,
        "Paris" AS city,
        "Île-de-France" AS state,
        "75001" AS zip_code
      ) AS address
    ) AS customer,
    [
      STRUCT(
        "SKU-C3" AS product_id,
        "Monitor" AS product_name,
        1 AS quantity,
        350.00 AS unit_price,
        [
          STRUCT("displays" AS category_name, "mid-margin" AS tag),
          STRUCT("office" AS category_name, "ergonomic" AS tag)
        ] AS attributes
      ),
      STRUCT(
        "SKU-D4" AS product_id,
        "Keyboard" AS product_name,
        1 AS quantity,
        75.00 AS unit_price,
        [
          STRUCT("accessories" AS category_name, "high-margin" AS tag),
          STRUCT("peripherals" AS category_name, "standard" AS tag)
        ] AS attributes
      )
    ] AS items,
    TIMESTAMP("2026-07-23T11:30:00Z") AS order_timestamp
)
SELECT 
  *
FROM orders
