{{ config(
    materialized = 'table',
    partition_by = {'field':'date','data_type':'date'},
    cluster_by   = ['warehouse_id','slot_hour']
) }}


WITH base_no_seller AS (
  SELECT
    site_id,
    warehouse_id,
    slot_hour,
    date,
    SUM(booking_cnt) AS booking_cnt,
    SAFE_DIVIDE(
      SUM(on_time_rate * booking_cnt),
      NULLIF(SUM(booking_cnt), 0)
    ) AS on_time_rate
  FROM {{ ref('agg_inbound_kpis') }}   
  GROUP BY 1,2,3,4
),

base AS (
  SELECT
    site_id,
    warehouse_id,
    slot_hour,
    date,
    booking_cnt,
    on_time_rate,
    (1.0 - on_time_rate) AS delay_pct_raw
  FROM base_no_seller
),

mm AS (
  SELECT
    MIN(delay_pct_raw) AS min_delay,
    MAX(delay_pct_raw) AS max_delay,
    MIN(booking_cnt)   AS min_book,
    MAX(booking_cnt)   AS max_book
  FROM base
)

SELECT
  b.site_id,
  b.warehouse_id,
  b.slot_hour,
  b.date,
  b.booking_cnt,
  b.on_time_rate,
  b.delay_pct_raw,

  SAFE_DIVIDE(b.delay_pct_raw - m.min_delay, NULLIF(m.max_delay - m.min_delay, 0)) AS delay_norm,
  SAFE_DIVIDE(b.booking_cnt   - m.min_book , NULLIF(m.max_book  - m.min_book , 0)) AS volume_norm,

  100 * SAFE_DIVIDE(b.delay_pct_raw - m.min_delay, NULLIF(m.max_delay - m.min_delay, 0))
      * SAFE_DIVIDE(b.booking_cnt   - m.min_book , NULLIF(m.max_book  - m.min_book , 0)) AS criticality_index_norm

FROM base b
CROSS JOIN mm m
