view: dt_proper_internal_release_schedule {
  derived_table: {
    sql:
 WITH change_history AS (
    SELECT DISTINCT
        ppch.product_id,
        CASE
            WHEN ppch.store_id IS NULL THEN INITCAP(REGEXP_REPLACE(ppch.field_name, '[^a-zA-Z0-9]', ' '))
            ELSE INITCAP(REGEXP_REPLACE(ppch.field_name, '[^a-zA-Z0-9]', ' '))||' - '||cmm.customer_name
            END AS record_type,
        ppch.date_changed
    FROM orchard_app_reporting_v2.art_relations_prod_art_relations.product_physical_change_history ppch
        LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.customer_master_master cmm ON ppch.store_id = cmm.customer_master_master_id
    WHERE ( store_id IS NULL OR store_id = 696 )
    UNION ALL (
        SELECT pp.release_id, 'New Addition', r.ingestion_completed
        FROM orchard_app_reporting_v2.art_relations_prod_art_relations.product_physical pp
            INNER JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.releases r ON pp.release_id = r.release_id
        WHERE DATEDIFF(hour, r.ingestion_completed, CURRENT_DATE()) <= 168
    )
)
SELECT
    ch.product_id,
    MAX(TO_DATE(ch.date_changed)) AS last_updated,
    LISTAGG(ch.record_type, ', ') WITHIN GROUP (ORDER BY ch.date_changed ASC) AS change_history
FROM change_history ch
    INNER JOIN (
        SELECT product_id, record_type, MAX(date_changed) AS max_date FROM change_history GROUP BY 1,2
    ) md ON ch.product_id = md.product_id AND ch.date_changed = md.max_date
WHERE {% condition date_changed %} date_changed {% endcondition %}
GROUP BY 1 ;;
  }

  filter: date_changed { type: date }

  dimension_group: last_updated {
    type: time
    sql: ${TABLE}.last_updated ;;
    timeframes: [date,week,month,year]
  }

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

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


}