entity_id: order_line

mapping_groups:
  - name: default_mapping_group
    tables:
      # Order lines. order_line_id is a line number within the order (values 1..3),
      # so the natural key is the composite (order_id, order_line_id), expressed as
      # one key expression. (A multi-entry primary_keys list means alternative
      # identifiers and requires allow_multiple_identifiers, which is irreversible.)
      # The table has no timestamp of its own; the order date is read from the
      # order header (the generated reader selects FROM stage.sales_order_lines
      # without an alias, so the correlated subquery binds to the outer row).
      - connection: duckdb
        table: stage.sales_order_lines
        primary_keys:
          - "order_id || '-' || order_line_id"
        # FULL: the source overwrites lines in place (fulfillment_status changes,
        # no updated_at), so each run is a snapshot compared against the previous one.
        ingestion_strategy: FULL
        entity_effective_timestamp_expression: >-
          (SELECT o.order_date FROM stage.sales_orders o
            WHERE o.order_id = sales_order_lines.order_id)
        attributes:
          - id: order_line_id
            transformation_expression: "order_id || '-' || order_line_id"
          - id: order_line_status
            transformation_expression: fulfillment_status
          - id: order_line_quantity
            transformation_expression: quantity
          - id: order_line_unit_price_amount        # child attribute of the order_line_unit_price group
            transformation_expression: unit_price
          - id: order_line_unit_price_currency      # child attribute of the order_line_unit_price group
            # USD assumed: every order in stage is USD
            transformation_expression: &order_currency "'USD'"
          - id: order_line_discount_amount          # child attribute of the order_line_discount group
            transformation_expression: discount_amount
          - id: order_line_discount_currency        # child attribute of the order_line_discount group
            transformation_expression: *order_currency
          - id: order_line_tax_amount               # child attribute of the order_line_tax group
            transformation_expression: tax_amount
          - id: order_line_tax_currency             # child attribute of the order_line_tax group
            transformation_expression: *order_currency
          - id: order_line_amount_amount            # child attribute of the order_line_amount group
            # Business rule: amount charged = quantity * unit_price - discount + tax,
            # taken from the source line total (tax included)
            transformation_expression: line_total
          - id: order_line_amount_currency          # child attribute of the order_line_amount group
            transformation_expression: *order_currency

    relationships:
      - id: order_line_is_part_of_order
        source_table: stage.sales_order_lines
        target_transformation_expression: order_id
      - id: order_line_is_of_book
        source_table: stage.sales_order_lines
        target_transformation_expression: product_id
      # (the full mapping also maps order_line_is_fulfilled_from_location;
      # location is not in this 5-entity excerpt of the model)