view: int_prod_accounting_analytics_blend {
  sql_table_name: intelligence.prod.accounting_analytics_blend ;;

  dimension: is_accounting {
    case: {
      when: { sql: ${TABLE}.is_accounting = TRUE ;; label: "Accounting" }
      else: "Analytics"
    }
    view_label: "Data Source"
    label: "Accounting or Analytics"
  }

#   dimension: activitymonth {
#     type: number
#     sql: ${TABLE}.activitymonth ;;
#   }
#
#   dimension: activityyear {
#     type: number
#     sql: ${TABLE}.activityyear ;;
#   }

  dimension_group: activity {
    view_label: "Date"
    type: time
    sql: DATE_FROM_PARTS(${TABLE}.activityyear, ${TABLE}.activitymonth, 1) ;;
    timeframes: [
      month,
      month_name,
      month_num,
      quarter,
      quarter_of_year,
      year
    ]
  }

#SOURCE
  dimension: store_id {
    type: number
    sql: ${TABLE}.storeid ;;
    hidden: yes
  }

  dimension: country_id {
    type: number
    sql: ${TABLE}.countryid ;;
    hidden: yes
  }

  dimension: is_local_revenue {
    type: yesno
    sql: ${TABLE}.countryid = ${facts_prod_dim_label.country_id} ;;
  }

  dimension: transaction_type_id {
    type: number
    sql: ${TABLE}.transactiontypeid ;;
    hidden: yes
  }

#DESTINATION
  dimension: label_id {
    type: number
    sql: ${TABLE}.labelid ;;
    hidden: yes
  }

  dimension: artist_id {
    type: number
    sql: ${TABLE}.artistid ;;
    hidden: yes
  }

  dimension: subaccount_id {
    type: number
    sql: ${TABLE}.subaccountid ;;
    hidden: yes
  }

  dimension: upc {
    type: number
    sql: ${TABLE}.releaseid ;;
    hidden: yes
    label: "UPC"
    value_format: "0"
  }

  dimension: track_id {
    type: number
    sql: ${TABLE}.trackid ;;
    hidden: yes
  }

#METRICS
  measure: gross_revenue_usd {
    type: sum
    sql: ${TABLE}.gross_usd ;;
    view_label: "Revenue (USD)"
    label: "Gross"
    value_format: "$#,##0.00"
  }

  measure: net_revenue_usd {
    type: sum
    sql: ${TABLE}.net_usd ;;
    view_label: "Revenue (USD)"
    label: "Net"
    value_format: "$#,##0.00"
  }

  measure: units {
    type: sum
    sql: ${TABLE}.units ;;
    view_label: "Units"
    label: "Units"
  }

   measure: estimated_revenue {
    type: sum
    hidden: no
    sql: ${int_prod_store_ppr.avg_ppr} * ${TABLE}.units ;;
    description: "Gross Revenue Estimate based on Avg PPR over the past 12 months on Analytics Units"
    value_format: "$#,##0.00"
    view_label: "Revenue (USD)"
  }

  measure: consumption_units_track {
    view_label: "Units"
    type: sum
    sql: CASE
      WHEN ${TABLE}.transactiontypeid = 1 THEN (${TABLE}.units / 125)
      WHEN ${TABLE}.transactiontypeid = 10 AND ${TABLE}.storeid != 453 THEN (${TABLE}.units / 375)
      WHEN ${TABLE}.transactiontypeid = 19 THEN ${TABLE}.units
      ELSE 0 END ;;
    label: "Consumption Units (Track)"
    value_format: "#,##0"
  }

  measure: consumption_units_album {
    view_label: "Units"
    type: sum
    sql: CASE
      WHEN ${TABLE}.transactiontypeid = 1 THEN (${TABLE}.units / 1250)
      WHEN ${TABLE}.transactiontypeid = 10 AND ${TABLE}.storeid != 453 THEN (${TABLE}.units / 3750)
      WHEN ${TABLE}.transactiontypeid = 19 THEN (${TABLE}.units / 10)
      WHEN ${TABLE}.transactiontypeid = 23 THEN ${TABLE}.units
      WHEN ${TABLE}.transactiontypeid = 49 THEN ${TABLE}.units
      WHEN ${TABLE}.transactiontypeid = 50 THEN ${TABLE}.units
      ELSE 0 END ;;
    label: "Consumption Units (Album)"
    value_format: "#,##0"
  }
}