📊 Regional Execution Tracker

WITH actuals_detail AS (
    -- Inbound Total (100004, 100032, 100360, 100359)
    SELECT
        i.warehouse_id as site,
        CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)::date as date,
        CASE
            WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 18 THEN 'Breakfast'
            WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 14 THEN 'Bedtime'
            WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 11 THEN 'Dinner'
            WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 6 THEN 'Lunch'
            ELSE 'Brunch'
        END as window,
        'transin' as department,
        SUM(i.unit_count) as total_units,
        SUM(i.each_count) as total_eaches,
        SUM(i.time_millis) / 3600.0 as total_hours
    FROM "andes"."scar_ssd_staging"."scar_latis_ppa_intra_day" i
    WHERE i.warehouse_id IN ('SOH1', 'SOH2', 'SOH3', 'SIN9', 'SKY2', 'SPA5')
      AND i.snapshot_date >= CURRENT_DATE - 7
      AND i.process_id IN (100004, 100032, 100360, 100359)
    GROUP BY 1, 2, 3, 4

    UNION ALL

    -- Non-Sort Actuals (100032 only - drill-in subset of Inbound)
    SELECT
        i.warehouse_id as site,
        CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)::date as date,
        CASE
            WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 18 THEN 'Breakfast'
            WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 14 THEN 'Bedtime'
            WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 11 THEN 'Dinner'
            WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 6 THEN 'Lunch'
            ELSE 'Brunch'
        END as window,
        'nonsort' as department,
        SUM(i.unit_count) as total_units,
        SUM(i.each_count) as total_eaches,
        SUM(i.time_millis) / 3600.0 as total_hours
    FROM "andes"."scar_ssd_staging"."scar_latis_ppa_intra_day" i
    WHERE i.warehouse_id IN ('SOH1', 'SOH2', 'SOH3', 'SIN9', 'SKY2', 'SPA5')
      AND i.snapshot_date >= CURRENT_DATE - 7
      AND i.process_id = 100032
    GROUP BY 1, 2, 3, 4

    UNION ALL

    -- Ship/Outbound (100131 only)
    SELECT
        i.warehouse_id as site,
        CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)::date as date,
        CASE
            WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 18 THEN 'Breakfast'
            WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 14 THEN 'Bedtime'
            WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 11 THEN 'Dinner'
            WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 6 THEN 'Lunch'
            ELSE 'Brunch'
        END as window,
        'outbound sd' as department,
        SUM(i.unit_count) as total_units,
        SUM(i.each_count) as total_eaches,
        SUM(i.time_millis) / 3600.0 as total_hours
    FROM "andes"."scar_ssd_staging"."scar_latis_ppa_intra_day" i
    WHERE i.warehouse_id IN ('SOH1', 'SOH2', 'SOH3', 'SIN9', 'SKY2', 'SPA5')
      AND i.snapshot_date >= CURRENT_DATE - 7
      AND i.process_id = 100131
    GROUP BY 1, 2, 3, 4

    UNION ALL

    -- ICQA Total (100017, 100026, 100112)
    SELECT
        i.warehouse_id as site,
        CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)::date as date,
        CASE
            WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 18 THEN 'Breakfast'
            WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 14 THEN 'Bedtime'
            WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 11 THEN 'Dinner'
            WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 6 THEN 'Lunch'
            ELSE 'Brunch'
        END as window,
        'icqa count' as department,
        SUM(i.unit_count) as total_units,
        SUM(i.each_count) as total_eaches,
        SUM(i.time_millis) / 3600.0 as total_hours
    FROM "andes"."scar_ssd_staging"."scar_latis_ppa_intra_day" i
    WHERE i.warehouse_id IN ('SOH1', 'SOH2', 'SOH3', 'SIN9', 'SKY2', 'SPA5')
      AND i.snapshot_date >= CURRENT_DATE - 7
      AND i.process_id IN (100017, 100026, 100112)
    GROUP BY 1, 2, 3, 4

    UNION ALL

    -- Sort Total (from regular sites - process_id 100021)
    SELECT
        i.warehouse_id as site,
        CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)::date as date,
        CASE
            WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 18 THEN 'Breakfast'
            WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 14 THEN 'Bedtime'
            WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 11 THEN 'Dinner'
            WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 6 THEN 'Lunch'
            ELSE 'Brunch'
        END as window,
        'sortation' as department,
        SUM(i.unit_count) as total_units,
        SUM(i.each_count) as total_eaches,
        SUM(i.time_millis) / 3600.0 as total_hours
    FROM "andes"."scar_ssd_staging"."scar_latis_ppa_intra_day" i
    WHERE i.warehouse_id IN ('SOH1', 'SOH2', 'SOH3', 'SIN9', 'SKY2', 'SPA5')
      AND i.snapshot_date >= CURRENT_DATE - 7
      AND i.process_id = 100021
    GROUP BY 1, 2, 3, 4

    UNION ALL

    -- Sort Total (from V-sites mapped to regular sites - process_id 100021)
    SELECT
        CASE
            WHEN i.warehouse_id = 'VOH1' THEN 'SOH1'
            WHEN i.warehouse_id = 'VOH2' THEN 'SOH2'
            WHEN i.warehouse_id = 'VOH3' THEN 'SOH3'
            WHEN i.warehouse_id = 'VIN9' THEN 'SIN9'
            WHEN i.warehouse_id = 'VKY2' THEN 'SKY2'
            WHEN i.warehouse_id = 'VPA5' THEN 'SPA5'
            ELSE i.warehouse_id
        END as site,
        CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)::date as date,
        CASE
            WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 18 THEN 'Breakfast'
            WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 14 THEN 'Bedtime'
            WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 11 THEN 'Dinner'
            WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 6 THEN 'Lunch'
            ELSE 'Brunch'
        END as window,
        'sortation' as department,
        SUM(i.unit_count) as total_units,
        SUM(i.each_count) as total_eaches,
        SUM(i.time_millis) / 3600.0 as total_hours
    FROM "andes"."scar_ssd_staging"."scar_latis_ppa_intra_day" i
    WHERE i.warehouse_id IN ('VOH1', 'VOH2', 'VOH3', 'VIN9', 'VKY2', 'VPA5')
      AND i.snapshot_date >= CURRENT_DATE - 7
      AND i.process_id = 100021
    GROUP BY 1, 2, 3, 4
),
actuals AS (
    SELECT
        site,
        date,
        window,
        SUM(CASE WHEN department = 'transin'     THEN total_units  END) as ib_units,
        SUM(CASE WHEN department = 'transin'     THEN total_eaches END) as ib_eaches,
        SUM(CASE WHEN department = 'transin'     THEN total_hours  END) as ib_hours,
        SUM(CASE WHEN department = 'nonsort'     THEN total_units  END) as nonsort_units,
        SUM(CASE WHEN department = 'nonsort'     THEN total_eaches END) as nonsort_eaches,
        SUM(CASE WHEN department = 'nonsort'     THEN total_hours  END) as nonsort_hours,
        SUM(CASE WHEN department = 'outbound sd' THEN total_units  END) as ob_units,
        SUM(CASE WHEN department = 'outbound sd' THEN total_eaches END) as ob_eaches,
        SUM(CASE WHEN department = 'outbound sd' THEN total_hours  END) as ob_hours,
        SUM(CASE WHEN department = 'sortation'   THEN total_units  END) as sort_units,
        SUM(CASE WHEN department = 'sortation'   THEN total_eaches END) as sort_eaches,
        SUM(CASE WHEN department = 'sortation'   THEN total_hours  END) as sort_hours,
        SUM(CASE WHEN department = 'icqa count'  THEN total_units  END) as icqa_units,
        SUM(CASE WHEN department = 'icqa count'  THEN total_eaches END) as icqa_eaches,
        SUM(CASE WHEN department = 'icqa count'  THEN total_hours  END) as icqa_hours
    FROM actuals_detail
    GROUP BY 1, 2, 3
),
planning AS (
    SELECT
        CASE
            WHEN lp_id = -15000149      THEN 'SOH2'
            WHEN lp_id = -295234656514  THEN 'SPA5'
            WHEN lp_id = -15000148      THEN 'SOH1'
            WHEN lp_id = -15000192      THEN 'SOH3'
            WHEN lp_id = -15000165      THEN 'SIN9'
            WHEN lp_id = -295234637505  THEN 'SKY2'
            ELSE 'Unknown'
        END as site,
        DATE(operations_date) as date,
        CASE
            WHEN EXTRACT(HOUR FROM start_datetime_local) >= 18 THEN 'Breakfast'
            WHEN EXTRACT(HOUR FROM start_datetime_local) >= 14 THEN 'Bedtime'
            WHEN EXTRACT(HOUR FROM start_datetime_local) >= 11 THEN 'Dinner'
            WHEN EXTRACT(HOUR FROM start_datetime_local) >= 6  THEN 'Lunch'
            WHEN EXTRACT(HOUR FROM start_datetime_local) >= 0  THEN 'Brunch'
        END as window,
        SUM(CASE WHEN process_path = 'transin'     THEN volume_original_value      END) as ib_vol_orig,
        SUM(CASE WHEN process_path = 'transin'     THEN volume_override_value      END) as ib_vol_override,
        SUM(CASE WHEN process_path = 'transin'     THEN labor_hours_original_value END) as ib_hrs_orig,
        SUM(CASE WHEN process_path = 'transin'     THEN labor_hours_override_value END) as ib_hrs_override,
        SUM(CASE WHEN process_path = 'outbound sd' THEN volume_original_value      END) as ob_vol_orig,
        SUM(CASE WHEN process_path = 'outbound sd' THEN volume_override_value      END) as ob_vol_override,
        SUM(CASE WHEN process_path = 'outbound sd' THEN labor_hours_original_value END) as ob_hrs_orig,
        SUM(CASE WHEN process_path = 'outbound sd' THEN labor_hours_override_value END) as ob_hrs_override,
        SUM(CASE WHEN process_path = 'sortation'   THEN volume_original_value      END) as sort_vol_orig,
        SUM(CASE WHEN process_path = 'sortation'   THEN volume_override_value      END) as sort_vol_override,
        SUM(CASE WHEN process_path = 'sortation'   THEN labor_hours_original_value END) as sort_hrs_orig,
        SUM(CASE WHEN process_path = 'sortation'   THEN labor_hours_override_value END) as sort_hrs_override,
        SUM(CASE WHEN process_path = 'icqa count'  THEN volume_original_value      END) as icqa_vol_orig,
        SUM(CASE WHEN process_path = 'icqa count'  THEN volume_override_value      END) as icqa_vol_override,
        SUM(CASE WHEN process_path = 'icqa count'  THEN labor_hours_original_value END) as icqa_hrs_orig,
        SUM(CASE WHEN process_path = 'icqa count'  THEN labor_hours_override_value END) as icqa_hrs_override
    FROM "andes"."GSF-Central"."labor_demand_outbound_aggregate"
    WHERE operations_date >= CURRENT_DATE - 7
      AND operations_date <= CURRENT_DATE + 7
      AND lp_id IN (-15000149, -295234656514, -15000148, -15000192, -15000165, -295234637505)
    GROUP BY 1, 2, 3
)
SELECT
    COALESCE(a.site, p.site)     as site,
    COALESCE(a.date, p.date)     as date,
    COALESCE(a.window, p.window) as window,

    -- Inbound Actuals
    a.ib_units              as ib_actual_units,
    a.ib_eaches             as ib_actual_eaches,
    a.ib_hours              as ib_actual_hours,

    -- Inbound Planning
    p.ib_vol_orig           as ib_plan_volume_original,
    p.ib_vol_override       as ib_plan_volume_override,
    p.ib_hrs_orig           as ib_plan_hours_original,
    p.ib_hrs_override       as ib_plan_hours_override,

    -- Outbound Actuals
    a.ob_units              as ob_actual_units,
    a.ob_eaches             as ob_actual_eaches,
    a.ob_hours              as ob_actual_hours,

    -- Outbound Planning
    p.ob_vol_orig           as ob_plan_volume_original,
    p.ob_vol_override       as ob_plan_volume_override,
    p.ob_hrs_orig           as ob_plan_hours_original,
    p.ob_hrs_override       as ob_plan_hours_override,

    -- Sort Actuals
    a.sort_units            as sort_actual_units,
    a.sort_eaches           as sort_actual_eaches,
    a.sort_hours            as sort_actual_hours,

    -- Sort Planning
    p.sort_vol_orig         as sort_plan_volume_original,
    p.sort_vol_override     as sort_plan_volume_override,
    p.sort_hrs_orig         as sort_plan_hours_original,
    p.sort_hrs_override     as sort_plan_hours_override,

    -- ICQA Actuals
    a.icqa_units            as icqa_actual_units,
    a.icqa_eaches           as icqa_actual_eaches,
    a.icqa_hours            as icqa_actual_hours,

    -- ICQA Planning
    p.icqa_vol_orig         as icqa_plan_volume_original,
    p.icqa_vol_override     as icqa_plan_volume_override,
    p.icqa_hrs_orig         as icqa_plan_hours_original,
    p.icqa_hrs_override     as icqa_plan_hours_override,

    -- Non-Sort Actuals (100032 only - drill-in subset of Inbound)
    a.nonsort_units         as nonsort_actual_units,
    a.nonsort_eaches        as nonsort_actual_eaches,
    a.nonsort_hours         as nonsort_actual_hours

FROM actuals a
FULL OUTER JOIN planning p
    ON a.site   = p.site
    AND a.date   = p.date
    AND a.window = p.window
ORDER BY site, date, window
;