view: deliverable_digital_release_snowflake {
  derived_table: {
    sql:
    {% assign var1=_filters['store'] %}
    {% assign var2=_filters['distribution_type'] %}
    {% assign var3=_filters['territory'] %}
      SELECT
        r.release_id,
        IFNULL(restrict_vendor_master.yes, TRUE)
          AND IFNULL(restrict_subaccount_master.yes, TRUE)
          AND IFNULL(restrict_release_master.yes, TRUE)
          AS is_deliverable_release_store,
        IFNULL(restrict_vendor_territory.yes, TRUE)
          AND IFNULL(restrict_subaccount_territory.yes, TRUE)
          AND IFNULL(restrict_release_territory.yes, TRUE)
          AS is_deliverable_release_territory,
        IFNULL(restrict_vendor_master.yes, TRUE)
          AND IFNULL(restrict_vendor_dms.yes, TRUE)
          AND IFNULL(restrict_vendor_territory.yes, TRUE)
          AND IFNULL(restrict_subaccount_master.yes, TRUE)
          AND IFNULL(restrict_subaccount_dms.yes, TRUE)
          AND IFNULL(restrict_subaccount_territory.yes, TRUE)
          AND IFNULL(restrict_release_master.yes, TRUE)
          AND IFNULL(restrict_release_dms.yes, TRUE)
          AND IFNULL(restrict_release_territory.yes, TRUE)
          AS is_deliverable_release_storeterritory
      FROM orchard_app_reporting_v2.art_relations_prod_art_relations.releases r
        INNER JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.artist_info ai ON r.artist_id = ai.artist_id
        INNER JOIN royalty_accounting_reporting.prod.vw_dim_abacus_ar_vendor v ON ai.vendor_id = v.vendor_id
        INNER JOIN intelligence.dbt_prod.active_vendor_contracts vw ON v.vendor_id = vw.vendor_id
        LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.subaccount sa ON v.vendor_id = sa.vendor_id AND r.subaccount_id = sa.subaccount_id
-- 1. CHECK RESTRICTIONS (VENDOR)
-- 1A. STORE
        LEFT JOIN (
            SELECT vc.id, FALSE AS yes
            FROM royalty_accounting_reporting.prod.vw_dim_abacus_ar_vendor_contract vc
              LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.vendor_dms_master_restriction vdmr ON vdmr.vendor_contract_id = vc.id
              LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.customer_master_master cmm ON vdmr.customer_master_master_id = cmm.customer_master_master_id
              LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.distribution_type dt ON vdmr.distribution_type_id = dt.id
            WHERE {% if var1 == "" %} cmm.customer_master_master_id = 0
                  {%else%} {% condition store %} cmm.customer_name {% endcondition %}
                  {% endif %}
            AND   {% if var2 == "" %} vdmr.distribution_type_id = 0
                  {%else%} {% condition distribution_type %} dt.name {% endcondition %}
                  {% endif %}
        ) restrict_vendor_master ON restrict_vendor_master.id = vw.vendor_contract_id
-- 1B. STORE/TERRITORY
        LEFT JOIN (
            SELECT vc_flatten.id, FALSE AS yes
            FROM (
                SELECT id, TRY_TO_NUMBER(a.value::string) AS dms_id
                FROM royalty_accounting_reporting.prod.vw_dim_abacus_ar_vendor_contract,
                LATERAL FLATTEN(INPUT => SPLIT(dms_carve_out, ',')) a
              ) vc_flatten
              LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.customer_master cm ON vc_flatten.dms_id = cm.customer_id
              LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.customer_master_master cmm ON cm.customer_master_master_id = cmm.customer_master_master_id
              LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.country c ON cm.territory = c.id
            WHERE {% if var1 == "" %} cmm.customer_master_master_id = 0
                  {%else%} {% condition store %} cmm.customer_name {% endcondition %}
                  {% endif %}
            AND   {% if var3 == "" %} c.id = 0
                  {%else%} {% condition distribution_type %} c.country_code {% endcondition %}
                  {% endif %}
          ) restrict_vendor_dms ON restrict_vendor_dms.id = vw.vendor_contract_id
-- 1C. TERRITORY
        LEFT JOIN (
            SELECT vc_flatten.id, FALSE AS yes
            FROM (
                SELECT id, TRY_TO_NUMBER(a.value::string) AS country_id
                FROM royalty_accounting_reporting.prod.vw_dim_abacus_ar_vendor_contract,
                LATERAL FLATTEN(INPUT => SPLIT(territory_carve_out, ',')) a
              ) vc_flatten
              LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.country c ON vc_flatten.country_id = c.id
            WHERE {% if var3 == "" %} c.id = 0
                  {%else%} {% condition territory %} c.country_code {% endcondition %}
                  {% endif %}
          ) restrict_vendor_territory ON restrict_vendor_territory.id = vw.vendor_contract_id
-- 2. CHECK RESTRICTIONS (SUBACCOUNT)
-- 2A. STORE
        LEFT JOIN (
            SELECT sa.subaccount_id, FALSE AS yes
            FROM orchard_app_reporting_v2.art_relations_prod_art_relations.subaccount sa
              LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.subaccount_dms_master_restriction sdmr ON sa.subaccount_id = sdmr.subaccount_id
              LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.customer_master_master cmm ON sdmr.customer_master_master_id = cmm.customer_master_master_id
            WHERE {% if var1 == "" %} cmm.customer_master_master_id = 0
                  {%else%} {% condition store %} cmm.customer_name {% endcondition %}
                  {% endif %}
        ) restrict_subaccount_master ON restrict_subaccount_master.subaccount_id = sa.subaccount_id
-- 2B. STORE/TERRITORY
        LEFT JOIN (
            SELECT sa.subaccount_id, FALSE AS yes
            FROM orchard_app_reporting_v2.art_relations_prod_art_relations.subaccount sa
              LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.subaccount_dms_restriction sdr ON sa.subaccount_id = sdr.subaccount_id
              LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.customer_master cm ON sdr.dms_id = cm.customer_id
              LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.customer_master_master cmm ON cm.customer_master_master_id = cmm.customer_master_master_id
              LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.country c ON cm.territory = c.id
            WHERE {% if var1 == "" %} cmm.customer_master_master_id = 0
                  {%else%} {% condition store %} cmm.customer_name {% endcondition %}
                  {% endif %}
            AND   {% if var3 == "" %} c.id = 0
                  {%else%} {% condition distribution_type %} c.country_code {% endcondition %}
                  {% endif %}
          ) restrict_subaccount_dms ON restrict_subaccount_dms.subaccount_id = sa.subaccount_id
-- 2C. TERRITORY
        LEFT JOIN (
            SELECT sa.subaccount_id, FALSE AS yes
            FROM orchard_app_reporting_v2.art_relations_prod_art_relations.subaccount sa
              LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.subaccount_territory_restriction str ON sa.subaccount_id = str.subaccount_id
              LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.country c ON str.country_id = c.id
            WHERE {% if var3 == "" %} c.id = 0
                  {%else%} {% condition territory %} c.country_code {% endcondition %}
                  {% endif %}
          ) restrict_subaccount_territory ON restrict_subaccount_territory.subaccount_id = sa.subaccount_id
-- 3. CHECK RESTRICTIONS (RELEASE)
-- 3A. STORE
        LEFT JOIN (
            SELECT r.release_id, FALSE AS yes
            FROM orchard_app_reporting_v2.art_relations_prod_art_relations.releases r
              LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.release_dms_master_restriction rdmr ON r.release_id = rdmr.release_id
              LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.customer_master_master cmm ON rdmr.customer_master_master_id = cmm.customer_master_master_id
              LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.distribution_type dt ON rdmr.distribution_type_id = dt.id
            WHERE {% if var1 == "" %} cmm.customer_master_master_id = 0
                  {%else%} {% condition store %} cmm.customer_name {% endcondition %}
                  {% endif %}
            AND   {% if var2 == "" %} rdmr.distribution_type_id = 0
                  {%else%} {% condition distribution_type %} dt.name {% endcondition %}
                  {% endif %}
        ) restrict_release_master ON restrict_release_master.release_id = r.release_id
-- 3B. STORE/TERRITORY
        LEFT JOIN (
            SELECT r.release_id, FALSE AS yes
            FROM orchard_app_reporting_v2.art_relations_prod_art_relations.releases r
              LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.release_dms_restriction rdr ON r.release_id = rdr.release_id
              LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.customer_master cm ON rdr.dms_customer_id = cm.customer_id
              LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.customer_master_master cmm ON cm.customer_master_master_id = cmm.customer_master_master_id
              LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.country c ON cm.territory = c.id
            WHERE {% if var1 == "" %} cmm.customer_master_master_id = 0
                  {%else%} {% condition store %} cmm.customer_name {% endcondition %}
                  {% endif %}
            AND   {% if var3 == "" %} c.id = 0
                  {%else%} {% condition distribution_type %} c.country_code {% endcondition %}
                  {% endif %}
          ) restrict_release_dms ON restrict_release_dms.release_id = r.release_id
-- 3C. TERRITORY
        LEFT JOIN (
            SELECT r.release_id, FALSE AS yes
            FROM orchard_app_reporting_v2.art_relations_prod_art_relations.releases r
              LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.release_territory_restriction rtr ON r.release_id = rtr.release_id
              LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.country c ON rtr.country_id = c.id
            WHERE {% if var3 == "" %} c.id = 0
                  {%else%} {% condition territory %} c.country_code {% endcondition %}
                  {% endif %}
          ) restrict_release_territory ON restrict_release_territory.release_id = r.release_id ;;
  }

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

  dimension: is_deliverable_release_store {
    type: yesno
    label: "Store"
    sql: ${TABLE}.is_deliverable_release_store ;;
    description: "Use Filter-Only Fields: Store Name and Distribution Type"
  }

  dimension: is_deliverable_release_territory {
    type: yesno
    label: "Territory"
    sql: ${TABLE}.is_deliverable_release_territory ;;
    description: "Use Filter-Only Fields: Country Code"
  }

  dimension: is_deliverable_release_storeterritory {
    type: yesno
    label: "Substore"
    sql: ${TABLE}.is_deliverable_release_storeterritory ;;
    description: "Use Filter-Only Fields: Store Name, Country Code, and Distribution Type"
  }

  filter: territory {
    label: "Country Code"
    type: string
  }

  filter: store {
    label: "Store Name"
    type: string
  }

  filter: distribution_type {
    label: "Distribution Type"
    type: string
  }

}