view: dt_global_track_certifications_by_country {
  derived_table: {
    sql:
          WITH content_filter AS (
              SELECT
                  di.labelid,
                  di.isrcid,
                  di.isrc,
                  di.isrcname,
                  dr.releaseid,
                  dr.releasename,
                  dr.releasedate
              FROM facts.prod.dim_isrc di
                  INNER JOIN facts.prod.dim_release dr ON di.upc = dr.releaseid
              {% if certification_type._parameter_value == "'releaseid'" %} WHERE {% condition content_filter %} releaseid {% endcondition %}
              {% elsif certification_type._parameter_value == "'ISRC'" %} WHERE {% condition ISRC %} ISRC {% endcondition %}
              {% endif %}
          ),

      fact_sales AS (
      SELECT
      'Accounting' AS data_source,
      fs.releaseid,
      fs.isrcid,
      cf.isrcname as track_name,
      fs.activityyear,
      fs.activitymonth,
      fs.storeid,
      fs.transactiontypeid,
      fs.countryid,
      SUM(fs.sales) AS sum_units
      FROM facts.prod.fact_sales fs
      INNER JOIN content_filter cf ON fs.labelid = cf.labelid
      AND fs.releaseid = cf.releaseid
      AND fs.isrcid = cf.isrcid
      AND {% condition label_id %} fs.labelid {% endcondition %}
      AND fs.transactiontypeid in (1,10,19,23,48,49,17,9,38,32,37,31) --17 (Subscription Video Streams), 9 (Ad-Supported Video Streams), 38 (Ad-Enabled Video Streams), 32 (Ad-Disabled Video Streams), 37 (Ad-Enabled Audio Streams), 31 (Ad-Disabled Audio Streams)
      AND fs.storeid not in (1202, 1294) --excludes TikTok
      GROUP BY 1,2,3,4,5,6,7,8,9
      ),

      fact_analytics AS (
      SELECT
      'Analytics' AS data_source,
      fa.releaseid,
      fa.isrcid,
      cf.isrcname as track_name,
      YEAR(fa.download_activity_date) AS activityyear,
      MONTH(fa.download_activity_date) AS activitymonth,
      fa.storeid,
      fa.transactiontypeid,
      fa.countryid,
      SUM(fa.units) AS sum_units
      FROM facts.prod.fact_analytics fa
      INNER JOIN content_filter cf ON fa.labelid = cf.labelid
      AND fa.releaseid = cf.releaseid
      AND fa.isrcid = cf.isrcid
      AND {% condition label_id %} fa.labelid {% endcondition %}
      AND fa.transactiontypeid in (1,10,19,23,48,49,17,9,38,32,37,31) --17 (Subscription Video Streams), 9 (Ad-Supported Video Streams), 38 (Ad-Enabled Video Streams), 32 (Ad-Disabled Video Streams), 37 (Ad-Enabled Audio Streams), 31 (Ad-Disabled Audio Streams)
      AND fa.storeid not in (1202, 1294) --exclude TikTok
      GROUP BY 1,2,3,4,5,6,7,8,9
      ),

      unit_detail AS (
      SELECT
      COALESCE(fs.data_source,fa.data_source) AS data_source,
      COALESCE(fs.releaseid,fa.releaseid) AS releaseid,
      COALESCE(fs.isrcid,fa.isrcid) AS isrcid,
      COALESCE(fs.track_name,fa.track_name) AS track_name,
      COALESCE(fs.activityyear,fa.activityyear) AS activityyear,
      COALESCE(fs.activitymonth,fa.activitymonth) AS activitymonth,
      COALESCE(fs.storeid,fa.storeid) AS storeid,
      COALESCE(fs.transactiontypeid,fa.transactiontypeid) AS transactiontypeid,
      COALESCE(fs.sum_units,fa.sum_units) AS units,
      COALESCE(fs.countryid,fa.countryid) AS countryid
      FROM fact_sales fs FULL OUTER JOIN fact_analytics fa
      ON fs.releaseid = fa.releaseid
      AND fs.isrcid = fa.isrcid
      AND fs.activityyear = fa.activityyear
      AND fs.activitymonth = fa.activitymonth
      AND fs.storeid = fa.storeid
      AND fs.transactiontypeid = fa.transactiontypeid
      AND fs.countryid = fa.countryid
      ),

      -- Calulations for United States and Canada streaming and downloads
      us_ca_calc as (
      SELECT
      ud.track_name,
      case
      when ud.transactiontypeid in (1,31,17,32) then 'Subscription'
      when ud.transactiontypeid in (10,37,9,38) then 'Ad-Supported'
      when ud.transactiontypeid in (48) then 'Mid-Tier'
      when ud.transactiontypeid in (19) then 'Track Downloads'
      -- when ud.transactiontypeid in (23) then 'Album Downloads'
      end as transaction_type,
      dc.countryname,
      SUM(
        case
          when ud.transactiontypeid in (1,31,17,32) then (ud.units/150)
          when ud.transactiontypeid in (10,37,9,38) then (ud.units/150)
          when ud.transactiontypeid in (48) then (ud.units/150)
          when ud.transactiontypeid in (19) then ud.units
        end) AS certification_units
      FROM unit_detail ud
      INNER JOIN facts.prod.dim_store ds ON ud.storeid = ds.storeid
      INNER JOIN facts.prod.dim_transactiontype dtt ON ud.transactiontypeid = dtt.transactiontypeid
      INNER JOIN facts.prod.dim_country dc on dc.countryid = ud.countryid
      INNER JOIN content_filter cf ON ud.releaseid = cf.releaseid AND ud.isrcid = cf.isrcid
      where dc.countryname in ('USA','Canada')
      and ud.storeid IN (187,716,1,286,1433,526,348,548,535,502,1213,173,531,1512,120,4,2,708,384,743,214,365,553,1505,677,286,399,1290,453,312,592,1327,569,12,213,1093,339,650,616,568,446,3,496,184) --stored identified from https://whymusicmatters.com/ which was recommended by RIAA as a list.
      and not (ud.storeid = 708 AND ud.transactiontypeid = 10) --excludes pandora ad-supported
      and not (ud.storeid = 708 AND ud.transactiontypeid = 48) --excludes pandora mid-tier
      --and ud.transactiontypeid != 49 --no physical
      group by 1,2,3
      ),

      -- Calulations for Denmark
      dk_calc as (
      SELECT
      ud.track_name,
      case
      when ud.transactiontypeid in (1) then 'Subscription'
      when ud.transactiontypeid in (10) then 'Ad-Supported'
      when ud.transactiontypeid in (48) then 'Mid-Tier'
      when ud.transactiontypeid in (19) then 'Track Downloads'
      --when ud.transactiontypeid in (23) then 'Album Downloads'
      --when ud.transactiontypeid in (49) then 'Physical'
      end as transaction_type,
      dc.countryname,
      SUM(CASE ud.transactiontypeid
      WHEN 1 THEN (ud.units/1) --subscription audio streams
      WHEN 10 THEN (ud.units/1) --ad-supported audio streams
      WHEN 48 THEN (ud.units/1) --mid-tier audio streams
      WHEN 19 THEN (ud.units*100) --track downloads
      --WHEN 23 THEN (ud.units) --album downloads
      --WHEN 49 THEN (ud.units) --physical sales
      END) AS certification_units
      FROM unit_detail ud
      INNER JOIN facts.prod.dim_store ds ON ud.storeid = ds.storeid
      INNER JOIN facts.prod.dim_transactiontype dtt ON ud.transactiontypeid = dtt.transactiontypeid
      INNER JOIN facts.prod.dim_country dc on dc.countryid = ud.countryid
      INNER JOIN content_filter cf ON ud.releaseid = cf.releaseid AND ud.isrcid = cf.isrcid
      where dc.countryname in ('Denmark')
      and ud.transactiontypeid in (1,10,48,19) --,23,49
      --and ds.storeid in () --add specific stores
      GROUP BY 1,2,3
      ),

      no_calc as (
      SELECT
      ud.track_name,
      case
      when ud.transactiontypeid in (1) then 'Subscription'
      when ud.transactiontypeid in (10) then 'Ad-Supported'
      when ud.transactiontypeid in (48) then 'Mid-Tier'
      when ud.transactiontypeid in (19) then 'Track Downloads'
      --when ud.transactiontypeid in (23) then 'Album Downloads'
      --when ud.transactiontypeid in (49) then 'Physical'
      end as transaction_type,
      dc.countryname,
      SUM(CASE ud.transactiontypeid
      WHEN 1 THEN (ud.units/1) --subscription audio streams
      WHEN 10 THEN (ud.units/1) --ad-supported audio streams
      WHEN 48 THEN (ud.units/1) --mid-tier audio streams
      WHEN 19 THEN (ud.units*100) --track downloads (1 track download = 100 units)
      --WHEN 23 THEN (ud.units) --album downloads
      --WHEN 49 THEN (ud.units) --physical sales
      END) AS certification_units
      FROM unit_detail ud
      INNER JOIN facts.prod.dim_store ds ON ud.storeid = ds.storeid
      INNER JOIN facts.prod.dim_transactiontype dtt ON ud.transactiontypeid = dtt.transactiontypeid
      INNER JOIN facts.prod.dim_country dc on dc.countryid = ud.countryid
      INNER JOIN content_filter cf ON ud.releaseid = cf.releaseid AND ud.isrcid = cf.isrcid
      where dc.countryname in ('Norway')
      and ds.storename in ('Spotify', 'TIDAL', 'iTunes/Apple') and ud.transactiontypeid in (1,10,48,19) --YouTube streams are not included at this time (8-13-2023)
      GROUP BY 1,2,3
      ),

      -- Australia calculations
      aus_calc AS (
      SELECT
      ud.track_name as track_name,
      case
      when ud.transactiontypeid in (1,31,17,32) then 'Subscription'
      when ud.transactiontypeid in (10,37,9,38) then 'Ad-Supported'
      when ud.transactiontypeid in (19) then 'Track Downloads'
      end as transaction_type,
      dc.countryname,
      SUM(
        case
          when ud.transactiontypeid in (1,31,17,32) then (ud.units/170) --this needs to be confirmed
          when ud.transactiontypeid in (10,37,9,38) then (ud.units/420) --this needs to be confirmed
          when ud.transactiontypeid in (19) then ud.units
        end) AS certification_units
      FROM unit_detail ud
      INNER JOIN facts.prod.dim_store ds ON ud.storeid = ds.storeid
      INNER JOIN facts.prod.dim_transactiontype dtt ON ud.transactiontypeid = dtt.transactiontypeid
      INNER JOIN facts.prod.dim_country dc on dc.countryid = ud.countryid
      INNER JOIN content_filter cf ON ud.releaseid = cf.releaseid AND ud.isrcid = cf.isrcid
      where dc.countryname in ('Australia')
      and ud.transactiontypeid in (1,10,31,17,32,37,9,38,19) --subscription and ad-supported (includes YT and Video transactions types)
      and ds.storename in ('Spotify','iTunes/Apple','YouTube','YouTube Subscription','Deezer') --specific stores
      group by 1,2,3
      ),

      uk_calc AS (
      SELECT
      ud.track_name as track_name,
      case
      when ud.transactiontypeid in (1,31,17,32) then 'Subscription'
      when ud.transactiontypeid in (10,37,9,38) then 'Ad-Supported'
      when ud.transactiontypeid in (19) then 'Track Downloads'
      end as transaction_type,
      dc.countryname,
      SUM(
        case
          when ud.transactiontypeid in (1,31,17,32) then (ud.units/100) --per OCC chart rules
          when ud.transactiontypeid in (10,37,9,38) then (ud.units/600) --per OCC chart rules
          when ud.transactiontypeid in (19) then ud.units
        end) AS certification_units
      FROM unit_detail ud
      INNER JOIN facts.prod.dim_store ds ON ud.storeid = ds.storeid
      INNER JOIN facts.prod.dim_transactiontype dtt ON ud.transactiontypeid = dtt.transactiontypeid
      INNER JOIN facts.prod.dim_country dc on dc.countryid = ud.countryid
      INNER JOIN content_filter cf ON ud.releaseid = cf.releaseid AND ud.isrcid = cf.isrcid
      where dc.countryname in ('United Kingdom')
      and ud.transactiontypeid in (1,10,31,17,32,37,9,38,19) --subscription and ad-supported only based on tricia's doc
      --and ds.storename in ('Spotify','iTunes/Apple','YouTube','YouTube Subscription','Deezer') --removed until specific stores are identified
      group by 1,2,3
      )



      select
      countryname,
      track_name,
      transaction_type,
      round(certification_units,0) as certification_units
      from us_ca_calc

      union all

      select
      countryname,
      track_name,
      transaction_type,
      round(certification_units,0) as certification_units
      from dk_calc

      union all

      select
      countryname,
      track_name,
      transaction_type,
      round(certification_units,0) as certification_units
      from no_calc

      union all

      select
      countryname,
      track_name,
      transaction_type,
      round(certification_units,0) as certification_units
      from aus_calc

      union all

      select
      countryname,
      track_name,
      transaction_type,
      round(certification_units,0) as certification_units
      from uk_calc

      ;;
  }

  parameter: certification_type {
    type: string
    #allowed_value: { label: "Album" value: "releaseid" }
    allowed_value: { label: "Track" value: "ISRC" }
  }

  filter: ISRC {
    label: "ISRC"
    type: string
  }

  # dimension: data_source {
  #   type: string
  #   sql: ${TABLE}.data_source ;;
  # }

  # dimension: upc {
  #   type: string
  #   sql: ${TABLE}.upc ;;
  #   label: "UPC"
  # }

  # dimension: release_name {
  #   type: string
  #   sql: ${TABLE}.releasename ;;
  # }

  # dimension: release_date {
  #   type: date
  #   sql: ${TABLE}.releasedate ;;
  # }

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

  dimension: track_name{
    type: string
    sql: ${TABLE}.track_name ;;
    label: "Track Name"
  }

  # dimension: track_name {
  #   type: string
  #   sql: ${TABLE}.isrcname ;;
  # }

  # dimension: activity_period {
  #   type: string
  #   sql: ${TABLE}.activity_period ;;
  # }

  # dimension: store {
  #   type: string
  #   sql: ${TABLE}.storename ;;
  # }

  dimension: transaction_type {
    type: string
    sql: ${TABLE}.transaction_type ;;
  }

  dimension: country_name {
    type: string
    sql: ${TABLE}.countryname ;;
  }

  # measure: units {
  #   type: sum
  #   sql: ${TABLE}.units ;;
  # }

  measure: certification_units {
    type: sum
    sql: ${TABLE}.certification_units ;;
    value_format: "#,##0"
  }

  filter: label_id {
    type: number
  }

}