view: dt_capif_certification {
  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 == "'isrcid'" %} WHERE {% condition content_filter %} isrc {% endcondition %}
        {% endif %}
    ),
    fact_sales AS (
        SELECT
            'Accounting' AS data_source,
            fs.releaseid,
            fs.isrcid,
            fs.activityyear,
            fs.activitymonth,
            fs.storeid,
            fs.transactiontypeid,
            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
        WHERE countryid = 23
            AND {% condition label_id %} fs.labelid {% endcondition %}
            --AND transactiontypeid IN {% if certification_type._parameter_value == "'releaseid'" %}(1,10,19,23,48){% else %}(1,10,19,48){% endif %}
            --AND storeid IN (1, 4, 36, 120, 136, 173, 187, 194, 213, 217, 286, 339, 348, 365, 399, 426, 446, 453, 496, 497, 504, 526, 531, 548, 553, 569, 580, 595, 616, 677, 708, 719, 1089, 1093, 1290)
        GROUP BY 1,2,3,4,5,6,7
    ),
    fact_analytics AS (
        SELECT
            'Analytics' AS data_source,
            fa.releaseid,
            fa.isrcid,
            YEAR(fa.download_activity_date) AS activityyear,
            MONTH(fa.download_activity_date) AS activitymonth,
            fa.storeid,
            fa.transactiontypeid,
            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
        WHERE countryid = 23
            AND {% condition label_id %} fa.labelid {% endcondition %}
            --AND transactiontypeid IN {% if certification_type._parameter_value == "'releaseid'" %}(1,10,19,23,48){% else %}(1,10,19,48){% endif %}
            --AND storeid IN (1, 4, 36, 120, 136, 173, 187, 194, 213, 217, 286, 339, 348, 365, 399, 426, 446, 453, 496, 497, 504, 526, 531, 548, 553, 569, 580, 595, 616, 677, 708, 719, 1089, 1093, 1290)
        GROUP BY 1,2,3,4,5,6,7
    ),
    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.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
        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
    )
    SELECT
        ud.data_source,
        ud.releaseid AS upc,
        cf.releasename,
        cf.releasedate,
        ud.isrcid,
        cf.isrc,
        cf.isrcname,
        cf.labelid,
        ud.activityyear||'-'||LPAD(ud.activitymonth,2,'0') AS activity_period,
        ds.storename,
        dtt.transactiontypedesc,
        SUM(ud.units) AS units,
      {% if certification_type._parameter_value == "'releaseid'" %}
        SUM(CASE
            WHEN ud.transactiontypeid IN (1, 10, 48) AND ud.storeid IN (453, 569) THEN (ud.units / 200)
            WHEN ud.transactiontypeid IN (1, 10, 48) THEN (ud.units / 200)
            WHEN ud.transactiontypeid = 23 THEN (ud.units * 10)
            WHEN ud.storeid IN (453, 569) THEN (ud.units / 1000)
            ELSE ud.units
        END) AS certification_units
    {% elsif certification_type._parameter_value == "'isrcid'" %}
        SUM(CASE
            WHEN ud.transactiontypeid IN (1, 10, 48) AND ud.storeid IN (453, 569) THEN (ud.units / 200)
            WHEN ud.transactiontypeid IN (1, 10, 48) THEN (ud.units / 200)
            WHEN ud.storeid IN (453, 569) THEN (ud.units / 1000)
            ELSE ud.units
        END) AS certification_units
    {% endif %}
    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 content_filter cf ON ud.releaseid = cf.releaseid AND ud.isrcid = cf.isrcid
    GROUP BY 1,2,3,4,5,6,7,8,9,10,11
    ORDER BY activity_period DESC, certification_units DESC  ;;
  }

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

  filter: content_filter {
    label: "Content Filter"
    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: isrc {
    type: string
    sql: ${TABLE}.isrc ;;
    label: "ISRC"
  }

  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}.transactiontypedesc ;;
  }

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

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

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

  filter: label_id {
    type: number
  }

}