view: facts_prod_dim_playlist {
  sql_table_name: facts.prod.dim_playlist;;
  suggestions: no


  #sql_table_name: facts.prod.dim_playlist ;;

  dimension: playlistid {
    type: number
    sql: ${TABLE}.playlistid ;;
    primary_key: yes
    hidden: yes
  }

  dimension: playlistauthor {
    label: "Author"
    type: string
    sql: ${TABLE}.playlistauthor ;;
  }

  dimension: playlistimage {
    label: "Image"
    type: string
    sql: ${TABLE}.playlistimage ;;
  }

  dimension: playlistname {
    label: "Playlist Name"
    type: string
    sql: ${TABLE}.playlistname ;;
    link: {
      label: "Playlist Analysis"
      url:
      "

      {% if facts_prod_dim_playlist.playlistname._in_query and facts_prod_dim_playlist.playlistauthor._in_query %}
      https://theorchard.looker.com/dashboards-next/1625?Playlist%20Store%20ID={{ facts_prod_dim_playlist.storeplaylistid._value }}

      {% else %}

      {% endif %}
      "
    }
  }

  dimension: playlisttypename {
    label: "Type"
    type: string
    sql: ${TABLE}.playlisttypename ;;
  }

  dimension: is_editorial {
    type: yesno
    description: "Based off logic where the author is the same as the store - includes both playlists and stations"
    sql:
    (${TABLE}.storeid = 1 AND CONTAINS(${TABLE}.playlisttypename, 'Editorial'))
    OR (${TABLE}.storeid = 286 AND CONTAINS(LOWER(${TABLE}.playlistauthor), 'spotify'))
    OR (${TABLE}.storeid = 187 AND (${TABLE}.playlistauthor = 'Amazon Music'))
     OR (${TABLE}.storeid = 716 AND (${TABLE}.playlistauthor = 'Amazon Unlimited'))

      ;;
  }

  dimension: playlisturi {
    label: "URI"
    type: string
    sql: ${TABLE}.playlisturi ;;
  }

  dimension: playlisturl {
    label: "URL"
    type: string
    sql: ${TABLE}.playlisturl ;;
  }

  dimension: storeplaylistid_original_value {
    label: "Store Playlist ID (original value)"
    description: "The unique identifier for a playlist as provided by the store"
    type: string
    sql:${TABLE}.storeplaylistid;;
    hidden: no
  }

  dimension: storeplaylistid {
    label: "Store Playlist ID"
    description: "The unique identifier for a playlist as provided by the store"
    type: string
    sql:
    CASE
      WHEN (${facts_prod_dim_playlist.storeid} = 187 or ${facts_prod_dim_playlist.storeid} = 716)
        THEN substring(${TABLE}.storeplaylistid,1,10)
    ELSE ${TABLE}.storeplaylistid
    END;;
    hidden: no
  }

  dimension_group: createddatetime {
    label: "Created"
    description: "The date the playlist was first seen by The Orchard; Note: playlists seen prior to 2019-05-15 have the same Created Date"
    type: time
    sql: ${TABLE}.createddatetime ;;
  }

  dimension_group: updateddatetime {
    label: "Updated"
    description: "The date the playlist's metadata was most recently updated; Note: playlists seen prior to 2019-05-15 have the same Updated Date"
    type: time
    sql: ${TABLE}.updateddatetime ;;
    timeframes: [year,quarter,month,week,date]
  }

  dimension: playlisttypeid {
    type: string
    sql: ${TABLE}.playlisttypeid ;;
    hidden: yes
  }

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

  measure: count_playlists {
    label: "Number of Playlists"
    type: count_distinct
    sql: ${playlistid} ;;
  }

  measure: first_week_editorial_playlists {
    label: "First Week Editorial Placements"
    type: count_distinct
    sql:

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

          CASE
                  WHEN (((DATEDIFF('day', ${orch_app_ar_releases.release_date}, ${facts_prod_fact_analytics_v2.download_activity_date_date})) >= -1
                         and (DATEDIFF('day', ${orch_app_ar_releases.release_date}, ${facts_prod_fact_analytics_v2.download_activity_date_date})) <= 6)

      and ((${TABLE}.storeid = 1 AND CONTAINS(${TABLE}.playlisttypename, 'Editorial'))
      OR (${TABLE}.storeid = 286 AND CONTAINS(${TABLE}.playlistauthor, 'spotify'))
      OR (${TABLE}.storeid = 187 AND (${TABLE}.playlistauthor = 'Amazon Music')))
      )
      THEN ${playlistid}
      ELSE NULL
      END

      {% else %}

      NULL

      {% endif %}
      ;;
  }

}