view: dt_flatten_marketing_info {
  derived_table: {
    sql:
SELECT
    r.release_id,
    COALESCE(mkt1.mktg_blurb, '') AS mktg_blurb,
    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(sh.sync_highlights, '') AS sync_highlights,
    COALESCE(dmp.internal_note, '') AS internal_note,
    COALESCE(pr.promo_link, '') AS promo_link
FROM orchard_app_reporting_v2.art_relations_prod_art_relations.releases r
    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
            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
            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
            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
    LEFT JOIN (
        SELECT product_id, ('https://promo.theorchard.com/'||code) AS promo_link
        FROM orchard_app_reporting_v2.promo_player.promo_player
        WHERE active = 1
    ) pr ON r.release_id = pr.product_id ;;
  }

# 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>
    ;;
  }
}