label: "KNR Models"
connection: "knr-looker"

include: "/views/chartmetric_spotify.view.lkml"
include: "/views/chartmetric_spotify_playlists.view.lkml" # Include the new pre-filtered playlist view
include: "/views/chartmetric_recordings.view.lkml"
include: "/views/global_countries.view.lkml"

# =========================================================================
# EXPLORE: CHARTMETRIC SPOTIFY PERFORMANCE (OPTIMIZED)
# =========================================================================
explore: chartmetric_spotify {
  hidden: no
  label: "Chartmetric Spotify Performance"
  description: "Comprehensive analytics hub tracking Spotify track consumption, popularity metrics, and live playlist snapshots tied back to the global directory."

  # 1. Join Global Directory (Optimized: Direct matching, no LOWER function penalty)
  join: chartmetric_recordings {
    type: left_outer
    relationship: many_to_many
    sql_on: ${chartmetric_spotify.isrc} = ${chartmetric_recordings.isrc} ;;
  }

  # 2. Join Pre-Filtered Playlists via internal track ID link
  join: chartmetric_spotify_playlists {
    type: left_outer
    relationship: one_to_many
    sql_on: ${chartmetric_spotify.track_id} = ${chartmetric_spotify_playlists.spotify_track_id_fk} ;;
  }

  # 3. Geography Mapping (Updated: Pointed to the new playlist view where the field lives)
  join: global_countries {
    view_label: "Chartmetric Spotify: Current Playlist Placement"
    type: left_outer
    relationship: many_to_one
    sql_on: UPPER(${chartmetric_spotify_playlists.playlist_country_code}) = UPPER(${global_countries.country_code}) ;;
  }
}