Untitled

   SELECT
        it.item_nr,
        it.order_nr,
        it.catalog_sku AS sku,
        it.product_fulltype_code,
        src_w.partner_warehouse_code AS source_warehouse,
        dst_w.partner_warehouse_code AS destination_warehouse,
        dst_w.misc ->> '$.darkstore_name' as destination_ds_name,
        DATE_FORMAT(it.created_at, '%Y-%m-%d') AS order_placed_at,
        DATE_FORMAT(it.picking_eligibility_at, '%Y-%m-%d'),
        DATE_FORMAT(it.target_shipped_at, '%Y-%m-%d'),
        CASE
            WHEN it.id_status = 1 AND it.id_mp_status = 30 AND COALESCE(it.misc->>'$.is_stock_available', 1) = 0 THEN 'stock_unavailable'
            WHEN it.id_status = 1 AND it.id_mp_status = 30 AND COALESCE(it.misc->>'$.is_stock_available', 1) = 1 THEN 'pending_job_gen'
            WHEN it.id_status = 44 AND it.id_mp_status = 30 THEN 'pending_picking'
            WHEN it.id_status IN (36, 17) AND it.id_mp_status = 30 AND (htl.id_status = 1 OR htl.id_hub_transfer_line IS NULL) THEN 'pending_staging'
            WHEN it.id_status = 17 AND it.id_mp_status = 30 AND htl.id_status = 57 THEN 'pending_release_staging'
            WHEN it.id_status = 17 AND it.id_mp_status = 30 AND htl.id_status = 36 AND ht.id_status = 26 THEN 'pending_dispatching'
            WHEN it.id_status = 17 AND it.id_mp_status = 30 AND htl.id_status = 36 AND ht.id_status = 13 THEN 'pending_receiving'
            WHEN it.id_status = 17 AND it.id_mp_status = 30 AND htl.id_status = 69 THEN 'received_at_darkstore'
            WHEN it.id_mp_status = 31 THEN 'cancelled'
            ELSE 'unexpected_status'
        END AS status,
        sa.code AS staging_location,
        sh.awb_nr AS shipmentBarcode,
        sl.pbarcode_canonical as gtin,
        sl.wms_barcode,
        ht.canter_code AS pallet_barcode,
        IF(COALESCE(sh.misc ->> "$.is_shipment_urgent", 0) = 0 AND sh.id_status != 58, TRUE, FALSE) AS enable_mark_shipment_urgent
    FROM item it
    LEFT JOIN warehouse src_w USING (id_warehouse)
    LEFT JOIN warehouse dst_w ON (it.misc ->> '$.id_dst_warehouse' = dst_w.id_warehouse)
    LEFT JOIN shipment_line sl ON (sl.id_item, sl.is_active) = (it.id_item, 1)
    LEFT JOIN shipment sh USING (id_shipment)
    LEFT JOIN hub_transfer_line htl ON (htl.type, htl.line_type, htl.line_id) = ('forward_nim', 'shipment', sh.id_shipment)
    LEFT JOIN hub_transfer_group htg USING (id_hub_transfer_group)
    LEFT JOIN hub_transfer ht USING (id_hub_transfer)
    LEFT JOIN staging_area sa ON (sa.id_staging_area = htg.id_staging_area)
    WHERE it.id_mp = 9
            AND it.target_shipped_at > DATE_SUB(NOW(), INTERVAL 1 DAY)
            AND src_w.id_country = 1
    GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17
        HAVING status = 'received_at_darkstore'
    ORDER BY target_shipped_at DESC, order_nr