view: unified_publishing_master_royalties {
  derived_table: {
    sql:
      with EARLIEST_DELIVERED_AT_DATE as (  -- Needed for Red Flag Report
        select
          pub_song_id,
          MIN(delivery_date) as first_delivered_date
        from intelligence.dbt_prod.publishing_metadata
        group by pub_song_id
      )

      select
        royalty_type,
        accounting_period_id,
        activity_date,
        esd.first_delivered_date,
        label_id,
        country_name,
        store_name,
        transaction_group,
        pub_song_id,
        song_title,
        composers,
        summed_splits,
        revenue_multiplier,
        {% if scale_publishing_revenue._parameter_value == "'Yes'" %} (gross_revenue_usd * revenue_multiplier)
        {% else %} gross_revenue_usd
        {% endif %} as gross_revenue_usd,

        {% if scale_publishing_revenue._parameter_value == "'Yes'" %} (adjusted_gross_revenue_usd * revenue_multiplier)
        {% else %} adjusted_gross_revenue_usd
        {% endif %} as adjusted_gross_revenue_usd,

        {% if scale_publishing_revenue._parameter_value == "'Yes'" %} (net_revenue_usd * revenue_multiplier)
        {% else %} net_revenue_usd
        {% endif %} as net_revenue_usd,

        {% if scale_publishing_revenue._parameter_value == "'Yes'" %} (net_revenue_preferred_currency * revenue_multiplier)
        {% else %} net_revenue_preferred_currency
        {% endif %} as net_revenue_preferred_currency

      from intelligence.dbt_prod.unified_publishing_and_master_royalties royalties
      left join EARLIEST_DELIVERED_AT_DATE esd using(pub_song_id)
    ;;
  }
  parameter: scale_publishing_revenue {
    type: string
    allowed_value: { label: "Yes" value: "Yes"}
    allowed_value: { label: "No" value: "No"}
  }

  dimension: royalty_type {
    description: "Specifies the type of royalty revenue is associated with. Either 'Publishing Royalties' or 'Master Royalties'"
    type: string
    sql: ${TABLE}.royalty_type ;;
  }

  dimension_group: activity_date {
    description: "The date in which activity occurred."
    label: "Activity"
    type: time
    timeframes: [quarter,date,month,year]
    sql: ${TABLE}.activity_date ;;
  }

  dimension_group: first_delivered_date {
    description: "Date when the release was first delivered."
    label: "First Delivered Date"
    type: time
    timeframes: [quarter,month,year,date]
    sql: ${TABLE}.first_delivered_date ;;
  }

  dimension: accounting_period_id {
    description: "The accounting period ID"
    type: number
    sql: ${TABLE}.accounting_period_id ;;
  }

  dimension: label_id{
    description: "The unique identifier for a label."
    type: number
    sql: ${TABLE}.label_id ;;
  }

  dimension: transaction_country {
    description: "The country in which a transaction occurred."
    type: string
    sql: ${TABLE}.country_name ;;
  }

  dimension: store_name {
    description: "The store from which royalties were reported."
    type: string
    sql: ${TABLE}.store_name ;;
  }

  dimension: transaction_group {
    description: "The type of transaction that revenue was reported for."
    type: string
    sql: ${TABLE}.transaction_group ;;
  }

  dimension: pub_song_id {
    description: "The song id associated with the composition. Pub Song IDs can have multiple ISRCs (sound recordings) associated with them."
    type: number
    sql: ${TABLE}.pub_song_id ;;
  }

  dimension: song_title {
    description: "The song's title."
    type: string
    sql: ${TABLE}.song_title ;;
  }

# previous composers field for filtering
  dimension: composers {
    description: "The composers of a composition."
    type: string
    sql: ${TABLE}.composers ;;
  }

  # parameter: apply_composer_filtering {
  #   type: string
  #   allowed_value: { label: "Yes" value: "Yes"}
  #   allowed_value: { label: "No" value: "No"}
  # }

  # filter: composers {
  #   case_sensitive: no
  #   type: string
  #   full_suggestions: yes
  #   # sql:
  #   #     SELECT DISTINCT
  #   #       psw.legal_name as composer
  #   #     FROM facts.prod.publishing_composition pc
  #   #       INNER JOIN facts.prod.publishing_has_composition hc  on pc.id = hc.composition_id
  #   #       INNER JOIN royalty_accounting_reporting.prod.vw_dim_abacus_ar_vendor ar ON hc.label_id = ar.vendor_id
  #   #       INNER JOIN facts.prod.publishing_composition_agreement pca on pca.composition_id = pc.id
  #   #       INNER JOIN facts.prod.publishing_agreement pa on pa.id = pca.agreement_id
  #   #       INNER JOIN facts.prod.publishing_songwriter_agreement psa on psa.agreement_id = pa.id
  #   #       INNER JOIN facts.prod.publishing_song_writer psw on psw.id = psa.songwriter_id
  #   # WHERE pc.draft = FALSE;;
  #   }

  dimension: summed_splits {
    description: "The sum of splits for composers that are controlled."
    type: number
    sql: ${TABLE}.summed_splits ;;
  }

  dimension: revenue_multiplier{
    description: "The multiple that revenue needs to be scaled in order to estimate publishing revenue if we controlled all composers who contributed to the composition."
    type: number
    sql: ${TABLE}.revenue_multiplier ;;
  }

  measure: gross_revenue_USD {
    description: "Gross revenue reported in USD."
    label: "Gross Revenue (USD)"
    type: sum
    value_format: "$#,##0.00"
    sql: ${TABLE}.gross_revenue_usd;;
  }

  measure: adjusted_gross_revenue_USD {
    description: "Adjusted gross revenue in USD."
    label: "Adjusted Gross Revenue (USD)"
    type: sum
    value_format: "$#,##0.00"
    sql: ${TABLE}.adjusted_gross_revenue_usd;;
  }

  measure: net_revenue_USD {
    description: "Net revenue in USD."
    label: "Net Revenue (USD)"
    type: sum
    value_format: "$#,##0.00"
    sql: ${TABLE}.net_revenue_usd;;
  }

  measure: net_revenue_preferred_currency{
    description: "Net revenue in the label's preferred currency."
    label: "Net Revenue (Preferred Currency)"
    type: sum
    sql: ${TABLE}.net_revenue_preferred_currency;;
  }

}