view: dt_release_schedule_dashboard {
  sql_table_name: INTELLIGENCE.DBT_PROD.RELEASE_SCHEDULE_DASHBOARD ;;

#  derived_table: {
#    sql:
#SELECT
#    r.release_id,
#    r.release_date,
#    r.sale_start_date,
#    r.preorder_date,
#    r.upc,
#    r.product_code,
#    r.last_updated,
#    r.delivered_version AS version,
#    ai.artist_id,
#    ai.name AS artist_name,
#    ac.name AS artist_country,
#    r.release_name,
#    r.label AS imprint,
#    g.genre,
#    sg.subgenre,
#    v.vendor_id AS label_id,
#    lc.name AS label_country,
#    IFF(v.company IS NULL OR v.company = '', v.name, v.company) AS label_name,
#    oa.f_name||' '||oa.l_name AS label_manager,
#    df.context_type,
#    v.quarterback_label_manager,
#    COALESCE(r.format, '') AS release_format,
#    COALESCE(df.media_format, '') AS media_format,
#    COALESCE(pp.display_configuration, '') AS display_configuration,
#    COALESCE(mkt1.mktg_blurb, '') AS mktg_blurb,
#    COALESCE(sh.sync_highlights, '') AS sync_highlights,
#    COALESCE(mkt2.global_mktg_highlights, '') AS global_mktg_highlights,
#    COALESCE(md.local_mktg_highlights, '') AS local_mktg_highlights,
#    COALESCE(dsh.store_specific_highlights, '') AS store_specific_highlights,
#    COALESCE(dmp.internal_note, '') AS internal_note,
#    COALESCE(p.description, '') AS project_description,
#    COALESCE(pr.promo_link, '') AS promo_link,
#    mp.priority,
#    mp.priority_ab,
#    mp.priority_a,
#    mp.priority_b,
#    r.release_status,
#    r.deletions,
#    r.not_for_distribution
#FROM orchard_app_reporting_v2.art_relations_prod_art_relations.releases r
#    INNER JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.project p ON r.project_id = p.project_id
#    INNER JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.artist_info ai ON r.artist_id = ai.artist_id
#        LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.country ac ON ai.orchard_country = ac.id
#    INNER JOIN royalty_accounting_reporting.prod.vw_dim_abacus_ar_vendor v ON ai.vendor_id = v.vendor_id
#        LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.country lc ON v.country = lc.id
#    INNER JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.orchadmin_users oa ON v.assigned_to = oa.id
#    LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.genre g ON r.genre_id = g.genre_id
#    LEFT JOIN (
#        SELECT rs.release_id, s.name AS subgenre
#        FROM orchard_app_reporting_v2.art_relations_prod_art_relations.release_subgenre rs
#        INNER JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.subgenre s ON rs.subgenre_id = s.orchard_id
#    ) sg ON r.release_id = sg.release_id
#    LEFT JOIN (
#        SELECT d.distribution_format_id, d.context_type, dfm.name AS media_format
#        FROM orchard_app_reporting_v2.art_relations_prod_art_relations.distribution_format d
#        INNER JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.distribution_format_media dfm ON d.distribution_format_media_id = dfm.distribution_format_media_id
#    ) df ON r.distribution_format_id = df.distribution_format_id
#    LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.product_physical pp ON r.release_id = pp.release_id
#    LEFT JOIN (
#        SELECT
#            info_for_id AS release_id,
#            REGEXP_REPLACE(description,'(<[^>]+?>|&nbsp;)') AS mktg_blurb
#        FROM orchard_app_reporting_v2.art_relations_prod_art_relations.mkt_program_info
#        WHERE info_for = 'release' AND mkt_program_id = 15
#    ) mkt1 ON r.release_id = mkt1.release_id
#    LEFT JOIN (
#        SELECT
#            info_for_id AS project_id,
#            REGEXP_REPLACE(description,'(<[^>]+?>|&nbsp;)') AS global_mktg_highlights
#        FROM orchard_app_reporting_v2.art_relations_prod_art_relations.mkt_program_info
#        WHERE info_for = 'project' AND mkt_program_id = 20
#    ) mkt2 ON r.project_id = mkt2.project_id
#        LEFT JOIN (
#        SELECT
#            info_for_id AS project_id,
#            REGEXP_REPLACE(description,'(<[^>]+?>|&nbsp;)') AS sync_highlights
#        FROM orchard_app_reporting_v2.art_relations_prod_art_relations.mkt_program_info
#        WHERE info_for = 'project' AND mkt_program_id = 22
#    ) sh ON r.project_id = sh.project_id
#     LEFT JOIN (
#         SELECT product_id, ('https://promo.theorchard.com/'||code) AS promo_link
#         FROM orchard_app_reporting_v2.prod_promo_player_promo_player.promo_player
#         WHERE active = 1
#     ) pr ON r.release_id = pr.product_id
#    LEFT JOIN (
#        SELECT
#            release_id,
#            priority,
#            LISTAGG(priority_ab, '||') WITHIN GROUP (ORDER BY priority_ab ASC) AS priority_ab,
#            LISTAGG(priority_a, '||') WITHIN GROUP (ORDER BY priority_a ASC) AS priority_a,
#            LISTAGG(priority_b, '||') WITHIN GROUP (ORDER BY priority_b ASC) AS priority_b
#        FROM (
#            SELECT
#                mp.release_id,
#                mp.priority as priority,
#                dmpc.country_name AS priority_ab,
#                IFF(mp.priority = 'a', dmpc.country_name, NULL) AS priority_a,
#                IFF(mp.priority = 'b', dmpc.country_name, NULL) AS priority_b
#            FROM orchard_app_reporting_v2.art_relations_prod_art_relations.mkt_priority mp
#                INNER JOIN (
#                    SELECT DISTINCT country_id, country_name
#                    FROM orchard_app_reporting_v2.prod_sales_goals_sales_goals.digital_marketing_projections_by_country
#                    WHERE country_name != 'United States'
#                ) dmpc ON mp.country_id = dmpc.country_id
#        )
#        GROUP BY 1,2
#    ) mp ON r.release_id = mp.release_id
#    LEFT JOIN (
#        SELECT
#            project_id,
#            LISTAGG(country_name||': '||value, '||') WITHIN GROUP (ORDER BY country_name ASC) AS local_mktg_highlights
#        FROM orchard_app_reporting_v2.prod_sales_goals_sales_goals.marketing_driver
#        GROUP BY 1
#    ) md ON r.project_id = md.project_id
#    LEFT JOIN (
#        SELECT
#            dsh.project_id,
#            LISTAGG(ds.store||': '||dsh.value, '||') WITHIN GROUP (ORDER BY ds.store ASC) AS store_specific_highlights
#            FROM orchard_app_reporting_v2.prod_sales_goals_sales_goals.digital_store_highlights dsh
#                INNER JOIN orchard_app_reporting_v2.prod_sales_goals_sales_goals.digital_stores ds ON dsh.store_id = ds.id
#        GROUP BY 1
#    ) dsh ON r.project_id = dsh.project_id
#    LEFT JOIN orchard_app_reporting_v2.prod_sales_goals_sales_goals.digital_marketing_projections dmp ON r.release_id = dmp.product_id
#WHERE r.product_type_id = 1
#    AND r.deletions = 'N'
#    AND r.not_for_distribution IN ('N','SwitchboardDummy') ;;
#  }

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

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

  dimension: is_active_release {
    label: "Is Active Release?"
    sql: ${TABLE}.release_status='in_content'
              AND ${TABLE}.deletions!='Y'
              AND ${TABLE}.not_for_distribution='N';;
    description: "Releases that have the following criteria:
    Release Status = in_content;
    NOT Deleted;
    (Not-Not) For Distribution"
    type: yesno
  }

  dimension: sale_start_date {
    type: date
    sql: ${TABLE}.sale_start_date ;;
  }

  dimension: preorder_date {
    type: date
    sql: ${TABLE}.preorder_date ;;
  }

  dimension: last_updated {
    type:date
    sql: ${TABLE}.last_updated ;;
  }

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

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

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

  dimension: artist_id {
    type: number
    sql: ${TABLE}.artist_id ;;
  }

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

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

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

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

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

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

  dimension: label_id {
    type: number
    sql: ${TABLE}.label_id ;;
  }

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

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

  dimension: label_manager {
    type: string
    label: "Relationship Manager"
    sql: ${TABLE}.label_manager ;;
  }

  dimension: quarterback_label_manager {
    type: number
    label: "Secondary Relationship Manager"
    sql: ${TABLE}.quarterback_label_manager ;;
    hidden: yes
  }

  dimension: context_type {
    case: {
      when: {
        sql: ${TABLE}.context_type = 'digital' ;;
        label: "Digital"
      }
      when: {
        sql: ${TABLE}.context_type = 'physical' ;;
        label: "Physical"
      }
    }
  }

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

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

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

  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 Marketing Highlights"
    type: string
    sql: ${TABLE}.global_mktg_highlights ;;
    html:
    {% assign highlights = value %}
    {% unless highlights.size == 0 %}

    <details>
    <summary>Expand</summary>
    {{highlights}}
    </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: local_mktg_highlights {
    label: "Local Marketing 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 {
    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: 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: project_description {
    type: string
    sql: ${TABLE}.project_description ;;
    html:
    {% assign project_description = value %}
    {% unless project_description.size == 0 %}

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

    {% endunless %}
    ;;
  }

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

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

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

  dimension: priority_a {
    type: string
    sql: ${TABLE}.priority_a ;;
    html:
      {% assign countries = value | split: "||" %}

      {% if countries == null or countries.size == 0 or (countries.size == 1 and countries[0] == "") %}
        ø
      {% else %}
        <details>
          <summary>Detail ({{countries.size}})</summary>
          <ul>
            {% for country in countries %}
              {% if country != "" %}
                <li>{{country}}</li>
              {% endif %}
            {% endfor %}
          </ul>
        </details>
      {% endif %}
      ;;
  }

  dimension: priority_b {
    type: string
    sql: ${TABLE}.priority_b ;;
    html:
    {% assign countries = value | split: "||" %}

    {% if countries == null or countries.size == 0 or (countries.size == 1 and countries[0] == "") %}
    ø
    {% else %}
    <details>
    <summary>Detail ({{countries.size}})</summary>
    <ul>
    {% for country in countries %}
    {% if country != "" %}
    <li>{{country}}</li>
    {% endif %}
    {% endfor %}
    </ul>
    </details>
    {% endif %}
    ;;
  }

  dimension: release_status {
    case: {
      when: { sql: ${TABLE}.release_status = 'in_content' ;; label: "In Content" }
      when: { sql: ${TABLE}.release_status = 'orchard_processing' ;; label: "Orchard Processing" }
      when: { sql: ${TABLE}.release_status = 'label_processing' ;; label: "Label Processing" }
      when: { sql: ${TABLE}.release_status = 'transfer_to_content' ;; label: "Transfer to Content" }
      when: { sql: ${TABLE}.release_status = 'label_confirmation' ;; label: "Label Confirmation" }
    }
  }

  dimension: contains_lyrics {
    type: yesno
    sql:  ${TABLE}.mktg_blurb is not null
      and ${TABLE}.mktg_blurb !='';;
  }

  dimension: delivered_with_spatial_audio_to_apple{
    type: yesno
    sql:  CONTAINS (${int_dbt_prod_apple_spatial_audio_deliveries.upc}, ${upc});;
  }
}