connection: "reportsar_compilations"

include: "*.view"

# Currently supports two reports: https://theorchard.looker.com/looks/12000 and https://theorchard.looker.com/looks/8022
# Talk to Nick Northrup / Zach Allard about deprecating them
explore: tcat_data {
  hidden: yes
}

explore: vendor {
  label: "Label Level Report"
  hidden: yes

  join: artist_info {
    sql_on: ${artist_info.vendor_id}=${vendor.vendor_id};;
    relationship: many_to_one
  }

  join: country {
    sql_on: ${country.id}=${artist_info.orchard_country} ;;
    relationship: many_to_one
    view_label: "Artist Country"
  }

  join: vendor_country {
    sql_on: ${vendor.country}=${vendor_country.id} ;;
    relationship: one_to_one
  }

  join: ioda_rightsholder_mapping {
    sql_on: ${vendor.vendor_id}=${ioda_rightsholder_mapping.orchard_vendor_id} ;;
    relationship: many_to_one
  }

  join: vendor_primary_genre {
    sql_on: ${vendor_primary_genre.genre_id}=${vendor.primary_genre} ;;
    relationship: one_to_one
  }

  join: vw_active_vendor_contract {
    sql_on: ${vendor.vendor_id}=${vw_active_vendor_contract.vendor_id} ;;
    relationship: one_to_one
  }

  join: vendor_contract {
    sql_on: ${vw_active_vendor_contract.vendor_contract_id}=${vendor_contract.id} ;;
    relationship: one_to_one
  }

  join: vend_contact {
    type: inner
    sql_on: ${vendor.vendor_id}=${vend_contact.vendor_id} AND ${vend_contact.subaccount_id} is NULL ;;
    relationship: many_to_one
  }

  join: contact {
    sql_on: ${vend_contact.contact_id}=${contact.contact_id} ;;
    relationship: many_to_one
  }

  join: orchard_state {
    sql_on: ${orchard_state.id}=${contact.address_state} ;;
    relationship: many_to_one
  }

  join: youtube_channel {
    sql_on: ${vendor.vendor_id}=${youtube_channel.vendor_id} ;;
    relationship: many_to_one
  }

  join: api_product_label_installations {
    sql_on: ${vendor.vendor_id}=${api_product_label_installations.label_id} ;;
    relationship: many_to_one
  }

  join: youtube_channel_service_tier_history {
    sql_on: ${youtube_channel_service_tier_history.youtube_channel_id}=${youtube_channel.id} ;;
    relationship: many_to_one
  }

  join: orchadmin_users {
    sql_on: ${vendor.assigned_to}=${orchadmin_users.id} ;;
    relationship: many_to_one
  }

  join: orchadmin_closer {
    sql_on: ${vendor_contract.orchrep_name}=${orchadmin_closer.id} ;;
    relationship: many_to_one
  }
}

