# Tutorial-sized OSI model used across osi-engine tests:
# one fact table (orders), two dimensions (customers, products),
# a time dimension, and one metric of each inferable kind.

version: "0.2.0.dev0"

semantic_model:
  - name: orders_model
    description: Minimal e-commerce model for engine tests
    datasets:
      - name: orders
        source: main.orders
        primary_key: [order_id]
        fields:
          - name: order_id
            expression:
              dialects:
                - dialect: ANSI_SQL
                  expression: order_id
          - name: customer_id
            expression:
              dialects:
                - dialect: ANSI_SQL
                  expression: customer_id
          - name: product_id
            expression:
              dialects:
                - dialect: ANSI_SQL
                  expression: product_id
          - name: order_date
            expression:
              dialects:
                - dialect: ANSI_SQL
                  expression: order_date
            dimension:
              is_time: true
          - name: status
            expression:
              dialects:
                - dialect: ANSI_SQL
                  expression: status
          - name: amount
            expression:
              dialects:
                - dialect: ANSI_SQL
                  expression: amount

      - name: customers
        source: main.customers
        primary_key: [customer_id]
        fields:
          - name: customer_id
            expression:
              dialects:
                - dialect: ANSI_SQL
                  expression: customer_id
          - name: region
            expression:
              dialects:
                - dialect: ANSI_SQL
                  expression: region
          - name: signup_date
            expression:
              dialects:
                - dialect: ANSI_SQL
                  expression: signup_date
            dimension:
              is_time: true

      - name: products
        source: main.products
        primary_key: [product_id]
        fields:
          - name: product_id
            expression:
              dialects:
                - dialect: ANSI_SQL
                  expression: product_id
          - name: category
            expression:
              dialects:
                - dialect: ANSI_SQL
                  expression: category
          - name: unit_cost
            expression:
              dialects:
                - dialect: ANSI_SQL
                  expression: unit_cost

    relationships:
      - name: orders_to_customers
        from: orders
        to: customers
        from_columns: [customer_id]
        to_columns: [customer_id]
      - name: orders_to_products
        from: orders
        to: products
        from_columns: [product_id]
        to_columns: [product_id]

    metrics:
      - name: revenue
        description: Total order amount
        expression:
          dialects:
            - dialect: ANSI_SQL
              expression: SUM(orders.amount)
      - name: order_count
        description: Number of orders
        expression:
          dialects:
            - dialect: ANSI_SQL
              expression: COUNT(orders.order_id)
      - name: unique_customers
        description: Distinct purchasing customers
        expression:
          dialects:
            - dialect: ANSI_SQL
              expression: COUNT(DISTINCT orders.customer_id)
      - name: avg_order_value
        description: Revenue per order (ratio)
        expression:
          dialects:
            - dialect: ANSI_SQL
              expression: SUM(orders.amount) / COUNT(orders.order_id)
      - name: total_margin
        description: Revenue minus cost (expression over two aggregates)
        expression:
          dialects:
            - dialect: ANSI_SQL
              expression: SUM(orders.amount) - SUM(products.unit_cost)