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
    sql:  ${TABLE}.storename  ;;

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

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

      {% elsif facts_prod_fact_analytics_v2.releaseid._is_filtered %}
      https://theorchard.looker.com/dashboards/1378?Label%20ID={{_filters['facts_prod_fact_analytics_v2.labelid']}}&Store%20Name={{ value }}&Country%20Name={{_filters['transaction_country.name']}}&UPC={{_filters['facts_prod_fact_analytics_v2.releaseid']}}&Territory%20Group={{_filters['int_prod_territory_grouping.territory_group']}}

      {% elsif _explore._name == 'facts_prod_fact_analytics_v2' %}
      https://theorchard.looker.com/dashboards/1378?ISRC={{_filters['orch_app_ar_track.isrc']}}&Label%20ID={{_filters['facts_prod_fact_analytics_v2.labelid']}}&Store%20Name={{ value }}&Country%20Name={{_filters['transaction_country.name']}}&UPC={{_filters['facts_prod_fact_analytics_v2.releaseid']}}&Territory%20Group={{_filters['int_prod_territory_grouping.territory_group']}}&Artist%20ID={{_filters['facts_prod_fact_analytics_v2.artistid']}}

      {% 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 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: 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} ;;
  }
}

# Missing Fields: raw_table (?)