# =============================================================================
# _int_models.yml
# Schema definitions and tests for intermediate models
# =============================================================================

version: 2

models:
  - name: int_aws_billing_ledger
    description: |
      Ledger-friendly intermediate view with account attribution and all charge types.
      Keeps Usage/Fee/Tax/Credit/Discount/Refund rows together for invoice and
      reconciliation use cases, while exposing helper usage metrics.

      Features:
      - Joins account_mapping with SCD Type 2 time-based lookup
      - Keeps join behavior transparent (no row_number-based silent dedup)
      - Preserves all line_item_type values (no usage-only filter)
      - Adds is_usage_row flag for downstream usage models
      - Adds effective_usage_cost_usd for SP/RI-aware usage cost analysis

    columns:
      - name: cloud_provider
        description: Cloud provider ('aws')
        tests:
          - not_null

      - name: usage_date
        description: Usage date
        tests:
          - not_null

      - name: billing_period_month
        description: First day of billing month
        tests:
          - not_null

      - name: account_id
        description: AWS account ID
        tests:
          - not_null

      - name: account_display_name
        description: Human-readable account name from account_mapping or fallback
        tests:
          - not_null

      - name: account_purpose
        description: Account purpose from account_mapping with default 'Others'
        tests:
          - not_null
          - accepted_values:
              values: ['Production', 'Development', 'Others', 'ProductionInfrastructure']

      - name: access_level
        description: Access level for row-level security
        tests:
          - not_null
          - accepted_values:
              values: ['public', 'restricted']

      - name: service
        description: AWS service code (e.g., AmazonEC2)
        tests:
          - not_null

      - name: region
        description: AWS region code or 'global'
        tests:
          - not_null

      - name: line_item_type
        description: Original AWS line item type (Usage/Fee/Tax/Credit/...)
        tests:
          - not_null

      - name: charge_type
        description: Simplified charge type
        tests:
          - not_null
          - accepted_values:
              values: ['Usage', 'Tax', 'Discount', 'Credit', 'Fee', 'Refund', 'Other']

      - name: pricing_model
        description: Normalized pricing model
        tests:
          - not_null

      - name: is_usage_row
        description: True for Usage and SavingsPlanCoveredUsage rows
        tests:
          - not_null

      - name: effective_usage_cost_usd
        description: |
          SP/RI-aware effective cost for usage rows.
          - SavingsPlanCoveredUsage: prefer aws_sp_effective_cost
          - Reserved usage: prefer aws_amortized_cost
          - Usage: fallback to actual_cost_usd
          NULL for non-usage rows.

      - name: sp_covered_list_cost_usd
        description: List-price amount covered by SP usage rows

      - name: ondemand_list_cost_usd
        description: List-price amount from on-demand usage rows

      - name: savings_amount
        description: Usage-row savings amount = list_cost_usd - effective_usage_cost_usd

      - name: savings_rate
        description: Usage-row savings rate = savings_amount / list_cost_usd

      - name: instance_family
        description: Instance family extracted from instance_type (e.g., m5 from m5.large)

  - name: int_aws_billing
    description: |
      Usage-focused intermediate view for optimization analysis.
      Built from int_aws_billing_ledger by filtering is_usage_row = true.

      Scope:
      - Keeps only Usage and SavingsPlanCoveredUsage rows
      - Provides list-price and SP/RI-aware effective usage cost
      - Serves as the input for current usage marts

    columns:
      - name: cloud_provider
        description: Cloud provider ('aws')
        tests:
          - not_null

      - name: usage_date
        description: Usage date
        tests:
          - not_null

      - name: billing_period_month
        description: First day of billing month
        tests:
          - not_null

      - name: account_id
        description: AWS account ID
        tests:
          - not_null

      - name: account_display_name
        description: Human-readable account name
        tests:
          - not_null

      - name: account_purpose
        description: Account purpose from account_mapping
        tests:
          - not_null
          - accepted_values:
              values: ['Production', 'Development', 'Others', 'ProductionInfrastructure']

      - name: access_level
        description: Access level for row-level security
        tests:
          - not_null
          - accepted_values:
              values: ['public', 'restricted']

      - name: service
        description: AWS service code
        tests:
          - not_null

      - name: region
        description: AWS region code or 'global'
        tests:
          - not_null

      - name: line_item_type
        description: Usage row types only in this model
        tests:
          - not_null
          - accepted_values:
              values: ['Usage', 'SavingsPlanCoveredUsage']

      - name: effective_usage_cost_usd
        description: SP/RI-aware effective usage cost

      - name: sp_covered_list_cost_usd
        description: List-price amount covered by SP usage rows

      - name: ondemand_list_cost_usd
        description: List-price amount from on-demand usage rows

      - name: savings_amount
        description: Savings amount = list_cost_usd - effective_usage_cost_usd

      - name: savings_rate
        description: Savings rate = savings_amount / list_cost_usd

      - name: instance_family
        description: Instance family extracted from instance_type

  - name: int_gcp_billing
    description: |
      Usage-focused intermediate view for GCP billing analytics.
      Combines account_mapping join and usage-only filter in one step
      (no separate _ledger model for GCP).

      Features:
      - Joins account_mapping with SCD Type 2 time-based lookup
      - Filters to usage rows only (charge_type = 'Usage', i.e. cost_type = 'regular')
      - Provides effective_usage_cost_usd (= actual_cost_usd for GCP v1)
      - Serves as the input for GCP usage marts

    columns:
      - name: cloud_provider
        description: Cloud provider ('gcp')
        tests:
          - not_null
          - accepted_values:
              values: ['gcp']

      - name: usage_date
        description: Usage date
        tests:
          - not_null

      - name: billing_period_month
        description: First day of billing month
        tests:
          - not_null

      - name: account_id
        description: GCP project ID (org-level charges with NULL project filtered out)
        tests:
          - not_null

      - name: account_display_name
        description: Human-readable account name from account_mapping or fallback
        tests:
          - not_null

      - name: account_purpose
        description: Account purpose from account_mapping with default 'Others'
        tests:
          - not_null
          - accepted_values:
              values: ['Production', 'Development', 'Others', 'ProductionInfrastructure']

      - name: access_level
        description: Access level for row-level security
        tests:
          - not_null
          - accepted_values:
              values: ['public', 'restricted']

      - name: service
        description: GCP service description (e.g., Compute Engine)
        tests:
          - not_null

      - name: region
        description: GCP region or 'global'
        tests:
          - not_null

      - name: charge_type
        description: Simplified charge type (Usage only in this model)
        tests:
          - not_null
          - accepted_values:
              values: ['Usage']

      - name: effective_usage_cost_usd
        description: |
          Effective usage cost for GCP.
          GCP v1: same as actual_cost_usd (no SP/RI equivalent).

  - name: int_alibaba_billing
    description: |
      Usage-focused intermediate view for Alibaba Cloud billing analytics.
      Combines account_mapping join and usage-only filter in one step
      (no separate _ledger model for Alibaba in MVP).

      Features:
      - Joins account_mapping with SCD Type 2 time-based lookup
      - Filters to usage rows only (SubscriptionOrder + PayAsYouGoBill)
      - Sets effective_usage_cost_usd = actual_cost_usd (no SP/RI equivalent)

    columns:
      - name: cloud_provider
        description: Cloud provider ('alibaba')
        tests:
          - not_null
          - accepted_values:
              values: ['alibaba']

      - name: usage_date
        description: Usage date
        tests:
          - not_null

      - name: billing_period_month
        description: First day of billing month
        tests:
          - not_null

      - name: account_id
        description: Alibaba owner account ID
        tests:
          - not_null

      - name: account_display_name
        description: Human-readable account name from account_mapping or fallback
        tests:
          - not_null

      - name: account_purpose
        description: Account purpose from account_mapping with default 'Others'
        tests:
          - not_null
          - accepted_values:
              values: ['Production', 'Development', 'Others', 'ProductionInfrastructure']

      - name: access_level
        description: Access level for row-level security
        tests:
          - not_null
          - accepted_values:
              values: ['public', 'restricted']

      - name: service
        description: Alibaba product name
        tests:
          - not_null

      - name: region
        description: Alibaba region or 'global'
        tests:
          - not_null

      - name: charge_type
        description: Simplified charge type (Usage only in this model)
        tests:
          - not_null
          - accepted_values:
              values: ['Usage']

      - name: effective_usage_cost_usd
        description: |
          Effective usage cost for Alibaba.
          Alibaba v1: same as actual_cost_usd (no SP/RI equivalent).