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

include: "/views/chartmetric_recordings.view.lkml"
include: "/views/chartmetric_spotify.view.lkml"
include: "/views/chartmetric_spotify_playlists.view.lkml"
include: "/views/chartmetric_itunes.view.lkml"
include: "/views/global_countries.view.lkml"
include: "/views/performance_nr_contribution.view.lkml"
include: "/views/performance_nr_contributor.view.lkml"
include: "/views/performance_nr_particpant.view.lkml"
include: "/views/performance_nr_sound_recording.view.lkml"
include: "/views/nr_sound_recording_has_featured_artist_nr_participant.view.lkml"
include: "/views/nr_sound_recording_has_primary_artist_nr_participant.view.lkml"
include: "/views/nr_contribution_contributed_to_nr_sound_recording.view.lkml"
include: "/views/nr_contribution_type.view.lkml"

# =========================================================================
# EXPLORE: CHARTMETRIC - NEW RELEASES (MODULAR ARCHITECTURE)
# =========================================================================
explore: chartmetric_recordings {
  hidden: no
  label: "Chartmetric - New Releases"
  description: "Unified cross-platform tracking matrix comparing live Spotify, iTunes, and internal Performer NR sound recording registries."

  # 1. Join Core Spotify Data via explicit matching keys
  join: chartmetric_spotify {
    type: left_outer
    relationship: one_to_many
    sql_on: ${chartmetric_recordings.isrc} = ${chartmetric_spotify.isrc} ;;
  }

  # 2. Join the high-performance Pre-Filtered Playlist spoke via the Spotify Track ID
  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. Join Chartmetric iTunes Spoke
  join: chartmetric_itunes {
    type: left_outer
    relationship: one_to_many
    sql_on: ${chartmetric_recordings.isrc} = ${chartmetric_itunes.isrc} ;;
  }

  # 4. Join Internal Catalog Spoke
  join: performance_nr_sound_recording {
    type: left_outer
    relationship: one_to_one
    sql_on: ${chartmetric_recordings.isrc} = ${performance_nr_sound_recording.isrc} ;;
  }

  # 5. Geography Dimensions & Storefront Resolutions
  join: global_countries {
    type: left_outer
    relationship: many_to_one
    sql_on: UPPER(${chartmetric_recordings.artist_country_code}) = UPPER(${global_countries.country_code}) ;;
  }

  join: spotify_countries {
    from: 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(${spotify_countries.country_code}) ;;
  }

  join: itunes_countries {
    from: global_countries
    view_label: "Chartmetric iTunes: Track Details"
    type: left_outer
    relationship: many_to_one
    sql_on: UPPER(${chartmetric_itunes.storefront}) = UPPER(${itunes_countries.country_code}) ;;
  }

  # 6. Core Performance Contributor & Participant Hierarchies
  join: nr_contribution_contributed_to_nr_sound_recording {
    type: left_outer
    relationship: one_to_many
    sql_on: ${performance_nr_sound_recording.id} = ${nr_contribution_contributed_to_nr_sound_recording.nr_sound_recording_id} ;;
  }

  join: performance_nr_contribution {
    type: left_outer
    relationship: many_to_one
    sql_on: ${nr_contribution_contributed_to_nr_sound_recording.nr_contribution_id} = ${performance_nr_contribution.id} ;;
  }

  join: nr_contribution_type {
    type: left_outer
    relationship: many_to_one
    sql_on: ${performance_nr_contribution.id} = ${nr_contribution_type.nr_contribution_id} ;;
  }

  join: performance_nr_contributor {
    type: left_outer
    relationship: many_to_one
    sql_on: ${nr_contribution_type.nr_contributor_id} = ${performance_nr_contributor.id} ;;
  }

  join: nr_sound_recording_has_featured_artist_nr_participant {
    type: left_outer
    relationship: one_to_many
    sql_on: ${performance_nr_sound_recording.id} = ${nr_sound_recording_has_featured_artist_nr_participant.nr_sound_recording_id} ;;
  }

  join: nr_sound_recording_has_primary_artist_nr_participant {
    type: left_outer
    relationship: one_to_many
    sql_on: ${performance_nr_sound_recording.id} = ${nr_sound_recording_has_primary_artist_nr_participant.nr_sound_recording_id} ;;
  }

  join: featured_artist_participant {
    from: performance_nr_particpant
    view_label: "Featured Artist Participant"
    type: left_outer
    relationship: many_to_one
    sql_on: ${nr_sound_recording_has_featured_artist_nr_participant.nr_participant_id} = ${featured_artist_participant.id} ;;
  }

  join: primary_artist_participant {
    from: performance_nr_particpant
    view_label: "Primary Artist Participant"
    type: left_outer
    relationship: many_to_one
    sql_on: ${nr_sound_recording_has_primary_artist_nr_participant.nr_participant_id} = ${primary_artist_participant.id} ;;
  }
}