view: prs_prod_sx_sales {
  sql_table_name: performance_rights_sales.prod.soundexchange_sales_stmt ;;

#Date
  dimension: allocation_period {
    type: string
    sql: ${TABLE}.allocation_period ;;
    view_label: "Date"
  }

  dimension: broadcast_start_date {
    type: date
    sql: ${TABLE}.broadcast_start_date ;;
    view_label: "Date"
  }

  dimension: broadcast_end_date {
    type: date
    sql: ${TABLE}.broadcast_end_date ;;
    view_label: "Date"
  }

#File
  dimension: file_name {
    type: string
    sql: ${TABLE}.filename ;;
    view_label: "File"
  }

  dimension: file_size {
    type: string
    sql: ${TABLE}.filesize ;;
    view_label: "File"
  }

#Licensor
  dimension: licensor_name {
    type: string
    sql: ${TABLE}.ro_name ;;
    view_label: "Licensor"
  }

#Track
  dimension: isrc {
    type: string
    sql: ${TABLE}.isrc ;;
    view_label: "Track"
    label: "ISRC"
  }

  dimension: track_name {
    type: string
    sql: ${TABLE}.track_name ;;
    view_label: "Track"
  }

#Release
  dimension: release_upc {
    type: string
    sql: ${TABLE}.release_upc ;;
    view_label: "Release"
    label: "UPC"
  }

  dimension: release_name {
    type: string
    sql: ${TABLE}.album_name ;;
    view_label: "Release"
  }

#Artist
  dimension: artist_id {
    type: string
    sql: ${TABLE}.artist_id ;;
    view_label: "Artist"
  }

  dimension: artist_name {
    type: string
    sql: ${TABLE}.artist_name ;;
    view_label: "Artist"
  }

#Label
  dimension: label_id {
    type: string
    sql: ${TABLE}.label_id ;;
    view_label: "Label"
    label: "SX Label ID"
  }

  dimension: label_name {
    type: string
    sql: ${TABLE}.label_name ;;
    view_label: "Label"
    label: "SX Label Name"
  }

  dimension: society_label_id {
    type: string
    sql: ${TABLE}.society_label_id ;;
    view_label: "Label"
    label: "SX Society Label ID"
  }

#Everything Else
  dimension: catalog_number {
    type: string
    sql: ${TABLE}.catalog_number ;;
  }

  dimension: category_of_service {
    type: string
    sql: ${TABLE}.category_of_service ;;
  }

  dimension: licensee_name {
    type: string
    sql: ${TABLE}.licensee_name ;;
  }

  dimension: pline_label_name {
    type: string
    sql: ${TABLE}.pline_label_name ;;
  }

  dimension: royalty_item_sxid {
    type: string
    sql: ${TABLE}.royalty_item_sxid ;;
  }

  dimension: schedule_id {
    type: string
    sql: ${TABLE}.schedule_id ;;
  }

  dimension: scr_repertoire_id {
    type: string
    sql: ${TABLE}.scr_repertoire_id ;;
  }

  dimension: third_party_name {
    type: string
    sql: ${TABLE}.third_party_name ;;
  }

  dimension: type {
    type: string
    sql: ${TABLE}.type ;;
  }

#Metrics
  measure: num_performances {
    type: sum
    sql: TO_NUMERIC(REGEXP_REPLACE(${TABLE}.num_performances, '[^\.0-9]+', '')) ;;
    label: "Performances"
  }

  measure: num_spins {
    type: sum
    sql: TO_NUMERIC(REGEXP_REPLACE(${TABLE}.num_spins, '[^\.0-9]+', '')) ;;
    label: "Spins"
  }

  measure: thirdparty_ephemeral_amount {
    type: sum
    sql: TO_DECIMAL(REGEXP_REPLACE(${TABLE}.thirdparty_ephemeral_amount, '[^\.0-9]+', ''), 10, 4) ;;
    label: "Third-Party (Ephemeral)"
    value_format: "$#,##0.00"
  }

  measure: third_party_non_ephemeral_amount {
    type: sum
    sql: TO_DECIMAL(REGEXP_REPLACE(${TABLE}.third_party_non_ephemeral_amount, '[^\.0-9]+', ''), 10, 4) ;;
    label: "Third-Party (Non-Ephemeral)"
    value_format: "$#,##0.00"
  }

  measure: non_ephemeral_amount {
    type: sum
    sql: TO_DECIMAL(REGEXP_REPLACE(${TABLE}.non_ephemeral_amount, '[^\.0-9]+', ''), 10, 4) ;;
    label: "Non-Ephemeral (Total)"
    value_format: "$#,##0.00"
  }

  measure: ephemeral_amount {
    type: sum
    sql: TO_DECIMAL(REGEXP_REPLACE(${TABLE}.ephemeral_amount, '[^\.0-9]+', ''), 10, 4) ;;
    label: "Ephemeral (Total)"
    value_format: "$#,##0.00"
  }

  measure: third_party_payment_amount {
    type: sum
    sql: TO_DECIMAL(REGEXP_REPLACE(${TABLE}.third_party_payment_amount, '[^\.0-9]+', ''), 10, 4) ;;
    label: "Third-Party (Total)"
    value_format: "$#,##0.00"
  }

  measure: your_payment_amount {
    type: sum
    sql: TO_DECIMAL(REGEXP_REPLACE(${TABLE}.your_payment_amount, '[^\.0-9]+', ''), 10, 4) ;;
    label: "Your Payment (Total)"
    value_format: "$#,##0.00"
  }
}