explore: releases {
  label: "Release Level Report"
  hidden: yes

  join: artist_info {
    sql_on: ${releases.artist_id}=${artist_info.artist_id} ;;
    relationship: many_to_one
  }

  join: distribution_format {
    sql_on: ${distribution_format.distribution_format_id}=${releases.distribution_format_id} ;;
    relationship: one_to_one
  }

  join: distribution_format_media {
    sql_on: ${distribution_format.distribution_format_media_id}=${distribution_format_media.distribution_format_media_id} ;;
    relationship: one_to_one
  }

  join: country {
    sql_on: ${artist_info.orchard_country}=${country.id} ;;
    relationship: one_to_one
  }

  join: vendor {
    sql_on: ${vendor.vendor_id}=${artist_info.vendor_id} ;;
    relationship: many_to_one
  }

  join: subaccount {
    sql_on: ${releases.subaccount_id}=${subaccount.subaccount_id} ;;
    relationship: many_to_one
  }

  join: vendor_country {
    sql_on: ${vendor.country}=${vendor_country.id} ;;
    relationship: one_to_one
  }

  join: ioda_rightsholder_mapping {
    sql_on: ${vendor.vendor_id}=${ioda_rightsholder_mapping.orchard_vendor_id} ;;
    relationship: many_to_one
  }

  join: vendor_primary_genre {
    sql_on: ${vendor_primary_genre.genre_id}=${vendor.primary_genre} ;;
    relationship: one_to_one
  }

  join: vw_active_vendor_contract {
    sql_on: ${vendor.vendor_id}=${vw_active_vendor_contract.vendor_id} ;;
    relationship: one_to_one
  }

  join: vendor_contract {
    sql_on: ${vw_active_vendor_contract.vendor_contract_id}=${vendor_contract.id} ;;
    relationship: one_to_one
  }

  join: vend_contact {
    type: inner
    sql_on: ${vendor.vendor_id}=${vend_contact.vendor_id} AND ${vend_contact.subaccount_id} is NULL ;;
    relationship: many_to_one
  }

  join: contact {
    sql_on: ${vend_contact.contact_id}=${contact.contact_id} ;;
    relationship: many_to_one
  }

  join: orchard_state {
    sql_on: ${orchard_state.id}=${contact.address_state} ;;
    relationship: many_to_one
  }

  join: youtube_channel {
    sql_on: ${vendor.vendor_id}=${youtube_channel.vendor_id} ;;
    relationship: many_to_one
  }

  join: api_product_label_installations {
    sql_on: ${vendor.vendor_id}=${api_product_label_installations.label_id} ;;
    relationship: many_to_one
  }

  join: youtube_channel_service_tier_history {
    sql_on: ${youtube_channel_service_tier_history.youtube_channel_id}=${youtube_channel.id} ;;
    relationship: many_to_one
  }

  join: product_type_art_relations {
    sql_on: ${releases.product_type_id}= ${product_type_art_relations.id} ;;
    relationship: one_to_one
  }

  join: mkt_priority {
    sql_on: ${mkt_priority.release_id}=${releases.release_id} AND ${mkt_priority.id} is not null ;;
    relationship: many_to_one
  }

  join: mkt_program_info {
    sql_on: ${mkt_program_info.info_for_id}=${releases.release_id} AND ${mkt_program_info.info_for} ='release' ;;
    relationship: many_to_one
  }

  join: mkt_program {
    sql_on: ${mkt_program_info.mkt_program_id}=${mkt_program.mkt_program_id} ;;
    relationship: many_to_one
  }

  join: release_subgenre {
    sql_on: ${release_subgenre.upc}=${releases.upc_join} ;;
    relationship: one_to_one
  }

  join: subgenre {
    sql_on: ${subgenre.orchard_id}=${release_subgenre.subgenre_id} ;;
    relationship: one_to_one
  }

  join: product_physical {
    sql_on: ${product_physical.release_id}=${releases.release_id} ;;
    relationship: one_to_one
  }

  join: product_physical_packaging {
    sql_on: ${product_physical.packaging_id}=${product_physical_packaging.id} ;;
    relationship: one_to_one
  }

  join: physical_order_targets {
    sql_on: ${physical_order_targets.product_id}=${product_physical.id} ;;
    relationship: one_to_many
  }

  join: product_physical_change_history {
    sql_on: ${releases.release_id}=${product_physical_change_history.product_id} ;;
    relationship: one_to_many
  }

  join: genre {
    sql_on: ${genre.genre_id}=${releases.genre_id} ;;
    relationship: one_to_one
  }

  join: proper_stock_essn {
    sql_on: ${releases.upc_join}=${proper_stock_essn.ean} ;;
    relationship: one_to_one
  }

  join: track {
    sql_on: ${track.upc}=${releases.upc_join} ;;
    relationship: many_to_one
  }

  join: orchadmin_users {
    sql_on: ${vendor.assigned_to}=${orchadmin_users.id} ;;
    relationship: many_to_one
  }

  join: orchadmin_closer {
    sql_on: ${vendor_contract.orchrep_name}=${orchadmin_closer.id} ;;
    relationship: many_to_one
  }

  join: release_deliverable_to_itunes {
    sql_on: ${release_deliverable_to_itunes.upc}=${releases.upc_join} ;;
    relationship: one_to_one
  }

  join: release_territory_restriction {
    sql_on: ${releases.release_id}=${release_territory_restriction.release_id} ;;
    relationship: many_to_one
  }

  join: release_territory_restriction_country {
    from: country
    sql_on: ${release_territory_restriction.country_id}=${release_territory_restriction_country.id} ;;
    relationship: many_to_one
  }

  join: vendor_territory_restriction {
    sql_on: ${vw_active_vendor_contract.vendor_contract_id}=${vendor_territory_restriction.vendor_contract_id} ;;
    relationship: many_to_one
  }

  join: releases_log {
    sql_on: ${releases_log.release_id}=${releases.release_id} ;;
    relationship: one_to_many
  }
  join: release_grid {
    sql_on: ${release_grid.release_id}=${releases.release_id} ;;
    relationship: one_to_one
  }

  join: red_essential_release_level_pricing {
    sql_on: ${red_essential_release_level_pricing.upc}=${releases.upc_join} ;;
    relationship: one_to_one
  }

  join: track_artist_reportsar {
    sql_on: ${track_artist_reportsar.isrc}=${track.isrc} and ${track_artist_reportsar.upc}=${track.upc} ;;
    relationship: many_to_one
  }

  join: delivery_history {
    sql_on: ${releases.upc}= ${delivery_history.upc} ;;
    relationship: one_to_many
  }

  join: customer_master_master_snowflake_2 {
    sql_on: ${delivery_history.customer_master_master_id} = ${customer_master_master_snowflake_2.customer_master_master_id} ;;

    fields: [customer_master_master_snowflake_2.customer_name]
  }

  join: release_artist {
    sql_on: ${releases.upc}= ${release_artist.upc} ;;
    relationship: many_to_many
  }
}

