view: facts_prod_dim_store {
  sql_table_name: facts.prod.dim_store ;;

  dimension: store_id {
    type: number
    sql: ${TABLE}.storeid ;;
    primary_key: yes
  }


  dimension: store_name {
    type: string
    bypass_suggest_restrictions: yes
    sql:  ${TABLE}.storename  ;;

    link: {
      label: "{{ value }} Deep Dive"
      url: "

      {% if _explore._name != 'facts_prod_fact_analytics_v2' %}

      {% elsif orch_app_ar_track.isrc._in_query and facts_prod_dim_playlist.playlistname._in_query and facts_prod_dim_playlist.playlistauthor._in_query%}

      https://theorchard.looker.com/dashboards-next/1761?ISRC={{ orch_app_ar_track.isrc._value}}&Artist%20Name={{ orch_app_ar_artist_info.name._value }}&Label%20ID={{facts_prod_fact_analytics_v2.labelid._value }}&Store%20Name={{ value }}

      {% else %}

      {% endif %}
      "
    }
  }

  dimension: store_amazon_breakdown {
    type: string
    description: "Use this field when wanting to see Amazon Prime broken out (only in Accounting or Analytics)"
    sql:
     {% if _explore._name == 'facts_prod_fact_analytics_v2' or _explore._name == 'facts_prod_fact_sales'%}

           CASE WHEN  (${TABLE}.storename = 'Amazon Music' AND ${facts_prod_dim_transactiontype.abbreviation} = 'S') THEN 'Amazon Unlimited'
           WHEN (${TABLE}.storename = 'Amazon Unlimited' AND ${facts_prod_dim_transactiontype.abbreviation} = 'MT') THEN 'Amazon Prime'
           WHEN  (${TABLE}.storename = 'Amazon Unlimited' AND ${facts_prod_dim_transactiontype.abbreviation} = 'S') THEN 'Amazon Unlimited'
           WHEN (${TABLE}.storename = 'Amazon Music' AND ${facts_prod_dim_transactiontype.abbreviation} = 'MT') THEN 'Amazon Prime'
           WHEN (${TABLE}.storename = 'Amazon Music' AND ${facts_prod_dim_transactiontype.abbreviation} = 'AS') THEN 'Amazon Free'
           ELSE ${TABLE}.storename END

      {% else %}

      NULL

      {% endif %}
      ;;
  }

  dimension: custom_store_breakdown {
    type: string
    label: "Custom Store Breakdown"
    description: "Most Popular Stores Only"
    sql:
      CASE WHEN  (${TABLE}.storename = 'Amazon Music' AND ${facts_prod_dim_transactiontype.abbreviation} = 'S') THEN 'Amazon Unlimited'
           WHEN  (${TABLE}.storename = 'Amazon Unlimited' AND ${facts_prod_dim_transactiontype.abbreviation} = 'S') THEN 'Amazon Unlimited'
           WHEN (${TABLE}.storename = 'Amazon Unlimited' AND ${facts_prod_dim_transactiontype.abbreviation} = 'MT') THEN 'Amazon Prime'
           WHEN (${TABLE}.storename = 'Amazon Music' AND ${facts_prod_dim_transactiontype.abbreviation} = 'MT') THEN 'Amazon Prime'
           WHEN (${TABLE}.storename = 'Amazon Music' AND ${facts_prod_dim_transactiontype.abbreviation} = 'AS') THEN 'Amazon Free'
           WHEN (${TABLE}.storename = 'Spotify') THEN 'Spotify'
           WHEN (${TABLE}.storename = 'iTunes/Apple') THEN 'iTunes/Apple'
           WHEN (${TABLE}.storename = 'Pandora') THEN 'Pandora'
           WHEN (${TABLE}.storename = 'YouTube') THEN 'YouTube'
           WHEN (${TABLE}.storename = 'YouTube Subscription') THEN 'YouTube'
           WHEN (${TABLE}.storename in (select storename from facts.prod.dim_store)) THEN 'All Other Partners'
           ELSE ${TABLE}.storename END;;
  }

  dimension: spotify_vs_apple_vs_other {
    label: "Group By: Spotify vs. Apple vs. YouTube vs. Other"
    case: {
      when: { sql: ${TABLE}.storeid = 1 ;; label: "Apple" }
      when: { sql: ${TABLE}.storeid = 286  ;; label: "Spotify" }
      when: { sql: ${TABLE}.storeid = 453 OR ${TABLE}.storeid = 569 ;; label: "YouTube" }
      else: "Other"
    }
  }

  measure: count_stores {
    label: "Number of Stores"
    type: count_distinct
    sql: ${store_id} ;;
  }
}