WITH dash AS (
         SELECT o.order_id,
            o.date_time,
            o.user_id,
            o.address_id,
            o.transaction_id,
            o.transaction_date_time,
            o.transaction_status,
            o.name,
            o.address,
            o.land_mark,
            o.location_geometry,
            o.phone,
            o.delivery_type_code,
            o.order_status_code,
            o.decline_cancellation_reason,
            o.delivery_charge,
            o.charge_customer,
            o.branch_id,
            o.order_status_date_time,
            o.dispatch_date_time,
            o.driver_assigned,
            o.dispatch_count,
            o.alamat_to_dlvry_srvc_charge,
            o.service_charge,
            o.service_charge_customer,
            o.picked_up_time,
            o.driver_id,
            o.restaurant_settled,
            o.third_party_settled,
            o.driver_settled,
            o.payment_method,
            o.total_amount,
            o.rest_resp_date_time,
            o.user_geometry,
            o.dispatch_id,
            o.merchant_reference,
            o.vat,
            o.order_amount,
            o.vat_enabled,
            o.uk_vat,
            o.service_charge_percent,
            o.hyperpay_registration_id,
            o.delivered_date_time,
            o.near_to_customer_date_time,
            o.near_to_pickup_date_time,
            o.driver_assigned_date_time,
            o.cc_amount,
            o.cart_id,
            o.at_customer_date_time,
            o.eta_to_pickup,
            o.eta_to_destination,
            o.expected_to_pickup,
            o.expected_to_destination,
            o.customer_comment,
            o.order_rated_status,
            o.menu_class,
            o.custom_order_id,
            o.use_customer_wallet,
            o.wallet_amount_to_deduct,
            o.saned_driver_adjustment,
            o.rest_dlvry_offer_amount,
            o.rest_dlvry_offer_vat,
            o.cc_amount_vat,
            o.schedule_datetime,
            c.city_id,
            c.city_name,
            c.city_name_ar,
            c.location,
            c.order_active,
            c.tier_radius,
            c.polygon_boundry,
            c.updated_at,
            c.delivery_group_id,
            f_now_immutable() AS systime,
            f_now_immutable()::timestamp with time zone - o.date_time AS status_age,
            f_now_immutable()::timestamp with time zone - o.date_time AS order_age,
            f_now_immutable()::timestamp with time zone - o.driver_assigned_date_time AS no_pickup_age,
            f_now_immutable()::timestamp with time zone - o.picked_up_time AS pickup_age
           FROM restaurant_order o
             JOIN branch b ON o.branch_id = b.branch_id
             JOIN city c ON b.city_id = c.city_id
          WHERE o.date_time >= get_businessday_f() AND o.date_time <= f_now_immutable()
        ), dupes_count AS (
         SELECT count(*) AS dupes,
            dupes_daily.city_id
           FROM dupes_daily
          GROUP BY dupes_daily.city_id
        )
 SELECT d.city_name,
    d.city_name_ar,
    d.city_id,
    d.systime,
    count(*) AS totalrecords,
    count(
        CASE
            WHEN d.order_status_code = 'A'::bpchar THEN 1
            ELSE NULL::integer
        END) AS accepted,
    count(
        CASE
            WHEN d.order_status_code = 'A'::bpchar AND d.driver_id IS NULL THEN 1
            ELSE NULL::integer
        END) AS nodriver,
    count(
        CASE
            WHEN d.order_status_code = 'J'::bpchar THEN 1
            ELSE NULL::integer
        END) AS rejected,
    count(
        CASE
            WHEN d.order_status_code = 'C'::bpchar THEN 1
            ELSE NULL::integer
        END) AS cancelled,
    count(
        CASE
            WHEN d.order_status_code = 'T'::bpchar THEN 1
            ELSE NULL::integer
        END) AS timedout,
    count(
        CASE
            WHEN d.order_status_code = 'O'::bpchar THEN 1
            ELSE NULL::integer
        END) AS outfordelivery,
    count(
        CASE
            WHEN d.order_status_code = 'N'::bpchar THEN 1
            ELSE NULL::integer
        END) AS new,
    count(
        CASE
            WHEN (d.order_status_code = ANY (ARRAY['A'::bpchar, 'O'::bpchar])) AND d.driver_id IS NOT NULL THEN 1
            ELSE NULL::integer
        END) AS onthego,
    count(
        CASE
            WHEN d.order_status_code = ANY (ARRAY['A'::bpchar, 'O'::bpchar, 'D'::bpchar]) THEN 1
            ELSE NULL::integer
        END) AS processed,
    count(
        CASE
            WHEN (d.order_status_code = ANY (ARRAY['A'::bpchar, 'N'::bpchar, 'O'::bpchar, 'D'::bpchar])) AND d.date_time > (f_now_immutable() - '00:01:00'::interval) THEN 1
            ELSE NULL::integer
        END) AS orderspermin,
    count(
        CASE
            WHEN d.order_status_code = 'A'::bpchar AND d.driver_id IS NULL AND d.order_age >= '00:10:00'::interval AND d.order_age <= '00:20:00'::interval THEN 1
            ELSE NULL::integer
        END) AS no10,
    count(
        CASE
            WHEN d.order_status_code = 'A'::bpchar AND d.driver_id IS NULL AND d.order_age >= '00:20:00'::interval THEN 1
            ELSE NULL::integer
        END) AS no20,
    count(
        CASE
            WHEN d.order_status_code = 'A'::bpchar AND d.driver_id IS NOT NULL AND d.no_pickup_age >= '00:25:00'::interval THEN 1
            ELSE NULL::integer
        END) AS nopickup25,
    count(
        CASE
            WHEN d.order_status_code = 'O'::bpchar AND d.pickup_age >= '00:30:00'::interval AND d.pickup_age <= '01:30:00'::interval THEN 1
            ELSE NULL::integer
        END) AS outfor30,
    count(
        CASE
            WHEN (d.order_status_code = ANY (ARRAY['A'::bpchar, 'O'::bpchar])) AND d.order_age >= '01:30:00'::interval THEN 1
            ELSE NULL::integer
        END) AS outfor90,
    count(
        CASE
            WHEN d.total_amount > 300::numeric AND (d.order_status_code = ANY (ARRAY['A'::bpchar, 'O'::bpchar, 'R'::bpchar])) THEN 1
            ELSE NULL::integer
        END) AS highvalue,
    count(
        CASE
            WHEN d.order_status_code = 'D'::bpchar THEN 1
            ELSE NULL::integer
        END) AS delivered,
    COALESCE(dc.dupes, 0::bigint) AS dupes,
    0::bigint AS newcustomers,
    count(
        CASE
            WHEN d.schedule_datetime IS NOT NULL AND (d.order_status_code = ANY (ARRAY['A'::bpchar, 'N'::bpchar])) THEN 1
            ELSE NULL::integer
        END) AS scheduleorders
   FROM dash d
     LEFT JOIN dupes_count dc ON dc.city_id = d.city_id
  WHERE d.city_id IS NOT NULL
  GROUP BY d.city_name, d.city_name_ar, d.city_id, d.systime, dc.dupes