explore: track {
  label: "Track Level Report"
  hidden: yes

  join: track_master_rights {
    sql_on: ${track.id}=${track_master_rights.track_id} ;;
    relationship: many_to_one
  }

  join: track_producer_nationality {
    view_label: "Producer Nationality"
    sql_on: ${track.id}=${track_producer_nationality.track_id} ;;
    relationship: one_to_one
  }

  join: delivery_history {
    sql_on: ${track.upc}= ${delivery_history.upc} ;;
    relationship: one_to_many
  }

  join: country {
    view_label: "Producer Nationality"
    sql_on: ${track_producer_nationality.nationality_country_id}=${country.id} ;;
    relationship: many_to_one
  }

  join: releases {
    sql_on: ${releases.upc_join}=${track.upc} ;;
    relationship: many_to_one
  }

  join: product_type_art_relations {
    view_label: "Product Type"
    sql_on: ${releases.product_type_id}= ${product_type_art_relations.id} ;;
    relationship: one_to_one
  }

  join: distribution_format {
    sql_on: ${distribution_format.distribution_format_id}=${releases.distribution_format_id} ;;
    relationship: one_to_one
  }

  join: artist_info {
    sql_on: ${releases.artist_id}=${artist_info.artist_id} ;;
    relationship: many_to_one
  }

  join: vendor {
    sql_on: ${vendor.vendor_id}=${artist_info.vendor_id} ;;
    relationship: many_to_one
  }

  join: vw_active_vendor_contract {
    sql_on: ${vendor.vendor_id}=${vw_active_vendor_contract.vendor_id} ;;
    relationship: one_to_one
  }

  join: vendor_contract {
    sql_on: ${vw_active_vendor_contract.vendor_contract_id}=${vendor_contract.id} ;;
    relationship: one_to_one
  }

  join: release_subgenre {
    sql_on: ${release_subgenre.upc}=${releases.upc_join} ;;
    relationship: one_to_one
  }

  join: subgenre {
    sql_on: ${subgenre.orchard_id}=${release_subgenre.subgenre_id} ;;
    relationship: one_to_one
  }

  join: product_physical {
    sql_on: ${product_physical.release_id}=${releases.release_id} ;;
    relationship: one_to_one
  }

  join: physical_order_targets {
    sql_on: ${physical_order_targets.product_id}=${product_physical.id} ;;
    relationship: one_to_many
  }

  join: product_physical_change_history {
    sql_on: ${releases.release_id}=${product_physical_change_history.product_id} ;;
    relationship: one_to_many
  }

  join: genre {
    sql_on: ${genre.genre_id}=${releases.genre_id} ;;
    relationship: one_to_one
  }

  join: proper_stock_essn {
    sql_on: ${releases.upc_join}=${proper_stock_essn.ean} ;;
    relationship: one_to_one
  }

  join: orchadmin_users {
    sql_on: ${vendor.assigned_to}=${orchadmin_users.id} ;;
    relationship: many_to_one
  }

  join: release_deliverable_to_itunes {
    sql_on: ${release_deliverable_to_itunes.upc}=${releases.upc_join} ;;
    relationship: one_to_one
  }

  join: release_deliverable_to_spotify {
    sql_on: ${release_deliverable_to_spotify.upc}=${releases.upc_join} ;;
    relationship: one_to_one
  }

  join: red_essential_release_level_pricing {
    sql_on: ${red_essential_release_level_pricing.upc}=${releases.upc_join} ;;
    relationship: one_to_one
  }

  join: track_artist_reportsar {
    sql_on: ${track_artist_reportsar.isrc}=${track.isrc} and ${track_artist_reportsar.upc}=${track.upc} ;;
    relationship: many_to_one
  }

  join: release_artist {
    sql_on: ${releases.upc}= ${release_artist.upc} ;;
    relationship: many_to_many
  }

  join: track_writer {
    sql_on: ${track.id}=${track_writer.unique_track_id} and ${track.upc}=${track_writer.upc} ;;
    relationship: many_to_one
  }

  join: track_publisher {
    sql_on: ${track.id}=${track_publisher.unique_track_id} and ${track.upc}=${track_publisher.upc} ;;
    relationship: many_to_one
  }

  join: track_artist_derived_table {
    sql_on: ${track_artist_derived_table.isrc}=${track.isrc} and ${track_artist_derived_table.upc}=${track.upc} ;;
    relationship: many_to_one
  }

  join: release_territory_restriction {
    sql_on: ${releases.upc}=${release_territory_restriction.upc} ;;
    relationship: many_to_one
  }

  join: release_territory_restriction_country {
    from: country
    sql_on: ${release_territory_restriction.country_id}=${country.id} ;;
    relationship: many_to_one
  }

  join: vendor_territory_restriction {
    sql_on: ${releases.upc}=${vendor_territory_restriction.vendor_contract_id} ;;
    relationship: many_to_one
  }

  join: vendor_territory_restriction_country {
    from: country
    sql_on: ${vendor_territory_restriction.vendor_contract_id}=${vw_active_vendor_contract.vendor_contract_id} ;;
    relationship: many_to_one
  }

  join: track_artist {
    sql_on: ${track.id}=${track_artist.track_id} ;;
    relationship: many_to_many
  }
}