view: chartmetric_spotify_playlists {
  derived_table: {
    sql:
      SELECT
        lp.ID AS link_playlist_id,
        lp.SPOTIFY AS spotify_track_id_fk,
        lp.ADDED_AT AS playlist_added_at,
        lp.POSITION_LATEST AS playlist_position_latest,
        lp.START_SYS_PERIOD AS playlist_start_sys_period,
        lp.END_SYS_PERIOD AS playlist_end_sys_period,
        p.ID AS playlist_pk_id,
        p.PLAYLIST_ID AS spotify_playlist_id,
        p.CATEGORY_ID AS playlist_category_id,
        p.USER_ID AS playlist_user_id,
        p.NAME AS playlist_name,
        p.URL AS playlist_url,
        p.DESCRIPTION AS playlist_description,
        p.OWNER_NAME AS playlist_owner_name,
        p.SPOTIFY_PLAYLIST_OWNER AS spotify_playlist_owner_id,
        p.FOLLOWERS_LATEST AS playlist_followers_latest,
        p.NUM_TRACK_LATEST AS playlist_num_tracks_latest,
        p.LAST_UPDATED AS playlist_last_updated,
        p.CODE2 AS playlist_country_code,
        p.PLAYLIST_UPDATED AS playlist_updated_at,
        p.PERSONALIZED AS playlist_is_personalized,
        p.PUBLIC AS playlist_is_public,
        p.COLLABORATIVE AS playlist_is_collaborative,
        p.RADIO AS playlist_is_radio,
        p.ACTIVE AS playlist_is_active,
        p.SNAPSHOT_ID AS playlist_snapshot_id,
        p.FORMAT AS playlist_format,
        p.CREATED_AT AS playlist_created_at,
        p.MODIFIED_AT AS playlist_modified_at
      FROM CHARTMETRIC.RAW_DATA.L_SPOTIFY_PLAYLIST lp
      INNER JOIN CHARTMETRIC.RAW_DATA.SPOTIFY_PLAYLIST p ON lp.SPOTIFY_PLAYLIST = p.ID
      WHERE p.NAME LIKE '%New Music Friday%'
        AND p.SPOTIFY_PLAYLIST_OWNER = 2
      ;;
  }

  # =========================================================================
  # PRIMARY KEYS & FOREIGN KEYS
  # =========================================================================
  dimension: link_playlist_id {
    primary_key: yes
    hidden: yes
    type: string
    sql: ${TABLE}.link_playlist_id ;;
  }

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

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

  # =========================================================================
  # PLAYLIST PLACEMENT DIMENSIONS
  # =========================================================================
  dimension: spotify_playlist_id {
    type: string
    sql: ${TABLE}.spotify_playlist_id ;;
    group_label: "Current Playlist Placement"
  }

  dimension: playlist_owner_name {
    type: string
    sql: ${TABLE}.playlist_owner_name ;;
    group_label: "Current Playlist Placement"
  }

  dimension: playlist_position_latest {
    type: number
    sql: ${TABLE}.playlist_position_latest ;;
    group_label: "Current Playlist Placement"
  }

  dimension: playlist_followers_latest {
    type: number
    sql: ${TABLE}.playlist_followers_latest ;;
    group_label: "Current Playlist Placement"
  }

  dimension: playlist_num_tracks_latest {
    type: number
    sql: ${TABLE}.playlist_num_tracks_latest ;;
    group_label: "Current Playlist Placement"
  }

  dimension: playlist_country_code {
    type: string
    sql: ${TABLE}.playlist_country_code ;;
    group_label: "Current Playlist Placement"
  }

  dimension: playlist_category_id {
    type: string
    sql: ${TABLE}.playlist_category_id ;;
    group_label: "Current Playlist Placement"
  }

  dimension: playlist_format {
    type: string
    sql: ${TABLE}.playlist_format ;;
    group_label: "Current Playlist Placement"
  }

  dimension: playlist_is_active {
    type: yesno
    sql: ${TABLE}.playlist_is_active ;;
    group_label: "Current Playlist Placement"
  }

  dimension: playlist_is_public {
    type: yesno
    sql: ${TABLE}.playlist_is_public ;;
    group_label: "Current Playlist Placement"
  }

  dimension: playlist_is_personalized {
    type: yesno
    sql: ${TABLE}.playlist_is_personalized ;;
    group_label: "Current Playlist Placement"
  }

  dimension: playlist_is_collaborative {
    type: yesno
    sql: ${TABLE}.playlist_is_collaborative ;;
    group_label: "Current Playlist Placement"
  }

  dimension: playlist_is_radio {
    type: yesno
    sql: ${TABLE}.playlist_is_radio ;;
    group_label: "Current Playlist Placement"
  }

  dimension: playlist_url {
    type: string
    sql: ${TABLE}.playlist_url ;;
    group_label: "Assets & Links"
  }

  dimension: playlist_name {
    type: string
    sql: ${TABLE}.playlist_name ;;
    case_sensitive: no
    group_label: "Current Playlist Placement"
  }

  dimension_group: playlist_added {
    type: time
    timeframes: [raw, time, date, week, month, year]
    datatype: timestamp
    sql: ${TABLE}.playlist_added_at ;;
    group_label: "Current Playlist Placement"
  }

  # =========================================================================
  # HYBRID DATE FIELDS
  # =========================================================================
  dimension_group: created_or_release {
    view_label: "Date"
    label: "Created or Release"
    description: "Replicated Metric: Yields the track index/playlist timestamp for New Music Friday tracks, or the official commercial release date otherwise."
    type: time
    datatype: date
    timeframes: [
      date,
      week,
      month,
      quarter,
      year,
      day_of_week,
      day_of_year,
      week_of_year,
      month_name,
      month_num,
      fiscal_month_num,
      fiscal_quarter,
      fiscal_quarter_of_year,
      fiscal_year
    ]
    sql: COALESCE(CAST(${TABLE}.playlist_added_at AS DATE), CAST(${chartmetric_spotify.album_release_date} AS DATE)) ;;
  }

  # =========================================================================
  # HYBRID PLAYLIST FIELDS
  # =========================================================================
  dimension: playlist_or_release_type {
    type: string
    label: "Playlist or Release Type"
    description: "Replicated Metric: Displays the New Music Friday playlist name if present; otherwise defaults to the commercial release format (Album/Single)."
    sql: COALESCE(${TABLE}.playlist_name, ${chartmetric_spotify.album_type}) ;;
  }

  # =========================================================================
  # MEASURES
  # =========================================================================
  measure: unique_playlist_count {
    type: count_distinct
    sql: ${playlist_pk_id} ;;
  }
}