view: dt_physical_delivery_flags {
  derived_table: {
    sql:
WITH delivery_restrictions AS (
    SELECT r.release_id, vdmr.customer_master_master_id
    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 intelligence.dbt_prod.active_vendor_contracts vw ON ai.vendor_id = vw.vendor_id
        INNER JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.vendor_dms_master_restriction vdmr ON vw.vendor_contract_id = vdmr.vendor_contract_id
        WHERE {% condition store_id %} customer_master_master_id {% endcondition %}
    UNION ALL
        SELECT r.release_id, sdmr.customer_master_master_id
        FROM orchard_app_reporting_v2.art_relations_prod_art_relations.releases r
        INNER JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.subaccount_dms_master_restriction sdmr ON r.subaccount_id = sdmr.subaccount_id
        WHERE r.subaccount_id IS NOT NULL AND {% condition store_id %} customer_master_master_id {% endcondition %}
    UNION ALL
        SELECT release_id, customer_master_master_id
        FROM orchard_app_reporting_v2.art_relations_prod_art_relations.release_dms_master_restriction
        WHERE {% condition store_id %} customer_master_master_id {% endcondition %}
),
delivery_history AS (
    SELECT DISTINCT upc
    FROM orchard_app_reporting_v2.art_relations_prod_art_relations.delivery_history
    WHERE {% condition store_id %} customer_master_master_id {% endcondition %}
)
SELECT
    r.release_id,
    IFF(dr.release_id IS NULL, TRUE, FALSE) AS is_deliverable,
    IFF(dh.upc IS NULL, FALSE, TRUE) AS is_delivered
FROM orchard_app_reporting_v2.art_relations_prod_art_relations.releases r
    LEFT JOIN delivery_restrictions dr ON r.release_id = dr.release_id
    LEFT JOIN delivery_history dh ON r.upc = dh.upc ;;
  }

  filter: store_id {
    type: number
  }

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

  dimension: is_deliverable {
    type: yesno
    sql: ${TABLE}.is_deliverable ;;
  }

  dimension: is_delivered {
    type: yesno
    sql: ${TABLE}.is_delivered ;;
  }

  dimension: delivery_discrepancy {
    type: yesno
    sql: ${TABLE}.is_deliverable != ${TABLE}.is_delivered ;;
  }
}