## Need the table distribution_format_media in order to create the genre_snowflake file
## view: distribution_format_media_snowflake {
##   sql_table_name: art_relations.distribution_format_media ;;
##
##   measure: count {
##     type: count
##     hidden: yes
##     drill_fields: [detail*]
##   }
##
##   dimension: distribution_format_media_id {
##     primary_key: yes
##     type: string
##     sql: ${TABLE}.distribution_format_media_id ;;
##   }
##
##   dimension: name {
##     label: "Format"
##     description: "(CD, LP, etc.)"
##     type: string
##     sql: ${TABLE}.name ;;
##   }
##
##   set: detail {
##     fields: [distribution_format_media_id, name]
##   }
## }
##

view: genre_snowflake {
  sql_table_name: art_relations.genre ;;



  dimension: transworld_genre_mapping_cd {
    view_label: "US Physical Custom Fields"
    sql:
    case
    when ${distribution_format_media_snowflake.distribution_format_media_id} in (7,11) and ${genre_id}=21 then '0050'
    when ${distribution_format_media_snowflake.distribution_format_media_id} in (7,11) and ${genre_id}=2 then '0011'
    when ${distribution_format_media_snowflake.distribution_format_media_id} in (7,11) and ${genre_id}=9 then '0080'
    when ${distribution_format_media_snowflake.distribution_format_media_id} in (7,11) and ${genre_id}=11 then '0190'
    when ${distribution_format_media_snowflake.distribution_format_media_id} in (7,11) and ${genre_id}=13 then '0160'
    when ${distribution_format_media_snowflake.distribution_format_media_id} in (7,11) and ${genre_id}=27 then '0102'
    when ${distribution_format_media_snowflake.distribution_format_media_id} in (7,11) and ${genre_id}=5 then '0060'
    when ${distribution_format_media_snowflake.distribution_format_media_id} in (7,11) and ${genre_id}=14 then '0102'
    when ${distribution_format_media_snowflake.distribution_format_media_id} in (7,11) and ${genre_id}=16 then '0040'
    when ${distribution_format_media_snowflake.distribution_format_media_id} in (7,11) and ${genre_id}=15 then '0200'
    when ${distribution_format_media_snowflake.distribution_format_media_id} in (7,11) and ${genre_id}=19 then '0180'
    when ${distribution_format_media_snowflake.distribution_format_media_id} in (7,11) and ${genre_id}=7 then '2830'
    when ${distribution_format_media_snowflake.distribution_format_media_id} in (7,11) and ${genre_id}=25 then '0030'
    when ${distribution_format_media_snowflake.distribution_format_media_id} in (7,11) and ${genre_id}=6 then '0031'
    when ${distribution_format_media_snowflake.distribution_format_media_id} in (7,11) and ${genre_id}=8 then '0070'
    when ${distribution_format_media_snowflake.distribution_format_media_id} in (7,11) and ${genre_id}=18 then '0250'
    when ${distribution_format_media_snowflake.distribution_format_media_id} in (7,11) and ${genre_id}=4 then '0018'
    when ${distribution_format_media_snowflake.distribution_format_media_id} in (7,11) and ${genre_id}=3 then '0013'
    when ${distribution_format_media_snowflake.distribution_format_media_id} in (7,11) and ${genre_id}=20 then '0070'
    when ${distribution_format_media_snowflake.distribution_format_media_id} in (7,11) and ${genre_id}=1 then '0012'
    when ${distribution_format_media_snowflake.distribution_format_media_id} in (7,11) and ${genre_id}=12 then '0020'
    else null
    end
    ;;
    type: string
  }


  dimension: transworld_genre_mapping_vinyl {
    view_label: "US Physical Custom Fields"
    sql:
       case
            when ${distribution_format_media_snowflake.distribution_format_media_id} in (1,2,3) and ${genre_id}=21 then '3050'
            when ${distribution_format_media_snowflake.distribution_format_media_id} in (1,2,3) and ${genre_id}=2 then '3011'
            when ${distribution_format_media_snowflake.distribution_format_media_id} in (1,2,3) and ${genre_id}=9 then '3080'
            when ${distribution_format_media_snowflake.distribution_format_media_id} in (1,2,3) and ${genre_id}=11 then '3190'
            when ${distribution_format_media_snowflake.distribution_format_media_id} in (1,2,3) and ${genre_id}=13 then '3180'
            when ${distribution_format_media_snowflake.distribution_format_media_id} in (1,2,3) and ${genre_id}=27 then '3102'
            when ${distribution_format_media_snowflake.distribution_format_media_id} in (1,2,3) and ${genre_id}=5 then '3060'
            when ${distribution_format_media_snowflake.distribution_format_media_id} in (1,2,3) and ${genre_id}=14 then '3190'
            when ${distribution_format_media_snowflake.distribution_format_media_id} in (1,2,3) and ${genre_id}=16 then '3040'
            when ${distribution_format_media_snowflake.distribution_format_media_id} in (1,2,3) and ${genre_id}=15 then '3310'
            when ${distribution_format_media_snowflake.distribution_format_media_id} in (1,2,3) and ${genre_id}=19 then '3180'
            when ${distribution_format_media_snowflake.distribution_format_media_id} in (1,2,3) and ${genre_id}=7 then '601'
            when ${distribution_format_media_snowflake.distribution_format_media_id} in (1,2,3) and ${genre_id}=25 then '3030'
            when ${distribution_format_media_snowflake.distribution_format_media_id} in (1,2,3) and ${genre_id}=6 then '3031'
            when ${distribution_format_media_snowflake.distribution_format_media_id} in (1,2,3) and ${genre_id}=8 then '3070'
            when ${distribution_format_media_snowflake.distribution_format_media_id} in (1,2,3) and ${genre_id}=18 then '3250'
            when ${distribution_format_media_snowflake.distribution_format_media_id} in (1,2,3) and ${genre_id}=4 then '3018'
            when ${distribution_format_media_snowflake.distribution_format_media_id} in (1,2,3) and ${genre_id}=3 then '3013'
            when ${distribution_format_media_snowflake.distribution_format_media_id} in (1,2,3) and ${genre_id}=20 then '3170'
            when ${distribution_format_media_snowflake.distribution_format_media_id} in (1,2,3) and ${genre_id}=1 then '3012'
            when ${distribution_format_media_snowflake.distribution_format_media_id} in (1,2,3) and ${genre_id}=12 then '3020'
        else null
        end
        ;;
    type: string
  }

  dimension: apple_genre_mapping {
    view_label: "Apple Custom Fields"
    case: {
      when: {
        sql: ${TABLE}.genre in ('Hip-hop/Rap');;
        label: "Hip Hop/Rap"
      }
      when : {
        sql: ${TABLE}.genre in ('R&B') ;;
        label: "R&B/Soul"
      }
      when : {
        sql: ${TABLE}.genre in ('Blues') ;;
        label: "Blues"
      }
      when : {
        sql: ${TABLE}.genre in ('Christian') ;;
        label: "Christian & Gospel"
      }
      when : {
        sql: ${TABLE}.genre in ('Country') ;;
        label: "Country"
      }
      when : {
        sql: ${TABLE}.genre in ('Holiday') ;;
        label: "Holiday"
      }
      when : {
        sql: ${TABLE}.genre in ('Jazz') ;;
        label: "Jazz"
      }
      when : {
        sql: ${TABLE}.subgenre in ('Indie Pop') ;;
        label: "Alternative"
      }
      when : {
        sql: ${TABLE}.genre in ('Soundtracks') ;;
        label: "Soundtrack"
      }
      when : {
        sql: ${TABLE}.genre in ('Reggae') ;;
        label: "Reggae"
      }
      when : {
        sql: ${TABLE}.genre in ('Christian') ;;
        label: "Christian & Gospel"
      }
      when : {
        sql: ${TABLE}.genre in ('Classical') ;;
        label: "Classical"
      }

      when : {
        sql: ${TABLE}.genre in ('World Music') ;;
        label: "World"
      }

      when : {
        sql: ${TABLE}.genre in ('Spoken Word') ;;
        label: "Comedy"
      }

      when : {
        sql: ${TABLE}.subgenre in ('Americana') ;;
        label: "Country"
      }

      when : {
        sql: ${TABLE}.subgenre in ('Singer/Songwriter') ;;
        label: "Singer/Songwriter"
      }

      when : {
        sql: ${TABLE}.subgenre in ('Singer/Songwriter') ;;
        label: "Singer/Songwriter"
      }

      when : {
        sql: ${TABLE}.genre in ('Electronic') ;;
        label: "Electronic"
      }

      when : {
        sql: ${TABLE}.genre in ('Latin Music') ;;
        label: "Latin"
      }

      when : {
        sql: ${TABLE}.genre in ('New Age') ;;
        label: "Rock"
      }

      when : {
        sql: ${TABLE}.genre in ('Punk') ;;
        label: "Hard Rock"
      }

      when : {
        sql: ${TABLE}.genre in ('Metal') ;;
        label: "Heavy Metal"
      }

      when : {
        sql: ${TABLE}.genre in ('Rock') ;;
        label: "Rock"
      }

      else: "Other"
    }
    type: string
  }

  dimension: genre_id {
    primary_key: yes
    type: number
    sql: ${TABLE}.genre_id ;;
  }

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

  dimension: genre_name_upper_case {
    view_label: "Custom Fields"
    label: "Genre"
    type: string
    sql: UPPER(${genre});;
  }

  dimension: hmv_dept {
    view_label: "Red Essential"
    case: {
      when: {
        sql: ${TABLE}.genre in ('Electronic','Metal','Pop','Hip-hop/Rap','Reggae','Punk','Rock','R&B') ;;
        label: "100"
      }
      when : {
        sql: ${TABLE}.genre in ('Classical');;
        label: "200"
      }
      when : {
        sql: ${TABLE}.genre in ('Blues','Children''s','Country','Folk','Jazz','Christian','Holiday','Rock','R&B','Soundtracks','Educational','Spoken Word');;
        label: "300"
      }
      when : {
        sql: ${TABLE}.genre in ('Latin Music','New Age','World Music') ;;
        label: "400"
      }
      else: "Other"
    }
    type: string
  }

  dimension: amazon_subject_1 {
    view_label: "Red Essential"
    case: {
      when: {
        sql: ${TABLE}.genre in ('Blues');;
        label: "Blues"
      }
      when : {
        sql: ${TABLE}.genre in ('Children''s');;
        label: "Children's Music"
      }
      when : {
        sql: ${TABLE}.genre in ('Holiday');;
        label: "Christmas Music"
      }
      when : {
        sql: ${TABLE}.genre in ('Country') ;;
        label: "Country"
      }
      when: {
        sql: ${TABLE}.genre in ('Electronic');;
        label: "Dance & Electronic"
      }
      when : {
        sql: ${TABLE}.genre in  ('Metal');;
        label: "Hark Rock & Metal"
      }
      when : {
        sql: ${TABLE}.genre in ('Hip-hop/Rap');;
        label: "Hip-Hop & Rap"
      }
      when : {
        sql: ${TABLE}.genre in ('Jazz') ;;
        label: "Jazz"
      }
      when: {
        sql: ${TABLE}.genre in ('Educational','Spoken Word') ;;
        label: "Miscellaneous"
      }
      when : {
        sql: ${TABLE}.genre in ('Pop','Punk');;
        label: "Pop"
      }
      when : {
        sql: ${TABLE}.genre in ('R&B');;
        label: "R&B and Soul"
      }
      when : {
        sql: ${TABLE}.genre in ('Reggae') ;;
        label: "Reggae"
      }
      when : {
        sql: ${TABLE}.genre in ('Punk','Rock');;
        label: "Rock"
      }
      when : {
        sql: ${TABLE}.genre in ('Soundtracks');;
        label: "Soundtracks"
      }
      when : {
        sql: ${TABLE}.genre in ('Folk','Latin Music','New Age','World Music') ;;
        label: "World & Folk"
      }
      else: "Other"
    }
    type: string
  }

  measure: count {
    type: count
    hidden: yes
    drill_fields: [detail*]
  }

  # ----- Sets of fields for drilling ------
  set: detail {
    fields: [
      genre_id,
      advance_release.count,
      comming_soon_release.count,
      dms_preferred_genre.count,
      pnl_sales_genre_detail.count,
      prospective_label.count,
      releases.count,
      release_tmp.count,
      subaccount.count,
      subgenre.count,
      subgenre_original.count
    ]
  }
}