view: dt_deliverable_from_vendor {
  derived_table: {
    sql:
    {% assign var1=_filters['store'] %}
    {% assign var2=_filters['distribution_type'] %}
    {% assign var3=_filters['territory'] %}
      SELECT
        v.vendor_id,
        IFNULL(restrict_vendor_master.yes, TRUE)
          AS is_deliverable_vendor_store,
        IFNULL(restrict_vendor_territory.yes, TRUE)
          AS is_deliverable_vendor_territory,
        IFNULL(restrict_vendor_master.yes, TRUE)
          AND IFNULL(restrict_vendor_dms.yes, TRUE)
          AND IFNULL(restrict_vendor_territory.yes, TRUE)
          AS is_deliverable_vendor_storeterritory
      FROM royalty_accounting_reporting.prod.vw_dim_abacus_ar_vendor v
        INNER JOIN intelligence.dbt_prod.active_vendor_contracts vw ON v.vendor_id = vw.vendor_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 ;;
  }

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

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

  dimension: is_deliverable_vendor_territory {
    type: yesno
    label: "Label to Country"
    sql: ${TABLE}.is_deliverable_vendor_territory ;;
    description: "Use Label-Level Filter-Only Fields: Country Code"
  }

  dimension: is_deliverable_vendor_storeterritory {
    type: yesno
    label: "Label to Store/Country"
    sql: ${TABLE}.is_deliverable_vendor_storeterritory ;;
    description: "Use Label-Level Filter-Only Fields: Store Name, Country Code, and Distribution Type"
  }

  filter: territory {
    label: "Country Code (Label-Level)"
    type: string
  }

  filter: store {
    label: "Store Name (Label-Level)"
    type: string
  }

  filter: distribution_type {
    label: "Distribution Type (Label-Level)"
    type: string
  }

}