{{ config(materialized='table') }}

WITH base AS (
  SELECT
    site_id,
    cus_cust_id,
    seller_state,
    warehouse_id,
    slot_hour,
    appointment_date AS date,
    COUNT(*) AS booking_cnt,
    AVG(on_time_flag) AS on_time_rate,
    APPROX_QUANTILES(arrival_delay_min, 100)[OFFSET(50)] AS p50_delay_min,
    APPROX_QUANTILES(arrival_delay_min, 100)[OFFSET(75)] AS p75_delay_min,
    APPROX_QUANTILES(arrival_delay_min, 100)[OFFSET(90)] AS p90_delay_min,
    APPROX_QUANTILES(arrival_delay_min, 100)[OFFSET(95)] AS p95_delay_min,
    AVG(CASE WHEN arrival_delay_min > 30 THEN 1 ELSE 0 END) AS pct_delay_gt_30
  FROM {{ ref('fct_inbound_events') }}
  WHERE appointment_date IS NOT NULL
    AND slot_hour IS NOT NULL
  GROUP BY 1,2,3,4,5,6
),

thresholds AS (
  SELECT
    APPROX_QUANTILES(on_time_rate, 100)[OFFSET(25)] AS p25_on_time_rate,
    APPROX_QUANTILES(booking_cnt, 100)[OFFSET(75)] AS p75_booking_cnt
  FROM base
)

SELECT
  b.*,
  t.p25_on_time_rate,
  t.p75_booking_cnt,
  CASE
    WHEN b.on_time_rate < t.p25_on_time_rate
         AND b.booking_cnt > t.p75_booking_cnt
    THEN 1 ELSE 0
  END AS critical_slot_flag
FROM base b
CROSS JOIN thresholds t
