view: dt_flatten_marketing_info {
  sql_table_name: INTELLIGENCE.DBT_PROD.DT_FLATTEN_MARKETING_INFO ;;

# PRIMARY KEY
  dimension: release_id {
    type: number
    sql: ${TABLE}.release_id ;;
    primary_key: yes
    hidden: yes
  }

  dimension: mktg_blurb {
    label: "Marketing Blurb"
    type: string
    sql: ${TABLE}.mktg_blurb ;;
    html:
    {% assign blurb = value %}
    {% unless blurb.size == 0 %}

    <details>
    <summary>Expand</summary>
    {{blurb}}
    </details>

    {% endunless %}
    ;;
  }

  dimension: global_mktg_highlights {
    label: "Global Highlights"
    type: string
    sql: ${TABLE}.global_mktg_highlights ;;
    html:
    {% assign highlights = value %}
    {% unless highlights.size == 0 %}

    <details>
    <summary>Expand</summary>
    {{highlights}}
    </details>

    {% endunless %}
    ;;
  }

  dimension: local_mktg_highlights {
    label: "Local Highlights"
    type: string
    sql: ${TABLE}.local_mktg_highlights ;;
    html:
    {% assign countries = value | split: "||" %}
    {% unless countries.size == 0 %}

      <details>
      <summary>Expand ({{countries.size}})</summary>

      <ul>
      {% for country in countries %}
      <li>{{country}}</li>
      {% endfor %}
      </ul>
      </details>

    {% endunless %}
    ;;
  }

  dimension: store_specific_highlights {
    label: "Store-Specific Highlights"
    type: string
    sql: ${TABLE}.store_specific_highlights ;;
    html:
    {% assign stores = value | split: "||" %}
    {% unless stores.size == 0 %}

      <details>
      <summary>Expand ({{stores.size}})</summary>

      <ul>
      {% for store in stores %}
      <li>{{store}}</li>
      {% endfor %}
      </ul>
      </details>

    {% endunless %}
    ;;
    }

  dimension: sync_highlights {
    label: "Sync Highlights"
    type: string
    sql: ${TABLE}.sync_highlights ;;
    html:
    {% assign highlights = value %}
    {% unless highlights.size == 0 %}

    <details>
    <summary>Expand</summary>
    {{highlights}}
    </details>

    {% endunless %}
    ;;
  }

  dimension: internal_note {
    type: string
    sql: ${TABLE}.internal_note ;;
    html:
    {% assign internal_note = value %}
    {% unless internal_note.size == 0 %}

    <details>
    <summary>Expand</summary>
    {{internal_note}}
    </details>

    {% endunless %}
    ;;
  }

  dimension: promo_link {
    type: string
    sql: ${TABLE}.promo_link ;;
    html:
    {% assign link = value %}
    <a href="{{link}}"><u>Promo Link</u></a>
    ;;
  }

  dimension: global_highlights_check {
    label: "Global Highlights Check"
    type: string
    hidden: yes
    sql: iff(length(${TABLE}.global_mktg_highlights) = 0, 'Needed','Have') ;;

  }

  dimension: focus_track_check {
    label: "Focus Track Check"
    type: string
    hidden: yes
    sql: CASE
            WHEN (contains(upper(${TABLE}.global_mktg_highlights),upper('Focus Track: Blank')) AND orch_app_ar_releases.format != 'Single') THEN 'Needed'
            WHEN orch_app_ar_releases.format = 'Single' THEN 'Have'
            ELSE 'Have'
            END ;;
  }

  dimension: focus_track {
    label: "Focus Track"
    type: string
    hidden: yes
    sql: CASE
            WHEN (CONTAINS(UPPER(${TABLE}.global_mktg_highlights),UPPER('Focus Track:')) AND orch_app_ar_releases.format != 'Single') THEN SUBSTR(LEFT(SUBSTR(${TABLE}.global_mktg_highlights, POSITION('Focus Track:',${TABLE}.global_mktg_highlights)),POSITION('Focus Track Mood:',${TABLE}.global_mktg_highlights)-POSITION('Focus Track:',${TABLE}.global_mktg_highlights)),13)
            WHEN orch_app_ar_releases.format = 'Single' THEN 'N/A'
            ELSE 'Needed'
            END ;;
  }

  dimension: focus_track_mood_check {
    label: "Focus Track Mood Check"
    type: string
    hidden: yes
    sql: CASE
            WHEN (contains(upper(${TABLE}.global_mktg_highlights),upper('Focus Track Mood: Blank')) AND orch_app_ar_releases.format != 'Single') THEN 'Needed'
            WHEN orch_app_ar_releases.format = 'Single' THEN 'Have'
            ELSE 'Have'
            END;;
  }

  dimension: focus_track_mood {
    label: "Focus Track Mood"
    type: string
    hidden: yes
    sql: CASE
            WHEN (CONTAINS(UPPER(${TABLE}.global_mktg_highlights),UPPER('Focus Track Mood:')) AND orch_app_ar_releases.format != 'Single') THEN SUBSTR(LEFT(SUBSTR(${TABLE}.global_mktg_highlights, POSITION('Focus Track Mood:',${TABLE}.global_mktg_highlights)),POSITION('Hometown:',${TABLE}.global_mktg_highlights)-POSITION('Focus Track Mood:',${TABLE}.global_mktg_highlights)),19)
            WHEN orch_app_ar_releases.format = 'Single' THEN 'N/A'
            ELSE 'Needed'
            END ;;
  }

  dimension: hometown_check {
    label: "Hometown Check"
    type: string
    hidden: yes
    sql: CASE
            WHEN contains(upper(${TABLE}.global_mktg_highlights),upper('Hometown: Blank')) THEN 'Needed'
            ELSE 'Have'
            END;;
  }

  dimension: hometown{
    label: "Hometown"
    type: string
    hidden: yes
    sql: CASE
            WHEN CONTAINS(UPPER(${TABLE}.global_mktg_highlights),UPPER('Hometown:')) THEN SUBSTR(LEFT(SUBSTR(${TABLE}.global_mktg_highlights, POSITION('Hometown:',${TABLE}.global_mktg_highlights)),POSITION('Currently Based:',global_mktg_highlights)-POSITION('Hometown:',${TABLE}.global_mktg_highlights)),11)
            ELSE 'Needed'
            END ;;
  }

  dimension: currently_based_check {
    label: "Currently Based Check"
    type: string
    hidden: yes
    sql: CASE
            WHEN contains(upper(${TABLE}.global_mktg_highlights),upper('Currently Based: Blank')) THEN 'Needed'
            ELSE 'Have'
            END;;
  }

  dimension: currently_based{
    label: "Currently Based"
    type: string
    hidden: yes
    sql: CASE
            WHEN CONTAINS(UPPER(${TABLE}.global_mktg_highlights),UPPER('Currently Based:')) THEN SUBSTR(LEFT(SUBSTR(${TABLE}.global_mktg_highlights, POSITION('Currently Based:',${TABLE}.global_mktg_highlights)),POSITION('Fans Also Like:',${TABLE}.global_mktg_highlights)-POSITION('Currently Based:',${TABLE}.global_mktg_highlights)),18)
            ELSE 'Needed'
            END ;;
  }

  dimension: fans_also_like_check {
    label: "Fans Also Like Check"
    type: string
    hidden: yes
    sql: CASE
            WHEN contains(upper(${TABLE}.global_mktg_highlights),upper('Fans Also Like: Blank')) THEN 'Needed'
            ELSE 'Have'
            END;;
  }

  dimension: fans_also_like{
    label: "Fans Also Like"
    type: string
    hidden: yes
    sql: CASE
            WHEN CONTAINS(UPPER(${TABLE}.global_mktg_highlights),UPPER('Fans Also Like:')) THEN SUBSTR(LEFT(SUBSTR(${TABLE}.global_mktg_highlights, POSITION('Fans Also Like:',${TABLE}.global_mktg_highlights)),POSITION('Press Photo Link:',${TABLE}.global_mktg_highlights)-POSITION('Fans Also Like:',${TABLE}.global_mktg_highlights)),17)
            ELSE 'Needed'
            END ;;
  }

  dimension: press_photo_link_check {
    label: "Press Photo Link Check"
    type: string
    hidden: yes
    sql: CASE
            WHEN contains(upper(${TABLE}.global_mktg_highlights),upper('Press Photo Link: Blank)')) THEN 'Needed'
            ELSE 'Have'
            END;;
  }

  dimension: press_photo_link{
    label: "Press Photo Link"
    type: string
    hidden: yes
    sql: CASE
            WHEN CONTAINS(UPPER(${TABLE}.global_mktg_highlights),UPPER('Press Photo Link:')) THEN SUBSTR(LEFT(SUBSTR(${TABLE}.global_mktg_highlights, POSITION('Press Photo Link:',${TABLE}.global_mktg_highlights)),POSITION('Release Timeline:',${TABLE}.global_mktg_highlights)-POSITION('Press Photo Link:',${TABLE}.global_mktg_highlights)),19)
            ELSE 'Needed'
            END ;;
  }

  dimension: release_timeline_check {
    label: "Release Timeline Check"
    type: string
    hidden: yes
    sql: CASE
            WHEN contains(upper(${TABLE}.global_mktg_highlights),upper('Release Timeline: Blank')) THEN 'Needed'
            ELSE 'Have'
            END;;
  }

  dimension: release_timeline{
    label: "Release Timeline"
    type: string
    hidden: yes
    sql: CASE
            WHEN CONTAINS(UPPER(${TABLE}.global_mktg_highlights),UPPER('Release Timeline:')) THEN SUBSTR(LEFT(SUBSTR(${TABLE}.global_mktg_highlights, POSITION('Release Timeline:',${TABLE}.global_mktg_highlights)),POSITION('Artist Quotes:',${TABLE}.global_mktg_highlights)-POSITION('Release Timeline:',${TABLE}.global_mktg_highlights)),20)
            ELSE 'Needed'
            END ;;
  }

  dimension: artist_quotes_check {
    label: "Artist Quotes Check"
    type: string
    hidden: yes
    sql: CASE
            WHEN contains(upper(${TABLE}.global_mktg_highlights),upper('Artist Quotes: Blank')) THEN 'Needed'
            ELSE 'Have'
            End;;
  }

  dimension: artist_quotes{
    label: "Artist Quotes"
    type: string
    hidden: yes
    sql: CASE
            WHEN CONTAINS(UPPER(${TABLE}.global_mktg_highlights),UPPER('Artist Quotes:')) THEN SUBSTR(LEFT(SUBSTR(${TABLE}.global_mktg_highlights, POSITION('Artist Quotes:',${TABLE}.global_mktg_highlights)),POSITION('Album Blurb:',${TABLE}.global_mktg_highlights)-POSITION('Artist Quotes:',${TABLE}.global_mktg_highlights)),16)
            ELSE 'Needed'
            END ;;
  }

  dimension: album_blurb_check {
    label: "Album Blurb Check"
    type: string
    hidden: yes
    sql: CASE
            WHEN contains(upper(${TABLE}.global_mktg_highlights),upper('Album Blurb: Blank')) THEN 'Needed'
            ELSE 'Have'
            END;;
  }

  dimension: album_blurb{
    label: "Album Blurb"
    type: string
    hidden: yes
    sql: CASE
            WHEN CONTAINS(UPPER(${TABLE}.global_mktg_highlights),UPPER('Album Blurb:')) THEN SUBSTR(LEFT(SUBSTR(${TABLE}.global_mktg_highlights, POSITION('Album Blurb:',${TABLE}.global_mktg_highlights)),POSITION('Recent Press:',${TABLE}.global_mktg_highlights)-POSITION('Album Blurb:',${TABLE}.global_mktg_highlights)),14)
            ELSE 'Needed'
            END ;;
  }

  dimension: recent_press_check {
    label: "Recent Press Check"
    type: string
    hidden: yes
    sql: CASE
            WHEN contains(upper(${TABLE}.global_mktg_highlights),upper('Recent Press: Blank')) THEN 'Needed'
            ELSE 'Have'
            END;;
  }

  dimension: recent_press{
    label: "Recent Press"
    type: string
    hidden: yes
    sql: CASE
            WHEN CONTAINS(UPPER(${TABLE}.global_mktg_highlights),UPPER('Recent Press:')) THEN SUBSTR(LEFT(SUBSTR(${TABLE}.global_mktg_highlights, POSITION('Recent Press:',${TABLE}.global_mktg_highlights)),POSITION('Press Quotes:',${TABLE}.global_mktg_highlights)-POSITION('Recent Press:',${TABLE}.global_mktg_highlights)),15)
            ELSE 'Needed'
            END ;;
  }

  dimension: press_quotes_check {
    label: "Press Quotes Check"
    type: string
    hidden: yes
    sql: CASE
            WHEN contains(upper(${TABLE}.global_mktg_highlights),upper('Press Quotes: Blank')) THEN 'Needed'
            ELSE 'Have'
            END;;
  }

  dimension: press_quotes{
    label: "Press Quotes"
    type: string
    hidden: yes
    sql: CASE
            WHEN CONTAINS(UPPER(${TABLE}.global_mktg_highlights),UPPER('Press Quotes:')) THEN SUBSTR(LEFT(SUBSTR(${TABLE}.global_mktg_highlights, POSITION('Press Quotes:',${TABLE}.global_mktg_highlights)),POSITION('Advertising Plans & Spends:',${TABLE}.global_mktg_highlights)-POSITION('Press Quotes:',${TABLE}.global_mktg_highlights)),15)
            ELSE 'Needed'
            END ;;
  }

  dimension: advertising_plans_check {
    label: "Advertising Plans & Spends Check"
    type: string
    hidden: yes
    sql: CASE
            WHEN contains(upper(${TABLE}.global_mktg_highlights),upper('Advertising Plans & Spends: Blank')) THEN 'Needed'
            ELSE 'Have'
            END;;
  }

  dimension: advertising_plans{
    label: "Advertising Plans & Spends"
    type: string
    hidden: yes
    sql: CASE
            WHEN CONTAINS(UPPER(${TABLE}.global_mktg_highlights),UPPER('Advertising Plans & Spends:')) THEN SUBSTR(LEFT(SUBSTR(${TABLE}.global_mktg_highlights, POSITION('Advertising Plans & Spends:',${TABLE}.global_mktg_highlights)),POSITION('Retail Campaign Drivers:',${TABLE}.global_mktg_highlights)-POSITION('Advertising Plans & Spends:',${TABLE}.global_mktg_highlights)),30)
            ELSE 'Needed'
            END ;;
  }

  dimension: retial_campaign_drivers_check {
    label: "Retail Campaign Drivers Check"
    type: string
    hidden: yes
    sql: CASE
            WHEN contains(upper(${TABLE}.global_mktg_highlights),upper('Retail Campaign Drivers: Blank')) THEN 'Needed'
            ELSE 'Have'
            END;;
  }

  dimension: retial_campaign_drivers{
    label: "Retail Campaign Drivers"
    type: string
    hidden: yes
    sql: CASE
            WHEN CONTAINS(UPPER(${TABLE}.global_mktg_highlights),UPPER('Retail Campaign Drivers:')) THEN SUBSTR(LEFT(SUBSTR(${TABLE}.global_mktg_highlights, POSITION('Retail Campaign Drivers:',${TABLE}.global_mktg_highlights)),POSITION('Growth/Streaming/Sales Stats:',${TABLE}.global_mktg_highlights)-POSITION('Retail Campaign Drivers:',${TABLE}.global_mktg_highlights)),26)
            ELSE 'Needed'
            END ;;
  }

  dimension: growth_streaming_sales_stats_check {
    label: "Growth/Streaming/Sales Stats Check"
    type: string
    hidden: yes
    sql: CASE
            WHEN contains(upper(${TABLE}.global_mktg_highlights),upper('Growth/Streaming/Sales Stats: Blank')) THEN 'Needed'
            ELSE 'Have'
            END;;
  }

  dimension: growth_streaming_sales_stats{
    label: "Growth/Streaming/Sales Stats"
    type: string
    hidden: yes
    sql: CASE
            WHEN CONTAINS(UPPER(${TABLE}.global_mktg_highlights),UPPER('Growth/Streaming/Sales Stats:')) THEN SUBSTR(LEFT(SUBSTR(${TABLE}.global_mktg_highlights, POSITION('Growth/Streaming/Sales Stats:',${TABLE}.global_mktg_highlights)),POSITION('Radio Promotion Plans:',${TABLE}.global_mktg_highlights)-POSITION('Growth/Streaming/Sales Stats:',${TABLE}.global_mktg_highlights)),31)
            ELSE 'Needed'
            END ;;
  }

  dimension: radio_promotion_plans_check {
    label: "Radio Promotion Plans Check"
    type: string
    hidden: yes
    sql: CASE
            WHEN contains(upper(${TABLE}.global_mktg_highlights),upper('Radio Promotion: Blank')) THEN 'Needed'
            ELSE 'Have'
            END;;
  }

  dimension: radio_promotion_plans{
    label: "Radio Promotion Plans"
    type: string
    hidden: yes
    sql: CASE
            WHEN CONTAINS(UPPER(${TABLE}.global_mktg_highlights),UPPER('Radio Promotion Plans:')) THEN SUBSTR(LEFT(SUBSTR(${TABLE}.global_mktg_highlights, POSITION('Radio Promotion Plans:',${TABLE}.global_mktg_highlights)),POSITION('Tour Dates:',${TABLE}.global_mktg_highlights)-POSITION('Radio Promotion Plans:',${TABLE}.global_mktg_highlights)),24)
            ELSE 'Needed'
            END ;;
  }

  dimension: tour_dates_check {
    label: "Tour Dates Check"
    type: string
    hidden: yes
    sql: CASE
            WHEN contains(upper(${TABLE}.global_mktg_highlights),upper('Tour Dates: Blank')) THEN 'Needed'
            ELSE 'Have'
            END;;
  }

  dimension: tour_dates{
    label: "Tour Dates"
    type: string
    hidden: yes
    sql: CASE
            WHEN CONTAINS(UPPER(${TABLE}.global_mktg_highlights),UPPER('Tour Dates:')) THEN SUBSTR(SUBSTR(${TABLE}.global_mktg_highlights, POSITION('Tour Dates:',${TABLE}.global_mktg_highlights)),13)
            ELSE 'Needed'
            END ;;
  }

  dimension: marketing_blurb_check {
    label: "Marketing Blurb Check"
    type: string
    hidden: yes
    sql: iff(length(${TABLE}.mktg_blurb) = 0, 'Needed','Have') ;;

  }

  dimension: store_specific_highlights_check {
    label: "Store Specific Highlights Check"
    type: string
    hidden: yes
    sql: iff(length(${TABLE}.store_specific_highlights) = 0, 'Needed', 'Have') ;;

  }



}